Online Version on Ubuntu for MySQL

MySQL is a popular database management system used for IVMS server applications. This guide will introduce how to install, configure and manage MySQL on a running Ubuntu 14.04 LTS (Trusty Tahr) using offline binary packages.

· Check the system’s hostname

To check your hostname run:
            #hostname
            #hostname –f

The first command should show your short hostname, and the second should show your fully qualified domain name (FQDN).

Before installation, identification of installed OS is required.

Check output of following command:

 #cat /etc/*-release

Typical Output:

DISTRIB_ID=Ubuntu

DISTRIB_RELEASE=14.04

DISTRIB_CODENAME=trusty

DISTRIB_DESCRIPTION="Ubuntu 14.04 LTS"

NAME="Ubuntu"

VERSION="14.04, Trusty Tahr"

ID=ubuntu

ID_LIKE=debian

PRETTY_NAME="Ubuntu 14.04 LTS"

VERSION_ID="14.04"

HOME_URL="http://www.ubuntu.com/"

SUPPORT_URL="http://help.ubuntu.com/"

BUG_REPORT_URL="http://bugs.launchpad.net/ubuntu/" 

For Checking architecture, use following command:

 # uname –m

Typical Output:

X86_64

The above output ensures that installed OS is 64 Bit.

MySQL database server is now owned by Oracle (formally Sun Microsystems) but can be installed using command line options without compiling anything under Ubuntu Linux. mysqld, also known as MySQL Server, is the main program that does most of the work in a MySQL installation. Open a terminal and type the following commands to upgrade package database:

    #sudo apt-get update

       1. Install MySQL Server

            #apt-get install  mysql-server  mysql-common  mysql-client 

Sample outputs:

Reading package lists... Done

Building dependency tree

Reading state information... Done

The following extra packages will be installed:

  libdbd-mysql-perl libdbi-perl libhtml-template-perl libmysqlclient18

  libnet-daemon-perl libplrpc-perl mysql-client-5.5 mysql-client-core-5.5

  mysql-server-5.5 mysql-server-core-5.5

Suggested packages:

  libipc-sharedcache-perl libterm-readkey-perl tinyca mailx

The following NEW packages will be installed:

  libdbd-mysql-perl libdbi-perl libhtml-template-perl libmysqlclient18

  libnet-daemon-perl libplrpc-perl mysql-client mysql-client-5.5

  mysql-client-core-5.5 mysql-common mysql-server mysql-server-5.5

  mysql-server-core-5.5

0 upgraded, 13 newly installed, 0 to remove and 0 not upgraded.

Need to get 27.3 MB of archives.

After this operation, 97.5 MB of additional disk space will be used.

Do you want to continue [Y/n]? y

Get:1 http://in.archive.ubuntu.com/ubuntu/ precise-updates/main mysql-common all 5.5.31-0ubuntu0.12.04.1 [13.4 kB]

Get:2 http://in.archive.ubuntu.com/ubuntu/ precise-updates/main libmysqlclient18 amd64 5.5.31-0ubuntu0.12.04.1 [948 kB]

Get:3 http://in.archive.ubuntu.com/ubuntu/ precise/main libnet-daemon-perl all 0.48-1 [43.1 kB]

Get:4 http://in.archive.ubuntu.com/ubuntu/ precise/main libplrpc-perl all 0.2020-2 [36.0 kB]

Get:5 http://in.archive.ubuntu.com/ubuntu/ precise/main libdbi-perl amd64 1.616-1build2 [849 kB]

Get:6 http://in.archive.ubuntu.com/ubuntu/ precise/main libdbd-mysql-perl amd64 4.020-1build2 [106 kB]

Get:7 http://in.archive.ubuntu.com/ubuntu/ precise-updates/main mysql-client-core-5.5 amd64 5.5.31-0ubuntu0.12.04.1 [1,936 kB]

Get:8 http://in.archive.ubuntu.com/ubuntu/ precise-updates/main mysql-client-5.5 amd64 5.5.31-0ubuntu0.12.04.1 [8,350 kB]

Get:9 http://in.archive.ubuntu.com/ubuntu/ precise-updates/main mysql-server-core-5.5 amd64 5.5.31-0ubuntu0.12.04.1 [6,085 kB]

