How InnoDB Redo Logs Safeguard MySQL: Generation, Buffering, and Flushing Explained

Overview

InnoDB must generate Redo logs for every DML and DDL operation to guarantee data durability. Although creating and writing these logs consumes extra resources, they become essential when a crash occurs.

Redo Log Generation

Each simple INSERT produces two Redo log records: one storing the auto‑increment column's maximum value and another storing the row's field values. Logs are temporarily held in an m_log linked list of 512‑byte blocks within a mini‑transaction (mtr), which groups related operations.

Writing to the Log Buffer

In MySQL 8.0, multiple threads can write Redo logs to the log buffer in parallel. Each thread reserves a contiguous LSN range ( start_sn to end_sn) calculated atomically, then converts it to an LSN range ( start_lsn to end_lsn) that maps to a slot in recent_written.m_links. The slot index is start_lsn % SLOT_COUNT (default 1,048,576). After a thread finishes its mtr, it writes end_lsn into the corresponding slot, allowing the log_writer to identify continuous log regions.

Writing to the Log File

The log_writer thread copies Redo logs from the log buffer to the log file buffer, but only for continuous LSN intervals. It uses recent_written.m_tail to track the highest LSN that forms a continuous sequence. When a slot contains zero or an older end_lsn, the continuous region ends, and the writer flushes up to m_tail and updates log_sys.write_lsn. After flushing, it notifies waiting user threads via log.write_events.

Log File Flushing

The log_flusher thread periodically (≈1 s) writes the log file buffer to disk. It compares log_sys.write_lsn with log_sys.flushed_to_disk_lsn; if the former is larger, flushing is required. Flushing can also be triggered immediately by transaction commit events (e.g., innodb_flush_log_at_trx_commit=1), which signal log.flusher_event. After a flush, the thread updates flushed_to_disk_lsn and notifies waiting threads via log.flush_events.

Summary

Redo logs are written in groups (mtr) to the log buffer without locks, using pre‑allocated LSN ranges. The log_writer ensures only continuous log segments are persisted to the log file, while the log_flusher guarantees those segments reach durable storage. Together, these mechanisms provide crash‑safe recovery for MySQL.