MySQL

MySQL is a fast, easy-to-use RDBMS being used for many small and big businesses. MySQL is developed, marketed, and supported by MySQL AB, which is a Swedish company. MySQL is becoming so popular because of many good reasons:

  • MySQL is released under an open-source license. So you have nothing to pay to use it.
  • MySQL is a very powerful program in its own right. It handles a large subset of the functionality of the most expensive and powerful database packages.
  • MySQL uses a standard form of the well-known SQL data language.
  • MySQL works on many operating systems and with many languages including PHP, PERL, C, C++, JAVA, etc.
  • MySQL works very quickly and works well even with large data sets.
  • MySQL is very friendly to PHP, the most appreciated language for web development.
  • MySQL supports large databases, up to 50 million rows or more in a table. The default file size limit for a table is 4GB, but you can increase this (if your operating system can handle it) to a theoretical limit of 8 million terabytes (TB).
  • MySQL is customizable. The open-source GPL license allows programmers to modify the MySQL software to fit their own specific environments.

Getting Started

MySQL comes downloaded witht he following bundled:

  • MySQL – The MySQL database server, which manages databases and tables, controls user access, and processes SQL queries.
  • MySQL-client – MySQL client programs, which make it possible to connect to and interact with the server.
  • MySQL-devel – Libraries and header files that come in handy when compiling other programs that use MySQL.
  • MySQL-shared – Shared libraries for the MySQL client.
  • MySQL-bench – Benchmark and performance testing tools for the MySQL database server.

Installation

Download the installation binaries from the website. The community edition is the free version that doesn’t require licence.

For Windows it is straight forward as running the executable binary and following the wizard.

For Linux and non RPM based installation use the generic Mysql binaries. And unpack and untar the package.

MySql on CentOS 6 and RHEL 6

Download RPMs from MySQL

# cd /opt/
# sudo wget http://dev.mysql.com/get/Downloads/MySQL-5.6/MySQL-5.6.26-1.el6.x86_64.rpm-bundle.tar
# sudo tar xvf MySQL-5.6.26-1.el6.x86_64.rpm-bundle.tar

Download link:

Redhat 7 – http://dev.mysql.com/get/Downloads/MySQL-5.6/MySQL-5.6.26-1.el7.x86_64.rpm-bundle.tar

Redhat 6 – http://dev.mysql.com/get/Downloads/MySQL-5.6/MySQL-5.6.26-1.el6.x86_64.rpm-bundle.tar

 

Create a mysql User and Group

# sudo groupadd mysql
# sudo useradd -r -g mysql mysql

 

Remove the existing 5.6.X installation. OLDVERSION is the version to remove.

# sudo rpm -e --nodeps mysql-libs-OLDVERSION.el6_6.x86_64

Install MysQL RPMs

# sudo rpm –ivh --force MySQL-server-5.6.26-1.el6.x86_64.rpm
# sudo rpm -ivh MySQL-client-5.6.26-1.el6.x86_64.rpm
# sudo rpm -ivh MySQL-shared-5.6.26-1.el6.x86_64.rpm

MySql will get installed into /usr/share/mysql

 

Start MySQL Service

# sudo /etc/init.d/mysql start
Starting MySQL.. SUCCESS!

Initial MySQL Configuration

Execute mysql_secure_installation script and follow the wizard. It will prompt for root password. To get temporary root password check ‘/root/.mysql_secret’ file

# sudo cat /root/.mysql_secret
The random password set for the root user at Thu Aug 20 15:44:15 2015 (local time): QZmk9SrGtgEG4Ff2
# sudo /usr/bin/mysql_secure_installation
NOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MySQL
SERVERS IN PRODUCTION USE!  PLEASE READ EACH STEP CAREFULLY!

In order to log into MySQL to secure it, we'll need the current password for the root user.  If you've just installed MySQL, and you haven't set the root password yet, the password will be blank, so you should just press enter here.
Enter current password for root (enter for none):
OK, successfully used password, moving on...
Setting the root password ensures that nobody can log into the MySQL root user without the proper authorisation. You already have a root password set, so you can safely answer 'n'.
Change the root password? [Y/n] y
New password:
Re-enter new password:
Password updated successfully!
Reloading privilege tables..... Success!

