应用背景:
MySQL慢查询日志功能默认是关闭的,通过开启慢查询日志,可以让MySQL记录下查询超过指定时间的语句,再通过分析定位问题瓶颈,优化查询提高数据库系统的性能。
测试环境:
centos7.4 / mysql5.6.40
参数说明:
slow_query_log: ON | OFF,开启或关闭慢查询功能;
slow_query_log_file: /PATH/TO/LOG_FILE,某指定路径下的文件;
long_query_time: TIME(单位:秒,默认10秒),查询语句执行后超过多少秒后就记录到慢查询日志中;
具体操作:
先查看一下相关参数
mysql> show variables like 'slow_query%';+---------------------+-----------------------------------+
| Variable_name | Value |
+---------------------+-----------------------------------+
| slow_query_log | OFF |
| slow_query_log_file | /var/lib/mysql/server-10-slow.log | //名字格式:一般为“主机名-slow.log”
+---------------------+-----------------------------------+
2 rows in set (0.00sec)
mysql> show variables like 'long_query%';+-----------------+-----------+
| Variable_name | Value |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+
1 row in set (0.00 sec)
修改方法1:在mysql交互命令行设置
开启慢查询功能
mysql> set global slow_query_log = ON;
修改慢查询日志存放路径
mysql> set global slow_query_log_file = '/var/lib/mysql/test-slow.log'; //引号别忘
修改慢查询时间
mysql> set global long_query_time = 2;
最后检查确认(退出,重新登录查看)
mysql> show variables like 'slow_query%';+---------------------+------------------------------+
| Variable_name | Value |
+---------------------+------------------------------+
| slow_query_log | ON |
| slow_query_log_file | /var/lib/mysql/test-slow.log |
+---------------------+------------------------------+
2 rows in set (0.01sec)
mysql> show variables like 'long_query%';+-----------------+----------+
| Variable_name | Value |
+-----------------+----------+
| long_query_time | 2.000000 |
+-----------------+----------+
1 row in set (0.01 sec)
修改方法2:在配置文件my.cnf文件中添加相关参数(需要重启mysql)
[mysqld]slow_query_log= ONslow_query_log_file= /var/lib/mysql/test-slow.loglong_query_time= 2
简单测试:
mysql> select sleep(5); //查询,睡5秒,模拟耗时(大于设定值2秒)+----------+
| sleep(5) |
+----------+
| 0 |
+----------+
1 row in set (5.00 sec)
查看慢查询日志记录内容
[root@server-10 ~]# cat /var/lib/mysql/test-slow.log/usr/sbin/mysqld, Version: 5.6.40-log (MySQL Community Server (GPL)). started with:
Tcp port:3306 Unix socket: /var/lib/mysql/mysql.sock
Time Id Command Argument
# Time:180906 14:38:54# User@Host: root[root] @ localhost [] Id:28# Query_time:5.000303 Lock_time: 0.000000 Rows_sent: 1 Rows_examined: 0SET timestamp=1536215934;select sleep(5);
那条查询超过2秒的select语句和耗时都被记录下来了。
结束.