Running multiple instances of mysql on the same machine

There are a lot of ways to run two instances of mysqld on one machine. Generally all you need to do is specify different ports and data directories. The method I describe below is basically to copy the default configuration shipped with Ubuntu Server into a different directory and name everything associated with the second instance “mysql-2”, creating new directories, and startup scripts with that name.

These commands create the directories needed:

mkdir /var/lib/mysql-2
mkdir /var/log/mysql-2
mkdir /var/run/mysqld-2
mkdir /etc/mysql-2
chown mysql.mysql /var/lib/mysql-2
chown mysql.mysql /var/log/mysql-2
chown mysql.mysql /var/run/mysql-2
cp -R /etc/mysql/* /etc/mysql-2

Now edit the /etc/mysql-2/my.cnf file and change all references of /mysql/ to /mysql-2/ (also /var/run/mysqld). Change the socket for the client and server to a different port (e.g. 3307).

By default, Ubuntu Server configures AppArmor to deny the mysql user access to anything it doesn’t need. So you’ll need to edit the /etc/apparmor.d/usr.sbin.mysqld file to allow it. Basically, just make a copy of each line referring to any of the directories above and add a “-2”.

Now, initialize the database (or you could just copy the contents of a different instance’s files to the data directory):

mysql_install_db --user=mysql --datadir=/var/lib/mysql-2

Next, copy the startup script:

cd /etc/init.d
cp mysql mysql-2

Edit the /etc/init.d/mysql-2 file. First change all instances of /mysql/ to /mysql-2/ and /mysqld/ to /mysqld-2/. Since a lot of commands by default are compiled to read from /etc/mysql/my.cnf, you have to add a –defaults-file=/etc/mysql-2/my.cnf to some commands where there is no indication of where it’s looking for configuration files. Namely any reference to mysqladmin, mysqld, and mysqld_safe.

Now you can start the new instance with:

/etc/init.d/mysql-2 start

By default, on Ubuntu Server, any errors will be logged to /var/log/daemon.log. After a successful start, you should see pid and sock files in /var/run/mysqld-2. And you can connect to the new instance using the following command:

mysql --defaults-file=/etc/mysql-2/my.cnf

Once you’ve got everything working, add symlinks in /etc/rc*.d /etc/init.d/mysql-2 to start and stop the new instance when the server is restarted. Additionally, I set up entries in logrotate and added an alias mysql2=’mysql –defaults-file=/etc/mysql-2/my.cnf’ for ease of use.

Leave a Reply