Understanding MySQL Redo, Undo, and Binary Logs: A Practical Guide

MySQL has six types of log files: redo log, undo log, binary log, error log, slow query log, general log, and relay log. Among them, redo log, undo log, and binary log are closely related to transaction processing and are essential for understanding MySQL transactions.

1. Redo Log (redo log)

1.1 Purpose

Ensures transaction durability by writing dirty pages to disk after a failure, allowing MySQL to redo operations during restart.

1.2 Content

Physical‑format log that records modifications to data pages; written sequentially to redo log files.

1.3 When It Is Created

Generated as soon as a transaction starts; writes to the redo log file occur continuously during transaction execution, not only at commit.

1.4 When It Is Released

After the corresponding dirty pages are flushed to disk, the redo log space can be reused.

1.5 Physical Files

By default stored as ib_logfile1 and ib_logfile2 under the data directory. Parameters such as innodb_log_group_home_dir, innodb_log_files_in_group, innodb_log_file_size, and innodb_mirrored_log_groups control location, count, size, and mirroring.

1.6 Other Details

The redo log is first written to the Innodb_log_buffer (default 8 MB, often set to 16 MB). The buffer is flushed to disk by three mechanisms: the Master Thread every second, at each transaction commit, and when the buffer is less than half full.

2. Undo Log (undo log)

2.1 Purpose

Stores the previous version of data before a transaction modifies it, enabling rollback and providing MVCC (non‑locking reads).

2.2 Content

Logical‑format log; during undo, data is restored logically rather than by physical page changes, unlike redo log.

2.3 When It Is Created

Generated before a transaction starts; an undo log also creates a redo log to guarantee its reliability.

2.4 When It Is Released

After transaction commit, the undo log is placed in a pending‑cleanup list. The purge thread removes it only when no other active transaction needs the older version.

2.5 Physical Files

Before MySQL 5.6, undo data resides in the shared tablespace (ibdata). From MySQL 5.6 onward, independent undo tablespaces can be configured via innodb_undo_directory, innodb_undo_logs, and innodb_undo_tablespaces. Without prior configuration, independent undo tablespaces cannot be created.

2.6 Other Details

Undo logs share the shared tablespace by default, which can grow large and does not shrink automatically. Therefore, configuring independent undo tablespaces (available from MySQL 5.7) is often necessary.

3. Binary Log (binlog)

3.1 Purpose

Used for replication (the slave replays the master’s binlog) and for point‑in‑time recovery.

3.2 Content

Logical‑format log that records the SQL statements of committed transactions, including both the original statements and the reverse information needed for undo operations.

3.3 When It Is Created

Written at transaction commit; all statements of the transaction are stored together in a defined format.

3.4 When It Is Released

Retention is controlled by the expire_logs_days parameter; files older than the configured days are automatically deleted.

3.5 Physical Files

The base name is set by log_bin_basename. When a binlog reaches the configured maximum size, a new file is created, and an index file tracks all binlog files.

3.6 Other Details

Redo log ensures transaction durability at the storage engine level, while binlog provides logical replication and recovery at the database level.

Redo log records physical page changes; binlog records logical SQL statements.

Their generation times, release mechanisms, and cleanup processes differ.

Recovery using redo log is generally faster because it works with physical changes.

MySQL uses a two‑phase commit to guarantee consistency between redo log and binlog: first the redo log is flushed, then the binlog is written. Only after both are persisted does the transaction become truly committed.

4. Summary

Each of the three logs—redo log, undo log, and binary log—has distinct characteristics and roles in MySQL transaction processing. Understanding their purposes, contents, lifecycles, and storage files helps clarify how MySQL ensures durability, consistency, and recoverability.