Get:10 http://in.archive.ubuntu.com/ubuntu/ precise-updates/main mysql-server-5.5 amd64 5.5.31-0ubuntu0.12.04.1 [8,877 kB]

Get:11 http://in.archive.ubuntu.com/ubuntu/ precise/main libhtml-template-perl all 2.10-1 [65.0 kB]

Get:12 http://in.archive.ubuntu.com/ubuntu/ precise-updates/main mysql-client all 5.5.31-0ubuntu0.12.04.1 [11.5 kB]

Get:13 http://in.archive.ubuntu.com/ubuntu/ precise-updates/main mysql-server all 5.5.31-0ubuntu0.12.04.1 [11.6 kB]

Fetched 27.3 MB in 1min 6s (411 kB/s)

Preconfiguring packages ...

Selecting previously unselected package mysql-common.

(Reading database ... 222510 files and directories currently installed.)

Unpacking mysql-common (from .../mysql-common_5.5.31-0ubuntu0.12.04.1_all.deb) ...

Selecting previously unselected package libmysqlclient18.

Unpacking libmysqlclient18 (from .../libmysqlclient18_5.5.31-0ubuntu0.12.04.1_amd64.deb) ...

Selecting previously unselected package libnet-daemon-perl.

Unpacking libnet-daemon-perl (from .../libnet-daemon-perl_0.48-1_all.deb) ...

Selecting previously unselected package libplrpc-perl.

Unpacking libplrpc-perl (from .../libplrpc-perl_0.2020-2_all.deb) ...

Selecting previously unselected package libdbi-perl.

Unpacking libdbi-perl (from .../libdbi-perl_1.616-1build2_amd64.deb) ...

Selecting previously unselected package libdbd-mysql-perl.

Unpacking libdbd-mysql-perl (from .../libdbd-mysql-perl_4.020-1build2_amd64.deb) ...

Selecting previously unselected package mysql-client-core-5.5.

Unpacking mysql-client-core-5.5 (from .../mysql-client-core-5.5_5.5.31-0ubuntu0.12.04.1_amd64.deb) ...

Selecting previously unselected package mysql-client-5.5.

Unpacking mysql-client-5.5 (from .../mysql-client-5.5_5.5.31-0ubuntu0.12.04.1_amd64.deb) ...

Selecting previously unselected package mysql-server-core-5.5.

Unpacking mysql-server-core-5.5 (from .../mysql-server-core-5.5_5.5.31-0ubuntu0.12.04.1_amd64.deb) ...

Processing triggers for man-db ...

Setting up mysql-common (5.5.31-0ubuntu0.12.04.1) ...

Selecting previously unselected package mysql-server-5.5.

(Reading database ... 222875 files and directories currently installed.)

Unpacking mysql-server-5.5 (from .../mysql-server-5.5_5.5.31-0ubuntu0.12.04.1_amd64.deb) ...

Selecting previously unselected package libhtml-template-perl.

Unpacking libhtml-template-perl (from .../libhtml-template-perl_2.10-1_all.deb) ...

Selecting previously unselected package mysql-client.

Unpacking mysql-client (from .../mysql-client_5.5.31-0ubuntu0.12.04.1_all.deb) ...

Selecting previously unselected package mysql-server.

Unpacking mysql-server (from .../mysql-server_5.5.31-0ubuntu0.12.04.1_all.deb) ...

Processing triggers for ureadahead ...

ureadahead will be reprofiled on next reboot

Processing triggers for man-db ...

Setting up libmysqlclient18 (5.5.31-0ubuntu0.12.04.1) ...

Setting up libnet-daemon-perl (0.48-1) ...

Setting up libplrpc-perl (0.2020-2) ...

Setting up libdbi-perl (1.616-1build2) ...

Setting up libdbd-mysql-perl (4.020-1build2) ...

Setting up mysql-client-core-5.5 (5.5.31-0ubuntu0.12.04.1) ...

Setting up mysql-client-5.5 (5.5.31-0ubuntu0.12.04.1) ...

Setting up mysql-server-core-5.5 (5.5.31-0ubuntu0.12.04.1) ...

Setting up mysql-server-5.5 (5.5.31-0ubuntu0.12.04.1) ...

mysql start/running, process 9683

Setting up libhtml-template-perl (2.10-1) ...

Setting up mysql-client (5.5.31-0ubuntu0.12.04.1) ...

