Speeding up database-bound tests with MySQL

In my day job we use MySQL a lot, and we also like to pretend that we have tests.

It's a good idea to run tests using the same database you're using in production, but MySQL ain't exactly a speed demon compared to memory-backed databases like Hypersonic, H2 or even Derby.

But wait - we can have our cake and eat it, too!

The trick is to tell MySQL to store its data files in memory; more specifically, the InnoDB files should be stored on a memory-backed filesystem like tmpfs.

So, create a directory somewhere (I use /var/lib/mysql/innodb) and mount tmpfs on it (mount -t tmpfs none /var/lib/mysql/innodb, or set it up in /etc/fstab).

After that, tell MySQL to store the InnoDB files there by sticking the following lines in /etc/mysql/my.cnf:

innodb_data_home_dir=/var/lib/mysql/innodb
innodb_log_group_home_dir=/var/lib/mysql/innodb

Now restart MySQL, and run your now-speedier tests!