查看慢查询日志的状态:
mysql> show variables like '%slow%';
+---------------------+-----------------------------+
| Variable_name | Value |
+---------------------+-----------------------------+
| log_slow_queries | ON |
| slow_launch_time | 2 |
| slow_query_log | ON |
| slow_query_log_file | /data1/mysql/mysql-slow.log |
+---------------------+-----------------------------+
4 rows in set (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)
先停慢查询功能:
mysql> set global slow_query_log=0;
Query OK, 0 rows affected (0.05 sec) mysql> show variables like '%slow%';
+---------------------+-----------------------------+
| Variable_name | Value |
+---------------------+-----------------------------+
| log_slow_queries | OFF |
| slow_launch_time | 2 |
| slow_query_log | OFF |
| slow_query_log_file | /data1/mysql/mysql-slow.log |
+---------------------+-----------------------------+
4 rows in set (0.00 sec)
重新设置慢查询日志的路径:
mysql> set global slow_query_log_file='/data1/mysql/mysql-slow01.log';
Query OK, 0 rows affected (0.00 sec)
开启慢查询工能:
mysql> set global slow_query_log=1;
Query OK, 0 rows affected (0.53 sec) mysql> show variables like '%slow%';
+---------------------+-------------------------------------------+
| Variable_name | Value |
+---------------------+-------------------------------------------+
| log_slow_queries | ON |
| slow_launch_time | 2 |
| slow_query_log | ON |
| slow_query_log_file | /data1/mysql/mysql-slow01.log |
+---------------------+-------------------------------------------+
4 rows in set (0.00 sec)
查看生成的慢查询日志:
# ls
cp_aurora ib_logfile1 mysql-bin. mysql.pid test
emove mysql mysql-bin. mysql-slow01.log zabbix
ibdata1 mysql-bin. mysql-bin.index mysql-slow.log
ib_logfile0 mysql-bin. mysql-error.log performance_schema