This site has been archived and you can no longer log in or post new messages. For up-to-date community resources please visit ezplatform.com

eZ Community » Blogs » Philippe VINCENT-ROYOL » Mysql Multi or how to run multiple...

By

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

Tuesday 25 October 2011 10:46:38 am

  • Currently 4 out of 5 Stars.
  • 1
  • 2
  • 3
  • 4
  • 5

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

Proudly Developed with from