MySQL Master-Slave Replication: Sync Principles, Delay Fixes, Read-Write Splitting & Failover

What Is MySQL Master-Slave Replication?

Master-slave replication builds one or more replicas that continuously sync data changes from the primary database, keeping primary and replica data consistent. The underlying mechanism relies on the binary log (binlog) which records all data modifications. In simple terms: the primary handles read and write requests, while replicas pull the primary's binlog and replay the SQL changes to create an identical data copy.

Two core values: read-write separation to offload query pressure from the primary, and data redundancy so a replica can be promoted if the primary fails, improving availability.

Why Production Environments Need Master-Slave Replication

A single MySQL instance has two fatal weaknesses: all read and write load hits one server, causing CPU/IO saturation under high concurrency; and disk failure or server crash leads to data loss and business interruption. Master-slave architecture addresses both:

Read requests are routed to replicas, leaving the primary to handle only writes, boosting overall concurrency.

Multiple data copies enable disaster recovery and support primary-replica failover.

Backup tasks run on replicas, avoiding table locks that would impact the primary.

Provides a foundation for advanced architectures like sharding and MGR clusters.

Core truth: Master-slave is not a silver bullet — it has inherent replication lag. Ignoring lag in read-write splitting scenarios causes the notorious bug where a query immediately after a write returns stale data.

Applicable Scenarios and Roles

Suitable scenarios: read-heavy workloads with many list/report queries; need for database disaster recovery; offline backup and analytics jobs that must not affect the primary; architectural evolution toward sharding or MGR.

Roles: Master — write entry point, generates binlog; Slave (Replica) — pulls binlog, replays changes, stays in sync. Common topologies: one master with one replica, or one master with multiple replicas. Multi-master and circular replication are rarely used in production due to high risk.

Complete Replication Process: Three Threads

MySQL replication is asynchronous and involves three core threads working together: the primary's dump thread, the replica's I/O thread, and the replica's SQL thread.

Step-by-step flow:

The replica establishes a network connection to the primary and sends a binlog sync request, recording the sync position.

The primary starts a dump thread, reads its local binlog files, and pushes them to the replica in real time.

The replica's I/O thread receives the binlog events and writes them to the local relay log.

The replica's SQL thread reads the relay log and replays the contained data-change SQL statements on the replica.

After execution, the replica updates its sync position (which binlog file and offset it has reached) for the next continuation.

Key point: the primary returns success to the client as soon as it writes to its binlog; by default it does not wait for the replica to receive the data — this is traditional asynchronous replication.

Three Replication Modes: Async, Semi-Sync, Lossless Semi-Sync

1. Asynchronous Replication (Default)

The primary commits the transaction, writes binlog, and returns success immediately without waiting for the replica. Pros: best performance. Cons: if the primary crashes, committed transactions may not have reached the replica, causing data loss.

2. Semi-Synchronous Replication (semi-sync)

After committing, the primary waits for at least one replica to acknowledge receipt of the binlog before returning success to the client. Note: this only guarantees the log has arrived at the replica, not that the replica has finished replaying it . Pros: greatly reduces data loss risk. Cons: primary write latency increases due to network wait, reducing write throughput.

3. Lossless Semi-Sync (rpl_semi_sync_master_wait_for_slave_count)

Enhanced semi-sync that requires at least N replicas to acknowledge receipt, suitable for core transactional business (orders, payments). Selection guideline: async for ordinary internal apps; semi-sync for core transactional workloads.

Master-Slave Delay: Root Causes and Troubleshooting

Replication lag means the primary has committed but the replica's replay lags behind, causing temporary inconsistency.

High-Frequency Causes

Replica hardware lower than primary — CPU/disk IO cannot keep up with replay load.

Large transactions or bulk DML — primary commits massive changes instantly, but the replica's single SQL thread replays sequentially and falls behind.

Replica runs extra analytical queries or reporting jobs, contending for resources and blocking relay log replay.

Primary uses row-format binlog — each row change generates large log volume, increasing network transfer pressure.

Tables lack primary keys — in row mode, updates require full table scans to locate rows, drastically slowing replay.

Troubleshooting Commands

Run show slave status\G and observe Seconds_Behind_Master for lag in seconds. Compare Relay_Master_Log_File, Read_Master_Log_Pos, and Exec_Master_Log_Pos to determine whether the bottleneck is network transfer (I/O thread lag) or SQL replay (SQL thread lag).

Read-Write Splitting Implementation and Pitfalls

Core Rule

All writes go to the primary; read queries are routed to replicas.

Critical pitfall: writing then immediately querying the replica returns empty results because the replica hasn't replayed the change yet.

Solutions: (1) force strongly consistent reads to the primary; (2) introduce a brief business-level delay before reading from replica; (3) use a cache as a fallback.

Master-Slave Failover Solutions

Manual failover: stop traffic, wait for replication to catch up, update application connection strings, promote replica to new primary.

Automatic failover: tools like MHA or Orchestrator detect failure, elect a new primary, perform switchover, and update routing automatically.

Crucial: before any switchover, verify that binlog sync is complete to prevent data loss.

Five Common Production Pitfalls

Pitfall 1: Replica allows writes. Manual writes on the replica cause data conflicts and break replication. Production replicas must have read_only=ON.

Pitfall 2: Ignoring lag, sending all reads to replica. New order submitted, immediate query for order details hits replica and returns empty — intermittent bugs.

Pitfall 3: Large transactions cause prolonged lag. Primary updates hundreds of thousands of rows in one transaction; replica replay piles up, lag spikes.

Pitfall 4: Missing primary keys, row-mode replay performance collapse. Row-format binlog updates without a primary key trigger full table scans on the replica, replay speed plummets.

Pitfall 5: Using replica for heavy analytical queries. Long-running scans consume IO, block the SQL thread, and continuously increase replication lag.

Key Takeaways

Master-slave replication relies on binlog + relay log, coordinated by three threads: dump, I/O, SQL.

Async replication offers high performance but risks data loss; semi-sync waits for replica acknowledgment, improving durability.

Most common lag causes: insufficient replica resources, large transactions, missing primary keys, heavy queries on replica.

Read-write splitting: writes to primary, reads to replica; strongly consistent reads must hit the primary.

Replicas should default to read_only=ON to prevent accidental writes.

Failover priority: ensure binlog sync is complete, guarantee no data loss.