数据库慢查询配置

慢查询日志配置

默认情况下slow_query_log的值为OFF，表示慢查询日志是禁用的，可以通过设置slow_query_log的值来开启，如下所示：

mysql> show variables like '%slow_query_log%';
+---------------------+-----------------------------------------------+
| Variable_name | Value |
+---------------------+-----------------------------------------------+
| slow_query_log | OFF |
| slow_query_log_file | /home/WDPM/MysqlData/mysql/DB-Server-slow.log |
+---------------------+-----------------------------------------------+
2 rows in set (0.00 sec)

mysql> set global slow_query_log=1; //开启慢查询
Query OK, 0 rows affected (0.09 sec)

mysql> show variables like '%slow_query_log%';
+---------------------+-----------------------------------------------+
| Variable_name | Value |
+---------------------+-----------------------------------------------+
| slow_query_log | ON |
| slow_query_log_file | /home/WDPM/MysqlData/mysql/DB-Server-slow.log |
+---------------------+-----------------------------------------------+
2 rows in set (0.00 sec)

mysql> show variables　like　'long_query_time%'; //慢查询记录时间

mysql> show variables like 'long_query_time%';
+-----------------+-----------+
| Variable_name | Value |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+
1 row in set (0.00 sec)

mysql> set global long_query_time=4;
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like 'long_query_time';
+-----------------+-----------+
| Variable_name | Value |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+
1 row in set (0.00 sec)

show variables like '%log_output%'; //慢查询输出方式

mysql永久开启了慢查询日志功能

编辑配置文件/etc/my.cnf加入如下内容

[mysqld]

slow_query_log = ON

slow_query_log_file = /var/lib/mysql/test-10-226-slow.log

long_query_time = 1

参考文档来源：https://blog.csdn.net/shukebai/article/details/70064287

修改配置后重启mysql

systemctl restart mysqld

show variables like 'slow_query%';

mysql永久开启了慢查询日志功能