Implementing Read/Write Splitting with MyBatisPlus, ShardingSphereJDBC, and MySQL Master‑Slave Replication in Spring Boot

1 Overview

This article explains how to use MyBatisPlus + ShardingSphereJDBC for read/write splitting and MySQL for a primary‑secondary replication setup.

2 Environment

OpenJDK 17.0.3

Spring Boot 2.7.0

MyBatis Plus 3.5.1

MyBatis Plus Generator 3.5.2

Druid 1.2.10

ShardingSphereJDBC 5.1.1

MySQL 8.0.29 (Docker)

3 Basic Theory

3.1 Read/Write Splitting

Read operations are directed to the slave database, while write operations go to the master, improving concurrency when reads dominate.

3.2 Master‑Slave Replication

The master writes changes to the binary log ( binlog ); the slave reads the log via its I/O thread, replays it with the SQL thread, and stays synchronized.

3.3 Database Middleware Overview

Middleware abstracts read/write splitting and sharding. Common options include server‑side proxies (e.g., Cobar, DRDS) and client‑side proxies (e.g., Sharding‑JDBC, MyCat).

4 MySQL Master‑Slave Replication Setup

4.1 Master Operations

4.1.1 Pull Image and Create Container

docker pull mysql
docker run -itd -p 3306:3306 -e MYSQL_ROOT_PASSWORD=123456 --name master mysql
docker exec -it master /bin/bash

4.1.2 Modify Configuration

vim /etc/mysql/my.cnf

Add the following lines:

[mysqld]
server-id=1
binlog-do-db=test

4.1.3 Prepare Data Source

CREATE DATABASE test;
USE test;
CREATE TABLE user(
  id BIGINT PRIMARY KEY,
  name VARCHAR(30) NOT NULL
);

4.1.4 Create Replication User

CREATE USER 'repl'@'172.17.0.3' IDENTIFIED WITH mysql_native_password BY '123456';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'172.17.0.3';

4.1.5 (Optional) Backup Data

FLUSH TABLES WITH READ LOCK;
mysqldump -u root -p --all-databases --master-data > dbdump.db;
UNLOCK TABLES;

4.1.6 View Master Status

SHOW MASTER STATUS;

4.2 Slave Operations

4.2.1 Pull Image and Create Container

docker pull mysql
docker run -itd -p 3307:3306 -e MYSQL_ROOT_PASSWORD=123456 --name slave mysql
docker exec -it slave /bin/bash

4.2.2 Modify Configuration

vim /etc/mysql/my.cnf

Add:

[mysqld]
server-id=2
replicate-do-db=test

4.2.3 Get Slave IP

ifconfig

Result: inet 172.17.0.3

4.2.4 Import Data (optional)

mysqldump -u root -p --all-databases < dbdump.db

4.2.5 Set Master on Slave

CHANGE REPLICATION SOURCE TO
source_host='172.17.0.2',
source_user='repl',
source_password='123456',
source_log_file='binlog.000003',
source_log_pos=594;

4.2.6 Start Slave

START SLAVE;
SHOW SLAVE STATUS\G

4.3 Test

Insert a row on the master and verify it appears on the slave.

5 Spring Boot Environment

5.1 Create Project and Add Dependencies

implementation 'com.alibaba:druid:1.2.10'
implementation 'com.baomidou:mybatis-plus-boot-starter:3.5.1'
implementation 'org.freemarker:freemarker:2.3.31'
implementation 'com.baomidou:mybatis-plus-generator:3.5.2'
implementation 'org.apache.shardingsphere:shardingsphere-jdbc-core-spring-boot-starter:5.1.1'

5.2 Use Generator

import com.baomidou.mybatisplus.generator.FastAutoGenerator;
import com.baomidou.mybatisplus.generator.engine.FreemarkerTemplateEngine;

public class Generator {
    public static void main(String[] args) {
        FastAutoGenerator.create("jdbc:mysql://localhost:3306/test", "root", "123456")
            .globalConfig(builder -> builder.author("author").outputDir(System.getProperty("user.dir") + "/src/main/java"))
            .packageConfig(builder -> builder.parent("com.example.demo").moduleName("user"))
            .strategyConfig(builder -> builder.addInclude("user").entityBuilder().enableLombok().disableSerialVersionUID())
            .templateEngine(new FreemarkerTemplateEngine())
            .execute();
    }
}

5.3 Configuration File (application.yml)

spring:
  shardingsphere:
    mode:
      type: Memory
    datasource:
      names: master,slave
      master:
        type: com.alibaba.druid.pool.DruidDataSource
        url: jdbc:mysql://127.0.0.1:3306/test
        username: root
        password: 123456
      slave:
        type: com.alibaba.druid.pool.DruidDataSource
        url: jdbc:mysql://127.0.0.1:3306/test
        username: root
        password: 123456
    rules:
      readwrite-splitting:
        data-sources:
          random:
            type: Static
            props:
              write-data-source-name: master
              read-data-source-names: slave
            load-balancer-name: round_robin
        load-balancers:
          round_robin:
            type: ROUND_ROBIN
    props:
      sql-show: true

The configuration defines the mode, data sources, read/write splitting rule, load‑balancer, and enables SQL logging.

5.3.1 Mode

Memory mode keeps metadata in the process; Standalone and Cluster modes provide persistence and clustering.

5.3.2 Data Source Configuration

Both master and slave use Druid connection pools with the same JDBC URL (adjust ports as needed).

5.3.3 Read/Write Splitting Rule

Defines a static data‑source named random with master as write and slave as read, using a round‑robin load balancer.

5.3.4 Additional Properties

Only sql-show=true is set to print executed SQL.

5.4 Controller

@RestController
@RequestMapping("/user")
@RequiredArgsConstructor(onConstructor = @__(@Autowired))
public class UserController {
    private final UserServiceImpl userService;

    @GetMapping("/select")
    public User select() {
        return userService.getById(1);
    }

    @GetMapping("/insert")
    public boolean insert() {
        return userService.saveOrUpdate(User.builder().id(3L).name("name3").build());
    }
}

6 Testing

Calling http://localhost:8080/user/insert writes to the master; calling http://localhost:8080/user/select reads from the slave, confirming successful read/write separation.