Understanding MySQL Binlog, Redo Log, and Undo Log: Mechanisms and Differences

In MySQL, logs are essential components that record various state information during operation.

The main log types include error log, general query log, slow query log, transaction log, and binary log.

For developers, the focus is on the binary log (binlog) and transaction logs (redo log and undo log). The article introduces these three logs in detail.

Binlog

Binlog records write operations (excluding reads) in binary form on disk. It is a logical log recorded by the Server layer and is written by any storage engine.

Logical log : essentially records the SQL statements.

Physical log : the actual data changes stored in data pages.

Binlog is written by appending; its size is controlled by the max_binlog_size parameter, which creates a new file when the limit is reached.

Binlog usage scenarios

The primary use cases are master‑slave replication and data recovery.

Master‑slave replication : The master enables binlog, sends it to slaves, and slaves replay the binlog to achieve data consistency.

Data recovery : Tools such as mysqlbinlog can be used to restore data.

Binlog flush timing

In InnoDB, the binlog is flushed to disk according to the sync_binlog setting, which can be 0 (let the system decide), 1 (flush on every commit), or N (flush every N transactions). Setting it to 1 provides the safest durability, which is the default from MySQL 5.7.7 onward, while larger values can improve performance at the cost of some consistency.

Binlog format

Binlog can be written in three formats: STATEMENT, ROW, and MIXED. Before MySQL 5.7.7 the default was STATEMENT; after that the default is ROW. The format is controlled by the binlog_format variable.

STATEMENT: replicates based on SQL statements; advantages: smaller log size and higher performance; disadvantages: may cause inconsistency for certain statements. ROW: replicates based on row changes; advantages: accurate replication; disadvantages: larger log size, especially for ALTER TABLE. MIXED: combines STATEMENT and ROW, using ROW when STATEMENT cannot replicate certain operations.

Redo Log

Redo log ensures durability of transactions. It records the physical changes to data pages, allowing MySQL to write modifications to a log buffer first and later flush them to the redo log file, implementing write‑ahead logging (WAL).

Redo log basic concepts

Redo log consists of an in‑memory redo log buffer and an on‑disk redo log file. Each DML statement writes to the buffer, and at certain points the buffer is flushed to the file.

The flush timing is controlled by innodb_flush_log_at_trx_commit, which can be 0, 1, or 2, determining when the log buffer is synchronized to disk.

During crash recovery, InnoDB checks the LSN of data pages against the LSN in the redo log. Pages with LSN lower than the log are recovered from the checkpoint; pages with higher LSN are already up‑to‑date.

Redo log uses a circular buffer; when the write position reaches the end, it wraps around to the beginning. The checkpoint marks the point up to which data pages have been flushed.

The write position indicates the current LSN in the redo log; the checkpoint indicates the LSN up to which data pages have been flushed.

Redo log vs. Binlog

Binlog is primarily for archiving and replication and is not crash‑safe on its own. Redo log is crash‑safe and specific to InnoDB, but its records are overwritten after checkpoint. Therefore both logs are needed to guarantee data integrity after a crash.

Undo Log

Undo log implements atomicity. It records the logical undo information for each DML operation (e.g., an INSERT generates a corresponding DELETE entry). It also underlies MVCC, enabling consistent reads.