Master‑Slave Replication in MySQL: Binlog Mechanics, Parallel Replication & Lag Reduction

In production environments a single MySQL server cannot satisfy requirements for security, high availability, and high concurrency, so clusters using master‑slave replication and read/write splitting are deployed.

Benefits of a MySQL master‑slave cluster:

Increases load capacity: the master handles writes, slaves handle reads.

Improves read/write performance, scalability, and availability.

Provides backup and disaster recovery; a slave in another location can take over instantly if the master fails.

Binlog Overview

The binary log (binlog) records all write‑type operations (excluding queries) in binary form on disk, effectively storing the executed SQL statements. It is a logical log generated by the MySQL server layer and is used by any storage engine.

Primary use cases:

Master‑slave replication – the master writes events to the binlog, which are sent to slaves and stored in their relay logs.

Data backup – binlog contains detailed changes after a backup, enabling incremental restores.

Binlog formats:

STATEMENT – records the SQL statements that modify data.

ROW – records changes at the row level.

MIXED – combines STATEMENT and ROW, using ROW for most data changes and STATEMENT for schema changes.

mysqlbinlog mysql-bin.00001 | more

Master‑Slave Replication Threads

Three threads are involved:

Binlog dump thread (master): Writes update events to the binlog and notifies the slave when new data is available.

I/O thread (slave): Connects to the master, requests binlog data from a specific position, and writes it to the local relay log.

SQL thread (slave): Reads the relay log, translates events back to SQL, and re‑executes them to keep the slave synchronized.

Basic Replication Process

The master writes data and generates a binlog entry.

After the binlog entry is written, the master commits the transaction.

The slave’s I/O thread requests binlog data from the master starting at a given position.

The master’s I/O thread streams the requested binlog chunks to the slave.

The slave writes the received events to its relay log and records the position in master‑info.

The slave’s SQL thread reads the relay log, converts events to SQL, and applies them.

The slave records the current relay‑log file and position in relay‑log.info for the next iteration.

Parallel Replication

MySQL 5.6 introduced a coordinator thread that distributes work to multiple worker threads, but true parallel replication arrived in MySQL 5.7, where the slave can replay transactions in the same parallelism as the master.

Configuration variable slave_parallel_type determines the parallelism mode:

DATABASE – default, parallelism based on database name.

LOGICAL_CLOCK – parallelism based on group commit identifiers.

Per‑Database Parallelism

Each worker thread maintains a hash table keyed by database name; transactions affecting different databases can be executed concurrently, provided the workload is balanced across databases.

Group‑Commit Parallelism

Transactions that belong to the same commit group share a commit_id. On the slave, events with the same commit_id are dispatched to multiple workers and executed in parallel; the coordinator moves to the next group after the current one finishes.

Master‑Slave Lag

Lag occurs because the slave finishes a transaction later than the master. The delay equals T3‑T1, where T1 is the master’s commit time, T2 the time the binlog reaches the slave, and T3 the slave’s commit time. The seconds_behind_master status variable reports this lag.

Common causes of lag:

Slave hardware is weaker than the master.

High read load on the slave.

Large transactions that generate massive binlog files.

DDL operations on the master that block the slave.

Lock conflicts (e.g., SELECT … FOR UPDATE or MyISAM tables).

Insufficient replication capacity; before MySQL 5.6 the slave’s SQL thread was single‑threaded.

How to Reduce Lag

Limit large concurrent transactions and optimize business logic.

Optimize slow SQL, avoid massive batch operations, and use scripts that throttle updates.

Upgrade slave hardware to match or exceed the master’s performance.

Minimize network latency between master and slave (short, high‑bandwidth links).

Route latency‑sensitive reads to the master; use slaves primarily for backup.