The binary log
The binary log, or binlog, is a log of all changes that modify data on the primary, kept at the SQL layer above the storage engine. Replication works by streaming binlog events to replicas, which apply them to stay in sync.
The flow
- The primary writes committed changes to its binlog.
- Each replica runs an IO thread that pulls binlog events and writes them to a local relay log.
- A SQL thread, or applier, reads the relay log and replays the changes.
Binlog formats
- STATEMENT logs the SQL text. Compact but unsafe for nondeterministic functions.
- ROW logs the actual before and after row images. Safe and the default, at the cost of larger logs.
- MIXED uses statement format when safe and switches to row format otherwise.
Sync versus async
By default replication is asynchronous: the primary commits without waiting for replicas, so a failover can lose recent transactions. Semi synchronous replication makes the primary wait until at least one replica acknowledges receipt, trading a little latency for less data loss.
Key idea
MySQL replication ships binlog events from the primary to each replica's relay log, where an applier thread replays them, using row based logging by default and async delivery unless semi sync is enabled.