MySQL(88)如何设置慢查询日志？

慢查询日志记录了执行时间超过指定阈值的SQL查询，帮助识别和优化性能较差的查询。下面详细介绍如何设置慢查询日志，并结合具体的代码示例进行说明。

1. 配置慢查询日志

1.1 编辑MySQL配置文件

首先，你需要编辑MySQL的配置文件（通常为my.cnf或my.ini），以启用慢查询日志并配置相关参数。

在配置文件中添加或修改以下设置：

[mysqld]
# 启用慢查询日志
slow_query_log = 1

# 设置慢查询日志文件路径
slow_query_log_file = /var/log/mysql/mysql-slow.log

# 设置慢查询时间阈值（秒）
long_query_time = 2

# 可选：记录没有使用索引的查询
log_queries_not_using_indexes = 1

解释：

• slow_query_log = 1：启用慢查询日志。
• slow_query_log_file：指定慢查询日志文件的路径。
• long_query_time：设置查询执行时间超过多少秒被认为是慢查询。
• log_queries_not_using_indexes = 1：可选，记录没有使用索引的查询。

保存并关闭配置文件后，重启MySQL服务以应用这些更改：

sudo service mysql restart

1.2 动态设置慢查询日志

也可以通过SQL命令动态设置慢查询日志参数，而无需重启MySQL服务：

-- 启用慢查询日志
SET GLOBAL slow_query_log = 'ON';

-- 设置慢查询日志文件路径
SET GLOBAL slow_query_log_file = '/var/log/mysql/mysql-slow.log';

-- 设置慢查询时间阈值（秒）
SET GLOBAL long_query_time = 2;

-- 可选：记录没有使用索引的查询
SET GLOBAL log_queries_not_using_indexes = 'ON';

2. 验证和查看慢查询日志

2.1 验证配置

可以通过查询MySQL系统变量来验证慢查询日志的配置：

SHOW VARIABLES LIKE 'slow_query_log';
SHOW VARIABLES LIKE 'slow_query_log_file';
SHOW VARIABLES LIKE 'long_query_time';
SHOW VARIABLES LIKE 'log_queries_not_using_indexes';

2.2 查看慢查询日志

可以使用命令行工具如cat、less、more和tail查看慢查询日志文件内容。

• 使用cat查看完整的慢查询日志文件：

cat /var/log/mysql/mysql-slow.log

• 使用less或more分页查看慢查询日志文件：

less /var/log/mysql/mysql-slow.log

more /var/log/mysql/mysql-slow.log

• 使用tail查看慢查询日志文件的最后几行：

tail -n 100 /var/log/mysql/mysql-slow.log

• 使用tail -f实时跟踪慢查询日志文件的输出：

tail -f /var/log/mysql/mysql-slow.log

3. 清理慢查询日志

随着时间的推移，慢查询日志文件可能会变得非常大。为了防止日志文件占用过多磁盘空间，需要定期清理慢查询日志。

3.1 使用logrotate进行日志轮换

logrotate是Linux中的一个日志管理工具，可以自动进行日志文件的轮换、压缩和删除。

创建或编辑/etc/logrotate.d/mysql文件，添加以下内容来配置慢查询日志的轮换：

/var/log/mysql/mysql-slow.log {
    daily
    rotate 7
    missingok
    compress
    delaycompress
    notifempty
    create 640 mysql adm
    sharedscripts
    postrotate
        test -x /usr/bin/mysqladmin || exit 0
        if [ -f /var/run/mysqld/mysqld.pid ]; then
            /usr/bin/mysqladmin flush-logs
        fi
    endscript
}

解释：

• daily：每天轮换日志文件。
• rotate 7：保留最近7个日志文件。
• missingok：如果日志文件不存在，不报错。
• compress：轮换后压缩旧日志文件。
• delaycompress：推迟压缩到下一次轮换。
• notifempty：如果日志文件为空，不进行轮换。
• create 640 mysql adm：以指定权限创建新的日志文件。
• sharedscripts：共享脚本，只在所有日志文件处理完后执行一次。
• postrotate：在日志轮换后执行的脚本。

3.2 手动清理慢查询日志

如果不使用logrotate，也可以手动清理慢查询日志。

1. 重命名当前慢查询日志文件：

mv /var/log/mysql/mysql-slow.log /var/log/mysql/mysql-slow.log.1

2. 创建一个新的慢查询日志文件，并设置正确的权限：

touch /var/log/mysql/mysql-slow.log
chown mysql:mysql /var/log/mysql/mysql-slow.log
chmod 640 /var/log/mysql/mysql-slow.log

3. 通知MySQL重新打开日志文件：

FLUSH LOGS;

4. 完整示例脚本

以下是一个示例脚本，展示了如何配置、查看和清理MySQL慢查询日志：

配置慢查询日志

编辑MySQL配置文件（my.cnf或my.ini）：

[mysqld]
# 启用慢查询日志
slow_query_log = 1

# 设置慢查询日志文件路径
slow_query_log_file = /var/log/mysql/mysql-slow.log

# 设置慢查询时间阈值（秒）
long_query_time = 2

# 可选：记录没有使用索引的查询
log_queries_not_using_indexes = 1

保存并重启MySQL服务：

sudo service mysql restart

动态设置慢查询日志

通过SQL命令动态设置慢查询日志参数：

-- 启用慢查询日志
SET GLOBAL slow_query_log = 'ON';

-- 设置慢查询日志文件路径
SET GLOBAL slow_query_log_file = '/var/log/mysql/mysql-slow.log';

-- 设置慢查询时间阈值（秒）
SET GLOBAL long_query_time = 2;

-- 可选：记录没有使用索引的查询
SET GLOBAL log_queries_not_using_indexes = 'ON';

查看慢查询日志

# 查看慢查询日志
cat /var/log/mysql/mysql-slow.log

# 分页查看慢查询日志
less /var/log/mysql/mysql-slow.log

# 实时跟踪慢查询日志
tail -f /var/log/mysql/mysql-slow.log

清理慢查询日志

使用logrotate

创建或编辑/etc/logrotate.d/mysql文件，添加以下内容：

/var/log/mysql/mysql-slow.log {
    daily
    rotate 7
    missingok
    compress
    delaycompress
    notifempty
    create 640 mysql adm
    sharedscripts
    postrotate
        test -x /usr/bin/mysqladmin || exit 0
        if [ -f /var/run/mysqld/mysqld.pid ]; then
            /usr/bin/mysqladmin flush-logs
        fi
    endscript
}

手动清理

# 重命名当前慢查询日志文件
mv /var/log/mysql/mysql-slow.log /var/log/mysql/mysql-slow.log.1

# 创建新的慢查询日志文件
touch /var/log/mysql/mysql-slow.log
chown mysql:mysql /var/log/mysql/mysql-slow.log
chmod 640 /var/log/mysql/mysql-slow.log

# 通知MySQL重新打开日志文件
mysql -u root -p -e "FLUSH LOGS;"

小结

通过配置和管理MySQL慢查询日志，可以有效地监控和优化数据库的性能。合理设置慢查询时间阈值和日志轮换策略，确保日志文件不会占用过多磁盘空间，同时能够及时发现和解决性能问题，保持数据库系统的稳定运行。