MySQL 运维日志实战：四类核心日志从入门到精通

这是一篇一线 DBA 的实战工作日志，记录我这些年处理过的真实生产故障。如果你刚接手 MySQL 运维，这篇文章能帮你少走三年弯路。

一、凌晨三点的报警：从一个真实故障说起

先还原一个场景，相信很多 DBA 都经历过。

凌晨 3:17，手机疯狂震动，监控平台报警：生产 MySQL 主库实例 Down。

你睡眼惺忪地登录服务器，执行 systemctl status mysqld，发现服务已经挂了。你尝试重启，服务起来了，但不到 10 分钟又挂了。这时候老板的电话也打过来了："订单系统全部报错，什么时候能恢复？"

这时候你脑子里必须立刻蹦出一个问题：MySQL 到底留下了什么线索？

答案就是日志。MySQL 有四大核心日志，每一类都对应不同的运维场景：

日志类型	解决什么问题	一句话概括
错误日志 (Error Log)	实例为什么挂了、为什么启动失败	MySQL 的"黑匣子"
二进制日志 (Binlog)	主从复制、数据恢复、误删闪回	MySQL 的"录像机"
慢查询日志 (Slow Query Log)	哪些 SQL 拖慢了系统	MySQL 的"体检报告"
查询日志 (General Query Log)	谁在什么时候干了什么	MySQL 的"监控摄像头"

回到上面的故障，我打开错误日志，看到这样一行：

[ERROR] InnoDB: Corrupted page [page id: space=0, page number=24576] of datafile './ibdata1' could not be found in the doublewrite buffer.

真相大白：磁盘故障导致 InnoDB 数据页损坏，双写缓冲恢复失败，实例崩溃。最终通过 binlog + 全量备份恢复了数据。

这就是日志的价值——它不会说谎，只要你看得懂。

接下来，我把四类日志的完整运维知识掰开揉碎讲给你听。

二、错误日志（Error Log）：MySQL 的"黑匣子"

2.1 它解决什么生产问题？

错误日志是 MySQL 实例的"生死簿"，记录的内容包括：

实例启动/关闭的完整过程：启动失败、端口被占用、参数配置错误
运行时的严重错误：InnoDB 崩溃、表损坏、磁盘满、连接数打满
主从复制中断的报错：复制线程停止的具体原因
自动崩溃恢复的信息：InnoDB 崩溃恢复进度

一句话定位：凡是 MySQL 起不来、莫名其妙挂了、复制断了，第一时间看错误日志。

2.2 生产环境标准配置

打开 my.cnf（Linux）或 my.ini（Windows），在 [mysqld] 段添加：

ini

[mysqld]
# 错误日志文件路径，必须显式指定，不要依赖默认值
log_error = /data/mysql/logs/error/mysql-error.log

# 日志详细程度：3 最详细，2 推荐，1 最少
# 生产环境推荐 2，兼顾信息量和日志体积
log_error_verbosity = 2

# 记录废弃的警告信息（如废弃的 SQL 语法），生产环境建议开启
log_error_suppression_list = ''

# 记录连接相关的警告（如连接被拒绝），排查问题时有用
log_connections = 1

配置生效方式：

bash

# 修改配置文件后需要重启生效
systemctl restart mysqld

# 验证配置是否生效
mysql -u root -p -e "SHOW VARIABLES LIKE 'log_error%';"

2.3 存储路径规划：别把鸡蛋放在一个篮子里

默认路径的坑：很多 MySQL 默认把错误日志放在 /var/log/mysqld.log 或数据目录下。如果系统盘只有 50G，日志写满后整个系统都会受影响。

生产推荐方案：

/data/mysql/logs/          # 独立挂载的磁盘分区
├── error/
│   └── mysql-error.log
├── binlog/
│   ├── mysql-bin.000001
│   └── mysql-bin.000002
├── slow/
│   └── mysql-slow.log
└── general/
    └── mysql-general.log

挂载独立分区：

bash

# 创建独立分区并挂载
mkdir -p /data/mysql/logs
mount /dev/sdb1 /data/mysql/logs

# 写入 /etc/fstab 保证重启自动挂载
echo '/dev/sdb1 /data/mysql/logs ext4 defaults,noatime 0 0' >> /etc/fstab

# 设置权限
chown -R mysql:mysql /data/mysql/logs
chmod 750 /data/mysql/logs

2.4 日志内容结构拆解

来看一段生产环境真实的错误日志：

2024-03-15T03:17:42.123456+08:00 0 [Note] [MY-011966] [Server] Starting MySQL Server...
2024-03-15T03:17:42.234567+08:00 0 [Note] [MY-011902] [InnoDB] InnoDB initialization has started.
2024-03-15T03:17:42.345678+08:00 0 [ERROR] [MY-011906] [InnoDB] Corrupted page [page id: space=0, page number=24576] of datafile './ibdata1' could not be found in the doublewrite buffer.
2024-03-15T03:17:42.345679+08:00 0 [ERROR] [MY-011972] [InnoDB] InnoDB Database recovery failed with error 18.
2024-03-15T03:17:42.345680+08:00 0 [ERROR] [MY-010119] [Server] Aborting

逐字段拆解：

字段	示例	含义
时间戳	`2024-03-15T03:17:42.123456+08:00`	精确到微秒，带时区
线程 ID	`0`	产生日志的线程，`0` 表示主线程
级别	`[Note]` / `[ERROR]` / `[Warning]`	日志严重程度
错误代码	`[MY-011906]`	MySQL 内部错误码，可官方文档查询
组件	`[Server]` / `[InnoDB]`	哪个模块产生的日志
具体内容	`Corrupted page...`	错误描述，排查的核心

常见关键错误速查：

# 启动失败：端口被占用
[ERROR] [MY-010262] [Server] Can't start server: Bind on TCP/IP port: Address already in use

