mysql配置慢日志查询

1.先查询是否开启慢日志

mysql> show variables like "%slow%";
+---------------------------+---------------------------------+
| Variable_name             | Value                           |
+---------------------------+---------------------------------+
| log_slow_admin_statements | OFF                             |
| log_slow_slave_statements | OFF                             |
| slow_launch_time          | 2                               |
| slow_query_log            | OFF                             |
| slow_query_log_file       | /var/lib/mysql/tencent-slow.log |
+---------------------------+---------------------------------+
5 rows in set (0.00 sec)

• slow_query_log |OFF 表示未开启
• slow_query_log_file 慢日志存储位置

2.临时开启

#将开关开启
mysql> set global slow_query_log='ON'; 

#配置日志文件存储位置
mysql> set global slow_query_log_file='/var/lib/mysql/slow.log'; 

#设置查询超过1秒就记录（如果有时候用命令不起作用，那么可以关闭再打开）
mysql> set global long_query_time=1;

3.永久开启

修改配置文件my.cnf，在[mysqld]下的下方加入

slow_query_log = ON
slow_query_log_file = /var/lib/mysql/slow.log    
long_query_time = 1

修改完之后执行重启命令service mysqld restart

4.测试

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

去慢查询日志里面查看,可以看到刚才的查询已经被记录了

$ tail -f /var/lib/mysql/d44dd071e008-slow.log
mysqld, Version: 5.7.30-log (MySQL Community Server (GPL)). started with:
Tcp port: 3306  Unix socket: /var/run/mysqld/mysqld.sock
Time                 Id Command    Argument

# Time: 2021-04-21T03:26:52.789379Z
# User@Host: root[root] @ localhost []  Id:     7
# Query_time: 2.003942  Lock_time: 0.000000 Rows_sent: 1  Rows_examined: 0
SET timestamp=1618975612;
select sleep(2);