Home > Database > How To Run Multiple instances of Mysql Server on a Single Linux Server

How To Run Multiple instances of Mysql Server on a Single Linux Server

Yesterday I faced a problem where our developer team wanted to have two mysql server with different root passwords.The problem was I am having only one redhat linux test server.After lots of thinking I searched for “multiple instances of mysql server” in Google.I found lots of different article on this and all are little bit confusing.Finally after lots of testing I succeeded in getting up and running two different instances of mysql in singleserver.Below are the steps you can follow to do the same.

Step-1:Login to your server as root user

Step-2:Login to your mysql server as root and execute the following command


mysql>GRANT SHUTDOWN ON *.* TO 'multi_admin'@'localhost' IDENTIFIED BY 'secret';
mysql>FLUSH PRIVILEGES;

It means we are giving shutdown privileges to the user “multi_admin”

step-3:come out of mysql prompt and stop mysql server.To stop you can execute

[root@localhost ~]#	service mysql stop
or
[root@localhost ~]#/sbin/service mysql stop

Step-4:Now we need to locate the mysql config file “my.cnf” and change it as per our requirement which is located at /etc/my.cnf

N:B:-If you are not finding the my.cnf file then go to your mysql installation folder.In my case it is /usr/share/mysql.You will find four configuration files like “my-small.cnf”,” my-medium.cnf”,” my-large.cnf “,” my-huge.cnf”.You can take any one and put it in /etc and rename it to my.cnf.

You can also execute the command below to get all of the above file.

[root@localhost ~]# find / -name mysql*.cnf

Step-5:open my.cnf and comment out the following lines in [mysql] section

# The MySQL server
[mysqld]
#port           = 3306
#socket         = /var/run/mysql/mysql.sock
# Change following line if you want to store your database elsewhere
#datadir        = /var/lib/mysql

Step-6:Now just below [mysqld] section put the following lines

[mysqld_multi]
mysqld     = /usr/bin/mysqld_safe
mysqladmin = /usr/bin/mysqladmin
log        = /var/log/mysqld_multi.log
user       = multi_admin
password   = admin123

Step -7:Then to create our desired two instances add the below lines after [mysql_multi] section. We have to define different unique values for each server instance or else the data and socket files for both servers collide and as a result mysql server will fail to start or your data could be corrupted.

[mysqld1]
port       = 3306
datadir    = /var/lib/mysql
pid-file   = /var/lib/mysql/mysqld.pid
socket     = /var/lib/mysql/mysql.sock
user       = mysql
log-error  = /var/log/mysql1.err

[mysqld2]
port       = 3307
datadir    = /var/lib/mysql-databases/mysqld2
pid-file   = /var/lib/mysql-databases/mysqld2/mysql.pid
socket     = /var/lib/mysql-databases/mysqld2/mysql.sock
user       = mysql
log-error  = /var/log/mysql2.err

Step-8:Save the configuration file and now create the files and folders as we have mentioned in the above configuration.To do that execute the following commands.

[root@localhost ~]#  mkdir -P /var/lib/mysql-databases/myqld2

Step-9:For mysql instance 1 we are using the defaults for previously running mysql server But we need to Create the data directory for instance2.Create it by

[root@localhost ~]# mkdir /var/lib/mysql-databases/mysql

Step-10:Copy the mysql database files from the original instance to the second instances database directory and change the ownership of the data directory to the mysql user so the instance can read them.

[root@localhost ~]# cp -r /var/lib/mysql/mysql/ /var/lib/mysql-databases/mysqld2/mysql
[root@localhost ~]# chown -R mysql:mysql /var/lib/mysql-databases

Step-11:Now the two instances are ready to run.We can start them by the folowing command

[root@localhost ~]#mysqld_multi start
To view the status of the instances you can run
[root@localhost ~]# mysqld_multi report
Output:
Reporting MySQL servers
MySQL server from group: mysqld1 is running
MySQL server from group: mysqld2 is running

You can see that the mysqld_multi script has started multiple mysql processes with the following commands.

ps -e | grep "mysql"