# 表损坏
[ERROR] [MY-012639] [InnoDB] Tablespace 12345, page 45678, corrupted

# 连接数打满
[ERROR] [MY-010289] [Server] Too many connections

# 磁盘满
[ERROR] [MY-012639] [InnoDB] Write to file ./ibdata1 failed at offset 123456. 1048576 bytes should have been written, only 0 bytes were written. Operating system error number 28 (No space left on device).

2.5 日常运维常用命令

bash

# 1. 查看错误日志位置（MySQL 内部）
mysql -u root -p -e "SHOW VARIABLES LIKE 'log_error';"

# 2. 实时跟踪错误日志（排查问题时）
tail -f /data/mysql/logs/error/mysql-error.log

# 3. 搜索特定时间段的错误
grep "2024-03-15T03" /data/mysql/logs/error/mysql-error.log | grep ERROR

# 4. 统计错误类型分布
grep "ERROR" /data/mysql/logs/error/mysql-error.log | awk -F'[][]' '{print $4}' | sort | uniq -c | sort -rn

# 5. 清空错误日志（谨慎操作，建议先备份）
> /data/mysql/logs/error/mysql-error.log

2.6 logrotate 轮转配置

错误日志会无限增长，必须配置轮转。创建 /etc/logrotate.d/mysql-error：

bash

/data/mysql/logs/error/mysql-error.log {
    # 每天轮转一次
    daily

    # 保留最近 30 天的日志
    rotate 30

    # 轮转后的日志压缩存储
    compress

    # 延迟压缩：上一次轮转的文件不压缩，方便查看
    delaycompress

    # 如果日志文件不存在，不报错
    missingok

    # 如果日志文件为空，不轮转
    notifempty

    # 创建新日志文件，权限和属主与原来一致
    create 640 mysql mysql

    # 轮转前执行的脚本：备份到远程
    prerotate
        /usr/bin/rsync -av /data/mysql/logs/error/mysql-error.log backup-server:/backup/mysql-logs/$(hostname)/ 2>/dev/null || true
    endscript

    # 轮转后执行的脚本：通知 MySQL 重新打开日志文件
    postrotate
        /usr/bin/mysqladmin -u root -p'your_password' flush-logs error-log 2>/dev/null || true
    endscript
}

验证配置：

bash

# 测试 logrotate 配置是否正确
logrotate -d /etc/logrotate.d/mysql-error

# 强制执行一次轮转（测试用）
logrotate -f /etc/logrotate.d/mysql-error

三、二进制日志（Binlog）：MySQL 的"录像机"

3.1 它解决什么生产问题？

Binlog 是 MySQL 最重要的日志，没有之一。它记录所有修改数据的 SQL 语句（DDL + DML），核心用途：

场景	说明
主从复制	从库通过读取主库的 binlog 实现数据同步
数据恢复	全量备份 + binlog 增量恢复，将数据恢复到任意时间点
误删闪回	通过解析 binlog 生成反向 SQL，恢复误删的数据
数据订阅	Canal、Debezium 等工具基于 binlog 做数据同步

一句话定位：只要涉及"数据能不能找回来"，就必须依赖 binlog。

3.2 生产环境标准配置

ini

[mysqld]
# 开启 binlog，指定文件前缀
log_bin = /data/mysql/logs/binlog/mysql-bin

# binlog 格式：ROW 模式（生产必选）
# STATEMENT：记录 SQL 语句，有主从不一致风险
# MIXED：混合模式，不够可靠
# ROW：记录每行数据的变更，最安全
binlog_format = ROW

# binlog 过期时间：秒，默认 30 天（2592000）
# 生产建议 7-15 天，根据磁盘空间和备份策略调整
binlog_expire_logs_seconds = 604800  # 7 天

# 单个 binlog 文件大小：默认 1G
# 建议保持默认，太大恢复时扫描慢，太小文件太多
max_binlog_size = 1073741824

# binlog 缓存大小：事务未提交时先写缓存
# 大事务场景建议调大，避免频繁刷盘
binlog_cache_size = 32768  # 默认 32K，建议 1M-4M

# 每个事务的 binlog 缓存上限
binlog_stmt_cache_size = 32768

# 同步策略：每次提交都刷盘，最安全
# 1：每次提交刷盘（推荐）
# 0：由操作系统决定，性能最好但可能丢数据
# N：每 N 次提交刷一次
sync_binlog = 1

# binlog 校验：确保 binlog 完整性
binlog_checksum = CRC32

# 记录 binlog 的 server_id，主从架构必须唯一
server_id = 1001

# 开启 GTID（推荐），方便主从切换和故障恢复
gtid_mode = ON
enforce_gtid_consistency = ON

配置生效：

bash

# 修改后重启生效
systemctl restart mysqld

# 验证
mysql -u root -p -e "SHOW VARIABLES LIKE 'binlog%';"
mysql -u root -p -e "SHOW VARIABLES LIKE 'gtid%';"

3.3 存储路径规划

Binlog 是 I/O 密集型，必须和数据文件分开存储：

ini

# 数据文件放在高性能 SSD
datadir = /data/mysql/data

# binlog 放在另一块磁盘，避免和数据文件争 I/O
log_bin = /data/mysql/logs/binlog/mysql-bin

磁盘空间预估：

假设：每天写入 100GB 数据，保留 7 天 binlog
需要空间 = 100GB × 7 = 700GB
建议预留 1TB 的 binlog 分区

3.4 日志内容结构拆解

Binlog 是二进制格式，不能直接查看，需要用 mysqlbinlog 工具解析。

查看 binlog 文件列表：

bash

mysql -u root -p -e "SHOW BINARY LOGS;"
# 输出：
# +------------------+-----------+-----------+
# | Log_name         | File_size | Encrypted |
# +------------------+-----------+-----------+
# | mysql-bin.000001 |      1256 | No        |
# | mysql-bin.000002 |   5242880 | No        |
# +------------------+-----------+-----------+

