Comprehensive Guide to MySQL Master‑Slave Replication (Principles, Architecture, Implementation)

MySQL Master‑Slave Replication Overview

MySQL master‑slave replication is a widely used high‑availability and read/write‑separation solution. The primary (master) receives all write requests and records each INSERT, UPDATE, or DELETE operation in the binary log (binlog). One or more replicas (slaves) continuously read the binlog, write the events to a local relay log, and replay the changes to stay consistent, allowing them to serve read queries.

Typical Architecture

A common deployment follows a one‑master‑multiple‑slaves pattern, with the application layer routing write traffic to the master and read traffic to the replicas.

Key Roles

Master : receives write requests and generates the binlog.

Slave/Replica : reads the binlog, writes it to a relay log, and replays the changes.

Replication channel : transports binlog events from master to slaves.

Application layer : can separate read and write traffic based on business needs.

Data Flow Steps

Master executes INSERT / UPDATE / DELETE and writes the changes to the binlog.

Slave I/O thread connects to the master and requests binlog events.

Master’s binlog‑dump thread streams events to the slave.

Slave writes received events into its relay log.

Slave’s SQL/applier thread reads the relay log and replays the changes on the replica.

The above sequence ensures reliable data synchronization between the primary and its replicas.

Change Types Recorded in Binlog

<ol><li>INSERT</li><li>UPDATE</li><li>DELETE</li></ol>

ASCII Architecture Illustration

<ol><li>应用系统</li><li>│</li><li>┌──────────────┼──────────────┐</li><li>││</li><li>│写请求│读请求</li><li>││</li><li>▼▼</li><li>┌─────────────────┐┌─────────────────┐</li><li>│Master││Slave1│</li><li>│主库│─────▶│从库│</li><li>└─────────────────┘└─────────────────┘</li><li>│</li><li>▼</li><li>┌─────────────────┐</li><li>│Slave2│</li><li>│从库│</li><li>└─────────────────┘</li></ol>

Replication Process Diagram