Replication: Keeping Data Safe
A single database server is a single point of failure. If the disk dies, the power goes out, or the machine catches fire — your data is gone. Replication solves this by keeping copies of your data on multiple machines.
Why Replicate?
Three Reasons to Replicate
- Durability — Data survives hardware failure. If one machine dies, copies exist elsewhere.
- Availability — If the primary goes down, a replica can take over. The system stays up.
- Latency — Serve reads from a geographically closer replica. Users in Europe don't need to query a server in Virginia.
Single-Leader Replication
The most common pattern: one node (the leader) accepts all writes, then replicates changes to followers. Followers serve read queries.
Synchronous vs Asynchronous
| Synchronous | Asynchronous | |
|---|---|---|
| How it works | Leader waits for follower to confirm write before acknowledging client | Leader acknowledges immediately, replicates in background |
| Consistency | Strong — followers always up to date | Eventual — followers may lag behind |
| Latency | Higher — must wait for network round-trip to replica | Lower — client gets instant confirmation |
| Availability | Lower — if follower is down, writes block | Higher — writes succeed even if follower is down |
| Data loss risk | None — data exists on 2+ nodes before confirming | Possible — if leader dies before replicating |
In practice: Most systems use semi-synchronous — one follower is synchronous (guarantees at least one copy), the rest are asynchronous (for performance).
Replication Lag
With async replication, there's always a gap between what the leader has and what followers have. This is replication lag — typically milliseconds, but can spike to seconds or even minutes under load.
When Lag Causes Problems
- Read-your-own-writes — User updates their profile, immediately refreshes, and sees the old data (read hit a stale replica)
- Monotonic reads — User sees a comment, refreshes, and it "disappears" (first read hit an up-to-date replica, second hit a lagging one)
- Causal consistency — User sees a reply before the original message (events replicated out of order)
Solution: Route "read-after-write" queries to the leader, or use session-sticky routing to ensure a user always reads from the same replica.
Multi-Leader Replication
For multi-datacenter deployments, you might want a leader in each datacenter (so writes are fast everywhere). But this creates conflict problems:
The Conflict Problem
User A in US edits their bio to "Hello." User B in EU edits the same bio to "Bonjour." Both succeed locally. When the datacenters sync... which one wins?
Strategies:
- Last-writer-wins — Highest timestamp wins (simple but loses data)
- Merge — Combine both values (works for some data types like sets)
- Custom resolution — Application logic decides (most flexible but most complex)
GitHub: MySQL Replication at Scale
GitHub runs MySQL with a single-leader setup where read replicas handle approximately 90% of all read traffic:
- One primary for writes (push events, issue creation, PR merges)
- Multiple read replicas across datacenters for reads (viewing repos, loading feeds, search)
- ProxySQL routes queries — writes to primary, reads to nearest replica
- Replication lag monitoring — If a replica falls too far behind, it's pulled from the read pool until it catches up
- Read-after-write guarantee — After a push, the user's subsequent requests are routed to the primary for a few seconds to avoid showing stale data
Failover: When the Leader Dies
The leader will eventually fail. What happens next is failover — promoting a follower to become the new leader.
Failover Steps
- Detection — Determine the leader is actually dead (not just slow). Usually via heartbeat timeout (30-60 seconds).
- Election — Choose which follower becomes the new leader (usually the most up-to-date one).
- Reconfiguration — Point all other followers and clients at the new leader.
The Split-Brain Problem
What if the old leader isn't actually dead — just temporarily unreachable? Now you have two leaders both accepting writes. When they reconnect, their data has diverged.
Prevention: Use fencing (STONITH — "Shoot The Other Node In The Head"). When a new leader is elected, the old one is forcibly shut down via hardware-level commands.
Interactive: Leader-Follower Simulation
Simulate a leader-follower setup. Send writes, observe replication, and trigger failures to see what happens.