MySQL Master‑Slave Replication and Read‑Write Splitting with Sharding‑JDBC

When a project uses a single monolithic database, write and read traffic all hit one server, causing high load and a single point of failure. To address these issues, the article introduces MySQL master‑slave replication and read‑write separation.

1. MySQL Master‑Slave Replication Overview

Replication is an asynchronous process based on MySQL's binary log (BINLOG). The master writes data changes to the binary log; the slave copies this log to its relay log and replays the events to keep its data consistent with the master.

2. Master Configuration

Edit vim /etc/my.cnf to enable binary logging and set a unique server ID:

log-bin=mysql-bin  # enable binary log
server-id=100       # unique ID for this server

Restart MySQL using any of the following commands:

net stop mysql; net start mysql;
systemctl restart mysqld;
service mysqld restart

Create a replication user with appropriate privileges:

GRANT REPLICATION SLAVE ON *.* TO 'masterDb'@'%' IDENTIFIED BY 'Master@123456';
FLUSH PRIVILEGES;

Check the master status (do not perform further writes before noting the File and Position values):

SHOW MASTER STATUS;

3. Slave Configuration

On the slave server, edit vim /etc/my.cnf to set a different server-id (e.g., 101) and enable binary logging if needed.

Restart MySQL using the same commands as for the master.

Configure the slave to connect to the master and start replication:

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;
START SLAVE;

Verify the slave status; both Slave_IO_Running and Slave_SQL_Running should be Yes :

SHOW SLAVE STATUS\G;

4. Common Pitfalls and Fixes

Duplicate server_id values cause replication failure – ensure each server has a unique ID.

Identical server_uuid on cloned machines – edit vim /var/lib/mysql/auto.cnf to generate a new UUID.

Synchronization errors (e.g., missing user) – skip the offending transaction: STOP SLAVE; SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1; START SLAVE;

5. Implementing Read‑Write Splitting with Sharding‑JDBC

Sharding‑JDBC is a lightweight Java framework that provides read‑write splitting without additional deployment.

Add the Maven dependency:

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

Configure data sources in 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

Run a write operation (e.g., INSERT) and a read operation (SELECT) and observe the logs to confirm that writes go to the master and reads are served by the slave.

After successful testing, the project achieves transparent read‑write separation using Sharding‑JDBC.