MySQL interview question · Most asked
How does MySQL replication work? Explain the binary log, asynchronous and semi-synchronous replication, and GTIDs.
Summary
The source writes changes to the binary log; each replica's I/O thread copies events into its relay log, and SQL (applier) threads replay them. By default this is asynchronous; semi-synchronous replication waits until at least one replica has received the event before the commit returns. GTIDs give every transaction a global ID, making failover and re-pointing replicas much simpler.
Longer Answer
- Asynchronous: lowest latency; a source crash can lose transactions not yet copied.
- Semi-synchronous: the commit waits for a replica acknowledgement (received, not applied), reducing data loss at the cost of latency.
- GTIDs (
server_uuid:transaction_number) let a replica ask for "everything I have not executed" instead of tracking file names and positions. - Replicas can apply transactions in parallel (multi-threaded replicas) to reduce lag.
Replication is not a backup: a DROP TABLE replicates too. Group Replication / InnoDB Cluster adds consensus-based, automatic failover.
Key Points
- Binlog, relay log, applier threads.
- Async vs semi-sync durability trade-off.
- GTIDs simplify failover.
- Replication is not a backup.
Deep dive: Scaling Databases & Data Consistency
More most-asked MySQL questions
- What is a clustered index in InnoDB, and why does primary key choice matter so much?
- Why is OFFSET pagination slow on large tables, and what is keyset pagination?
- How do you read EXPLAIN output in MySQL?
- Why might MySQL not use an index you created?
- What are the transaction isolation levels, and which does InnoDB use by default?
- How does a composite index work, and what is the leftmost-prefix rule?
- What causes deadlocks in MySQL, and how do you handle them?
- What are gap locks and next-key locks, and why do they surprise developers?