Setting up mysql-server (5.5.31-0ubuntu0.12.04.1) ...

Processing triggers for libc-bin ...

ldconfig deferred processing now taking place

During the installation process, you will be prompted to set a password for the MySQL root user as shown below. Choose a strong password and keep it in a safe place for future reference.

After reboot and auto-login open terminal ( ALT +CTRL +T or from menu )

 To check whether MySQL is installed correctly, run the following command

            netstat –tap | grep mysql

Typical output : tcp        0          0          localhost:mysql *.*         LISTEN 2173/mysql

Where 2173 is the process id. It will differ in your case.

This will confirm MySQL Server is running and operational.

Configuration of MySQL Server:
The standard tool for interacting with MySQL is the mysql client which installs with the mysql-server package. The MySQL client is accessed through a terminal.

After completion of the above steps, please configure the MySQL server as follows:

To log in to MySQL as the root user: 

# mysql –u root –p

When prompted, enter the root password you assigned during installation.

You’ll then be presented with the MySQL monitor prompt:

To generate a list of commands for the MySQL prompt, enter \h. You’ll then see:

To display which accounts exist in the mysql.user table and check whether their passwords are empty, use the following statement:
 
 mysql> SELECT User, Host, Password FROM mysql.user;

Sample output as follows: 

+------+--------------------+----------+

| User | Host               | Password |

+------+--------------------+----------+

| root | localhost          |          |

| root | myhost.example.com |          |

| root | 127.0.0.1          |          |

|      | localhost          |          |

|      | myhost.example.com |          |

+------+--------------------+----------+

mysql>\q

Assigning root Account Passwords 

· Open Terminal

#mysql -u root -p

When prompted, enter the root password you assigned during installation step.

mysql> UPDATE mysql.user SET Password = PASSWORD('root') WHERE User = 'root';

mysql> FLUSH PRIVILEGES;

The FLUSH statement causes the server to reread the grant tables. Without it, the password change remains unnoticed by the server until you restart it. 

Logout from database :

mysql> \q

To allow connection from remote host to newly created database, username do the following in server side.

· Open Terminal

#mysql -u root -p

When prompted, enter the root password you assigned during installation.

mysql>GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'root';

mysql>FLUSH PRIVILEGES;

Viewing  MySQL log files
 

The log is stored at the file location /var/log/mysql/error.log, enter:
 
 

# tail -f /var/log/mysql/error.log
 
 Sample output:

130511 13:36:51 InnoDB: Completed initialization of buffer pool

130511 13:36:51 InnoDB: highest supported file format is Barracuda.

130511 13:36:52  InnoDB: Waiting for the background threads to start

130511 13:36:53 InnoDB: 5.5.31 started; log sequence number 1595675

130511 13:36:53 [Note] Server hostname (bind-address): '127.0.0.1'; port: 3306

130511 13:36:53 [Note]   - '127.0.0.1' resolves to '127.0.0.1';

130511 13:36:53 [Note] Server socket created on IP: '127.0.0.1'.

130511 13:36:53 [Note] Event Scheduler: Loaded 0 events

130511 13:36:53 [Note] /usr/sbin/mysqld: ready for connections.

Version: '5.5.31-0ubuntu0.12.04.1'  socket: '/var/run/mysqld/mysqld.sock'  port: 3306  (Ubuntu)

Start / stop / restart the MySQL database server using command line tools?

Type the following commands:
$ sudo service mysql restart
$ sudo service mysql stop
$ sudo service mysql start
Sample outputs:
mysql start/running, process 4930

 You can also use the following command for older version:
 
 $ sudo /etc/init.d/mysql start
 $ sudo /etc/init.d/mysql stop
 $ sudo /etc/init.d/mysql restart 

Secure MySQL Server
 

Now that you have setup MySQL on Ubuntu Server, let us secure it. Securing your MySQL database server is highly recommended and it is very easy to do. Just run the following command and follow through the 6 steps. 

                               #sudo mysql_secure_installation

 As shown in the picture below, first you will be asked to enter the current root password to continue. Then you will have the option to change the root password. You can say “n” if you don’t want to change it. Then say “Y” to “Remove Anonymous Users”, “n” to “Disallow root login remote”, and “n” to “Remove test database and access to it”. And finally, answer “Y” to reload the new privileges.

