MySQL 日志文件
一、MySQL 日志体系概述
1.1 MySQL 有哪些日志
MySQL 共有 7 类核心日志，按职责分为两个层次：
MySQL 日志体系
│
├── Server 层日志（与存储引擎无关）
│ ├── 错误日志 (Error Log)
│ ├── 二进制日志 (Binlog)
│ ├── 慢查询日志 (Slow Query Log)
│ └── 通用查询日志 (General Query Log)
│
└── InnoDB 层日志（存储引擎特有）
├── 重做日志 (Redo Log)
├── 回滚日志 (Undo Log)
└── 中继日志 (Relay Log) — 主从复制用1.2 日志速览表
|
日志 |
所属层 |
记录内容 |
核心用途 |
默认状态 |
|---|---|---|---|---|
|
Error Log |
Server |
启动/关闭/错误信息 |
排障诊断 |
✅ 开启 |
|
Binlog |
Server |
所有数据变更 SQL（逻辑日志） |
主从复制、数据恢复 |
❌ 默认关闭 |
|
Slow Query Log |
Server |
超过阈值的慢 SQL |
性能调优 |
❌ 默认关闭 |
|
General Query Log |
Server |
所有客户端请求 |
审计/调试 |
❌ 默认关闭 |
|
Redo Log |
InnoDB |
物理数据页修改（物理日志） |
崩溃恢复 (crash-safe) |
✅ 必须开启 |
|
Undo Log |
InnoDB |
数据修改前的旧值 |
事务回滚、MVCC |
✅ 必须开启 |
|
Relay Log |
Server
|
从 Master 同步的 Binlog 副本 |
主从复制中转 |
从库开启 |
二、错误日志（Error Log）
2.1 是什么
记录 MySQL 服务进程的启动、运行、关闭过程中的所有错误和异常信息，以及告警和提示。
2.2 记录内容
- MySQL Server 启动/关闭事件
- 启动时检查表的报错（表损坏、修复失败）
- 运行中的关键错误（内存不足、磁盘满、文件权限）
- InnoDB 引擎的内部错误
- 主从复制中的 I/O 线程和 SQL 线程错误
2.3 查看与配置
-- 查看错误日志位置
SHOW VARIABLES LIKE 'log_error';
-- 查看错误日志级别（MySQL 8.0+）
SHOW VARIABLES LIKE 'log_error_verbosity';
-- 1 = 仅错误
-- 2 = 错误 + 告警
-- 3 = 错误 + 告警 + 提示（默认）# my.cnf 配置
[mysqld]
log_error = /var/log/mysql/error.log
log_error_verbosity = 32.4 应用场景
|
场景 |
说明 |
|---|---|
|
服务启动失败 |
MySQL 起不来时第一件事看 error log，会写明失败原因 |
|
表损坏检测 |
查询报错 |
|
主从复制异常 |
Slave I/O 线程断连、SQL 线程执行失败，error log 均有记录 |
|
死锁检测 |
InnoDB 检测到死锁时，会将死锁信息写入 error log |
|
磁盘空间告警 |
写入失败时 error log 会记录 |
2.5 实战示例
# 排查 MySQL 启动失败
tail -50 /var/log/mysql/error.log
# 常见内容示例
[ERROR] InnoDB: Unable to lock ./ibdata1 error: 11
# → 说明 ibdata1 被其他进程占用
[ERROR] Aborting
[Note] /usr/sbin/mysqld: Shutdown complete
# → 说"Shutdown complete"才是正常关闭，直接"Aborting"说明 crash 了三、二进制日志（Binlog）
3.1 是什么
Binlog 是 Server 层产生的逻辑日志，记录所有对数据库执行了实际修改的 SQL 语句（DDL、DML）。SELECT 和 SHOW 这类不修改数据的操作不会记录。
核心特点：
- 记录的是"逻辑操作"（如
UPDATE t SET c=1 WHERE id=2），不是物理页变更 - 事务提交前一次性写入（先写 Redo Log，事务提交时写 Binlog）
- 可以用于数据恢复和主从复制
3.2 三种记录格式
|
格式 |
记录内容 |
优点 |
缺点 |
|---|---|---|---|
|
STATEMENT |
原始 SQL 语句 |
日志量小 |
部分函数（UUID/NOW）在主从执行结果不一致 |
|
ROW（推荐） |
每行数据的具体变化 |
精确，不会出现不一致 |
日志量大（批量UPDATE会产生巨量日志） |
|
MIXED |
STATEMENT+ROW 混合 |
平衡 |
判断逻辑复杂，一般不用 |
-- 查看当前格式
SHOW VARIABLES LIKE 'binlog_format';
-- 查看 Binlog 状态
SHOW MASTER STATUS;3.3 查看 Binlog 内容
# 查看所有 binlog 文件列表
mysql -e "SHOW BINARY LOGS;"
# 查看指定 binlog 的事件
mysqlbinlog /var/lib/mysql/binlog.000001
# 按时间范围提取
mysqlbinlog --start-datetime="2026-05-20 10:00:00" \
--stop-datetime="2026-05-20 11:00:00" \
/var/lib/mysql/binlog.000001
# 按位置提取（用于 PITR 精确恢复）
mysqlbinlog --start-position=154 --stop-position=12345 binlog.000001
# 还原到数据库
mysqlbinlog binlog.000001 | mysql -u root -p3.4 Binlog 写入时机（两阶段提交）
1. 执行 UPDATE 语句
│
2. InnoDB 写 Redo Log（prepare 状态）
│
3. Server 写 Binlog
│
4. InnoDB 将 Redo Log 改为 commit 状态
│
5. 事务提交完成为什么需要两阶段提交？ 保证 Redo Log 和 Binlog 的一致性。如果在写 Binlog 之前崩溃，Redo 是 prepare 状态且没有对应的 Binlog → 回滚。如果写 Binlog 之后崩溃，Redo 是 prepare 状态且有对应的 Binlog → 提交。
3.5 配置
[mysqld]
# 开启 Binlog
log_bin = /var/lib/mysql/mysql-bin
# 格式（推荐 ROW）
binlog_format = ROW
# 单个文件最大大小（达到后自动切新文件）
max_binlog_size = 512M
# 保留天数（生产建议 7-30 天）
binlog_expire_logs_seconds = 6048003.6 应用场景
|
场景 |
说明 |
|---|---|
|
主从复制 |
Master 将 Binlog 发送给 Slave，Slave 的 I/O 线程接收并写入 Relay Log，SQL 线程重放 |
|
PITR 时间点恢复 |
最近全量备份 + 备份点之后的 Binlog → 恢复到任意时间点 |
|
数据审计 |
追踪谁在什么时候修改了什么数据 |
|
异构同步 |
Canal/Maxwell 解析 Binlog → 同步到 ES/Redis/Kafka/数据仓库 |
|
闪回恢复 |
解析 Binlog 生成反向 SQL，撤销误操作（如误 DELETE 恢复） |
四、慢查询日志（Slow Query Log）
4.1 是什么
记录执行时间超过指定阈值的 SQL 语句。是 SQL 性能调优的第一入口。
4.2 核心配置
[mysqld]
# 开启慢查询
slow_query_log = 1
# 日志文件路径
slow_query_log_file = /var/log/mysql/slow.log
# 阈值（单位：秒），超过即记录
long_query_time = 2
# 记录不使用索引的查询（建议开启，发现索引问题）
log_queries_not_using_indexes = 1
# 每分钟允许的未使用索引查询数量（防止刷屏）
log_throttle_queries_not_using_indexes = 10-- 在线设置
SET GLOBAL slow_query_log = 1;
SET GLOBAL long_query_time = 1;4.3 慢日志分析工具
# 1. mysqldumpslow（MySQL 自带）
mysqldumpslow -s t -t 10 /var/log/mysql/slow.log
# -s t：按总时间排序
# -t 10：显示前 10 条
# 2. pt-query-digest（Percona Toolkit，更强大推荐）
pt-query-digest /var/log/mysql/slow.log > slow_report.log
# 输出示例：
# Rank Response time Calls R/Call Query
# 1 150.00 50.0% 1000 0.150 SELECT * FROM orders WHERE ...
# 2 80.00 26.7% 200 0.400 SELECT u.*, o.* FROM users u JOIN ...4.4 应用场景
|
场景 |
说明 |
|---|---|
|
定位慢 SQL |
直接找到执行最慢、执行次数最多的语句 |
|
分析 SQL 趋势 |
每天跑一遍 pt-query-digest，发现新增慢 SQL |
|
优化验证 |
加索引前 vs 加索引后的执行时间对比 |
|
容量评估 |
从慢日志中分析数据量增长对 SQL 性能的影响 |
五、通用查询日志（General Query Log）
5.1 是什么
记录 MySQL 收到的每一个客户端请求，包括连接、断开、所有 SQL 语句（含 SELECT）。是最详细但也最占资源的日志。
5.2 配置
-- 查看状态
SHOW VARIABLES LIKE 'general_log%';
-- 开启（⚠️ 生产环境慎用，有巨大性能开销）
SET GLOBAL general_log = 1;
SET GLOBAL general_log_file = '/var/log/mysql/general.log';
-- 更推荐的方式：记录到表（方便查，但开销更大）
SET GLOBAL log_output = 'TABLE';
SET GLOBAL general_log = 1;
SELECT * FROM mysql.general_log LIMIT 10;5.3 应用场景
|
场景 |
说明 |
|---|---|
|
开发调试 |
开发环境开启，观察应用实际发送的 SQL |
|
安全审计 |
短暂开启，排查可疑操作 |
|
连接异常排查 |
频繁的连接/断开可以在 general log 中看到 |
5.4 为什么生产不能常开
- 记录每一条 SQL（包括 SELECT），高并发下日志写入量极大
- 磁盘 I/O 成为瓶颈
- 日志文件膨胀速度非常快
- 性能损耗 10%-30%
⚠️ 严格禁止生产环境常开。 只能临时开启用于排障，查完立刻关闭。
六、重做日志（Redo Log）
6.1 是什么
Redo Log 是 InnoDB 存储引擎层的物理日志，记录了"对某个数据页做了什么物理修改"。用于保证事务的持久性（Durability）——即使数据库崩溃，已提交事务的数据不会丢失。
6.2 核心原理
WAL（Write-Ahead Logging，预写日志）：
修改一条数据：
1. 先从磁盘读取数据页到 Buffer Pool（内存）
2. 修改 Buffer Pool 中的数据页
3. 将修改记录写入 Redo Log Buffer（内存）
4. 事务提交时，将 Redo Log Buffer 刷到 Redo Log 文件（磁盘）
5. 返回客户端"提交成功"
6. 后台线程慢慢将 Buffer Pool 的脏页刷回数据文件（磁盘）为什么这样设计？
写入 Redo Log：顺序写，很快
写入数据文件：随机写，很慢
如果每次提交都直接写数据文件 → 磁盘随机写 → 性能极差
WAL 的思路：先顺序写 Redo Log（快），再异步刷脏页（不影响事务响应）6.3 Redo Log 的结构
Redo Log 文件（固定大小，循环写入）：
┌─────────────────────────────────┐
│ ib_logfile0 ib_logfile1 │ ← 默认两个文件，大小相同
│ ┌──────────┐ ┌──────────┐ │
│ │ │ │ │ │
│ └──────────┘ └──────────┘ │
└─────────────────────────────────┘
write pos（写指针）：当前写入位置
checkpoint（检查点）：已刷到数据文件的位置
write pos 和 checkpoint 之间 = 空闲可用空间
write pos 追上 checkpoint → 不能再写入，必须等 checkpoint 推进[mysqld]
# Redo Log 文件大小（每个），默认 48MB
innodb_log_file_size = 512M
# Redo Log 文件数量
innodb_log_files_in_group = 2
# 总大小 = 512M × 2 = 1GB6.4 Redo Log 刷盘策略
|
参数值 |
策略 |
说明 |
|---|---|---|
|
0 |
每秒刷一次 |
性能最好，但崩溃可能丢 1 秒数据 |
|
1 |
每次提交都刷 |
默认且推荐，保证事务持久性 |
|
2 |
每次提交写 OS 缓存 |
数据库崩溃不丢，但 OS 崩溃可能丢 |
SHOW VARIABLES LIKE 'innodb_flush_log_at_trx_commit';
-- 默认值 1：每次提交都刷盘，保证 ACID 中的 D6.5 应用场景：崩溃恢复
数据库崩溃后重启：
1. InnoDB 扫描 Redo Log
2. 找到所有已提交但数据文件未同步的事务 → 重做（Redo）
3. 找到所有未提交的事务 → 交给 Undo Log 回滚（Undo）
4. 数据库恢复到崩溃前的一致状态📌 这就是为什么即使突然断电，已提交的数据也不会丢失——因为有 Redo Log。
七、回滚日志（Undo Log）
7.1 是什么
Undo Log 记录了数据修改前的旧值，用于事务回滚和 MVCC（多版本并发控制）。
7.2 核心原理
-- 假设 user 表中 id=1 的 name 原值是 "张三"
UPDATE user SET name = '李四' WHERE id = 1;Undo Log 记录：
id=1, name 修改前 = "张三" ← 这就是 Undo Log回滚时： 将 Undo Log 中的旧值写回数据页，数据恢复为"张三"。
7.3 Undo Log 的两个核心作用
|
作用 |
原理 |
|---|---|
|
事务回滚 |
执行 ROLLBACK 时，按 Undo Log 把数据恢复成修改前的样子 |
|
MVCC 读视图 |
其他事务读取时，如果数据已被修改但未提交，通过 Undo Log 找到符合自己可见性版本的旧数据 |
7.4 MVCC 与 Undo Log
事务 A (trx_id=100) 要读取 id=1 的数据
但 id=1 被事务 B (trx_id=101) 修改了且未提交
此时 InnoDB 怎么处理？
1. 数据页中 id=1 的当前值是事务 B 修改后的新值
2. 该行有一个 roll_pointer 指向 Undo Log
3. Undo Log 中存着旧值 + 旧的事务 ID
4. 读取时，沿着 Undo Log 版本链往前找
5. 找到事务 A 可见的版本 → 返回该版本的数据
这就是 MVCC 的"快照读"原理7.5 Undo Log 与 Redo Log 的关系
|
对比维度 |
Redo Log |
Undo Log |
|---|---|---|
|
记录内容 |
修改后的新值 |
修改前的旧值 |
|
作用 |
保证持久性（已提交不丢） |
保证原子性（未提交回滚）+ MVCC |
|
写入方式 |
顺序写 |
也是顺序写 |
|
崩溃恢复 |
重做（Redo） |
回滚未提交事务 |
|
清理时机 |
checkpoint 后覆盖 |
事务提交后不会立即删除，等待 Purge 线程清理 |
|
可否关闭 |
不可 |
不可 |
📌 一句话：Redo 保证"提交了的一定不丢"，Undo 保证"没提交的一定能回滚"。
八、中继日志（Relay Log）
8.1 是什么
Relay Log 只存在于从库，是从库 I/O 线程从 Master 复制过来的 Binlog 的本地副本。从库 SQL 线程读取 Relay Log 并重放其中的 SQL，实现数据同步。
8.2 主从复制流程
Master Slave
│ │
│ Dump 线程：读取 Binlog │
├──────── 发送 Binlog ──────────→ │
│ I/O 线程：接收 → 写入 Relay Log
│ │
│ SQL 线程：读取 Relay Log → 重放 SQL → 修改本地数据
│ │8.3 配置
# 从库配置
[mysqld]
server_id = 2
relay_log = /var/lib/mysql/relay-bin
relay_log_purge = 1 # SQL 线程执行完后自动删除8.4 Relay Log 与 Binlog 的区别
|
对比维度 |
Binlog |
Relay Log |
|---|---|---|
|
产生位置 |
Master（主库） |
Slave（从库） |
|
来源 |
用户执行的 SQL |
从 Master 的 Binlog 复制而来 |
|
内容格式 |
与 Binlog 格式一致 |
与 Master 的 Binlog 一致 |
|
清理方式 |
根据 |
SQL 线程执行后自动清理 |
|
是否可读 |
是（用 mysqlbinlog） |
是（用 mysqlbinlog） |
九、七种日志对比总结
|
日志 |
存储层 |
记录了什么 |
核心用途 |
生产环境 |
性能影响 |
|---|---|---|---|---|---|
|
Error Log |
Server |
错误/告警/启动信息 |
排障 |
✅ 常开 |
极小 |
|
Binlog |
Server |
数据修改 SQL（逻辑） |
主从复制 + 数据恢复 |
✅ 必须开 |
适中 |
|
Slow Query |
Server |
超过阈值的慢 SQL |
SQL 调优 |
✅ 建议开 |
小（仅记慢的） |
|
General Log |
Server |
所有请求（含SELECT） |
临时调试/审计 |
❌ 禁止常开 |
极大 |
|
Redo Log |
InnoDB |
数据页物理修改 |
崩溃恢复 |
✅ 强制 |
必须的代价 |
|
Undo Log |
InnoDB |
修改前的旧值 |
回滚 + MVCC |
✅ 强制 |
必须的代价 |
|
Relay Log |
Server |
Master Binlog 副本 |
主从同步 |
从库需开 |
适中 |
各日志的"黄金搭档"
|
你要做的事 |
用到哪些日志 |
|---|---|
|
MySQL 起不来 |
Error Log |
|
主从复制 |
Binlog + Relay Log |
|
误 DELETE 恢复数据 |
Binlog（mysqlbinlog 解析回滚） |
|
SQL 跑得慢 |
Slow Query Log |
|
停电后数据不丢 |
Redo Log + Undo Log（自动的，无需手动操作） |
|
临时看应用发了哪些 SQL |
General Log（查完立刻关） |
|
数据同步到 ES |
Binlog（Canal 解析） |
|
生产环境日常 |
Error Log + Binlog + Slow Query + Redo + Undo |
十、生产环境日志最佳实践
# my.cnf 生产推荐配置
# ========== Error Log ==========
log_error = /var/log/mysql/error.log
log_error_verbosity = 2
# ========== Binlog ==========
log_bin = /data/mysql/binlog/mysql-bin
binlog_format = ROW
max_binlog_size = 512M
binlog_expire_logs_seconds = 1209600 # 14天
sync_binlog = 1 # 每次提交刷盘
# ========== Slow Query ==========
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 1 # 1秒
log_queries_not_using_indexes = 1
log_throttle_queries_not_using_indexes = 10
# ========== Redo Log ==========
innodb_log_file_size = 1G
innodb_log_files_in_group = 3 # 总大小 3GB
innodb_flush_log_at_trx_commit = 1 # 持久性优先
# ========== General Log ==========
general_log = 0 # 永远关着