To stop both instances just execute the below command.

rhys@linux-n0sm:~> mysqld_multi stop

We are also able to control individual instances by referring to the assigned number.

rhys@linux-n0sm:~> mysqld_multi stop 1

To verify this
rhys@linux-n0sm:~> mysqld_multi report

Reporting MySQL servers
MySQL server from group: mysqld1 is not running
MySQL server from group: mysqld2 is running

rhys@linux-n0sm:~> mysqld_multi start 1
rhys@linux-n0sm:~> mysqld_multi report
Reporting MySQL servers
MySQL server from group: mysqld1 is running
MySQL server from group: mysqld2 is running

Here you need to remember that both the instances running on different port and also having different socket files.You can refer to the topic how to connect to mysql in php in case you are facing any problem to connect ot mysql through some script

Advertisements
  1. ani
    November 8, 2010 at 7:20 pm

    Hi Kirti Ranjan Parida,
    we have 3 servers.i setup replication.In slave box i create 3 instances.i am login mysql

    mysql -uroot -S /var/lib/mysql/mysql.sock -ppassword

    mysql -uroot -S /var/lib/mysql/mysql.sock1 -ppassword

    it is working fine.

    if suppose i give

    mysql -uroot -ppassword –port=3306

    mysql -uroot -ppassword –port=3307

    it is not working,every time it connect to the 3306 port only.By using sock file only i connect to the server.

    i get new requirement,”from slave box to new box set up the replication”. I will create multiple instance in new box.how to communicate the slave box to the new box..
    please help me.

  2. January 27, 2011 at 10:35 am

    Hi,
    Thank you for your howto but I have some problems when I tried to run the multiple instances:
    in the log file /var/log/mysql1.err of every instance:
    […]
    [ERROR] Can’t open the mysql.plugin table
    […]
    [ERROR] Fatal error: Can’t open and lock privilege tables: Can’t find file: ‘./mysql/host.frm’ (errno: 13)

    I already copied mysql database files from the original one:
    cp -r /var/lib/mysql/mysql/ /var/lib/mysql-databases/mysqld1/

    and the files exist into the /var/lib/mysql-databases/mysqld1/ folder!
    ls -al /var/lib/mysql-databases/mysqld1/mysql
    […]
    -rw-r—– 1 root root 9510 Jan 27 12:44 host.frm
    -rw-r—– 1 root root 0 Jan 27 12:44 host.MYD
    -rw-r—– 1 root root 2048 Jan 27 12:44 host.MYI
    […]
    -rw-r—– 1 root root 8586 Jan 27 12:44 plugin.frm
    -rw-r—– 1 root root 0 Jan 27 12:44 plugin.MYD
    -rw-r—– 1 root root 1024 Jan 27 12:44 plugin.MYI

    And it’s the same content than into the original folder /var/lib/mysql/mysql/:
    ls -al /var/lib/mysql/mysql/
    […]
    -rw-rw—- 1 mysql mysql 9510 Jan 14 01:55 host.frm
    -rw-rw—- 1 mysql mysql 0 Jan 14 01:55 host.MYD
    -rw-rw—- 1 mysql mysql 2048 Jan 14 01:55 host.MYI
    […]
    -rw-rw—- 1 mysql mysql 8586 Jan 14 01:55 plugin.frm
    -rw-rw—- 1 mysql mysql 0 Jan 14 01:55 plugin.MYD
    -rw-rw—- 1 mysql mysql 1024 Jan 14 01:55 plugin.MYI

    Here is the content of my my.cnf:
    [mysqld_multi]
    mysqld = /usr/bin/mysqld_safe
    mysqladmin = /usr/bin/mysqladmin
    log = /var/log/mysqld_multi.log
    user = multi_admin
    password = multi_admin_pass

    [mysqld0]
    port = 3306
    datadir = /var/lib/mysql
    pid-file = /var/lib/mysql/mysqld.pid
    socket = /var/lib/mysql/mysql.sock
    user = mysql
    log-error = /var/log/mysql0.err

    [mysqld1]
    port = 3307
    datadir = /var/lib/mysql-databases/mysqld1
    pid-file = /var/lib/mysql-databases/mysqld1/mysql.pid
    socket = /var/lib/mysql-databases/mysqld1/mysql.sock
    user = mysql
    log-error = /var/log/mysql1.err

    [mysqld2]
    port = 3308
    datadir = /var/lib/mysql-databases/mysqld2
    pid-file = /var/lib/mysql-databases/mysqld2/mysql.pid
    socket = /var/lib/mysql-databases/mysqld2/mysql.sock
    user = mysql
    log-error = /var/log/mysql2.err

    [mysqld3]
    port = 3309
    datadir = /var/lib/mysql-databases/mysqld3
    pid-file = /var/lib/mysql-databases/mysqld3/mysql.pid
    socket = /var/lib/mysql-databases/mysqld3/mysql.sock
    user = mysql
    log-error = /var/log/mysql3.err

    After starting the mysql_multi:
    Reporting MySQL servers
    MySQL server from group: mysqld0 is running
    MySQL server from group: mysqld1 is not running
    MySQL server from group: mysqld2 is not running
    MySQL server from group: mysqld3 is not running

    Maybe something obvious for me but not for me?
    Any help is welcome!

    Yoong

    PS: I have another question. How can I use PhpMyAdmin? How to select the instance of Mysql to access to databases?

    • September 28, 2012 at 2:23 pm

      Hello Yoong,

      The problem you are mentioing is coming from Apparmor, you need to add some line in apparmor config file to fix this issue, Actually its not an issue, apparmor is doing his job to prevent access of mysql data directory

      pls follow below steps

      Step 1: Open apparmor config file (I am using Ubuntu 12.04 Server LTS OS)

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

      Step 2 : add below line,

      /var/lib/mysql-databases/mysqld1/ r,
      /var/lib/mysql-databases/mysqld1/** rwk,
      /var/lib/mysql-databases/mysqld2/ r,
      /var/lib/mysql-databases/mysqld2/** rwk,
      /var/lib/mysql-databases/mysqld3/ r,
      /var/lib/mysql-databases/mysqld3/** rwk,

      Step 3, save and exit the editor

      Step 4 : reload Apparmor
      /etc/init.d/apparmor reload

      Step 5 : Strat multi mysql process
      mysqld_multi start

      Step 6 : Check
      mysqld_multi report

      Thanks

  3. tobi468
    June 7, 2011 at 1:58 pm

    @yoong,

    maybe you have just copy / paste this command:

    mkdir -P /var/lib/mysql-databases/myqld2

    it must be mkdir -p and not -P

  4. August 28, 2011 at 1:28 am

    nice post man..will it work with different instances of mysql ?? or to be more simple when we have two versions of mysql

  5. elalendil
    January 12, 2012 at 7:59 pm

    It works perfectly for me, i’m gonna setup replication for each instance, so i’ll leave feedback.

  6. February 26, 2012 at 9:33 pm

    Thanks mate…great help, need a minor amendment:

    [root@localhost ~]# cp -r /var/lib/mysql/mysql/ /var/lib/mysql-databases/mysqld2/mysql

    Should be:

    [root@localhost ~]# cp -r /var/lib/mysql/mysql/ /var/lib/mysql-databases/mysqld2

    I think 🙂

    — Anish Sneh

  7. June 1, 2012 at 1:15 pm

    Realy helpfull post..

    Thanks,
    Prem singh.

  8. jeevantha
    July 14, 2012 at 10:50 pm

    can someone plz tell me how to work this out in windows

  9. September 28, 2012 at 2:25 pm

    Hello Kirti,

    Very nice post, i suggest you to add Apparmor config setting in this post to make this post rock-solid.

    Pls read my reply on user “Yoong”, to know what you forget to add in your post.

    Regards

  10. Cicakpoyo
    November 17, 2012 at 2:26 pm

    Hi everyone,

    i already follow all the step and everything work. but i dont know how to do i connect to database. can someone tell me how or give a link for this tutorial. thanks

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: