默认,MySQL的慢查询日志功能是关闭的。
mysql> Show variables like 'slow_query%';
+---------------------+-------------------------------+
| Variable_name | Value |
+---------------------+-------------------------------+
| slow_query_log | OFF |
| slow_query_log_file | /var/lib/mysql/node1-slow.log |
+---------------------+-------------------------------+
2 rows in set (0.00 sec)
mysql>
开启MySQL的慢查询日志功能
mysql> set global slow_query_log='ON';
Query OK, 0 rows affected (0.00 sec)
mysql> Show variables like 'slow_query%';
+---------------------+-------------------------------+
| Variable_name | Value |
+---------------------+-------------------------------+
| slow_query_log | ON |
| slow_query_log_file | /var/lib/mysql/node1-slow.log |
+---------------------+-------------------------------+
2 rows in set (0.00 sec)
mysql>
mysql> show variables like 'long_query_time%';
+-----------------+-----------+
| Variable_name | Value |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+
1 row in set (0.00 sec)
long_query_time 默认为 10s。生产环境下,如果 SQL 的执行时间超过 1s,我们可以认为这条 SQL是比较慢,我们将long_query_time的值改为 2s
mysql> set global long_query_time = 2;
Query OK, 0 rows affected (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>
对于当前会话窗口,查看long_query_time值没有更新。我们需要新开一个会话窗口,可以查询到更新后的值。
在新的Session窗口中,执行一个3秒查询
mysql> select sleep(3);
+----------+
| sleep(3) |
+----------+
| 0 |
+----------+
1 row in set (3.01 sec)
mysql>
也可以多执行多个
[root@node1 ~]# cat /var/lib/mysql/node1-slow.log
/usr/sbin/mysqld, Version: 8.0.27 (MySQL Community Server - GPL). started with:
Tcp port: 3306 Unix socket: /var/lib/mysql/mysql.sock
Time Id Command Argument
# Time: 2021-11-14T19:41:41.225465Z
# User@Host: root[root] @ localhost [] Id: 31
# Query_time: 3.003578 Lock_time: 0.000000 Rows_sent: 1 Rows_examined: 1
SET timestamp=1636918898;
select sleep(3);
[root@node1 ~]#
[root@node1 ~]# mysqldumpslow -s at -t 1 /var/lib/mysql/node1-slow.log
Reading mysql slow query log from /var/lib/mysql/node1-slow.log
Count: 3 Time=8.00s (24s) Lock=0.00s (0s) Rows=1.0 (3), root[root]@localhost
select sleep(N)
[root@node1 ~]#
重启MySQL服务之后,上面相关设置丢失。
[root@node1 ~]# systemctl restart mysqld
[root@node1 ~]# mysql -uroot -p123456
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.27 MySQL Community Server - GPL
Copyright (c) 2000, 2021, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> Show variables like 'slow_query%';
+---------------------+-------------------------------+
| Variable_name | Value |
+---------------------+-------------------------------+
| slow_query_log | OFF |
| slow_query_log_file | /var/lib/mysql/node1-slow.log |
+---------------------+-------------------------------+
2 rows in set (0.00 sec)
mysql>
结论:通过变量方式启动MySQL相关功能,重启后恢复到默认配置。永久有效,则可以通过修改 MySQL 的配置文件 my.cnf。
[root@node1 ~]# vi /etc/my.cnf
在[mysqld]最后添加
slow-query-log = on
long_query_time = 2
然后重启MySQL服务
[root@node1 ~]# systemctl restart mysqld
[root@node1 ~]# mysql -uroot -p123456
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.27 MySQL Community Server - GPL
Copyright (c) 2000, 2021, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> Show variables like 'slow_query%';
+---------------------+-------------------------------+
| Variable_name | Value |
+---------------------+-------------------------------+
| slow_query_log | ON |
| slow_query_log_file | /var/lib/mysql/node1-slow.log |
+---------------------+-------------------------------+
2 rows in set (0.00 sec)
mysql> show variables like 'long_query_time%';
+-----------------+----------+
| Variable_name | Value |
+-----------------+----------+
| long_query_time | 2.000000 |
+-----------------+----------+
1 row in set (0.00 sec)
mysql>