Mysql开启慢查询日志

1.查看当前慢查询开关是否开启

mysql> show variables like "%slow_query%";
+---------------------+-----------------------------------------------------+
| Variable_name       | Value                                               |
+---------------------+-----------------------------------------------------+
| slow_query_log      | OFF                                                 |
| slow_query_log_file | /usr/local/mysql/data/wodembpdeMacBook-Pro-slow.log |
+---------------------+-----------------------------------------------------+

2.打开慢查询开关

mysql> set global slow_query_log="ON";
Query OK, 0 rows affected (0.01 sec)

mysql> show variables like "%slow_query%";
+---------------------+-----------------------------------------------------+
| Variable_name       | Value                                               |
+---------------------+-----------------------------------------------------+
| slow_query_log      | ON                                                  |
| slow_query_log_file | /usr/local/mysql/data/wodembpdeMacBook-Pro-slow.log |
+---------------------+-----------------------------------------------------+

3.修改慢查询时间

mysql> show variables like "%long_query_time%";
+-----------------+-----------+
| Variable_name   | Value     |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+
1 row in set (0.01 sec)
mysql> set global long_query_time=3;
mysql> show variables like "%long_query_time%";
+-----------------+----------+
| Variable_name   | Value    |
+-----------------+----------+
| long_query_time | 3.000000 |
+-----------------+----------+
1 row in set (0.00 sec)

注意：修改long_query_time时候，当前会话理解查询可能看不到修改，重新打开会话查询就可以看到修改结果

4.重启mysql

mysqld restart

5. 执行慢sql

mysql> select sleep(4);
+----------+
| sleep(4) |
+----------+
|        0 |
+----------+
1 row in set (4.02 sec)

5.查看日志

wodembpdeMacBook-Pro:~ wodembp$ sudo tail -f /usr/local/mysql/data/wodembpdeMacBook-Pro-slow.log
/usr/local/mysql/bin/mysqld, Version: 8.0.13 (MySQL Community Server - GPL). started with:
Tcp port: 3306  Unix socket: /tmp/mysql.sock
Time                 Id Command    Argument
# Time: 2019-10-24T07:22:23.886956Z
# User@Host: root[root] @ localhost []  Id:    11
# Query_time: 4.020252  Lock_time: 0.000000 Rows_sent: 1  Rows_examined: 0
SET timestamp=1571901743;
select sleep(4);