Understanding MySQL Redo Log vs Binlog: How They Ensure Data Safety

1. Concept Review

1.1 Two‑Phase Commit

Distributed transactions typically use a two‑phase commit to achieve eventual consistency. In a purchase scenario, the order service saves the order, the account service deducts the amount, and the inventory service reduces stock. These three services first prepare and then commit to ensure transaction consistency.

1.2 Transaction Commit

In MySQL's logical architecture, the executor runs the SQL statements and interacts with the storage engine.

The executor acts as the coordinator of the two‑phase commit. The execution process is illustrated below:

Example SQL: update t set a=10 where id=5; The execution steps are:

The executor reads the row with id=5 from the storage engine, changes column a to 10, and writes the modified row back. prepare phase: the storage engine writes the new data to the Redo Log buffer and records the update operation, but does not commit yet; the Redo Log is in the prepare state.

The executor generates a Binlog entry for the update and writes it to disk, subject to the sync_binlog setting. commit phase: the executor tells the storage engine to commit the transaction; the Redo Log entry is marked commit, completing the transaction.

2. Redo Log

2.1 Introduction

Redo Log is a physical log specific to the InnoDB storage engine. It records modifications to data pages and is primarily used for crash recovery.

The flushing behavior of Redo Log is controlled by the innodb_flush_log_at_trx_commit parameter: innodb_flush_log_at_trx_commit 0: On transaction commit, the Redo Log stays in the buffer and is flushed to the OS page cache once per second, then fsync is called. This reduces I/O but may lose up to 1 second of data if MySQL or the OS crashes.

1: Default. The Redo Log is written to the page cache and fsync is called on every commit, minimizing data loss risk but increasing I/O.

2: The Redo Log is written to the page cache, and the OS flushes it to disk asynchronously. Performance is good; data is lost only if the OS crashes before the cache is flushed.

2.2 Writing Logs

Redo Log is written in a circular fashion with a fixed total size. When the space is full, older logs are overwritten. The write position is controlled by write pos, and the checkpoint determines the furthest safe write point; when they meet, older logs are cleared and the checkpoint moves forward.

2.3 Redo Log Use Cases

Crash Recovery

Redo Log implements Write‑Ahead Logging (WAL). When MySQL restarts after a crash, InnoDB compares the LSN of data pages with the LSN in the Redo Log; entries with a larger LSN are replayed. If a Redo Log entry is in COMMIT state, it is redone; if it is in PREPARE, the corresponding Binlog is checked. If the Binlog exists and is complete, the transaction is considered committed and is redone; otherwise the transaction is rolled back.

Performance Optimization

By converting random writes of data files into sequential writes of the Redo Log, transaction execution efficiency and overall database throughput are greatly improved.

3. Binlog

3.1 Introduction

Binlog is a logical log at the MySQL server layer, usable by all storage engines. It records logical data changes (e.g., INSERT statements) and is used for master‑slave replication and point‑in‑time recovery.

Unlike Redo Log’s circular write, Binlog is appended; when one file is full, a new file is created.

3.2 Flush Timing

The sync_binlog parameter defines how often Binlog contents are synchronized from memory to disk.

0: MySQL never forces a Binlog sync; it relies on the OS flush mechanism. This yields the best performance but risks data loss.

1: Binlog is synced to disk on every transaction commit, reducing data‑loss risk but increasing I/O.

N (N>1): Binlog is synced after every N commits, balancing performance and safety.

3.3 Binlog Use Cases

Master‑Slave Replication

The primary use of Binlog is replication. The master writes its Binlog and streams it to slave nodes, which replay the SQL statements to keep data synchronized.

Point‑in‑Time Recovery

Because Binlog records every data change, it can be replayed to restore the database to a specific moment. Typically, a recent full backup is restored first, then all Binlog files from that backup up to the target time are applied.

Conclusion

Redo Log, stored in InnoDB, guarantees that committed transactions survive crashes and forms the foundation of MySQL's transactional support. Binlog, stored at the server layer, enables master‑slave synchronization and point‑in‑time recovery. Together they complement each other to ensure MySQL data safety and high availability.

Key differences:

Ownership: Redo Log belongs to the InnoDB storage engine; Binlog belongs to the MySQL server.

Log type: Redo Log is a physical log recording page modifications; Binlog is a logical log recording SQL or row changes.

Purpose: Redo Log is used for crash recovery; Binlog is used for replication and point‑in‑time recovery.

Write method: Redo Log uses circular writes; Binlog uses append‑only writes.