05.3 - Binlog 详解
定位：Binlog 是 MySQL Server 层的日志，支撑了主从复制和数据恢复——理解它的三种格式及差异是部署高可用架构的基础 面试高频度：⭐⭐⭐⭐⭐ 考查方式：三种格式的区别及选型、Binlog 与 Redo Log 的区别、PITR（时间点恢复）、sync_binlog 配置
一、这是什么？为什么需要它？
是什么
Binlog（二进制日志）是 MySQL Server 层的日志，记录所有数据变更操作。与 InnoDB 引擎层的 Redo Log 不同，Binlog 对所有的存储引擎都适用。
为什么需要 Binlog？
Binlog 解决的是 Redo Log 无法覆盖的问题：
| 能力 | Redo Log | Binlog |
|---|---|---|
| 主从复制 | ❌ 引擎层，无法跨实例 | ✅ 核心能力 |
| 时间点恢复 (PITR) | ❌ 循环写，历史数据被覆盖 | ✅ 追加写，可回溯到任意时间点 |
| 审计 | ❌ 内容格式引擎私有 | ✅ 可解析为 SQL 语句 |
| 跨引擎 | ❌ 只有 InnoDB | ✅ 所有引擎 |
核心 insight：Redo Log 是"不丢数据"的保证，Binlog 是"数据可回溯"和"数据可复制"的保证。两者分工不同，缺一不可。
二、原理拆解
2.1 三种格式对比
STATEMENT（语句格式）
记录 SQL 语句本身。
binlog
# at 123456
#2024-01-15 10:30:00 server id 1
SET @@session.time_zone = '+08:00'/*!*/;
DELETE FROM users WHERE age > 30;
/*!*/;| 特性 | 说明 |
|---|---|
| 日志大小 | 最小（只存 SQL 语句） |
| 优点 | 空间占用少，可读性强 |
| 缺点 | 不安全——依赖上下文（时间函数、UUID、LOAD DATA INFILE 等）可能导致主从不一致 |
| 适用 | MySQL 5.7 之前默认，现已不推荐 |
ROW（行格式）
记录每一行数据的具体变更。
binlog
# at 789012
#2024-01-15 10:30:00 server id 1
### DELETE FROM `db`.`users`
### WHERE
### @1=1 -- id=1
### @2='Tom' -- name='Tom'
### @3=25 -- age=25| 特性 | 说明 |
|---|---|
| 日志大小 | 最大（每行变更全记录） |
| 优点 | 最安全——主从绝对一致 |
| 缺点 | 空间占用大（尤其大范围 UPDATE/DELETE） |
| 适用 | 生产环境推荐（5.7+ 默认） |
MIXED（混合格式）
STATEMENT + ROW 的自动切换。
sql
-- 大多数情况用 STATEMENT（省空间）
DELETE FROM users WHERE id = 1; -- 用 STATEMENT
-- 遇到"不安全"语句自动切 ROW
DELETE FROM users WHERE create_time = NOW(); -- NOW() 不确定，切 ROW
DELETE FROM users LIMIT 10; -- LIMIT 不确定，切 ROW| 特性 | 说明 |
|---|---|
| 日志大小 | 中等 |
| 优点 | 兼顾空间和安全 |
| 缺点 | 自动切换规则复杂，可能出现意外不一致 |
| 适用 | 少数场景，但不如直接 ROW |
格式对比总结
| 对比维度 | STATEMENT | ROW | MIXED |
|---|---|---|---|
| 日志大小 | 小 | 大 | 中 |
| 一致性保证 | ❌ 不安全 | ✅ 绝对安全 | ⚠️ 大部分安全 |
| 可读性 | ✅ SQL | ❌ 难读 | 看情况 |
| 恢复灵活度 | ❌ 不支持闪回 | ✅ 支持闪回 | 看情况 |
| 性能影响 | 小 | 大（尤其大事务） | 中 |
| 推荐 | ❌ 不推荐 | ✅ 推荐 | ⚠️ 少用 |
2.2 Binlog 写入机制
事务提交 → Binlog 写入流程:
1. 事务执行期间 → 产生的 Binlog 事件写入 Binlog Cache（每个事务独立）
2. 事务提交时 → Binlog Cache 写入 Binlog 文件
└── sync_binlog 控制刷盘策略:
├── sync_binlog=1 → 每次提交刷盘 ✅ 最安全
├── sync_binlog=0 → 由 OS 决定刷盘（可能丢）
└── sync_binlog=N → 每 N 次提交刷一次盘
3. 事务提交成功 ✅sync_binlog 配置对比：
| 值 | 安全性 | 性能 | 说明 |
|---|---|---|---|
| 1 | ✅ 不丢数据 | 低 | 每次提交 fsync，最安全 |
| 0 | ❌ 可能丢最后 N 个事务 | 高 | 依赖 OS 调度 |
| N (如 100) | ⚠️ 最多丢 N 个事务 | 中 | 批量刷盘，性能折中 |
生产推荐： sync_binlog = 1 + innodb_flush_log_at_trx_commit = 1
2.3 Binlog 的主从复制
Binlog 复制流程
Master Slave
┌──────────────┐ ┌──────────────┐
│ 写入操作 │ │ SQL 线程 │
│ ↓ │ │ ↑ │
│ Binlog │ ──────→ │ Relay Log │
│ 文件 │ IO │ (中继日志) │
└──────────────┘ 线程 └───────┬──────┘
│
▼
┌──────────────┐
│ 数据文件 │
└──────────────┘
步骤:
1. Master 提交事务 → 写入 Binlog
2. Slave IO 线程 → 从 Master 拉取 Binlog → 写入 Relay Log
3. Slave SQL 线程 → 读取 Relay Log → 在 Slave 上重放复制延迟的原因：
- Slave SQL 线程是单线程的（直到 8.0 支持 MTS 多线程复制）
- Master 的写入并发度高时，Slave 重放跟不上
- 大事务（如大批量 DELETE）在 Slave 上执行时间长
2.4 时间点恢复（PITR）
bash
# 1. 全量备份
mysqldump --all-databases --single-transaction --master-data=2 > backup.sql
# 2. 恢复到某个时间点（比如误 DROP 之前）
mysql < backup.sql # 先恢复全量
# 3. 从 Binlog 中找到并执行误操作之前的日志
mysqlbinlog --stop-datetime="2024-01-15 10:29:00" mysql-bin.000001 | mysql
# 4. 跳过误操作，继续执行后续
mysqlbinlog --start-datetime="2024-01-15 10:30:00" \
--stop-datetime="2024-01-15 10:31:00" mysql-bin.000001 \
| grep -v "DROP TABLE" | mysql三、面试视角
| 追问 | 答案要点 |
|---|---|
| Binlog 和 Redo Log 的区别？ | Redo=引擎层物理日志（循环写），Binlog=Server 层逻辑日志（追加写）。Redo 保证持久性，Binlog 保证复制和恢复 |
| 三种 Binlog 格式怎么选？ | 推荐 ROW——最安全，支持闪回。STATEMENT 不安全，MIXED 切换规则复杂 |
| ROW 格式有什么缺点？ | 日志量大（尤其大事务，ALTER TABLE 改结构全量记录），小心大事务导致磁盘满 |
| sync_binlog=1 和 =0 的区别？ | 1=每次事务提交刷盘（最安全），0=OS 控制（性能高但可能丢数据） |
| Binlog 怎么做数据恢复？ | 全量备份 + Binlog 增量：mysqlbinlog 解析并重放到指定时间点 |
| 复制延迟怎么解决？ | 配置 MTS（多线程复制，slave_parallel_workers）、避免大事务、使用并行复制 |
📚 相关链接
- Redo Log：**Redo Log**
- 两阶段提交：**两阶段提交与崩溃恢复**
- 主从复制：**主从复制原理**
- ← 返回 **日志系统索引**