解析 binlog 内容：

bash

mysqlbinlog --base64-output=DECODE-ROWS -v /data/mysql/logs/binlog/mysql-bin.000002

一段典型的 binlog 解析结果：

# at 1234
#240315 10:23:45 server id 1001  end_log_pos 1456 CRC32 0x12345678
#   Query   thread_id=123   exec_time=0   error_code=0
SET TIMESTAMP=1710476625/*!*/;
BEGIN
/*!*/;
# at 1456
#240315 10:23:45 server id 1001  end_log_pos 1567 CRC32 0x87654321
#   Table_map: `test`.`users` mapped to number 108
# at 1567
#240315 10:23:45 server id 1001  end_log_pos 1789 CRC32 0xabcdef12
#   Update_rows: table id 108 flags: STMT_END_F

### UPDATE `test`.`users`
### WHERE
###   @1=1001 /* INT meta=0 nullable=0 is_null=0 */
###   @2='张三' /* VARSTRING(192) meta=192 nullable=0 is_null=0 */
###   @3='zhangsan@example.com' /* VARSTRING(192) meta=192 nullable=0 is_null=0 */
### SET
###   @1=1001 /* INT meta=0 nullable=0 is_null=0 */
###   @2='张三' /* VARSTRING(192) meta=192 nullable=0 is_null=0 */
###   @3='zhangsan_new@example.com' /* VARSTRING(192) meta=192 nullable=0 is_null=0 */
# at 1789
#240315 10:23:45 server id 1001  end_log_pos 1820 CRC32 0x1234abcd
#   Xid = 123456
COMMIT/*!*/;

关键字段解读：

标记	含义
`# at 1234`	事件在 binlog 文件中的起始位置（position）
`#240315 10:23:45`	事件时间：24年3月15日 10:23:45
`server id 1001`	产生该事件的 MySQL 实例 ID
`end_log_pos 1456`	事件结束位置，下一个事件从这里开始
`Query` / `Table_map` / `Update_rows` / `Xid`	事件类型
`### UPDATE ...`	ROW 模式下解码后的 SQL，可读性最强

3.5 日常运维常用命令

bash

# 1. 查看当前正在使用的 binlog 文件
mysql -u root -p -e "SHOW MASTER STATUS\G"

# 2. 查看 binlog 文件列表
mysql -u root -p -e "SHOW BINARY LOGS;"

# 3. 切换新的 binlog 文件（Flush 日志）
mysql -u root -p -e "FLUSH LOGS;"
# 或
mysqladmin -u root -p flush-logs

# 4. 查看 binlog 事件（不解析内容，只看元信息）
mysql -u root -p -e "SHOW BINLOG EVENTS IN 'mysql-bin.000002';"

# 5. 解析 binlog 为 SQL 语句（最常用）
mysqlbinlog --base64-output=DECODE-ROWS -v /data/mysql/logs/binlog/mysql-bin.000002 > binlog.sql

# 6. 按时间范围解析（用于时间点恢复）
mysqlbinlog --start-datetime="2024-03-15 10:00:00" \
            --stop-datetime="2024-03-15 11:00:00" \
            /data/mysql/logs/binlog/mysql-bin.000002 > recovery.sql

# 7. 按 position 范围解析
mysqlbinlog --start-position=1234 --stop-position=1789 \
            /data/mysql/logs/binlog/mysql-bin.000002 > partial.sql

# 8. 临时关闭 binlog（执行大批量操作时，提升性能）
mysql -u root -p -e "SET SQL_LOG_BIN = 0;"
# 执行大批量操作...
mysql -u root -p -e "SET SQL_LOG_BIN = 1;"
# 注意：只在当前会话有效，不影响其他会话

# 9. 手动清理过期 binlog（谨慎！）
mysql -u root -p -e "PURGE BINARY LOGS BEFORE '2024-03-08 00:00:00';"
# 或按文件名清理
mysql -u root -p -e "PURGE BINARY LOGS TO 'mysql-bin.000010';"

# 10. 查看 binlog 使用空间
du -sh /data/mysql/logs/binlog/

3.6 logrotate 轮转配置

Binlog 通常由 MySQL 自己管理（通过 expire_logs_days 或 binlog_expire_logs_seconds），但也可以配合 logrotate 做额外保护。创建 /etc/logrotate.d/mysql-binlog：

bash

/data/mysql/logs/binlog/mysql-bin.* {
    # 每小时检查一次（binlog 增长快）
    hourly

    # 保留 7 天
    rotate 168

    # 压缩
    compress
    delaycompress

    # 不处理 .index 文件
    # 注意：binlog 的轮转主要由 MySQL 控制，这里只是兜底

    missingok
    notifempty

    # 轮转前备份到远程
    prerotate
        /usr/bin/rsync -av /data/mysql/logs/binlog/ backup-server:/backup/mysql-binlogs/$(hostname)/ 2>/dev/null || true
    endscript
}

重要提醒：binlog 的清理优先依赖 MySQL 自身的 binlog_expire_logs_seconds，logrotate 只是兜底方案。不要手动删除 binlog 文件，否则 .index 文件会不一致，导致 MySQL 启动失败。

四、查询日志（General Query Log）：MySQL 的"监控摄像头"

4.1 它解决什么生产问题？

查询日志记录所有到达 MySQL 的 SQL 语句，包括连接、断开、查询、错误等。用途：

临时排查"到底是谁在发这个 SQL"
审计：记录所有数据库操作
复现问题：拿到完整 SQL 序列，在测试环境重放

一句话定位：只在需要"监控所有 SQL"时临时开启，用完立即关闭。

4.2 生产环境标准配置

ini

[mysqld]
# 开启查询日志
general_log = 1

# 日志文件路径
general_log_file = /data/mysql/logs/general/mysql-general.log

