【MySQL学习】MySQL的慢查询日志和错误日志

'# 【MySQL学习】MySQL的慢查询日志和错误日志

一、背景与问题

在MySQL数据库运维中，日志系统是性能调优和故障排查的核心工具。慢查询日志和错误日志作为两大核心日志类型，分别承担着性能监控和系统健康度诊断的职责。

慢查询日志通过记录执行时间超过阈值的SQL语句，帮助开发人员定位性能瓶颈；错误日志则记录数据库运行时的异常信息，是系统崩溃分析的重要依据。但这两个日志系统存在显著差异：

慢查询日志需要显式开启，并依赖配置参数控制日志行为
错误日志是MySQL默认开启的系统日志，记录所有非正常运行状态
两者在日志格式、存储位置、日志级别等维度存在本质差异

在实际项目中，我们曾遇到过因慢查询日志配置不当导致磁盘空间耗尽的生产事故，也经历过因错误日志未记录关键信息导致的系统故障排查困难。这些问题促使我们深入理解这两个日志系统的内部机制。

二、基本原理

1. 慢查询日志原理

慢查询日志记录的是执行时间超过long_query_time阈值的SQL语句。其核心机制包含三个关键组件：

查询执行时间统计：MySQL通过query_time字段记录每个查询的执行时长
日志记录机制：当查询时间超过配置阈值时，会触发日志记录逻辑
日志格式控制：支持多种格式输出（如CSV、JSON、原始日志）

关键配置参数包括：

[mysqld]
slow_query_log = 1
long_query_time = 1
slow_query_log_file = /var/log/mysql/slow.log
log_output = FILE

2. 错误日志原理

错误日志是MySQL的系统日志系统，其核心特性包括：

自动记录：所有非正常运行状态都会自动记录
多源日志：包含启动日志、运行时错误、系统信号等
日志级别控制：支持不同严重级别的日志记录（如FATAL、ERROR、WARNING）

关键配置参数：

[mysqld]
log_error = /var/log/mysql/error.log
log_error_verbosity = 3

三、环境准备

我们使用以下开发环境进行演示：

MySQL 8.0.28
Ubuntu 20.04 LTS
磁盘空间 ≥ 10GB
可访问的数据库权限

配置文件示例（/etc/mysql/my.cnf）：

[mysqld]
slow_query_log = 1
long_query_time = 1
slow_query_log_file = /var/log/mysql/slow.log
log_output = FILE
log_error = /var/log/mysql/error.log
log_error_verbosity = 3

四、核心实现

1. 慢查询日志配置

# 创建日志目录
sudo mkdir -p /var/log/mysql
sudo chown -R mysql:mysql /var/log/mysql

# 修改配置文件
sudo nano /etc/mysql/my.cnf

# 重启MySQL服务
sudo systemctl restart mysql

关键代码解释：

slow_query_log 控制日志开启状态
long_query_time 设置阈值（单位：秒）
log_output 控制日志输出方式（FILE/STDOUT）
slow_query_log_file 指定日志文件路径

2. 错误日志配置

# 查看当前错误日志配置
mysql -u root -p -e "SHOW VARIABLES LIKE 'log_error';"

输出示例：

+---------------+----------------------------+
| Variable_name | Value                      |
+---------------+----------------------------+
| log_error     | /var/log/mysql/error.log   |
+---------------+----------------------------+

3. 日志分析工具

# 安装pt-query-digest工具
sudo apt-get install percona-toolkit

# 分析慢查询日志
pt-query-digest /var/log/mysql/slow.log > /var/log/mysql/slow_analysis.txt

五、完整案例

案例背景

某电商平台在促销期间遇到查询性能下降问题，我们通过慢查询日志定位到如下SQL：

SELECT * FROM orders WHERE user_id = 12345;

案例实施

配置慢查询日志

[mysqld]
slow_query_log = 1
long_query_time = 1
slow_query_log_file = /var/log/mysql/slow.log
log_output = FILE

创建测试表

CREATE TABLE orders (
 id INT AUTO_INCREMENT PRIMARY KEY,
 user_id INT,
 order_date DATETIME,
 amount DECIMAL(10,2)
) ENGINE=InnoDB;

插入测试数据

INSERT INTO orders (user_id, order_date, amount)
SELECT 
 FLOOR(1 + RAND() * 1000000) AS user_id,
 NOW() AS order_date,
 FLOOR(100 + RAND() * 900) AS amount
FROM 
 mysql.help_topic
LIMIT 100000;

执行慢查询

SELECT * FROM orders WHERE user_id = 12345;

分析日志
```
pt-query-digest /var/log/mysql/slow.log
```

分析结果显示该查询执行时间为0.15秒，但发现索引缺失问题。

优化方案

添加索引

CREATE INDEX idx_user_id ON orders(user_id);

验证优化效果

