Master‑Slave Replication in MySQL: Principles, Config Tips, and Latency Fixes

Problems Solved by Master‑Slave Replication

Data distribution across geographic locations

Load balancing via read/write separation and distributing reads to multiple slaves

Real‑time backup

High availability using master‑master setups

Replication Principle

The process consists of three steps:

The primary writes data‑change events to the binary log (binlog) before committing the transaction.

A slave I/O thread connects to the primary and streams those binlog events, storing them locally in a relay log.

The slave SQL thread replays the relay‑log events to apply the changes.

Two Replication Formats

Statement‑Based Replication (SBR)

SBR records the original SQL statements in the binlog. It is space‑efficient and offers high transmission speed, but can cause inconsistencies when statements rely on nondeterministic functions (e.g., NOW()), stored procedures, or triggers.

Row‑Based Replication (RBR)

RBR records the actual row changes. It consumes more space and bandwidth but provides precise replication, avoiding many of the nondeterministic issues of SBR.

Comparison

Statement replication

Higher transmission efficiency, lower latency.

Updates missing rows on the slave still succeed.

One UPDATE affecting many rows sends a single statement.

Row replication

No need for query planning on the slave.

Exact data changes are applied.

MySQL can switch dynamically between the two modes; the default is statement‑based.

Key Configuration Parameters

# Avoid circular sync in dual‑master setups
server_id=1

# Choose binlog format (statement, row, or mixed)
binlog_format=mixed

# Control how often the binlog is flushed (sync_binlog)
sync_binlog=n

# innodb_flush_logs_at_trx_commit options (0,1,2)
innodb_flush_logs_at_trx_commit=0

# Prevent automatic slave start after crash
skip_slave_start=1

# Record slave updates in its own binlog
log_slave_updates

# Log expiration (days)
expire_logs_days=7

The innodb_flush_logs_at_trx_commit setting has three meanings:

0 – Log buffer flushed to log file every second; data may be lost for up to one second on crash.
1 – Log buffer flushed to log file and disk at each transaction commit; only the current transaction can be lost.
2 – Log buffer flushed to log file each commit, but the OS flushes to disk once per second; loss is limited to one second unless the OS also crashes.

Recommended profiles:

# Performance‑oriented
innodb_flush_logs_at_trx_commit=2
sync_binlog=500

# Safety‑oriented
innodb_flush_logs_at_trx_commit=1
sync_binlog=1

Replication Lag Issues

Causes

High TPS on the primary leads to many concurrent writes, while the slave’s SQL thread is single‑threaded and may fall behind.

Mitigation Strategies

Network: Place slaves in the same LAN or low‑latency environment.

Hardware: Upgrade slave hardware to improve random‑write performance.

Configuration: Adjust settings such as sync_binlog, innodb_flush_logs_at_trx_commit, and increase innodb_buffer_pool_size to keep more operations in memory.

Architecture: Direct transactional reads/writes to the primary, route non‑transactional reads to slaves, add caching layers, or use parallel replication (MySQL 5.7+).

These adjustments help reduce lag and improve overall replication reliability.