Mysql Multi or how to run multiple mysql instance on the same server
Tuesday 25 October 2011 10:46:38 am
Pour les personnes ne parlant pas l'anglais vous trouverez une traduction du post sur http://www.pheelit.fr/HighTech/Developpement/mysql_multi-ou-comment-avoir-plusieurs-serveurs-mysql-sur-la-meme-machine
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:
- mysql
- mysqldump
- my_print_defaults
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_multi] mysqld = /usr/sbin/mysqld mysqladmin = /usr/bin/mysqladmin user = mysql log = /var/log/mysqld_multi.log # configuration d'un premier serveur [mysqld1]datadir=/var/lib/mysql1 socket=/var/lib/mysql1/mysql.sock1 port=3306 old_passwords=1pid-file=/var/run/mysqld/mysqld.pid1user = mysql # configuration d'un second serveur [mysqld2]datadir=/var/lib/mysql2 socket=/var/lib/mysql2/mysql.sock2 port=3307 old_passwords=1pid-file=/var/run/mysqld/mysqld.pid2 user = mysql # configuration serveur maître [mysql.server] user=mysql basedir=/var/lib
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=127.0.0.1 --port=3307 -uroot
Or by socket :
mysql --socket=/var/lib/mysql2/mysql.sock2
For an obscur reason, if you replace 127.0.0.1 by localhost, it ll not work.
A tip to check all mysql status :
sudo mysqld_multi --defaults-file=/etc/multi.my.cnf 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
Cheers