Ubuntu运维中MySQL数据库慢查询日志开启

在Ubuntu系统上运维MySQL数据库时，开启慢查询日志是定位性能瓶颈、优化SQL语句的关键第一步。很多数据库响应缓慢的问题，根源都在于那些未被发现的“慢查询”。下面我将直接告诉你如何在Ubuntu上开启和配置MySQL慢查询日志，并分享后续的分析与管理方法。

慢查询日志的核心配置参数

MySQL的慢查询日志功能主要由几个核心参数控制，它们通常位于MySQL的配置文件"/etc/mysql/mysql.conf.d/mysqld.cnf"或"/etc/mysql/my.cnf"中。你需要关注的参数主要有以下三个：

1. "slow_query_log"：这是一个开关，设置为"ON"来启用慢查询日志，设置为"OFF"则关闭。

2. "slow_query_log_file"：这个参数指定慢查询日志文件的完整路径和文件名。你需要确保MySQL进程用户（通常是"mysql"）对这个路径有写入权限。

3. "long_query_time"：这是定义“慢查询”的阈值，单位是秒。默认是10秒，但生产环境中通常设置为1秒甚至更低（如0.5秒），以便捕捉更多潜在的性能问题。查询执行时间超过这个阈值的，都会被记录到日志中。

还有一个有用的参数是"log_queries_not_using_indexes"，如果设置为"ON"，它会将所有未使用索引的查询（即使执行时间很快）也记录到慢查询日志中，这对索引优化非常有帮助。

在Ubuntu上编辑MySQL配置文件

首先，使用你熟悉的文本编辑器（如"nano"或"vim"）打开MySQL的主配置文件。在终端中执行以下命令：

sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf

在文件中找到"[mysqld]"这个段落。如果上述参数不存在，你需要手动添加。一个典型的配置示例如下：

[mysqld]
...
# 慢查询日志配置
slow_query_log          = ON
slow_query_log_file     = /var/log/mysql/mysql-slow.log
long_query_time         = 2
log_queries_not_using_indexes = ON

在这个例子中，我们开启了慢查询日志，将日志文件设置在"/var/log/mysql/"目录下，设定慢查询阈值为2秒，并开启了记录未使用索引的查询。

应用配置并重启MySQL服务

配置文件修改保存后，必须重启MySQL服务才能使新的配置生效。在Ubuntu上，使用"systemctl"命令来操作：

sudo systemctl restart mysql

重启后，建议立即检查服务状态，确保MySQL已成功启动：

sudo systemctl status mysql

同时，你可以登录MySQL客户端，使用以下SQL命令验证慢查询日志是否已成功开启：

mysql -u root -p
SHOW VARIABLES LIKE 'slow_query_log%';
SHOW VARIABLES LIKE 'long_query_time';

如果"slow_query_log"的值为"ON"，并且"slow_query_log_file"指向你设置的路径，说明配置成功。

慢查询日志的权限与轮转管理

确保日志目录和文件的权限正确至关重要。通常，"/var/log/mysql/"目录的所有者和组应该是"mysql"。你可以通过以下命令检查和设置：

sudo chown -R mysql:mysql /var/log/mysql/
sudo chmod 755 /var/log/mysql/

慢查询日志会不断增长，如果不加管理，可能占用大量磁盘空间。在Ubuntu上，最优雅的方式是使用"logrotate"工具。系统通常已经为MySQL日志配置了轮转，配置文件在"/etc/logrotate.d/mysql-server"。你可以检查或编辑这个文件，确保它包含了你的慢查询日志文件。一个基本的配置片段如下：

/var/log/mysql/mysql-slow.log {
        daily
        rotate 7
        missingok
        create 640 mysql adm
        compress
        delaycompress
        postrotate
                test -x /usr/bin/mysqladmin || exit 0
                MYADMIN="/usr/bin/mysqladmin --defaults-file=/etc/mysql/debian.cnf"
                $MYADMIN ping &>/dev/null && $MYADMIN flush-logs
        endscript
}

这个配置表示日志每天轮转一次，保留最近7份，轮转后进行压缩，并在轮转后通知MySQL刷新日志。

使用mysqldumpslow工具进行初步分析

MySQL自带了一个非常实用的命令行工具"mysqldumpslow"，用于解析和汇总慢查询日志内容，让海量日志变得可读。它的常用命令格式如下：

# 查看执行时间最长的10条慢查询
sudo mysqldumpslow -s t -t 10 /var/log/mysql/mysql-slow.log

# 查看累计执行时间最长的10条查询
sudo mysqldumpslow -s at -t 10 /var/log/mysql/mysql-slow.log

