Comprehensive Guide to MySQL Master‑Slave Replication, Binlog/Redo Log, and ShardingSphere‑Jdbc/Sharding‑Jdbc Read‑Write Splitting and Sharding

Overview

ShardingSphere‑Jdbc is a lightweight Java framework that extends the JDBC layer, providing client‑side direct connection to databases and full compatibility with JDBC and ORM frameworks.

MySQL Master‑Slave Replication with Docker

Step 1: Create directories for the master server.

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

Step 2: Define the master configuration file ( mysql.cnf ) with server-id=1 , enable binlog, set binlog_format=mixed , etc.

vim /usr/local/mysqlData/master/cnf/mysql.cnf
[mysqld]
## 设置server_id,注意要唯一
server-id=1
## 开启binlog
log-bin=mysql-bin
binlog_cache_size=1M
binlog_format=mixed

Step 3: Run the master container.

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

Step 4: Create a replication user reader on the master.

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

Step 5‑7: Create slave directories, configure mysql.cnf with server-id=2 , enable log-bin , set relay_log , and start the slave container.

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

Step 8: On the slave, execute change master with the master’s IP, user, password, binlog file and position, then start the slave threads.

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;

Verification shows Slave_IO_Running: Yes and Slave_SQL_Running: Yes .

Redo Log and Binlog Fundamentals

Redo log is an InnoDB‑specific physical log written to a circular set of files; it is flushed on transaction commit, every second, or when the buffer is half full. Binlog is a logical log at the MySQL server layer, used for replication and point‑in‑time recovery.

Redo log records page‑level changes (physical).

Binlog records SQL statements or row changes (logical) in STATEMENT, ROW, or MIXED format.

Two‑Phase Commit in InnoDB

During an UPDATE, the engine writes the change to the redo log (prepare phase), the executor records a binlog entry, and finally the engine commits the redo log (commit phase).

MySQL Replication Process

The slave establishes a long‑lived connection to the master, the master streams binlog events, the slave writes them to a relay log, and the SQL thread replays the events. The replication is asynchronous, and multi‑threaded slaves can parallelize the SQL thread.

Sharding‑Jdbc Read‑Write Splitting

Dependencies (Spring Boot, MyBatis, Druid, Sharding‑Jdbc, Lombok) are added to pom.xml . The application.properties defines three data sources (one master ds1 and two slaves ds2 , ds3 ), the master‑slave rule, and round‑robin load balancing.

spring.shardingsphere.masterslave.name=ms
spring.shardingsphere.masterslave.master-data-source-name=ds1
spring.shardingsphere.masterslave.slave-data-source-names=ds2,ds3
spring.shardingsphere.masterslave.load-balance-algorithm-type=round_robin

Entity, Mapper, and Controller code demonstrate CRUD operations that automatically route writes to the master and reads to the slaves.

Sharding‑Jdbc Sharding Configuration

Inline sharding strategy uses user_id%2 to select the database and order_id%2 to select the table.

spring.shardingsphere.sharding.tables.t_order.database-strategy.inline.sharding-column=user_id
spring.shardingsphere.sharding.tables.t_order.database-strategy.inline.algorithm-expression=ds${user_id%2}
spring.shardingsphere.sharding.tables.t_order.table-strategy.inline.sharding-column=order_id
spring.shardingsphere.sharding.tables.t_order.table-strategy.inline.algorithm-expression=t_order${order_id%2}

Distributed primary‑key generation is configured with Snowflake.

spring.shardingsphere.sharding.tables.t_order.key-generator.column=id
spring.shardingsphere.sharding.tables.t_order.key-generator.type=SNOWFLAKE

A more complex example splits t_user tables by sex (data source) and age (table) using the same inline expressions.

spring.shardingsphere.sharding.tables.t_user.database-strategy.inline.sharding-column=sex
spring.shardingsphere.sharding.tables.t_user.database-strategy.inline.algorithm-expression=ds${sex%2}
spring.shardingsphere.sharding.tables.t_user.table-strategy.inline.sharding-column=age
spring.shardingsphere.sharding.tables.t_user.table-strategy.inline.algorithm-expression=t_user${age%2}

JUnit tests verify that records with different sex and age values are routed to the expected physical tables.

References

ShardingSphere documentation and related video tutorials.