技术 · 思考 · 实践 · 成长

首页/MySQL8专栏/正文

MySQL 8 日志体系详解:error、slow、binlog、redo、undo 与 relay log

MySQL8专栏730 次阅读

MySQL 8 的日志既服务于故障恢复和复制,也用于性能分析与问题排查。理解日志体系时,应先区分 MySQL Server 层日志与 InnoDB 存储引擎日志:error、general、slow 和 binlog 主要位于 Server 层;redo 与 undo 由 InnoDB 管理;relay log 则服务于复制副本。

一、日志体系全景

MySQL 8 日志体系图

日志主要作用常见场景
Error Log记录启动、运行、关闭过程中的重要信息和错误启动失败、崩溃恢复、复制报错
General Query Log记录连接和服务器收到的语句短时间确认应用实际发送的 SQL
Slow Query Log记录达到慢查询条件的语句定位耗时 SQL 和低效访问
Binary Log记录数据变更事件复制、时间点恢复、变更追踪
Redo Log记录 InnoDB 页的物理变更信息崩溃恢复、保证已提交修改的持久性
Undo Log保存行修改前的信息事务回滚、MVCC 一致性读
Relay Log保存副本从源库接收的 binlog 事件复制应用与故障排查

可以简单记忆:redo 和 undo 支撑 InnoDB 事务,binlog 支撑复制与时间点恢复,error、general 和 slow 负责观测与排查,relay log 连接源库 binlog 与副本应用过程。

二、一条 UPDATE 可能经过哪些日志

客户端发送 UPDATE
  ↓
Server 层解析、优化和执行
  ├─ general log:启用时记录收到的语句
  ↓
InnoDB 修改数据
  ├─ undo:保存回滚与一致性读所需的旧版本信息
  ├─ buffer pool:在内存中修改数据页
  └─ redo:记录页变更,用于崩溃恢复
  ↓
事务提交
  ├─ binlog:记录 Server 层变更事件
  └─ redo 与 binlog 协调提交
  ↓
返回客户端
  ├─ slow log:达到慢查询条件时记录
  └─ error log:异常或重要状态可能写入

这是便于理解的简化流程。实际内部顺序还受到事务、组提交、刷盘策略、binlog 配置和 MySQL 小版本实现影响。关键点是:redo 与 binlog 通过协调提交保持 InnoDB 状态和 binlog 的一致性。

三、错误日志 Error Log

MySQL 8 错误日志参数

1. 错误日志记录什么

  • MySQL 启动、初始化、关闭和崩溃恢复过程。
  • 配置文件错误、端口占用、权限和磁盘问题。
  • InnoDB 数据页损坏、断言失败和严重异常。
  • 复制接收线程或应用线程错误。
  • 证书、插件、组件和账号相关的重要警告。

2. 查看配置

SHOW VARIABLES LIKE 'log_error';
SHOW VARIABLES LIKE 'log_error_services';
SHOW VARIABLES LIKE 'log_error_verbosity';

MySQL 8 支持组件化错误日志,由过滤器和输出组件组成。具体可用组件、加载方式和默认目标会因安装方式与版本而不同,不应直接复制其他环境的组件配置。

3. 查看日志

# systemd 管理的 MySQL
journalctl -u mysqld --since "1 hour ago"

# Debian/Ubuntu 服务名可能是 mysql
journalctl -u mysql --since "1 hour ago"

# 查看文件日志
sudo tail -n 200 /var/log/mysql/error.log

容器环境中,错误日志经常输出到标准错误流:

docker logs --tail 200 mysql8

不要只搜索 ERROR,启动失败的根因可能出现在它之前的 warning 或上下文中。

四、通用查询日志 General Query Log

General Log 记录客户端连接、断开以及服务器收到的语句,适合确认 ORM 或应用实际发送了什么。它会产生大量 I/O,也可能记录 SQL 中的敏感值,因此生产环境不应长期开启。

1. 查看当前配置

SHOW VARIABLES LIKE 'general_log';
SHOW VARIABLES LIKE 'general_log_file';
SHOW VARIABLES LIKE 'log_output';

2. 短时间开启和关闭

SET GLOBAL log_output = 'FILE';
SET GLOBAL general_log = ON;

-- 复现问题后立即关闭
SET GLOBAL general_log = OFF;

SET GLOBAL 只影响之后建立的行为或全局状态,并且不一定跨重启持久化。是否使用 SET PERSIST 或配置文件,应根据变更流程决定;临时排障通常不需要持久化。

3. 表输出模式

SET GLOBAL log_output = 'TABLE';
SET GLOBAL general_log = ON;

