Understanding MySQL Logical Architecture and Its Logging Mechanisms (Binlog, Redo Log, Undo Log)

MySQL’s logical architecture can be divided into three layers: the connection layer that handles client connections, authentication and security checks; the server layer that parses, optimizes and executes SQL statements; and the storage engine layer that stores and retrieves data.

The server layer does not manage transactions; transaction support is provided by the storage engine, and InnoDB is the most widely used engine for transactional workloads.

MySQL logs are essential for recording the database’s runtime state. The main log types are error log, general query log, slow query log, transaction log, and binary log. Developers usually focus on the binary log (binlog) and transaction logs (redo log and undo log).

Figure 2: MySQL data update flow

1. Binary Log (binlog)

Binlog is a logical log stored in binary format on disk at the server layer, independent of the storage engine. It records all write‑type operations (INSERT, UPDATE, DELETE, DDL) and is used for replication and point‑in‑time recovery.

Typical uses of binlog are master‑slave replication—where the master writes binlog entries and slaves replay them to stay synchronized—and data recovery, where a specific start and stop position or datetime can be used to restore data.

Binlog is written by appending; the max_binlog_size parameter controls the maximum size of each binlog file. The sync_binlog setting determines when the binlog is flushed to disk: 0 (no explicit sync), 1 (flush on every commit), or N (flush every N transactions). The safest setting is 1, but larger values can improve performance at the cost of durability.

2. Redo Log

Redo log is the InnoDB transaction log that records changes to data pages before they are written to the storage engine. It consists of an in‑memory redo log buffer and on‑disk redo log files. MySQL uses Write‑Ahead Logging (WAL): changes are first written to the buffer, then flushed to disk, ensuring crash‑safe recovery.

When a transaction commits, the redo log is flushed to disk (fsync). The actual dirty pages in the buffer pool are flushed asynchronously by background threads. If a crash occurs before the dirty pages are written, MySQL can replay the redo log to restore the committed changes.

Figure 3: Redo log write process

3. Undo Log

Undo log records the logical before‑image of data modifications, enabling transaction rollback and supporting Multi‑Version Concurrency Control (MVCC). For each INSERT, UPDATE, or DELETE, an opposite operation is stored in the undo log so that the database can revert to the previous state if needed.

During recovery, MySQL first performs forward‑roll (applying redo log entries that were not yet persisted) and then rollback (using undo log to undo uncommitted changes), ensuring that committed data is not lost.

In summary, MySQL relies on the coordinated use of binlog, redo log, and undo log to provide replication, crash‑safe recovery, and transactional atomicity while balancing performance through configurable settings such as sync_binlog and buffer pool policies.

Reference: MySQL Server Logs Documentation