Understanding MySQL Binlog and Its Two‑Phase Commit Process

1. What is binlog and why it matters

Binlog (binary log) is generated by the MySQL server layer and records every database change—DDL and DML—while SELECT statements are omitted. It serves two main purposes: primary‑replica replication (the master writes changes to binlog, replicas pull and replay it) and data recovery (combined with full backups to replay incremental changes).

Enable/disable via the log_bin system variable (ON/OFF) and the --log-bin[=basename] startup option.

Binlog consists of a series of files basename.000001, basename.000002, … plus an .index file that lists them.

It is a binary, append‑only archive; you view it with SHOW BINLOG EVENTS or the mysqlbinlog utility.

Key characteristic: it never overwrites old data, unlike the redo log which cycles.

2. Redo log vs. binlog

The following dimensions highlight their differences:

Layer: Redo log belongs to the InnoDB engine layer; binlog belongs to the MySQL server layer and works for all storage engines.

Content: Redo is a physical log (page, offset, new value); binlog is a logical log (SQL statement or before/after row values).

Write mode: Redo uses circular writes that overwrite the oldest entries; binlog uses append‑only writes, creating new files when full.

Core purpose: Redo ensures crash‑safe recovery of committed transactions; binlog enables replication and archival recovery.

Lifecycle: Redo entries are short‑lived and can be overwritten after their dirty pages are flushed; binlog entries are retained long‑term as a change history.

Necessity: Redo is indispensable for InnoDB crash recovery; binlog can be disabled but is required in production for replication and point‑in‑time recovery.

In short, redo is for the engine itself, while binlog is for external consumers such as replicas and backup tools.

3. Binlog formats

The binlog_format variable determines what is written:

STATEMENT: Stores the original SQL statement. Saves space but can cause replica divergence for nondeterministic statements (e.g., INSERT INTO t(c) SELECT c FROM other_table; or use of NOW(), UUID(), RAND()).

ROW: Stores before‑and‑after values for each changed row. Guarantees replica consistency but produces larger logs (an UPDATE affecting 10,000 rows generates 10,000 row entries).

MIXED: Default in MySQL 5.7.7+ and 8.0; uses STATEMENT unless a nondeterministic statement is detected, then switches to ROW.

Since MySQL 5.7.7 the default binlog_format is ROW; production environments usually prefer ROW for safety.

4. Full execution flow of an UPDATE statement

Using UPDATE orders SET status='shipped' WHERE id>9990; as an example, the steps are:

Choose execution plan: Optimizer runs EXPLAIN to pick the cheapest access path (e.g., secondary index scan over range (9990, ∞)).

Server‑engine interaction loop: For each qualifying row:

The overall idea follows the Write‑Ahead Logging (WAL) principle: write redo first, then modify pages.

5. Why a two‑phase commit is needed

Both redo and binlog must be persisted for a transaction to be considered fully committed. If they are written independently, a crash after one write but before the other leads to inconsistency between primary and replica.

Redo first, binlog later → primary has the change, replica does not.

Binlog first, redo later → replica has the change, primary does not.

Therefore MySQL binds the two writes using a two‑phase commit.

6. Two‑phase commit process

① redo (prepare) → ② binlog flush → ③ redo (commit)

Prepare phase: InnoDB flushes redo logs and marks the transaction as PREPARE, recording the internal XA xid. If a crash occurs now, redo can recover the transaction to the prepared state.

Write binlog: The in‑memory binlog (containing the same xid) is flushed to the binlog file.

Commit phase: InnoDB changes the transaction state to COMMIT, adding a tiny commit marker to redo.

The binlog flush acts as the “waterline”—crash recovery checks which side of this line the crash occurred on.

Group commit (MySQL 5.7+) batches redo and binlog writes of concurrent transactions to reduce I/O, but it does not affect the correctness of the two‑phase commit.

7. Crash recovery decision

After a restart, InnoDB scans redo logs and applies the following rules:

If the transaction’s redo entry is COMMIT → commit.

If the redo entry is PREPARE → look for the matching xid in binlog:

This guarantees that a transaction is committed only when its binlog is safely persisted.

8. Brief overview of replication

Master writes changes to its binlog.

Replica’s I/O thread pulls the binlog and writes it to a local relay log.

Replica’s SQL thread reads the relay log and replays the changes.

This enables read‑write splitting and load distribution.

Replication lag: Large transactions or single‑threaded replay can cause the replica to fall behind; mitigations include parallel replication and splitting large transactions.

Why ROW is used in production: ROW format eliminates the nondeterministic‑statement problems that cause data drift in STATEMENT mode.

9. Practical recommendations and summary

Enable binlog in production – it is essential for replication and point‑in‑time recovery.

Set binlog_format to ROW for maximum safety.

Use the “double‑1” configuration for highest durability: innodb_flush_log_at_trx_commit=1 and sync_binlog=1. This incurs extra I/O but prevents data loss.

Avoid large transactions – they enlarge the binlog cache, hold locks longer, and increase replication lag.

In summary, redo logs (engine‑level, physical, circular) guarantee crash‑safe recovery of committed data, while binlog (server‑level, logical, append‑only) records changes for replication and archival recovery. The two‑phase commit sequence prepare → binlog → commit binds these independent logs, and the crash‑recovery rule based on binlog completeness ensures primary and replica stay consistent.