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

When using MySQL with the InnoDB storage engine, two fundamental logging mechanisms are unavoidable: the redo log (a storage‑engine‑level log) and the binlog (a server‑level binary log).

Redo log exists only for InnoDB; it records physical changes made by the engine. Binlog is recorded by the MySQL server itself, regardless of the storage engine, and is the basis for MySQL master‑slave replication.

The redo log is crucial for handling large update workloads efficiently. Instead of writing every change directly to disk, InnoDB first writes the modification to the redo log and updates the in‑memory page, employing Write‑Ahead Logging (WAL) so that the operation is considered successful before the data is flushed to disk.

Because the redo log has a fixed size (commonly configured as a set of files, e.g., four 1 GB files), it uses a write pos pointer to indicate where the next record will be written and a checkpoint to mark the oldest data that has already been persisted to disk. When the write position catches up to the checkpoint, the system must advance the checkpoint before more updates can be logged.

This mechanism guarantees crash recovery: after an unexpected restart, InnoDB can replay the redo log to restore the database to a consistent state, often allowing recovery to any point within the log’s retention period.

The binlog, by contrast, is a logical log that records statements or row‑based events (e.g., "increment column c for id = 2 ") and can grow indefinitely because it is appended rather than overwritten.

Redo log is InnoDB‑specific; binlog is server‑wide.

Redo log is a physical log (records page‑level changes); binlog is a logical log (records SQL statements or row changes).

Redo log has a fixed size and can fill up; binlog is append‑only and has no inherent size limit.

During an update such as UPDATE table SET c = c + 1 WHERE id = 2 , the engine first finds the row, updates the in‑memory page, writes the change to the redo log in a prepare state, then the executor generates the corresponding binlog entry and writes it to disk. After the binlog is safely stored, the redo log entry is moved to commit state, completing the transaction.

This sequence implements a two‑phase commit : the redo log remains in a prepared state until the binlog is successfully persisted, ensuring that both logs stay logically consistent. Without this ordering, a failure after writing the redo log but before writing the binlog could lead to data inconsistency.

Thank you for reading, and see the reference: GeekTime – MySQL in Practice, Lecture 45.