This page needs a newer browser to run its tools. You can still read everything here.

Binary log and replication interview questions

Interview questions on binary log and replication: short warm-ups, then what each harder question tests, the first step of a good answer and its follow-ups.

19 questions: 4 warm-ups and 15 with model answers · Levels: Fresher to staff

Warm-up questions

Short questions with short answers, each answered in full.

Fresher to mid

What does a semi-synchronous acknowledgement guarantee, and what does it not?

Answered in the lesson Semi-sync replication and safe failover
Fresher

Why does a MySQL server keep a binary log when InnoDB already writes a redo log?

They do different jobs. The redo log is InnoDB's own record of page changes, kept only until a checkpoint makes it unneeded, and nothing but InnoDB reads it; its job is crash recovery. The binary log is the server's record of committed transactions in commit order, for every storage engine, in a format that other servers and tools read: replicas apply it, a restore replays it from a backup to the present, and change-data-capture programs turn it into a stream of changes. It is kept for days (30 by default), not minutes. At commit the server writes both through an internal two-phase commit, so after a crash a transaction is in both logs or in neither.
From the lesson The binary log and mysqlbinlog
Fresher to mid

Before GTIDs, a replica resumed from a file name and byte offset on its source. Why does SOURCE_AUTO_POSITION=1 replace that bookkeeping with a comparison of GTID sets?

A GTID set is a few bytes per range of consecutive transactions on each source, so comparing two is cheap even across millions of transactions. A file name and offset identifies one position on one source: on a failover the operator has to work out the equivalent position on the new source, which the pre-GTID protocol made error-prone and sometimes impossible without re-provisioning. The GTID set survives a change of source because the UUID in each GTID stays the same, so the replica keeps the record of what it has applied, and the new source works out what to send next with the same subtraction GTID_SUBTRACT performs on demand. A failover becomes a change of host in the replica's CHANGE REPLICATION SOURCE TO, and no bookkeeping beyond that.
From the lesson GTIDs: sets, tags and auto-positioning
Fresher to mid

A dashboard of Seconds_Behind_Source reads 0, but readers of the replica still see old values of rows the source changed minutes ago. Name two states that explain it.

Answered in the lesson Replication lag and parallel apply

Questions with model answers

What each question tests, the first step of a good answer and the follow-ups to expect.

Mid to senior

A MySQL 8.4 primary has failed. You have three replicas, and SHOW REPLICA STATUS shows all three receivers stopped when the primary went down. How do you decide which replica to promote, and what steps follow the promotion?
What it tests

Comparing GTID sets to find the most advanced replica, checking for errant GTIDs, and completing the failover with repointing and fencing.

First step

Compare each replica's executed and received GTID sets with GTID_SUBSET and GTID_SUBTRACT to find the one that holds every transaction the others have. Check it for errant GTIDs, let its applier finish its relay log, and only then promote it.
Follow-ups
- What is an errant GTID, and how do you detect one?
- After promotion, what commands repoint the other two replicas to the new primary?
- Why must you fence the old primary, and what are two ways to do it?
- How do you handle scheduled events that were created on the old primary?
From the lesson Semi-sync replication and safe failover
Model answer, Pro. Model answers open with any pass; prices are in the notice on this page.
Opening the model answers with your pass…
Model answers open with any MySmartCoPilot pass.
Pro pass · 1 week₹79$2.99
30 days₹179$5.99
1 year₹1,699$45
One payment; a pass never renews. The price shown is the total you pay: no fees or charges are added at checkout.
Passes can’t be bought right now, so the model answers can’t be opened at the moment. If you have a pass, enter your licence key.
Choose a pass I have a licence key
It also opens every other in-depth part in Learn and every Pro tool. Sales are final, except where the law gives you a right to withdraw, for example in the EU and the UK (refund policy).
In the answer:
- Model answer
- Follow-up answers
- Common mistakes
- Rubric
- Variants
- about 4 minutes
Mid to staff

During a failover drill, the old primary comes back online before anyone fenced it. Both the old and new primaries are now accepting writes. Describe the problem, how you would recover, and what you should have done to prevent it.
What it tests

Recognising split-brain, planning data reconciliation, and naming the fencing step that prevents it.

First step

Identify split-brain: two servers with divergent GTID sets and conflicting data. Stop writes on the old primary immediately (offline_mode or super_read_only). Then assess the divergence: which transactions are on each server but not the other?
Follow-ups
- What commands would you use to compare the two servers' gtid_executed values?
- If the old primary wrote 10 transactions and the new primary wrote 20, how do you reconcile them?
- What fencing methods could have prevented this?
- How does InnoDB Cluster handle this scenario automatically?
From the lesson Semi-sync replication and safe failover
Model answer, Pro.
In the answer:
- Model answer
- Follow-up answers
- Common mistakes
- Rubric
- Variants
- about 4 minutes
Senior to staff

