日志文件
title: 日志文件 sidebar_position: 4
一、概述
1.1 MySQL日志系统简介
MySQL作为一款成熟的关系型数据库，其日志系统是保障数据安全、实现事务特性和支持数据恢复的核心机制。理解MySQL的日志系统，对于数据库调优、故障排查和架构设计都至关重要。
MySQL中最重要的三种日志：
400px
1.2 三大日志的对比
| 特性 | Redo Log | Undo Log | Binlog |
|---|---|---|---|
| 所属层级 | InnoDB存储引擎层 | InnoDB存储引擎层 | MySQL Server层 |
| 记录内容 | 物理日志（数据页的修改） | 逻辑日志（修改前的数据） | 逻辑日志（SQL语句/行变化） |
| 主要作用 | 崩溃恢复、保证持久性 | 事务回滚、MVCC | 主从复制、数据备份 |
| 写入时机 | 事务执行过程中 | 数据修改前 | 事务提交时 |
| 文件形式 | 循环写入固定文件 | 存储在系统表空间/独立表空间 | 追加写入多个文件 |
1.3 为什么需要这三种日志？
这三种日志各司其职，共同保障了MySQL的ACID特性：
| ACID特性 | 实现机制 |
|---|---|
| 原子性 (Atomicity) | Undo Log：事务失败时回滚到修改前状态 |
| 一致性 (Consistency) | 由原子性、隔离性和持久性共同保证 |
| 隔离性 (Isolation) | Undo Log：通过MVCC实现多版本并发控制 |
| 持久性 (Durability) | Redo Log：崩溃后通过重做日志恢复已提交事务 |
二、Redo Log（重做日志）
2.1 什么是Redo Log
Redo Log（重做日志） 是InnoDB存储引擎特有的日志，用于记录事务操作引起的数据页变化。它是一种物理日志，记录的是"在某个数据页上做了什么修改"。
Redo Log的核心作用是实现WAL（Write-Ahead Logging） 机制：
:::tip WAL (Write-Ahead Logging) 机制 先写日志，再写磁盘。事务提交时，先将Redo Log写入磁盘，不必立即将数据页刷盘。即使系统崩溃，也能通过Redo Log恢复数据，保证事务的持久性。 :::
2.2 为什么需要Redo Log
问题背景：
InnoDB以页（Page） 为单位管理数据，默认每页16KB。如果每次修改都直接写磁盘：
问题场景分析：
┌────────────────────────────────────────────────────────────────────┐
│ 假设：修改一行只有几十字节的数据 │
├────────────────────────────────────────────────────────────────────┤
│ │
│ 方案一：直接写数据页到磁盘 │
│ ┌─────────────────────────────────────────────────────────────┐ │
│ │ 问题1：每次写入16KB，即使只改几十字节，IO效率极低 │ │
│ │ 问题2：数据页可能分散在磁盘各处，随机IO性能差 │ │
│ │ 问题3：修改和刷盘之间若宕机，数据丢失 │ │
│ └─────────────────────────────────────────────────────────────┘ │
│ │
│ 方案二：使用Redo Log（WAL机制） │
│ ┌─────────────────────────────────────────────────────────────┐ │
│ │ 优势1：Redo Log体积小，只记录变化部分 │ │
│ │ 优势2：Redo Log顺序写入，顺序IO性能远高于随机IO │ │
│ │ 优势3：事务提交只需Redo Log落盘，数据页可延迟刷盘 │ │
│ └─────────────────────────────────────────────────────────────┘ │
│ │
└────────────────────────────────────────────────────────────────────┘
性能对比：
| 操作 | 磁盘IO类型 | 性能 |
|---|---|---|
| 直接刷数据页 | 随机IO（分散写） | 慢（约100 IOPS） |
| 写Redo Log | 顺序IO（追加写） | 快（约10000+ IOPS） |
2.3 Redo Log的结构
2.3.1 Redo Log Buffer（缓冲区）
Redo Log Buffer是内存中的一块区域，用于暂存Redo Log记录：
┌─────────────────────────────────────────────────────────────┐
│ Redo Log Buffer (内存) │
│ 默认大小：16MB (innodb_log_buffer_size) │
├─────────────────────────────────────────────────────────────┤
│ │
│ ┌──────────┐ ┌──────────┐ ┌──────────┐ ┌──────────┐ │
│ │ Redo Log │ │ Redo Log │ │ Redo Log │ │ ... │ │
│ │ Record 1 │ │ Record 2 │ │ Record 3 │ │ │ │
│ └──────────┘ └──────────┘ └──────────┘ └──────────┘ │
│ │
│ │ │
│ ▼ │
│ ┌──────────┐ │
│ │ 刷盘 │ │
│ └──────────┘ │
│ │ │
└──────────────────────────┼──────────────────────────────────┘
│
▼
┌─────────────────────────────────────────────────────────────┐
│ Redo Log Files (磁盘) │
└─────────────────────────────────────────────────────────────┘
2.3.2 Redo Log Files（日志文件）
Redo Log以日志文件组的形式存储在磁盘上，采用循环写入的方式：
┌────────────────────────────────────────────────────────────────────────┐
│ Redo Log 循环写入机制 │
├────────────────────────────────────────────────────────────────────────┤
│ │
│ ib_logfile0 ib_logfile1 ib_logfile2 (可选) │
│ ┌──────────────┐ ┌──────────────┐ ┌──────────────┐ │
│ │ │ │ │ │ │ │
│ │ 写满后 │ │ 继续写 │ │ 继续写 │ │
│ │ →→→→→→→ │ →→ │ →→→→→→→ │ →→ │ →→→→→→→ │ →→ ... │
│ │ │ │ │ │ │ │ │
│ │ │ │ │ │ │ │ │
│ └──────────────┘ └──────────────┘ └──────────────┘ │ │
│ ↑ │ │
│ └──────────────────────────────────────────────────────┘ │
│ 循环覆盖 │
│ │
│ ┌─────────────────────────────────────────────────────────────────┐ │
│ │ write pos：当前写入位置 │ │
│ │ checkpoint：当前已刷盘到数据页的位置，此前的日志可以被覆盖 │ │
│ │ 可用空间 = write pos 到 checkpoint 之间的区域 │ │
│ └─────────────────────────────────────────────────────────────────┘ │
│ │
└────────────────────────────────────────────────────────────────────────┘
关键指针说明：
| 指针 | 含义 |
|---|---|
| write pos | 当前Redo Log写入的位置，边写边后移 |
| checkpoint | 当前要擦除的位置，表示此前的Redo Log对应的数据页已刷盘 |
:::warning 注意
当write pos追上checkpoint时，说明Redo Log文件满了，必须暂停写入，先将部分脏页刷盘，推进checkpoint，腾出空间。这就是为什么要合理设置Redo Log大小的原因。
:::
2.4 Redo Log的写入流程
2.4.1 事务执行过程
┌────────────────────────────────────────────────────────────────────────┐
│ Redo Log 完整写入流程 │
├────────────────────────────────────────────────────────────────────────┤
│ │
│ ① 事务开始执行 │
│ │ │
│ ▼ │
│ ② 读取数据页到Buffer Pool │
│ │ │
│ ▼ │
│ ③ 在Buffer Pool中修改数据（产生脏页） │
│ │ │
│ ▼ │
│ ④ 将修改操作以Redo Log形式写入Redo Log Buffer │
│ │ │
│ ▼ │
│ ⑤ 事务提交时，根据刷盘策略将Redo Log Buffer刷入磁盘 │
│ │ │
│ ▼ │
│ ⑥ 后台线程异步将脏页刷回磁盘（Checkpoint机制） │
│ │
└────────────────────────────────────────────────────────────────────────┘
2.4.2 刷盘策略（innodb_flush_log_at_trx_commit）
这是MySQL中最重要的参数之一，控制Redo Log的刷盘时机：
| 值 | 行为 | 性能 | 安全性 |
|---|---|---|---|
| 0 | 每秒刷盘一次，事务提交时不刷盘 | 最高 | 最低（最多丢1秒数据） |
| 1 | 每次事务提交都刷盘（默认） | 最低 | 最高（不丢数据） |
| 2 | 每次提交写入OS缓存，每秒刷盘 | 中等 | 中等（OS崩溃可能丢数据） |
┌────────────────────────────────────────────────────────────────────────┐
│ innodb_flush_log_at_trx_commit 刷盘策略 │
├────────────────────────────────────────────────────────────────────────┤
│ │
│ 值 = 0 │
│ ┌─────────────┐ ┌─────────────┐ ┌─────────────┐ │
│ │ Log Buffer │ ──×──→ │ OS Cache │ ──×──→ │ Disk │ │
│ └─────────────┘ 提交 └─────────────┘ 提交 └─────────────┘ │
│ 不操作 不操作 │
│ 后台线程每秒刷盘一次 │
│ │
│ 值 = 1（默认，最安全） │
│ ┌─────────────┐ ┌─────────────┐ ┌─────────────┐ │
│ │ Log Buffer │ ─────→ │ OS Cache │ ─────→ │ Disk │ │
│ └─────────────┘ 提交 └─────────────┘ 提交 └─────────────┘ │
│ 写入 fsync │
│ │
│ 值 = 2 │
│ ┌─────────────┐ ┌─────────────┐ ┌─────────────┐ │
│ │ Log Buffer │ ─────→ │ OS Cache │ ──×──→ │ Disk │ │
│ └─────────────┘ 提交 └─────────────┘ 提交 └─────────────┘ │
│ 写入 不操作 │
│ 后台线程每秒刷盘一次 │
│ │
└────────────────────────────────────────────────────────────────────────┘
:::important 生产环境建议
对于金融、电商等对数据安全要求高的场景，innodb_flush_log_at_trx_commit必须设置为1。设置为0或2虽然性能更好，但在数据库或操作系统崩溃时可能丢失数据。
:::
2.5 Redo Log的崩溃恢复
当MySQL意外崩溃重启后，InnoDB会执行崩溃恢复：
┌────────────────────────────────────────────────────────────────────────┐
│ 崩溃恢复流程 │
├────────────────────────────────────────────────────────────────────────┤
│ │
│ ① MySQL重启 │
│ │ │
│ ▼ │
│ ② 读取Redo Log文件 │
│ │ │
│ ▼ │
│ ③ 找到checkpoint位置 │
│ │ │
│ ▼ │
│ ④ 从checkpoint开始，将Redo Log中的操作重新执行（Redo） │
│ │ │
│ ▼ │
│ ⑤ 恢复到崩溃前的状态 │
│ │ │
│ ▼ │
│ ⑥ 回滚未完成的事务（配合Undo Log） │
│ │
└────────────────────────────────────────────────────────────────────────┘
2.6 Redo Log相关配置
| 参数 | 默认值 | 说明 |
|---|---|---|
innodb_log_file_size | 48MB | 单个Redo Log文件大小 |
innodb_log_files_in_group | 2 | Redo Log文件数量 |
innodb_log_buffer_size | 16MB | Redo Log Buffer大小 |
innodb_flush_log_at_trx_commit | 1 | 刷盘策略 |
-- 查看当前配置
SHOW VARIABLES LIKE 'innodb_log%';
SHOW VARIABLES LIKE 'innodb_flush_log_at_trx_commit';
-- MySQL 8.0.30+ 动态调整Redo Log大小
-- 总大小 = innodb_redo_log_capacity
ALTER INSTANCE DISABLE INNODB REDO_LOG; -- 仅特殊场景使用三、Undo Log（回滚日志）
3.1 什么是Undo Log
Undo Log（回滚日志） 是InnoDB存储引擎用于保存数据修改前状态的日志。它是一种逻辑日志，记录的是"如何撤销某个操作"。
Undo Log的两大核心作用：
| 作用 | 说明 |
|---|---|
| 事务回滚 | 当事务失败或执行ROLLBACK时，通过Undo Log将数据恢复到修改前的状态 |
| MVCC支持 | 提供数据的历史版本，实现多版本并发控制，使读操作不阻塞写操作 |
3.2 Undo Log的类型
根据操作类型，Undo Log分为两种：
| 类型 | 触发操作 | 保留时长 | 说明 |
|---|---|---|---|
| Insert Undo Log | INSERT | 事务提交后即可删除 | 因为INSERT只对当前事务可见，回滚只需删除即可 |
| Update Undo Log | UPDATE / DELETE | 需保留至没有事务引用 | 需要支持MVCC，其他事务可能读取历史版本 |
┌────────────────────────────────────────────────────────────────────────┐
│ Undo Log 类型对比 │
├────────────────────────────────────────────────────────────────────────┤
│ │
│ Insert Undo Log Update Undo Log │
│ ┌──────────────────────┐ ┌──────────────────────┐ │
│ │ │ │ │ │
│ │ INSERT INTO t │ │ UPDATE t SET │ │
│ │ VALUES (1, 'A') │ │ name = 'B' │ │
│ │ │ │ WHERE id = 1 │ │
│ │ ────────────────── │ │ │ │
│ │ Undo: DELETE │ │ ────────────────── │ │
│ │ WHERE id = 1 │ │ Undo: 原值 'A' │ │
│ │ │ │ + 版本链指针 │ │
│ └──────────────────────┘ └──────────────────────┘ │
│ │
│ 回滚：直接删除记录 回滚：恢复为原值 │
│ 提交后：立即清理 提交后：保留供MVCC │
│ │
└────────────────────────────────────────────────────────────────────────┘
3.3 Undo Log与版本链
每次对记录进行UPDATE或DELETE操作时，InnoDB都会：
- 将修改前的数据写入Undo Log
- 通过
DB_ROLL_PTR指针将新旧版本串联起来 - 形成一条版本链（Version Chain）
┌────────────────────────────────────────────────────────────────────────┐
│ 版本链示例 │
├────────────────────────────────────────────────────────────────────────┤
│ │
│ 事务执行过程： │
│ T1: INSERT (id=1, name='张三') by trx_id=100 │
│ T2: UPDATE name='李四' by trx_id=200 │
│ T3: UPDATE name='王五' by trx_id=300 │
│ │
│ 版本链结构： │
│ │
│ ┌─────────────────┐ ┌─────────────────┐ ┌─────────────────┐ │
│ │ 当前行 │ │ Undo Log │ │ Undo Log │ │
│ │ name='王五' │────→│ name='李四' │────→│ name='张三' │ │
│ │ trx_id=300 │ │ trx_id=200 │ │ trx_id=100 │ │
│ │ roll_ptr ───────┼─┘ │ roll_ptr ───────┼─┘ │ roll_ptr=NULL │ │
│ └─────────────────┘ └─────────────────┘ └─────────────────┘ │
│ 最新版本 历史版本 初始版本 │
│ │
└────────────────────────────────────────────────────────────────────────┘
备注
关于MVCC如何利用版本链实现可见性判断，请参考 MVCC多版本并发控制 文档。
3.4 Undo Log的事务回滚
当事务回滚时，InnoDB会根据Undo Log记录的信息，将数据恢复到修改前的状态：
-- 事务回滚场景示例
BEGIN;
INSERT INTO users VALUES (1, '张三'); -- Undo: DELETE WHERE id=1
UPDATE users SET name='李四' WHERE id=2; -- Undo: 记录原值 '王五'
DELETE FROM users WHERE id=3; -- Undo: 记录完整的被删除行
-- 执行回滚
ROLLBACK;
-- InnoDB 按相反顺序执行Undo Log中的操作：
-- 1. 恢复 id=3 的行
-- 2. 将 id=2 的name恢复为'王五'
-- 3. 删除 id=1 的行3.5 Undo Log的存储
3.5.1 存储位置
| MySQL版本 | 存储位置 |
|---|---|
| MySQL 5.6及之前 | 只能存储在系统表空间（ibdata1） |
| MySQL 5.6+ | 可配置独立的Undo表空间 |
| MySQL 8.0 | 默认使用独立的Undo表空间 |
-- 查看Undo表空间配置
SHOW VARIABLES LIKE 'innodb_undo%';
-- MySQL 8.0 配置示例
-- innodb_undo_tablespaces = 2 -- Undo表空间数量
-- innodb_undo_directory = /data/undo -- Undo文件存储目录3.5.2 Undo Log的清理（Purge）
Update Undo Log不能在事务提交后立即删除，因为其他事务可能还需要读取历史版本（MVCC）。InnoDB通过Purge线程来清理不再需要的Undo Log：
┌────────────────────────────────────────────────────────────────────────┐
│ Purge 清理机制 │
├────────────────────────────────────────────────────────────────────────┤
│ │
│ 清理条件： │
│ ┌──────────────────────────────────────────────────────────────────┐ │
│ │ 当没有任何活跃的ReadView需要访问某个Undo Log版本时， │ │
│ │ Purge线程才会将其清理。 │ │
│ └──────────────────────────────────────────────────────────────────┘ │
│ │
│ 问题：长事务导致Undo Log无法清理 │
│ ┌──────────────────────────────────────────────────────────────────┐ │
│ │ 假设：事务A一直未提交，保持着一个很早的ReadView │ │
│ │ 结果：Purge线程无法清理事务A之后产生的Undo Log │ │
│ │ 后果：Undo Log持续增长，占用大量磁盘空间 │ │
│ └──────────────────────────────────────────────────────────────────┘ │
│ │
└────────────────────────────────────────────────────────────────────────┘
:::caution 警告 避免长事务！ 长事务会导致：
- Undo Log无法及时清理，占用大量存储空间
- 版本链过长，查询历史版本效率降低
- 锁持有时间过长，影响并发性能 :::
3.6 Undo Log相关配置
| 参数 | 默认值 | 说明 |
|---|---|---|
innodb_undo_tablespaces | 2 (MySQL 8.0) | Undo表空间数量 |
innodb_max_undo_log_size | 1GB | 单个Undo表空间最大值 |
innodb_undo_log_truncate | ON | 是否启用Undo表空间自动截断 |
innodb_purge_threads | 4 | Purge线程数量 |
-- 查看Undo Log使用情况
SELECT * FROM information_schema.INNODB_METRICS
WHERE NAME LIKE '%undo%';四、Binlog（归档日志）
4.1 什么是Binlog
Binlog（Binary Log，二进制日志） 是MySQL Server层的日志，记录了所有对数据库的数据变更操作。它是一种逻辑日志，记录的是SQL语句或数据行的变化。
:::note 与Redo Log的区别
- Redo Log 是InnoDB存储引擎层的日志，MyISAM等其他引擎没有
- Binlog 是MySQL Server层的日志，所有存储引擎都会产生 :::
4.2 Binlog的作用
| 作用 | 说明 |
|---|---|
| 主从复制 | 从库通过读取主库的Binlog来同步数据 |
| 数据恢复 | 通过回放Binlog可以将数据恢复到某个时间点 |
| 数据审计 | 记录了所有的数据变更，可用于审计 |
┌────────────────────────────────────────────────────────────────────────┐
│ MySQL 主从复制架构 │
├────────────────────────────────────────────────────────────────────────┤
│ │
│ 主库 (Master) 从库 (Slave) │
│ ┌─────────────────────┐ ┌─────────────────────┐ │
│ │ │ │ │ │
│ │ ┌─────────────┐ │ │ ┌─────────────┐ │ │
│ │ │ Client │ │ │ │ Client │ │ │
│ │ └──────┬──────┘ │ │ └──────┬──────┘ │ │
│ │ │ │ │ │ 读 │ │
│ │ ▼ 写/读 │ │ ▼ │ │
│ │ ┌─────────────┐ │ │ ┌─────────────┐ │ │
│ │ │ MySQL │ │ Binlog │ │ MySQL │ │ │
│ │ │ Server │───┼───────────→│ │ Server │ │ │
│ │ └─────────────┘ │ 复制 │ └─────────────┘ │ │
│ │ │ │ │ │ │ │
│ │ ▼ │ │ ▼ │ │
│ │ ┌─────────────┐ │ │ ┌─────────────┐ │ │
│ │ │ Binlog │ │ │ │ Relay Log │ │ │
│ │ └─────────────┘ │ │ └─────────────┘ │ │
│ │ │ │ │ │
│ └─────────────────────┘ └─────────────────────┘ │
│ │
│ 复制流程： │
│ 1. 主库执行写操作，生成Binlog │
│ 2. 从库IO线程读取主库Binlog，写入本地Relay Log │
│ 3. 从库SQL线程读取Relay Log，重放SQL操作 │
│ │
└────────────────────────────────────────────────────────────────────────┘
4.3 Binlog的记录格式
Binlog有三种记录格式，通过binlog_format参数设置：
| 格式 | 记录内容 | 优点 | 缺点 |
|---|---|---|---|
| STATEMENT | SQL语句 | 日志量小 | 某些函数可能导致主从不一致 |
| ROW | 行数据的变化 | 数据一致性高 | 日志量大 |
| MIXED | 自动选择 | 兼顾两者 | 某些场景仍有问题 |
4.3.1 STATEMENT格式
-- 执行的SQL
UPDATE users SET status = 1 WHERE created_at < NOW();
-- Binlog记录的内容（简化）
SET TIMESTAMP=1702876800;
UPDATE users SET status = 1 WHERE created_at < NOW();:::warning STATEMENT格式的问题
使用NOW()、UUID()、RAND()等不确定性函数时，主从库执行结果可能不同，导致数据不一致。
:::
4.3.2 ROW格式（推荐）
-- 执行的SQL
UPDATE users SET status = 1 WHERE id IN (1, 2, 3);
-- Binlog记录的内容（简化）
-- 记录每一行修改前和修改后的完整数据
### UPDATE users
### WHERE id=1, name='张三', status=0
### SET id=1, name='张三', status=1
### UPDATE users
### WHERE id=2, name='李四', status=0
### SET id=2, name='李四', status=1
...4.3.3 格式对比
┌────────────────────────────────────────────────────────────────────────┐
│ Binlog 格式对比示例 │
├────────────────────────────────────────────────────────────────────────┤
│ │
│ SQL: DELETE FROM users WHERE status = 0; │
│ 假设：影响100万行数据 │
│ │
│ STATEMENT格式： │
│ ┌──────────────────────────────────────────────────────────────────┐ │
│ │ DELETE FROM users WHERE status = 0; │ │
│ │ 日志大小：约100字节 │ │
│ └──────────────────────────────────────────────────────────────────┘ │
│ │
│ ROW格式： │
│ ┌──────────────────────────────────────────────────────────────────┐ │
│ │ DELETE users WHERE id=1, name='xxx', status=0, ... │ │
│ │ DELETE users WHERE id=2, name='xxx', status=0, ... │ │
│ │ ... (100万条记录) │ │
│ │ 日志大小：可能几百MB │ │
│ └──────────────────────────────────────────────────────────────────┘ │
│ │
└────────────────────────────────────────────────────────────────────────┘
:::tip 生产环境建议 推荐使用ROW格式。虽然日志量较大，但数据一致性最高，且便于数据恢复和问题排查。MySQL 8.0默认使用ROW格式。 :::
4.4 Binlog的写入流程
┌────────────────────────────────────────────────────────────────────────┐
│ Binlog 写入流程 │
├────────────────────────────────────────────────────────────────────────┤
│ │
│ ① 事务执行过程中 │
│ │ │
│ ▼ │
│ ② 将变更写入线程的 binlog cache（内存） │
│ │ │
│ ▼ │
│ ③ 事务提交时，将 binlog cache 写入 binlog 文件 │
│ │ │
│ ▼ │
│ ④ 根据 sync_binlog 配置决定是否刷盘 │
│ │
│ ┌─────────────────────────────────────────────────────────────────┐ │
│ │ binlog cache │ │
│ │ ┌─────────┐ ┌─────────┐ ┌─────────┐ │ │
│ │ │ Thread1 │ │ Thread2 │ │ Thread3 │ 每个线程独立的缓存 │ │
│ │ │ Cache │ │ Cache │ │ Cache │ │ │
│ │ └────┬────┘ └────┬────┘ └────┬────┘ │ │
│ │ └───────────┬┴───────────┘ │ │
│ │ ▼ │ │
│ │ ┌─────────────┐ │ │
│ │ │ Binlog File │ 追加写入 │ │
│ │ └─────────────┘ │ │
│ └─────────────────────────────────────────────────────────────────┘ │
│ │
└────────────────────────────────────────────────────────────────────────┘
sync_binlog刷盘策略
| 值 | 行为 | 性能 | 安全性 |
|---|---|---|---|
| 0 | 由OS决定何时刷盘 | 最高 | 最低 |
| 1 | 每次事务提交都刷盘 | 最低 | 最高 |
| N | 每N个事务刷盘一次 | 中等 | 中等 |
:::important 生产环境建议
为保证数据安全，生产环境建议设置sync_binlog=1。配合innodb_flush_log_at_trx_commit=1，可以实现最高的数据安全级别，但性能会有所下降。这种配置也称为"双1配置"。
:::
4.5 Binlog文件管理
Binlog以追加方式写入多个文件，需要定期清理：
-- 查看所有Binlog文件
SHOW BINARY LOGS;
-- 查看当前正在使用的Binlog文件
SHOW MASTER STATUS;
-- 查看Binlog内容
SHOW BINLOG EVENTS IN 'mysql-bin.000001';
-- 手动清理Binlog
PURGE BINARY LOGS TO 'mysql-bin.000005';
PURGE BINARY LOGS BEFORE '2024-01-01 00:00:00';
-- 自动清理配置（保留7天）
SET GLOBAL binlog_expire_logs_seconds = 604800; -- MySQL 8.0
SET GLOBAL expire_logs_days = 7; -- MySQL 5.74.6 使用Binlog恢复数据
# 查看Binlog内容（解析为可读格式）
mysqlbinlog --base64-output=decode-rows -v mysql-bin.000001
# 恢复数据到指定时间点
mysqlbinlog --stop-datetime="2024-01-01 12:00:00" mysql-bin.000001 | mysql -u root -p
# 恢复数据到指定位置
mysqlbinlog --stop-position=12345 mysql-bin.000001 | mysql -u root -p
# 跳过某个错误操作（如误删除）
mysqlbinlog --start-position=12345 --stop-position=23456 mysql-bin.000001 | mysql -u root -p4.7 Binlog相关配置
| 参数 | 默认值 | 说明 |
|---|---|---|
log_bin | ON (MySQL 8.0) | 是否开启Binlog |
binlog_format | ROW | Binlog格式 |
sync_binlog | 1 | 刷盘策略 |
max_binlog_size | 1GB | 单个Binlog文件最大值 |
binlog_expire_logs_seconds | 2592000 (30天) | Binlog过期时间 |
binlog_cache_size | 32KB | 每个线程的Binlog缓存大小 |
五、两阶段提交
5.1 为什么需要两阶段提交
在InnoDB存储引擎中，一个事务的提交需要同时写入Redo Log和Binlog。如果这两个操作不是原子的，可能导致两种日志不一致：
┌────────────────────────────────────────────────────────────────────────┐
│ 崩溃场景分析 │
├────────────────────────────────────────────────────────────────────────┤
│ │
│ 场景一：先写Redo Log，后写Binlog │
│ ┌──────────────────────────────────────────────────────────────────┐ │
│ │ 1. 写Redo Log成功 │ │
│ │ 2. 写Binlog前崩溃 │ │
│ │ 3. 恢复：Redo Log有记录，数据被恢复 │ │
│ │ 4. 问题：Binlog没有记录，从库没有这条数据 │ │
│ │ 5. 结果：主从数据不一致 │ │
│ └──────────────────────────────────────────────────────────────────┘ │
│ │
│ 场景二：先写Binlog，后写Redo Log │
│ ┌──────────────────────────────────────────────────────────────────┐ │
│ │ 1. 写Binlog成功 │ │
│ │ 2. 写Redo Log前崩溃 │ │
│ │ 3. 恢复：Redo Log没有记录，数据不存在 │ │
│ │ 4. 问题：Binlog有记录，从库有这条数据 │ │
│ │ 5. 结果：主从数据不一致 │ │
│ └──────────────────────────────────────────────────────────────────┘ │
│ │
└────────────────────────────────────────────────────────────────────────┘
5.2 两阶段提交流程
为了保证Redo Log和Binlog的一致性，MySQL采用了两阶段提交（2PC）：
┌────────────────────────────────────────────────────────────────────────┐
│ 两阶段提交流程 │
├────────────────────────────────────────────────────────────────────────┤
│ │
│ 事务提交 │
│ │ │
│ ▼ │
│ ┌───────────────────────────────────────────────────────────────────┐ │
│ │ 阶段一：Prepare阶段 │ │
│ │ │ │
│ │ 1. 将事务状态设置为Prepare │ │
│ │ 2. 将Redo Log写入磁盘（此时Redo Log状态为Prepare） │ │
│ │ │ │
│ └───────────────────────────────────────────────────────────────────┘ │
│ │ │
│ ▼ │
│ ┌───────────────────────────────────────────────────────────────────┐ │
│ │ 写入Binlog │ │
│ │ │ │
│ │ 3. 将Binlog写入磁盘 │ │
│ │ │ │
│ └───────────────────────────────────────────────────────────────────┘ │
│ │ │
│ ▼ │
│ ┌───────────────────────────────────────────────────────────────────┐ │
│ │ 阶段二：Commit阶段 │ │
│ │ │ │
│ │ 4. 将Redo Log状态更新为Commit │ │
│ │ 5. 事务提交完成 │ │
│ │ │ │
│ └───────────────────────────────────────────────────────────────────┘ │
│ │
└────────────────────────────────────────────────────────────────────────┘
5.3 崩溃恢复规则
两阶段提交保证了崩溃恢复时的数据一致性：
| Redo Log状态 | Binlog状态 | 恢复策略 |
|---|---|---|
| Prepare | 无Binlog | 回滚事务 |
| Prepare | 有Binlog | 提交事务 |
| Commit | 有Binlog | 提交事务 |
┌────────────────────────────────────────────────────────────────────────┐
│ 崩溃恢复判断逻辑 │
├────────────────────────────────────────────────────────────────────────┤
│ │
│ 崩溃重启 │
│ │ │
│ ▼ │
│ ┌─────────────────┐ │
│ │ 扫描Redo Log │ │
│ └────────┬────────┘ │
│ │ │
│ ▼ │
│ ┌──────────────────────────────┐ │
│ │ 检查事务状态是否为Prepare │ │
│ └──────────────┬───────────────┘ │
│ │ │
│ ┌─────────────┴─────────────┐ │
│ │ │ │
│ ▼ ▼ │
│ ┌─────────────┐ ┌─────────────────┐ │
│ │ 状态=Commit │ │ 状态=Prepare │ │
│ └──────┬──────┘ └────────┬────────┘ │
│ │ │ │
│ ▼ ▼ │
│ ┌─────────────┐ ┌─────────────────────┐ │
│ │ 提交事务 │ │ 检查Binlog是否完整 │ │
│ └─────────────┘ └──────────┬──────────┘ │
│ │ │
│ ┌──────────────┴──────────────┐ │
│ │ │ │
│ ▼ ▼ │
│ ┌─────────────┐ ┌─────────────┐ │
│ │ Binlog完整 │ │ Binlog不完整│ │
│ └──────┬──────┘ └──────┬──────┘ │
│ │ │ │
│ ▼ ▼ │
│ ┌─────────────┐ ┌─────────────┐ │
│ │ 提交事务 │ │ 回滚事务 │ │
│ └─────────────┘ └─────────────┘ │
│ │
└────────────────────────────────────────────────────────────────────────┘
六、三种日志的协作
6.1 完整的事务执行流程
┌─────────────────────────────────────────────────────────────────────────┐
│ 完整的事务执行流程 │
├─────────────────────────────────────────────────────────────────────────┤
│ │
│ BEGIN; │
│ UPDATE users SET age = 26 WHERE id = 1; │
│ COMMIT; │
│ │
│ 详细流程： │
│ │
│ ① 读取id=1的数据页到Buffer Pool │
│ │ │
│ ▼ │
│ ② 记录原值到Undo Log（age=25） │
│ - 用于事务回滚 │
│ - 形成版本链，支持MVCC │
│ │ │
│ ▼ │
│ ③ 在Buffer Pool中修改数据（age=26），产生脏页 │
│ │ │
│ ▼ │
│ ④ 将修改记录到Redo Log Buffer │
│ │ │
│ ▼ │
│ ⑤ 执行COMMIT，进入两阶段提交 │
│ │ │
│ ┌───────────────┴───────────────┐ │
│ │ Prepare阶段 │ │
│ │ ⑥ Redo Log刷盘（状态=Prepare）│ │
│ └───────────────┬───────────────┘ │
│ │ │
│ ┌───────────────┴───────────────┐ │
│ │ 写入Binlog │ │
│ │ ⑦ Binlog刷盘 │ │
│ └───────────────┬───────────────┘ │
│ │ │
│ ┌───────────────┴───────────────┐ │
│ │ Commit阶段 │ │
│ │ ⑧ Redo Log标记Commit │ │
│ └───────────────┬───────────────┘ │
│ │ │
│ ▼ │
│ ⑨ 事务提交成功 │
│ │ │
│ ▼ │
│ ⑩ 后台线程异步将Buffer Pool脏页刷盘 │
│ │
└─────────────────────────────────────────────────────────────────────────┘
6.2 三者的关系总结
| 日志 | 写入时机 | 作用域 | 核心价值 |
|---|---|---|---|
| Undo Log | 修改数据之前 | 单事务 | 支持回滚和MVCC |
| Redo Log | 事务执行过程中 | 崩溃恢复 | 保证持久性 |
| Binlog | 事务提交时 | 跨服务器 | 主从复制、数据恢复 |
┌─────────────────────────────────────────────────────────────────────────┐
│ 三种日志协作关系图 │
├─────────────────────────────────────────────────────────────────────────┤
│ │
│ ┌─────────────────┐ │
│ │ 事务执行 │ │
│ └────────┬────────┘ │
│ │ │
│ ┌────────────────────────┼────────────────────────┐ │
│ │ │ │ │
│ ▼ ▼ ▼ │
│ ┌───────────────┐ ┌───────────────┐ ┌───────────────┐ │
│ │ Undo Log │ │ Redo Log │ │ Binlog │ │
│ └───────┬───────┘ └───────┬───────┘ └───────┬───────┘ │
│ │ │ │ │
│ ▼ ▼ ▼ │
│ ┌───────────────┐ ┌───────────────┐ ┌───────────────┐ │
│ │ 事务回滚 │ │ 崩溃恢复 │ │ 主从复制 │ │
│ │ MVCC │ │ 持久性保证 │ │ 数据恢复 │ │
│ └───────────────┘ └───────────────┘ └───────────────┘ │
│ │ │ │ │
│ │ │ │ │
│ └───────────────────────┴───────────────────────┘ │
│ │ │
│ ▼ │
│ ┌─────────────────┐ │
│ │ 数据安全保障 │ │
│ └─────────────────┘ │
│ │
└─────────────────────────────────────────────────────────────────────────┘
七、常见问题
7.1 Redo Log和Binlog的区别
| 区别点 | Redo Log | Binlog |
|---|---|---|
| 实现层级 | InnoDB存储引擎层 | MySQL Server层 |
| 日志类型 | 物理日志 | 逻辑日志 |
| 写入方式 | 循环写，空间固定 | 追加写，文件递增 |
| 主要用途 | 崩溃恢复 | 主从复制、备份恢复 |
| 存储引擎 | 仅InnoDB | 所有引擎 |
7.2 为什么要有两份日志？
- 历史原因：MySQL早期使用MyISAM引擎，没有崩溃恢复能力，只有Binlog用于主从复制
- 架构分层：InnoDB作为插件引入MySQL后，使用自己的Redo Log实现崩溃恢复
- 各司其职：
- Redo Log：保证单机数据不丢失（持久性）
- Binlog：保证跨服务器数据同步（复制）
7.3 只用Binlog能否保证崩溃恢复？
不行。原因：
- 性能问题：Binlog是逻辑日志，恢复时需要解析SQL重新执行，效率低
- 写入时机：Binlog在事务提交时才写入，事务执行过程中的变更无法恢复
- 循环写入：Redo Log循环写入固定大小，而Binlog追加写入，Redo Log的checkpoint机制更适合崩溃恢复
7.4 "双1配置"是什么？
"双1配置"指同时设置：
innodb_flush_log_at_trx_commit = 1sync_binlog = 1
这是数据安全性最高的配置，保证每次事务提交时Redo Log和Binlog都刷盘，但性能会有所损失。
7.5 如何优化日志相关性能？
| 优化点 | 建议 |
|---|---|
| Redo Log大小 | 适当增大，减少Checkpoint频率 |
| Redo Log数量 | 建议2-4个文件 |
| Binlog格式 | 使用ROW格式，虽然日志量大但一致性高 |
| Binlog过期 | 根据业务需求设置合理的过期时间 |
| Undo表空间 | 使用独立Undo表空间，便于空间回收 |
八、总结
MySQL的日志系统是保障数据安全和实现高可用的基石：
| 日志 | 核心作用 | 一句话总结 |
|---|---|---|
| Redo Log | 崩溃恢复 | 保证已提交事务的持久性 |
| Undo Log | 事务回滚 + MVCC | 保证事务的原子性和隔离性 |
| Binlog | 主从复制 + 备份恢复 | 保证数据的可复制性和可恢复性 |
理解这三种日志的原理和协作关系，对于数据库运维、性能调优和故障排查都有重要意义。在生产环境中，合理配置日志参数，可以在数据安全和性能之间取得最佳平衡。