提供了编程的基础技术教程

zl程序教程

您现在的位置是：首页 >  其他

当前栏目

MySQL 8.0慢查询日志实验

日志
2023-03-14 22:52:11 时间

1、开启慢查询日志

默认，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>

2、设置慢查询超时时间

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值没有更新。我们需要新开一个会话窗口，可以查询到更新后的值。

3、测试慢查询

在新的Session窗口中，执行一个3秒查询

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

mysql>

也可以多执行多个

4、查询慢日志

[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 ~]#

5、慢日志分析工具-mysqldumpslow

[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 ~]#

6、重启失效

重启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。

7、修改配置文件

[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>

相关文章