mysql 慢查询监控工具

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

-- 设置慢查询阈值（单位：秒）
SET GLOBAL long_query_time = 2;

-- 查看慢查询日志配置
SHOW VARIABLES LIKE 'slow_query_log%';
SHOW VARIABLES LIKE 'long_query_time';

-- 定期清理慢查询日志
PURGE BINARY LOGS BEFORE '2023-01-01 00:00:00';

-- 或者设置日志轮转
SET GLOBAL log_bin_trust_function_creators = 1;
CREATE EVENT IF NOT EXISTS log_rotate_event
ON SCHEDULE EVERY 1 DAY
DO
BEGIN
    RENAME MASTER TO MASTER_OLD, MASTER TO MASTER_NEW;
    SET GLOBAL read_only = ON;
    FLUSH TABLES WITH READ LOCK;
    RENAME MASTER_NEW TO MASTER;
    SET GLOBAL read_only = OFF;
    UNLOCK TABLES;
END;

# 安装Percona Toolkit
sudo apt-get install percona-toolkit

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