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;

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.