Understanding MySQL Binlog, Redo Log, and Undo Log: Ensuring Data Consistency

Binlog

Binlog records all write operations (excluding reads) of MySQL in binary form and is a logical log generated by the server layer for any storage engine. It is written by appending; the max_binlog_size parameter controls the maximum size of each binlog file.

Logical log: essentially stores the executed SQL statements.

Physical log: records changes to data pages on disk.

Typical use cases are master‑slave replication and data recovery.

Replication: the master enables binlog, streams it to slaves, and slaves replay the log to keep data consistent.

Data recovery: the mysqlbinlog utility can replay binlog files to restore data.

The sync_binlog variable controls when binlog is flushed to disk (0‑N):

0 – MySQL decides automatically.

1 – Flush to disk on every transaction commit (default after MySQL 5.7.7).

N – Flush after every N transactions, improving performance at the cost of some durability.

Binlog Format

MySQL supports three binlog formats: STATEMENT, ROW, and MIXED. Before MySQL 5.7.7 the default was STATEMENT; from 5.7.7 onward the default is ROW. The format is set with the binlog_format variable.

In MySQL 5.7.7 and later, the default binlog format is ROW.

STATEMENT (statement‑based replication): records the original SQL statements.

Pros: smaller log size, less I/O.

Cons: can cause inconsistency for nondeterministic functions like SYSDATE() or SLEEP().

ROW (row‑based replication): records the actual row changes.

Pros: avoids the nondeterministic‑statement problem.

Cons: generates larger logs, especially during ALTER TABLE operations.

MIXED : uses STATEMENT by default and switches to ROW for statements that cannot be safely replicated.

Redo Log

Redo log guarantees durability of committed transactions. Instead of flushing every modified data page to disk at commit (which would be I/O‑intensive), MySQL writes the modifications to an in‑memory redo‑log buffer and later flushes the buffer to a redo‑log file using Write‑Ahead Logging (WAL).

Redo Log Components

Redo log consists of two parts: the in‑memory redo‑log buffer and the on‑disk redo‑log file.

When a DML statement executes, the change is first written to the buffer; at a later point the buffered records are flushed to the file. This two‑step process is illustrated below:

The innodb_flush_log_at_trx_commit parameter controls when the buffer is flushed:

0 – MySQL decides the timing.

1 – Flush to disk on every transaction commit.

2 – Flush once per second.

Redo log uses a fixed‑size circular buffer. The diagram shows write position (LSN), checkpoint LSN, and how space is reclaimed when the write pointer catches up to the checkpoint.

During InnoDB startup, recovery reads the redo log to replay any unflushed page changes, which is faster than replaying logical binlog entries because redo log records physical page modifications.

Undo Log

Undo log implements the atomicity property of transactions. For each DML operation, an opposite operation is recorded in the undo log (e.g., an INSERT generates a DELETE entry, an UPDATE generates an inverse UPDATE). This enables the transaction to be rolled back completely if an error occurs.

Undo log also underpins MVCC (multi‑version concurrency control), allowing consistent reads without locking.