By default, a MySQL installation has an anonymous user, allowing anyone to log into MySQL without having to have a user account created for them.  This is intended only for testing, and to make the installation go a bit smoother.  You should remove them before moving into a production environment.
Remove anonymous users? [Y/n] y... Success!
Normally, root should only be allowed to connect from 'localhost'.  This ensures that someone cannot guess at the root password from the network.
Disallow root login remotely? [Y/n] y ... Success!
By default, MySQL comes with a database named 'test' that anyone can access.  This is also intended only for testing, and should be removed before moving into a production environment.

Remove test database and access to it? [Y/n] y
- Dropping test database...... Success!
- Removing privileges on test database...... Success!
Reloading the privilege tables will ensure that all changes made so far will take effect immediately.
Reload privilege tables now? [Y/n] y... Success!
All done!  If you've completed all of the above steps, your MySQL installation should now be secure.
Thanks for using MySQL!
Cleaning up...

Login to MySQL

Now you have successfully installed MySQL 5.6. Let login to MySQL using root access and try to create a dummy database. The root user password can be found in /root/.mysql_secret.

# sudo mysql -u root -p

Create a dummy database using following command.

mysql> create database dummydb;
Query OK, 1 row affected (0.01 sec)

 

Check MySQL Version

Verify your MySQL version installed on your system. Following command will display installed MySQL version.

# sudo mysql -V
mysql  Ver 14.14 Distrib 5.6.26, for Linux (x86_64) using  EditLine wrapper

 

Mysql has been setup with user: root password: password

  • Setting up the database

IMPORTANT: These steps to setup the database should only be performed for the first time installation.

Two set of data sql scripts are provided :-

migration_data.sql – this will setup all the database tables and insert an admin user to login to the application for the first time.

templates_data.sql – this will insert an Internal BTEC and an External BTEC template to get start with transforming word documents.

Ensure the database tables and initial data is setup in the MYSQL database. Scripts are provided and these are run from the command line as follows.

# sudo mysql -uroot -ppassword < migration_data.sql
# sudo mysql -uroot -ppassword pearsonbtecquals < templates_data.sql
# sudo mysql -uroot –ppassword
mysql> use pearsonbtecquals

 

Now test that the script have inserted data. There should be one user (btectest / Password123) setup in the USER table as follows:

# sudo mysql –u root –p
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| pearsonbtecquals   |
| performance_schema |
+--------------------+
4 rows in set (0.00 sec)
mysql> use pearsonbtecquals
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> show tables;
+----------------------------+
| Tables_in_pearsonbtecquals |
+----------------------------+
| hibernate_unique_key       |
| image                      |
| specunit                   |
| template                   |
| template_templatesection   |
| transformation             |
| user                       |
+----------------------------+
7 rows in set (0.00 sec)
mysql> select * from user;
+----+--------------------------------------+---------+----------------+-----------+----------+----------------+------------+----------+
| id | UUID                                 | version | email          | firstname | lastname | passwordDigest | role       | username |
+----+--------------------------------------+---------+----------------+-----------+----------+----------------+------------+----------+
|  1 | 5e20ed06-0e33-4990-ae20-15b2946f0297 |       0 | test@email.com | BtecUser  | BtecTest | Password123    | ROLE_ADMIN | btectest |
+----+--------------------------------------+---------+----------------+-----------+----------+----------------+------------+----------+
1 row in set (0.00 sec)
Btectest user with password: Password123 has ROLE_ADMIN access. It is advisable to change the password either by USER MANAGEMENT page in the application or updating the record directly in the database.
Now test the second script to see if two templates have been inserted:
mysql> mysql> select * from template;
+----+--------------------------------------+---------+---------------------+----------+---------------+----------------------------+-------------------+--------------------+
| id | UUID                                 | version | description         | revision | templateName  | xQueryScriptLocation       | xsdScriptLocation | xsltScriptLocation |
+----+--------------------------------------+---------+---------------------+----------+---------------+----------------------------+-------------------+--------------------+
|  1 | 0546abf4-b5ce-42b8-bc9d-b28602500096 |       0 | 2016 BTEC Nationals | 1.0      | INTERNAL UNIT | wordxml_unit2_table.xquery | Unit.xsd          | wordxml_unit.xsl   |
|  2 | d1478cff-da5b-42ee-88cc-2dd9fa6523f3 |       0 | 2016 BTEC Nationals | 1.0      | EXTERNAL UNIT | wordxml_unit2_table.xquery | Unit.xsd          | wordxml_unit.xsl   |
+----+--------------------------------------+---------+---------------------+----------+---------------+----------------------------+-------------------+--------------------+
2 rows in set (0.00 sec)
mysql> quit
Bye

 

