Understanding MySQL Binlog, Redo Log, and Undo Log

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

Binlog

The binlog records all write operations (excluding reads) in binary form on disk. It is a logical log created by the Server layer and is written by every MySQL instance regardless of storage engine.

Logical log : essentially stores the executed SQL statements.

Physical log : MySQL ultimately stores data in pages; the physical log records changes to those pages.

The binlog is written by appending; its size is controlled by the max_binlog_size parameter, after which a new file is created.

Binlog usage scenarios

Two primary scenarios: master‑slave replication and data recovery . In replication, the master enables the binlog, sends it to slaves, and slaves replay it to keep data consistent. For recovery, the mysqlbinlog tool can restore data from the binlog.

Binlog flush timing

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

0 – system decides when to write; roughly once per second.

1 – flush on every transaction commit (safest but slower).

N – flush after N transactions (better performance, less safety).

MySQL 5.7.7 and later default to sync_binlog = 1 , but a larger value can be set to improve performance at the cost of some durability.

Binlog format

Three possible formats: STATEMENT , ROW , and MIXED . Before MySQL 5.7.7 the default was STATEMENT ; after that it is ROW . The format is set with the binlog-format variable.

In MySQL 5.7.7 and later, the default binlog format is ROW , which records row‑level changes, while STATEMENT records the original SQL statements.

STATEMENT : records the SQL statements; lower log volume, higher performance, but can cause inconsistencies in some cases.

ROW : records the actual row changes; avoids many replication issues but generates larger logs.

MIXED : uses STATEMENT where safe and falls back to ROW for statements that cannot be safely replicated.

Redo Log

Why redo log is needed

To guarantee the durability property of transactions, MySQL writes modifications to a redo log instead of flushing every changed page to disk immediately, which would be inefficient.

Redo log basics

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

Flushing behavior is controlled by innodb_flush_log_at_trx_commit :

Parameter value

Meaning

0 (delayed write)

Buffer is written to OS cache and flushed to disk roughly every second; up to one second of data may be lost on crash.

1 (real‑time write & flush)

Buffer is written and flushed to disk on every transaction commit; safest but slower.

2 (real‑time write, delayed flush)

Buffer is written on each commit, but flushed to disk only once per second.

Redo log record form

Redo logs use a fixed‑size circular buffer; when the end is reached, writing wraps to the beginning.

Redo log vs. binlog

Redo log

Binlog

File size

Fixed size

Configurable via

max_binlog_size

Implementation

InnoDB engine layer

Server layer, usable by all engines

Recording method

Circular write

Append‑only, new files when size limit reached

Use case

Crash recovery

Replication and data recovery

Both logs are needed: redo log ensures crash‑safe recovery, while binlog provides archival and replication capabilities.

Undo Log

Undo log implements the atomicity property of transactions. It records logical changes so that each INSERT has a corresponding DELETE entry and each UPDATE has an opposite UPDATE entry, allowing the system to roll back to the state before the transaction.

Undo log is also a key component of MVCC (multi‑version concurrency control).

When InnoDB starts, it checks the LSN (log sequence number) of data pages against the redo log. If the page LSN is smaller, recovery replays logs from the checkpoint; if larger, the extra part is ignored because it has already been applied.

In summary, the combination of binlog and redo log ensures that MySQL can both replicate data across servers and recover safely after crashes, while the undo log provides transaction atomicity and supports MVCC.