ShardingSphere-JDBC MySQL Master‑Slave Replication, Redo Log, Binlog and Sharding Implementation Guide

ShardingSphere‑Jdbc is a lightweight Java framework that enhances the JDBC layer, providing read/write splitting and sharding capabilities while remaining fully compatible with JDBC and ORM frameworks.

The article first explains how to set up MySQL master‑slave replication using Docker, including directory creation, configuration files, container startup, user creation, and synchronization commands.

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

It then reviews MySQL redo log and binlog mechanisms, their flushing conditions, formats (STATEMENT, ROW, MIXED), and differences, as well as the two‑phase commit process for update statements.

mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000003| 591      |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+

Next, the principles of MySQL replication are described, covering change master, start slave, I/O and SQL threads, and the asynchronous nature of replication.

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;

Subsequently, the guide shows how to implement read/write splitting with Sharding‑Jdbc, providing Maven dependencies, Spring Boot configuration, data source definitions, and master‑slave settings.

<dependencies>
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-web</artifactId>
    </dependency>
    <dependency>
        <groupId>org.apache.shardingsphere</groupId>
        <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
        <version>4.0.0-RC1</version>
    </dependency>
</dependencies>

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

It also details sharding configuration using inline strategies for database and table routing, distributed primary‑key generation, and examples of logical and actual tables.

spring.shardingsphere.sharding.tables.t_order.actual-data-nodes=ds$->{0..1}.t_order$->{0..1}
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}
spring.shardingsphere.sharding.tables.t_order.key-generator.column=id
spring.shardingsphere.sharding.tables.t_order.key-generator.type=SNOWFLAKE

Finally, a complete example of sharding user data by age and gender across two databases is presented, including table definitions, application.properties settings, and JUnit test cases that insert records into the appropriate shards.

CREATE TABLE `t_user0` (
  `id` bigint(20) DEFAULT NULL,
  `nickname` varchar(200) DEFAULT NULL,
  `password` varchar(200) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `sex` int(11) DEFAULT NULL,
  `birthday` varchar(100) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE `t_user1` (...same columns...);

@Test
public void test01() {
    User user = new User();
    user.setNickname("zhangsan" + new Random().nextInt());
    user.setPassword("123456");
    user.setAge(17);
    user.setSex(1);
    user.setBirthday("1997-12-03");
    userMapper.addUser(user);
}