Master MySQL Replication: Modes, Architecture, and Practical Guide

MySQL Replication

MySQL replication copies data from a primary server (master) to one or more replica servers (slaves), optionally replicating all databases, specific databases, or specific tables.

Replication Modes

MySQL supports three replication modes: asynchronous, semi‑synchronous, and fully synchronous.

1. Asynchronous Replication

In the default asynchronous mode, the master returns the result to the client immediately after committing a transaction, without waiting for the replica to receive or apply the changes. If the master crashes, committed transactions may be lost on the replicas.

2. Semi‑Synchronous Replication

Semi‑synchronous replication lies between asynchronous and fully synchronous modes. After committing a transaction, the master waits until at least one replica has received the events and written them to its relay log before acknowledging the client.

Semi‑synchronous replication improves data safety but adds at least one round‑trip network latency, so it works best on low‑latency networks.

3. Fully Synchronous Replication

In fully synchronous mode, the master waits until all replicas have received and executed the transaction before returning success to the client. This guarantees consistency but significantly reduces performance.

Replication Architecture

Replication relies on the master’s binary log (binlog) that records all data‑changing statements. Replicas fetch the binlog, parse the SQL statements, and replay them to stay consistent with the master.

The process involves three threads:

I/O thread : connects to the master, reads the binlog, and writes it to the relay log on the replica.

Log dump thread on the master: sends binlog events to the replica.

SQL thread on the replica: reads the relay log and executes the statements on the replica’s database.

After setting up replication, write operations go to the master while read requests can be distributed across the replicas, improving read scalability and providing fault tolerance.

In production, MySQL replication is commonly used to avoid single‑point failures and to enhance read/write performance.