EXPLAIN SELECT * FROM orders WHERE user_id = 12345;

六、源码解析

1. 慢查询日志核心代码

在sql/log.cc中，MySQL通过slow_query_log全局变量控制日志开启状态。关键函数包括：

void log_slow_query(THD *thd, const char *query, size_t query_len) {
    if (slow_query_log && long_query_time > 0) {
        // 记录日志逻辑
        write_slow_query_log(thd, query, query_len);
    }
}

2. 错误日志核心代码

在sql/log.cc中，错误日志系统通过log_error变量控制日志路径。关键函数包括：

void log_error(const char *message) {
    if (log_error && log_error_verbosity > 0) {
        // 写入错误日志
        write_error_log(message);
    }
}

七、进阶使用

1. 慢查询日志高级配置

[mysqld]
slow_query_log = 1
long_query_time = 0.1
slow_query_log_file = /var/log/mysql/slow.log
log_output = FILE
min_examined_row_limit = 100

min_examined_row_limit 控制记录日志的最小行数
log_queries_not_using_indexes 记录未使用索引的查询

2. 错误日志高级配置

[mysqld]
log_error = /var/log/mysql/error.log
log_error_verbosity = 3
log_bin = /var/log/mysql/mysql-bin.log

log_bin 配置二进制日志路径
log_error_verbosity 控制日志详细程度（1-3级）

八、性能与工程实践

1. 慢查询日志性能优化

索引优化：确保查询字段有索引
查询优化：避免SELECT *，使用LIMIT
日志配置：合理设置long_query_time阈值
日志清理：定期清理旧日志文件

2. 错误日志安全风险

敏感信息泄露：错误日志可能包含连接信息
日志文件权限：设置合适的文件权限（644）
日志存储位置：避免公开访问路径

3. 性能监控方案

# 实时监控慢查询日志
tail -f /var/log/mysql/slow.log | grep "Query took"

九、常见问题与踩坑

1. 慢查询日志未生效

错误示例：

[mysqld]
slow_query_log = 1
long_query_time = 1

问题分析：

未指定日志文件路径（slow_query_log_file）
配置文件未生效（未重启MySQL）

解决办法：

sudo systemctl restart mysql

2. 错误日志未记录启动信息

错误示例：

[mysqld]
log_error = /var/log/mysql/error.log

问题分析：

log_error_verbosity 未设置为 ≥ 1

解决办法：

log_error_verbosity = 3

3. 日志文件过大

错误示例：

ls -lh /var/log/mysql/

输出：

-rw-r--r-- 1 mysql mysql 1.2G Jul 10 14:30 slow.log

解决办法：

定期清理日志
配置日志轮转（logrotate）

十、最佳实践

1. 慢查询日志最佳实践

生产环境：开启慢查询日志，设置long_query_time = 0.1
开发环境：关闭慢查询日志，减少性能损耗
日志分析：使用pt-query-digest进行分析
索引优化：根据日志优化查询语句

2. 错误日志最佳实践

生产环境：设置log_error_verbosity = 3，记录详细信息
安全防护：设置log_error路径为安全目录，权限为644
监控告警：配置日志文件大小监控，防止磁盘满
日志轮转：配置logrotate定期清理旧日志

十一、总结

MySQL的慢查询日志和错误日志是数据库运维的核心工具。通过深入理解其工作原理，我们可以更有效地进行性能调优和故障排查。在实际项目中，合理配置这两个日志系统能够显著提升系统稳定性。

需要注意的是，慢查询日志需要谨慎配置，避免在高并发场景下产生过多日志影响性能；错误日志则需要关注安全风险，防止敏感信息泄露。通过结合日志分析工具和合理的配置策略，我们可以将日志系统转化为提升系统稳定性的利器。

在实际开发中，建议将日志系统作为监控体系的重要组成部分，结合其他监控工具（如Prometheus、Grafana）构建完整的运维体系。对于关键业务系统，建议定期进行日志分析，及时发现潜在问题。

【MySQL学习】MySQL的慢查询日志和错误日志

一、背景与问题

二、基本原理

1. 慢查询日志原理

2. 错误日志原理

三、环境准备

四、核心实现

1. 慢查询日志配置

2. 错误日志配置

3. 日志分析工具

五、完整案例

案例背景

案例实施

优化方案

六、源码解析

1. 慢查询日志核心代码

2. 错误日志核心代码

七、进阶使用

1. 慢查询日志高级配置

2. 错误日志高级配置

八、性能与工程实践

1. 慢查询日志性能优化

2. 错误日志安全风险

3. 性能监控方案

九、常见问题与踩坑

1. 慢查询日志未生效

2. 错误日志未记录启动信息

3. 日志文件过大

十、最佳实践

1. 慢查询日志最佳实践

2. 错误日志最佳实践

十一、总结

评论已关闭

推荐阅读