Mastering MySQL Master‑Slave Replication: Principles, Delays, and Solutions

1. MySQL Master‑Slave Overview

1.1 What is MySQL master‑slave?

MySQL master‑slave involves two identical databases: a primary (master) that handles read‑write operations and a replica (slave) that serves read‑only queries.

1.2 Why use master‑slave?

On a single 4‑core, 8 GB server running MySQL 5.7, typical capacity is about 500 TPS and 10 000 QPS. When traffic spikes, separating reads from writes improves performance. Most workloads are read‑heavy, so a one‑master‑multiple‑slave setup lets the master focus on writes while slaves handle reads, reducing load, increasing availability, and providing backup.

Read‑write separation : slaves handle queries, easing master load.

High availability : a failed master can be promoted from a slave.

Data backup : slaves store copies of data.

2. Replication Mechanism

2.1 How replication works

Replication relies on the binary log (binlog), which records all changes in binary form. The master writes updates to the binlog, then streams it to the slave, which writes the received events to a relay log and replays them.

Detailed steps:

Master writes binlog : update/insert/delete statements are recorded.

Master sends binlog : a dump thread streams the binlog to the slave.

Slave writes relay log : an IO thread fetches the binlog and stores it locally.

Slave replays : an SQL thread reads the relay log and applies the changes.

2.2 Ensuring consistency

If the master and slave use different execution plans, inconsistencies can arise. Example SQL:

delete from t where a > '666' and create_time < '2022-03-01' limit 1;

When the binlog format is STATEMENT, the master may use index a while the slave uses create_time, leading to different rows being deleted.

Solutions:

Switch binlog format to ROW, which records the actual row changes, eliminating plan‑dependent mismatches.

Use MIXED format, which automatically chooses ROW when potential inconsistency is detected, otherwise STATEMENT.

Note that ROW format can increase binlog size and I/O for large transactions.

3. Replication Lag

Lag occurs when the time between the master writing to the binlog and the slave replaying it grows, causing stale reads on the slave.

3.1 Causes of lag

Single‑threaded replication on the slave.

IO thread fetching binlog efficiently, but SQL thread replaying events sequentially.

Heavy DDL/DML workload, lock contention, or long‑running transactions.

Insufficient slave hardware or network bandwidth.

Older MySQL versions that lack multi‑threaded replication.

3.2 Mitigation strategies

Monitor slave lag as a key metric; alert when lag exceeds seconds.

Cache writes to reduce read load on slaves.

Direct critical reads (e.g., inventory, payments) to the master.

Use asynchronous processing or message queues to avoid immediate slave reads.

Scale out with multiple slaves or upgrade to multi‑threaded replication.

4. Failover Scenarios

4.1 One‑master‑one‑slave

Two servers A (master) and B (slave). If A fails, B is promoted to master; after recovery, A becomes the slave.

Pros : Simple, provides read offloading and basic HA.

Cons : Limited read capacity and single point of failure if both nodes go down.

4.2 One‑master‑multiple‑slaves

One master with several slaves (B, C, D). If the master fails, one slave takes over, and the original master re‑joins as a slave after repair.

Pros : Higher read throughput and better fault tolerance.

Cons : Write throughput remains limited to the single master.