How MySQL’s Write‑Ahead Log Protects Data During Power Outages

Background

A junior developer worries that a sudden power cut during a batch insert might corrupt the MySQL database. A colleague reassures him that MySQL’s write‑ahead log (WAL) and transaction support will protect the data.

What Is Write‑Ahead Logging?

Databases normally keep tables and indexes on disk, writing changes first to memory. WAL records every modification in a log before the data is flushed to permanent storage, allowing the system to recover consistently after a crash.

Direct disk writes for each change are slow, so buffering in memory and batch‑writing improves performance.

If power is lost while data is being written, the log enables MySQL to restore consistency on restart.

MySQL’s WAL Components

InnoDB implements WAL using several structures:

Buffer Pool

The in‑memory cache that stores frequently accessed pages, reducing I/O by serving reads from RAM instead of disk.

Redo Log Buffer

A memory area that accumulates changes before they are written to the redo log file. Its size is controlled by innodb_log_buffer_size (default 16 MB). The flush timing is set by innodb_flush_log_at_trx_commit:

0 – never flush to disk immediately

1 – flush to disk on every commit

2 – flush to OS cache on commit

Redo Log

The persistent log file that records committed changes. It is written when the buffer is flushed, when the buffer is half full, every second by a background thread, or on normal shutdown.

Undo Log

Stores before‑image copies of modified rows so that uncommitted or rolled‑back transactions can be undone, preserving atomicity.

The combined workflow for updating a row is:

Prepare the UPDATE statement.

InnoDB searches the Buffer Pool (or loads the page from disk) for the target row.

The original row version is saved to the Undo log.

The update is applied in the Buffer Pool.

Modified data is written to the Redo Log Buffer.

On commit, the Redo Log Buffer is flushed to the Redo Log file according to innodb_flush_log_at_trx_commit.

During restart, the Redo Log is replayed to restore the Buffer Pool.

Extra Topic: Checkpoints

Checkpoints periodically force dirty pages and the Redo Log to be written to data files, ensuring durability and limiting the size of the log.

Sharp Checkpoint

All dirty pages are flushed—typically when the instance shuts down.

Fuzzy Checkpoint

Only a subset of dirty pages is flushed when a threshold is reached, which is the common case during normal operation.

Checkpoints also allow the Redo and Undo logs to be overwritten, reducing recovery time after a crash.

Power‑Loss Recovery Scenarios

Case 1: Power loss before Redo Log is written – The transaction is considered failed; on restart MySQL uses the Undo log to roll back the partial changes, so no data is lost.

Case 2: Power loss after Redo Log is persisted – The committed changes are recovered by replaying the Redo Log, so the data remains intact.

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

Conclusion

The WAL mechanism in MySQL’s InnoDB engine, together with Buffer Pool, Redo/Undo logs, and checkpoints, guarantees data consistency and durability even in the event of abrupt power failures, making MySQL a reliable choice for mission‑critical applications.