MySQL Master‑Slave Replication and Sharding‑JDBC Sharding & Read/Write Splitting Guide

Overview

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

MySQL Master‑Slave Replication Setup (Docker)

1) Create directories for the master server:

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

2) Define the master configuration ( mysql.cnf ) with unique server-id , enable binlog, set cache size and binlog format:

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

3) Start 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 reader on the master:

docker exec -it 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 (similar to the master, but with server-id=2 , log-bin=mysql-slave-bin , relay_log=edu-mysql-relay-bin , and 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 master_log_file and master_log_pos :

mysql> show master status;

7) On the slave, configure the master connection:

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;

8) Start the slave I/O and SQL threads:

start slave;

The replication runs asynchronously; the I/O thread pulls binlog events, writes them to a relay log, and the SQL thread executes them.

Redo Log and Binlog Review

Redo log (InnoDB) is a physical log written to a buffer and flushed to disk under three conditions: periodic flush, transaction commit, or when the buffer is half full. Binlog records logical changes for replication and incremental recovery, supporting STATEMENT, ROW, and MIXED formats.

Two‑Phase Commit

During an UPDATE , the executor reads the row, modifies it in memory, writes the change to the redo log (prepare state), generates a binlog entry, and finally commits the redo log, completing the transaction.

Sharding‑JDBC Read/Write Splitting

1) Add dependencies in pom.xml for Spring Boot, MyBatis, MySQL driver, Druid, Sharding‑JDBC, Lombok, and test libraries.

<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-web</artifactId>
</dependency>
... (other dependencies as shown in the source)

2) Configure data sources and master‑slave rules in application.properties :

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.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) Create the t_user table and corresponding Entity, Mapper, and Controller classes (shown in the source).

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

4) Verify that write requests go to the master ( ds1 ) and read requests are load‑balanced between the slaves ( ds2 , ds3 ).

MySQL Database Sharding Principles

Horizontal sharding splits rows across multiple tables/databases (e.g., based on hash or range). Vertical sharding splits columns into separate tables. Non‑stop migration can be achieved with CDC tools like Canal and full‑load import.

Sharding‑JDBC Sharding Configuration

Define logical tables and actual data nodes, then use inline sharding strategies:

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}

Distributed primary key generation can be configured (default SNOWFLAKE):

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

Example: split t_user by sex (data source) and age (table) across two databases ( ds0 , ds1 ) using inline expressions ds${sex%2} and t_user${age%2} .

Test cases demonstrate inserts routing to the correct physical tables based on the sharding rules.