# 日志输出方式：FILE / TABLE / NONE
# FILE：输出到文件（推荐）
# TABLE：输出到 mysql.general_log 表，方便查询但性能差
# NONE：关闭
log_output = FILE

生产环境建议：默认关闭，只在排查问题时临时开启。

4.3 日志内容结构拆解

2024-03-15T10:30:45.123456+08:00    12345 Connect   root@localhost on test using TCP/IP
2024-03-15T10:30:45.234567+08:00    12345 Query     SELECT * FROM users WHERE id = 1001
2024-03-15T10:30:45.345678+08:00    12345 Query     UPDATE users SET name = '李四' WHERE id = 1001
2024-03-15T10:30:45.456789+08:00    12345 Quit

字段解读：

字段	含义
`2024-03-15T10:30:45.123456+08:00`	时间戳
`12345`	线程 ID（Thread ID）
`Connect` / `Query` / `Quit` / `Prepare` / `Execute`	命令类型
`root@localhost on test using TCP/IP`	连接信息：用户@主机 on 数据库协议

4.4 日常运维常用命令

bash

# 1. 查看查询日志状态
mysql -u root -p -e "SHOW VARIABLES LIKE 'general%';"

# 2. 临时开启查询日志（无需重启）
mysql -u root -p -e "SET GLOBAL general_log = 'ON';"

# 3. 临时关闭查询日志
mysql -u root -p -e "SET GLOBAL general_log = 'OFF';"

# 4. 查看查询日志文件位置
mysql -u root -p -e "SHOW VARIABLES LIKE 'general_log_file';"

# 5. 实时监控查询日志（排查问题时）
tail -f /data/mysql/logs/general/mysql-general.log

# 6. 按线程 ID 过滤（追踪特定连接）
grep "12345" /data/mysql/logs/general/mysql-general.log

# 7. 统计 SQL 类型分布
awk '{print $4}' /data/mysql/logs/general/mysql-general.log | sort | uniq -c | sort -rn

4.5 logrotate 轮转配置

查询日志增长极快，必须严格轮转。创建 /etc/logrotate.d/mysql-general：

bash

/data/mysql/logs/general/mysql-general.log {
    # 每小时轮转
    hourly

    # 只保留 24 小时
    rotate 24

    compress
    delaycompress
    missingok
    notifempty
    create 640 mysql mysql

    postrotate
        /usr/bin/mysqladmin -u root -p'your_password' flush-logs general-log 2>/dev/null || true
    endscript
}

五、慢查询日志（Slow Query Log）：MySQL 的"体检报告"

5.1 它解决什么生产问题？

慢查询日志记录执行时间超过阈值的 SQL 语句，是性能优化的第一入口：

找出执行慢的 SQL，针对性加索引或改写
发现全表扫描的语句
发现锁等待时间长的语句
定期分析，建立慢 SQL 基线

一句话定位：系统变慢了、某个页面加载超时，先开慢查询日志看看。

5.2 生产环境标准配置

ini

[mysqld]
# 开启慢查询日志
slow_query_log = 1

# 日志文件路径
slow_query_log_file = /data/mysql/logs/slow/mysql-slow.log

# 慢查询阈值：秒，默认 10 秒
# 生产环境建议 1-2 秒，核心业务建议 0.5 秒
long_query_time = 1

# 是否记录未使用索引的查询（即使没超时）
# 强烈建议开启，能发现很多潜在问题
log_queries_not_using_indexes = 1

# 是否记录管理语句（如 ALTER TABLE、ANALYZE TABLE）
log_slow_admin_statements = 1

# 是否记录从库上的慢查询（主从架构下从库排查）
log_slow_slave_statements = 1

# 每分钟允许记录的未使用索引的查询数（防止日志爆炸）
# 0 表示不限制，建议设置一个上限
log_throttle_queries_not_using_indexes = 60

# 慢查询日志输出方式
log_output = FILE

# 记录锁等待时间（需要 performance_schema 支持）
# MySQL 8.0 默认开启

5.3 日志内容结构拆解

bash

# Time: 2024-03-15T10:35:22.123456+08:00
# User@Host: app_user[app_user] @ web-server-01 [10.0.1.100]  Id: 12345
# Query_time: 12.345678  Lock_time: 0.000123  Rows_sent: 1  Rows_examined: 5000000
# Rows_affected: 0  Bytes_sent: 1234
SET timestamp=1710477322;
SELECT * FROM orders WHERE user_id = 12345 AND status = 'pending' ORDER BY create_time DESC LIMIT 20;

逐行解读：

行	字段	含义
第1行	`Time`	查询执行时间
第2行	`User@Host`	执行用户、来源主机、线程 ID
第3行	`Query_time`	查询执行总时间（秒），最重要指标
第3行	`Lock_time`	锁等待时间（秒）
第3行	`Rows_sent`	返回给客户端的行数
第3行	`Rows_examined`	扫描的行数，判断是否需要加索引的关键
第4行	`Rows_affected`	影响的行数（INSERT/UPDATE/DELETE）
第4行	`Bytes_sent`	发送给客户端的字节数
第5行	`SET timestamp`	SQL 执行时的 Unix 时间戳
第6行	`SELECT ...`	慢 SQL 语句本体

关键判断标准：

Rows_examined 远大于 Rows_sent：说明做了大量无用扫描，需要加索引
Lock_time 很大：说明有锁竞争，需要优化事务或隔离级别
Query_time 很大但 Rows_examined 很小：可能是磁盘 I/O 问题或锁等待

5.4 日常运维常用命令

bash

# 1. 查看慢查询日志状态
mysql -u root -p -e "SHOW VARIABLES LIKE 'slow%';"
mysql -u root -p -e "SHOW VARIABLES LIKE 'long_query_time';"

# 2. 临时修改慢查询阈值（无需重启）
mysql -u root -p -e "SET GLOBAL long_query_time = 0.5;"

