Sample questions
Mysql Replication ScalingDifficulty 1
In MySQL replication, what does the source server actually send to a replica so it can reproduce the same changes?
- aA live TCP stream of raw InnoDB buffer pool pages
- bA stream of binary log (binlog) events describing the changes that happened on the source✓
- cA nightly compressed copy of the entire data directory
- dA set of
SELECT results that the replica replays as INSERT statements
Explanation:MySQL replication is binlog-based: the source writes every change (or the resulting row changes, depending on format) to its binary log, and replicas read that log via a replication I/O thread, store it locally as a relay log, and apply it with a SQL/apply thread. There is no raw page streaming and no periodic full-directory copy involved in normal replication.
Mysql Replication ScalingDifficulty 2
binlog_format can be set to STATEMENT, ROW, or MIXED. What does ROW format actually record in the binary log for an UPDATE?
- aThe exact SQL text of the
UPDATE statement, to be re-executed as-is on the replica - bOnly the primary key of the affected row, leaving the replica to look up the new values itself
- cThe before/after images of the actual rows that changed✓
- dA checksum of the affected page, used only to verify replica data integrity
Explanation:Row-based replication (RBR) logs the actual data changes — the row's before image (for UPDATE/DELETE) and after image — instead of the SQL statement. This makes replication deterministic even for statements that could behave differently on source and replica (e.g. those using NOW(), RAND(), or triggers with side effects), unlike statement-based replication which logs and re-executes the SQL text itself.
Mysql Replication ScalingDifficulty 2
A developer writes a stored routine that calls UUID() to generate a value inserted into a table, and the server is running binlog_format=STATEMENT. Why is this risky for replication?
- a
UUID() is non-deterministic, so re-executing the same SQL statement on the replica could generate a different value than what was inserted on the source✓ - b
UUID() cannot be used inside stored routines at all under statement-based replication and the statement will simply be rejected - cStatement-based replication only supports numeric column types, so a UUID string column breaks replication
- dIt is not risky; MySQL automatically detects
UUID() and converts the statement to row-based on the fly regardless of binlog_format
Explanation:Statement-based replication (SBR) re-runs the logged SQL on the replica, so any non-deterministic function (UUID(), RAND(), NOW() in some contexts, etc.) can produce different results on source and replica, causing data drift. This is exactly why MySQL introduced row-based replication and why MIXED format automatically switches to row-based logging for statements it detects as unsafe.
Mysql Replication ScalingDifficulty 2
In modern MySQL replication terminology (8.0.23+), which command shows a replica's replication status, including how far behind it is from the source?
- a
SHOW ENGINE INNODB STATUS - b
SHOW PROCESSLIST - c
SHOW BINLOG EVENTS - d
SHOW REPLICA STATUS✓
Explanation:SHOW REPLICA STATUS (the modern name for the older SHOW SLAVE STATUS, both still supported) reports the replica's I/O and SQL thread state, the source it is connected to, and Seconds_Behind_Source (formerly Seconds_Behind_Master), which indicates replication lag. SHOW ENGINE INNODB STATUS and SHOW PROCESSLIST are general-purpose diagnostics unrelated to replication status specifically.
Mysql Replication ScalingDifficulty 2
What is the main purpose of GTID (Global Transaction Identifier) based replication compared to classic file/position-based replication?
- aGTIDs remove the need for a binary log entirely, since transactions are tracked in a separate metadata table
- bGTIDs make replication synchronous by default instead of asynchronous
- cEvery transaction gets a globally unique identifier, so a replica can be pointed at a new source and resume correctly without manually specifying a binlog file and position✓
- dGTIDs are only used to speed up
mysqldump exports, not related to replica failover
Explanation:With GTID-based replication (GTID_MODE=ON), each transaction is tagged with a unique ID (source_uuid:transaction_id). When repointing a replica to a new source (e.g. during failover), MySQL can automatically determine which transactions are missing by comparing GTID sets, instead of requiring an operator to manually track and specify the exact binlog file name and byte offset as in classic positional replication.
Mysql Replication ScalingDifficulty 1
By default, when a source commits a transaction and sends it to replicas, does it wait for any replica to confirm receipt before returning success to the client?
- aNo — default MySQL replication is asynchronous, so the source commits and returns success without waiting for any replica✓
- bYes — the source always waits for every replica to apply the transaction before committing
- cYes — the source waits for exactly one replica to acknowledge before committing, by default
- dIt depends on the storage engine of the replicated tables, not on any replication setting
Explanation:Standard (asynchronous) MySQL replication does not require any replica acknowledgment: the source commits locally and returns success to the client, then sends binlog events to replicas independently. This gives the best write latency but means a crash right after commit can lose transactions that never reached any replica — which is exactly the gap semi-synchronous replication addresses.