查看慢查询日志的状态:
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.000057 mysql.pid test emove mysql mysql-bin.000058 mysql-slow01.log zabbix ibdata1 mysql-bin.000055 mysql-bin.index mysql-slow.log ib_logfile0 mysql-bin.000056 mysql-error.log performance_schema