# 3. 查看慢查询日志文件位置
mysql -u root -p -e "SHOW VARIABLES LIKE 'slow_query_log_file';"

# 4. 使用 mysqldumpslow 分析慢查询（MySQL 自带工具）
# 按查询时间排序，取前 10 条
mysqldumpslow -s t -t 10 /data/mysql/logs/slow/mysql-slow.log

# 按扫描行数排序
mysqldumpslow -s r -t 10 /data/mysql/logs/slow/mysql-slow.log

# 按出现次数排序
mysqldumpslow -s c -t 10 /data/mysql/logs/slow/mysql-slow.log

# 只看 SELECT 语句
mysqldumpslow -s t -t 10 -g "SELECT" /data/mysql/logs/slow/mysql-slow.log

# 5. 使用 pt-query-digest（Percona Toolkit，更强大）
# 安装：yum install percona-toolkit 或 apt install percona-toolkit

# 生成完整分析报告
pt-query-digest /data/mysql/logs/slow/mysql-slow.log > slow_report.txt

# 只看最近 1 小时的慢查询
pt-query-digest --since=1h /data/mysql/logs/slow/mysql-slow.log

# 按用户分组统计
pt-query-digest --group-by=user /data/mysql/logs/slow/mysql-slow.log

# 6. 切割慢查询日志（手动轮转）
mv /data/mysql/logs/slow/mysql-slow.log /data/mysql/logs/slow/mysql-slow.log.$(date +%Y%m%d%H%M%S)
mysqladmin -u root -p flush-logs slow-log

5.5 logrotate 轮转配置

创建 /etc/logrotate.d/mysql-slow：

bash

/data/mysql/logs/slow/mysql-slow.log {
    # 每天轮转
    daily

    # 保留 30 天
    rotate 30

    compress
    delaycompress
    missingok
    notifempty
    create 640 mysql mysql

    # 轮转前用 pt-query-digest 生成日报
    prerotate
        /usr/bin/pt-query-digest /data/mysql/logs/slow/mysql-slow.log > /data/mysql/logs/slow/daily-report-$(date +\%Y\%m\%d).txt 2>/dev/null || true
    endscript

    postrotate
        /usr/bin/mysqladmin -u root -p'your_password' flush-logs slow-log 2>/dev/null || true
    endscript
}

六、运维避坑指南：血泪教训总结

6.1 查询日志（General Log）

坑	后果	正确做法
长期开启	磁盘 I/O 暴涨 300%，日志每天几百 GB，系统卡死	只在排查问题时临时开启，用完立即关闭
写入 TABLE 模式	每次查询都要写 mysql.general_log 表，性能雪崩	只用 FILE 模式，或干脆不开
不清理	几天就写满磁盘	必须配置 logrotate，且保留时间不超过 24 小时

真实案例：某电商大促前，运维为了排查问题开了 general log，忘记关闭。大促当天 MySQL 实例 I/O 打满，订单系统瘫痪 40 分钟，直接损失百万。

6.2 二进制日志（Binlog）

坑	后果	正确做法
`expire_logs_days` 设太短	从库还没同步完，主库 binlog 被删了，复制中断	保留时间必须大于从库最大延迟 + 备份周期
手动删除 binlog 文件	`.index` 文件不一致，MySQL 启动失败	只用 `PURGE BINARY LOGS` 命令
`sync_binlog = 0`	实例崩溃时丢失已提交的事务	生产环境必须 `sync_binlog = 1`
binlog 和数据文件同盘	I/O 争用，写入延迟飙升	独立磁盘或独立分区

从库延迟保护脚本：

bash

#!/bin/bash
# 在从库上执行，检查延迟，如果延迟超过阈值则告警

DELAY=$(mysql -u root -p'password' -e "SHOW SLAVE STATUS\G" | grep Seconds_Behind_Master | awk '{print $2}')

if [ "$DELAY" -gt 3600 ]; then
    echo "从库延迟超过 1 小时：${DELAY} 秒，禁止清理主库 binlog" | mail -s "MySQL 复制延迟告警" dba@company.com
fi

6.3 慢查询日志

坑	后果	正确做法
阈值设 10 秒	只有极慢的 SQL 被记录，大部分问题 SQL 漏掉	根据业务设 0.5-2 秒，核心系统建议 0.1-0.5 秒
阈值设 0.001 秒	日志爆炸，一天几百 GB，失去分析价值	循序渐进，从 2 秒开始，逐步下调
不看 `Rows_examined`	只看执行时间，忽略扫描行数，优化方向错误	重点关注 `Rows_examined / Rows_sent` 比值
不分析直接优化	盲目加索引，写入性能下降	先用 `pt-query-digest` 分析，找出 TOP SQL 再优化

阈值设置参考：

业务类型	建议阈值
核心交易（支付、订单）	0.1 - 0.5 秒
一般业务（查询、列表）	1 - 2 秒
后台报表、统计	5 - 10 秒

6.4 错误日志

坑	后果	正确做法
不配置独立路径	系统盘写满，整个服务器瘫痪	必须挂载独立分区
不配置轮转	日志无限增长，最终写满磁盘	必须配置 logrotate
只看 ERROR 级别	忽略 Warning 中的隐患（如连接数接近上限）	定期 Review Warning 级别日志

七、生产问题排查实战案例

案例一：通过错误日志定位实例意外重启

故障现象：凌晨 4 点监控报警，MySQL 实例自动重启。

排查过程：

bash

# 1. 查看错误日志最后 100 行
tail -100 /data/mysql/logs/error/mysql-error.log

发现关键日志：

2024-03-15T03:59:58.123456+08:00 0 [Note] [MY-011971] [InnoDB] InnoDB: Starting crash recovery...
2024-03-15T04:00:02.234567+08:00 0 [Note] [MY-011976] [InnoDB] InnoDB: Crash recovery finished.
2024-03-15T04:00:03.345678+08:00 0 [Note] [MY-010308] [Server] /usr/sbin/mysqld: ready for connections.

