Understanding MySQL Binlog, Redo Log, and Undo Log: A Deep Dive

Background

MySQL logs are essential components that record various state information during database operation. The main log types include error log, general query log, slow query log, transaction logs, and binary logs (binlog).

Developers mainly focus on the binary log ( binlog) and transaction logs ( redo log and undo log), which are detailed below.

Binlog

The binlog records all write operations (excluding reads) in binary form on disk. It is a logical log managed by the MySQL server and is written by all storage engines.

Logical log : essentially records the executed SQL statements.

Physical log : the actual data page changes stored by MySQL.

The binlog is appended sequentially; its size is limited by max_binlog_size, after which a new file is created.

Binlog usage scenarios

Two primary scenarios: master‑slave replication and data recovery .

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

Data recovery : use the mysqlbinlog tool to restore data.

Binlog flush timing

For InnoDB, the sync_binlog parameter controls when the binlog is flushed to disk. Values:

0 – let the system decide.

1 – flush after every transaction commit.

N – flush after N transactions.

Setting sync_binlog=1 is safest (default from MySQL 5.7.7), though larger values can improve performance at the cost of some durability.

Binlog format

Three formats exist: STATEMENT, ROW, and MIXED. The default changed from STATEMENT to ROW starting with MySQL 5.7.7, configurable via binlog_format.

Before MySQL 5.7.7 the default was STATEMENT ; from 5.7.7 onward it is ROW .

Redo Log

Why redo log is needed

To guarantee the durability property of transactions, MySQL writes changes to a redo log instead of flushing each modified page immediately, avoiding costly random I/O and reducing write volume.

Redo log basics

The redo log consists of an in‑memory buffer ( redo log buffer) and an on‑disk file ( redo logfile). Each DML statement first writes to the buffer; later the buffer is flushed to the file, implementing Write‑Ahead Logging (WAL).

Flushing is controlled by innodb_flush_log_at_trx_commit, with values 0, 1, or 2 (N) determining when the buffer is written to the OS buffer and when fsync() forces it to disk.

During recovery, InnoDB compares LSNs of data pages and the redo log; pages with older LSNs are updated from the redo log, ensuring crash‑safe recovery.

Redo log record form

Redo logs use a fixed‑size circular buffer; new records are written between the current write position and the checkpoint. When the write position catches up to the checkpoint, the checkpoint advances to free space.

On startup, InnoDB checks page LSNs against the redo log LSN and replays logs from the checkpoint if needed.

Undo Log

Purpose of undo log

The undo log implements transaction atomicity. It records logical changes (e.g., an INSERT is logged as a corresponding DELETE entry, an UPDATE as the inverse UPDATE) so that a transaction can be rolled back to its previous state.

Undo logs also provide the basis for MVCC (multi‑version concurrency control) in InnoDB.