Embedded MySQL in Java With Connector/MXJ and 64-bit Linux

MySQL’s Connector/MXJ is a tool that exposes the ability to start and stop an embedded MySQL server through a Java API. You can have the MySQL JDBC driver start up a server just by appropriately configuring your JDBC url or you can programmatically control the server through the MysqldResource class. It does this by bundling precompiled versions of the mysql (client) and mysqld (server) binaries and invoking the correct ones based on the os.name and os.arch system properties. This sounds great for spinning up an instance of mysqld for testing, but there are a few issues to be solved. MySQL hasn’t published any recent versions of MXJ to the central Maven repository, so you’ll have to install the files by hand into your local ~/.m2 (or repo server, if you have one set up). Also, they don’t include 64-bit Linux binaries. Update: I have submitted artifacts for Connector/MXJ 5.0.12 to Maven Central and they have been approved, so you need only add the following dependency to your code and you’re good to go.

<dependency>
  <groupId>mysql</groupId>
  <artifactId>mysql-connector-mxj</artifactId>
  <version>5.0.12</version>
  <scope>test</scope>
</dependency>

If you’d like to build the artifacts yourself, carry on with the instructions… Download the zip distribution yourself from MySQL’s download page (5.0.12 is current as of this article). When unzipped, you’ll find two jars: mysql-connector-mxj-gpl-5-0-12-db-files.jar (the ‘db-files’ jar) and mysql-connector-mxj-gpl-5-0-12.jar (the ‘mxj’ jar). The mxj jar is quite small and just contains some Java classes. The db-files jar is pretty hefty because it contains the actual binaries for x86 32-bit FreeBSD, Linux, Mac OS X, Windows and Solaris (as well as Solaris on SPARC in case anyone still uses that…). Alas, no 64-bit Linux… If this doesn’t affect you, skip to the end of the article to see how to install the jars correctly into your local ~/.m2 repo. You’ll know that you’re hitting this problem if you get output like this:

[MysqldResource] launching mysqld (driver_launched_mysqld_1)
/tmp/test-mxj/bin/mysqld: error while loading shared libraries: libaio.so.1: cannot open shared object file: No such file or directory

This is (correctly) saying is that the 32-bit mysqld binary cannot find libaio. You should have libaio installed since the 64-bit version we’ll be building will need it, though!

Adding 64-bit Linux binaries to the db-files jar

Fortunately, MySQL provides instructions on how to modify the db-files jar. If you’d rather use my pre-built jar with Linux 64-bit binaries, go to the end of the article. Download the mysql 5.5.9 source tarball from the MySQL archives. 5.5.9 isn’t the latest version, but all the other binaries in MXJ 5.0.12 were built with 5.5.9 so we’ll use that to stay consistent. The source installation manual shows the steps necessary to perform a complete installation. We don’t need to do all of that since we just need the compiled code.

cmake .
make

The mysql binary is in client/mysql and the mysqld binary is in sql/mysqld. Extract mysql-connector-mxj-gpl-5-0-12-db-files.jar into a directory of your choosing so we can add in proper 64-bit versions of mysql and mysqld. The actual binaries that are run is determined by the platform-map.properties file in the root of db-files jar. Replace the following section

Linux-x86_64=Linux-i386
Linux-amd64=Linux-i386

with

Linux-x86_64=Linux-x86_64
Linux-amd64=Linux-x86_64

This means that when x86_64 or amd64 are seen in the os.arch Java system property, the contents of the Linux-x86_64 directory will be used. First, we’ll need the directory to exist inside the 5-5-9 directory:

mkdir 5-5-9/Linux-x86_64

Copy your newly compiled mysql and mysqld binaries into the Linux-x86_64 directory, and add a version.txt file containing appropriate contents. I followed the template of the Linux-i386 version, modifying for my newer kernel (3.0.4) and platform (x86_64).

mysql-5.5.9-linux3.0.4-x86_64/bin/mysqld

Go back to the root of the extracted jar, then create a new jar.

jar -cf ../mysql-connector-mxj-gpl-5-0-12-db-files.jar *

Installing the Maven artifacts

Now we’ve constructed a mysql-connector-mxj-gpl-5-0-12-db-files.jar. To use it, you’ll need to install it into your ~/.m2 repo. I’ve built a very simple pom.xml for it to save you the trouble. Download the pom and put it in the same directory as your newly built jar. Or, if you’d rather not go to the trouble of building your own db-files jar, download mine.

mvn install:install-file -Dfile=mysql-connector-mxj-gpl-5-0-12-db-files.jar -DpomFile=mysql-connector-mxj-gpl-5-0-12-db-files.pom

You still need the mxj jar, though, so I built a pom for that jar as well.

mvn install:install-file -Dfile=mysql-connector-mxj-gpl-5-0-12.jar -DpomFile=mysql-connector-mxj-gpl-5-0-12.pom

MXJ will re-use a deployment of mysql that it has made previously, so make sure to remove any temporary directories left over by mysql. Otherwise, you’ll keep on using the old (broken) mysqld binaries. This will clear both the default mxj directory and the one used by ConnectorMXJUrlTestExample, in case you ran that previously. Make sure to add also include any directories you’ve configured yourself (e.g. by setting server.basedir in the JDBC url).

rm -rf /tmp/mysql-c.mxj
rm -rf /tmp/test-mxj

MXJ should now be ready to use. To depend on it from a Maven project, this is the dependency you need (you probably don’t want to use MXJ for production deployments, so it’s set to test scope). If you’re using Maven 3, you will also need to put the artifacts in your repo server since Maven 3 complains if it can only find artifacts in the local ~/.m2 repo.

<dependency>
  <groupId>mysql</groupId>
  <artifactId>mysql-connector-mxj</artifactId>
  <version>5.0.12</version>
  <scope>test</scope>
</dependency>