ShardingSphere-JDBC Tutorial: MySQL Master‑Slave Replication, Redo/Binlog, and Sharding Configuration

ShardingSphere‑Jdbc is introduced as a lightweight Java framework that enhances the JDBC layer, providing compatibility with standard JDBC and ORM frameworks while enabling additional services such as read‑write splitting.

1. MySQL Master‑Slave Replication Setup

Docker commands are used to create directories, configure my.cnf for the master and slave, start the containers, and create a replication user. Key steps include:

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

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

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

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

After creating the master, a replication user 'reader'@'%' identified by 'reader' is granted, and the slave configuration is prepared similarly with its own my.cnf (server‑id=2, log‑bin, relay_log, etc.).

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

On the 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 is executed, followed by start slave to launch the I/O and SQL threads.

2. Redo Log and Binlog Overview

Redo log (InnoDB) is a physical log written to a buffer and flushed under three conditions: every second, transaction commit, or when the buffer is half full. It follows a write‑ahead‑log (WAL) approach, ensuring durability and enabling crash recovery by replaying logs after the last checkpoint.

Binlog is a logical log at the MySQL server layer, recording all data‑changing statements (excluding SELECT/SHOW). It supports three formats—STATEMENT, ROW, and MIXED—and is essential for replication and incremental backups.

Key differences:

Redo log is engine‑specific (InnoDB), physical, and circular.

Binlog is server‑wide, logical, and append‑only.

3. Two‑Phase Commit

The article illustrates the two‑phase commit process for an UPDATE statement: the executor fetches the row, the engine writes the change to the redo log (prepare), the executor records a binlog entry, and finally the engine commits the redo log, completing the transaction.

4. MySQL Replication Principle

The master maintains a thread serving the slave’s long‑living connection. The slave issues CHANGE MASTER with the master’s IP, port, credentials, and binlog position, then runs START SLAVE . The I/O thread pulls binlog events, writes them to a relay log, and the SQL thread replays them, possibly using multiple threads in modern MySQL.

5. Sharding‑Jdbc Read‑Write Splitting

A Spring Boot project is set up with dependencies for Spring Web, MyBatis, MySQL driver, Druid, Sharding‑Jdbc, and Lombok. The application.properties configures three data sources (one master, two slaves) and enables master‑slave routing:

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

Mapper and controller examples demonstrate that /api/user/save always writes to the master, while /api/user/findUsers reads from the slaves in a round‑robin fashion.

6. MySQL Horizontal Partitioning (Sharding)

The guide explains horizontal (row‑based) and vertical (column‑based) splitting, then shows how to configure Sharding‑Jdbc for sharding a t_user table across two databases ( ds0 , ds1 ) and two tables ( t_user0 , t_user1 ) based on sex (database) and age (table) using an inline strategy:

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}

A distributed primary key is generated with Snowflake ( key-generator.type=SNOWFLAKE ).

Test cases illustrate four scenarios where different combinations of sex (odd/even) and age (odd/even) route inserts to the appropriate database and table.

References to the official ShardingSphere documentation and a Bilibili video are provided for further study.