Configuring Multiple MySQL Servers on a Single Machine

18 May 2011

A bit of a dry post here, but I thought I’d share my experience of trying to get two instances of MySQL (and two different versions, to boot) running simultaneously on a single piece of hardware as I’ve spent the past two days tearing my hear out and swearing profusely (mostly) under my breath.

So the background for this post is that we’ve recently received a new toy server for data crunching in short, sharp bursts. The idea is that when we’re working with sensitive data that can’t be stored ‘in the cloud’ we can use this machine, with its 32GB of RAM, to whiz through aggregation and querying. However, because we’ve got multiple users of the system and some of us need different levels of security as well as different versions of the software, there really was no alternative to installing two sets of binaries with two very different configurations.

We installed the first using Ubuntu’s handy apt-get utility, but the second one proved the problem. First, it needs to be installed somewhere else, and because it’s Ubuntu we can’t install from RPM. So custom configuration here we come… which is where the fun begins because it turns out that the configuration process has changed utterly since I last did anything like this.

Apparently, cmake has now replaced ./configure as the ‘right way’ to do these things, and after a great deal of huffing and puffing this got me a configurable system:

If you’re very, very lucky then this will have compiled smoothly. If you’re not then, in all probability you don’t have curses or libaio1 installed and will have to make clean before you can reach this point without an error.

You’re now ready to finish the installation, so head on over to your base directory for the install:

cd /usr/local/mysql-secure/

I’m assuming here that you still want to run MySQL as the mysql user (who should have their login shell set to /dev/null), but you could quite easily run it as another user if you want:

You should then go through the normal steps to set up a root password and generally lock down MySQL as best you can. You will also want to set up a separate configuration file for your ‘secure’ MySQL installation because, at the very least, you need it to run on a different port (here specified as XXXX) and with a different socket. Something like this:

I’m still trying to figure out what isn’t quite right about the configuration, but here’s how to connect locally and remotely to a database called db on the second instance of MySQL server listening on port XXX:

Next up: figuring out how to configure SSL with MySQL for additional security.

Configuring a Third Server

As with all things computer related, you can always have more. In this case, I wanted access to the OpenQuery Graph engine for MySQL/MariaDB. Well, it turns out that MySQL isn’t so keen on the engine and MariaDB (a drop-in replacement for MySQL) is the only way to go. So for posterity’s sake here’s how I got a third database up and running on the same machine.

I grabbed the source distribution for MariaDB (this is version 5.3, by the time you’re reading this you might want to check for a 5.x) from here: http://downloads.askmonty.org/mariadb/5.3/#file_type=sourcetar -xzvf maria...
cd ./maria...
./configure --prefix=/usr/local/mariadb-5.3 --datadir=/cosmic/...
make && sudo make install
sudo chown -R XXX /cosmic/...
cd /usr/local/mariadb-5.3
sudo ./bin/mysql_install_db --user=XXX --defaults-file=/etc/my-openquery.cnf --datadir=/cosmic/...
That last command obviously relies on you having created a configuration file for the OpenQuery-enabled MariaDB installation. Here’s a minimal working copy: