MySQL Practical Guide #17: Building a High‑Availability Service with Master‑Slave Replication, Read‑Write Splitting, and Load Balancing

1. Master‑Slave Replication Theory and Configuration

1.1 Replication Principle

MySQL master‑slave replication works asynchronously via the binary log (binlog). The process has three stages: binlog generation on the master, relay log forwarding by the slave I/O thread, and SQL thread replay on the slave.

Master → Slave
    ↓
1. Binary log recording (master)
    ↓
2. Relay log forwarding (slave I/O thread)
    ↓
3. SQL thread replay (slave)

Detailed workflow:

Binary log generation: The master records all data changes (INSERT, UPDATE, DELETE, etc.) as events in the binlog.

Log transfer: The slave I/O thread connects to the master, reads binlog events and writes them to a local relay log.

Data replay: The slave SQL thread reads the relay log and executes the SQL events in order, achieving data synchronization.

1.2 Configuration Steps

Master my.cnf

[mysqld]
server-id = 1               # unique server ID
log_bin = mysql-bin         # enable binary log
binlog_format = ROW        # recommended ROW format
binlog_do_db = your_database   # databases to replicate
expire_logs_days = 7       # log retention days

Slave my.cnf

[mysqld]
server-id = 2               # must differ from master
relay_log = mysql-relay-bin # relay log file
read_only = 1               # set slave to read‑only
log_slave_updates = 1       # record updates for chained replication

Configuration commands

-- Create replication user on master
CREATE USER 'repl'@'%' IDENTIFIED BY 'password';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';

-- Show master status (record File and Position)
SHOW MASTER STATUS;

-- Configure master info on slave
CHANGE MASTER TO
    MASTER_HOST='master_host',
    MASTER_USER='repl',
    MASTER_PASSWORD='password',
    MASTER_LOG_FILE='mysql-bin.000001',
    MASTER_LOG_POS=107;

-- Start replication
START SLAVE;

-- Check replication status
SHOW SLAVE STATUS\G

2. Read‑Write Splitting Middleware Selection

2.1 Common Middleware Comparison

2.2 ProxySQL Configuration

-- Add MySQL servers to ProxySQL
INSERT INTO mysql_servers(hostgroup_id, hostname, port) VALUES
(10, 'master_host', 3306),   -- write group
(20, 'slave1_host', 3306),   -- read group
(20, 'slave2_host', 3306);

-- Configure monitoring user
INSERT INTO mysql_users(username, password, default_hostgroup)
VALUES ('app_user', 'password', 10);

-- Define read‑write routing rules
INSERT INTO mysql_query_rules(rule_id, active, match_digest, destination_hostgroup, apply) VALUES
(1, 1, '^SELECT.*FOR UPDATE', 10, 1),   -- SELECT FOR UPDATE to master
(2, 1, '^SELECT', 20, 1),               -- read queries to slaves
(3, 1, '.*', 10, 1);                     -- other statements to master

-- Apply configuration
LOAD MYSQL SERVERS TO RUNTIME;
LOAD MYSQL USERS TO RUNTIME;
LOAD MYSQL QUERY RULES TO RUNTIME;
SAVE MYSQL SERVERS TO DISK;
SAVE MYSQL USERS TO DISK;
SAVE MYSQL QUERY RULES TO DISK;

3. Load‑Balancing Strategies

3.1 Strategy Types

1. Round Robin
2. Weighted
3. Least Connections
4. Response Time
5. Source IP Hash

3.2 Detailed Strategies

1. Round Robin

-- Example: set equal weight for read group
UPDATE mysql_servers SET weight = 1 WHERE hostgroup_id = 20;

Principle: Assign requests to each slave in order.

Advantages: Simple and fair; each server receives the same number of requests.

Disadvantages: Ignores differences in server load.

2. Weighted

-- Assign higher weight to more powerful servers
UPDATE mysql_servers SET weight = 100 WHERE hostname = 'slave1';
UPDATE mysql_servers SET weight = 50  WHERE hostname = 'slave2';

