Unlocking MySQL Replication: What, Why, and How Explained

What is MySQL replication

MySQL replication copies data from a primary (master) server to one or more secondary (slave) servers, forming a cluster where each node holds the same dataset. The built‑in replication is asynchronous, but MySQL also provides synchronous, semi‑synchronous, and delayed strategies.

Synchronous : the master waits for acknowledgments from all slaves before committing.

Semi‑synchronous : the master commits after at least one slave acknowledges.

Asynchronous : the master commits without waiting for any slave.

Delayed : slaves intentionally lag behind the master by a configured interval.

Replication can be performed using three binary‑log formats:

Statement‑Based Replication (SBR)

Row‑Based Replication (RBR)

Mixed mode (combination of SBR and RBR)

Why use replication

Read scaling : writes go to the master, reads can be distributed across many slaves.

Backup safety : slaves can be paused for backups, protecting the master from I/O contention.

Fault tolerance : if a slave fails, others continue serving reads; a slave can be promoted to master when the master fails.

Analytics off‑loading : analytical queries run on slaves, preserving master performance.

How binary‑log replication works

The master writes every data‑changing operation to the binary log ( binlog) before committing the transaction.

A slave I/O thread connects to the master, reads the binary log via the master’s dump thread, and writes the events to the relay log.

A slave SQL thread reads the relay log and re‑executes the events, applying the changes to the slave’s data.

Thus three threads are involved: the master’s commit thread, the slave’s I/O thread, and the slave’s SQL thread.

Common operational issues and mitigations

Write bottleneck : replication does not increase write capacity. Typical mitigation is sharding or horizontal partitioning of tables into separate databases to distribute writes.

Replication lag (asynchronous mode) : choose semi‑synchronous or synchronous replication for tighter consistency, or redesign the application to tolerate eventual consistency.

Choosing a binlog format

SBR is simple but can produce nondeterministic results with functions like NOW() and requires statement ordering.

RBR records actual row changes, avoiding nondeterminism but increasing binlog size and I/O.

Mixed mode lets MySQL decide per statement.

Master I/O thread saturation : with many slaves, the master may spend significant CPU/network on dump threads. Parallel slave execution ( slave_parallel_workers) can reduce slave‑side bottlenecks; tuning max_connections and network bandwidth on the master also helps.

Power loss and binlog durability : set sync_binlog=1 and innodb_flush_log_at_trx_commit=1 to force binlog and InnoDB redo log to be flushed to disk on each transaction.

Corrupted master.info after an unplanned restart : ensure the file is flushed on shutdown (e.g., FLUSH TABLES WITH READ LOCK before stopping) or re‑initialize the slave from a fresh dump.

Binary‑log offset loss : if the master’s binlog is not flushed, the slave may miss the last offset. Enabling sync_binlog mitigates this; otherwise start the slave from the next binlog file, accepting possible data loss.

Key configuration parameters

sync_binlog

– controls how often the binary log is synchronized to disk (0, 1, or N). innodb_flush_log_at_trx_commit – 1 forces InnoDB redo log flush on each commit. slave_parallel_workers – number of parallel SQL threads on the slave (available from MySQL 5.7). master_info_repository and relay_log_info_repository – can be set to TABLE for transactional safety.

References

“High Performance MySQL”

MySQL Reference Manual (official documentation)