Running multiple instances of MySQL

It’s reasonably easy to run multiple instances of MySQL with the mysqld_multi bash script. This can be really useful in development environments where you need to give several developers their own instance. To install multiple Microsoft SQL Server instances we have to get the install DVD and go through a laborious series of wizards. MySQL makes this easy with a few configuration files changes. In this example I’m going to outline how to configure 2 instances of MySQL by cloning an existing single instance.
Login to your existing mysql instance and run the below statement to create a user.

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

The mysqld_multi script needs this user, on each instance, to function correctly. This user only needs the SHUTDOWN privilege.
If you already have an instance of MySQL running then you’ll want to shut it down before beginning. You can do this at the command prompt with…

sudo /sbin/service mysql stop

Next we need to edit the my.cnf file.

First we need to comment out a few lines in the mysqld 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

Any other configuration options you leave in the mysqld section will serve as defaults for all MySQL instances. I’m keeping things simple here but, for production environments, you’ll want to pay more attention to these settings. I’m particulary thinking of memory usage for each instance.

# 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

Add the lines below replacing the username and password values the multi_admin user you created earlier. The mysqld line tells the mysqld_multi script the MySQL binary to use.

[mysqld_multi]
mysqld     = /usr/bin/mysqld_safe # location MySQL binary
mysqladmin = /usr/bin/mysqladmin
log        = /var/log/mysqld_multi.log
user       = multi_admin
password   = secret

Add the below line to create 2 instances. It is critical that each instance has its own unique values or the 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

Next create all the required directories on your system.

rhys@linux-n0sm:~> sudo mkdir /var/lib/mysql-databases/
rhys@linux-n0sm:~> sudo mkdir /var/lib/mysql-databases/mysqld2

Create a directory for the mysql database for instance 2.

rhys@linux-n0sm:~> sudo mkdir /var/lib/mysql-databases/mysql

Copy the mysql database files from the original instance to the second instances database directory. Then this instance will have all of the same users as instance 1.

sudo cp -r /var/lib/mysql/mysql/ /var/lib/mysql-databases/mysqld2/mysql

You can also copy across any other databases you want the new server to host. Change the owner of the data directory to the mysql user so the instance can read them.

sudo chown -R mysql:mysql /var/lib/mysql-databases

Finally we are ready to start up the instances.

rhys@linux-n0sm:~> mysqld_multi start

To check both instances have started correctly execute the below command.

rhys@linux-n0sm:~> mysqld_multi report
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"
10779 pts/2    00:00:00 mysqld_safe
11002 pts/2    00:00:00 mysqld
11166 pts/2    00:00:00 mysqld_safe
11279 pts/2    00:00:00 mysqld

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
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

Check the manual for more details on mysqld_multi.


4 Comments

  1. Roberto says:

    Very good.
    I need a similar configuration, but to 2 MySQL instances, dif versions as MySQL5.0 and MySQL 5.1. I am trying on dirs /usr/local/mysql50 and /usr/local/mysql51 but with errors yet… do you have a tuto to this other especific case ? I continue testing other ways, i will post later… (my case is Fedora Linux 13).

  2. Rhys says:

    Hi Roberto,

    No I don’t. What errors are you getting? Different versions should be ok in theory.

    Cheers,

    Rhys

  3. Naresh Yannam says:

    Cheers !

    I have followed the step & completed the task…thanxxxxx
    Can u followed me the link as to how to do replicaiton of (Master &
    Slave) two mysql in single machine.

  4. Rhys says:

    Hi Naresh,

    There’s a guide here….

    http://www.howtoforge.com/one_machine_mysql_replication

    Cheers,

    Rhys

Leave a Reply

Current day month ye@r *