Configuration files

The /etc/my.cnf File Configuration:

Most of the cases, you should not touch this file. By default, it will have the following entries:

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

[mysql.server]
user=mysql
basedir=/var/lib

[safe_mysqld]
err-log=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

Here, you can specify a different directory for error log, otherwise you should not change any entry in this table.

 

Commands

Running and Shutting down MySQL Server:

First check if your MySQL server is running or not. You can use the following command to check this:

 ps -ef | grep mysqld

If your MySql is running, then you will see mysqld process listed out in your result. If server is not running, then you can start it by using the following command:

root@host# cd /usr/bin
./safe_mysqld &

Now, if you want to shut down an already running MySQL server, then you can do it by using the following command:

root@host# cd /usr/bin
./mysqladmin -u root -p shutdown
Enter password: ******

Setting Up a MySQL User Account:

For adding a new user to MySQL, you just need to add a new entry to usertable in database mysql.

Below is an example of adding new user guest with SELECT, INSERT and UPDATE privileges with the password guest123; the SQL query is:

root@host# mysql -u root -p
Enter password:*******
mysql> use mysql;
Database changed

mysql> INSERT INTO user 
          (host, user, password, 
           select_priv, insert_priv, update_priv) 
           VALUES ('localhost', 'guest', 
           PASSWORD('guest123'), 'Y', 'Y', 'Y');
Query OK, 1 row affected (0.20 sec)

mysql> FLUSH PRIVILEGES;
Query OK, 1 row affected (0.01 sec)

mysql> SELECT host, user, password FROM user WHERE user = 'guest';
+-----------+---------+------------------+
| host      | user    | password         |
+-----------+---------+------------------+
| localhost | guest | 6f8c114b58f2ce9e |
+-----------+---------+------------------+
1 row in set (0.00 sec)

When adding a new user, remember to encrypt the new password using PASSWORD() function provided by MySQL. As you can see in the above example the password mypass is encrypted to 6f8c114b58f2ce9e.

Notice the FLUSH PRIVILEGES statement. This tells the server to reload the grant tables. If you don’t use it, then you won’t be able to connect to mysql using the new user account at least until the server is rebooted.

Another way of adding user account is by using GRANT SQL command; following example will add user zara with password zara123 for a particular database called TUTORIALS.

root@host# mysql -u root -p password;
Enter password:*******
mysql> use mysql;
Database changed

mysql> GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP
    -> ON TUTORIALS.*
    -> TO 'zara'@'localhost'
    -> IDENTIFIED BY 'zara123';

This will also create an entry in mysql database table called user.

NOTE: MySQL does not terminate a command until you give a semi colon (;) at the end of SQL command.

 

Administrative MySQL Command:

Here is the list of important MySQL commands, which you will use time to time to work with MySQL database:

  • USE Databasename : This will be used to select a particular database in MySQL workarea.
  • SHOW DATABASES: Lists the databases that are accessible by the MySQL DBMS.
  • SHOW TABLES: Shows the tables in the database once a database has been selected with the use command.
  • SHOW COLUMNS FROM tablename: Shows the attributes, types of attributes, key information, whether NULL is permitted, defaults, and other information for a table.
  • SHOW INDEX FROM tablename: Presents the details of all indexes on the table, including the PRIMARY KEY.
  • SHOW TABLE STATUS LIKE tablename\G: Reports details of the MySQL DBMS performance and statistics.

 

Website

 

 

Tutorials