SELECT event_time, user_host, command_type, argument
FROM mysql.general_log
ORDER BY event_time DESC
LIMIT 100;

SET GLOBAL general_log = OFF;

清理日志前必须先关闭相关写入并确认保留要求。不要在不了解影响时直接操作 mysql.general_log。

五、慢查询日志 Slow Query Log

MySQL 8 慢查询日志参数

Slow Log 用于记录执行时间达到阈值的语句,是数据库性能排查的重要来源,但“慢”只代表耗时达到配置条件,并不等同于一定缺少索引。

1. 查看关键参数

SHOW VARIABLES WHERE Variable_name IN (
  'slow_query_log',
  'slow_query_log_file',
  'long_query_time',
  'min_examined_row_limit',
  'log_queries_not_using_indexes',
  'log_slow_admin_statements',
  'log_slow_extra',
  'log_output'
);

2. 临时启用

SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 1.0;

新建连接会使用新的 long_query_time。log_queries_not_using_indexes 可能生成大量日志,不能未经评估直接在繁忙生产库开启。

3. 重点观察的字段

  • Query_time:语句总耗时。
  • Lock_time:等待表锁的时间;它不能覆盖所有 InnoDB 锁等待含义。
  • Rows_examined:执行过程中检查的行数。
  • Rows_sent:返回给客户端的行数。

Rows_examined 很大而 Rows_sent 很小时,值得检查执行计划,但不能只凭一个比值直接断定索引设计错误。

4. 分析日志

# MySQL 自带的简单汇总工具
mysqldumpslow -s t -t 20 /var/log/mysql/slow.log

# 已安装 Percona Toolkit 时
pt-query-digest /var/log/mysql/slow.log > slow-report.txt

5. 使用 Performance Schema 实时聚合

SELECT DIGEST_TEXT,
       COUNT_STAR,
       ROUND(SUM_TIMER_WAIT / 1000000000000, 2) AS total_seconds,
       ROUND(AVG_TIMER_WAIT / 1000000000000, 6) AS avg_seconds,
       SUM_ROWS_EXAMINED,
       SUM_ROWS_SENT
FROM performance_schema.events_statements_summary_by_digest
WHERE DIGEST_TEXT IS NOT NULL
ORDER BY SUM_TIMER_WAIT DESC
LIMIT 20;

Slow Log 适合留痕和离线分析,Performance Schema 适合查看当前实例的聚合情况,两者可以互补。

六、二进制日志 Binary Log

MySQL 8 二进制日志参数

1. 主要用途

  • 复制:副本读取并应用源库的变更事件。
  • 时间点恢复:恢复全量备份后,继续重放 binlog。
  • 变更分析:在有授权和审计流程的情况下分析数据变化。

binlog 不是查询日志,通常记录影响数据或结构的事件,而不是每一条只读 SELECT。

2. 查看配置

SHOW VARIABLES WHERE Variable_name IN (
  'log_bin',
  'binlog_format',
  'binlog_row_image',
  'sync_binlog',
  'binlog_expire_logs_seconds',
  'max_binlog_size'
);

SHOW BINARY LOGS;

3. 三种格式

  • STATEMENT:记录产生变更的 SQL,体积可能较小,但部分非确定性语句存在限制。
  • ROW:记录行变更事件,复制结果更确定,是 MySQL 8 常用格式。
  • MIXED:由服务器在 Statement 和 Row 之间选择。

4. 使用 mysqlbinlog 查看

mysqlbinlog \
  --base64-output=DECODE-ROWS \
  -vv \
  binlog.000123 \
  | less

ROW 格式展示的是事件解释结果,不一定能还原成原始 SQL 文本。binlog 可能包含敏感数据,应限制文件权限和访问范围。

5. 安全清理

PURGE BINARY LOGS BEFORE '2026-11-01 00:00:00';

清理前必须确认备份、时间点恢复和所有副本仍需的日志范围。不要直接使用操作系统命令删除 binlog 文件,否则索引和复制状态可能不一致。更推荐设置经过容量评估的自动过期策略。

七、InnoDB Redo Log

Redo Log 记录 InnoDB 数据页变更所需的信息。事务提交时不必立即把所有脏页写回数据文件,而是依赖 redo 的 WAL(Write-Ahead Logging)机制保证崩溃后可以恢复。

1. 查看持久性相关配置

SHOW VARIABLES WHERE Variable_name IN (
  'innodb_flush_log_at_trx_commit',
  'innodb_log_buffer_size',
  'innodb_redo_log_capacity'
);

