Running multiple MySQL instances on MacOsX

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.

  • mysql
  • mysqldump
  • my_print_defaults

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

or socket

mysql --socket=/tmp/mysql.sock2

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.

Simples!

Resources:

Leave a Comment


NOTE - You can use these HTML tags and attributes:
<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>