Master MySQL Redo Log & Binlog: 15 Essential Interview Questions Explained

1. What is redo log and why is it needed?

Redo log (redo log) records changes made to data pages.

It stores modifications performed by a transaction so they can be recovered if the server crashes or dirty pages are not flushed to disk.

Redo log is specific to the InnoDB storage engine.

Why redo log is required?

Provides a crash‑safe recovery mechanism after MySQL restarts, ensuring data consistency.

Works together with MySQL's Write‑Ahead Logging (WAL) to avoid data loss when updates are written asynchronously to disk.

2. What is WAL technology and its benefits?

WAL (Write‑Ahead Logging) writes the log to memory first, then to disk, before the actual data pages are persisted.

This allows fast response to SQL statements while still guaranteeing that a crash can be recovered using redo log.

3. How is redo log written?

Redo log consists of two parts: the in‑memory redo log buffer and the on‑disk redo log file . Each DML statement first writes its changes to the buffer; later, the buffer is flushed to the file as a batch, implementing WAL.

Because user‑space buffers cannot be written directly to disk, the data passes through the OS buffer and is finally persisted with an fsync() system call.

Parameter innodb_flush_log_at_trx_commit controls when the buffer is flushed:

0 – delayed write: redo buffer is written to OS buffer every second, then to the file.

1 – real‑time write: each transaction commit flushes the buffer to the file.

2 – real‑time write with delayed file flush: buffer → OS buffer on commit, file flush every second.

4. Execution flow of redo log

Example SQL: update T set a = 1 where id = 666; The MySQL client sends the statement to the MySQL server.

The server parses, optimizes, and generates an execution plan, which is handed to the InnoDB engine.

InnoDB records the modification (a = 1) in memory.

The change is logged in the redo log buffer, describing which data page needs what modification.

The transaction state is set to prepare , indicating it is ready to commit.

After the server finishes processing, the transaction state becomes commit .

Redo log flushes the buffered record to the redo log file on disk, completing the logging process.

5. Why does redo log guarantee crash‑safe recovery?

Each update is written to redo log; if the write fails, the transaction cannot be committed.

The log stores page‑level changes, so after a crash the log can be replayed to restore the database to a consistent state.

6. What is binlog and what does it do? Is it crash‑safe?

Binlog (binary log) is an archive log at the MySQL server layer, used for replication and data recovery.

During recovery, a specific time‑range of binlog can be replayed.

Binlog alone is not crash‑safe because it may not be fully written before a crash; it must be combined with redo log for crash‑safe recovery.

7. Differences between binlog and redo log

Purpose: redo log – crash recovery; binlog – replication & data recovery.

Implementation: redo log – InnoDB engine; binlog – server layer, usable by all engines.

Recording method: redo log – circular writes; binlog – append‑only, new files created when size limit is reached.

File size: redo log – fixed size; binlog – configurable via max_binlog_size.

Crash‑safe: redo log – yes; binlog – no (requires redo log).

Log type: redo log – physical (records page modifications); binlog – logical (records original SQL statements).

8. How do the executor and InnoDB process an UPDATE?

The executor selects an index, reads the target row into memory via InnoDB.

After the SQL operation, the row is updated in memory, and both redo log and binlog are written.

InnoDB later flushes the changes from memory to disk at an appropriate time.

9. Recovering from accidental data loss

Locate the binlog entry closest to the time of the mistake, replay it on a temporary database, extract the missing rows, and apply them back to the production instance.

10. Three formats of binlog

Statement: records the original SQL statements (statement‑based replication).

Row: records the actual row changes (row‑based replication).

Mixed: combines both; MySQL chooses the appropriate format per statement.

11. What is MySQL two‑phase commit and why is it needed?

Two‑phase commit splits a transaction into two stages to ensure consistency between redo log and binlog.

After redo log is written, the transaction enters the prepare state.

The executor writes the corresponding binlog entry.

The transaction moves to commit , making it durable.

If the system crashes before binlog is written, the transaction could become inconsistent; the two‑phase protocol prevents this by coordinating both logs.

12. Problems when not using two‑phase commit

Writing redo log first: after a crash, binlog may miss the update, leading to data inconsistency.

Writing binlog first: after a crash, redo log may be missing, making the binlog entry invalid and causing inconsistency.

13. Binlog flushing mechanism

Uncommitted transactions are first cached in the binlog buffer. When a transaction commits, the buffer is flushed to the binlog file. The flushing behavior is controlled by sync_binlog: sync_binlog = 0: MySQL relies on the OS to decide when to write; a crash may lose buffered binlog. sync_binlog = N (N > 0): binlog is flushed to disk every N transactions. sync_binlog = 1: binlog is flushed on every commit.

14. What is undo log and its purpose?

Undo log records the state of data before it is modified, enabling rollback of a transaction.

It complements redo log, which records the after‑state; together they allow both rollback and crash recovery.

15. How does redo log record data?

Redo log files have a fixed size and use a circular write pattern: when the end of the file is reached, writing wraps around to the beginning.

The redo log buffer consists of four linked files (e.g., ib_logfile_1 … ib_logfile_4). Key positions:

write pos: current write location in the log. checkpoint: position up to which data has been flushed to disk. Space between write pos and checkpoint is reserved for new log entries. Data between checkpoint and write pos is pending flush; if not flushed, it may be overwritten.

After a crash, MySQL replays the redo log from the checkpoint onward, restoring all committed transactions and achieving crash‑safe durability.