How MySQL Two-Phase Commit Guarantees Data Consistency Across Master‑Slave Replication

Redo Log and Binlog Overview

Redo log is the physical log of InnoDB that records page‑level modifications, ensuring transaction durability and fast crash recovery. Binlog is MySQL's binary log at the server layer that records all data‑changing SQL statements (DDL/DML) in three formats—statement, row, and mixed—and is used for replication and backup.

Why Two‑Phase Commit Is Needed

Because redo log and binlog reside in different layers, the order of writing them can cause inconsistency between master and slave during a crash. If redo log is persisted but binlog is not, the master may recover the change while the slave never receives it, and vice‑versa.

Two‑Phase Commit Process

The protocol splits a transaction commit into a prepare phase and a commit phase. First, the redo log is written and marked with a prepare status. Then the binlog is written. After the binlog succeeds, the redo log’s status is changed to commit. This guarantees that either both logs are persisted or neither is.

Failure Scenarios

Scenario 1 – Redo log first, then binlog: If the server crashes after the redo log is written but before the binlog, the master will recover using the redo log, but the slave will not receive the corresponding binlog entry, leading to data divergence.

Scenario 2 – Binlog first, then redo log: If the crash occurs after the binlog is written but before the redo log, the slave will apply the change from the binlog, while the master’s recovery based on the redo log will miss the operation, again causing inconsistency.

Resolution with Two‑Phase Commit

By writing the redo log, marking it prepare, then writing the binlog, and finally flipping the redo log to commit, MySQL ensures that during recovery the master checks the redo log’s state. If it is still in prepare, the corresponding binlog entry is sought; if absent, the redo log is discarded, otherwise both logs are committed, keeping master and slave consistent.

When a crash happens after the redo log is in prepare but before the binlog succeeds, the master treats the redo log as invalid and ignores it. When both logs succeed, the redo log is promoted to commit and the transaction is applied on both sides.

Summary

MySQL uses a two‑phase commit to guarantee data consistency across master‑slave replication: write redo log → mark prepare → write binlog → on success change redo log to commit. This atomic handling prevents the split‑brain problem where only one side persists a transaction.