That’s it, you have secured your MySQL server.  

This is the end of configuration section. 

MySQL is one of the most used database worldwide. To manage MySQL database there are lot of open source GUI tools are available. Some of the popular GUI based tools is MySQL workbench, phpMyAdmin etc. 

MySQL Workbench is a GUI based visual tool. Workbench is widely used by developers,  DBAs for data modeling, SQL development, and comprehensive administration tools for server configuration, user administration, backup, and much more. This tool is available for Windows, Linux and Mac OS operating system.
 
 In this tutorial we will show how to install MySQL workbench on Ubuntu 14.04. 

Install MySQL Workbench

· Open Terminal

                    #apt-get install mysql-workbench
 It will install mysql-workbench and dependent packages in your system.

Restore your database from a previous backup

• Enter your  database password when prompted.

NOTE: If you select Save password in vault MySQL Workbench will not ask you to enter your password again. You should never use this option on a public or shared computer.

Click Data Import / Restore under Management section

Select .sql file which will be different depending upon your application category(Pro VMS, ANPR, RLVD etc.)

As an example: 

Application Name

Schema file

IVMS Pro

ivms_30.sql

 Click Start Import and enter the database password if prompted. This process may take several minutes depending on the size of your database backup.

Trouble Shooting :

·  Change the MySQL Root Password

This method exposes the password to the command-line history, these commands should be run as root.

1. Login through mysql command line tool:

# mysql –uroot –poldpassword

2. Run this command:

mysql>SET PASSWORD FOR root@'localhost' = PASSWORD('newpassword');

or

mysql>SET PASSWORD = PASSWORD('newpassword');

·  Reset the MySQL Root Password  [ Procedure 1 ]

               If you forget your root MySQL password, it can be reset.
1. Stop the current MySQL server instance.
                              #sudo  service  mysql  stop

                                              Or

                               #sudo /etc/init.d/mysql stop

     2. Start the mysqld configuration with below command: 

#sudo mysqld --skip-grant-tables &

     3. Login to MySQL as root 

#mysql -u root mysql

                After successful  execution , mysql prompt  will appear

mysql>

               4.   Replace YOURNEWPASSWORD with your new password

               mysql>UPDATE user SET Password=PASSWORD('YOURNEWPASSWORD') WHERE User='root';

mysql> FLUSH PRIVILEGES;

mysql> exit;

·  Reset the MySQL Root Password  [ Procedure 2 ]

               Step 1: Stop MySQL Service.

#sudo stop mysql

               Step 2: Kill all running mysqld.

#sudo killall -9 mysqld

               Step 3: Starting mysqld in Safe mode.

#sudo mysqld_safe --skip-grant-tables --skip-networking &

               Step 4: Start mysql client

#mysql -u root

               Step 5: After successful login, please execute this command to change any password.

mysql>FLUSH PRIVILEGES;

               Step 6: You can update mysql root password .

mysql>UPDATE mysql.user SET Password=PASSWORD('newpwd') WHERE User='root';

Step 7: Please execute this command.

mysql>FLUSH PRIVILEGES;

·  Database ErrorHost “domain.com' is blocked because of many connection errors; unblock with 'mysqladmin flush-hosts'

               Sometimes, user can see above log messages during debugging. To resolve this unblock issue, do the below exercise:

               Linux:

                               # mysql -u root -p -e 'flush hosts'

     Windows:

                               mysql> SET GLOBAL max_connect_errors=10000;

How to uninstall mysql completely in ubuntu [ optional ]

1. open terminal

2. Execute the following commands sequentially

               #apt-get remove - - purge mysql-server mysql-client mysql-common

             #apt-get autoremove

               #apt-get autoclean

How to move the mysql data directory in ubuntu [ Optional ]

• Open the terminal
• Stop MySQL with  the command

/etc/init.d/mysql stop

• Copy the existing  data directory (which is located in /var/lib/mysql)  using the command 

cp -R -p /var/lib/mysql /opt/new_datadir

• All you need are  the data files. Delete the others with the command 

rm /opt/new_datadir

(You will get a message about not being able to delete some directories, but do not care about them)

• Edit the MySQL  configuration file with the command 

nano /etc/mysql/my.cnf

