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

错误日志概述

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

---

核心作用与功能

1. 故障诊断：记录服务器启动/关闭问题和运行时错误
2. 安全审计：记录未授权的访问尝试
3. 系统监控：跟踪警告和关键事件
4. 复制问题：记录主从复制相关的错误
5. 操作审计：记录关键管理操作（如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替代）

补充说明

1. log_error_verbosity 详细说明

   • 1 (ERROR): 仅记录错误信息
   • 2 (ERROR+WARNING): 记录错误和警告（默认值）
   • 3 (ERROR+WARNING+INFORMATION): 记录错误、警告和普通信息

2. log_error_services (MySQL 8.0+)

   • 默认值通常为 "log_filter_internal; log_sink_internal"
   • 可扩展支持JSON格式日志等新特性

3. 弃用说明

   • 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"';