Understanding MySQL redo, binlog, and undo logs: Roles and Differences

Introduction

MySQL logs include error log, query log, slow query log, transaction log, and binary log. When using InnoDB, the binary log and transaction logs (redo log and undo log) are essential; this article explains these three logs.

Redo Log

Why is redo log needed?

Durability, one of the ACID properties, requires that once a transaction commits, its changes are permanently stored. Writing all modified pages to disk on each commit would cause severe performance problems due to random I/O and unnecessary writes.

InnoDB therefore writes changes first to the redo log, which is small and written sequentially, and later flushes pages to disk at appropriate times. This mechanism is known as Write‑Ahead Logging (WAL).

How is redo log flushed to disk?

Redo log consists of two parts:

In‑memory redo log buffer

On‑disk redo log file

Each DML statement writes records to the redo log buffer; later, multiple records are flushed to the redo log file. The flush timing is controlled by the innodb_flush_log_at_trx_commit parameter, which offers three options (0, 1, N).

Binlog

The binary log (binlog) is a logical log recorded by the MySQL server layer, capturing all write operations (excluding reads) in binary form. It is written regardless of the storage engine.

Binlog also has a flush policy controlled by the sync_binlog parameter with three settings (0, 1, N).

Differences between redo log and binlog

Key differences:

Binlog is implemented at the server level and works with all engines; redo log exists only for InnoDB.

Binlog records logical changes (which table/row was modified); redo log records physical changes (which data page/record was altered).

Redo log has a fixed size and can fill up, requiring flushing; binlog is append‑only with no size limit.

Undo Log

Atomicity, another ACID property, ensures that a series of operations either all succeed or all fail. Undo log implements atomicity by recording the inverse of each modification, allowing the transaction to be rolled back.

Undo log also underpins MVCC (multi‑version concurrency control) by keeping previous versions of rows.

Summary

Redo log is an InnoDB transaction log used for crash recovery; recommended innodb_flush_log_at_trx_commit setting is 2.

Binlog is a MySQL server‑level logical log used for archiving.

Undo log is an InnoDB log that enables rollback and MVCC.