Toscalability of database performance is critical in high-concurrency environments, especially for internet-scale applications. A common approach involves setting up one master and multiple slave instances, with write operations targeting only the master and read operations distributed across slaves. This architecture relies on binlog-based replication and is typically complemanted by a read-write splitting layer in the application tier.

Replication Mechanism operates as follows:

Write operations (INSERT, UPDATE, DELETE) on the master are recorded in the binary log (binlog) in strict order. Each connected slave initiates an I/O thread that connects to the master's binary log dump thread. When the master's binlog updates, the dump thread pushes the relevant events to each slave. The slave’s I/O thread saves these events into its own relay log. The SQL thread on the slave reads the relay log and applies the changes locally,保持数据一致性.

Practical Setup Example (one master, two slaves):

Assuming three Linux servers with IPs:

Master: 192.168.0.104 Slave1: 192.168.0.106 Slave2: 192.168.0.107

Master Configuration

In the MySQL console:

GRANT REPLICATION SLAVE ON *.* TO 'repl_user'@'192.168.0.106' IDENTIFIED BY 'SecurePass2024!';
GRANT REPLICATION SLAVE ON *.* TO 'repl_user'@'192.168.0.107' IDENTIFIED BY 'SecurePass2024!';
FLUSH PRIVILEGES;

In /etc/my.cnf:

[mysqld]
log-bin = mysql-bin
server-id = 104
binlog-do-db = shopdb
expire-logs-days = 7

Restart MySQL, then verify binlog status:

SHOW MASTER STATUS\G

Slave Configuration

On each slave, in /etc/my.cnf set unique server IDs:

[mysqld]
server-id = 106  # or 107 for other slave

After restarting MySQL, configure replication from the master:

CHANGE MASTER TO
  MASTER_HOST = '192.168.0.104',
  MASTER_USER = 'repl_user',
  MASTER_PASSWORD = 'SecurePass2024!',
  MASTER_LOG_FILE = 'mysql-bin.000001',
  MASTER_LOG_POS = 154,
  MASTER_PORT = 3306;

START SLAVE;

SHOW SLAVE STATUS\G;

Verify successful replication by checking that both Slave_IO_Running and Slave_SQL_Running are "Yes".

Data Synchronization Test

Execute the following DDL on the master to create a table:

CREATE TABLE product_catalog (
  id VARCHAR(32) PRIMARY KEY,
  name VARCHAR(512) DEFAULT NULL,
  price DECIMAL(10,2) DEFAULT 0,
  quantity INT DEFAULT 0,
  description TEXT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

Confirm the table appears on both slaves within seconds.

Implementing Read-Write Splitting

While ad hoc routing logic (e.g., AOP-based method-name analysis) is feasible, using a mature middleware is more maintainable. Apache ShardingSphere-JDBC offers client-side read-write splitting as a lightweight JDBC extension.

Spring Boot Integration Example

Dependencies (Maven):

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

Configuration in application.yml:

spring:
  shardingsphere:
    datasource:
      names: master,slave0,slave1
      master:
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        url: jdbc:mysql://192.168.0.104:3306/shopdb?useSSL=false&serverTimezone=UTC
        username: app_user
        password: AppPass2024!
      slave0:
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        url: jdbc:mysql://192.168.0.106:3306/shopdb?useSSL=false&serverTimezone=UTC
        username: app_user
        password: AppPass2024!
      slave1:
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        url: jdbc:mysql://192.168.0.107:3306/shopdb?useSSL=false&serverTimezone=UTC
        username: app_user
        password: AppPass2024!
    props:
      sql-show: true
    sharding:
      master-slave-rules:
        ds:
          master-data-source-name: master
          slave-data-source-names: slave0,slave1
      load-balance-algorithm-type: ROUND_ROBIN

This setup ensures:

INSERT/UPDATE/DELETE statements route to the master. SELECT statements are distributed across slaves using round-robin.

Caveats

Asynchronous replication introduces replication lag — reads against a slave may not see latest writes immediately after a commit. Failing to manually handle transactions across architectures may lead to stale reads or inconsistent business outcomes. Database failover is not automatic here; external mechanisms (e.g., ProxySQL, MHA) are required for high availability beyond basic redundency.