A production MySQL 8.4 server with semi-sync has rpl_semi_sync_source_timeout set to 10 seconds (the default). During a network partition, writes stall for 10 seconds before continuing. The team asks you to reduce the timeout to 500 ms. What trade-offs should you explain, and what is your recommendation?
What it tests

Balancing availability against durability, understanding the fallback behaviour, and making a reasoned recommendation.

First step

Explain what the timeout does: if no ack arrives within this period, the source falls back to asynchronous mode. A shorter timeout means faster fallback and shorter write stalls, but also more frequent fallbacks for transient network delays, which reduces the durability guarantee.
Follow-ups
- What happens to Rpl_semi_sync_source_status when the timeout fires?
- How does the source recover to semi-sync mode after a fallback?
- Under what conditions would a 500 ms timeout be acceptable?
- How would you monitor how often the fallback happens?
From the lesson Semi-sync replication and safe failover
Model answer, Pro.
In the answer:
- Model answer
- Follow-up answers
- Common mistakes
- Rubric
- Variants
- about 4 minutes
Mid to senior

At 14:05 a DELETE without a WHERE clause removed all 9,000 rows of orders on a MySQL 8.4 server that runs with GTIDs. The last full backup finished at 02:00, and binary logs are kept for 30 days. How do you bring the orders back without losing the transactions that came after the mistake?
What it tests

Point-in-time recovery from a backup and the binary log: finding one transaction by time and GTID, replaying around it, and keeping the logs and the live server safe while doing it.

First step

Protect the evidence first: make sure no file written since 02:00 can be purged, and copy those files off the server. Then restore the 02:00 backup on a separate server, never over the live one, and decode the log around 14:05 with TZ=UTC to find the DELETE and its GTID before you decide how to replay.
Follow-ups
- How do you replay everything after the backup except the DELETE?
- What must the restored server know about GTIDs before the replay starts?
- How long will the replay take, and how do you check the result?
- What changes if the server had run with gtid_mode OFF?
From the lesson The binary log and mysqlbinlog
Model answer, Pro.
In the answer:
- Model answer
- Follow-up answers
- Common mistakes
- Rubric
- Variants
- about 5 minutes
Mid to staff

A MySQL 8.4 source writes 25 GB of binary log a day and keeps the default 30 days; its 1 TB disk is 90 % full. Nightly backups are kept for 7 days, and one replica lags by up to two days. What retention do you set, and how do you get space back today without breaking anything?
What it tests

Sizing binary log retention from the recovery window and replica lag, freeing space safely with PURGE, and judging compression with measured numbers.

First step

Work out what the log must still cover before deleting anything: the oldest backup a restore might start from, and the oldest file that any replica or other reader still needs, connected or not. Then put a number on today's cost: 25 GB a day for 30 days is about 750 GB, most of the full disk.
Follow-ups
- Which expiry would you set, and how much disk does it leave the logs?
- How do you free space today without breaking the lagging replica?
- Would compression help, and how would you know before relying on it?
- What would you monitor so the disk never fills like this again?
From the lesson The binary log and mysqlbinlog
Model answer, Pro.
In the answer:
- Model answer
- Follow-up answers
- Common mistakes
- Rubric
- Variants
- about 4 minutes
Mid to senior

A write-heavy MySQL 8.4 source feeds two replicas and a change-data-capture pipeline that publishes every row change to other teams. Someone proposes setting the row image to MINIMAL to cut binary log volume. What do you check before agreeing, and how would you roll the change out?
What it tests

Knowing what FULL and MINIMAL images keep, what replicas and change-data-capture readers need from them, and changing a global logging setting safely.

First step

List every reader of the log and what it does with an update or a delete. A replica only has to find the row and change it, but a downstream consumer may publish the whole row or compare old and new values. Then measure what MINIMAL would save on this workload before weighing the two.
Follow-ups
- How much would MINIMAL save here, and how do you measure it?
- Which conditions must the replicas' tables meet?
- What do the change-data-capture readers lose, and is there a middle ground?
- How do you roll the change out, and back if it goes wrong?
From the lesson The binary log and mysqlbinlog
Model answer, Pro.
In the answer:
- Model answer
- Follow-up answers
- Common mistakes
- Rubric
- Variants
- about 5 minutes
Mid to senior

