MySQL Master‑Slave Replication, Binlog/Redo Log Basics, and Sharding‑JDBC Read‑Write Splitting & Sharding Implementation

Overview : ShardingSphere‑JDBC is a lightweight Java framework that extends the JDBC layer, offering enhanced driver capabilities while remaining fully compatible with standard JDBC and ORM frameworks.

MySQL Master‑Slave Replication (Docker setup)

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 file ( mysql.cnf ) with a unique server-id , enable binary logging, and set the binlog format:

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=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 on the master:

docker exec -it master /bin/bash
mysql -u root -p123456
GRANT REPLICATION SLAVE ON *.* TO 'reader'@'%' IDENTIFIED BY 'reader';
FLUSH PRIVILEGES;

5) Prepare the slave server directories and configuration ( mysql.cnf ) with a different server-id and additional relay‑log settings:

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
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

6) Start the 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

7) Retrieve the master’s binary log file and position:

docker exec -it master mysql -u root -p123456 -e "show master status;"
# Example output: File: mysql-bin.000003  Position: 591

8) Configure the slave to connect to the master using the retrieved log file and position:

docker exec -it slaver mysql -u root -p123456
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;
SHOW SLAVE STATUS\G

The Slave_IO_Running and Slave_SQL_Running fields should both show Yes , indicating successful replication.

Binlog and Redo Log Review

Redo log is an InnoDB‑specific physical log written to a circular set of files; it records page changes and is flushed under three conditions (periodic thread, transaction commit, or half‑full buffer).

Binlog is a logical log at the MySQL server layer, recording SQL statements (or row changes) for replication and point‑in‑time recovery.

Three binlog formats exist: STATEMENT, ROW, and MIXED, each with trade‑offs between log size and consistency.

Two‑Phase Commit in InnoDB

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

The execution flow involves the executor fetching the row, modifying it in memory, writing a prepare‑state redo log, generating a binlog entry, and finally committing the redo log, illustrating the two‑phase commit mechanism.

MySQL Replication Principle

Slave issues CHANGE MASTER with master IP, port, credentials, and binlog position.

START SLAVE launches an I/O thread (fetches binlog) and an SQL thread (replays it).

The master streams binlog entries to the slave, which writes them to a relay log before execution.

Replication is asynchronous, so latency may appear under heavy master load.

Sharding‑JDBC Read‑Write Splitting

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

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

2) Configure application.properties with master‑slave data sources (ds1 as master, ds2 and ds3 as slaves) and load‑balance strategy:

spring.shardingsphere.datasource.names=ds1,ds2,ds3
spring.shardingsphere.datasource.ds1.url=jdbc:mysql://.../sharding-jdbc-db
spring.shardingsphere.datasource.ds2.url=jdbc:mysql://.../sharding-jdbc-db
spring.shardingsphere.datasource.ds3.url=jdbc:mysql://.../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) Define the t_user table and use MyBatis mapper/entity classes to perform CRUD operations. Example entity:

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

4) Test the read‑write splitting by invoking http://localhost:8080/api/user/save (writes go to master) and http://localhost:8080/api/user/findUsers (reads are load‑balanced across slaves).

Sharding‑JDBC Horizontal Sharding

Define logical table t_user with actual data nodes spanning two databases (ds0, ds1) and two tables (t_user0, t_user1). Use inline sharding strategies based on sex for database selection and age for table selection:

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

5) Write JUnit tests that insert users with different sex and age values to verify that records are routed to the correct database/table combination (e.g., ds1.t_user1 for odd sex and odd age).

References: ShardingSphere documentation and related tutorial videos.