Understanding MySQL Master‑Slave Replication: Core Principles, Modes, and Latency Mitigation

MySQL master‑slave replication copies DDL and DML changes from a primary server to one or more replicas, enabling read/write separation, fault tolerance, and backup. The process follows a push‑pull hybrid model: the replica’s I/O thread pulls binlog files from the primary (pull), while the primary continuously pushes new binlog events to the replicas (push).

Core Replication Steps

When the primary executes a write operation (INSERT, UPDATE, DELETE, REPLACE), it records the change in the binlog.

The replica’s I/O thread requests a binlog dump from the primary; the primary’s Log Dump thread streams the binlog to the replica.

The replica’s I/O thread writes the received binlog to a local Relay Log.

The replica’s SQL thread reads the Relay Log, parses each entry into SQL statements, and executes them, ensuring eventual data consistency.

Thus, replication combines pull (initial binlog dump) and push (real‑time binlog streaming) mechanisms.

Replication Modes

Asynchronous mode : The primary returns results to the client immediately after writing to the binlog, without waiting for replicas to acknowledge. This yields high performance but risks data loss if the primary fails before replication.

Full‑sync mode : The primary waits for all replicas to confirm receipt of each binlog event before responding to the client, guaranteeing strong consistency at the cost of lower throughput.

Semi‑sync mode : The primary waits for at least one configurable replica to acknowledge the binlog event, balancing performance and consistency.

Binlog Formats

STATEMENT : Records the original SQL statement. It is the most compact and fastest but can cause inconsistencies with nondeterministic functions such as NOW(), UUID(), or RAND().

ROW (default): Records before‑and‑after images of each row changed, ensuring strong consistency but generating larger logs, especially for bulk updates.

MIXED : Uses STATEMENT by default and automatically switches to ROW when nondeterministic statements are detected.

Replication Lag and Mitigation

Asynchronous replication can lead to noticeable lag, manifested as stale data on replicas. Major contributors to lag include:

Large transactions : Bulk updates generate massive binlog entries, especially in ROW mode. Splitting large transactions into smaller batches and inserting brief pauses (e.g., SLEEP()) can reduce lag.

Primary server overload : High write throughput (e.g., tens of thousands of TPS during peak periods) delays binlog generation and transmission. Solutions include sharding the primary, scaling up resources, or distributing write load.

Replica server overload : Heavy read workloads or resource‑intensive analytics on replicas slow down Relay Log processing. Adding more replicas, optimizing queries, or offloading analytics to specialized stores (Redis, Elasticsearch, ClickHouse, Doris) can alleviate pressure.

Network bandwidth and latency : Insufficient network capacity or high latency between primary and replicas exacerbates lag. Deploying primary and replicas within the same data center or high‑speed network and increasing bandwidth are essential.

By understanding these mechanisms and applying the suggested mitigations, practitioners can design more reliable MySQL replication architectures.