Master‑Slave MySQL with ShardingSphereJDBC: A Step‑by‑Step Spring Boot Guide

1 Overview

This article explains how to use MyBatisPlus + ShardingSphereJDBC for read‑write splitting with a master‑slave MySQL 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‑write splitting separates read and write operations: writes go to the master database, reads go to the slave database, improving concurrency when read traffic dominates.

3.2 Master‑Slave Replication

Master‑slave replication copies data from the master to the slave so that reads can be served from the slave. Although replication is not strictly required for read‑write splitting, it is essential for keeping data consistent.

3.3 Database Middleware Overview

Database middleware simplifies read‑write splitting and sharding. Two main designs are server‑side proxies (independent proxy service) and client‑side proxies (driver‑level routing).

Cobar (deprecated)

DRDS (based on Cobar)

MyCat

Atlas (Qihoo 360)

tddl (Alibaba)

Sharding‑JDBC (lightweight Java framework)

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

Inside the master container, update sources and install vim and net-tools:

cd /etc/apt
cat <<EOF > sources.list
deb http://mirrors.aliyun.com/debian/ buster main non-free contrib
deb-src http://mirrors.aliyun.com/debian/ buster main non-free contrib
deb http://mirrors.aliyun.com/debian-security buster/updates main
deb-src http://mirrors.aliyun.com/debian-security buster/updates main
deb http://mirrors.aliyun.com/debian/ buster-updates main non-free contrib
deb-src http://mirrors.aliyun.com/debian/ buster-updates main non-free contrib
deb http://mirrors.aliyun.com/debian/ buster-backports main non-free contrib
deb-src http://mirrors.aliyun.com/debian/ buster-backports main non-free contrib
EOF
apt update && apt upgrade
apt install vim net-tools

4.1.2 Modify Configuration

vim /etc/mysql/my.cnf

Add the following lines:

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

Restart MySQL after saving.

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 (recommended)

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

FLUSH TABLES WITH READ LOCK;

In another terminal, dump the data:

mysqldump -u root -p --all-databases --master-data > dbdump.db

UNLOCK TABLES;

4.1.6 View Master Status

SHOW MASTER STATUS;

Record the File and Position values for slave configuration.

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

Update sources and install vim and net-tools as done on the master.

4.2.2 Modify Configuration

vim /etc/mysql/my.cnf

Add:

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

Restart MySQL.

4.2.3 Get Slave IP

ifconfig

Result shows inet 172.17.0.3, so the replication user on the master should be [email protected].

4.2.4 Import Data (optional)

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

4.2.5 Prepare Data Source

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

4.2.6 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.7 Start Slave

START SLAVE;
SHOW SLAVE STATUS\G

For MySQL 8.0.22+ use START REPLICA; and SHOW REPLICA STATUS\G. Both IO and SQL threads should show Yes.

4.3 Test

INSERT INTO user VALUES (1, 'name');

Then query from the slave:

SELECT * FROM user;

5 Spring Boot Setup

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'

Maven example:

<dependency>
  <groupId>com.baomidou</groupId>
  <artifactId>mybatis-plus-boot-starter</artifactId>
  <version>3.5.1</version>
</dependency>
... (other dependencies omitted for brevity)

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

Key points: spring.shardingsphere.mode.type can be Memory, Standalone, or Cluster.

Define master and slave data sources with Druid.

Configure read‑write splitting rule, specifying write and read data source names and load‑balancer.

Supported load‑balancers: ROUND_ROBIN, RANDOM, WEIGHT.

5.4 Prepare 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 Test

Call http://localhost:8080/user/insert – the write goes to the master (see master screenshot).

Call http://localhost:8080/user/select – the read is served by the slave (see slave screenshot).

Thus, read‑write splitting is successfully implemented.