MySQL Redo, Bin, and Undo Logs Explained: Roles, Differences & Tips

Introduction

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

Redo Log

Why does MySQL need a redo log?

One of the ACID properties, durability, guarantees that once a transaction commits, its changes are permanently stored. MySQL ensures durability by writing modified pages to disk at commit time, but doing so incurs severe performance penalties due to random I/O and unnecessary full‑page writes.

Therefore InnoDB writes changes first to the redo log, a sequential write that is much faster, and updates memory. The actual data pages are flushed to disk later. This write‑ahead logging (WAL) mechanism ensures durability without the performance cost of immediate page writes.

How is the redo log flushed to disk?

The redo log consists of two parts:

Redo log buffer in memory

Redo log file on disk

Each DML statement writes to the redo log buffer; at certain points the buffer is flushed to the redo log file. MySQL supports three flushing strategies controlled by the innodb_flush_log_at_trx_commit parameter:

Binlog

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

Like the redo log, the binlog has its own flushing policy controlled by the sync_binlog parameter:

0 – Write to OS cache; the OS decides when to flush.

1 – Synchronous write directly to disk.

N – Flush to disk after every N transaction commits.

Differences between redo log and binlog

The main distinctions are:

Binlog is implemented at the server layer and works with all engines; redo log is specific to InnoDB.

Binlog is a logical log recording which table rows changed; redo log is a physical log recording which data pages and records were modified.

Redo log has a fixed size and can fill up, requiring a flush; binlog is append‑only and has no fixed 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 inverse operations (e.g., an UPDATE’s opposite UPDATE, an INSERT’s corresponding DELETE), enabling rollback on errors.

Undo log also underpins MySQL’s MVCC (multiversion concurrency control) mechanism.

Summary

Redo log is an InnoDB‑specific log used for crash recovery; the recommended innodb_flush_log_at_trx_commit setting is 2.

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

Undo log is an InnoDB log that enables transaction rollback.