热门搜索:
您现在的位置是：首页 > 文章详情

mysqldump 备份产生大量慢查询，有办法过滤么？

日期：2024-04-08点击：227

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
原文链接：https://my.oschina.net/actiontechoss/blog/11051276
关注公众号

低调大师中文资讯倾力打造互联网数据资讯、行业资源、电子商务、移动互联网、网络营销平台。

持续更新报道IT业界、互联网、市场资讯、驱动更新,是最及时权威的产业资讯及硬件资讯报道平台。

转载内容版权归作者及来源网站所有，本站原创内容转载请注明来源。

相关文章

    文章评论

    共有0条评论来说两句吧...

    文章二维码

    扫描即可查看该文章

    点击排行

    推荐阅读

    最新文章