Master MySQL Logs: Redo, Binlog, Undo & Crash‑Safe Mechanisms Explained

Introduction

The article gathers 15 classic MySQL log interview questions and offers concise, technical answers covering redo log, write‑ahead logging (WAL), binlog, undo log, two‑phase commit, crash‑safe mechanisms, and relevant configuration parameters.

What is redo log and 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 recovery?

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

Differences between binlog and redo log.

Execution flow of an UPDATE statement in InnoDB.

How to recover data after accidental operations?

Three formats of binlog.

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

Problems when writing redo log before binlog or vice‑versa.

Binlog flushing mechanism.

What is undo log and its purpose?

How does redo log record data?

1. Redo Log Overview

What is redo log?

Redo log (re‑do log) records changes made to data pages.

It stores the modifications performed by a transaction for backup.

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

It is specific to the InnoDB storage engine.

Why is redo log needed?

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

Works with MySQL’s WAL mechanism: updates are written to memory first, then asynchronously flushed to disk, but redo log guarantees that changes are not lost on crash.

2. Write‑Ahead Logging (WAL)

WAL means the log is written to memory before the actual data is persisted to disk.

Benefit: avoids synchronous disk writes for every operation, while still allowing recovery after a crash.

3. Redo Log Write Path

Redo log consists of an in‑memory redo log buffer and a on‑disk redo log file . When a DML statement executes, changes are first placed in the buffer; later they are flushed to the file using the OS buffer and the fsync() system call.

The flushing behavior is controlled by the innodb_flush_log_at_trx_commit parameter:

0 – delayed write: logs are written to the OS buffer every second and flushed to disk later.

1 – immediate write: each transaction commit forces the buffer to be flushed to disk.

2 – write to OS buffer on commit, but flush to disk once per second.

4. Redo Log Execution Flow

When executing update T set a =1 where id =666:

Client sends the SQL to the MySQL server.

The server parses, optimizes, and creates an execution plan.

InnoDB records the modification in memory.

The change is logged in the redo log buffer, describing which data page to modify.

The transaction state is set to prepare .

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

Redo log flushes the buffered record to the redo log file on disk.

5. Crash‑Safe Guarantee of Redo Log

Each update to redo log is forced to be written; a failure to write means the transaction cannot commit.

The log is page‑based, recording field‑level changes, so replaying the log after a crash restores the data.

6. Binlog Basics

Binlog is an archive log at the MySQL server layer, used for master‑slave replication and data recovery.

It cannot guarantee crash‑safety on its own because it may not be fully written before a crash; it must be used together with redo log.

7. Redo Log vs. Binlog

Purpose : Redo log – crash recovery; Binlog – replication and recovery.

Implementation : Redo log – InnoDB engine; Binlog – server layer, usable by all engines.

Recording method : Redo log – circular writes; Binlog – append‑only files with size controlled by max_binlog_size.

File size : Redo log size is fixed; Binlog size is configurable.

Crash‑safe : Redo log – yes; Binlog – no.

Log type : Redo log – physical (page changes); Binlog – logical (SQL statements).

8. UPDATE Execution in InnoDB

The executor reads the target row into memory after index selection.

After the UPDATE, changes are written to memory, then both redo log and binlog are recorded.

InnoDB later flushes the modified pages to disk at an appropriate time.

9. Data Recovery After Mistakes

Locate the binlog segment closest to the erroneous operation, replay it on a temporary instance, and extract the missing rows to restore the production database.

10. Binlog Formats

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

Row – records changed rows (row‑based replication).

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

11. Two‑Phase Commit

MySQL splits a transaction into two phases: prepare and commit.

The three steps are:

Redo log is written and the transaction enters the prepare state.

The executor writes the binlog.

The transaction moves to commit and can be finalized.

Two‑phase commit ensures consistency between redo log and binlog, preventing data loss or inconsistency after a crash.

12. Problems When Ordering Logs Differently

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

Writing binlog first: if redo log is not written before a crash, the transaction is invalid, leading to inconsistency when binlog is later replayed.

13. Binlog Flushing Mechanism

Uncommitted transactions are first stored in the binlog cache. When a transaction commits, the cache is flushed to the binlog file. The cache size is controlled by binlog_cache_size. The actual disk flush is governed by sync_binlog: sync_binlog = 0: MySQL relies on the OS to decide when to flush; a crash may lose cached binlog data. sync_binlog = N (N > 1): Flushes to disk every N transactions. sync_binlog = 1: Flushes to disk on every transaction commit.

14. Undo Log

Undo log (rollback log) records the state of data before it is modified.

It complements redo log, which records the after‑state; undo log enables rolling back transactions when errors occur.

15. Redo Log Recording Method

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

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

write pos : current write location in the log. checkpoint : position after the last flush to disk. Space between write pos and checkpoint is available for new records. Space between checkpoint and write pos holds records waiting to be flushed; if not flushed, they may be overwritten.

After a crash, the redo log can replay changes after the checkpoint, providing crash‑safe recovery.