面试题：MySQL慢查询日志存储与清理的深度优化

Question

假设MySQL服务器处于资源紧张的环境，既要确保慢查询日志完整记录且存储高效，又要实现自动化且低损耗的清理机制，结合MySQL底层原理和操作系统特性，阐述一套完整的解决方案，并说明方案中可能存在的风险及应对措施。

Answer 1

解决方案

1. 慢查询日志配置
   • 调整日志记录阈值：通过 long_query_time 参数设置合理的慢查询时间阈值。例如，将其设置为 2 秒，表示查询执行时间超过 2 秒的将被记录到慢查询日志。这一参数可以在 my.cnf 配置文件中修改，如：

[mysqld]
long_query_time = 2

• 开启慢查询日志：在 my.cnf 中确保 slow_query_log 参数开启，即 slow_query_log = 1。同时，可以设置 slow_query_log_file 参数指定慢查询日志文件路径，例如：

slow_query_log_file = /var/log/mysql/slow - query.log

2. 存储优化
   • 日志轮转：利用操作系统的日志轮转工具，如 logrotate。在 /etc/logrotate.d/ 目录下创建一个针对 MySQL 慢查询日志的配置文件，例如 mysql - slow - query，内容如下：

/var/log/mysql/slow - query.log {
    daily
    missingok
    rotate 7
    compress
    delaycompress
    notifempty
    create 640 mysql mysql
    sharedscripts
    postrotate
        /usr/bin/mysqladmin flush - logs
    endscript
}

• 日志格式优化：MySQL 8.0 及以上版本支持 JSON 格式的慢查询日志。相比传统文本格式，JSON 格式更紧凑，解析效率更高。在 my.cnf 中设置 log_output = JSON，并确保 slow_query_log 开启。

3. 自动化清理机制
   • 使用 logrotate：上述 logrotate 配置会按天自动轮转日志文件，保留 7 天的日志，新日志文件会被压缩，以节省磁盘空间。
   • 数据库内清理：可以通过创建一个定期运行的存储过程，从数据库中清理已经处理过的慢查询记录（如果有相关存储）。例如：

DELIMITER //
CREATE PROCEDURE clean_slow_query_history()
BEGIN
    DELETE FROM slow_query_history WHERE query_date < CURDATE() - INTERVAL 7 DAY;
END //
DELIMITER ;

然后使用 event scheduler 来定期执行这个存储过程：

SET GLOBAL event_scheduler = ON;
CREATE EVENT clean_slow_query_event
ON SCHEDULE EVERY 1 DAY
STARTS CURDATE() + INTERVAL 1 DAY
DO
CALL clean_slow_query_history();

可能存在的风险及应对措施

1. 日志丢失风险
   • 风险：在日志轮转过程中，如果 mysqladmin flush - logs 命令执行失败，可能导致新的慢查询日志记录丢失。
   • 应对措施：在 logrotate 的 postrotate 脚本中添加错误处理。例如：

postrotate
    /usr/bin/mysqladmin flush - logs || {
        logger -t logrotate -p local0.error "Failed to flush MySQL slow query logs"
        exit 1
    }
ends cript

2. 磁盘空间耗尽风险
   • 风险：如果日志增长速度过快，超过了 logrotate 配置的保留策略，可能导致磁盘空间耗尽。
   • 应对措施：监控磁盘空间使用情况，设置合理的告警阈值。例如，可以使用 df -h 命令结合 cron 任务定期检查磁盘空间，当空间使用率超过 80% 时发送告警邮件。同时，可以根据实际情况动态调整 logrotate 中的 rotate 参数，增加日志保留天数。
3. JSON 格式兼容性风险
   • 风险：如果使用 JSON 格式日志，旧版本的 MySQL 客户端或分析工具可能无法正确解析日志。
   • 应对措施：在使用 JSON 格式日志时，确保相关的日志分析工具和客户端支持 JSON 格式。如果必须与旧版本工具兼容，可以保留部分旧格式日志，或者开发中间层工具将 JSON 格式日志转换为旧格式。