MySQL 日志系统概述
MySQL 实现了多种类型的日志，各自承担不同的职责，主要包括：
- 错误日志（Error Log）：记录启动、运行、关闭过程中的问题
- 通用查询日志（General Query Log）：记录所有 SQL 查询
- 慢查询日志（Slow Query Log）：记录执行时间超过阈值的查询
- 二进制日志（Binary Log，即 binlog）：记录所有数据修改语句
- 重做日志（Redo Log）：确保事务的持久性
- 撤销日志（Undo Log）：支持事务回滚和多版本并发控制
- 中继日志（Relay Log）：在从库中接收并重放主库的 binlog
本文将深入讲解 binlog、redo log 和 undo log 这三种核心日志的工作原理和应用场景。
binlog（二进制日志）
什么是 binlog？
binlog 是 MySQL 服务层实现的一种逻辑日志，记录了所有对数据库内容的修改操作（不包括 SELECT 等查询操作）。它以二进制形式存储，可用于数据恢复、主从复制等场景。
binlog 的三种格式
MySQL 支持三种 binlog 格式，各有优缺点：
- STATEMENT 格式：记录 SQL 语句原文
- 优点：日志量小，节省存储空间和网络带宽
- 缺点：某些函数在主从复制时可能导致数据不一致
- 实际案例：在使用INSERT INTO target_table SELECT * FROM source_table WHERE create_time < NOW()时，主从节点执行 NOW()的时间不同，导致复制的数据不一致
- ROW 格式：记录数据行变更前后的值
- 优点：精确记录数据变化，主从复制更可靠
- 缺点：日志量大，特别是批量操作时
- 实际案例：更新一张有 10 万行数据的表，ROW 格式可能产生几十 MB 甚至上百 MB 的 binlog，而 STATEMENT 格式可能只有几百字节
- MIXED 格式：混合模式，MySQL 会自动选择使用 STATEMENT 还是 ROW 格式
注意：从 MySQL 8.0 开始，默认的 binlog 格式已从 STATEMENT 改为 ROW，这反映了 MySQL 对数据一致性要求的提升。
binlog 定位与恢复案例
基于时间范围恢复
# 查看当前的binlog文件
SHOW BINARY LOGS;
# 查看当前正在写入的binlog位置
SHOW MASTER STATUS;
# 查看特定时间段的binlog内容
mysqlbinlog --start-datetime="2023-11-10 14:00:00" \
--stop-datetime="2023-11-10 15:00:00" \
/var/lib/mysql/mysql-bin.000123 > recovery.sql
# 分析日志内容，确定问题所在
grep "UPDATE orders" recovery.sql
# 使用binlog恢复数据
mysql -u root -p < recovery.sql
基于位置点精确恢复
当需要更精确的恢复时，可以使用位置点：
# 先查看binlog内容，定位需要恢复的位置点
mysqlbinlog --base64-output=decode-rows -v /var/lib/mysql/mysql-bin.000123 | less
# 基于位置点恢复（更精确）
mysqlbinlog --start-position=1000 --stop-position=2000 /var/lib/mysql/mysql-bin.000123 > recovery.sql
误删表恢复案例
# 1. 定位误操作的binlog位置
mysqlbinlog --base64-output=decode-rows -v /var/lib/mysql/mysql-bin.000123 | grep "DROP TABLE"
# 2. 在从库模拟恢复（避免主库操作风险）
mysqlbinlog --start-position=4321 --stop-position=5678 /var/lib/mysql/mysql-bin.000123 | mysql -h slave -u root -p
binlog 相关配置
# my.cnf中的binlog配置
[mysqld]
# 启用binlog
log-bin=mysql-bin
# 设置binlog格式
binlog_format=ROW
# binlog过期时间（天）
expire_logs_days=7
# 单个binlog文件大小上限
max_binlog_size=100M
# binlog刷盘策略：1表示每次事务提交都刷盘，最安全但性能较低
sync_binlog=1
redo log（重做日志）
redo log 的作用
redo log 是 InnoDB 存储引擎特有的日志，主要用于实现事务的持久性。当数据修改时，修改内容会先写入 redo log，再慢慢刷回磁盘，这种方式称为 WAL（Write-Ahead Logging）。
redo log 的主要特点：
- 物理日志，记录"在某个数据页上做了什么修改"
- 循环写入，大小固定，写满时会覆盖最早的部分
- 崩溃后，InnoDB 会使用 redo log 恢复未刷盘的数据修改
redo log 与 Buffer Pool 的交互
数据修改的流程：
- 当事务修改数据时，首先将修改写入 Buffer Pool 中的页，此时页变为"脏页"
- 同时将修改操作记录到 redo log buffer
- 根据配置决定 redo log 写盘时机
- 后台线程会定期将脏页刷新到磁盘（checkpoint）
- 当脏页刷盘后，对应的 redo log 空间可以被重用
redo log 的刷盘策略
根据innodb_flush_log_at_trx_commit参数决定刷盘时机：
- 值为 0：每秒写入磁盘一次，可能丢失 1 秒数据
- 值为 1：每次事务提交都写入磁盘，最安全但性能较低
- 值为 2：每次提交写入 OS 缓存，由 OS 决定何时刷盘
性能对比：设置为 1 时，TPS 通常比设置为 0 或 2 低 15-20%，但提供最高的数据安全性。
redo log 的两阶段提交
为了保证 binlog 和 redo log 的一致性，MySQL 使用两阶段提交机制：
- 准备阶段：记录 redo log，标记为 prepare 状态，同时记录 XID（事务 ID）
- 提交阶段：写入 binlog，然后提交事务，更新 redo log 为 commit 状态
组提交（Group Commit）机制
为了提高性能，MySQL 实现了组提交机制，将多个事务的 redo log 一起刷盘：
这大大减少了磁盘I/O操作，提高了吞吐量。
崩溃恢复机制
当MySQL崩溃重启时，会进行崩溃恢复：
- 扫描最后的redo log
- 检查每个prepare状态的事务
- 通过XID查找对应的binlog记录
- 如果找到完整binlog，则提交事务；否则回滚事务
例如，对于一个银行转账操作：
START TRANSACTION;
UPDATE accounts SET balance = balance - 1000 WHERE id = 'A';
UPDATE accounts SET balance = balance + 1000 WHERE id = 'B';
COMMIT;
崩溃恢复逻辑：
- 崩溃发生在 prepare 前：重启后没有 prepare 日志，事务自动回滚
- 崩溃发生在 prepare 后、写 binlog 前：重启后发现 redo log 为 prepare 但没有对应的 binlog，回滚事务
- 崩溃发生在 binlog 写完、commit 前：重启后发现 redo log 为 prepare 且存在对应的 binlog，自动提交事务
- 崩溃发生在 commit 后、数据页刷盘前：重启后通过 redo log 恢复未刷盘的数据修改
redo log 配置优化
生产环境的 redo log 配置建议：
# 计算公式：innodb_log_file_size = (TPS * 每事务redo量) * 缓冲时间
# 例如：TPS=1000，每事务1KB，缓冲10秒，则至少需要10MB
# redo log文件大小
innodb_log_file_size = 512M
# redo log文件组中的文件数量（通常2-4个）
innodb_log_files_in_group = 4
# 事务提交时的刷盘策略，生产环境建议设为1
innodb_flush_log_at_trx_commit = 1
undo log（撤销日志）
undo log 的作用
undo log 主要有两个作用：
- 支持事务回滚：记录数据修改前的值，当事务回滚时用于恢复原始数据
- 实现 MVCC（多版本并发控制）：通过保存数据的历史版本，允许不同事务看到同一数据的不同版本
undo log 的工作原理
当事务对数据进行修改时，InnoDB 会先记录修改前的数据到 undo log 中：
- INSERT 操作：记录如何删除这条记录
- DELETE 操作：记录如何恢复这条记录
- UPDATE 操作：记录如何将数据恢复到修改前的状态
这些日志记录保存在回滚段（Rollback Segment）中，支持事务的原子性。
undo log 的生命周期
undo log 的管理流程：
- 事务开始时，分配 undo log 空间
- 事务执行过程中，记录修改前的数据
- 事务提交后，undo log 不会立即删除（为 MVCC 服务）
- Purge 线程定期检查不再被任何事务视图使用的 undo log 并回收
可通过以下命令查看 Purge 线程状态：
SHOW ENGINE INNODB STATUS\G
在输出中查找"History list length"值，如果该值持续增长，说明 Purge 线程跟不上事务提交速度。
MVCC 实现原理详解
在 InnoDB 中，每行数据都有三个隐藏字段：
- DB_TRX_ID：创建或最后修改该行的事务 ID
- DB_ROLL_PTR：指向 undo log 的指针（回滚指针）
- DB_ROW_ID：如果表没有主键和唯一索引，InnoDB 会自动生成此 ID
不同事务隔离级别下，Read View（读视图）的创建时机不同：
- READ COMMITTED：每次 SELECT 都创建新的 Read View
- REPEATABLE READ：事务开始时创建 Read View 并重用
事务回滚与 MVCC 案例分析
事务回滚案例
START TRANSACTION;
DELETE FROM products WHERE id = 10;
-- 此时发现删错了
ROLLBACK;
MySQL 处理流程：
- 执行 DELETE 前，先将 id=10 的产品完整记录到 undo log
- 标记数据行为删除状态
- 执行 ROLLBACK 时，根据 undo log 恢复原始数据
MVCC 读取案例
假设有两个并发事务：
-- 事务A（事务ID=100）：更新商品价格
START TRANSACTION;
UPDATE products SET price = 200 WHERE id = 1;
-- 未提交
-- 同时事务B（事务ID=101）：查询商品
START TRANSACTION; -- 隔离级别为REPEATABLE READ
SELECT price FROM products WHERE id = 1;
-- 此时返回更新前的价格，如100
MVCC 处理流程：
- 事务 A 修改 id=1 的价格，将原价格 100 写入 undo log，更新当前值为 200
- 事务 B 开始，创建 Read View，记录当前活跃事务包括事务 A(100)
- 事务 B 查询 id=1 的价格，发现最新版本由事务 100 创建，不可见
- 顺着回滚指针找到 undo log 中的历史版本，发现可见
- 返回历史版本中的价格 100
undo log 性能监控与优化
长事务监控
-- 查看长时间运行的事务
SELECT trx_id, trx_started, trx_state,
trx_tables_in_use, trx_rows_modified,
trx_mysql_thread_id, trx_query
FROM information_schema.INNODB_TRX
WHERE trx_started < NOW() - INTERVAL 10 MINUTE
ORDER BY trx_started;
大事务优化建议
- 避免在一个事务中处理过多数据，将大事务拆分成多个小事务
- 对于大表删除操作，使用TRUNCATE TABLE替代DELETE FROM，因为前者不生成 undo log
- 避免长时间活跃的事务，特别是那些修改大量数据的事务
- 合理设置事务隔离级别，必要时可降级为 READ COMMITTED
-- 大批量删除数据的优化方案
START TRANSACTION;
DELETE FROM large_table WHERE id <= 10000 LIMIT 1000;
COMMIT;
-- 分批提交，每批处理1000行
日志系统架构对比
三种日志的核心区别
|
维度 |
Binlog |
Redo Log |
Undo Log |
|
所属层 |
服务层 |
InnoDB 引擎层 |
InnoDB 引擎层 |
|
日志类型 |
逻辑日志（SQL/行变更） |
物理日志（页修改） |
逻辑日志（数据旧版本） |
|
主要作用 |
主从复制、数据恢复 |
崩溃恢复、事务持久性 |
事务回滚、MVCC |
|
写入方式 |
追加写入，永不覆盖 |
循环写入，空间固定 |
回滚段管理 |
|
刷盘策略 |
sync_binlog 参数控制 |
innodb_flush_log_at_trx_commit |
随事务提交写入 |
|
空间管理 |
追加，需手动/自动清理 |
循环覆盖 |
回滚段自动回收 |
|
日志内容 |
DML/DDL 等修改操作 |
页级别的物理修改记录 |
行记录的旧值 |
三种日志与 ACID 的关系
- 原子性：由undo log保障，确保事务要么完全执行，要么完全不执行
- 一致性：由数据库完整性约束+binlog保障，确保数据符合预期规则
- 隔离性：由锁机制和MVCC（基于undo log）共同保障
- 持久性：由redo log保障，确保已提交事务的修改永久生效
协同工作流程
在一个典型的更新操作中，三种日志的协同工作过程：
实战案例：日志系统在故障恢复中的应用
案例一：误删数据库恢复
# 1. 假设误执行了DROP DATABASE important_db
# 2. 定位binlog中DROP DATABASE语句的位置
mysqlbinlog --base64-output=decode-rows -v /var/lib/mysql/mysql-bin.000123 | grep -A 10 "DROP DATABASE"
# 3. 创建新的数据库
CREATE DATABASE important_db_new;
# 4. 在从库上重放DROP DATABASE之前的所有操作
mysqlbinlog --stop-position=12345678 /var/lib/mysql/mysql-bin.000123 | mysql -u root -p important_db_new
# 5. 检查并恢复数据
案例二：大事务导致 undo log 暴涨
问题现象：系统响应变慢，磁盘空间急剧减少
排查过程：
-- 检查是否有大事务长时间运行
SELECT * FROM information_schema.INNODB_TRX
WHERE trx_rows_modified > 10000
ORDER BY trx_started;
-- 发现一个删除千万级数据的事务运行了2小时
解决方案：
- 立即提交或回滚该事务释放 undo 空间
- 改进应用代码，使用批量提交或 TRUNCATE 替代大 DELETE
- 监控设置，对大事务提前告警
日志系统性能优化建议
- binlog 优化：
- 选择合适的 binlog 格式：需要主从一致性选 ROW，需要性能且业务简单选 STATEMENT
- 合理设置 sync_binlog：关键业务设为 1，性能优先可设为 0 或 100-1000
- 定期清理旧 binlog：PURGE BINARY LOGS BEFORE '2023-11-01'
- redo log 优化：
- 设置足够大的 redo log 文件：通常总大小在 1-8GB
- 根据业务设置 flush 策略：金融类必须为 1，普通业务可设为 2 提升性能
- 在 RAID 卡有电池保护的服务器上，可安全设置为 2
- undo log 优化：
- 控制事务大小：单个事务修改行数最好控制在万级以内
- 合理设置隔离级别：非必要不使用 SERIALIZABLE
- 定期监控 History list length，正常应在几百到几千
总结
|
日志类型 |
核心用途 |
性能影响 |
数据安全保障 |
最佳实践 |
|
binlog |
主从复制 |
设为 sync_binlog=1 会降低 10-20%性能 |
防止服务器崩溃导致的数据丢失 |
根据复制需求选择格式 |
|
redo log |
事务持久性 |
设为 innodb_flush_log_at_trx_commit=1 会降低 15-20%性能 |
防止数据库崩溃导致的数据丢失 |
配置足够大的文件 |
|
undo log |
事务回滚 |
长事务会导致性能下降和空间占用 |
支持事务原子性和隔离性 |
避免大事务 |
扫一扫
1223
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