One source reports gtid_executed as 'a:1-120000' and gtid_purged as 'a:1-100000'. Two replicas report 'a:1-105000' and 'a:1-80000' respectively. What does each pair mean for a reconnect, and which replica is healthy?
What it tests

Reading gtid_executed and gtid_purged together, and relating a replica's set to both.

First step

Draw the three sets on a number line and mark where each replica sits relative to the source's gtid_purged. Name what GTID_SUBTRACT(source, replica) returns for each pair, and whether every GTID in that result is still in the source's binary logs. One replica is above gtid_purged, one is below, and only one can resume.
Follow-ups
- What signals an unhealthy replica before anyone tries to reconnect it?
- How do binlog_expire_logs_seconds and the backup window relate to the gap between gtid_executed and gtid_purged?
- How do you re-provision the unhealthy replica, and what sets gtid_purged on it after the restore?
- What does the operator do if the backup used was taken while the source was briefly cut off from this replica?
From the lesson GTIDs: sets, tags and auto-positioning
Model answer, Pro.
In the answer:
- Model answer
- Common mistakes
- Rubric
- Variants
- about 4 minutes
Mid to staff

A monitoring probe discovers an errant GTID on a replica: GTID_SUBTRACT(replica, source) returns '<replica-uuid>:1', which turned out to be an INSERT a maintenance script ran on the replica by mistake. A failover of the source is likely within the hour. Which fix do you choose, and what else do you change?
What it tests

Comparing two documented fixes for one errant transaction, estimating their cost and judging what to change so the next one does not reach production.

First step

Name both documented fixes before you pick: what each one does to the replica's rows, how long it takes, and whether the GTID stays in the audit trail. Then decide against the deadline you were given and the content of the errant write, and list the configuration changes that keep the next maintenance script from writing to a replica.
Follow-ups
- What makes the row the maintenance script inserted either safe to keep or unsafe to keep?
- What does the fix leave in each server's gtid_executed, and how does a later audit read the record?
- Which replica setting would have refused the maintenance script's INSERT in the first place?
- What alert threshold would catch the next errant GTID earlier, and what fields does the alert carry?
From the lesson GTIDs: sets, tags and auto-positioning
Model answer, Pro.
In the answer:
- Model answer
- Common mistakes
- Rubric
- Variants
- about 4 minutes
Senior to staff

A fleet of 8.4 servers runs with gtid_mode and enforce_gtid_consistency both unset. The team wants to upgrade to 9.7 next quarter without surprises. What do you set, where and in what order, and what do you watch for on each stage of the upgrade?
What it tests

Planning a GTID-mode transition across a fleet, choosing between a before-upgrade switch and an after-upgrade switch, and naming the signals that say each stage finished cleanly.

First step

Name the recorded default on each version and what an upgrade without explicit settings would do to the fleet. Then decide whether to turn GTIDs on before the upgrade or after it, and write the order of the steps on each server and across the fleet, with the variable settings each server will carry in its option file.
Follow-ups
- Which status variable and query confirm that each stage of the online procedure finished before the next one starts?
- What goes into the option file for the upgrade itself, and why?
- How do you handle a replica that cannot be put through the online procedure in time?
- What rollback plan do you have if the upgraded server refuses writes on the new default?
From the lesson GTIDs: sets, tags and auto-positioning
Model answer, Pro.
In the answer:
- Model answer
- Common mistakes
- Rubric
- Variants
- about 4 minutes
Mid to senior

A fresh MySQL 8.4 replica has been loaded with mysqldump of the source and then started with CHANGE REPLICATION SOURCE TO ... SOURCE_AUTO_POSITION=1. The applier stops at once with a row conflict. How do you diagnose and fix the state, and how do you keep the next new replica from falling into the same trap?
What it tests

Reading the applier error together with gtid_executed and gtid_purged on both servers, naming the right seed order on a replica, and writing a provisioning script that is safe by default.

First step

Compare the replica's @@global.gtid_executed with the source's. If the replica's set is strictly smaller or empty while the dump holds matching rows, GTID_PURGED was not seeded. Read the dump's head for the SET @@GLOBAL.GTID_PURGED line, then plan the reset and reload before anything else.
Follow-ups
- What does RESET BINARY LOGS AND GTIDS do, and why is it run before the load, not after?
- Why does the dump open with SET @@GLOBAL.GTID_PURGED and not with SET @@GLOBAL.GTID_EXECUTED?
- How do you make the next provisioning script refuse to continue when GTID_PURGED on the fresh replica is empty?
- A clone-plugin recipient needs no manual GTID_PURGED. Why?
From the lesson Set up GTID replication with TLS
Model answer, Pro.
In the answer:
- Model answer
- Common mistakes
- Rubric
- Variants
- about 3 minutes
Mid to senior