再往前翻，发现崩溃前的日志：

2024-03-15T03:59:45.123456+08:00 0 [ERROR] [MY-012681] [InnoDB] InnoDB: Assertion failure in thread 140234567890 in file fil0fil.cc line 12345
InnoDB: Failing assertion: offset < file_size
InnoDB: We intentionally generate a memory trap.

根因定位：InnoDB 在读取数据文件时，发现文件偏移量超过文件大小，触发断言失败，主动崩溃。结合 dmesg 查看系统日志，发现同期有磁盘 I/O 错误。

结论：磁盘故障导致数据文件损坏，InnoDB 崩溃后自动恢复。

处理措施：

更换故障磁盘
用备份 + binlog 恢复数据到最新状态
增加磁盘监控，提前预警

案例二：通过 Binlog 完成误删数据闪回

故障现象：下午 3 点，开发执行了一条 DELETE FROM orders WHERE user_id = 12345，忘记加 LIMIT，删除了 50 万条订单记录。

恢复过程：

bash

# 1. 确认误删时间点
# 开发说大概是 15:05 左右执行的

# 2. 找到包含该时间段的 binlog 文件
mysql -u root -p -e "SHOW BINARY LOGS;"
# 确认 mysql-bin.000023 包含 15:05 的数据

# 3. 解析 binlog，找到 DELETE 语句的位置
mysqlbinlog --base64-output=DECODE-ROWS -v \
    --start-datetime="2024-03-15 15:00:00" \
    --stop-datetime="2024-03-15 15:10:00" \
    /data/mysql/logs/binlog/mysql-bin.000023 > /tmp/binlog_1505.sql

# 4. 在解析结果中找到 DELETE 语句
grep -n "DELETE.*orders" /tmp/binlog_1505.sql
# 找到位置：# at 12345678

# 5. 提取误删之前的数据（反向恢复）
# 方法：用 binlog2sql 或自己写脚本解析
# 这里使用 binlog2sql（开源工具）

# 安装 binlog2sql
pip install binlog2sql

# 生成回滚 SQL
python binlog2sql.py \
    -h127.0.0.1 -P3306 -uroot -p'password' \
    -d ecommerce -t orders \
    --start-file='mysql-bin.000023' \
    --start-position=12345678 \
    --stop-position=23456789 \
    --flashback > /tmp/rollback.sql

# 6. 检查回滚 SQL
head -100 /tmp/rollback.sql
# 输出应该是 INSERT 语句，把删掉的 50 万条数据插回来

# 7. 执行回滚（先在测试环境验证！）
mysql -u root -p ecommerce < /tmp/rollback.sql

# 8. 验证数据
mysql -u root -p -e "SELECT COUNT(*) FROM orders WHERE user_id = 12345;"
# 恢复到 500,000 条

关键点：

恢复前先锁表或暂停写入，避免新数据覆盖
回滚 SQL 必须在测试环境先验证
binlog2sql 要求 binlog 格式是 ROW

案例三：通过慢查询日志优化超 10s 的订单查询

故障现象：用户反馈"我的订单"页面加载超时，接口响应时间超过 10 秒。

排查过程：

bash

# 1. 开启慢查询日志，阈值设为 5 秒
mysql -u root -p -e "SET GLOBAL slow_query_log = 'ON';"
mysql -u root -p -e "SET GLOBAL long_query_time = 5;"

# 2. 复现问题，让用户再次访问页面

# 3. 分析慢查询日志
pt-query-digest /data/mysql/logs/slow/mysql-slow.log > /tmp/slow_report.txt

# 4. 查看报告，找到 TOP 慢 SQL

发现这条 SQL：

# Query 1: 12.34 QPS, 15.67x concurrency, ID 0x1234567890ABCDEF at byte 12345678
# Scores: V/M = 2.34
# Time range: 2024-03-15 14:30:00 to 15:30:00
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count         15    1234
# Exec time     78   15234s     10s     18s     12s     15s      2s     12s
# Lock time      2      15s      0      1s     12ms     20ms     50ms      5ms
# Rows sent      1   1.20k       1      20       1       1       0       1
# Rows examine  92  50.00M   40.00k  60.00k  50.00k  55.00k   5.00k  50.00k
# Rows affecte   0       0       0       0       0       0       0       0
# Bytes sent     1 150.00k     100     200     120     150      20     110
# Query size     1 120.00k      90     110     100     100       5     100
# String:
# Databases    ecommerce
# Hosts        web-server-01 (1000/81%), web-server-02 (200/16%)... 1 more
# Users        app_user
# Query_time distribution
#   1us
#  10us
# 100us
#   1ms
#  10ms
# 100ms
#    1s  ################################################################
#  10s+  ####
# Tables
#    SHOW TABLE STATUS FROM `ecommerce` LIKE 'orders'\G
#    SHOW CREATE TABLE `ecommerce`.`orders`\G
# EXPLAIN /*!50100 PARTITIONS*/
SELECT o.*, u.username, p.product_name
FROM orders o
LEFT JOIN users u ON o.user_id = u.id
LEFT JOIN products p ON o.product_id = p.id
WHERE o.user_id = 12345
  AND o.status IN ('pending', 'paid', 'shipped')
ORDER BY o.create_time DESC
LIMIT 20\G

问题分析：

Rows_examined = 50,000，但 Rows_sent = 20，扫描了 2500 倍的数据
Query_time = 12 秒，远超阈值
EXPLAIN 查看执行计划：

sql

EXPLAIN SELECT o.*, u.username, p.product_name
FROM orders o
LEFT JOIN users u ON o.user_id = u.id
LEFT JOIN products p ON o.product_id = p.id
WHERE o.user_id = 12345
  AND o.status IN ('pending', 'paid', 'shipped')
ORDER BY o.create_time DESC
LIMIT 20;

