Replication Interview Questions

ROW binlog records before/after images of changed rows; STATEMENT logs the SQL; MIXED switches when the optimizer thinks a statement is unsafe. ROW is the safe default for triggers, non-deterministic UDFs, and stored procedures. It is larger on bulk UPDATE. binlog_row_image=MINIMAL shrinks ROW events but complicates row lookup on the replica if keys are missing.

GTID (source_id:transaction_id) uniquely names every committed transaction so a replica can auto-position without you copying File/Position. Failover becomes CHANGE REPLICATION SOURCE TO SOURCE_AUTO_POSITION=1. Duplicate GTIDs are rejected, which prevents applying the same trx twice. Enabling GTID on a long-running anonymous estate needs ASSIGN_GTIDS_TO_ANONYMOUS_TRANSACTIONS care.

Replica lag is Seconds_Behind_Source in SHOW REPLICA STATUS, but that metric is only as good as the last timestamp in the relay log and hides SQL-thread versus IO-thread delay. Parallel appliers make it even noisier. Better signals are Performance Schema replication_applier_status_by_worker and the lag between source binlog and replica applied GTID set. A zero-second replica can still miss a huge ROW event in flight.

Parallel applier (replica_parallel_workers) applies independent transactions concurrently using logical-clock or writeset dependencies from the source binlog. replica_preserve_commit_order=ON keeps visible commit order for clients reading the replica. Too many workers on a tiny schema just fight InnoDB. WRITESET tracking on the source is what makes parallelism real for same-schema OLTP.

Filtered replication (replicate-do-db) is schema-name based and silently drops events for other schemas, including cross-schema joins and USE-less statements under STATEMENT format. ROW events still carry db names, so behavior differs by binlog_format. Forgotten mysql schema filters skip GRANTs. Interviewers want GTID holes and missing FKs as the failure story.

Crash-safe replication needs relay_log_info_repository=TABLE plus relay_log_recovery=ON so InnoDB and relay positions stay in sync. FILE repositories could lose the last applied GTID after a crash. replica_preserve_commit_order plus crash-safe tables also matter. After recovery, GTID_EXECUTED must match applied row state before you point a new source.

Failing over with GTID is CHANGE REPLICATION SOURCE TO SOURCE_AUTO_POSITION=1; without GTIDs you must pick the replica with the highest retrieved File/Position and stitch everyone else. Promoting a lagged replica without waiting for relay drain loses data. Orchestrator-style tools still need to wait for the most caught-up replica. Errant transactions on the new source poison the old master’s GTID set.

Write-set parallelization (binlog_transaction_dependency_tracking=WRITESET) lets the replica know two transactions touched disjoint primary keys and can apply together. LOGICAL_CLOCK is older and less aggressive. Huge transactions that touch many keys serialize workers. Enable it on the source; the replica cannot invent writesets from STATEMENT events.

Anonymous transactions after enabling GTID require ASSIGN_GTIDS_TO_ANONYMOUS_TRANSACTIONS or a binlog dump that never mixed modes. The usual path is enable GTIDs on every server, then SET GTID_PURGED from a backup. Errant anonymous events after a crash are why people drain replicas first. mysqlbinlog --skip-gtids is a recovery scalpel, not a migration plan.

Multi-source replication uses one replication channel per source; GTIDs must not collide because GTID_EXECUTED is global to the server, not per channel. Two clones of the same mysql.server_uuid are the classic collision. Channels still share innodb_buffer_pool and can stall each other. Filter rules are per channel—easy to apply a DROP on the wrong schema.

Group Replication / InnoDB Cluster uses Paxos-like consensus and a majority; a network split isolates a minority which becomes read-only or offline. Single-primary mode avoids conflict handling; multi-primary needs writeset certification and can abort a local COMMIT. group_replication_consistency=AFTER controls read-your-writes. It is not async replica lag—it is quorum.

binlog_expire_logs_seconds too aggressive plus a lagged replica means the replica cannot fetch vanished events and replication stops with a missing-GTID error. PURGE BINARY LOGS on the source has the same effect. Backups that record executed_gtid_set still need those files for PITR. Size binlog retention to the worst replica lag plus dump time, not to disk comfort.

Row events for a table without a primary key force the replica to do a full table scan (or use a hidden generated key in 8.0) to apply each UPDATE/DELETE. Apply throughput collapses and replica lag explodes. InnoDB still has a clustered index internally, but without a logical PK the ROW image matching is expensive. Add an explicit PK before you enable ROW.

Default MySQL replication is asynchronous: the source writes the binlog and returns COMMIT before any replica has the event. A crash of the source can lose transactions a replica never saw. Replica lag (Seconds_Behind_Source) is expected under load. Semi-sync and Group Replication exist because async durability stops at the local redo plus binlog.

Semi-sync waits until at least one replica has acknowledged the binlog event into its relay log (after_sync in 5.7+). If no replica ACKs before rpl_semi_sync_source_timeout, the source falls back to async. That is still not a full majority quorum like Group Replication. Lost-ack after the wait window is the interview follow-up.

binlog_row_image=FULL vs MINIMAL changes how much of the row is logged; FULL is safer for tables without a primary key because the replica can find the row. MINIMAL logs only changed columns plus keys, shrinking binlog and network. Tools that parse ROW events for CDC often require FULL or NOBLOB. Switching mid-stream confuses mysqlbinlog readers.

A replica that is also a source (chain) must enable log_replica_updates so its binlog contains applied events for downstream replicas. Without it, the chain goes silent. GTIDs still originate from the original source_id, which is what you want. Delayed replicas in the middle still rewrite timestamps unless you plan SOURCE_DELAY only on the leaf.

Delayed replication (SOURCE_DELAY) holds events for N seconds, giving you a window to stop the SQL thread before a DROP TABLE replicates. It is not a backup—binlog on the source can still expire. Combine with a regular dump/XtraBackup. Failover to a delayed replica means accepting data age equal to the delay plus lag.

FAIL_ON_NONDETERMINISTIC or MIXED mode still slips user variables, temporary tables, and some stored-procedure paths into STATEMENT events. UUID() in a trigger is a textbook desync. Setting binlog_format=ROW on the session for ETL is the blunt fix. mysqlbinlog output that shows Query instead of Table_map/Write_rows is your audit.

Orchestrator/MHA style failover must wait for the most caught-up replica; promoting a node whose GTID set is a subset loses transactions that other replicas already have. Then re-point every replica with AUTO_POSITION and check for errant GTIDs. Super_read_only on leftover old-source prevents split brain. Semi-sync ACK does not replace that wait.