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 spring.datasource.url=jdbc:mysql:replication://localhost:3333,localhost:3334/schema?autoReconnect=true

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' services:   master-db:     image: 'bitnami/mysql:latest'     ports:       - '3333:3306'     volumes:       - 'mysql_master_data:/bitnami'     environment:       MYSQL_REPLICATION_MODE: master       MYSQL_REPLICATION_USER: root       MYSQL_REPLICATION_PASSWORD: root       MYSQL_ROOT_PASSWORD: root       MYSQL_USER: crud       MYSQL_PASSWORD: crud       MYSQL_DATABASE: schema       ALLOW_EMPTY_PASSWORD: "yes"   slave-db:     image: 'bitnami/mysql:latest'     ports:      - '3334:3306'     depends_on:      - db     environment:       MYSQL_REPLICATION_MODE: slave       MYSQL_REPLICATION_USER: root       MYSQL_REPLICATION_PASSWORD: root       MYSQL_MASTER_ROOT_PASSWORD: root       MYSQL_ROOT_PASSWORD: root       MYSQL_USER: crud       MYSQL_PASSWORD: crud       MYSQL_DATABASE: schema       MYSQL_MASTER_HOST: master-db       MYSQL_MASTER_PORT_NUMBER: 3306       ALLOW_EMPTY_PASSWORD: "yes" volumes:   mysql_master_data:     driver: local

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.

Michael is a Software Engineer working in the North West of England. Michael spends his days building hand-crafted PHP applications. Rumours of his super-hero status are currently unconfirmed. He savours his victories when solving difficult programming challenges; occasionally writing about them here, on his personal blog.