Understanding MLOG_CHECKPOINT in MySQL InnoDB Crash Recovery

This article explains the MLOG_CHECKPOINT mechanism in MySQL InnoDB crash recovery. During recovery, after applying redo logs and before executing undo operations, the system must establish a mapping between space IDs and file paths. To achieve this, InnoDB writes various MLOG_FILE logs (CREATE2, RENAME2, DELETE, NAME) when files are created, renamed, deleted, or first modified.

A critical issue arises when checkpoints occur: not all MLOG_FILE_NAME records for modified data pages may exist after a checkpoint, potentially leaving gaps in the mapping. The article illustrates this with diagrams showing how a checkpoint might fall between a file's first modification (which writes an MFN log) and subsequent page modifications.

MLOG_CHECKPOINT was introduced to solve this problem. During checkpoint creation, InnoDB scans all active data files and writes MLOG_FILE_NAME logs for any files whose max_lsn exceeds the checkpoint LSN, then writes an MLOG_CHECKPOINT marker. This ensures that when recovery starts from a given checkpoint, all necessary file mappings are complete.

The article traces the evolution of this mechanism: MySQL 5.6 scanned all data files to build mappings, 5.7 introduced MLOG_CHECKPOINT via WL#7142, and 8.0 replaced it with WL#9499, using separate tablespaces.open files and MLOG_FILE_OPEN logs. The author, who reviewed WL#9499, notes that while MLOG_CHECKPOINT served its purpose, simpler and more reliable alternatives exist.

Alternative approaches discussed include using InnoDB's data dictionary tables, though this would require complex recovery procedures. The article concludes that while MLOG_CHECKPOINT completed its historical mission, the 5.6 approach was simpler and more reliable, while 8.0's hybrid solution represents a good compromise.