MySQL Logs Explained: Why Undo, Redo, and Binlog Aren’t As Simple As They Seem

MySQL relies on three main log files—undo log, redo log, and binlog—to implement most of its features. Undo log, generated by the InnoDB storage engine, records the state of data before a transaction modifies it, enabling atomic rollbacks and supporting MVCC through Read View + undo log. Redo log, also an InnoDB log, records the physical changes made to data pages after a transaction commits, providing durability and crash‑safe recovery via Write‑Ahead Logging (WAL). Binlog, created at the server layer, archives all data‑changing statements (not SELECTs) for backup, point‑in‑time recovery, and master‑slave replication.

Why Undo Log Is Needed

Even a single UPDATE runs inside an implicit transaction (controlled by the autocommit variable). If MySQL crashes before the transaction commits, the undo log supplies the pre‑change values needed to roll back to the original state, guaranteeing the atomicity property of ACID.

Undo log entries differ by operation type:

Insert: store the primary key so the row can be deleted on rollback.

Delete: store the full row contents so the row can be re‑inserted.

Update: store the old column values so they can be restored.

During rollback, InnoDB reads the undo log and performs the opposite operation (e.g., inserting a row that was deleted).

Each undo record contains a trx_id (identifying the transaction) and a roll_pointer that links records into a version chain, which is used by MVCC to provide consistent snapshot reads for "read‑committed" and "repeatable‑read" isolation levels.

Why Buffer Pool Is Needed

MySQL stores data on disk, but reads and writes first go through the InnoDB buffer pool, which caches pages (default 16 KB each) in memory. When a page is read, the whole page is loaded; when a page is modified, it becomes a dirty page. Dirty pages are flushed to disk later by background threads, reducing random I/O.

The buffer pool also caches index pages, undo pages, insert buffers, adaptive hash indexes, and lock information.

Why Redo Log Is Needed

Because memory is volatile, MySQL cannot rely solely on dirty pages in the buffer pool. Before a transaction commits, InnoDB writes the change to the redo log (a physical log) and marks the page dirty. The redo log is persisted to disk, enabling recovery after power loss.

Redo log entries record the exact page, offset, and modification performed. During crash recovery, MySQL replays redo log entries to bring the database to the latest committed state.

Writing to redo log is an append‑only sequential write , which is far more efficient than random writes required for updating data pages directly.

Redo Log Buffer and Flush Timing

Redo log records first go into the redo log buffer (default 16 MB, configurable with innodb_log_buffer_size). The buffer is flushed to disk in several situations:

When MySQL shuts down normally.

When the buffer is more than half full.

Every second by an InnoDB background thread.

At each transaction commit, controlled by innodb_flush_log_at_trx_commit (values 0, 1, 2).

Value 1 forces a synchronous fsync() on every commit (maximum safety). Value 0 writes only to the OS page cache and syncs once per second (fastest but can lose up to one second of data). Value 2 writes to the OS cache on each commit and performs fsync() once per second (a compromise).

Why Binlog Is Needed

After the storage engine finishes an update, the server layer writes a binlog entry. Binlog records all data‑definition and data‑modification statements (but not SELECTs) and is used for backup, point‑in‑time recovery, and replication.

Binlog differs from redo log in four key ways:

Scope: binlog is a server‑level logical log usable by any storage engine; redo log is InnoDB‑specific.

Format: binlog can be STATEMENT, ROW, or MIXED; redo log is a physical page‑level log.

Write pattern: binlog is append‑only and never overwritten; redo log is circular and overwrites old space after checkpoints.

Purpose: binlog supports replication and logical backup; redo log supports crash‑safe recovery of dirty pages.

Because redo log is circular and only retains unflushed changes, it cannot be used to recover a completely deleted database; binlog must be used for that purpose.

Master‑Slave Replication

Replication relies on binlog. The primary writes binlog entries, commits the transaction, and returns success to the client. An I/O thread on each replica pulls the binlog from the primary, writes it to a relay log, and a replay thread applies the events to the replica's storage engine. Replication is asynchronous by default, but MySQL also offers synchronous and semi‑synchronous modes.

Typical deployment uses one primary with two or three replicas to balance read scalability and resource consumption.

In summary, undo log ensures atomic rollbacks and MVCC, redo log provides durability and crash‑safe recovery through WAL, and binlog enables logical backup and replication. Understanding their interactions, buffer‑pool caching, and flush parameters helps DBAs balance data safety against write performance.