Mastering MySQL’s Three Core Logs: Redo, Undo, and Binlog Explained

1. Overview of MySQL Log Types

MySQL maintains three core logs: redo log (ensures transaction durability), undo log (supports atomic rollback and MVCC), and binlog (records logical changes for backup and replication).

2. Log Hierarchy and Purpose

Redo and undo logs belong to the InnoDB storage‑engine layer.

Binlog belongs to the MySQL server layer and can be used by any storage engine.

3. Redo Log (Write‑Ahead Logging)

3.1 Why Redo Log Is Needed

Redo log acts like an "auto‑save" feature: it writes changes to a memory buffer first, then flushes them to disk, guaranteeing crash‑safe recovery even if the server crashes before the data pages are flushed.

3.2 How Redo Log Works

Modify data in the Buffer Pool (in‑memory).

Write the modification to the redo‑log buffer.

Flush the buffer to the redo‑log file according to the configured policy.

Return success to the client while the dirty pages are still in memory.

Background thread asynchronously flushes dirty pages to disk.

This converts random writes into sequential writes, dramatically improving performance.

3.3 Circular Write Mechanism

Redo log uses two fixed‑size files (e.g., ib_logfile0 and ib_logfile1) that are written in a circular fashion. Two pointers are crucial:

write pos : current write location, moves forward as logs are appended.

checkpoint : position up to which dirty pages have been flushed; space between the two pointers is available for new logs.

Problem: What if the redo log becomes full? Increase the redo‑log size. Optimize the flush policy to speed up dirty‑page flushing. Check for long‑running transactions that block checkpoint progress.

3.4 Flush Policy (innodb_flush_log_at_trx_commit)

0

: Log is flushed by the background thread every second (best performance, up to 1 second of data loss). 1 (default): Log is flushed on every transaction commit (safest, slower). 2: Log is written to OS cache; the OS decides when to flush (balanced).

Production environments usually set this to 1 for data safety.

4. Undo Log (Rollback and MVCC)

4.1 Dual Role of Undo Log

Undo log records the previous state of data so that uncommitted or failed transactions can be rolled back, and it provides the historical versions needed for MVCC snapshot reads.

4.2 What Undo Log Stores

INSERT → records a DELETE entry.

DELETE → records an INSERT entry.

UPDATE → records the inverse UPDATE.

These are logical records, not raw SQL statements.

4.3 Version Chain (MVCC Foundation)

Each row has hidden fields trx_id (the transaction that last modified the row) and roll_pointer (pointer to the previous version). Multiple modifications create a linked version chain, enabling consistent snapshot reads.

4.4 Cleanup Mechanism

The purge thread removes undo records that are no longer needed—i.e., when no active transaction’s read view requires them. Long‑running transactions prevent cleanup, causing undo‑log growth and lock resource consumption.

Consequences of Long Transactions Undo‑log bloat. Locks held for extended periods, blocking other transactions. Potential master‑slave replication lag due to large binlog.

5. Binlog (Binary Log)

5.1 Purpose and Position

Binlog is a logical, server‑level log used for data backup, recovery, and especially for asynchronous or semi‑synchronous replication.

5.2 Formats

STATEMENT : Stores the original SQL (compact, but can cause inconsistency with nondeterministic functions).

ROW : Stores before/after row images (guarantees consistency, larger size).

MIXED : MySQL chooses STATEMENT by default and switches to ROW when needed; from MySQL 5.7 onward, ROW is the default.

5.3 Write Process

During transaction execution, each SQL’s binlog is written to a per‑thread binlog cache (memory).

On transaction commit, the cache is flushed to the binlog file’s page cache.

Flushing to disk is controlled by sync_binlog (0 = OS decides, 1 = flush on every commit, N = flush after N transactions).

Production typically sets sync_binlog=1 for safety.

Problem: Binlog Takes Too Much Space Configure binlog expiration to purge old files automatically. Manually purge unnecessary binlog files. Disable binlog only if replication is not required (not recommended).

5.4 Replication Role

Master writes binlog entries; slaves read and replay them. Replication modes:

Asynchronous : Master returns immediately after writing binlog (high performance, possible data loss).

Semi‑synchronous : Master waits for at least one slave to acknowledge receipt (higher safety).

Replication lag can occur if the slave’s SQL thread cannot keep up.

6. Coordinated Work of the Three Logs

6.1 Full Lifecycle of an UPDATE Statement

The following diagram shows the end‑to‑end process, highlighting key steps:

Undo log is written first to record the old values.

Redo log uses a two‑phase commit: write in prepare state, then commit after binlog is written.

Binlog is written only at transaction commit, capturing the whole transaction.

6.2 Two‑Phase Commit Guarantees Consistency

If MySQL crashes after the redo log is prepared but before the binlog is written, the transaction is rolled back. If it crashes after the binlog is written but before the redo log is committed, the transaction is committed. This ensures that both logs are either present together or absent together.

6.3 Core Differences Summary

Layer : Redo & undo – InnoDB engine; binlog – MySQL server.

Type : Redo – physical; undo – logical; binlog – logical (SQL or row).

Main Role : Redo – durability; undo – atomicity & MVCC; binlog – backup & replication.

Write Timing : Redo – during transaction; undo – before data change; binlog – at commit.

Write Method : Redo – circular; undo – random (cleanable); binlog – append‑only.

Can be disabled : Redo & undo – no; binlog – yes (but not advised).