Understanding MySQL's Layered Architecture: Buffer Pool, Redo Log, Undo Log, and Binlog

The article begins by showing a simple UPDATE statement and explains that the processing of a SQL statement in MySQL passes through two main layers: the Server layer (which acts like a product manager interpreting client requests) and the InnoDB storage engine (which implements the actual data operations).

Layered Structure

MySQL separates the Server layer from the storage engine to achieve decoupling, allowing users to choose different storage engines for different workloads. This design has proven advantageous as MySQL evolved from early engines like MyISAM to the powerful InnoDB.

Buffer Pool

InnoDB’s Buffer Pool is a memory area that caches disk pages, reducing the number of disk I/O operations. The article illustrates with an example: without a Buffer Pool, three updates to a single row require six disk I/Os; with a Buffer Pool, only two I/Os are needed because the data is read once and flushed once.

The Buffer Pool greatly improves performance but introduces a risk: if power is lost before dirty pages are flushed, the changes are lost. MySQL mitigates this risk using the redo log.

Redo Log (Recovery)

Redo log records the modifications after they occur. When a transaction commits, the new values are first written to the redo log on disk (a sequential write), ensuring that even if the Buffer Pool is lost, the changes can be replayed during recovery.

MySQL provides three flushing strategies controlled by innodb_flush_log_at_trx_commit :

Value 1 – write and flush on every commit (safest, lowest performance).

Value 0 – write to the redo‑log buffer and flush periodically (fastest, highest data‑loss risk).

Value 2 – write to OS cache on each commit but flush to disk once per second (a compromise, recommended).

Undo Log (Rollback)

Undo log stores the original row values before they are changed, enabling transaction rollback. When a transaction updates a row, the previous value is written to the undo log; if the transaction aborts, MySQL reads the undo log to restore the original data.

Undo log entries are stored in the global tablespace and themselves generate redo log records.

Binlog (Archiving)

Binary log (binlog) records the committed changes at the logical level (which table and which rows were modified). It is used for replication and point‑in‑time recovery. Unlike redo log, binlog is an append‑only log and lives in the Server layer.

Binlog flushing is controlled by sync_binlog with three options (0 = rely on OS cache, 1 = synchronous write, N = flush every N transactions).

Summary

Buffer Pool caches data in memory, reducing disk I/O.

Redo log provides crash‑recovery with three flush policies; value 2 is generally recommended.

Undo log enables transaction rollback.

Binlog archives logical changes for replication and point‑in‑time recovery.

MySQL failures can be of two types: both OS and MySQL crash, or only MySQL crashes while the OS stays up.

Overall, the layered design, combined with Buffer Pool, redo/undo logs, and binlog, gives MySQL a balance of performance, durability, and flexibility.