A code review of a replication set-up finds that the repl account has no REQUIRE SSL clause and that CHANGE REPLICATION SOURCE TO sets SOURCE_USER and SOURCE_PASSWORD without SOURCE_SSL=1. Nothing on the running server looks wrong. What is the risk, and what sequence of changes fixes it on a running pair without stopping writes?
What it tests

Explaining the attack surface of plain-text replication, reading Source_SSL_Allowed on the running replica, and planning a change that converts the account and the channel to TLS without losing a transaction.

First step

Name the risk first: the replication connection carries every write the primary accepts, in clear text, over whatever network sits between the two servers. Then read Source_SSL_Allowed on the replica (expected: No) and draft the three-step fix in a safe order: certificates and the CA first, then the channel on TLS, then the account's REQUIRE SSL.
Follow-ups
- Why is ALTER USER ... REQUIRE SSL enough, if the replica was already working?
- What goes wrong if ALTER USER REQUIRE SSL runs before SOURCE_SSL=1 is set on the replica?
- What does Source_SSL_Allowed = Yes guarantee, and what does it not?
- A log line from the IO thread says 'Error reading packet from server for channel ...'. How does TLS make that line more trustworthy?
From the lesson Set up GTID replication with TLS
Model answer, Pro.
In the answer:
- Model answer
- Common mistakes
- Rubric
- Variants
- about 3 minutes
Senior to staff

A 2.1 TB InnoDB database must be copied to a new replica in the same data centre, overnight. The schema has no MyISAM tables, the source carries about 4,000 writes a second, and the operations team has used mysqldump for years. Which provisioning path do you choose, and what is the plan, including the roll-back?
What it tests

Estimating the time each path would take at 2.1 TB, reading the trade-offs around locks and GTID seeding, and writing a plan that someone else can run from the handover.

First step

Rule the dump out first: at 2.1 TB a mysqldump load is a day of CPU, not a night. The two options left are the clone plugin and a physical backup; the schema is InnoDB-only, so pick clone and plan the donor, the recipient's data directory, the restart that follows, and the alert that fires if the catch-up GTID gap grows faster than it shrinks.
Follow-ups
- What does the clone plugin need from the donor, and from the recipient, that mysqldump does not?
- How does the recipient find its GTID state after the clone, and what statement starts replication from there?
- What goes wrong if a non-InnoDB table creeps into the schema the night before?
- If the clone fails half-way, what is on disk, and what do you do?
From the lesson Set up GTID replication with TLS
Model answer, Pro.
In the answer:
- Model answer
- Common mistakes
- Rubric
- Variants
- about 3 minutes
Mid to senior

A MySQL 8.4 replica's Seconds_Behind_Source dashboard has shown 0 all morning, yet readers see rows the source changed minutes ago. You have a client on the replica and one on the source. What do you check, in what order, and what tells you which fault it is?
What it tests

Knowing when the lag field reads 0 or NULL, reading the receiver and applier rows of performance_schema, and proving the gap with GTID sets.

First step

Prove the gap first: GTID_SUBTRACT of the source's gtid_executed and the replica's lists what is missing. Then read SERVICE_STATE in replication_connection_status and replication_applier_status, and when the receiver last heard from the source (its last heartbeat and last queued transaction).
Follow-ups
- Why can the lag field read 0 for far longer than replica_net_timeout on a slow network?
- The receiver row says ON but its last heartbeat is two minutes old on a 30-second period. What do you conclude?
- How do you alert on a stopped receiver when the dashboard draws NULL as 0?
- Which per-worker reading would show the fault if a worker were stuck on one transaction instead?
From the lesson Replication lag and parallel apply
Model answer, Pro.
In the answer:
- Model answer
- Follow-up answers
- Common mistakes
- Rubric
- Variants
- about 4 minutes
Mid to senior

Three workers of a four-worker replica sit idle while the fourth has been applying the same GTID for ninety seconds, and small transactions behind it wait. How do you confirm the cause, and what stops it happening again?
What it tests

Tracing one worker's APPLYING_TRANSACTION to one big transaction, reading its row events, explaining the key-less scan and commit order, and fixing the write pattern.

First step

Take the GTID from APPLYING_TRANSACTION and decode just that transaction on the source with mysqlbinlog --include-gtids=<gtid> -vv --base64-output=DECODE-ROWS. Count its row events and rows, and check whether the table has a primary key or a unique key on NOT NULL columns.
Follow-ups
- Why does a table without a primary key make each row event so slow to apply?
- Why do small transactions that touch other rows still wait behind it?
- Would more workers, or replica_preserve_commit_order OFF, help, and what would OFF cost?
- What change to the batch job stops the next ninety-second transaction?
From the lesson Replication lag and parallel apply
Model answer, Pro.
In the answer:
- Model answer
- Follow-up answers
- Common mistakes
- Rubric
- Variants
- about 3 minutes
Senior to staff

