ShardingSphere-JDBC Overview, MySQL Master‑Slave Replication Setup, Binlog & Redo Log Review, and Sharding‑JDBC Read/Write Splitting & Sharding Implementation

ShardingSphere‑JDBC is a lightweight Java framework that extends the JDBC layer, offering client‑side direct connections, jar‑based deployment, and full compatibility with JDBC and various ORM frameworks.

MySQL Master‑Slave Replication (Docker)

1) Create required directories for the master server:

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

2) Define the master configuration file ( mysql.cnf) with unique server_id, enabled binlog, cache size, and mixed binlog format.

vim /usr/local/mysqlData/master/cnf/mysql.cnf
[mysqld]
server-id=1
log-bin=mysql-bin
binlog_cache_size=1M
binlog_format=mixed

3) Launch 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

4) Create a replication user on the master:

mysql -u root -p123456
GRANT REPLICATION SLAVE ON *.* TO 'reader'@'%' IDENTIFIED BY 'reader';
FLUSH PRIVILEGES;

5) Create directories and configuration for the slave server, then start it:

mkdir -p /usr/local/mysqlData/slave/cnf
mkdir -p /usr/local/mysqlData/slave/data
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 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

6) On the master, obtain the current binlog file and position:

mysql -u root -p123456
SHOW MASTER STATUS;

7) On the slave, configure the master connection and start 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;
START SLAVE;

8) Verify that Slave_IO_Running and Slave_SQL_Running are both Yes, indicating successful replication.

Binlog and Redo Log Review

Redo log (InnoDB) is a physical log written first to a buffer and periodically flushed to disk; it follows the Write‑Ahead Log (WAL) principle, ensuring durability and fast crash recovery. Binlog is a logical log at the MySQL server layer, recording statement‑level changes for replication and point‑in‑time recovery. Three binlog formats exist: STATEMENT, ROW, and MIXED, each with trade‑offs between size and consistency.

Two‑Phase Commit

During an UPDATE, InnoDB writes the change to the redo log in a prepare state, the executor records the corresponding binlog entry, and finally the engine commits the redo log, completing the transaction. This separation provides atomicity across storage and replication layers.

Sharding‑JDBC Read/Write Splitting

1) Create a Spring Boot project and add dependencies (Spring Web, MyBatis, MySQL driver, Druid, Sharding‑JDBC, Lombok, test).

<dependencies>
    <dependency>...spring-boot-starter-web...</dependency>
    <dependency>...mybatis-spring-boot-starter...</dependency>
    <dependency>...mysql-connector-java...</dependency>
    <dependency>...druid-spring-boot-starter...</dependency>
    <dependency>...sharding-jdbc-spring-boot-starter...</dependency>
    <dependency>...lombok...</dependency>
    <dependency>...spring-boot-starter-test...</dependency>
</dependencies>

2) Configure application.properties with three data sources (one master ds1 and two slaves ds2, ds3), enable SQL logging, and define the master‑slave rule:

spring.shardingsphere.datasource.names=ds1,ds2,ds3
spring.shardingsphere.datasource.ds1.url=jdbc:mysql://...:3306/sharding-jdbc-db
spring.shardingsphere.datasource.ds2.url=jdbc:mysql://...:3307/sharding-jdbc-db
spring.shardingsphere.datasource.ds3.url=jdbc:mysql://...:3307/sharding-jdbc-db
spring.shardingsphere.sharding.default-data-source-name=ds1
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

3) Define the t_user table, entity, mapper, and REST controller. The controller provides /api/user/save (writes to master) and /api/user/findUsers (reads from slaves with round‑robin load balancing).

Sharding‑JDBC Sharding Configuration

1) Inline sharding strategy: data source is chosen by sex % 2, table is chosen by age % 2. Example configuration:

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

2) Test cases illustrate four possible routing outcomes based on sex (odd/even) and age (odd/even), confirming that data lands in the correct database ( ds0 or ds1) and table ( t_user0 or t_user1).

References

ShardingSphere documentation and related tutorial videos are listed for further reading.