Mastering MySQL Binlog, Undo Log, Redo Log, and ChangeBuffer

Background

MySQL logs are essential for recording the database's runtime state, including error logs, query logs, slow query logs, binary logs (binlog), and transaction logs (redo log and undo log). Among these, binlog and transaction logs are critical but often only superficially understood.

Essential Concept Dictionary

A quick reference of key terms is provided to help readers revisit fundamental concepts when needed.

Understanding the Binary Log (Binlog)

Binlog Concept

Binlog is a logical log that records write operations (queries are not logged). The server layer writes it independently of the storage engine, appending entries to files whose size is controlled by the max_binlog_size parameter.

Binlog Uses

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

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

Binlog Recording Process and Flush Timing

Writes first go to the Binlog Buffer, then are flushed to the OS buffer and finally to the binlog file on disk. The sync_binlog parameter controls when the flush occurs (values 0, 1, or N).

Binlog Format

Before MySQL 5.7.7 the default format was STATEMENT; later versions default to ROW. The format can be set with the binlog-format parameter.

Understanding the Undo Log

Undo Log Concept

Undo log is a logical log used for rollback. For each modifying statement, it records the inverse operation (e.g., a +3 update generates a -3 entry), enabling the database to revert to the pre‑transaction state.

Undo Log Uses

Rollback: ensures atomicity by reverting changes when an error occurs.

MVCC view consistency: provides version numbers for multi‑version concurrency control.

Undo Log Summary

The key purposes are rollback and MVCC. Long‑running transactions generate many undo entries; undo logs are created after transaction start (before redo log) and are deleted when no longer needed.

Understanding the Redo Log

Redo Log Concept

Redo log is a physical log belonging to the InnoDB engine. It records page‑level changes (e.g., adding a record to a data page).

Redo Log Uses

Crash‑safe recovery: guarantees data can be restored after power loss.

Performance boost: writes are sequential (WAL), reducing random‑write I/O.

Redo Log Two‑Phase Commit

During a transaction, the engine writes the redo log in the prepare phase, the server writes the binlog, then the transaction commits, moving both logs to the commit phase.

Redo Log Recovery Process

If the redo log is complete (committed), it is used directly for recovery. If only prepare is present, the binlog is checked; a complete binlog allows committing the redo log, otherwise the transaction is rolled back.

Redo Log Flush Timing

The innodb_flush_log_at_trx_commit parameter (0, 1, 2) controls when redo logs are flushed to disk, with 1 being the safest and default.

Redo Log Storage

Redo logs are stored in a circular file structure with two pointers: write pos (current write location) and checkpoint (oldest location to be overwritten). Checkpoints advance as dirty pages are flushed.

LSN (Log Sequence Number)

LSN is an 8‑byte logical sequence number that grows with each log write. It is used to locate positions in the circular redo log and to coordinate checkpointing.

Understanding ChangeBuffer

Why Mention ChangeBuffer

ChangeBuffer is often confused with redo log; both use memory to reduce I/O, but they optimize different stages.

ChangeBuffer Concept and Purpose

When a page to be updated is not in memory, ChangeBuffer records the change instead of reading the page, saving random‑read I/O. The change is later merged into the page.

Difference Between ChangeBuffer and Redo Log

Redo log reduces random‑write I/O by converting it to sequential writes, while ChangeBuffer reduces random‑read I/O by avoiding page reads.

ChangeBuffer Merge Process

Read the target page from disk into memory.

Apply all ChangeBuffer records for that page, producing the latest version.

Write the updated page and corresponding redo log entries.

Integrated Workflow: Binlog, Undo Log, Redo Log, and ChangeBuffer

Demo Data and Operations

1、insert into ta(a,b) values(2,5),(7,5)

2、select * from t where a in (2, 7)

A sample update inserts rows into two pages, one already in memory and one not.

Without Logs or ChangeBuffer

The simplified update flow reads the page, modifies it in memory, and writes it back to disk.

Full InnoDB Process with All Logs

Update in‑memory page directly; record ChangeBuffer for off‑memory page.

Write undo log (cached, flushed according to its parameters).

Write redo log (including ChangeBuffer changes).

Set log state to prepare, write binlog, then commit.

Merge Phase During a Query

If a query accesses a page that has pending ChangeBuffer entries, the engine merges those entries into the page before reading.

Data Flush Scenarios

Redo log is full – checkpoint advances to free space.

Memory shortage – dirty pages are flushed.

System idle – background flush.

Normal shutdown – flush all dirty pages.

Flushing dirty pages also advances the redo log checkpoint.

Conclusion

The article covered MySQL's binlog, undo log, redo log, and ChangeBuffer, explaining their roles, interactions, and recovery mechanisms. Readers are encouraged to explore the remaining questions about binlog crash‑safety and the necessity of multiple logs.