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

Preface

Hello, I am SanYou. The job market is getting more competitive, so I have compiled 15 classic MySQL log interview questions to help you land your ideal offer.

What is redo log? Why is it needed?

What is WAL technology and its benefits?

How is redo log written?

What is the execution flow of redo log?

How does redo log guarantee crash‑safe mechanisms?

What is binlog, its role, and can it guarantee crash‑safety?

What are the differences between binlog and redo log?

What is the execution process of the executor and InnoDB when updating a row?

How to recover data after accidental database operations?

What are the three formats of binlog?

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

If two‑phase commit is not used, what problems arise from writing redo log first or binlog first?

How does binlog flushing work?

What is undo log and its purpose?

How is redo log recorded?

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

Redo log definition

Redo log is a redo log that records changes on data pages.

It records modifications made by a transaction for backup storage.

If the server crashes or dirty pages are not written to disk, redo log can be used for recovery.

It is specific to the InnoDB storage engine.

Why redo log is needed

Redo log provides a recovery mechanism after an abnormal MySQL restart, ensuring data consistency.

It works with MySQL's WAL mechanism: updates are written to memory first, returning quickly, but crash‑safe recovery relies on redo log.

2. What is WAL technology and its benefits?

WAL (Write‑Ahead Logging) writes the log to memory before writing data to disk.

Benefit: the system can respond quickly without flushing each operation to disk, and after a crash the redo log can restore data.

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 .

When MySQL executes a DML statement, it first writes the record to the redo log buffer; later, at a certain point, multiple records are flushed to the redo log file. This is the WAL technique.

The buffer resides in user space and cannot be written directly to disk; it must pass through the OS kernel buffer.

The log is first written to the InnoDB redo log buffer (user space).

Then it is saved to the OS buffer.

Finally, the system call fsync() writes the OS buffer to the redo log file on disk, completing the flush.

The parameter innodb_flush_log_at_trx_commit controls how the buffer is flushed:

0 – delayed write: redo log buffer is written to the OS buffer every second, not at each transaction commit.

1 – real‑time write: each commit flushes the buffer to the OS buffer and then to the redo log file.

2 – real‑time write with delayed flush: commit writes to the OS buffer, and the buffer is flushed to the file every second.

4. Execution flow of redo log

Assume the following SQL statement:

<code>update T set a =1 where id =666</code>

The MySQL client sends the statement to the MySQL server.

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

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

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

The transaction state is set to prepare .

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

Redo log writes the buffered operation to the redo log file on disk.

5. How does redo log guarantee crash‑safe mechanism?

Each update is written to redo log; if the write fails, the operation fails and the transaction cannot commit.

Redo log stores page‑level changes; after a crash, replaying the redo log restores the data.

6. What is binlog, its role, and can it guarantee crash‑safe?

Binlog 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 cannot guarantee crash‑safety because it may not be fully written before a crash; it must be used together with redo log.

7. Differences between binlog and redo log

Purpose : redo log – crash recovery; binlog – replication and 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 after size limit.

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

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

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

8. Executor and InnoDB process for an UPDATE statement

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

After the SQL operation, the changes are applied in memory, and both redo log and binlog are written.

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

9. How to recover data after accidental operations?

Locate the nearest binlog entry before the mistake, replay it on a temporary database, and then restore the lost rows to the production database.

10. Three formats of binlog

Binlog has three formats:

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.

The three steps are:

After writing redo log, the transaction enters the prepare state.

The executor writes the binlog.

The transaction moves to commit , allowing it to be committed.

Without two‑phase commit, a crash between redo log and binlog writes could cause data inconsistency.

12. Problems when not using two‑phase commit

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

Writing binlog first: after a crash, redo log may not be written, making the transaction invalid and causing inconsistency during recovery.

13. Binlog flushing mechanism

Uncommitted transactions are first stored in the binlog cache. When the transaction commits, the cache is flushed to the binlog file. The cache size is controlled by binlog_cache_size .

Flushing to disk is controlled by sync_binlog :

0 – MySQL does not control flushing; the OS decides when to write to disk (risk of loss on OS crash).

N – Binlog is flushed to disk every N transactions.

1 – Binlog is flushed to disk on every commit.

14. What is undo log and its purpose

Undo log records the state of data before modification, enabling rollback.

It complements redo log, which records the after‑state; undo log stores logical changes needed to revert errors.

15. How is redo log recorded?

Redo log has a fixed size and uses circular writing; when the end is reached, it wraps to the beginning.

The redo log buffer consists of four linked files: ib_logfile_1, ib_logfile_2, ib_logfile_3, ib_logfile_4.

write pos – current write position in the log. checkpoint – position after the last flush to disk. Space between write pos and checkpoint is used for new log entries. Space between checkpoint and write pos holds pending flushes; if not flushed, it may be overwritten.

After a crash, redo log can restore unflushed data (after the checkpoint), providing crash‑safe guarantees.