The idea: replay the log, then undo the unfinished
InnoDB does not write changed pages at commit. It writes redo log records and keeps the 16 KB pages dirty in the buffer pool, writing them later in any order. It may even write a page that holds a change of a transaction that has not committed yet. So after a crash the data files are a mix: some changes missing, some uncommitted changes present, maybe a page half written. Recovery puts this right in a fixed order: find where to start, repair torn pages, redo every logged change, then roll back every transaction that did not commit. Because MySQL also has a binlog, a transaction caught between the two logs is settled by the binlog.
The page models one table t with three rows on pages P3, P4 and P5 of t.ibd, one undo page U9, two connections and a binlog. See How MySQL Runs a Query for where these parts sit in the server.
What a change leaves behind
An UPDATE runs as one mini-transaction (mtr). It first writes an undo record ("id 1 was 100") into an undo page, then changes the row on its data page. Both are page changes, so both get redo records: UNDO_INSERT for the undo page and REC_UPDATE for the data page. Each page remembers the LSN (log sequence number, a byte position in the redo log) of the last record that changed it: the page LSN. Redo goes first into the log buffer and reaches the redo files when the log writer flushes it. See the InnoDB redo log for mtrs, the log buffer and checkpoint age.
1. The checkpoint LSN
A checkpoint records an LSN, checkpoint_lsn, in the header of the redo log. Every change before it is already in the data files, because InnoDB only moves the checkpoint past a change once the page holding it has been flushed. Recovery starts reading the redo log there. The page keeps the checkpoint fixed at 8000; a busy server moves it forward all the time (fuzzy checkpoints), which keeps recovery short.
2. Torn pages and the doublewrite buffer
A 16 KB page is written by the disk in smaller pieces, often 4 KB. If the power fails in the middle, the page on disk is torn: part new, part old, and its checksum fails. A redo record like "set bal = 110 in row 1" cannot be applied to a page that is garbage. So the page cleaner writes every page twice: first into the doublewrite buffer (files #ib_16384_0.dblwr in MySQL 8.0.20 and later), fsync, then to its home place in the tablespace. At startup InnoDB checks the pages in the doublewrite file: if the home copy is torn, the whole copy from the doublewrite file replaces it. If the doublewrite write itself was torn, the home page was never touched and is still the old, whole version. Demo: torn page, doublewrite repairs it. See the doublewrite buffer.
3–4. Redo scan and apply
InnoDB reads the redo files from checkpoint_lsn to the last complete record and parses the records into a hash table keyed by page (recv_sys). Then it reads each page once and applies that page's records in LSN order, but only where page LSN < record LSN. A page that was flushed after the change already has a page LSN at least as large, so the record is skipped. That one test makes redo idempotent: running recovery twice, or crashing during recovery, gives the same pages. Records that never reached the redo files are gone, and so are the changes they described, which is fine: their transactions had not committed (a COMMIT waits for its redo). Demo: committed, only in redo brings back a committed update whose page was never flushed.
5. Undo is rebuilt by redo
Undo pages are ordinary pages in the undo tablespaces, logged like any other page. So the redo pass also rebuilds them, even if they were never flushed. Each transaction's undo log has a header with its state: ACTIVE, PREPARED or committed. After redo, InnoDB reads the undo logs and builds the transaction table again: every ACTIVE and PREPARED transaction comes back, with its row locks. Committed ones need nothing more; purge removes their undo later (see undo logs and purge).
6. PREPARED transactions and the binlog
With the binlog on, a COMMIT is an internal two-phase commit between InnoDB and the binlog. Phase 1, prepare: InnoDB marks the undo log PREPARED and flushes the redo (innodb_flush_log_at_trx_commit = 1). Phase 2: the server writes the transaction's events and an Xid event to the binlog and fsyncs it (sync_binlog = 1). Phase 3, commit: InnoDB marks the undo log committed. The binlog write is the commit point, because replicas and point-in-time restore read the binlog.
At startup the server scans the last binlog file for Xid events and asks InnoDB for its PREPARED transactions. Xid in the binlog: commit. Xid missing: roll back. Either way InnoDB ends up agreeing with the binlog, so a replica never has a transaction the source lost, or the other way round. Demos: prepared, Xid in binlog → commit; prepared, no Xid → rollback. See binlog group commit for how the three phases are batched.
| found after redo | Xid in binlog? | outcome |
|---|---|---|
| committed undo log | (yes) | nothing to do ✓ |
| PREPARED | yes | commit ✓ |
| PREPARED | no | roll back ✗ |
| ACTIVE | no | roll back ✗ (background) |
7. Rolling back ACTIVE transactions
InnoDB uses a steal policy: the page cleaner may write a page holding an uncommitted change. The WAL rule makes that safe: before the page is written, the redo up to its page LSN is flushed, and that redo includes the undo record (written first, in the same mtr). After a crash the ACTIVE transaction is rolled back from its undo log, newest record first. Each restore is a normal page change with its own redo. In MySQL 8.0 this runs in a background thread after the server already accepts connections; the rows stay locked until it finishes. Demo: uncommitted page flushed (steal): redo skips P4 (already on disk), but undo puts bal 200 back.
InnoDB vs PostgreSQL
PostgreSQL recovery is redo only: it replays WAL from the checkpoint's REDO point and stops. A transaction without a COMMIT record simply counts as aborted; its new row versions stay in the heap, invisible, until VACUUM removes them. InnoDB updates rows in place and keeps old versions in undo, so it must undo. InnoDB is close to textbook ARIES (redo history, then undo losers), and PostgreSQL is not. See PostgreSQL WAL.
| InnoDB | PostgreSQL | |
|---|---|---|
| Start point | checkpoint_lsn in the redo header | REDO point in pg_control |
| Torn pages | doublewrite buffer | full-page images in the WAL |
| Redo test | page LSN < record LSN | pd_lsn < record LSN |
| Uncommitted changes | rolled back from undo | left in place, invisible |
| Two logs | redo + binlog, settled by Xid | one WAL (replication reads it) |
What the page leaves out
Real redo records are grouped by mtr and a page LSN is the end of the mtr, not the record's start; redo log blocks, checksums and the 8.0.30 #innodb_redo file set; the checkpoint moving forward and fuzzy checkpoints; crash during recovery; the change buffer merge; innodb_force_recovery; data dictionary transactions rolled back before startup ends; rollback segments, insert vs update undo and purge; the binlog's own crash safety (a truncated last event); the Xid being a separate number from the trx id; and the fact that MySQL 5.7+ skips the fsync of the InnoDB commit mark, because the binlog already makes the commit durable. Here there are three rows, one undo page and two connections.