Skip to content

MySQL binlog vs InnoDB redo#

2024-04-12

Every so often someone asks me "why does MySQL have two logs" and I fumble the answer. Writing it down.

The two logs#

  • InnoDB redo log (ib_logfile* or #innodb_redo/ since 8.0.30) is physical, per-storage-engine, and used for crash recovery. It records page-level changes: "page X, offset Y, bytes Z were modified". It's circular — old records get overwritten once the corresponding dirty page has been flushed.

  • binlog is logical (or row-based, or mixed), per-server, and used for replication and PITR. It records statements or row changes: "UPDATE row R in table T from V1 to V2". It's append-only across segment files.

Two-phase commit#

MySQL uses XA-style two-phase commit between the two logs to ensure they stay consistent after a crash:

  1. PREPARE: write the binlog event, fsync it.
  2. COMMIT in InnoDB: write the redo commit record, fsync it.

If we crash between 1 and 2, recovery reads the binlog, finds committed-in- binlog-but-not-in-redo transactions, and rolls them forward. If we crash before 1, InnoDB rolls back.

This is why single-transaction commits are expensive in MySQL: two fsyncs. Group commit (binlog_group_commit_sync_delay) helps by batching many transactions into one round of fsyncs.

Why keep both#

  • Redo is required for InnoDB crash recovery. You can't remove it.
  • Binlog is required for replication. You can turn it off if you don't replicate, but then you lose PITR.
  • They contain different information: redo can't reconstruct row-level changes for replication (it's page bytes), binlog can't do page-level recovery (it doesn't know about physical pages).

Practical footguns#

  • sync_binlog=1 + innodb_flush_log_at_trx_commit=1 is the safe setting and it's slow. Many production installs run sync_binlog=1000 in practice and accept the risk.
  • binlog_format=ROW is required for GTID and for parallel replication. STATEMENT is a compat mode.
  • With encryption at rest, redo and binlog are encrypted with different keys. Backup / restore procedures need both.