Mastering MySQL Replication: From Basics to Advanced Configurations

Introduction

Hello, I am Feng. In previous MySQL series articles we covered table and index design, which helps developers write business‑logic code. However, to keep services online you also need high‑availability design; otherwise hardware failures can make the entire business unavailable. This article introduces the core of MySQL high‑availability: MySQL replication.

MySQL Replication Architecture

Replication is essentially data synchronization. MySQL uses the binary log (binlog) to record every data‑changing operation. In ROW format, each affected row is logged with before‑image and after‑image data. For example:

DELETE FROM orders_test<br/>WHERE o_orderdate = '1997-12-31';<br/>Query OK, 2482 rows affected (0.07 sec)

You can view the binlog content with the command SHOW BINLOG EVENTS. The mysqlbinlog utility can parse the binlog and display detailed row information, as shown in the following image:

The replication architecture consists of a Master (Primary) and one or more Slaves (Standby). The Master sends binlog data via a Dump thread, the Slave’s I/O thread receives it and writes a relay log, and the SQL/Worker thread replays the relay log on the Slave.

Master sends binlog through Dump thread.

Slave I/O thread receives and stores it as relay log.

SQL/Worker thread replays the relay log.

Because of the binlog, MySQL replication is flexible and can form various topologies, such as the following cascade example:

In this diagram, Slave1, Slave2, and Slave3 are direct slaves of the Master, while Slave11 is a slave of Slave1, illustrating chained replication.

MySQL Replication Configuration

Setting up replication is straightforward. The basic steps are:

1. Create a replication user with appropriate privileges.

2. Copy data from the Master using logical tools (mysqldump, mysqlpump) or the physical Clone Plugin.

3. Establish the replication relationship with CHANGE MASTER TO.

4. Monitor replication status with SHOW SLAVE STATUS.

Ensure the following crash‑safe parameters are set in the configuration file:

gtid_mode = on<br/>enforce_gtid_consistency = 1<br/>binlog_gtid_simple_recovery = 1<br/>relay_log_recovery = ON<br/>master_info_repository = TABLE<br/>relay_log_info_repository = TABLE

These settings guarantee that after a crash, both Master and Slave resume with consistent data.

Replication Types and Application Options

Asynchronous replication does not require the Slave to acknowledge receipt of binlog data, offering the best performance but no guarantee of data consistency. It is suitable for non‑critical workloads that can tolerate data loss.

Semi‑synchronous replication requires at least N Slaves to acknowledge receipt of the binlog before the Master commits a transaction. It requires installing the semi‑sync plugin and enabling it:

plugin-load="rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so"<br/>rpl-semi-sync-master-enabled = 1<br/>rpl-semi-sync-slave-enabled = 1<br/>rpl_semi_sync_master_wait_no_slave = 1

Line 1 loads the semi‑sync plugins.

Lines 2‑3 enable the Master and Slave plugins.

Line 4 forces the Master to wait for at least one Slave acknowledgment.

Older MySQL versions used a lossy semi‑sync mode where, if the Master crashed after the transaction was committed but before the Slave ACK, the last batch of data could be lost. MySQL 5.7 introduced lossless semi‑sync, moving the ACK wait before the transaction commit, eliminating data loss.

Multi‑source replication allows multiple Masters to replicate to a single Slave, useful for consolidating data from order, inventory, and supplier databases for analytical queries.

Delayed replication intentionally lags the Slave’s replay of binlog events, providing a time‑window backup (e.g., one‑hour or one‑day delay) that can be used to recover from accidental destructive operations. CHANGE MASTER TO master_delay = 3600 This command sets the Slave to lag the Master by one hour, a common strategy for disaster‑recovery snapshots, especially in financial systems.

Summary

Key takeaways:

The binary log records all MySQL data changes.

Use SHOW BINLOG EVENTS and mysqlbinlog to inspect binlog content.

Configure crash‑safe parameters to avoid data inconsistency after failures.

Choose asynchronous replication for non‑critical workloads.

Use lossless semi‑synchronous replication for core business scenarios requiring strict consistency.

Leverage multi‑source replication to aggregate data from multiple Masters for analytics.

Apply delayed replication to protect against accidental data loss, particularly in finance.