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.
Enabling the Replication Driver
In Spring this is as simple as adding the following to your properties file;
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.
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.