Running Multiple Instance of MYSQL in Same Machine

How to create single mysql server run on multiple port in a machine

Description :

 here, we are going to discuss on how can we run a multiple mysql instance on different port using single mysql version in same machine. If you not have mysql server in your local machine just follow step 1 else you can follow from step 2.

Step 1: Installing MYSQL Server:

   We need to install the mysql server in linux or windows. The below command to use to install in linux machine.

1.	sudo apt-get install mysql-server

Using this we can install latest version Mysql Server in linux machine. During installation process it prompt the root user password.

Step 2:  Setting up mysqld_multi :

In order to manage two instances effectively in same machine, we need to use  mysqld_multi . use this link to read about mysqld_multi . The mysqld_multi used to mange the mysql different group in the same machine. Now, we are going to discuss on how to create mysql group  in existing my.cnf file.

 If your system have mysql server means it have the my.cnf file in /etc/mysql/ location. In that my.cnf file we need to make required groups. In this example i am going to show you, how to create two groups and those groups to run on different port.

Group One Setting :

     Open my.cnf file in any text editor which you are like. Once opened you have the one default group [mysqld]. you should rename it to [mysqld1]. The renamed group used for the first instance configuration .   This instance must have the unique server-id . The [mysqld1] group must have the following setting,

server-id = 1

Group two Creation and Setting:

     Since we need to create second group for another instance.We should copy the first group configuration and paste with the group name of [mysqld2]. After copied you should change the following configurations

server-id           = 2
port                = 3307 #you can change the port as your required
socket              = /var/run/mysqld/mysqld_second.sock
pid-file            = /var/run/mysqld/mysqld_second.pid
datadir             = /var/lib/mysql_second
log_error           = /var/log/mysql_second/error_second.log

After setup second instance configuration must have different server-id from other group server-id and the server-id must be unique.

Now the two instance configuration done for fist instance run on 3306 port and second instance run on 3307 port.

Apart from two [mysqld1] and [mysqld2] group. we also need to create new group mysqld_multi. copy the below configuration and paste on my.cnf file.

[mysqld_multi]
mysqld     = /usr/bin/mysqld_safe
mysqladmin = /usr/bin/mysqladmin
user       = m_admin
password   = mutiuserpass

Once we finished the above configuration we need to provide the privileges for second instance and its folder.

Create Folder for second Mysql Instance:

    In the previous step we prepared the configuration file for the second MySQL instance. In that configuration file two new folders are used. The following Linux commands should be used in order to create those folders with appropriate privileges.

mkdir -p /var/lib/mysql_second
chmod --reference /var/lib/mysql /var/lib/mysql_second
chown --reference /var/lib/mysql /var/lib/mysql_second
 
mkdir -p /var/log/mysql_second
chmod --reference /var/log/mysql /var/log/mysql_second
chown --reference /var/log/mysql /var/log/mysql_second

After finish the above steps we need to add the additional securities in linux.

Additional Security settings in AppArmor:

In linux environment we need to give the some additional environment for second mysql instance. for setting /etc/apparmor.d/usr.sbin.mysqld to edit this file in your liked editor.

/var/lib/mysql_second/ r,
/var/lib/mysql_second/** rwk,
/var/log/mysql_second/ r,
/var/log/mysql_second/* rw,
/var/run/mysqld/mysqld_second.pid rw,
/var/run/mysqld/mysqld_second.sock w,
/run/mysqld/mysqld_second.pid rw,
/run/mysqld/mysqld_second.sock w,

Note : the security setting path must be  same as the path given in my.cnf socket and pid-file path.

After you save this file . you must reboot your machine to effect this changes.

Installation of second Mysql instance:

     Since we have already prepared the configuration file and the necessary folders and security changes in the previous steps, the final installation step of the second MySQL instance is the initialization of the MySQL data directory.

    Execute the following command in order to initialize new MySQL data directory:

mysql_install_db --user=mysql --datadir=/var/lib/mysql_second

Once MySQL data directory is initialized, you can start both MySQL instances using the mysqld_multi service:

mysqld_multi start

Set the root password for the second MySQL instance by using the mysqladmin with the appropriate host and port.
Note :  If host and port are not specified, mysqladmin will connect to the first MySQL instance by the default:

mysqladmin --host=127.0.0.1 --port=3307 -u root password root

Once both group started. you can test it via telnet. In example,

telnet 127.0.0.1 3306
telnet 127.0.0.1 3307

you can start one by one group also using the group id number. For example

mysqld_multi start 1

one indicate the group number we appended after mysqld.

Setting for start up both Mysql Instance on Booting time:

The final step of setting up mysqld_multi is the installation of the automatic boot script in the init.d.
To do that, create new file named mysqld_multi in /etc/init.d, and give it appropriate privileges:

cd /etc/init.d
touch mysqld_multi
chmod +x /etc/init.d/mysqld_multi

open your liked editor to paste the below codes,

#!/bin/sh

### BEGIN INIT INFO
# Provides:       scriptname
# Required-Start: $remote_fs $syslog
# Required-Stop:  $remote_fs $syslog
# Default-Start:  2 3 4 5
# Default-Stop:   0 1 6
# Short-Description: Start daemon at boot time
# Description:    Enable service provided by daemon.
### END INIT INFO
 
bindir=/usr/bin
 
if test -x $bindir/mysqld_multi
then
    mysqld_multi="$bindir/mysqld_multi";
else
    echo "Can't execute $bindir/mysqld_multi";
    exit;
fi
 
case "$1" in
    'start' )
     "$mysqld_multi" start $2
     ;;
    'stop' )
     "$mysqld_multi" stop $2
     ;;
    'report' )
     "$mysqld_multi" report $2
     ;;
    'restart' )
     "$mysqld_multi" stop $2
     "$mysqld_multi" start $2
     ;;
    *)
     echo "Usage: $0 {start|stop|report|restart}" >&2
     ;;
esac

Add mysqld_multi service to the default runlevels with the following command:

update-rc.d mysqld_multi defaults

After the you should Reboot your machine and check two instances running using the below commands,

mysqld_multi report

*** END ***