MySQL Binlog 详解

[ MySQL ]

MySQL 的二进制日志（Binary Log，简称 Binlog）是 MySQL Server 记录数据库变更操作的重要日志文件，主要用于数据恢复、主从复制（Replication）和审计。

Binlog 记录什么？

Binlog 记录会修改数据库数据或结构的操作，例如：

  • INSERT

  • UPDATE

  • DELETE

  • CREATE TABLE

  • ALTER TABLE

  • DROP TABLE

通常不会记录纯查询语句（如 SELECT）。

Binlog 的作用

1. 主从复制（Replication）

MySQL 主从架构中：

  • 主库（Master）将数据变更写入 Binlog。

  • 从库（Replica）读取 Binlog 并重放其中的操作。

  • 从而保持主从数据一致。

流程：

Client
   ↓
Master
   ↓ 写入 Binlog
Binlog
   ↓
Replica IO Thread
   ↓
Relay Log
   ↓
Replica SQL Thread
   ↓
执行变更

2. 数据恢复（Point-in-Time Recovery）

假设：

  • 凌晨 1:00 做了全量备份。

  • 上午 10:00 误删数据。

恢复步骤：

  1. 恢复 1:00 的备份。

  2. 使用 Binlog 重放 1:00～9:59 的所有操作。

  3. 恢复到误操作前的状态。

这种方式称为：

PITR（Point In Time Recovery，时间点恢复）

3. 数据同步与 CDC

许多数据同步工具直接解析 Binlog：

应用场景：

  • MySQL → Elasticsearch

  • MySQL → Kafka

  • MySQL → Data Warehouse

  • 实时数据同步

Binlog 的三种格式

1. Statement-Based Logging（SBL）

记录执行的 SQL 语句。

例如：

UPDATE user
SET balance = balance - 100
WHERE id = 1;

Binlog 中保存的是这条 SQL。

优点：

  • 日志小

  • 写入快

缺点：

  • 某些非确定性函数可能导致主从不一致

例如：

UPDATE user
SET create_time = NOW();

或者：

INSERT INTO t VALUES(UUID());

2. Row-Based Logging（RBL）

记录数据行变化。

例如：

UPDATE user
SET balance = 900
WHERE id = 1;

Binlog 记录：

Before:
id=1 balance=1000

After:
id=1 balance=900

优点：

  • 数据最准确

  • 主从一致性最好

缺点：

  • 日志体积较大

3. Mixed-Based Logging（MBL）

混合模式：

binlog_format=MIXED

MySQL 自动决定：

  • 普通 SQL → Statement

  • 风险 SQL → Row

查看当前 Binlog 格式

SHOW VARIABLES LIKE 'binlog_format';

结果：

+---------------+-----+
| binlog_format | ROW |
+---------------+-----+

MySQL 8.0 默认通常为：

ROW

Binlog 文件示例

查看：

SHOW BINARY LOGS;

结果：

mysql-bin.000001
mysql-bin.000002
mysql-bin.000003

当前使用的文件：

SHOW MASTER STATUS;

例如：

File: mysql-bin.000003
Position: 1547

这里的：

  • File = Binlog 文件名

  • Position = 写入位置

主从复制就是依赖这两个值定位同步位置。

查看 Binlog 内容

使用 MySQL 自带工具：

mysqlbinlog mysql-bin.000003

查看某个时间段：

mysqlbinlog \
--start-datetime="2025-01-01 10:00:00" \
--stop-datetime="2025-01-01 11:00:00" \
mysql-bin.000003

查看指定位置：

mysqlbinlog \
--start-position=1000 \
--stop-position=2000 \
mysql-bin.000003

开启 Binlog

查看是否开启：

SHOW VARIABLES LIKE 'log_bin';

结果：

ON

配置文件（my.cnf）：

[mysqld]

server-id=1

log_bin=mysql-bin

binlog_format=ROW

重启 MySQL 后生效。

Binlog 与其他日志的区别

日志类型 作用
Binlog 记录数据变更，用于复制和恢复
Redo Log 保证事务持久性（崩溃恢复）
Undo Log 支持事务回滚和 MVCC
Error Log 记录 MySQL 错误信息
Slow Query Log 记录慢 SQL
General Log 记录所有客户端请求

Binlog 与 Redo Log 的核心区别

很多会问：

Binlog Redo Log
MySQL Server 层 InnoDB 存储引擎层
逻辑日志 物理日志
记录数据变更 记录页修改
用于主从复制 用于崩溃恢复
可追加写 循环写

简化理解：

  • Binlog：记录“执行了什么操作”。

  • Redo Log：记录“数据页如何被修改”。

例如：

UPDATE account
SET money = 100
WHERE id = 1;

Binlog 记录：

UPDATE account ...

Redo Log 记录：

Page 100:
offset 320
1000 → 100

因此：

  • Binlog 负责复制和时间点恢复。

  • Redo Log 负责数据库宕机后的事务恢复。

这是 MySQL 日志体系中最重要的两个日志。