Articles
MySQL Replication Interview Questions: GTIDs, Replica Lag, and Safe Failover
MySQL replication interview questions: practice a realistic scenario, troubleshoot common failures, and explain the trade-offs with a verification plan.
Safe promotion requires proving which transactions the candidate replica actually applied and fencing the old writer. This guide explains the mechanism, traces a concrete failure, and shows how to verify a proposed fix. The examples are original study scenarios, not claims that a specific company asks these exact questions.
Key takeaways
- Start with the exact state and version, not a generic product label.
- Separate what the system received from what it actually applied.
- Make retries, failover, and partial completion explicit.
- Use a report or query plan to verify the result after a change.
How does the mechanism work?
MySQL GTIDs identify transactions across source and replica. With GTID auto-positioning, a replica can request transactions it has not executed, but compare executed and purged sets and understand the topology. Also distinguish transactions received from the source from those applied to the data.
Trace the operation in order. Identify the source of truth, the persistent state, and the point at which an operation is considered committed. Ask which state is asynchronous, which state can be replayed, and what identifier lets you compare two observations of the same logical event.
Can you reason through an example?
A replica has received transactions through GTID 120 but has applied only through 112. Promoting it now may omit acknowledged writes from 113–120. If the source is still reachable, allow apply to catch up and verify the executed set. If it is lost, estimate possible data loss and follow the recovery policy rather than calling the replica current based only on its receiver thread.
Make one small test case with a known expected result. Keep the inputs and the version fixed, then vary one condition. That tells you whether a failure comes from identity, timing, configuration, data shape, or capacity rather than encouraging trial-and-error edits.
What hidden failure should you catch?
GTID auto-positioning does not erase errant transactions or make split brain impossible. Before redirecting writes, fence the previous source, account for stale client connections, and verify that only one writable primary remains. Check the exact MySQL version and topology tooling.
In an interview, say what you would preserve before changing anything: the exact request, event or transaction identity, relevant state, timestamp, and error details. State whether retry is safe. If it is not, explain how you would reconcile the uncertain outcome instead of replaying blindly.
| Diagnostic step | Question it answers |
|---|---|
| Compare received and applied transaction state | Which assumption is false? |
| Inspect executed and purged GTID sets | Where did expected state diverge? |
| Fence the old writer before promotion | Can the operation safely be repeated? |
| Verify one writer after repointing | Did the final invariant hold? |
How do you debug it in an interview?
- Compare received and applied transaction state
- Inspect executed and purged GTID sets
- Fence the old writer before promotion
- Verify one writer after repointing
- State the expected end state and the recovery or rollback boundary.
Then explain the trade-off. A setting that reduces latency may increase resource use; a more aggressive retry can amplify an outage; a looser schema or policy can move validation risk downstream. Recommend a change only after naming the signal that would show it worked and the condition that would make you reverse it.
What follow-up questions should you prepare for?
Be ready to explain what changes if the data arrives late, a process stops halfway through, or the operation is repeated. Discuss how you would handle a conflicting update, how you would prove the system is caught up, and which part of the answer depends on a version-specific feature. These follow-ups test whether the first solution preserved correctness beyond the happy path.
MySQL Replication Interview Questions interviews reward a concise chain of reasoning: define the invariant, inspect the narrowest useful evidence, test one hypothesis, and verify the outcome.
Sources and further reading
Research checked September 23, 2026. Confirm version and configuration details against the environment named in your prompt.
MySQL replication: how do GTIDs help with safe failover?
GTIDs identify transactions independently of a replica’s local binlog position, which can simplify comparing executed transaction sets and attaching a replica after topology changes. They do not guarantee that every replica has applied every committed transaction at the instant a primary fails. Before promotion, establish which transactions the candidate has executed and what writes may be missing.
A safe failover also needs a fencing plan so the old primary cannot accept writes after the new one becomes authoritative. Otherwise two writable primaries can create divergent histories even when replication metadata is correct. Check the application’s write routing, proxy or orchestration state, and how clients discover the promoted server.
For lag, separate receive progress from apply progress where the tooling exposes them. A replica can have fetched logs but be applying slowly because of large transactions, limited parallelism, storage pressure, or a hot table. In an interview, state the consistency promise: can reads be stale, can acknowledged writes be lost, and what recovery action resolves uncertainty?