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

MySQL maintains six kinds 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 operations and are essential for understanding MySQL transaction mechanisms.

Redo Log ( redo log )

Purpose:

Ensures transaction durability.

Prevents loss of dirty pages during a crash by replaying the redo log on MySQL restart.

Content:

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

When it is generated:

Starts as soon as a transaction begins; writes are flushed to the redo log file continuously, not only at commit.

When it is released:

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

Physical files:

Default files: ib_logfile1 and ib_logfile2 under the data directory.

Configuration parameters: innodb_log_group_home_dir , innodb_log_files_in_group , innodb_log_file_size , innodb_mirrored_log_groups .

The InnoDB log buffer ( innodb_log_buffer , default 8 MB) holds redo entries before they are flushed. Flushing occurs via three mechanisms: the Master Thread (once per second), at each transaction commit, and when the buffer is less than half full.

Undo Log ( undo log )

Purpose:

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

Content:

Logical‑format log; unlike redo log, it records logical changes rather than physical page modifications.

When it is generated:

Created before a transaction starts; an associated redo entry is also written to guarantee undo reliability.

When it is released:

After transaction commit, undo records are placed on a purge list; the purge thread removes them when no active transaction needs the older version.

Physical storage:

In MySQL 5.6 and earlier, undo resides in the shared tablespace (ibdata).

From MySQL 5.7 onward, undo can be placed in independent undo tablespaces, configured with innodb_undo_directory , innodb_undo_logs , and innodb_undo_tablespaces .

Binary Log ( binlog )

Purpose:

Used for replication; slaves replay the master’s binlog to stay synchronized.

Enables point‑in‑time recovery of the database.

Content:

Logical‑format log that records the SQL statements of committed transactions (including the inverse information for DML operations).

Allows features similar to Oracle’s flashback by reconstructing data from the recorded statements.

When it is generated:

At transaction commit, all statements of the transaction are written to the binlog in one batch.

Unlike redo log, binlog is not flushed gradually; this can make large commits slower when binlog is enabled.

When it is released:

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

Physical files:

Path defined by log_bin_basename ; binlog files roll over when they reach a configured size, with an index file tracking them.

Both redo log and binlog participate in MySQL’s two‑phase commit to guarantee that they are written to disk before a transaction is considered complete, ensuring consistency for replication and point‑in‑time recovery.

Summary

Each of the three logs—redo, undo, and binary—has distinct roles, formats, lifecycles, and configuration options, and together they provide durability, rollback capability, and replication support for MySQL transactions.

Reference: "MySQL Technical Internals – InnoDB Storage Engine" (page 37).