Comprehensive Guide to ShardingSphere-Jdbc, MySQL Master‑Slave Replication, and Sharding Strategies

1. Overview of ShardingSphere‑Jdbc

ShardingSphere‑Jdbc is a lightweight Java framework that enhances the JDBC layer, acting as an advanced driver compatible with JDBC and various ORM frameworks.

2. MySQL Master‑Slave Replication with Docker

2.1 Create Directories for Master

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

2.2 Master Configuration (my.cnf)

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

2.3 Start 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 Create Replication User

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

2.5 Create Directories for Slave

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

2.6 Slave Configuration (my.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

2.7 Start 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.8 Configure Slave to Connect to Master

# On master
SHOW MASTER STATUS;
# Get master_log_file and master_log_pos
# On 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;
START SLAVE;

3. Redo Log and Binlog Review

3.1 Redo Log (InnoDB)

Redo log is written to a buffer and flushed to disk on transaction commit, every second, or when the buffer is half full. It is a physical log used for crash recovery.

3.2 Binlog (Server Layer)

Binlog records logical changes (SQL statements) and is used for replication and point‑in‑time recovery. Formats include STATEMENT, ROW, and MIXED.

3.3 Differences

Redo log is engine‑specific (InnoDB) and physical; binlog is server‑wide and logical.

Redo log is circular; binlog is append‑only.

4. Two‑Phase Commit in InnoDB

CREATE TABLE T(ID int primary key, c int);
UPDATE T SET c=c+1 WHERE ID=2;

The executor writes to the redo log (prepare phase), generates a binlog entry, and finally commits the redo log, completing the two‑phase commit.

5. Sharding‑Jdbc Read/Write Splitting

5.1 Maven Dependencies (Spring Boot)

<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>
    ...
</dependencies>

5.2 application.properties (master‑slave)

spring.shardingsphere.datasource.names=ds1,ds2,ds3
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

5.3 Entity, Mapper, Controller (User)

@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() { /* ... */ }
    @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();
}

6. MySQL Sharding Principles

6.1 Horizontal vs Vertical Partitioning

Horizontal splits rows across multiple tables/databases; vertical splits columns into separate tables.

6.2 Non‑Stop Migration

Use Canal for incremental sync.

Use middleware for full data import.

Compare and reconcile data.

Switch configuration after stability.

7. Sharding‑Jdbc Sharding Implementation

7.1 Logical Table Definition

t_order0 and t_order1 are physical tables; logical name is t_order.

7.2 Inline Sharding Strategy

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}

7.3 Distributed Primary Key

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

7.4 Example: Sharding by Age and Sex

Data is routed to ds0/ds1 based on sex%2 and to t_user0/t_user1 based on age%2 . The following Spring Boot test inserts users covering all four combinations.

@Test
public void test01() { /* sex=1, age=17 → ds1.t_user1 */ }
@Test
public void test02() { /* sex=1, age=18 → ds1.t_user0 */ }
@Test
public void test03() { /* sex=2, age=17 → ds0.t_user1 */ }
@Test
public void test04() { /* sex=2, age=18 → ds0.t_user0 */ }

8. References

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

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