Understanding MySQL Binlog, Redo Log, and Undo Log

MySQL logs are a crucial component of the database system, recording various runtime states. The main log types include error, query, slow query, transaction, and binary logs. For developers, the most important are the binary log (binlog) and transaction logs (redo log and undo log), which are described in detail 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 generated by all storage engines.

Logical log: essentially records the executed SQL statements. Physical log: records changes to data pages.

Binlog entries are appended; the max_binlog_size parameter controls the size of each binlog file, after which a new file is created.

binlog usage scenarios

Two primary scenarios use binlog:

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

Data recovery: using the mysqlbinlog tool to restore data.

binlog flush timing

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

0 – No explicit flush; the OS decides when to write.

1 – Flush to disk on every transaction commit.

N – Flush after every N transactions.

Setting sync_binlog=1 provides the highest safety (default since MySQL 5.7.7), while larger values can improve performance at the cost of some durability.

binlog formats

Three formats are supported: 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 via binlog_format .

STATEMENT (statement‑based replication): records the SQL statements that modify data. Advantages: smaller log size and lower I/O. Disadvantages: can cause inconsistency for nondeterministic functions (e.g., SYSDATE(), SLEEP()).

ROW (row‑based replication): records the actual row changes. Advantages: reliable replication of all operations. Disadvantages: larger logs, especially during large schema changes.

MIXED : combines both; uses STATEMENT by default and switches to ROW when STATEMENT cannot safely replicate.

redo log

Why redo log is needed

To guarantee transaction durability without the performance penalty of flushing every modified data page on each commit, MySQL writes only the modifications to a redo log, which is much smaller and can be flushed sequentially.

Basic concepts of redo log

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

The flush timing is controlled by innodb_flush_log_at_trx_commit:

Redo log record form

Redo log uses a fixed‑size circular buffer. New entries are written at the write position; when the end is reached, it wraps around. The checkpoint marks the point up to which data pages have been flushed, freeing space for new log records.

During InnoDB startup, recovery checks the LSN of data pages against the log LSN; if the page LSN is smaller, redo log entries from the checkpoint are applied to bring the pages up to date.

Difference between redo log and binlog

Binlog is for archival and replication, but lacks crash‑safety on its own. Redo log provides crash‑safe durability for InnoDB but is engine‑specific and its records are overwritten after checkpoint. Both logs are required to ensure no data loss after a crash.

undo log

Undo log implements transaction atomicity. For each DML operation, an opposite operation is recorded (e.g., INSERT → DELETE, UPDATE → reverse UPDATE). This enables rollback on error and supports MVCC (multi‑version concurrency control).

© This article is part of the "Big Data Technology and Architecture" collection, authored exclusively for the import_bigdata WeChat public account.