+----+-------------+-------+------+---------------+------+---------+------+--------+-----------------------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows   | Extra                       |
+----+-------------+-------+------+---------------+------+---------+------+--------+-----------------------------+
|  1 | SIMPLE      | o     | ALL  | NULL          | NULL | NULL    | NULL | 500000 | Using where; Using filesort |
|  1 | SIMPLE      | u     | ALL  | PRIMARY       | NULL | NULL    | NULL | 100000 | Using where                 |
|  1 | SIMPLE      | p     | ALL  | PRIMARY       | NULL | NULL    | NULL |  50000 | Using where                 |
+----+-------------+-------+------+---------------+------+---------+------+--------+-----------------------------+

根因：orders 表全表扫描（type = ALL），没有索引可用。

优化方案：

sql

-- 添加复合索引
ALTER TABLE orders ADD INDEX idx_user_status_time (user_id, status, create_time DESC);

-- 优化后执行计划
+----+-------------+-------+------+------------------------+------------------------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys          | key                    | key_len | ref   | rows | Extra       |
+----+-------------+-------+------+------------------------+------------------------+---------+-------+------+-------------+
|  1 | SIMPLE      | o     | ref  | idx_user_status_time   | idx_user_status_time   | 8       | const |  150 | Using where |
|  1 | SIMPLE      | u     | eq_ref| PRIMARY               | PRIMARY                | 4       | o.user_id | 1 |             |
|  1 | SIMPLE      | p     | eq_ref| PRIMARY               | PRIMARY                | 4       | o.product_id | 1 |             |
+----+-------------+-------+------+------------------------+------------------------+---------+-------+------+-------------+

优化效果：Rows_examined 从 50,000 降到 150，查询时间从 12 秒降到 0.02 秒。

八、MySQL 日志日常巡检 Checklist

每日巡检（建议早上 9 点执行）

检查项	操作命令	正常标准	异常处理
错误日志是否有新错误	`grep "ERROR" /data/mysql/logs/error/mysql-error.log \| grep "$(date +%Y-%m-%d)"`	无新错误	立即排查并处理
磁盘空间是否充足	`df -h /data/mysql/logs`	使用率 < 80%	清理旧日志或扩容
binlog 文件数量	`ls /data/mysql/logs/binlog/ \| wc -l`	与保留策略一致	检查 expire_logs_days 配置
慢查询日志是否有新增	`wc -l /data/mysql/logs/slow/mysql-slow.log`	与昨日持平或下降	用 pt-query-digest 分析
主从复制状态	`mysql -e "SHOW SLAVE STATUS\G" \| grep -E "Slave_IO_Running\|Slave_SQL_Running\|Seconds_Behind"`	两个 Yes，延迟 < 60s	排查复制中断原因
错误日志文件大小	`ls -lh /data/mysql/logs/error/`	单文件 < 1GB	手动执行 logrotate

每周巡检（建议周一上午执行）

检查项	操作命令	输出物
慢查询 TOP 10 分析	`pt-query-digest --since=7d /data/mysql/logs/slow/mysql-slow.log > /reports/slow_weekly_$(date +%Y%m%d).txt`	周报文档
binlog 增长趋势	`du -sh /data/mysql/logs/binlog/`	容量规划依据
错误日志 Warning 汇总	`grep "Warning" /data/mysql/logs/error/mysql-error.log \| grep "$(date -d '7 days ago' +%Y-%m)" \| awk -F'[][]' '{print $4}' \| sort \| uniq -c \| sort -rn`	隐患清单
logrotate 执行状态	`logrotate -d /etc/logrotate.d/mysql-* 2>&1 \| grep -E "error\|failed"`	确保轮转正常
备份恢复演练	用上周的备份 + binlog 恢复到测试环境	验证备份有效性

每月巡检（建议 1 号执行）

检查项	操作命令	输出物
日志存储容量规划	统计过去 30 天各类日志增长量，预测下月需求	容量规划报告
慢查询优化跟进	对比上月和本月慢查询 TOP 10，验证优化效果	优化效果报告
日志配置 Review	检查 my.cnf 中日志相关参数是否需要调整	配置优化建议
审计日志归档	将上月查询日志（如有开启）归档到冷存储	审计合规材料
灾难恢复演练	模拟实例崩溃，用备份 + binlog 完整恢复	恢复时间目标 (RTO) 验证

巡检自动化脚本模板

bash

#!/bin/bash
# MySQL 日志每日巡检脚本
# 建议放入 crontab：0 9 * * * /scripts/mysql_daily_check.sh

LOG_DIR="/data/mysql/logs"
REPORT_DIR="/reports/mysql_daily"
DATE=$(date +%Y%m%d)
REPORT_FILE="${REPORT_DIR}/daily_check_${DATE}.txt"

mkdir -p ${REPORT_DIR}

echo "========== MySQL 日志每日巡检报告 ==========" > ${REPORT_FILE}
echo "巡检时间: $(date)" >> ${REPORT_FILE}
echo "" >> ${REPORT_FILE}

# 1. 磁盘空间检查
echo "【1. 磁盘空间】" >> ${REPORT_FILE}
df -h ${LOG_DIR} >> ${REPORT_FILE}
echo "" >> ${REPORT_FILE}

# 2. 错误日志检查
echo "【2. 今日错误日志】" >> ${REPORT_FILE}
grep "ERROR" ${LOG_DIR}/error/mysql-error.log 2>/dev/null | grep "$(date +%Y-%m-%d)" >> ${REPORT_FILE} || echo "无错误" >> ${REPORT_FILE}
echo "" >> ${REPORT_FILE}

# 3. 慢查询统计
echo "【3. 慢查询统计】" >> ${REPORT_FILE}
mysqldumpslow -s c -t 5 ${LOG_DIR}/slow/mysql-slow.log 2>/dev/null >> ${REPORT_FILE}
echo "" >> ${REPORT_FILE}

