Understanding MySQL Redo Log and Binlog: Mechanisms, Flush Strategies, and Two‑Phase Commit

1. Redo Log: InnoDB's Crash‑Recovery Tool

Redo log is a log type unique to the InnoDB storage engine that provides crash‑recovery capability; after an unexpected MySQL crash, InnoDB uses the redo log to restore data to the most recent consistent state, ensuring durability and integrity.

Working Principle

When a table row is updated, InnoDB first modifies the data in the in‑memory Buffer Pool and records the change in the redo‑log buffer. The buffered redo records are later flushed to redo log files on disk according to a defined strategy.

Flush Policy

InnoDB offers several redo‑log flush policies controlled by the parameter innodb_flush_log_at_trx_commit :

0: redo log is flushed to disk once per second; this improves performance but may lose up to one second of data on crash.

1: default; the redo log is flushed to disk at every transaction commit, providing the highest safety.

2: at each commit the redo log is written to the OS cache without forcing a disk flush, balancing safety and performance.

Additional flush triggers include log‑buffer space shortage, checkpoint execution, background flush thread activity, and normal server shutdown.

Log File Group

Redo logs are stored on disk as a log‑file group, where each file has the same size and is written in a circular fashion. The group maintains a write position and a checkpoint; the space between them is used for new redo records.

2. Binlog: MySQL Cluster Data Consistency Assurance

Binlog is a server‑level log independent of the storage engine; it records logical data‑changing operations for every table update and is essential for replication, backup, and recovery.

Working Principle

Binlog records are written sequentially and are only flushed to disk when a transaction commits, unlike redo logs which may be written continuously during transaction execution.

Log Formats

The format is set by the binlog_format parameter and can be:

Statement : stores the original SQL statement; compact but may produce inconsistent results across environments.

Row : stores before‑and‑after row images; ensures consistency but consumes more space.

Mixed : MySQL chooses Statement or Row based on the statement's potential for inconsistency.

In practice, the Row format is often preferred for its reliability.

3. Two‑Phase Commit: Ensuring Log Logical Consistency

Redo logs can be written continuously, while binlog is written only at commit, which may cause logical inconsistency. InnoDB solves this with a two‑phase commit that splits redo log writing into a prepare and a commit step.

Workflow

Example of an UPDATE on a row (id=2, c:0→1):

Redo log prepare phase: the transaction’s redo log is written in the prepare state, recording the modification but not yet committed.

Binlog write: upon transaction commit, MySQL writes the binlog entry to disk.

Redo log commit phase: after the binlog is safely written, InnoDB advances the redo log to the commit state, completing the transaction.

If a crash occurs, InnoDB examines redo logs; transactions left in the prepare state without a corresponding binlog entry are rolled back, preserving consistency.

4. Collaborative Operation: From Write to Recovery

The coordination of redo log and binlog spans the entire lifecycle: data modification, log flushing, transaction commit, and crash recovery.

Transaction Execution and Commit

During an UPDATE, InnoDB modifies data in the Buffer Pool and records the change in the redo‑log buffer (prepare state). At commit, the binlog is written, then the redo log is promoted to commit state.

Crash Recovery

After a crash, InnoDB replays redo logs. For transactions in the prepare state, it checks for a matching binlog entry: if none exists, the transaction is rolled back; if it exists, the transaction is applied, ensuring data consistency.

5. Summary: Perfect Coordination of Redo Log and Binlog

Redo log and binlog each play distinct but complementary roles in MySQL. Redo log focuses on InnoDB‑level crash recovery with efficient write and flush mechanisms, while binlog operates at the server level to capture logical changes for replication and backup. The two‑phase commit bridges them, guaranteeing logical consistency and enabling MySQL to recover gracefully from failures.