Running Multiple MySQL Servers With Different Versions On The Same Machine.

{{{
Today I was urged to install MySQL 5.1 to run a “new” Rails project. Since I refused to uninstall my existing 5.5 I found a way of running two separate instances on my machine using “MySQL-Sandbox”:http://search.cpan.org/~gmax/MySQL-Sandbox-3.0.12/.

Frankly, it was a pain in the ass and MySQL-Sandbox saved my day. Here is what I did on my Ubuntu machine – conceptually, this should work for other Linuxes, OSX, etc, as well.

h3. MySQL-Sandbox?

This little tool helps you by installing and pre-configuring a separate MySQL instance. It also provides scripts for administrating your servers. It is great.

h3. Download The Binary.

Download the _binary_ tarball from the “MySQL download”:http://dev.mysql.com/downloads/mysql/ site. I downloaded @mysql-5.1.72-linux-i686-glibc23.tar.gz@.

h3. Install Sandbox.

I had to install some Ubuntu packages as listed on “this helpful post”:http://brianinksell.blogspot.com.au/2012/10/mysql-sandbox-on-ubuntu.html. However, this might not be necessary on OSX.

$ sudo apt-get install build-essential libaio1 libaio-dev

Then, install the sandbox tool.

sudo cpan sandbox

h3. Create Your Sandbox.

The @make_sandbox@ command will now install and configure a brand-new MySQL setup in a separate directory. I ran the following command.

make_sandbox mysql-5.1.72-linux-i686-glibc23.tar.gz

This installs mysql 5.1.72 into @/home/nick/sandboxes/msb_5_1_72@. Changing into that directory you can simply configure and spin up the server.

h3. Configuring MySQL.

Your configuration file now lives in @msb_5_1_72/my.sandbox.cnf@ and is ready to be edited – which wasn’t necessary as I was happy with the settings.

The only interesting directive to me was the port.

port               = 5172

h3. Starting The Server.

The @msb_5_1_72@ directory comes with handy administration scripts, so within that dir I just ran the @start@ command.

msb_5_1_72$ ./start

h3. Using The Server.

This runs a completely isolated MySQL 5.1 instance on port 5172 while letting my 5.5 alive on the standard port! Awesome!!!

Now, to connect to that server you just have to provide the port number in your client.

Note: On Linux, you also need to provide the @–host@ with @127.0.0.1@ as “described here”:http://dev.mysql.com/doc/refman/5.5/en/connecting.html. Don’t say I didn’t warn you.

mysqladmin -u root --host=127.0.0.1 --port=5172 
  -p msandbox password

The original root password is @msandbox@, so go change this. Everything else works just like your “global” installation.

h3. And, In Rails?

My @database.yml@ looks like this.

development:
  adapter: mysql
  database: blog
  username: "root"
  password: ""
  host: 127.0.0.1
  port: 5172

Thanks to “Giuseppe Maxia”:https://twitter.com/datacharmer for this helpful tool.
}}}