• Find the entry for datadir, change the path to the new data directory.
• But there is a trick involved here. Ubuntu  uses some security software called AppArmor that  specifies the areas of your filesystem applications are allowed to access.  Unless you modify the AppArmor profile for MySQL, you'll never be able to  restart MySQL with the new datadir location. 
• In the terminal,  enter the command 

nano /etc/apparmor.d/usr.sbin.mysqld

• Copy the lines beginning with /var/lib/mysql
• Comment out the originals with hash marks  (#), and  paste the lines below the originals.
• Now change /var/lib/mysql in the two new lines  with /opt/new_datadir. 
• Save and close the file.
• Restart the AppArmor profiles with the command
   /etc/init.d/apparmor restart
• Restart MySQL with  the command
   /etc/init.d/mysql restart

MySQL should now start without any errors and your data will be stored in the new location.
 
 Backup Database in MySQL Server 

Backups are important with any kind of data, and this is especially relevant when talking about databases. MySQL can be backed up in a few different ways that we will discuss one of the popular method where we will use utility program  “automysqlbackup” that is available in the ubuntu repositories.

This utility can be scheduled to automatically perform backup at regular intervals

Below steps will explain the procedure to configure step by step 

Step 1: Create  the directory “automysqlbackup” under /etc

#mkdir –p /etc/automysqlbackup

Step 2: Copy configuration files to the proper destination directory

#cp Prerequisite Server/automysqlbackup/automysqlbackup.conf \

 /etc/automysqlbackup/myserver.conf

#cp Prerequisite Server/automysqlbackup/automysqlbackup /usr/local/bin

Step 3: Set executable permission to the required file

#chmod a+x  /usr/local/bin/automysqlbackup

Step 4: Edit the configuration file with proper database name, database username , database password, backup directory location, port etc. 

 #gedit /etc/automysqlbackup/myserver.conf

Edit the below few lines to meet your requirement 

CONFIG_mysql_dump_username='root' CONFIG_mysql_dump_password='root' CONFIG_backup_dir='/opt/database-backup'

CONFIG_db_names=('ivms_30')   # might be different in your  installation

CONFIG_mysql_dump_port=3306

Step 5: Create  a folder  /opt/database-backup . This is the backup file location . We recommend to set this in NAS.

#mkdir /opt/database-backup

For NAS, the above command will be like this

#mkdir –p /mnt/nas1/DB-Backup [ Assuming NAS path : /mnt/nas1/DB-Backup ]

Step 6: Run the below command once from command line for the first time. 

#/usr/local/bin/automysqlbackup /etc/automysqlbackup/myserver.conf

This will create all necessary meaningful folders/subfolders and files.

Search this directory “/opt/database-backup” to see the structure of the backup folders/files.

Step 7:  Now to run this script periodically you have to add this CRONTAB                 

(i) Open  Terminal

(ii) Open crontab
 #crontab –e 

[ It will ask what  editor should you use (First time) , Please choose   nano  editor .
 It’s easiest] It will open  a window.

 Add  the following entry :

# m h  dom mon dow  command

30 5  *  *  * /usr/local/bin/automysqlbackup  /etc/automysqlbackup/myserver.conf

This backup command execute everyday at 5:30 morning

If you look into the daily directory, you can see a subdirectory for each database, inside of which is a gzipped sql dump. Example  of such a file is “daily_centraldb_2015-12-01_05h30m_Tuesday.sql.gz “.

Restore Database in MySQL Server


 Restoration of database is equally important . Here is the explanation of restoration process with Snapshots.

We are taking example of the gzipped schema from the previous page.

Step 1: GUnzip the zipped sql file

            #gunzip  daily_centraldb_2015-12-01_05h30m_Tuesday.sql.gz

 Below sql file will be available in the same directory ( Eg: /root ]

            “ daily_centraldb_2015-12-01_05h30m_Tuesday.sql”

Step 2: Open Mysql Workbench:  

Connect MySQL service . During this operation, root password is required.


Click on Data Import /Restore

Browse the unzipped .sql file from explorer.

The name will be different for various product. ( Ex: for ivms rich version database name: ivms_20)

Create a new database by clicking button “new” as marked in the below screen. 

After creating new database name, select the database from drop down menu. Then Click on “Start Import”

After successful importing, below screen will appear.

Click on Refresh button

Check table data .

Crosscheck database name, username and password with the package which you want to run.