Inside MySQL UPDATE: How Undo, Redo, and Binlog Work

When an UPDATE statement is issued, MySQL follows a series of steps similar to a SELECT, but also involves three types of logs: undo log, redo log, and binlog.

UPDATE t_user SET name = 'xiaolin' WHERE id = 1;

The execution flow includes:

Client connects through the connector, which authenticates the user.

The parser performs lexical analysis, builds a syntax tree, and conducts syntax checking.

The pre‑processor verifies that the referenced tables and columns exist.

The optimizer chooses an execution plan, using the primary‑key index on id in this case.

The executor locates the target row and performs the update.

Why Undo Log Is Needed

Even without an explicit BEGIN, MySQL implicitly starts a transaction for DML statements and commits it automatically. The autocommit variable controls this behavior. Undo log records the before‑image of changes, enabling transaction rollback and supporting MVCC (multi‑version concurrency control). For INSERT, DELETE, and UPDATE operations, undo log stores the primary‑key, the old row data, or the inverse operation respectively, and each record includes a trx_id and a roll_pointer that form a version chain.

Undo log also works with ReadView to implement MVCC, allowing consistent snapshot reads under REPEATABLE READ isolation.

Why Buffer Pool Is Needed

Data resides on disk; reading a row requires loading the page into memory. Buffer Pool caches pages, reducing disk I/O. InnoDB allocates a contiguous memory area divided into 16 KB pages; pages hold index and data pages, undo pages, adaptive hash indexes, lock information, etc.

Why Redo Log Is Needed

Buffer Pool is volatile; to survive power loss, InnoDB writes modifications first to redo log (WAL technique). Redo log records physical changes to data pages. Upon transaction commit, redo log is flushed to disk, ensuring crash‑safe recovery.

Redo log is written sequentially, which is much faster than random writes required for data pages.

Why Binlog Is Needed

After InnoDB updates a row, the server layer writes a binlog entry. Binlog records logical changes (SQL statements or row images) and is used for backup, point‑in‑time recovery, and asynchronous master‑slave replication.

Differences Between Binlog and Redo Log

Binlog is a server‑level logical log (STATEMENT, ROW, MIXED formats); redo log is a storage‑engine physical log.

Binlog is append‑only and never overwritten; redo log is circular.

Binlog supports replication; redo log supports crash recovery.

Two‑Phase Commit

To keep redo log and binlog consistent, MySQL uses an internal XA transaction. The prepare phase writes the XID to redo log and flushes it; the commit phase writes the XID to binlog, flushes binlog, then commits the redo log. This guarantees that either both logs contain the transaction or both discard it.

Group Commit

MySQL groups multiple transactions to reduce fsync calls. The process has three stages: flush (write binlog entries without fsync), sync (fsync the combined binlog), and commit (apply InnoDB commits). Similar grouping is applied to redo log in MySQL 5.7.

Optimizing High Disk I/O

To reduce I/O, you can:

Enable binlog group commit (tune binlog_group_commit_sync_delay and binlog_group_commit_sync_no_delay_count).

Increase sync_binlog to batch fsyncs.

Set innodb_flush_log_at_trx_commit=2 to defer redo‑log fsyncs.

These settings trade off durability for performance.