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 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=1
pid-file=/var/run/mysqld/mysqld.pid1
user = mysql
 
# configuration d'un second serveur
[mysqld2]datadir=/var/lib/mysql2
socket=/var/lib/mysql2/mysql.sock2
port=3307
old_passwords=1
pid-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

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *

*

Этот сайт использует Akismet для борьбы со спамом. Узнайте как обрабатываются ваши данные комментариев.