Master MySQL’s Redo, Undo, and Binlog: Deep Dive for Interview Success

Interview Focus Points

Log system understanding : Interviewers expect you to know not only the names of the three logs but also MySQL’s log architecture and each log’s role throughout a transaction’s lifecycle.

Crash recovery mechanism : Demonstrate how redo log guarantees durability, undo log guarantees atomicity, and how binlog participates in master‑slave replication.

Two‑phase commit principle : Explain the two‑phase commit between redo log and binlog and why it is required to keep the two independent log systems consistent.

Core Answer

MySQL’s three logs have distinct responsibilities:

+----------------+-----------+-----------+--------+
| Comparison     | redo log  | undo log  | binlog |
+----------------+-----------+-----------+--------+
| Ownership      | InnoDB engine layer | InnoDB engine layer | MySQL server layer |
| Core role      | Crash recovery → durability | Transaction rollback & MVCC → atomicity | Replication & backup |
| Write mode     | Circular write, fixed size | Write per transaction | Append write, configurable file |
| Content form  | Physical log (page changes) | Logical log (reverse SQL) | Logical log (SQL or row changes) |
| Transaction link| Flushed on commit | Written continuously during transaction | Flushed once per transaction |
+----------------+-----------+-----------+--------+

One‑sentence summary : redo log is the “lifeline for crash recovery”, undo log is the “ingredient of rollback”, and binlog is the “messenger for replication”.

Deep Analysis

1. Architecture Position of the Three Logs

binlog : Located in the Server layer, usable by all storage engines, records SQL statements or row changes for archiving, replication, and backup.

redo log & undo log : Reside in the InnoDB engine layer. redo log implements WAL for crash recovery; undo log stores logical “reverse” operations for rollback and MVCC.

Why layer separation matters : Because binlog lives in the Server layer while redo/undo live in the engine layer, a two‑phase commit is needed to keep them consistent.

2. redo log details

Circular write : redo log uses fixed‑size files (default two files of 1 GB) that are overwritten in a circle.

WAL mechanism : on transaction commit the redo log is written first (sequential I/O), then dirty pages are flushed asynchronously, allowing recovery of unflushed data after a crash.

Flush timing : controlled by innodb_flush_log_at_trx_commit (values 0, 1, 2).

Key configuration :

SHOW VARIABLES LIKE 'innodb_log%';
-- innodb_log_file_size: size of each log file (default 48 MB, recommended 1‑2 GB)
-- innodb_log_files_in_group: number of log files (default 2)
-- innodb_flush_log_at_trx_commit: flush policy (0/1/2, recommended 1)

3. undo log details

Transaction rollback : records reverse operations (UPDATE → old value, DELETE → whole row, INSERT → primary key) so that data can be restored when a transaction is rolled back.

MVCC implementation : each row has hidden columns trx_id and roll_pointer; the roll pointer finds the older version in undo log, enabling “read‑committed” and “repeatable‑read” isolation levels.

Storage location :

-- MySQL 5.6 and earlier: stored in shared tablespace ibdata1
-- MySQL 5.6+: can be configured as independent undo tablespaces
SHOW VARIABLES LIKE 'innodb_undo%';
-- innodb_undo_tablespaces: number of undo tablespaces
-- innodb_max_undo_log_size: max size of undo tablespace
-- innodb_undo_log_truncate: whether to truncate automatically

4. binlog details

Primary‑server write : on transaction commit, modifications are appended to binlog.

Replica synchronization :

I/O thread : reads binlog from the primary and writes to a local relay log.

SQL thread : reads the relay log and replays the SQL statements to keep the replica consistent.

Binlog formats : STATEMENT: stores the original SQL; small log size but may cause inconsistency with nondeterministic functions. ROW: stores row changes; best consistency but larger log size. MIXED: combines both; balances size and consistency but can be complex in edge cases.

Configuration example:

SHOW VARIABLES LIKE '%binlog%';
-- binlog_format: format (recommended ROW)
-- binlog_cache_size: transaction cache size
-- sync_binlog: flush policy (1 = flush on every commit, safest)

5. Two‑phase commit

Why needed : redo log and binlog are independent; without coordination you could have redo written but binlog missing (data loss) or binlog written but redo missing (inconsistent replica).

Process :

Prepare phase : write redo log and mark state prepare.

Commit phase : write binlog, then write redo log and mark state commit.

Crash recovery : if a crash occurs while redo log is in prepare state, the system checks whether the binlog is complete to decide whether to commit or roll back.

High‑frequency Follow‑up Questions

Why does redo log use circular writes instead of simple appends? – to reuse space; only data from the checkpoint to the current position needs to be retained.

What are the effects of innodb_flush_log_at_trx_commit values 0/1/2?

0: flush once per second (fast, may lose up to 1 second of data).

1: flush on every commit (safest, slower performance).

2: write to OS cache and flush once per second (medium safety and performance).

Why can’t binlog replace redo log for crash recovery? – binlog is a logical log that requires re‑execution of statements, which is slower and less reliable; redo log is a physical page‑level log that can be applied directly.

When is undo log deleted? – after all active transactions no longer need it; the purge thread removes it once it is safe.

Common Interview Variants

How does MySQL guarantee the ACID properties of a transaction?

What is the difference between redo log and binlog? Why is a two‑phase commit required?

How is MVCC implemented? What role does undo log play?

How does MySQL recover data after a crash?

Memory Mnemonics

Redo log : engine layer, circular write, “crash‑recovery lifeline”.

Undo log : engine layer, reverse record, “rollback & MVCC ingredient”.

Binlog : server layer, append write, “replication messenger”.

Summary

Redo log, undo log, and binlog are MySQL’s three core logs. Redo log lives in the InnoDB engine layer and uses a circular WAL mechanism to guarantee durability; undo log records reverse operations for transaction rollback and MVCC; binlog resides in the Server layer, appends changes for replication and backup. The two‑phase commit synchronizes them, forming the foundation of MySQL’s high‑availability architecture.