Reference · Cheat sheet

Durability: redo & undo logs

The one-page compressed essence: log first, pages later — redo rolls forward, undo rolls back.

Lesson: 0007Context: InnoDB, MySQL 8.0+

The one idea

Write-ahead logging

At COMMIT, only the small sequential redo append must be durable. Data pages stay dirty in the buffer pool and flush later. Log first, pages later — cheap and crash-safe.

Two logs, opposite jobs

Log	Direction	Used for
redo	roll forward	crash recovery — replay committed-but-unflushed changes
undo	roll back	ROLLBACK + MVCC old-version reads (0005)

Mnemonic: redo = re-do committed work after a crash; undo = un-do uncommitted work (and remember old rows for readers).

The durability dial

innodb_flush_log_at_trx_commit:

Value	At commit	Max loss
1 (default)	write + fsync redo	nothing — full ACID
2	write to OS cache; fsync ~1s	≤1s on OS/power crash only (mysqld crash safe)
0	write + fsync ~1s (not at commit)	≤1s even on mysqld crash

Vocabulary

buffer pool	in-memory cache of data/index pages
dirty page	modified page not yet written to the data files
LSN	log sequence number — redo's ever-growing byte counter
checkpoint	how far dirty pages are guaranteed flushed; redo before it can be reused

Inspect it

SHOW VARIABLES LIKE 'innodb_flush_log_at_trx_commit'; -- 1 = full ACID SHOW ENGINE INNODB STATUS\G -- LOG section: LSN, "Log flushed up to", checkpoint START TRANSACTION; UPDATE …; ROLLBACK; -- undo restores the old value

Source: MySQL 8.0 Manual — Redo Log · Undo Logs · flush_log_at_trx_commit · All lessons