Understanding MySQL Redo Log and Binlog: Architecture, Strategies, and Recovery

Hello, I am Lianbian, and this is my 22nd original article.

Today we discuss two important MySQL logs— redo log and binlog —explaining their concepts, architecture, and practical usage.

Article Overview

MySQL Layered Architecture

MySQL consists of three layers: client, server, and storage engine. The server layer generates binlog, while the InnoDB engine generates the redo log.

Redo Log (redo log)

What is redo log?

Redo log ensures the durability (the “D” in ACID) of transactions. It records changes to the ib_logfile files, which are used for crash recovery.

WAL (Write‑Ahead Log)

InnoDB follows the Write‑Ahead Log principle: changes are first written to memory (buffer) and later flushed to disk, avoiding performance penalties while guaranteeing persistence.

Redo Log Write Strategy

A background thread flushes the redo log buffer to the page cache every second, then calls fsync to persist the data to ib_logfile0 and ib_logfile1. The parameter innodb_flush_log_at_trx_commit controls this behavior:

0 – keep logs only in the buffer.

1 – flush to disk on every transaction commit (default for strong durability).

2 – write to the page cache without immediate fsync.

For most workloads, setting the parameter to 1 is recommended.

Redo Log Erasure

Redo logs have a fixed size (e.g., four 8 MB files). When the write position catches up with the checkpoint, the log wraps around and overwrites old data. Erasure occurs when the system is idle, the log is full, or MySQL shuts down cleanly.

Crash‑Safe Guarantee

Once the redo log is persisted, InnoDB can recover committed transactions after a crash, providing crash‑safe durability.

Archive Log (binlog)

Binlog is a logical log generated at the server layer, recording SQL statements rather than physical row changes.

Viewing Binlog

# Login to MySQL command line
mysql -uroot -p
# Flush binlog
flush logs;
# Verify flush
show master status;
# Show binlog variables
show variables like 'log_bin%';

Sample DDL/DML and Binlog Translation

## Create table
CREATE TABLE `User` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(10) CHARACTER SET gb2312 COLLATE gb2312_chinese_ci NOT NULL,
  `age` int(11) UNSIGNED NOT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1 CHARACTER SET=utf8mb4 COLLATE=utf8mb4_bin ROW_FORMAT=Compact;

## Insert
INSERT `User` VALUES ("1", "张三", 18);
INSERT `User` VALUES ("2", "李四", 20);

## Delete
DELETE FROM `User` WHERE id = 1;

Translating the binlog to SQL shows that logical logs record statements (INSERT, UPDATE, DELETE) while physical changes are handled by the redo log.

Differences Between redo log and binlog

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

Redo log is circular with fixed size; binlog is append‑only and creates new files when a size limit is reached.

Binlog Write Strategy

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

0 – write only, no fsync.

1 – fsync on every commit.

N>1 – fsync after N transactions.

Setting a large sync_binlog can improve performance under I/O bottlenecks, but risks losing up to N transactions if the server crashes.

Two‑Phase Commit (2PC) for redo log and binlog

2PC ensures logical consistency between redo log and binlog. The steps are:

Execute statements.

Record redo log with state “prepare”.

Notify the server that the transaction is ready to commit.

Write the changes to binlog.

Commit the transaction.

Mark the redo log entry as “committed”.

During recovery, the system scans redo logs: if both “prepare” and “commit” markers exist, the transaction is applied; if only “prepare” exists, the presence of the XID in binlog determines whether to commit or roll back.

Summary

This article covered MySQL’s redo log and binlog, their architecture, write and flush strategies, crash‑safe guarantees, and how two‑phase commit keeps them consistent, providing practical commands and configuration guidance for reliable database management.