Understanding MySQL Crash‑Safe Mechanism: WAL, Redo/Undo/Binlog, Two‑Phase and Group Commit, and Recovery Process

MySQL, as the most popular open‑source relational database, must guarantee that data is never lost. This article explains how MySQL is designed to be crash‑safe, i.e., it can recover to any point in time and ensure that a sudden crash never results in lost committed transactions.

Two key capabilities :

Point‑in‑time recovery using sufficient binary logs (binlog).

Crash‑safe execution where any transaction, even if the server crashes at any stage, will either be fully committed or automatically rolled back. This relies on the redo log and undo log.

The crash‑safe property is implemented by the Write‑Ahead Log (WAL) technique. In InnoDB, the transaction lifecycle is recorded in three core logs:

Redo log – records physical page changes; used to replay committed changes after a crash.

Undo log – records logical inverse operations; provides rollback and MVCC.

Binlog – records the original SQL statements; used for replication and point‑in‑time recovery.

The article walks through the execution flow of an UPDATE statement, showing how MySQL writes to memory, records changes in the redo log, logs the SQL in the binlog, and finally flushes both logs to disk asynchronously.

The article then introduces the WAL mechanism, explaining why MySQL writes to memory first and logs changes before persisting to disk: sequential writes to log files are far faster than random writes to data files.

Two‑Phase Commit (2PC)

To keep redo log and binlog consistent, MySQL splits the redo‑log write into a prepare and a commit phase, inserting the binlog write between them. This ensures that both logs reflect the same transaction order, which is essential for master‑slave consistency.

The article also shows a concrete example where interleaving redo‑log and binlog writes incorrectly would cause data divergence between primary and replica.

When multiple transactions commit concurrently, simple 2PC can still lead to ordering mismatches. The following code snippet illustrates such a scenario:

T1 (
--prepare--binlog---------------------commit)
T2 (
-----prepare-----binlog----commit)
T3 (
--------prepare-------binlog------commit)
解析：
redo log
prepare
的顺序：T1
--》T2 --》T3
binlog
的写入顺序：T1
--》 T2 --》 T3
redo
log
commit
的顺序：T2
--》 T3 --》T1
结论：由于
binlog
写入的顺序和
redo
log
的提交结束的顺序不一致，导致
binlog
和
redo
log
所记录的事务提交结束的顺序不一样，最终导致的结果就是主从数据不一致。

To mitigate the lock contention of 2PC, MySQL 5.6 introduced Binary Log Group Commit (BLGC) . BLGC groups multiple transactions and flushes their binlogs together, reducing the number of fsync operations and the lock granularity. The process consists of three stages:

Flush Stage : leader acquires Lock_log , writes a batch of binlog entries to the cache.

Sync Stage : leader releases Lock_log , acquires Lock_sync , and performs a single fsync for the whole batch.

Commit Stage : leader releases Lock_sync , acquires Lock_commit , and commits each transaction in InnoDB, clearing undo information.

Group commit dramatically reduces I/O overhead and lock contention, while still preserving the order between redo and binlog.

Data Recovery Process

If MySQL crashes during a transaction, the recovery procedure examines the redo log for prepared transactions, matches them with binlog entries, and either re‑commits the redo log or rolls back using the undo log. The article enumerates four crash moments (A‑D) and explains the recovery actions for each.

Finally, the article summarizes the key takeaways:

WAL is the fundamental technique for consistency and durability in most databases.

MySQL’s redo log, undo log, binlog, two‑phase commit, and group commit together provide robust crash‑safe guarantees.

The recovery workflow validates logs at various crash points to ensure no committed data is lost.