How MySQL’s Write‑Ahead Log Safeguards Data During Power Failures

Write‑Ahead Logging (WAL) in MySQL InnoDB

MySQL uses the InnoDB storage engine’s built‑in WAL mechanism to guarantee durability and consistency even when a power failure occurs. The core components are the Buffer Pool, Redo Log Buffer, Redo Log, Undo Log, and periodic Checkpoints.

Buffer Pool

The Buffer Pool is a large in‑memory cache for InnoDB pages. All reads are served from this cache when possible, and modified pages (dirty pages) are kept in memory until they are flushed to disk. This reduces I/O latency for both reads and writes.

Redo Log Buffer

The Redo Log Buffer temporarily holds change records before they are written to the Redo Log file. Its size is controlled by the innodb_log_buffer_size system variable (default 16 MB). A larger buffer allows large transactions to run without forcing a disk write on each commit.

The flush timing is governed by innodb_flush_log_at_trx_commit:

0 – Do not flush to disk on commit (writes only to OS cache).

1 – Flush to disk on every transaction commit (default, strongest durability).

2 – Flush to OS cache on commit, the OS flushes to disk later.

Redo Log

The Redo Log is a set of on‑disk files that record every committed change. During crash recovery MySQL replays the Redo Log to bring the Buffer Pool up‑to‑date, ensuring that all committed transactions become durable.

Undo Log

The Undo Log stores before‑images of modified rows. If a transaction is rolled back or the server crashes before the transaction commits, InnoDB uses the Undo Log to restore the original state, guaranteeing atomicity.

Checkpoint

Checkpoints periodically write dirty pages and the Redo Log to the data files, limiting the amount of work required during recovery and allowing the Redo/Undo Log files to be overwritten.

Two checkpoint types exist:

Sharp Checkpoint – Writes all dirty pages to disk (e.g., during a graceful shutdown).

Fuzzy Checkpoint – Writes a subset of dirty pages when a threshold is reached; this is the common case during normal operation.

Power‑Failure Recovery Scenarios

Case 1 – Power loss before the Redo Log is flushed : The transaction’s changes are not recorded in the Redo Log, so they are considered failed. Upon restart, the Undo Log restores the affected rows to their pre‑update state.

Case 2 – Power loss after the Redo Log is flushed : The Redo Log contains the committed changes. During startup MySQL replays the Redo Log, applying those changes to the Buffer Pool, so no data is lost.

Typical UPDATE Flow (single row)

MySQL parses the UPDATE statement.

The engine searches the Buffer Pool for the target row; if absent, it reads the row from the data file and loads it into the Buffer Pool.

Before modifying the row, InnoDB writes a before‑image to the Undo Log.

The row is updated in the Buffer Pool.

The modification is recorded in the Redo Log Buffer.

When the transaction commits, the Redo Log Buffer is flushed to the Redo Log file according to innodb_flush_log_at_trx_commit.

Dirty pages are eventually written to the data files by a checkpoint.

On restart, InnoDB first replays the Redo Log to reconstruct the Buffer Pool state, then uses the Undo Log to roll back any uncommitted transactions.

Illustrations

References

https://xiaolincoding.com/mysql/log/how_update.html#redo-log-%E6%96%87%E4%BB%B6%E5%86%99%E6%BB%A1%E4%BA%86%E6%80%8E%E4%B9%88%E5%8A%9E

https://pdai.tech/md/db/sql-mysql/sql-mysql-execute.html

https://zhuanlan.zhihu.com/p/552706911