innodb_redo_log_capacity 从 MySQL 8.0.30 起用于控制 redo 总容量;更早的 8.0 版本使用 innodb_log_file_size 和 innodb_log_files_in_group。调整前必须先确认准确版本。

2. MySQL 8.0.30 及以上监控

SHOW STATUS LIKE 'Innodb_redo_log%';

SELECT FILE_ID,
       START_LSN,
       END_LSN,
       SIZE_IN_BYTES,
       IS_FULL
FROM performance_schema.innodb_redo_log_files;

redo 太小可能导致频繁 checkpoint 和脏页刷盘;过大则可能增加崩溃恢复时间和磁盘占用。容量需要结合写入速率、checkpoint 压力与恢复目标评估。

八、InnoDB Undo Log

Undo Log 保存行修改前的信息,主要服务于两个功能:

  • 事务回滚:撤销尚未提交的修改。
  • MVCC:为一致性读构造较早版本的数据行。

长事务会阻止旧版本及时清理,使 undo 和 history list 持续增长。可以查看正在运行的事务:

SELECT trx_id,
       trx_state,
       trx_started,
       trx_mysql_thread_id,
       trx_rows_modified,
       trx_query
FROM information_schema.INNODB_TRX
ORDER BY trx_started;

查看 purge 相关历史长度指标:

SELECT NAME, COUNT
FROM information_schema.INNODB_METRICS
WHERE NAME = 'trx_rseg_history_len';

不要手动删除 undo 表空间文件。用户定义 undo 表空间的创建、停用和删除必须遵循 MySQL 官方流程。

九、复制中继日志 Relay Log

在复制副本上,接收线程从源库读取 binlog 事件并写入 relay log,应用线程再读取 relay log 并执行这些事件。

源库 binlog
  ↓  复制接收线程
副本 relay log
  ↓  复制应用线程
副本数据

查看复制状态:

SHOW REPLICA STATUS;

排查时重点关注接收线程、应用线程、最后错误、延迟和待应用事务。relay log 通常由 MySQL 自动轮换和清理,不应直接手工删除文件。

十、redo 与 binlog 的持久性参数

以下两个参数直接影响事务提交时的刷盘行为:

SHOW VARIABLES WHERE Variable_name IN (
  'innodb_flush_log_at_trx_commit',
  'sync_binlog'
);
  • innodb_flush_log_at_trx_commit=1:每次事务提交时写入并刷盘 redo,通常提供更强持久性。
  • sync_binlog=1:每次提交时同步 binlog 到磁盘,通常提供更强的 binlog 持久性。

降低刷盘频率可能提高吞吐,但在操作系统崩溃或断电时增加数据丢失风险。参数选择必须以 RPO、存储能力和高可用方案为依据,不能只追求性能。

十一、日志排查顺序

  • MySQL 无法启动:先看 error log,再检查配置、权限、磁盘和端口。
  • 接口突然变慢:看 slow log、Performance Schema、锁等待和系统资源。
  • 应用 SQL 与预期不符:短时间开启 general log,复现后立即关闭。
  • 误更新或误删除:保护现场,确认全量备份和 binlog,再在隔离环境恢复。
  • 复制中断:查看 SHOW REPLICA STATUS、error log、relay log 与网络。
  • 崩溃后恢复缓慢:观察 error log 的 InnoDB recovery 进度及 redo/checkpoint 状态。
  • undo 持续增长:检查长事务、未结束会话和 purge 状态。

十二、日志安全与维护

  • 日志可能包含账号、SQL 参数和业务数据,应设置最小文件权限。
  • General Log 与 Slow Log 不应无期限增长,应配置轮转、压缩和保留周期。
  • 轮转 MySQL 管理的日志时应使用支持的方式,不要随意移动或删除正在写入的文件。
  • binlog 保留周期必须覆盖备份恢复和复制需求。
  • 集中采集日志时,应控制脱敏、传输加密和访问审计。
  • 日志不能替代监控,应同时关注连接数、QPS、延迟、磁盘、锁和复制状态。

十三、官方文档

十四、总结

Error Log 用于故障诊断,General Log 用于短时捕获客户端行为,Slow Log 和 Performance Schema 用于性能分析,binlog 用于复制与时间点恢复,redo 和 undo 支撑 InnoDB 事务,relay log 则承担副本事件中转。正确使用日志的关键,是明确用途、控制性能代价,并做好安全、轮转、保留和恢复演练。

梦想在远方闪烁,脚步则需踏实前行。

评论交流

1 条评论 · 支持多级回复
验证码

相关文章

继续阅读