Understanding MySQL 5.7 Master‑Slave Replication: Binlog, Threads, and Latency Solutions

MySQL Master‑Slave Replication Overview

To alleviate load on the primary server, applications use read‑write separation: writes go to the master, reads to the slaves. The official MySQL diagram shows how this architecture distributes traffic, improves performance, and enhances availability.

What Is a Binlog?

The binary log (binlog) records every data‑changing statement or row change. It stores events in binary form and can be inspected with mysqlbinlog. New binlog files are created when MySQL restarts or when the file reaches max_binlog_size, using sequential suffixes such as mysql-bin.00001, mysql-bin.00002, etc.

Binlog Formats

Statement : logs the original SQL statement. Small size and high I/O performance, but some functions (e.g., LOAD_FILE(), UUID()) are not logged, causing replication gaps.

Row : logs each row’s changes. Guarantees accurate replication even with special functions, but generates large logs and may increase replication delay.

Mixed : uses statement format by default; switches to row format when a statement cannot be replicated safely.

Binlog Content and Event Types

Running mysqlbinlog displays the raw events. Each event has a type, such as QUERY_EVENT for statement‑based DML and ROWS_EVENT for row‑based DML. Detailed event definitions are documented in the MySQL manual.

Replication Threads

Replication involves three threads:

Master (binlog dump thread) : writes events to the binlog and notifies slaves when new data is available.

Slave I/O thread : connects to the master, requests the binlog from a specific position, and stores it locally as a relay log (e.g., host_name-relay-bin.000001), tracking the current file with an index file.

Slave SQL thread : reads the relay log, re‑executes the events on the slave, and deletes the relay log file after all events are applied.

Replication Lag Causes and Mitigation

Since replication is single‑threaded on the slave, heavy DDL statements or large queries can block the SQL thread, causing latency. When the master’s TPS is high, the slave may fall behind.

Common mitigation strategies include:

Adopt a sharding architecture to distribute load across multiple MySQL instances.

Introduce a caching layer (e.g., Memcached or Redis) between the application and MySQL.

Use more powerful hardware for the slave.

Set sync_binlog=0 on the slave to reduce sync overhead.

Enable --log-slave-updates so the slave’s updates are not written to its own binlog.

Disable the slave’s binlog if it is not needed.

References

https://dev.mysql.com/doc/refman/5.7/en/replication.html

http://www.linuxidc.com/Linux/2014-05/101450.htm

http://blog.csdn.net/xiongping_/article/details/49907095

http://www.cnblogs.com/martinzhang/p/3454358.html