Mysql 开启慢查询日志(一)、慢查询日志切割

Mysql 开启慢查询日志

一、背景说明

①、系统版本：

[root@mysql-106 mysql]# cat /etc/redhat-release
CentOS Linux release 8.2.2004 (Core)

②、Mysql 版本：

mysql> select version();
+------------+
| version()  |
+------------+
| 5.7.31-log |
+------------+
1 row in set (0.00 sec)

二、查询mysql 是否已开启慢查询

①、查询是否开启慢查询

show variables like ‘slow_query%’;

mysql> show variables like 'slow_query_log';
+----------------+-------+
| Variable_name  | Value |
+----------------+-------+
| slow_query_log | OFF   |
+----------------+-------+
1 row in set (0.39 sec)

②、查询sql 语句超过多少秒计入慢查询

show variables like ‘long_query_time’;

mysql> show variables like 'long_query_time';
+-----------------+-----------+
| Variable_name   | Value     |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+
1 row in set (0.00 sec)

③、查询慢查询日志存放路径

show variables like ‘slow_query_log%’;

mysql> show variables like 'slow_query_log%';
+---------------------+--------------------------------+
| Variable_name       | Value                          |
+---------------------+--------------------------------+
| slow_query_log      | OFF                            |
| slow_query_log_file | /data/mysql/mysql-106-slow.log |
+---------------------+--------------------------------+
2 rows in set (0.00 sec)

三、开启慢查询（临时开启，重启mysql失效）

①、设置记录慢查询日志状态为ON

将 slow_query_log 全局变量设置为“ON”状态
set global slow_query_log=‘ON’;

mysql> set global slow_query_log='ON';
Query OK, 0 rows affected (0.48 sec)

mysql> show variables like 'slow_query_log%';
+---------------------+--------------------------------+
| Variable_name       | Value                          |
+---------------------+--------------------------------+
| slow_query_log      | ON                             |
| slow_query_log_file | /data/mysql/mysql-106-slow.log |
+---------------------+--------------------------------+
2 rows in set (0.00 sec)

②、设置慢查询日志路径

set global slow_query_log_file=’/data/mysql/mysql-106-slow.log’;

mysql> set global slow_query_log_file='/data/mysql/mysql-106-slow.log';
Query OK, 0 rows affected (0.00 sec)

③、设置Sql语句执行超过1秒就计入慢查询

mysql> set global long_query_time=1;
Query OK, 0 rows affected (0.00 sec)

④、验证使用下面命令验证

show variables like ‘%quer%’;

mysql开启了漫查询日志功能

mysql> show variables like '%quer%';
+----------------------------------------+--------------------------------+
| Variable_name                          | Value                          |
+----------------------------------------+--------------------------------+
| binlog_rows_query_log_events           | OFF                            |
| ft_query_expansion_limit               | 20                             |
| have_query_cache                       | YES                            |
| log_queries_not_using_indexes          | OFF                            |
| log_throttle_queries_not_using_indexes | 0                              |
| long_query_time                        | 1.000000                       |
| query_alloc_block_size                 | 8192                           |
| query_cache_limit                      | 1048576                        |
| query_cache_min_res_unit               | 4096                           |
| query_cache_size                       | 1048576                        |
| query_cache_type                       | OFF                            |
| query_cache_wlock_invalidate           | OFF                            |
| query_prealloc_size                    | 8192                           |
| slow_query_log                         | ON                             |
| slow_query_log_file                    | /data/mysql/mysql-106-slow.log |
+----------------------------------------+--------------------------------+
15 rows in set (0.74 sec)

四、永久开启慢查询日志记录

找到my.cnf，添加如下内容sudo vim /etc/mysql/mysql.cnf

# 添加慢查询日志
[mysqld]
slow_query_log = ON  #开启慢查询
slow_query_log_file = /data/mysql/mysql-106-slow.log #慢查询日志存储路径     //linux
long_query_time = 1  #sql 超过1秒进行记录

修改并重启mysql

systemctl restart mysql

五、验证慢查询日志是否有记录

[root@mysql-106 mysql]# cat /data/mysql/mysql-106-slow.log

# Time: 2021-08-04T06:37:43.543153Z
# User@Host: user_risk[user_risk] @  [10.20.11.100]  Id: 7181860
# Query_time: 3.149760  Lock_time: 0.000449 Rows_sent: 0  Rows_examined: 247468
SET timestamp=1628059063;
select


        id, user_name, user_phone_num, user_address, user_sex, user_card_num, user_card_age,
        user_card_nation, user_card_past_due, user_address_longitude, user_address_latitude,
        user_work_unit_name, user_work_unit_address, user_work_unit_address_longitude, user_work_unit_address_latitude,
        user_work_unit_verify, user_work_unit_verify_matching, user_work_unit_firsttime,
        user_work_unit_lasttime, user_real_name_authentication, operator_type, operator_withhold,
        operator_phone_num_real_name, operator_phone_num_network_time, operator_phone_num_network_state,

六、慢查询日志切割

由于生产环境数据库量比较大，记录慢查询的时间只要超过1秒就记录，所以一天下来慢查询日志文件达到500多M，如果不做切割日志文件月累越大将无法打开。
这里制作一个脚本，通过定时任务每天凌晨将慢查询日志mv到指定路径。

①、慢查询日志切割脚本

vim mysql_slow_log.sh

#!/bin/bash
time=`date -d yesterday +"%Y-%m-%d"`
user="root"
passwd="*******"
#提前创建好一个存放目录：/var/lib/mysql/slow_log/
mv /data/mysql/mysql-106-slow.log /data/log/slow_log/server-106-slow-$time.log
/usr/local/mysql/bin/mysqladmin -u$user -p$passwd -h127.0.0.1 -P3309 flush-logs slow

②、定时任务

每天凌晨1点过两分定时进行慢查询日志切割。

[root@mysql-106 autoshell]# crontab -l
02 01 * * * /mnt/autoshell/mysql_slow_log.sh