MySQL错误日志：诊断与优化的关键工具

原创

晚上不吃饭了

发布于 2025-05-17 14:27:58

11200

代码可运行

文章被收录于专栏：数据库数据库

运行总次数：0

代码可运行

错误日志概述

错误日志是MySQL记录服务器运行期间发生的错误、警告和关键事件的日志文件，是诊断MySQL问题的首要工具。

核心作用与功能

故障诊断：记录服务器启动/关闭问题和运行时错误
安全审计：记录未授权的访问尝试
系统监控：跟踪警告和关键事件
复制问题：记录主从复制相关的错误
操作审计：记录关键管理操作（如clean shutdown）

工作机制

写入时机：实时写入（同步写入磁盘） 内容格式：

   时间戳 [日志级别] [错误代码] [子系统] 错误信息

日志轮转： 需要外部工具（如logrotate）管理 多版本差异： - MySQL 5.7：简单文本格式 - MySQL 8.0+：支持组件化和JSON格式

日志内容组成

服务器启停信息 严重错误信息（崩溃、表损坏等） 警告信息（非致命问题） 事件调度器信息 从库复制错误 InnoDB引擎状态信息

配置方式

配置文件设置（my.cnf/my.ini）

[mysqld]
log_error = /var/log/mysql/mysql-error.log
log_error_verbosity = 3
log_error_services = log_filter_internal; log_sink_internal
log_warnings = 2

动态查看日志位置

SHOW VARIABLES LIKE 'log_error';

MySQL 错误日志记录方式比较：表 vs 文件

MySQL 提供了两种主要的错误日志记录方式：将错误日志存储在系统表中或存储在文件系统中。以下是它们的比较和各自存在的问题。

表存储错误日志:

实现方式： 将错误日志存储在 mysql.error_log 系统表中

优点：

便于使用 SQL 查询和分析日志
可以与其他表进行关联查询
便于实现基于权限的访问控制
日志与数据库一起备份

缺点/问题：

性能影响： 数据库繁忙时，写入日志可能成为瓶颈
循环日志限制： 表存储不易实现自动循环覆盖，可能导致表过大
数据库故障时不可用： 当MySQL服务完全崩溃时，无法记录最新错误
存储空间管理： 需要手动清理旧日志，否则会占用数据库空间

文件存储错误日志:

实现方式： 将错误日志写入指定的文件（如 hostname.err）

优点：

性能更好，对数据库影响小
操作系统工具（如logrotate）可以方便管理日志轮转
数据库完全崩溃时仍能记录错误
文件系统操作通常比数据库操作更轻量级

缺点/问题：

查询不便： 需要使用文件操作工具（如grep）分析日志
权限管理复杂： 需要文件系统权限控制
与数据库备份不同步： 需要单独备份日志文件
多实例管理： 在多实例环境下需要更复杂的日志文件管理

混合使用建议

主要使用文件日志，因其更可靠且对性能影响小
对于需要SQL分析的部分日志，可以定期导入到表中
使用专门的日志管理工具（如ELK栈）进行集中管理

核心参数

参数名	类型	默认值	说明
log_error	String	主机名.err	错误日志文件路径
log_error_verbosity	Integer	2	日志详细程度： 1=ERROR 2=ERROR+WARNING 3=ERROR+WARNING+INFORMATION
log_error_services	String	见说明	MySQL 8.0+日志服务组件
log_warnings	Integer	2	警告日志级别（已弃用，建议使用log_error_verbosity替代）

补充说明

log_error_verbosity 详细说明
- (ERROR): 仅记录错误信息
- (ERROR+WARNING): 记录错误和警告（默认值）
- (ERROR+WARNING+INFORMATION): 记录错误、警告和普通信息
log_error_services (MySQL 8.0+)
- 默认值通常为 "log_filter_internal; log_sink_internal"
- 可扩展支持JSON格式日志等新特性
弃用说明
- log_warnings 参数在MySQL 8.0中已被标记为弃用
- 建议使用 log_error_verbosity 替代

配置示例:

-- 设置错误日志路径
SET GLOBAL log_error = '/var/log/mysql/mysql-error.log';

-- 设置详细日志级别（记录错误、警告和信息）
SET GLOBAL log_error_verbosity = 3;

-- 查看当前设置
SHOW VARIABLES LIKE 'log_error%';

日志级别说明

MySQL 5.7及以下：

log_warnings：
- 0：不记录警告
- 1：记录错误和警告
- 2+：更详细警告

MySQL 8.0+：

log_error_verbosity：
- 1：仅错误
- 2：错误+警告
- 3：错误+警告+信息

日志文件示例

2025-04-20T14:23:45.123456Z 0 [ERROR] [MY-010123] [Server] Access denied for user 'root'@'localhost'
2025-04-20T14:23:46.234567Z 0 [Warning] [MY-010000] [Server] InnoDB: Cannot open table test/corrupted_table
2025-04-20T14:23:47.345678Z 0 [Note] [MY-010000] [Server] Server shutdown initiated

日志轮转配置

logrotate配置示例

/var/log/mysql/mysql-error.log {
    daily
    rotate 30
    missingok
    compress
    delaycompress
    notifempty
    create 640 mysql mysql
    postrotate
        mysqladmin flush-logs
    endscript
}

错误日志分析

# 查看最近错误
tail -n 100 /var/log/mysql/mysql-error.log

# 检查启动错误
grep -A 5 "ERROR" /var/log/mysql/mysql-error.log | head -20

# 检查复制错误
grep "replication" /var/log/mysql/mysql-error.log

# 检查表损坏错误
grep "corrupt" /var/log/mysql/mysql-error.log

# 查找特定错误
grep -i "error" /var/log/mysql/mysql-error.log

# 统计错误类型
awk '/\[ERROR\]/{print $5}' /var/log/mysql/mysql-error.log | sort | uniq -c

常见错误代码速查

错误代码	说明	解决方案
MY-010123	访问被拒绝	检查用户权限
MY-012345	表损坏	使用 REPAIR TABLE 修复
MY-002233	连接数耗尽	增加 max_connections 参数
MY-003344	磁盘空间不足	清理磁盘空间或扩容存储

生产环境建议

路径设置： 错误日志应放在独立分区
权限控制： 仅限管理员访问
监控配置： 对关键错误设置告警
定期审查： 建议每日检查错误日志
版本差异：
- MySQL 5.7：使用log_warnings
- MySQL 8.0+：使用log_error_verbosity

MySQL 8.0+增强功能

组件化架构：

-- 安装syslog组件
INSTALL COMPONENT "file://component_log_sink_syseventlog";
-- 启用多目标输出
SET GLOBAL log_error_services = 'log_filter_internal; log_sink_syseventlog';

多日志输出：

log_error_services = 'log_filter_internal; log_sink_internal; log_sink_json'

JSON格式日志：

{
  "timestamp": "2023-08-20T14:23:45.123456Z",
  "priority": "ERROR",
  "err_code": "MY-010123",
  "subsystem": "Server",
  "message": "Access denied for user 'root'@'localhost'"
}

增强过滤:

-- 只记录特定子系统的错误
SET GLOBAL log_error_services = 'log_filter_internal; 
                               log_sink_filter; 
                               log_sink_internal';
ALTER COMPONENT "file://component_log_filter_dragnet"
  SET VARIABLES = 'filter_rules = "subsystem=InnoDB"';