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

The article begins with an overview of using MyBatisPlus and ShardingSphereJDBC to achieve read‑write separation on a MySQL master‑slave setup.

1. Environment Preparation : Install Docker, pull the MySQL image, and run two containers named master (port 3306) and slave (port 3307). Inside each container, update the apt sources, install vim and net-tools , and edit /etc/mysql/my.cnf to set server-id and binlog-do-db=test for the master, and server-id=2 with replicate-do-db=test for the slave.

2. Master Configuration : Create a replication user with mysql_native_password , grant REPLICATION SLAVE privileges, and record the master’s binlog file and position using SHOW MASTER STATUS . Optionally export existing data with mysqldump and import it on the slave.

3. Slave Configuration : Perform similar source updates, set server-id=2 , and configure the slave to connect to the master using CHANGE REPLICATION SOURCE TO (or CHANGE MASTER TO for MySQL <8.0.22). Start the replication with START SLAVE (or START REPLICA ) and verify that IO and SQL threads are running.

4. Spring Boot Project : Create a new Spring Boot project and add the following Gradle dependencies (or Maven equivalents): com.alibaba:druid:1.2.10 , com.baomidou:mybatis-plus-boot-starter:3.5.1 , org.freemarker:freemarker:2.3.31 , com.baomidou:mybatis-plus-generator:3.5.2 , and org.apache.shardingsphere:sharding-jdbc-spring-boot-starter:5.1.1 . Use the provided generator class to auto‑generate entity, mapper and service code.

5. Configuration File (application.yml) : Define spring.shardingsphere.mode.type=Memory , list the two data sources ( master and slave ) with Druid connection details, and configure a read‑write‑splitting rule named random that uses type: Static , write-data-source-name=master , read-data-source-names=slave , and a round_robin load balancer. Enable SQL logging with spring.shardingsphere.props.sql-show=true .

6. Controller Example : A simple @RestController provides /user/insert (writes to the master) and /user/select (reads from the slave) endpoints, demonstrating that write operations go to the master database while reads are routed to the slave.

7. Testing : Access http://localhost:8080/user/insert to insert a record (verified in the master) and http://localhost:8080/user/select to retrieve it (served from the slave), confirming that read‑write splitting works as intended.