Understanding the Write Order of Undo Log, Redo Log, and Binlog in MySQL Transactions

1. Key Caches Involved

During a transaction, MySQL uses several memory caches:

Buffer Pool : In‑memory buffer for InnoDB data pages; modified pages become dirty pages .

Undo Log Buffer : Stores the before‑image of rows, later flushed to the undo tablespace.

Redo Log Buffer : Holds the after‑image (physical changes) and is forced to disk on commit.

Binlog Cache : MySQL server‑level cache that records the logical SQL statements for replication.

2. Write Flow in the Transaction Execution Phase

Assume an UPDATE statement:

UPDATE account SET balance = balance - 100 WHERE id = 1;

The steps are:

Load the target data page into the Buffer Pool.

Write the original row version to the Undo Log Buffer (later flushed asynchronously).

Modify the page in the Buffer Pool, turning it into a dirty page awaiting checkpoint.

Write the new row version to the Redo Log Buffer.

Flush the Redo Log Buffer to the Redo Log File when any of the following occurs: Transaction commit Buffer reaches a configured threshold Background thread flushes periodically

Write the SQL statement to the Binlog Cache.

At this point (before commit) the state is:

Undo Log Buffer contains the old version.

Redo Log Buffer contains the new version.

Buffer Pool holds dirty pages.

Binlog Cache holds the logical SQL.

3. Transaction Commit Phase (Two‑Phase Commit)

The commit uses a two‑phase protocol:

Prepare : InnoDB forces the Redo Log Buffer (and Undo Log) to be written and fsynced to disk. Binlog is still in memory.

Commit : MySQL server flushes the Binlog Cache to the Binlog file (write + fsync), then marks the Redo Log as committed.

4. Why a Two‑Phase Commit?

Writing Redo Log and Binlog in a different order can cause data inconsistency between primary and replica after a crash. The two‑phase approach ensures that both logs are durable before the transaction is considered committed, preventing scenarios where one log is persisted while the other is not.

5. Important Configuration Parameters

Redo Log Parameters

Parameter

Effect

innodb_log_file_size

Size of a single redo log file (affects crash‑recovery speed).

innodb_log_files_in_group

Number of redo log files (default 2). Total size = size × count.

innodb_flush_log_at_trx_commit

Controls redo‑log flush policy: 0 = flush once per second, 1 = flush on every commit (safest, default), 2 = write to OS cache only.

innodb_log_buffer_size

Size of the redo‑log buffer; flushed on commit or when full.

Binlog Parameters

Parameter

Effect

log_bin

Enables binary logging (off by default); required for replication and point‑in‑time recovery.

binlog_format

Format of binlog records:

STATEMENT

,

ROW

(recommended), or

MIXED

.

sync_binlog

Controls binlog flush policy: 0 = OS decides, 1 = flush on each commit (default), >1 = flush every N transactions.

max_binlog_size

Maximum size of a single binlog file (default 1 GB); a new file is created after this limit.

expire_logs_days

Number of days to keep binlog files before automatic removal.

Undo Log Parameters

Parameter

Effect

innodb_undo_tablespaces

Number of separate undo‑log tablespaces (default 0, meaning a single tablespace).

innodb_undo_log_truncate

Whether undo logs can be truncated automatically (default 1).

innodb_max_undo_log_size

Threshold that triggers undo‑log truncation (default 1 GB).

innodb_undo_logs

Maximum number of concurrent undo segments per transaction (default 128).

By understanding the write order and configuration of Undo Log, Redo Log, and Binlog, developers can better tune MySQL for reliability, performance, and correct replication behavior.