My team has recently been working on a new Spring Boot application. One of the many best practice requirements—see non-functional requirements—is that we separate our read and write layers when utilising RDBMS (such as MySQL). As a newbie to this particular problem in Java, it seems that the easiest way is to use the official replication driver provided by the com.mysql package.

Note: We had some interesting issues with this method. I would not recommend ReplicationDriver for use in a spring boot application.

Enabling the Replication Driver #

In Spring this is as simple as adding the following to your properties file;

spring.datasource.driverClassName=com.mysql.jdbc.ReplicationDriver

The replicaton driver assumes that the credentials on the write layer are the same as those for the read layer. To use the slave connection, you can annotate any repository method with @Transactional like so;

@Transactional(readOnly = true)

Testing Replicated Reads #

To test the solution, I created a replicated cluster launched with docker compose and a pair of bitnami/mysql containers. This supports master-slave replication using environment variables. The following configuration worked;

version: '2'

Bootstrapping the database using flyway, the application should now work and have a replicated slave. What did we used to do without docker 😍? However, you won't be able to easily tell if you are reading from the master or the slave! I ended up bashing into each container, enabling query logging, and tailing the logs whilst firing CRUD requests at the API. I'm pretty sure it's possible through application logs, though, but this was definitive proof that the correct database layer was receiving the queries as we expect.

Final Thoughts #

This solution is far from ideal. The problem lies with the fact that the application and connection pool are unaware of the underlying separation. As far as the application is concerned all the connections are the same. The driver simply directs to one server or another based upon the readOnly flag above—the driver is handling the master / slave connections. You are unable to control the pool sizes between master and slave connections, which is handled automatically. If this functionality is desired then I would recommend a code-based solution.