Understanding MySQL Redo Log and Binlog: Architecture, Mechanisms, and Two‑Phase Commit

Article Overview

This article introduces the two important MySQL logs— redo log and binlog —explaining their concepts, generation layers, and how they work together to guarantee data durability and consistency.

MySQL Layered Architecture

MySQL consists of three layers: client, server, and storage‑engine. The binlog is generated by the server layer, while the redo log is specific to the InnoDB storage engine.

Redo Log (Write‑Ahead Log)

What Is Redo Log?

Redo log records changes to ensure the D (Durability) part of ACID. Changes are first written to an in‑memory buffer and later flushed to the ib_logfile files on disk.

WAL (Write‑Ahead Log) Concept

InnoDB follows the Write‑Ahead Log principle: modifications are written to memory first, then persisted to disk, avoiding performance penalties from immediate disk I/O.

Redo Log Write Strategy

A background thread flushes the redo‑log buffer to the page cache every second and then calls fsync to persist the data. The behavior is controlled by the innodb_flush_log_at_trx_commit parameter:

0 – Log stays in the buffer on each commit.

1 – Log is flushed to disk on each commit (default for most workloads).

2 – Log is written to the page cache on each commit.

Redo Log Erasure

Redo logs have a fixed size (e.g., four 8 MB files) set by innodb_log_file_size . The system writes circularly; when the write position catches up with the checkpoint, old pages are overwritten after being flushed to data files.

Crash‑Safe Guarantee

As long as the redo log is persisted, InnoDB can recover from a crash without losing committed transactions, a property known as crash‑safe .

Binlog (Archive Log)

Binlog is a logical, server‑level log that records SQL statements (e.g., INSERT, UPDATE) rather than physical row changes. It is created by executing FLUSH LOGS and can be inspected with mysqlbinlog :

# login to MySQL
mysql -uroot -p
# flush binlog
flush logs;
# show status
show master status;
# view variables
show variables like 'log_bin%';

Translating a binlog file to SQL:

sudo /usr/local/mysql/bin/mysqlbinlog --base64-output=DECODE-ROWS -v mysql-bin.000006 > mysqlbin.sql

The binlog records logical changes, while the redo log records physical changes.

Differences Between Redo Log and Binlog

Redo log is InnoDB‑specific; binlog is server‑wide and works for all engines.

Redo log is circular with fixed space; binlog is append‑only and creates new files when size limits are reached.

Binlog Write Strategy

During a transaction, statements are first written to a per‑thread binlog cache; on commit, the cache is flushed to the binlog file. The sync_binlog variable controls when fsync is performed:

0 – write only, no fsync.

1 – fsync on every commit.

N>1 – fsync after N commits.

Higher sync_binlog values improve performance under I/O bottlenecks but increase the risk of losing up to N transactions on a crash.

Two‑Phase Commit Between Redo Log and Binlog

MySQL uses a two‑phase commit to keep redo log and binlog consistent:

Execute statements.

Write redo log and mark the transaction as prepare .

Notify the server that the transaction is ready.

Write the transaction to binlog.

Commit the transaction.

Mark the redo log entry as committed .

During recovery, the system scans redo logs; if a transaction has both prepare and commit marks, it is applied. If only prepare is present, the binlog is checked for the transaction’s XID to decide whether to commit or roll back.

Promotional Note

The author invites readers to add them on WeChat for interview questions and to join a community for more content.