Ensuring Redo Log and Binlog Consistency in InnoDB with Two‑Phase Commit

Introduction

The article builds on previous discussions of MySQL redo log and binlog, focusing on how the InnoDB storage engine guarantees logical consistency between these two logs.

Redo Log vs Binlog

Redo log provides crash‑recovery capability for InnoDB, while binlog ensures data consistency across MySQL replication clusters. Redo log records are written continuously during a transaction, whereas binlog entries are written only at commit time, leading to different write timings.

Inconsistency Example

Consider an UPDATE T SET c=1 WHERE id=2 statement. If the redo log is flushed but an exception occurs before the binlog entry is written, the binlog will lack the corresponding modification. During recovery, the binlog‑based restore will miss this update, leaving column c as 0, while redo‑log recovery will apply the change, resulting in a value of 1 and data inconsistency.

Two‑Phase Commit Solution

InnoDB resolves this by using a two‑phase commit. The redo‑log write is split into a prepare step and a commit step. This ensures that both redo log and binlog reach a consistent state before the transaction is considered committed.

Failure Scenarios and Recovery

If an exception occurs while writing the binlog after the redo log is in the prepare state, MySQL detects the missing binlog entry during recovery and rolls back the transaction, preserving consistency.

Additional Edge Cases

When an exception occurs during the redo‑log commit phase, the transaction is not rolled back. MySQL can locate the corresponding binlog entry via the transaction ID, treats the transaction as complete, and proceeds with data recovery, ensuring the transaction is committed.