MySQL 8.0.30 新功能,再也不用担心大量无效日志了!
作者:李富强,爱可生 DBA 团队成员,熟悉 MySQL,TiDB,OceanBase 等数据库。相信持续把对的事情做好一点,会有不一样的收获。
爱可生开源社区出品,原创内容未经授权不得随意使用,转载请联系小编并注明来源。
本文约 1600 字,预计阅读需要 5 分钟。
新特性介绍
MySQL 8.0.30 版本中,mysqldump 逻辑备份工具引入了 mysqld-long-query-time 选项,用于设置 mysqldump 备份的会话级别慢查询阈值 long_query_time
。
生产环境 long_query_time
一般设置的比较小,由于 mysqldump 备份时执行的是全表扫描,SQL 执行时间很容易超过 long_query_time
,导致慢查询日志中记录大量备份产生的慢查询。通过指定 mysqld-long-query-time
选项,给 mysqldump 单独设定合适的会话级别慢查询阈值,可以减少慢查询日志中大量无效慢查询。
使用方法
mysqldump 指定备份选项 mysqld-long-query-time
为具体的值,该选项取值范围为 0 到 31536000(365天),单位是秒。如果不指定此选项,mysqldump 产生的查询是否属于慢查询根据全局变量 long_query_time
阈值来确定。
# 添加 mysqldump 备份选项为 --mysqld-long-query-time=100
# 即小于 100s 的 mysqldump 备份语句不会记录到慢查询日志中
mysqldump -ubackup_user -h10.186.58.39 -P3000 -p123456 --all-databases
--mysqld-long-query-time=100 >/opt/all_databases.sql
对比实验
mysqldump 不添加备份选项 mysqld-long-query-time
和添加备份选项 mysqld-long-query-time
对比测试,分别观察慢查询日志内容。
环境信息
#数据库服务器配置
8c16g150g(ssd)
#数据库版本
MySQL localhost:3000 ssl SQL > select @@version;
+-----------+
| @@version |
+-----------+
| 8.0.30 |
+-----------+
#mysqldump版本
[root@node1 ~]# mysqldump --version
mysqldump Ver 8.0.30 for Linux on x86_64 (MySQL Community Server - GPL)
#全局慢查询阈值
MySQL localhost:3000 ssl SQL > show variables like '%long_query_time%';
+-----------------+----------+
| Variable_name | Value |
+-----------------+----------+
| long_query_time | 0.100000 |
+-----------------+----------+
#慢查询已开启
MySQL localhost:3000 ssl SQL > show variables like '%slow_query_log%';
+---------------------+-------------------------------------------------------+
| Variable_name | Value 服务器托管 |
+---------------------+-------------------------------------------------------+
| slow_query_log | ON |
| slow_query_log_file | /root/mysql-sandboxes/3000/sandboxdata/node1-slow.log |
+---------------------+-------------------------------------------------------+
#使用sysbench工具,分别造了数据量为10w,100w,1000w,50000w共4张表
MySQL localhost:3000 ssl SQL > select count(*) from fq_10w.sbtest1;
+----------+
| count(*) |
+----------+
| 100000 |
+----------+
1 row in set (0.1108 sec)
MySQL localhost:3000 ssl SQL > select count(*) from fq_100w.sbtest1;
+----------+
| count(*) |
+----------+
| 1000000 |
+----------+
1 row in set (0.3367 sec)
MySQL localhost:3000 ssl SQL > select count(*) from fq_1000w.sbtest1;
+----------+
| count(*) |
+----------+
| 10000000 |
+----------+
1 row in set (2.3046 sec)
MySQL localhost:3000 ssl SQL > select count(*) from fq_5000w.sbtest1;
+----------+
| count(*) |
+----------+
| 50000000 |
+----------+
1 row in set (8.7858 sec)
场景一
mysqldump 不添加备份选项 mysqld-long-query-time
进行数据库备份。
#执行备份
[root@node1 ~]# mysqldump -ubackup_user -h10.186.58.39 -P3000 -p123456 --all-databases >/opt/all_databases.sql
#备份完成后,查看慢日志,可以看到备份产生的查询的Query_time只要超过全局变量long_query_time(0.1s)的值就会记录到慢查询日志中
[root@node1 ~]# cat /root/mysql-sandboxes/3000/sandboxdata/node1-slow.log
# Time: 2024-03-20T06:25:51.689799Z
# User@Host: backup_user[backup_user] @ node1 [10.186.58.39] Id: 31
# Query_time: 77.574346 Lock_time: 0.000000 Rows_sent: 50000000 Rows_examined: 50000000
use fq_5000w;
SET timestamp=1710915874;
SELECT /*!40001 SQL_NO_CACHE */ * FROM `sbtest1`;
# Time: 2024-03-20T06:37:00.974869Z
# User@Host: backup_user[backup_user] @ node1 [10.186.58.39] Id: 32
# Query_time: 18.428685 Lock_time: 0.000000 Rows_sent: 10000000 Rows_examined: 10000000
use fq_1000w;
SET timestamp=1710916602;
SELECT /*!40001 SQL_NO_CACHE */ * FROM `sbtest1`;
# Time: 2024-03-20T06:37:02.571939Z
# User@Host: backup_user[backup_user] @ node1 [10.186.58.39] Id: 32
# Query_time: 1.527482 Lock_time: 0.000000 Rows_sent: 1000000 Rows_examined: 1000000
use fq_100w;
SET timestamp=1710916621;
SELECT /*!40001 SQL_NO_CACHE */ * FROM `sbtest1`;
# Time: 2024-03-20T06:37:02.754243Z
# User@Host: backup_user[backup_user] @ node1 [10.186.58.39] Id: 32
# Query_time: 0.125747 Lock_time: 0.000000 Rows_sent: 100000 Rows_examined: 100000
use fq_10w;
SET timestamp=1710916622;
SELECT /*!40001 SQL_NO_CACHE */ * FROM `sbtest1`;
场景二
mysqldump 添加备份选项 mysqld-long-query-time
进行数据库备份。
#清空慢日志文件
[root@node1 ~]# cat /dev/null > /root/mysql-sandboxes/3000/sandboxdata/node1-slow.log
[root@node1 ~]# cat /root/mysql-sandboxes/3000/sandboxdata/node1-slow.log
[root@node1 ~]#
#执行备份,根据场景1得知,备份5000w的表耗时77.57s,这里我们指定备份选项mysqld-long-query-time为100,尝试把备份本身产生的慢查询过滤掉
[root@node1 ~]# mysqldump -ubackup_user -h10.186.58.39 -P3000 -p123456 --all-databases --mysqld-long-query-time=100 >/opt/all_databases.sql
#备份完成后,查看慢日志,可以看到慢日志文件为空,备份本身产生的慢查询被过滤掉了
[root@node1 ~]# cat /root/mysql-sandboxes/3000/sandboxdata/node1-slow.log
[root@node1 ~]#
结论
MySQL 8.0.30 版本,通过给 mysqldump 指定 mysqld-long-query-time
选项,设定合适的会话级别慢查询阈值,可以过滤掉备份产生的慢查询。
知识补充
- MySQL 8.0.30 版本的 mysqldump 备份工具中的
mysqld-long-query-time
选项,也适用于 MySQL 5.7 版本的备份。 -
mysqldump 的
mysqld-long-query-time
选项的值不支持小数(eg:0.1),否则执行备份会报错。
更多技术文章,请访问:https://opensource.actionsky.com/
关于 SQLE
SQLE 是一款全方位的 SQL 质量管理平台,覆盖开发至生产环境的 SQL 审核和管理。支持主流的开源、商业、国产数据库,为开发和运维提供流程自动化能力,提升上线效率,提高数据质量。
SQLE 获取
类型 | 地址 |
---|---|
版本库 | https://github.com/actiontech/sqle |
文档 | https:/服务器托管/actiontech.github.io/sqle-docs/ |
发布信息 | https://github.com/actiontech/sqle/releases |
数据审核插件开发文档 | https://actiontech.github.io/sqle-docs/docs/dev-manual/plugins/howtouse |
服务器托管,北京服务器托管,服务器租用 http://www.fwqtg.net