一、【不停机在线开启慢查询，重启后失效】
###查看慢查询关闭状态
mysql> show variables like "%slow_query_log%";
+---------------------+------------------------------------------------+
| Variable_name | Value |
+---------------------+------------------------------------------------+
| slow_query_log | OFF |
| slow_query_log_file | /data/mysql/server1-slow.log |
+---------------------+------------------------------------------------+
###开启慢查询日志记录
mysql> set global slow_query_log=on;
Query OK, 0 rows affected (0.02 sec)
###确认慢查询已开启
mysql> show variables like "%slow_query_log%";
+---------------------+------------------------------------------------+
| Variable_name | Value |
+---------------------+------------------------------------------------+
| slow_query_log | ON |
| slow_query_log_file | /data/mysql/server1-slow.log |
+---------------------+------------------------------------------------+
###查看慢查询阈值时间定义
mysql> show variables like "%long_query_time%";
+-----------------+----------+
| Variable_name | Value |
+-----------------+----------+
| long_query_time | 5.000000 |
+-----------------+----------+
###修改查询时间超过阈值定义为慢查询
mysql> set global long_query_time=3;
Query OK, 0 rows affected (0.00 sec)
>quit //退出重新登录查看生效
###重新登录查看阈值生效
# mysql -uroot -p
mysql> show variables like "%long_query_time%";
+-----------------+----------+
| Variable_name | Value |
+-----------------+----------+
| long_query_time | 3.000000 |
+-----------------+----------+
###修改慢查询日志文件存放路径
mysql> set global slow_query_log_file= '/data/mysql/sql_slow.log' ;
Query OK, 0 rows affected (0.00 sec)
二、【配置文件中开启慢查询，永久生效】
###记录没有使用索引的查询 (记录文件和慢查询是同一个)
log_queries_not_using_indexes = 0 //配置文件中关闭记录没有使用索引查询的sql，不用记录0为关闭
slow_query_log = 1 //配置文件中定义开启慢查询
long_query_time = 3 //配置文件中定义慢查询时间阈值
