Understanding MySQL Binlog, Redo Log, and Undo Log: How They Ensure Data Consistency

Binlog

Binlog records write‑type operations of MySQL in binary form and serves as the logical log for the server layer. It can be viewed as a logical log (recording SQL statements) and a physical log (recording changes to data pages).

Logical log : essentially records the SQL statements.

Physical log : MySQL finally stores data in data pages, and the physical log records the changes to those pages.

Binlog is written by appending; the

max_binlog_size

parameter controls the size of each binlog file, and a new file is created when the limit is reached.

Binlog usage scenarios

The main scenarios are master‑slave replication and data recovery.

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

Data recovery : Use the

mysqlbinlog

tool to restore data.

Binlog flush timing

For the InnoDB storage engine, only when a transaction commits is the redo log written. The

sync_binlog

parameter controls when the binlog is flushed to disk: 0 (no forced sync, system decides), 1 (flush on every commit), N (flush every N transactions). Setting

sync_binlog=1

is the safest and is the default from MySQL 5.7.7, while larger values can improve performance at the cost of durability.

Binlog format

Binlog supports three formats:

STATEMENT

,

ROW

, and

MIXED

. Before MySQL 5.7.7 the default was

STATEMENT

; from MySQL 5.7.7 onward the default is

ROW

. The format is selected via the

binlog_format

variable.

STATEMENT : statement‑based replication (SBR) records each modifying SQL statement in the binlog. ROW : row‑based replication (RBR) records the actual row changes, avoiding issues with stored procedures, functions, or triggers. MIXED : combines STATEMENT and ROW, using ROW for operations that cannot be safely replicated with STATEMENT.

Redo Log

Redo log guarantees durability (the “D” in ACID). Instead of flushing every modified page to disk at commit, MySQL writes the changes to the redo log buffer first, then later writes the buffer to the redo log file, achieving write‑ahead logging (WAL).

Each DML statement writes to the

redo log buffer

; at a later point the buffer is flushed to the

redo log file

. This process involves the OS buffer and the

fsync()

system call.

The flushing timing is controlled by

innodb_flush_log_at_trx_commit

with values 0, 1, or 2, defining when the redo log buffer is written to disk.

Redo log uses a fixed‑size circular buffer. The write position marks the current LSN; the checkpoint marks the LSN of the last flushed data page. When the write position catches up to the checkpoint, the checkpoint advances, freeing space for new log records.

During InnoDB startup, regardless of a normal or abnormal shutdown, MySQL recovers using the redo log because it records physical page changes, making recovery faster than using logical binlog alone.

Undo Log

Undo log implements atomicity (the “A” in ACID). It records logical changes so that each

INSERT

has a corresponding

DELETE

entry and each

UPDATE

has an opposite

UPDATE

entry, enabling rollback to the state before the transaction.

Undo log is also the key mechanism for MVCC (multi‑version concurrency control), allowing consistent reads without locking.