Recently I had to look at a tool developed in-house to archive data from a main db cluster to an archive cluster. As I had to implement a brand new feature I wanted to test it first on my machine and cover it fully with unit tests, but to do so I obviously needed 2 MySQL instances running on the same machine – one working as a source db and another that I would be archiving the data to.
I’ve already had MySQL installed from a dmg archive (version 5.1.52) running on localhost on port 3306 and as the installed sets it up system-wise I couldn’t just install another one running on a different port. So I looked at one of the MySQL features allowing to run its multiple instances on one machine. And I must say it works really nice and the setup process is really simple.
Set up global symlinks
First of all make sure that the following binaries are in your system PATH.
I have simply simlinked them in /usr/bin directory:
lrwxr-xr-x 1 root wheel 26 26 Nov 08:17 mysql -> /usr/local/mysql/bin/mysql lrwxr-xr-x 1 root wheel 30 9 Mar 11:08 mysqldump -> /usr/local/mysql/bin/mysqldump lrwxr-xr-x 1 root wheel 38 18 May 13:12 my_print_defaults -> /usr/local/mysql/bin/my_print_defaults
Setting up separate data directory
The second MySQL instance will store data in a separate data directory so you have to set it up first. Simply use mysql_install_db script to set it up. I have decided to store them in /usr/local/mysql/data2
sudo /usr/local/mysql/scripts/mysql_install_db --user=_mysql --basedir=/usr/local/mysql/ --datadir=/usr/local/mysql/data2
MySQL on MacOSx is run by default as _mysql, that’s why I have used –user option when I run the script to set up permissions in a similar way.
Set up multiple instances in my.cnf file
The last thing that I had to do was to configure my instances in my.cnf file which I have placed in /etc folder. Here’s my configuration:
[mysqld_multi] mysqld = /usr/local/mysql/bin/mysqld_safe mysqladmin = /usr/local/mysql/bin/mysqladmin user = root [mysqld1] socket = /tmp/mysql.sock port = 3306 pid-file = /usr/local/mysql/data/mysqld1.pid datadir = /usr/local/mysql/data language = /usr/local/mysql/share/english user = _mysql [mysqld2] socket = /tmp/mysql.sock2 port = 3307 pid-file = /usr/local/mysql/data2/mysqld2.pid datadir = /usr/local/mysql/data2 language = /usr/local/mysql/share/english user = _mysql
Every instance is configured in its own section, where you can configure the port you want the instance to be running on and where is the instance data directory.
Managing and accessing multiple instances
To start or stop an instance use mysqld_multi tool and specify the instance number (it matches the number specified in my.cnf file).
sudo /usr/local/mysql/bin/mysqld_multi start 1 sudo /usr/local/mysql/bin/mysqld_multi stop 2
To access mutliple instances you can either connect via IP address:
mysql --host=127.0.0.1 --port=3307 -uroot
For some weird reason to access second instance via IP running locally I had to use address 127.0.0.1 and not localhost name, otherwise the client always connected to the main instance running on port 3306.