MySQL 8.0慢查询日志实验
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> 本文参与 腾讯云自媒体分享计划，分享自作者个人站点/博客。
原始发表：2021-11-15 ，如有侵权请联系 cloudcommunity@tencent.com 删除
评论
登录后参与评论
推荐阅读
目录
相关产品与服务
云数据库 SQL Server
腾讯云数据库 SQL Server （TencentDB for SQL Server）是业界最常用的商用数据库之一，对基于 Windows 架构的应用程序具有完美的支持。TencentDB for SQL Server 拥有微软正版授权，可持续为用户提供最新的功能，避免未授权使用软件的风险。具有即开即用、稳定可靠、安全运行、弹性扩缩等特点。