Master MySQL Binlog, Redo Log, and Undo Log: Complete Guide

MySQL logs are a crucial component of the database, recording various state information during operation. The main log types include error log, query log, slow query log, transaction log, and binary log. Developers typically focus on the binary log (binlog) and transaction logs (redo log and undo log), which are detailed below.

binlog

The binlog records write operations (excluding queries) in binary form on disk. It is a logical log written by the Server layer, and every storage engine records a binlog. It can be viewed as:

Logical log : essentially records the executed SQL statements.

Physical log : the actual data changes are stored in data pages; this log records the page modifications.

The binlog is written by appending; the max_binlog_size parameter controls the size of each binlog file, creating a new file when the limit is reached.

binlog usage scenarios

In practice, the main uses of binlog are:

Master‑slave replication : the master enables the binlog, sends it to each slave, and the slaves replay the binlog to keep data consistent.

Data recovery : the mysqlbinlog tool can be used to restore data from the binlog.

binlog flush timing

The sync_binlog parameter controls when the binlog is flushed to disk. Its values are 0‑N:

0: let the system decide when to write to disk.

1: flush the binlog to disk after every transaction commit.

N: flush after every N transactions.

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

binlog log format

The binlog supports three formats: STATEMENT, ROW, and MIXED. Before MySQL 5.7.7 the default was STATEMENT; from 5.7.7 the default changed to ROW. The format is set via the binlog-format variable.

In MySQL 5.7.7 and later, the default binlog-format is ROW . Earlier versions defaulted to STATEMENT .

redo log

Why redo log is needed

Durability, one of the four ACID properties, requires that once a transaction commits, its changes are permanently stored. MySQL achieves this efficiently using the redo log, which records only the modifications made to data pages.

redo log basic concept

The redo log consists of two parts: an in‑memory redo log buffer and an on‑disk redo log file. Each DML statement first writes to the buffer; later, multiple entries are flushed to the file as a batch. This write‑ahead logging (WAL) technique ensures that changes are safely persisted before the data pages themselves are flushed.

When a transaction commits, the buffer is flushed to the file according to the innodb_flush_log_at_trx_commit setting:

0: the system decides when to write the log.

1: the log is written to disk on every commit.

N: the log is written after every N transactions.

redo log write timing

In InnoDB, the innodb_flush_log_at_trx_commit parameter controls when the redo log is flushed to disk. Values of 0, 1, or N provide a trade‑off between performance and durability.

During recovery, InnoDB compares the LSN (log sequence number) of data pages with that of the redo log. If a page’s LSN is lower, recovery replays the redo log from the checkpoint to bring the page up to date. Because redo logs record physical page changes, recovery is faster than replaying logical binlogs.

undo log

Atomicity, another ACID property, is implemented via the undo log. The undo log records the logical changes needed to roll back a transaction. For example, an INSERT generates a corresponding DELETE entry in the undo log, and an UPDATE generates an opposite UPDATE entry. The undo log also underpins MVCC (multi‑version concurrency control).

Difference between binlog and redo log

The binlog is primarily for archiving and replication; it is not crash‑safe on its own. The redo log provides crash safety but is InnoDB‑specific and its records are overwritten after being applied. Therefore, both logs must be recorded to guarantee that no data is lost after a crash or restart.