Notesreplication🌱 seedling
Replication Log
Also: Statement-Based Replication · Write-Ahead Log Shipping · WAL Shipping · Logical Replication · Row-Based Replication · Binlog
The ordered stream of changes a leader sends to its followers. What it records (SQL statements, storage-engine bytes, or row changes) decides how fragile replication is and whether replicas can run different versions.
Why it exists
Followers must end up holding the same data as the leader by replaying its changes in order. The format of those changes is a real design choice with consequences for correctness and operations.
How it works
- Statement-based: ship the SQL text. Compact, but nondeterministic statements (
RAND(),UUID(),UPDATE … LIMITwithoutORDER BY) diverge across replicas. - Physical / WAL shipping: ship the storage engine's Write-Ahead Log. Exact, but it describes disk blocks, so all replicas must run the same major version.
- Logical / row-based: ship row-level changes (insert this row, update row 7's columns, delete row 9). Decoupled from storage format, so it allows mixed versions and feeds Change Data Capture.
- Trigger-based: triggers write changes to a side table that an external process ships. Flexible, slower, mostly legacy.
- Positions in the log (an LSN, binlog coordinates, a GTID set) let a follower resume or find its place on a new leader.
Trade-offs
| Format | Gains | Costs |
|---|---|---|
| statement | small log, readable | nondeterminism breaks replicas |
| physical WAL | exact, cheap to produce | version-locked; hard upgrades |
| logical rows | version-independent; CDC-friendly | bigger log for bulk updates |
In practice
- MySQL defaults to row-based binary logging and has deprecated the
binlog_formatsetting, signalling row format as the future. - PostgreSQL uses physical streaming for standbys and logical replication (publications and subscriptions) for selective, cross-version replication.
Pitfalls
- A follower that falls behind the leader's log retention can't catch up and must be rebuilt from a snapshot; pinning the log (replication slots) instead risks filling the leader's disk.
Connections
- Part of
- Leader-Based Replication
- Learn first
- Write-Ahead Log
- Builds on this
- Change Data Capture
- In real systems
- MySQLPostgreSQL
Recall
These cards are also in your review deck, scheduled with spaced repetition.