Principle: Servers with higher performance receive a larger weight and thus more requests.

Applicable scenario: Environments where server configurations are uneven.

3. Least Connections

-- ProxySQL automatically uses least‑connections algorithm
UPDATE mysql_servers SET weight = 1;

Principle: New connections go to the server with the fewest current connections.

Advantages: Dynamically balances load.

Disadvantages: Does not consider connection processing time.

4. Response‑Time‑Based

-- Enable response‑time monitoring
UPDATE global_variables SET variable_value='true'
WHERE variable_name='mysql-monitor_slave_lag_when_null';

Principle: Monitor server response times and prefer faster servers.

Advantages: Improves user experience.

Disadvantages: Requires continuous monitoring and adds overhead.

4. Replication Lag Solutions

4.1 Causes of Replication Lag

1. Network latency between master and slaves
2. Hardware differences (slave weaker than master)
3. Large transactions on master
4. Single‑threaded replication (SQL thread)
5. Lock contention during replay

4.2 Mitigation Methods

Solution 1: Parallel Replication

STOP SLAVE;
SET GLOBAL slave_parallel_type = 'LOGICAL_CLOCK';
SET GLOBAL slave_parallel_workers = 8;
START SLAVE;
SHOW VARIABLES LIKE 'slave_parallel%';

Solution 2: Semi‑Synchronous Replication

-- Install plugin on master
INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';
SET GLOBAL rpl_semi_sync_master_enabled = 1;
SET GLOBAL rpl_semi_sync_master_timeout = 1000;  -- ms

-- Install plugin on slave
INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';
SET GLOBAL rpl_semi_sync_slave_enabled = 1;
STOP SLAVE IO_THREAD;
START SLAVE IO_THREAD;

Solution 3: Lag Monitoring and Alerting

SELECT
    slave_host,
    TIMESTAMPDIFF(SECOND, last_update, NOW()) AS delay_seconds,
    (slave_lag > 60) AS is_critical
FROM replication_monitor
WHERE slave_lag > 10;  -- alert if lag > 10 seconds

Solution 4: Read‑Write Splitting Optimizations

-- Auto‑remove lagging slaves in ProxySQL
INSERT INTO mysql_replication_hostgroups (writer_hostgroup, reader_hostgroup, comment)
VALUES (10, 20, 'cluster1');
UPDATE mysql_servers SET max_replication_lag = 5;  -- seconds

Solution 5: Application‑Level Handling

// Java example: Read‑Your‑Writes
public class ReadWriteSeparation {
    // After a write, force reads from master for a short window
    public Object readAfterWrite(String key, long timeoutMs) {
        long writeTime = getLastWriteTime(key);
        long now = System.currentTimeMillis();
        if (now - writeTime < timeoutMs) {
            return readFromMaster(key);
        } else {
            return readFromSlave(key);
        }
    }
}

4.3 Preventive Measures

Hardware Optimization

Ensure slave hardware is not weaker than the master.

Use SSD storage to reduce I/O latency.

Guarantee sufficient network bandwidth between master and slaves.

Database Tuning

-- Optimize slave parameters
SET GLOBAL innodb_flush_log_at_trx_commit = 2;
SET GLOBAL sync_binlog = 0;
SET GLOBAL innodb_buffer_pool_size = 70% of system memory;
PURGE BINARY LOGS BEFORE DATE_SUB(NOW(), INTERVAL 7 DAY);

Architecture Optimization

Adopt multi‑level replication topology.

Avoid large transactions on critical business tables.

Consider caching hot data.

Key Takeaways

Master‑slave replication is the foundation of MySQL high‑availability.

Read‑write splitting can increase system throughput.

Load‑balancing strategy should match business characteristics.

Replication lag requires multi‑dimensional mitigation.

Monitoring and alerting are essential for stable operation.