A replica of a busy OLTP source runs 4 workers with replica_preserve_commit_order ON and falls behind at peak. The team proposes 16 workers, or commit order OFF. What do you measure first, and what do you recommend?
What it tests

Measuring how much parallelism the source's logical clock allows, weighing more workers against commit order, and naming what each change costs.

First step

Measure the parallelism on offer: count how many transactions share each last_committed in a busy stretch of the source's binary log, and sample the per-worker rows at peak for how many workers hold an APPLYING_TRANSACTION. Workers can only run what the source marked independent.
Follow-ups
- What do the source's writesets give the replica, and what makes a transaction lose that benefit?
- When does replica_preserve_commit_order OFF save time, and which readers does it put at risk?
- Would 16 workers ever help a workload of a few long transactions?
- Which two variables in this area did MySQL 8.4 and 9.7 remove, and why?
From the lesson Replication lag and parallel apply
Model answer, Pro.
In the answer:
- Model answer
- Follow-up answers
- Common mistakes
- Rubric
- Variants
- about 3 minutes

The lessons of this module

The binary log and mysqlbinlog Free lesson, Pro answers: 20 minutes
GTIDs: sets, tags and auto-positioning Free core, Pro depth: 5 of 15 minutes free
Set up GTID replication with TLS Free core, Pro depth: 6 of 15 minutes free
Replication lag and parallel apply Free core, Pro depth: 6 of 15 minutes free
Semi-sync replication and safe failover Free core, Pro depth: 6 of 15 minutes free

All MySQL & MariaDB in Depth interview questions · The MySQL & MariaDB in Depth track

Warm-up questions

What does a semi-synchronous acknowledgement guarantee, and what does it not?

Why does a MySQL server keep a binary log when InnoDB already writes a redo log?

Before GTIDs, a replica resumed from a file name and byte offset on its source. Why does SOURCE_AUTO_POSITION=1 replace that bookkeeping with a comparison of GTID sets?

A dashboard of Seconds_Behind_Source reads 0, but readers of the replica still see old values of rows the source changed minutes ago. Name two states that explain it.

Questions with model answers

A MySQL 8.4 primary has failed. You have three replicas, and SHOW REPLICA STATUS shows all three receivers stopped when the primary went down. How do you decide which replica to promote, and what steps follow the promotion?

During a failover drill, the old primary comes back online before anyone fenced it. Both the old and new primaries are now accepting writes. Describe the problem, how you would recover, and what you should have done to prevent it.

At 14:05 a DELETE without a WHERE clause removed all 9,000 rows of orders on a MySQL 8.4 server that runs with GTIDs. The last full backup finished at 02:00, and binary logs are kept for 30 days. How do you bring the orders back without losing the transactions that came after the mistake?

A MySQL 8.4 source writes 25 GB of binary log a day and keeps the default 30 days; its 1 TB disk is 90 % full. Nightly backups are kept for 7 days, and one replica lags by up to two days. What retention do you set, and how do you get space back today without breaking anything?

A write-heavy MySQL 8.4 source feeds two replicas and a change-data-capture pipeline that publishes every row change to other teams. Someone proposes setting the row image to MINIMAL to cut binary log volume. What do you check before agreeing, and how would you roll the change out?

One source reports gtid_executed as 'a:1-120000' and gtid_purged as 'a:1-100000'. Two replicas report 'a:1-105000' and 'a:1-80000' respectively. What does each pair mean for a reconnect, and which replica is healthy?

A fleet of 8.4 servers runs with gtid_mode and enforce_gtid_consistency both unset. The team wants to upgrade to 9.7 next quarter without surprises. What do you set, where and in what order, and what do you watch for on each stage of the upgrade?

A MySQL 8.4 replica's Seconds_Behind_Source dashboard has shown 0 all morning, yet readers see rows the source changed minutes ago. You have a client on the replica and one on the source. What do you check, in what order, and what tells you which fault it is?

Three workers of a four-worker replica sit idle while the fourth has been applying the same GTID for ninety seconds, and small transactions behind it wait. How do you confirm the cause, and what stops it happening again?

A replica of a busy OLTP source runs 4 workers with replica_preserve_commit_order ON and falls behind at peak. The team proposes 16 workers, or commit order OFF. What do you measure first, and what do you recommend?