Implementing MySQL Read/Write Splitting with Sharding-JDBC Using Docker

This article explains why read/write splitting is useful, then provides a complete step‑by‑step guide to building a MySQL master‑slave architecture with Docker and configuring Sharding‑JDBC (ShardingSphere) to achieve transparent read/write splitting.

Why read/write splitting? Write operations (INSERT, UPDATE, DELETE) are executed on the primary server, while read‑only queries are sent to the replica to avoid write‑induced latency affecting read performance.

Building the MySQL master‑slave cluster

Using Docker, the author creates a one‑master‑one‑slave setup on MySQL 5.7. The steps include pulling the image, creating configuration directories, writing my.cnf for both master and slave, starting the containers, creating a replication user, and configuring the slave to connect to the master.

Key commands:

docker pull mysql:5.7.26

mkdir -p /usr/local/mysqlData/master/cnf
mkdir -p /usr/local/mysqlData/master/data
mkdir -p /usr/local/mysqlData/slave/cnf
mkdir -p /usr/local/mysqlData/slave/data

vim /usr/local/mysqlData/master/cnf/mysql.cnf

[mysqld]
server-id=1
log-bin=mysql-bin
binlog_cache_size=1M
binlog_format=mixed

vim /usr/local/mysqlData/slave/cnf/mysql.cnf

[mysqld]
server-id=2
log-bin=mysql-slave-bin
relay_log=edu-mysql-relay-bin
log_bin_trust_function_creators=true
binlog_cache_size=1M
binlog_format=mixed
slave_skip_errors=1062

docker run -itd -p 3306:3306 --name master -v /usr/local/mysqlData/master/cnf:/etc/mysql/conf.d -v /usr/local/mysqlData/master/data:/var/lib/mysql -e MYSQL_ROOT_PASSWORD=123456 mysql:5.7.26

# Grant replication user
GRANT REPLICATION SLAVE ON *.* TO 'reader'@'%' IDENTIFIED BY 'reader';
FLUSH PRIVILEGES;

docker run -itd -p 3307:3306 --name slaver -v /usr/local/mysqlData/slave/cnf:/etc/mysql/conf.d -v /usr/local/mysqlData/slave/data:/var/lib/mysql -e MYSQL_ROOT_PASSWORD=123456 mysql:5.7.26

# On master
show master status;
# Get master_log_file and master_log_pos
# On slave
change master to master_host='172.17.0.2', master_user='reader', master_password='reader', master_log_file='mysql-bin.000003', master_log_pos=591;
start slave;
show slave status\G

When Slave_IO_Running: Yes and Slave_SQL_Running: Yes appear, replication is successful.

Configuring Sharding‑JDBC for read/write splitting

The author defines two data sources (ds1 for the master, ds2 for the slave) in application.yml and then creates a master‑slave logical data source named ms that points to these physical sources.

spring:
  shardingsphere:
    datasource:
      names: ds1,ds2
      ds1:
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.jdbc.Driver
        url: jdbc:mysql://192.168.47.149:3306/product_db1?useUnicode=true&characterEncoding=utf-8
        username: root
        password: 123456
      ds2:
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.jdbc.Driver
        url: jdbc:mysql://192.168.47.149:3307/product_db1?useUnicode=true&characterEncoding=utf-8
        username: root
        password: 123456
    masterslave:
      load-balance-algorithm-type: round_robin
      name: ms
      master-data-source-name: ds1
      slave-data-source-names:
        - ds2

With this configuration, Sharding‑JDBC automatically routes write SQL to ds1 and read SQL to ds2 . The author verifies the behavior by inserting a record (observed in the master logs) and querying a product by ID (observed in the slave logs).

Conclusion

The article demonstrates how to build a MySQL master‑slave environment using Docker and how to leverage Sharding‑JDBC to achieve seamless read/write splitting, laying the groundwork for further advanced topics.