Configuring Multiple MySQL Servers on a Single Machine

Posted on Wednesday 18 May 2011 by Jon Reades

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:

cmake . -DCMAKE_INSTALL_PREFIX=/usr/local/mysql-secure -DMYSQL_DATADIR=/var/mysql-secure/ -DWITH_SSL=yes -DCURSES_LIBRARY=/usr/lib/libncurses.a -DWITH_DEBUG=OFF -DMYSQL_MAINTAINER_MODE=OFF -DENABLED_PROFILING=ON -DINSTALL_LAYOUT=STANDALONE

Now you go back to the old habit of:

make sudo make install

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:

sudo ./scripts/mysql_install_db --user=mysql --basedir=/usr/local/mysql-secure/ --datadir=/var/lib/mysql-secure

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:

[client] port = XXXX socket = /var/run/mysqld/mysqld-secure.sock

user = mysql socket = /var/run/mysqld/mysqld-secure.sock port = XXXX basedir = /usr/local/mysql-secure datadir = /var/lib/mysql-secure tmpdir = /tmp

You’re nearly there, and from here on out the server can be started by simply running:

sudo ./bin/mysqld_safe --defaults-file=/etc/mysql/secure.cnf --user=mysql &

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:

mysql --socket=/var/run/mysqld/mysqld-secure.sock -P XXX -u root -p db mysql -P XXX -u foo -p db

Hope this helps anyone trying to do anything similar.

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

data, library, News, software