# 4. Binlog 状态
echo "【4. Binlog 状态】" >> ${REPORT_FILE}
mysql -u root -p'password' -e "SHOW BINARY LOGS;" 2>/dev/null >> ${REPORT_FILE}
echo "" >> ${REPORT_FILE}

# 5. 主从复制状态
echo "【5. 主从复制状态】" >> ${REPORT_FILE}
mysql -u root -p'password' -e "SHOW SLAVE STATUS\G" 2>/dev/null | grep -E "Slave_IO_Running|Slave_SQL_Running|Seconds_Behind_Master|Last_Error" >> ${REPORT_FILE}
echo "" >> ${REPORT_FILE}

# 6. 日志文件大小
echo "【6. 日志文件大小】" >> ${REPORT_FILE}
ls -lh ${LOG_DIR}/*/ >> ${REPORT_FILE}

# 发送邮件
mail -s "MySQL 日志每日巡检报告 - ${DATE}" dba@company.com < ${REPORT_FILE}

echo "巡检完成，报告已保存至 ${REPORT_FILE}"

九、总结

MySQL 四类日志是运维的"左膀右臂"：

日志	核心价值	使用频率
错误日志	实例生死的"黑匣子"	每次故障必看
Binlog	数据安全的"最后防线"	主从架构必备
慢查询日志	性能优化的"导航仪"	每周必分析
查询日志	问题排查的"显微镜"	按需临时开启

记住三条铁律：

日志必须独立存储，不要和系统盘、数据盘混在一起
轮转必须配置，任何日志都不能无限增长
定期巡检，把问题消灭在萌芽状态

希望这篇文章能帮你从"救火队员"变成"防火专家"。运维的路上，日志是你最忠实的朋友——它永远在说真话，只要你愿意听。

作者：一个修过无数 MySQL 故障的一线 DBA
最后更新：2024-03-15
适用版本：MySQL 5.7 / 8.0
转载声明：欢迎转发给需要的运维同行，请注明出处

MySQL 运维日志实战：四类核心日志从入门到精通 ​

一、凌晨三点的报警：从一个真实故障说起 ​

二、错误日志（Error Log）：MySQL 的"黑匣子" ​

2.1 它解决什么生产问题？ ​

2.2 生产环境标准配置 ​

2.3 存储路径规划：别把鸡蛋放在一个篮子里 ​

2.4 日志内容结构拆解 ​

2.5 日常运维常用命令 ​

2.6 logrotate 轮转配置 ​

三、二进制日志（Binlog）：MySQL 的"录像机" ​

3.1 它解决什么生产问题？ ​

3.2 生产环境标准配置 ​

3.3 存储路径规划 ​

3.4 日志内容结构拆解 ​

3.5 日常运维常用命令 ​

3.6 logrotate 轮转配置 ​

四、查询日志（General Query Log）：MySQL 的"监控摄像头" ​

4.1 它解决什么生产问题？ ​

4.2 生产环境标准配置 ​

4.3 日志内容结构拆解 ​

4.4 日常运维常用命令 ​

4.5 logrotate 轮转配置 ​

五、慢查询日志（Slow Query Log）：MySQL 的"体检报告" ​

5.1 它解决什么生产问题？ ​

5.2 生产环境标准配置 ​

5.3 日志内容结构拆解 ​

5.4 日常运维常用命令 ​

5.5 logrotate 轮转配置 ​

六、运维避坑指南：血泪教训总结 ​

6.1 查询日志（General Log） ​

6.2 二进制日志（Binlog） ​

6.3 慢查询日志 ​

6.4 错误日志 ​

七、生产问题排查实战案例 ​

案例一：通过错误日志定位实例意外重启 ​

案例二：通过 Binlog 完成误删数据闪回 ​

案例三：通过慢查询日志优化超 10s 的订单查询 ​

八、MySQL 日志日常巡检 Checklist ​

每日巡检（建议早上 9 点执行） ​

每周巡检（建议周一上午执行） ​

每月巡检（建议 1 号执行） ​

巡检自动化脚本模板 ​

九、总结 ​

MySQL 运维日志实战：四类核心日志从入门到精通

一、凌晨三点的报警：从一个真实故障说起

二、错误日志（Error Log）：MySQL 的"黑匣子"

2.1 它解决什么生产问题？

2.2 生产环境标准配置

2.3 存储路径规划：别把鸡蛋放在一个篮子里

2.4 日志内容结构拆解

2.5 日常运维常用命令

2.6 logrotate 轮转配置

三、二进制日志（Binlog）：MySQL 的"录像机"

3.1 它解决什么生产问题？

3.2 生产环境标准配置

3.3 存储路径规划

3.4 日志内容结构拆解

3.5 日常运维常用命令

3.6 logrotate 轮转配置

四、查询日志（General Query Log）：MySQL 的"监控摄像头"

4.1 它解决什么生产问题？

4.2 生产环境标准配置

4.3 日志内容结构拆解

4.4 日常运维常用命令

4.5 logrotate 轮转配置

五、慢查询日志（Slow Query Log）：MySQL 的"体检报告"

5.1 它解决什么生产问题？

5.2 生产环境标准配置

5.3 日志内容结构拆解

5.4 日常运维常用命令

5.5 logrotate 轮转配置

六、运维避坑指南：血泪教训总结

6.1 查询日志（General Log）

6.2 二进制日志（Binlog）

6.3 慢查询日志

6.4 错误日志

七、生产问题排查实战案例

案例一：通过错误日志定位实例意外重启

案例二：通过 Binlog 完成误删数据闪回

案例三：通过慢查询日志优化超 10s 的订单查询

八、MySQL 日志日常巡检 Checklist

每日巡检（建议早上 9 点执行）

每周巡检（建议周一上午执行）

每月巡检（建议 1 号执行）

巡检自动化脚本模板

九、总结