ShardingSphere-Jdbc Overview, MySQL Master‑Slave Replication, Binlog & Redo Log, and Sharding Implementation

1. Overview

ShardingSphere‑Jdbc is positioned as a lightweight Java framework that provides additional services at the JDBC layer. It works as an enhanced JDBC driver, fully compatible with JDBC and various ORM frameworks.

2. MySQL Master‑Slave Replication (Docker)

2.1 Create directories for the master server

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

2.2 Define the master configuration file

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

[mysqld]
## Set server_id, must be unique
server-id=1
## Enable binlog
log-bin=mysql-bin
## Binlog cache size
binlog_cache_size=1M
## Binlog format (mixed, statement, row, default is statement)
binlog_format=mixed

2.3 Create and start the MySQL 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

2.4 Add a replication user on the master

# docker ps
CONTAINER ID        IMAGE               COMMAND               CREATED             STATUS              PORTS                     NAMES
6af1df686fff        mysql:5.7           "docker-entrypoint..." 5 seconds ago       Up 4 seconds        0.0.0.0:3306->3306/tcp, 33060/tcp   master
# docker exec -it master /bin/bash
root@...:/# mysql -u root -p123456
mysql> GRANT REPLICATION SLAVE ON *.* TO 'reader'@'%' IDENTIFIED BY 'reader';
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

2.5 Create directories and configuration for the slave

mkdir -p /usr/local/mysqlData/slave/cnf
mkdir -p /usr/local/mysqlData/slave/data
vim /usr/local/mysqlData/slave/cnf/mysql.cnf

[mysqld]
## Set server_id, must be unique
server-id=2
## Enable binlog for potential downstream slaves
log-bin=mysql-slave-bin
## Relay log configuration
relay_log=edu-mysql-relay-bin
## Allow creation of functions/stored procedures
log_bin_trust_function_creators=true
## Binlog cache size
binlog_cache_size=1M
## Binlog format
binlog_format=mixed
## Skip specific errors (e.g., duplicate key 1062)
slave_skip_errors=1062

2.6 Create and run the MySQL 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

2.7 Configure the slave to connect to the master

On the master, obtain master_log_file and master_log_pos :

mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000003| 591      |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

Find the master container IP:

docker inspect --format='{{.NetworkSettings.IPAddress}}' master
172.17.0.2

On the slave, run:

docker exec -it slaver /bin/bash
mysql -u root -p123456
mysql> 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;
mysql> start slave;

Verify replication:

mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 172.17.0.2
Master_User: reader
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000003
Read_Master_Log_Pos: 591
Relay_Log_File: edu-mysql-relay-bin.000002
Relay_Log_Pos: 320
Relay_Master_Log_File: mysql-bin.000003
Slave_IO_Running: Yes
Slave_SQL_Running: Yes

3. Binlog and Redo Log Review

3.1 Redo Log (InnoDB)

InnoDB first writes changes to the redo‑log buffer, then flushes it to the redo‑log file at a fixed frequency, on transaction commit, or when the buffer is half full. It implements Write‑Ahead Logging (WAL): the redo log is written before the data page is modified.

The buffer pool caches pages; dirty pages are later flushed to disk by a dedicated flush thread. Redo logs are fixed‑size circular files (e.g., four 1 GB files) that are overwritten in order.

3.2 Binlog (Server Layer)

The binary log records all data‑changing statements (excluding SELECT/SHOW) and is used for master‑slave replication and incremental recovery. Three formats exist: STATEMENT, ROW, and MIXED.

STATEMENT: stores the original SQL; low volume but can cause inconsistency in edge cases.

ROW: stores row‑level changes; ensures consistency but generates larger logs.

MIXED: uses STATEMENT by default and falls back to ROW when needed.

3.3 Differences Between Redo Log and Binlog

Redo log is InnoDB‑specific physical logging; binlog is a logical log used by the MySQL server.

Redo log records page modifications; binlog records the original SQL operation.

Redo log is circular and overwrites old data; binlog is append‑only and retains old files.

3.4 Two‑Phase Commit

create table T(ID int primary key, c int);
update T set c=c+1 where ID=2;

The execution flow:

The executor fetches the row with ID = 2 from the engine.

The executor increments the column value and asks the engine to write the new row.

The engine updates the in‑memory page and writes a redo‑log entry in the PREPARE state.

The executor generates a binlog entry and writes it to disk.

The executor commits the transaction; the engine marks the redo‑log entry as COMMITTED.

4. MySQL Master‑Slave Replication Principle

The slave maintains a long‑running connection to the master. The master runs an I/O thread that streams binlog events to the slave. The slave writes them to a relay log, and an SQL thread reads the relay log and replays the statements. Multiple SQL threads can be used in modern implementations, and replication is asynchronous.

5. Sharding‑Jdbc Read‑Write Splitting

5.1 Create a Spring Boot project and add dependencies

