Reference · Cheat sheet

Replication & the binary log

The one-page compressed essence: the binlog is a logical change feed; async means a failover can lose commits.

Lesson: 0008Context: InnoDB, MySQL 8.0+

Two logs — don't confuse them

	redo log (0007)	binary log (0008)
layer	InnoDB engine	server (all engines)
content	physical page changes	logical row/statement changes
purpose	crash recovery (1 server)	replication & PITR (many servers)

redo heals one server · binlog copies one server to another. Undo = rollback + MVCC.

Replication flow

SOURCE writes binlog → replica I/O (receiver) thread → RELAY LOG → replica SQL (applier) thread → replica data Reads fan out to replicas; writes stay on the source.

Async vs semisync

Mode	Source commit waits for…	Failover data loss
async (default)	nothing	can lose acknowledged commits
semisync	≥1 replica to receive (relay log), not apply	committed txns reached ≥1 replica

Semisync cost = one network round-trip of commit latency. "Received" ≠ "applied" — a replica can still lag on applying.

Inspect it

SHOW VARIABLES LIKE 'log_bin'; -- logging on? (default ON) SHOW BINARY LOGS; -- files SHOW BINARY LOG STATUS; -- current file + position SHOW BINLOG EVENTS IN 'binlog.000001'; -- the logged events SHOW VARIABLES LIKE 'binlog_format'; -- ROW (default) / STATEMENT / MIXED SHOW REPLICA STATUS\G -- on a replica: threads + lag

Source: MySQL 8.0 Manual — Replication · Semisynchronous Replication · The Binary Log · All lessons