Understanding MySQL Log Files: Redo Log, Undo Log, and Binary Log

MySQL maintains six types of log files: redo log (redo log), undo log (undo log), binary log (binlog), error log, slow query log, general log, and relay log. The following sections concentrate on the three logs most relevant to transaction processing.

Redo Log (redo log)

Purpose: Guarantees transaction durability by allowing recovery of dirty pages after a crash.

Ensures that unflushed dirty pages are persisted when MySQL restarts.

Content: Physical‑format log that records modifications to data pages; entries are written sequentially to the redo log files.

Generation: Begins as soon as a transaction starts; logs are flushed to disk continuously, not only at commit.

Release: When the corresponding dirty pages are flushed to disk, the occupied space can be reused.

Physical files: By default ib_logfile1 and ib_logfile2 located in the data directory. The directory is set by innodb_log_group_home_dir (default ./ ), and the number of files is controlled by innodb_log_files_in_group (default 2).

Size and number configuration:

innodb_log_file_size – size of each redo log file.

innodb_mirrored_log_groups – number of mirrored log groups (default 1).

Flushing mechanisms: The redo log buffer ( Innodb_log_buffer , default 8 MB) is flushed to disk by three methods:

Master thread flushes once per second.

Each transaction commit forces a flush.

When the buffer’s free space falls below half, it is flushed.

Undo Log (undo log)

Purpose: Stores the pre‑transaction version of rows, enabling rollback and providing MVCC for non‑locking reads.

Content: Logical‑format log; during undo the data is restored logically rather than physically, unlike the redo log.

Generation: Created before a transaction starts; an associated redo log entry ensures undo log reliability.

Release: After transaction commit, undo records are placed on a purge list. The purge thread removes them only when no other active transaction needs the older version.

Physical storage:

Before MySQL 5.6, undo data resides in the shared tablespace ( ibdata ).

From MySQL 5.6 onward, undo tablespaces can be configured as independent files using parameters such as innodb_undo_directory , innodb_undo_logs , and innodb_undo_tablespaces .

When using a shared tablespace, the space can grow large and does not shrink automatically, which is why independent undo tablespaces are recommended for newer versions.

Binary Log (binlog)

Purpose: Supports replication (master‑to‑slave replay) and point‑in‑time recovery.

Content: Logical‑format log that records the SQL statements of committed transactions, including reverse information for DELETE, UPDATE, and INSERT operations, enabling features similar to Oracle’s flashback.

Generation: Written once at transaction commit; all statements of the transaction are stored together.

Release: Retained according to the expire_logs_days setting; older files are automatically removed.

Physical files: Path defined by log_bin_basename ; binlog files roll over when they reach a configured size and are indexed by a common index file.

Other notes:

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

Redo log is a physical log; binlog is a logical log of SQL statements.

During a transaction commit, MySQL follows a two‑phase commit: redo log is flushed first, then binlog; both must be persisted before the transaction is considered complete.

Summary

Each of the three logs—redo log, undo log, and binary log—has distinct characteristics and roles in MySQL’s transaction processing and recovery mechanisms. Understanding their purposes, contents, lifecycle, and configuration helps grasp how MySQL ensures data durability, supports rollback, and enables replication.

Even if a transaction has not committed, InnoDB flushes the redo log buffer to the redo log file every second, which explains why commit latency remains short even for large transactions.