<dependencies>
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-web</artifactId>
    </dependency>
    <dependency>
        <groupId>org.mybatis.spring.boot</groupId>
        <artifactId>mybatis-spring-boot-starter</artifactId>
        <version>2.1.4</version>
    </dependency>
    <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
        <scope>runtime</scope>
    </dependency>
    <dependency>
        <groupId>com.alibaba</groupId>
        <artifactId>druid-spring-boot-starter</artifactId>
        <version>1.1.21</version>
    </dependency>
    <dependency>
        <groupId>org.apache.shardingsphere</groupId>
        <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
        <version>4.0.0-RC1</version>
    </dependency>
    <dependency>
        <groupId>org.projectlombok</groupId>
        <artifactId>lombok</artifactId>
        <optional>true</optional>
    </dependency>
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-test</artifactId>
        <scope>test</scope>
    </dependency>
</dependencies>

5.2 application.properties configuration

spring.main.allow-bean-definition-overriding=true
spring.shardingsphere.props.sql.show=true

# data sources
spring.shardingsphere.datasource.names=ds1,ds2,ds3

# master ds1
spring.shardingsphere.datasource.ds1.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.ds1.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.ds1.url=jdbc:mysql://47.101.58.187:3306/sharding-jdbc-db?useUnicode=true&useSSL=false&serverTimezone=Asia/Shanghai
spring.shardingsphere.datasource.ds1.username=root
spring.shardingsphere.datasource.ds1.password=123456

# slave ds2
spring.shardingsphere.datasource.ds2.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.ds2.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.ds2.url=jdbc:mysql://47.101.58.187:3307/sharding-jdbc-db?useUnicode=true&useSSL=false&serverTimezone=Asia/Shanghai
spring.shardingsphere.datasource.ds2.username=root
spring.shardingsphere.datasource.ds2.password=123456

# slave ds3 (same as ds2 in this example)
spring.shardingsphere.datasource.ds3.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.ds3.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.ds3.url=jdbc:mysql://47.101.58.187:3307/sharding-jdbc-db?useUnicode=true&useSSL=false&serverTimezone=Asia/Shanghai
spring.shardingsphere.datasource.ds3.username=root
spring.shardingsphere.datasource.ds3.password=123456

# default (write) data source
spring.shardingsphere.sharding.default-data-source-name=ds1

# master‑slave rule
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

mybatis.type-aliases-package=com.ppdai.shardingjdbc.entity

5.3 Create table t_user

CREATE TABLE `t_user` (
  `id` int NOT NULL AUTO_INCREMENT,
  `nickname` varchar(100) DEFAULT NULL,
  `password` varchar(100) DEFAULT NULL,
  `sex` int DEFAULT NULL,
  `birthday` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4;

5.4 Define Entity, Mapper, and Controller

@Data
public class User {
    private Integer id;
    private String nickname;
    private String password;
    private Integer sex;
    private String birthday;
}

@RestController
@RequestMapping("/api/user")
public class UserController {
    @Autowired
    private UserMapper userMapper;

    @PostMapping("/save")
    public String addUser() {
        User user = new User();
        user.setNickname("zhangsan" + new Random().nextInt());
        user.setPassword("123456");
        user.setSex(1);
        user.setBirthday("1997-12-03");
        userMapper.addUser(user);
        return "success";
    }

    @GetMapping("/findUsers")
    public List
findUsers() {
        return userMapper.findUsers();
    }
}

public interface UserMapper {
    @Insert("insert into t_user(nickname,password,sex,birthday) values(#{nickname},#{password},#{sex},#{birthday})")
    void addUser(User user);

    @Select("select * from t_user")
    List
findUsers();
}

5.5 Verification

When calling http://localhost:8080/api/user/save the request is routed to the master (ds1). When calling http://localhost:8080/api/user/findUsers the read requests are load‑balanced between the slaves ds2 and ds3.

6. Sharding‑Jdbc Sharding Implementation

6.1 Logical Tables

User data is split into two physical tables (t_user0, t_user1) based on age % 2 . The logical table name is t_user .

6.2 Inline Sharding Strategy

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

6.3 Test Cases

@SpringBootTest
class ShardingJdbcApplicationTests {
    @Autowired
    private UserMapper userMapper;

    /** sex: odd, age: odd → ds1.t_user1 */
    @Test
    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);
    }

    /** sex: odd, age: even → ds1.t_user0 */
    @Test
    void test02() { /* similar to test01 with age=18 */ }

    /** sex: even, age: odd → ds0.t_user1 */
    @Test
    void test03() { /* similar with sex=2, age=17 */ }

    /** sex: even, age: even → ds0.t_user0 */
    @Test
    void test04() { /* similar with sex=2, age=18 */ }
}

7. References

Official documentation: https://shardingsphere.apache.org/document/current/cn/overview/

Video tutorial: https://www.bilibili.com/video/BV1ei4y1K7dn

8. Promotional Content (Original)

Search the public account "Java Architecture Expert" and reply with keywords such as "architecture" or "clean architecture" to receive a surprise gift package.

© Content sourced from the internet; all rights belong to the original author.