Master‑Slave MySQL Replication: Architecture, Principles, and Step‑by‑Step Setup

Overview

MySQL master‑slave (master‑replica) replication is a widely used database synchronization method that provides data redundancy, read‑write separation, and backup recovery. The primary (master) handles write operations and records changes in binary logs (binlog), while replicas (slaves) replay these logs to stay consistent.

Typical Topologies

Depending on business scale and disaster‑recovery requirements, several common architectures are used:

Single master with one or multiple slaves (classic one‑master‑many‑slaves).

Master‑master (dual‑master) where two servers act as each other's master, usually with one active writer and the other as hot standby, often using Keepalived for VIP failover.

Cascading replication (master‑slave‑slave) to reduce the master’s I/O load when the number of slaves exceeds 5‑10; the master replicates to a few intermediate slaves, which in turn replicate to additional slaves.

Data Flow and Components

The replication chain can be visualized as:

Client → Proxy (e.g., ProxySQL/MySQL Router) → Write Request → Master → binlog → Dump Thread → Network → Slave IO Thread → Relay Log → Slave SQL Thread → Apply to Slave Data

Key components:

Master : Executes transactions, writes redo log and binlog.

Slave IO Thread : Pulls binlog events from the master and writes them to the relay log.

Slave SQL Thread : Reads the relay log, parses events, and replays them on the slave.

Replication Process (Asynchronous Mode)

The master executes a transaction (INSERT/UPDATE/DELETE).

InnoDB commits the transaction, writes to redo log, and flushes to disk.

After commit, the transaction is recorded in the binlog.

The slave’s IO Thread fetches binlog events from the master and stores them in the relay log.

The slave’s SQL Thread reads the relay log, parses each event, and executes the corresponding statements locally.

Successful execution updates the slave’s data files, completing the replication cycle.

Implementation Tips

To achieve transparent read‑write separation, a proxy layer such as ShardingSphere or MyCat can be introduced, routing read queries to slaves and write queries to the master without modifying application code.

Conclusion

Understanding the architecture, data flow, and step‑by‑step replication process enables engineers to design robust MySQL deployments that balance write performance, read scalability, and high availability.