Guide to Setting Up MySQL Master‑Slave Replication with MyBatis‑Plus, ShardingSphereJDBC and Spring Boot for Read‑Write Splitting

This article provides a step‑by‑step guide for establishing a MySQL master‑slave replication environment and using it for read‑write splitting in a Spring Boot project.

Environment preparation : Docker is used to create two MySQL containers (master and slave) and install necessary tools such as vim and net-tools .

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

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

Master configuration : Edit /etc/mysql/my.cnf to enable binlog and set a unique server ID.

[mysqld]
server-id=1            # globally unique ID
binlog-do-db=test      # database to replicate

Slave configuration : Set a different server ID and specify the database to replicate.

[mysqld]
server-id=2            # must differ from master
replicate-do-db=test   # same as master

Replication user (optional but 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';

Data backup (optional) using mysqldump and restoring on the slave.

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

Configure replication on the 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;
START SLAVE;
SHOW SLAVE STATUS\G

Spring Boot project : Create a new Spring Boot application and add the following 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:sharding-jdbc-spring-boot-starter:5.1.1'

Code generator (FastAutoGenerator) example:

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();
    }
}

ShardingSphere configuration (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-name: slave
            load-balancer-name: round_robin
        load-balancers:
          round_robin:
            type: ROUND_ROBIN
    props:
      sql-show: true

Controller example demonstrating read and write endpoints:

@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());
    }
}

Testing : Access http://localhost:8080/user/insert to perform a write (executed on the master) and http://localhost:8080/user/select to perform a read (executed on the slave), confirming that read‑write splitting works as expected.