xingliuhuaMySQL-08 日志系统
目录
MySQL 日志系统
目录
- 日志系统全景
- Redo Log（重做日志）
- Undo Log（回滚日志）
- Binlog（二进制日志）
- 两阶段提交（2PC）
- Crash Safe 机制
- 慢查询日志
- 错误日志与通用查询日志
- Relay Log（中继日志）
1. 日志系统全景
日志分类：
InnoDB 特有：
Redo Log ── 物理日志，持久性（D），WAL
Undo Log ── 逻辑日志，原子性（A）+ MVCC（I）
MySQL Server 层：
Binlog ── 逻辑日志，主从复制，备份恢复
Slow Query Log ── 慢查询分析
Error Log ── 启动/关闭/错误信息
General Query Log ── 所有SQL记录（一般不开）
Relay Log ── 主从复制中间日志（从库）
2. Redo Log（重做日志）
2.1 为什么需要 Redo Log
InnoDB 采用 Buffer Pool 缓存数据页，修改数据先在内存中进行（脏页）。如果每次修改都立即写磁盘（随机 I/O），性能极差。
解决方案：WAL（Write-Ahead Logging）
- 先将修改记录写入 Redo Log（顺序写，速度快）
- 修改数据页留在 Buffer Pool（异步，后台慢慢刷到磁盘）
- 即使 MySQL 崩溃，重启后通过 Redo Log 重放即可恢复
写操作流程：
修改 Buffer Pool 中的数据页（内存）
↓
写 Redo Log Buffer（内存）
↓（事务提交时）
Redo Log 刷盘（磁盘，顺序写）ib_logfile0/1
↓（异步，由 checkpoint 触发）
数据页刷盘（磁盘，随机写）.ibd 文件
2.2 Redo Log 文件
# 配置
innodb_log_file_size = 256M # 每个文件大小
innodb_log_files_in_group = 2 # 文件个数（默认2个，环形写入）
# 总大小 = 256M × 2 = 512M
# MySQL 8.0.30+ 改为单文件，动态调整
innodb_redo_log_capacity = 512M
环形写入机制：
write pos ────►
ib_logfile0 [──────────────────────]
ib_logfile1 [──────────────────────]
◄──── checkpoint pos
write pos：当前写入位置（向右移动）
checkpoint pos：已经刷到磁盘的数据页对应的日志位置（向右移动）
write pos 追上 checkpoint pos → Redo Log 写满 → 必须等待 checkpoint 推进
（这会导致写操作阻塞！需要设置足够大的 redo log）
2.3 Redo Log 的内容（物理日志）
Redo Log 记录的是物理操作：
"将表空间 X 的第 Y 页的第 Z 字节改为值 W"
优点：恢复时只需重放物理操作，无需理解业务逻辑，速度快。
2.4 Redo Log 刷盘策略
# innodb_flush_log_at_trx_commit（最重要的参数之一）
= 1 # 默认，最安全
# 每次事务提交都将 Redo Log Buffer 写入文件并 fsync 到磁盘
# 保证不丢失任何已提交事务
= 0 # 每秒写入文件并 fsync
# MySQL 崩溃可能丢失最近 1 秒的事务
= 2 # 每次提交写入文件（OS buffer），每秒 fsync
# MySQL 崩溃不丢数据，OS 崩溃可能丢失 1 秒数据
双1配置（金融级可靠性）：
innodb_flush_log_at_trx_commit=1+sync_binlog=1
-- 查看当前配置
SHOW VARIABLES LIKE 'innodb_flush_log_at_trx_commit';
SHOW VARIABLES LIKE 'sync_binlog';
2.5 LSN（Log Sequence Number）
-- 查看 LSN 信息
SHOW ENGINE INNODB STATUS\G
-- 找 LOG 部分：
-- Log sequence number: 当前 Redo Log 写到的位置
-- Log flushed up to: 已写入文件的位置
-- Last checkpoint at: checkpoint 的位置
3. Undo Log（回滚日志）
3.1 Undo Log 的作用
- 事务回滚：执行反向操作撤销修改
- MVCC：为其他事务提供历史数据版本（Read View 使用）
3.2 Undo Log 的内容（逻辑日志）
Undo Log 记录的是逻辑操作的反向：
INSERT id=1 → Undo: DELETE id=1
DELETE id=1 → Undo: INSERT id=1, val=...
UPDATE id=1 SET name='Bob' → Undo: UPDATE id=1 SET name='Alice'（旧值）
3.3 Undo Log 存储
-- 查看 Undo 表空间
SELECT * FROM information_schema.INNODB_TABLESPACES
WHERE NAME LIKE '%undo%';
-- Undo 表空间文件（MySQL 8.0+）
-- undo_001, undo_002
-- 默认在 innodb_undo_directory 中
SHOW VARIABLES LIKE 'innodb_undo%';
3.4 Undo Log 清理（Purge）
Update Undo Log（UPDATE/DELETE 产生的）不能立即删除，因为还需要支持 MVCC。
清理条件：当前没有任何活跃事务需要访问这个版本。
-- Purge 配置
SHOW VARIABLES LIKE 'innodb_purge%';
innodb_purge_threads = 4 -- Purge 线程数
innodb_max_purge_lag = 0 -- 允许的最大 Purge lag（Undo 版本数）
innodb_max_purge_lag_delay = 0 -- Purge lag 超过时，限制 DML 延迟（微秒）
4. Binlog（二进制日志）
4.1 Binlog 的作用
- 主从复制：从库通过重放 Binlog 同步数据
- 备份恢复：全量备份 + Binlog 增量恢复到任意时间点
- 数据订阅：Canal/Debezium 等工具解析 Binlog 实现数据同步
4.2 Binlog 三种格式
binlog_format = STATEMENT | ROW | MIXED
| 格式 | 记录内容 | 优点 | 缺点 |
|---|---|---|---|
| STATEMENT | SQL 语句原文 | 日志量小 | 函数/触发器可能主从不一致（NOW(), RAND() 等） |
| ROW | 每行数据的变化（前后镜像） | 精确，安全 | 日志量大（批量操作会产生大量日志） |
| MIXED | 自动选择：通常 STATEMENT，不安全时自动换 ROW | 均衡 | 较复杂 |
生产推荐：
ROW格式，最安全，配合 binlog_row_image=MINIMAL 减少日志量。
binlog_format = ROW
binlog_row_image = MINIMAL # 只记录修改的列（而非全部列）
# = FULL（默认，记录全部列）
# = NOBLOB（除 BLOB/TEXT 外的全部列）
4.3 Binlog 配置
log_bin = mysql-bin # 开启 binlog，指定文件名前缀
log_bin_index = mysql-bin.index # 索引文件
binlog_expire_logs_seconds = 604800 # 7天后过期（MySQL 8.0）
expire_logs_days = 7 # 7天后过期（MySQL 5.7）
max_binlog_size = 1G # 单个文件最大 1GB
sync_binlog = 1 # 每次提交都 fsync（最安全）
# = 0 由 OS 控制（性能好，有丢失风险）
# = N 每 N 次提交 fsync
4.4 Binlog 操作
-- 查看 Binlog 文件列表
SHOW BINARY LOGS;
SHOW MASTER LOGS;
-- 查看当前 Binlog 位置
SHOW MASTER STATUS;
-- 查看 Binlog 内容
SHOW BINLOG EVENTS IN 'mysql-bin.000001' FROM 4 LIMIT 10;
-- 用 mysqlbinlog 工具解析
mysqlbinlog mysql-bin.000001
mysqlbinlog --start-datetime='2024-01-15 10:00:00' \
--stop-datetime='2024-01-15 11:00:00' \
mysql-bin.000001
-- 手动切换 Binlog 文件（FLUSH LOGS 或 mysqladmin flush-logs）
FLUSH BINARY LOGS;
-- 删除 Binlog
PURGE BINARY LOGS TO 'mysql-bin.000010'; -- 删除到某个文件之前
PURGE BINARY LOGS BEFORE '2024-01-01 00:00:00'; -- 删除某时间之前
4.5 基于 Binlog 的数据恢复
# 场景：误操作 DELETE，需要恢复
# 1. 找到误操作前的最后一个全量备份
mysql -u root -p devdb < backup_20240115.sql
# 2. 找到 Binlog 中误操作的位置
mysqlbinlog --start-datetime='2024-01-15 09:00:00' mysql-bin.000001 | grep -n 'DELETE'
# 3. 重放误操作之前的 Binlog
mysqlbinlog --start-position=4 --stop-position=3456 mysql-bin.000001 | mysql -u root -p devdb
# 4. 跳过误操作，继续重放后续 Binlog
mysqlbinlog --start-position=3789 mysql-bin.000001 | mysql -u root -p devdb
5. 两阶段提交（2PC）
5.1 为什么需要两阶段提交
Redo Log 和 Binlog 是两个独立的日志系统，必须保证两者的一致性。
问题：如果先写 Redo Log 再写 Binlog（或相反），中间崩溃会导致不一致：
- 数据库通过 Redo Log 恢复了，但 Binlog 没有记录 → 从库/备份缺失这次修改
- Binlog 有记录，但 Redo Log 未完成 → 数据库没有这次修改，但从库/备份有
5.2 两阶段提交流程
事务提交过程：
1. Redo Log 写入 "prepare" 状态并刷盘
2. Binlog 写入并刷盘（sync_binlog=1）
3. Redo Log 更新为 "commit" 状态
崩溃恢复规则：
- Redo Log prepare + Binlog 完整 → 提交（继续）
- Redo Log prepare + Binlog 不完整 → 回滚
- Redo Log commit → 正常已提交
Executor InnoDB(Redo Log) Binlog
│ │ │
├─ 写数据页 ──►│ │
│ │ │
├──────────────►│ prepare │
│ │ │
├──────────────────────────────►│ write & fsync
│ │ │
├──────────────►│ commit │
│ │ │
5.3 组提交（Group Commit）
为了减少 fsync 的次数，MySQL 实现了组提交：多个事务的日志合并为一次 fsync。
Binlog 写入阶段（3个子步骤）：
flush stage: 将多个事务的 Binlog 从缓存写到文件
sync stage: 对一批 Binlog 执行一次 fsync（组提交关键！）
commit stage: 按顺序依次 commit
并发事务 T1, T2, T3 可以在 sync 阶段合并，一次 fsync 搞定
大幅减少磁盘 I/O，提升高并发写入性能
# 控制组提交行为
binlog_group_commit_sync_delay = 100 # 等待 100 微秒，积累更多事务
binlog_group_commit_sync_no_delay_count = 10 # 达到 10 个事务时不再等待
6. Crash Safe 机制
6.1 崩溃恢复流程
MySQL 启动时（崩溃后重启）：
1. InnoDB 扫描 Redo Log，找到所有未提交完成的事务
2. 对于每个 Redo Log 中的事务：
a. 状态为 commit → 数据已提交，重放 Redo Log（可能数据页未落盘）
b. 状态为 prepare → 检查 Binlog 是否有对应完整记录
- Binlog 完整 → 提交（重放 Redo Log）
- Binlog 不完整 → 回滚（执行 Undo Log）
3. 启动完成，数据库可用
6.2 Double Write Buffer（二次写）
解决**部分写（partial write）**问题：操作系统崩溃时，16KB 的数据页可能只写了一半（比如只写了 4KB 后断电），Redo Log 无法修复损坏的数据页（Redo Log 是幂等的增量，需要原始数据页完整）。
写数据页时：
1. 先将脏页写入 Double Write Buffer（顺序写，速度快）
2. 再将数据页写入 .ibd 文件
崩溃恢复时：
.ibd 文件中的数据页损坏？
→ 从 Double Write Buffer 中复制完整数据页
→ 重放 Redo Log
innodb_doublewrite = ON # 默认 ON（生产必须开）
7. 慢查询日志
7.1 配置开启
slow_query_log = ON
slow_query_log_file = /var/lib/mysql/slow.log
long_query_time = 1 # 超过 1 秒记录（建议 0.5~1s）
log_queries_not_using_indexes = ON # 未用索引的查询也记录
log_throttle_queries_not_using_indexes = 10 # 每分钟最多记录 10 条未用索引的查询
min_examined_row_limit = 100 # 扫描行数少于 100 行不记录
-- 动态开启（不需要重启）
SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 1;
SET GLOBAL log_queries_not_using_indexes = ON;
7.2 慢查询日志格式
# Time: 2024-01-15T10:30:00.123456Z
# User@Host: appuser[appuser] @ [10.0.0.1] Id: 1234
# Query_time: 2.345678 Lock_time: 0.000123 Rows_sent: 1 Rows_examined: 1000000
# Bytes_sent: 256
SET timestamp=1705289400;
SELECT * FROM orders WHERE user_id = 100 ORDER BY created_at DESC LIMIT 10;
关键字段：
Query_time：查询总耗时Lock_time：等待锁的时间Rows_sent：返回给客户端的行数Rows_examined：存储引擎扫描的行数（越大越需要优化）
7.3 pt-query-digest 分析
# 安装 Percona Toolkit
# macOS: brew install percona-toolkit
# CentOS: yum install percona-toolkit
# 分析慢查询日志
pt-query-digest /var/lib/mysql/slow.log
# 输出示例：
# Profile
# Rank Query ID Response time Calls R/Call V/M Item
# ==== =================================== ============= ===== ======= ===== =====
# 1 0xABC123... 100.0000 40.0% 500 0.2000 0.10 SELECT orders
# 2 0xDEF456... 80.0000 32.0% 200 0.4000 0.15 SELECT users
7.4 mysqldumpslow
# 内置工具
# 按查询时间排序，显示前 10 条
mysqldumpslow -s t -t 10 /var/lib/mysql/slow.log
# 按出现次数排序
mysqldumpslow -s c -t 10 /var/lib/mysql/slow.log
# 过滤包含特定字符串的查询
mysqldumpslow -g 'orders' /var/lib/mysql/slow.log
7.5 performance_schema 查慢 SQL
-- 不需要开慢查询日志，直接查 performance_schema
SELECT
DIGEST_TEXT,
COUNT_STAR AS exec_count,
ROUND(SUM_TIMER_WAIT / 1e12, 3) AS total_sec,
ROUND(AVG_TIMER_WAIT / 1e12, 3) AS avg_sec,
ROUND(MAX_TIMER_WAIT / 1e12, 3) AS max_sec,
SUM_ROWS_EXAMINED,
SUM_ROWS_SENT,
ROUND(SUM_ROWS_EXAMINED / COUNT_STAR, 0) AS avg_rows_examined
FROM performance_schema.events_statements_summary_by_digest
WHERE SCHEMA_NAME = 'myapp'
ORDER BY total_sec DESC
LIMIT 10;
8. 错误日志与通用查询日志
8.1 错误日志
log_error = /var/log/mysql/error.log
log_error_verbosity = 2 # 1=错误, 2=错误+警告, 3=错误+警告+注释
常见内容：
- MySQL 启动/关闭信息
- InnoDB 崩溃恢复信息
- 主从复制错误
- 表损坏信息
# 实时查看
tail -f /var/log/mysql/error.log
8.2 通用查询日志
记录所有 SQL（包括 SELECT），一般不开，性能影响大。
general_log = ON
general_log_file = /var/lib/mysql/general.log
使用场景：临时开启用于调试，排查某个时间段内执行了什么 SQL。
9. Relay Log（中继日志）
Relay Log 是主从复制中从库使用的日志，存储从主库 Binlog 复制过来的数据。
主库 Binlog ──[IO Thread]──► 从库 Relay Log ──[SQL Thread]──► 从库数据
-- 查看从库 Relay Log 状态
SHOW SLAVE STATUS\G
-- 关注：
-- Relay_Log_File: 当前 Relay Log 文件名
-- Relay_Log_Pos: 当前读取位置
-- Exec_Master_Log_Pos: 已执行到主库 Binlog 的哪个位置
-- Seconds_Behind_Master: 主从延迟（秒）
relay_log = /var/lib/mysql/relay-bin
relay_log_purge = ON # SQL Thread 应用后自动清理
relay_log_recovery = ON # 崩溃恢复：重新从主库获取（避免 Relay Log 损坏）
小结
| 日志 | 位置 | 类型 | 用途 | 格式 |
|---|---|---|---|---|
| Redo Log | InnoDB 引擎 | 物理 | 持久性，崩溃恢复 | 循环写，二进制 |
| Undo Log | InnoDB 引擎 | 逻辑 | 回滚，MVCC | 段文件 |
| Binlog | Server 层 | 逻辑 | 复制，备份恢复 | 追加写，二进制 |
| Slow Query | Server 层 | 文本 | 慢查询分析 | 文本追加 |
| Error Log | Server 层 | 文本 | 错误诊断 | 文本追加 |
| Relay Log | 从库 | 物理 | 主从复制中转 | 循环写 |
关键理解：
- WAL：Redo Log 的核心思想，顺序写 > 随机写
- 两阶段提交：Redo Log + Binlog 保证一致性
- 组提交：批量 fsync，提升写入吞吐量
- 慢查询日志是性能优化的第一入口