Master‑Slave MySQL Replication & Read‑Write Splitting with ShardingJDBC: A Step‑by‑Step Guide

MySQL Master‑Slave Replication

When using a single monolithic database, read and write load concentrates on one server, creating performance pressure and a single point of failure that can cause data loss.

Solution

Deploy a master and one or more slaves. Write operations (INSERT, UPDATE, DELETE) go to the master, while read operations (SELECT) go to the slaves, achieving read‑write separation.

How replication works

MySQL uses asynchronous binary logs (binlog). The master writes data changes to the binlog; the slave copies the binlog to its relay log and replays the events, keeping the slave’s data consistent with the master.

Setup steps

Configure the master: enable binlog, set a unique server-id, and restart MySQL.

Create a replication user on the master and grant REPLICATION SLAVE privileges.

On the master, run SHOW MASTER STATUS to obtain the File and Position values.

Configure each slave: set a different server-id, edit /etc/my.cnf to enable binlog, restart MySQL, and run

CHANGE MASTER TO master_host='192.168.150.100', master_user='masterDb', master_password='Master@123456', master_log_file='mysql-bin.000010', master_log_pos=68479;

then START SLAVE;.

Verify the slave with SHOW SLAVE STATUS\G; both Slave_IO_Running and Slave_SQL_Running should be Yes.

Common issues such as duplicate server-id, mismatched server_uuid, or synchronization failures can be resolved with commands like

STOP SLAVE; SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1; START SLAVE;

.

Read‑Write Splitting with ShardingJDBC

Sharding‑JDBC is a lightweight Java framework that provides read‑write splitting without additional deployment, acting as an enhanced JDBC driver compatible with JPA, Hibernate, MyBatis, and other ORM tools.

Dependency

<dependency>
    <groupId>org.apache.shardingsphere</groupId>
    <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
    <version>4.0.0-RC1</version>
</dependency>

Configuration (application.yml)

spring:
  shardingsphere:
    datasource:
      names: master,slave
      master:
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        url: jdbc:mysql://192.168.150.100:3306/db_test?useUnicode=true&characterEncoding=utf-8&useSSL=false
        username: root
        password: 123456
      slave:
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        url: jdbc:mysql://192.168.150.101:3306/db_test?useUnicode=true&characterEncoding=utf-8&useSSL=false
        username: root
        password: 123456
    masterslave:
      load-balance-algorithm-type: round_robin
      name: dataSource
      master-data-source-name: master
      slave-data-source-names: slave
    props:
      sql:
        show: true
    main:
      allow-bean-definition-overriding: true

Testing

Invoke a write API and check the master logs; invoke a read API and check the slave logs. The logs confirm that ShardingJDBC routes queries to the appropriate data source.