# 查看锁定时间最长的10条查询
sudo mysqldumpslow -s l -t 10 /var/log/mysql/mysql-slow.log

# 按照出现次数排序查看
sudo mysqldumpslow -s c -t 10 /var/log/mysql/mysql-slow.log

该工具会将结构相似的SQL语句（即使参数不同）归类统计，输出其执行次数、总耗时、平均耗时、最大锁定时间等信息，帮助你快速找到最需要关注的“问题SQL”。

进阶分析：使用Percona Toolkit的pt-query-digest

对于更专业、更深入的分析，我强烈推荐使用Percona Toolkit中的"pt-query-digest"工具。它比"mysqldumpslow"功能强大得多，能提供一份极其详尽的报告。首先，你需要安装它：

sudo apt-get update
sudo apt-get install percona-toolkit

安装完成后，使用以下命令生成一份完整的分析报告：

sudo pt-query-digest /var/log/mysql/mysql-slow.log > slow_report.txt

打开"slow_report.txt"文件，你会看到：

1. 整体概要：包括日志时间段、总查询量、唯一查询数量、时间分布等。

2. 响应时间分布直方图。

3. 最严重的“问题查询”列表：对每一条被归类的SQL，报告会详细列出其执行次数、占总时间的百分比、平均/最小/最大执行时间、发送的行数、扫描的行数等关键指标。

这份报告能精准地告诉你，数据库的哪些SQL消耗了最多的总时间，是优化工作优先级排序的黄金标准。

从日志到优化：关键的后续步骤

开启和分析日志只是开始，真正的价值在于后续的优化行动：

1. 解读执行计划：从慢查询日志或"pt-query-digest"报告中找到目标SQL后，在MySQL客户端中使用"EXPLAIN"或"EXPLAIN FORMAT=JSON"命令来查看该SQL的执行计划。重点关注"type"列（扫描类型，应避免ALL全表扫描）、"key"列（是否使用了索引）、"rows"列（预估扫描行数）和"Extra"列（是否使用了文件排序、临时表等）。

2. 优化索引：这是解决慢查询最有效的手段。根据"EXPLAIN"结果和"WHERE"、"JOIN"、"ORDER BY"子句，创建或调整合适的索引。注意避免索引冗余，并理解最左前缀原则。

3. 重写SQL：有时，优化需要从SQL语句本身入手。例如，避免使用"SELECT *"，优化子查询为"JOIN"，避免在索引列上使用函数或计算，合理使用分页（对大偏移量"LIMIT"进行优化）等。

4. 调整架构：对于单条SQL已无法优化的复杂查询，或数据量极大的场景，可能需要考虑数据库架构调整，如引入读写分离、分库分表，或使用缓存（如Redis）来分担数据库压力。

动态开启与临时采样

除了修改配置文件，你还可以在MySQL运行时动态开启慢查询日志进行临时采样，这在生产环境调试时非常有用，无需重启服务：

SET GLOBAL slow_query_log = 'ON';
SET GLOBAL slow_query_log_file = '/tmp/temporary_slow.log';
SET GLOBAL long_query_time = 1;
SET GLOBAL log_queries_not_using_indexes = 'ON';

请注意，这些动态设置会在MySQL服务重启后失效，永久生效仍需修改配置文件。临时采样结束后，记得将"slow_query_log"设置为"OFF"。

总结与最佳实践建议

在Ubuntu上开启和管理MySQL慢查询日志是一项基础但至关重要的运维技能。为了使其效益最大化，我建议遵循以下最佳实践：

1. 阈值设置合理化：初期可将"long_query_time"设得稍低（如0.5-1秒），以便广泛收集问题SQL；后期优化见效后，可适当调高。

2. 结合未使用索引日志：在优化初期，同时开启"log_queries_not_using_indexes"，它能帮你发现那些虽然执行快但缺乏索引支持的查询，防患于未然。

3. 定期分析与归档：将日志分析作为一项常规工作，每周或每两周运行一次"pt-query-digest"，生成报告并归档。对比历史报告，可以清晰看到优化工作的成效。

4. 日志安全与清理：确保慢查询日志文件的安全，因为它可能包含敏感的SQL信息。通过"logrotate"做好自动轮转和清理，避免磁盘空间告警。

5. 融入监控体系：可以将慢查询数量、平均查询时间等指标，通过监控代理（如Prometheus exporter for MySQL）集成到你的整体监控告警系统中，实现主动性能管理。

通过系统地开启、分析并依据慢查询日志进行优化，你能显著提升Ubuntu上MySQL数据库的稳定性和响应速度，这是保障业务顺畅运行的核心环节之一。

Ubuntu运维中MySQL数据库慢查询日志开启

服务指南

帮助中心