Understanding MySQL’s Three Write-Ahead Logs: Redo, Binlog, and Undo

Two-Phase Commit

The two‑phase commit consists of two steps that coordinate the redo log and binary log (binlog) . When a write SQL statement is executed, the engine records the change in the redo log (in prepare state) and notifies the executor.

The executor then creates a corresponding binlog entry and writes it to disk. Finally, the engine’s commit‑transaction interface is called, changing the redo log state to commit , completing the operation.

Redo Log (Physical Log)

The redo log resides in the storage engine layer and records the physical modifications made to data pages. It implements Write‑Ahead Logging (WAL), meaning the log is written to disk before the actual data pages are flushed.

WAL provides two key benefits:

It avoids random‑write latency by allowing sequential writes to the log.

It enables crash recovery, as the redo log can replay changes to restore the database to a consistent state.

Writes to the redo log are first buffered in the redo log buffer (in memory). When a COMMIT occurs, the buffered entries are flushed to the redo log file on disk according to the innodb_flush_log_at_trx_commit parameter.

The redo log file is a circular buffer: multiple log files (e.g., ib_logfile0, ib_logfile1, …) are written sequentially; when the last file fills, the system wraps around to the first file after a checkpoint clears old entries.

Binary Log (Logical Log)

The binlog lives at the server (service) layer and records logical changes—what operation was performed, not how the data pages were altered. It supports replication (master‑slave sync) and point‑in‑time recovery.

Binlog entries are written in an append‑only fashion. When a file reaches a configured size, a new binlog file is created.

Both redo log and binlog share a common transaction identifier ( XID ). During crash recovery, the XID links the prepared redo log records with the corresponding binlog entries.

Undo Log (Rollback Log)

The undo log records the inverse of each data modification, enabling transaction rollback. For an UPDATE, an opposite UPDATE is stored; for a DELETE, an INSERT record is kept.

When a transaction is rolled back, the engine reads the undo log and applies these inverse operations to restore the original state.

Summary

During a two‑phase commit, the engine writes changes to the redo log (prepare) and the binlog (persisted), then commits the transaction, flushing the redo log to disk.

The redo log is a physical, circular log that provides durability and crash recovery via WAL.

The binlog is a logical, append‑only log that drives replication and point‑in‑time recovery.

The undo log captures inverse operations, allowing safe rollback of transactions.