Mysql Multi or how to run multiple mysql instance on the same server

Pour les personnes ne parlant pas l’anglais vous trouverez une traduction du post sur
I had a question this week: how to run multiple mysql instance on the same server without running VMs ? Mysql_multi is here to help you !
In this blog post, i ll not explain how to install mysql server on Debian. It’s relatively easy to use this tutorial with others unix systems.

First step.

Stop all running mysqld service :

$ ps aux | grep mysqld
$ sudo /etc/init.d/mysql stop

Check symlinks.

Verify that program listening are correctly defined on system $PATH:
This is an example on my Debian system /usr/bin

-rwxr-xr-x 1 root root 1455912 30 nov. 2010 /usr/bin/my_print_defaults
-rwxr-xr-x 1 root root 183376 30 nov. 2010 /usr/bin/mysql
-rwxr-xr-x 1 root root 105792 30 nov. 2010 /usr/bin/mysqldump

Preparing directories.

We should create two new directories:

mkdir /var/lib/mysql1 /var/lib/mysql2
chown -R mysql:mysql /var/lib/mysql1 /var/lib/mysql2
cp -Rp /var/lib/mysql/mysql /var/lib/mysql1/
cp -Rp /var/lib/mysql/mysql /var/lib/mysql2/

Editing your configuration.

I propose to you to create a new file separately from my.cnf. Edit, with your favorite editor, (vim of course ;) ) /etc/multi_my.cnf file. (This file doesn’t exists so don’t be afraid not finding it)

mysqld = /usr/sbin/mysqld
mysqladmin = /usr/bin/mysqladmin
user = mysql
log = /var/log/mysqld_multi.log
# configuration d'un premier serveur
user = mysql
# configuration d'un second serveur
user = mysql
# configuration serveur maître

Managing instances

Really easy, just type :
sudo mysqld_multi --defaults-file=/etc/multi_my.cnf start 1,2

should start both mysql server instances. You could manage separately too :

sudo mysqld_multi --defaults-file=/etc/multi_my.cnf start 1

And to access to mysql console, there are two options. First one by ip :

mysql --host= --port=3307 -uroot

Or by socket :

mysql --socket=/var/lib/mysql2/mysql.sock2

For an obscur reason, if you replace by localhost, it ll not work.
A tip to check all mysql status :

sudo mysqld_multi --defaults-file=/etc/ report

And keep an eye on log file :

tail -f /var/log/mysqld_multi.log

To stop server 2, you should give grant privileges on mysql user and configure password, or execute via root user command line :

mysqld_multi --defaults-file=/etc/multi_my.cnf stop 2 --user=root --password=root