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

2024-04-08 300

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业界、互联网、市场资讯、驱动更新,是最及时权威的产业资讯及硬件资讯报道平台。

MySQL 核心模块揭秘 | 12 期 | 创建 savepoint

回滚操作，除了回滚整个事务，还可以部分回滚。部分回滚，需要保存点（savepoint）的协助。本文我们先看看保存点里面都有什么。作者：操盛春，爱可生技术专家，公众号『一树一溪』作者，专注于研究 MySQL 和 OceanBase 源码。爱可生开源社区出品，原创内容未经授权不得随意使用，转载请联系小编并注明来源本文基于 MySQL 8.0.32 源码，存储引擎为 InnoDB。 1. undo 日志序号 InnoDB 的事务对象有一个名为 undo_no 的属性。事务每次改变（插入、更新、删除）某个表的一条记录，都会产生一条 undo 日志。这条 undo 日志中会存储它自己的序号。这个序号就来源于事务对象的 undo_no 属性。也就是说，事务对象的 undo_no 属性中保存着事务改变（插入、更新、删除）某个表中下一条记录产生的 undo 日志的序号。每个事务都维护着各自独立的 undo 日志序号，和其它事务无关。每个事务的 undo 日志序号都从 0 开始。事务产生的第 1 条 undo 日志的序号为 0，第 2 条 undo 日志的序号为 1，依此类推。 InnoDB...

2024-04-07

271

很多小伙伴可能都用过 MyBatis-Plus，这里边我们构造 where 条件的时候，可以直接通过方法引用的方式去指定属性名： LambdaQueryWrapper<Book> qw = new LambdaQueryWrapper<>(); qw.eq(Book::getId, 2); List<Book> list = bookMapper.selectList(qw); System.out.println("list = " + list); Book::getId 这就是方法引用，松哥之前也专门写过文章介绍相关内容，这里就不再多说。这里我们就单纯来说说为什么 MP 通过 Book::getId 就可以识别出来这里的属性名。 1. 源码分析这个问题其实好解决，我们顺着 qw.eq 这个方法往下看就可以了，这个方法在执行的过程中几经辗转会来到 getColumnCache 方法中，这个方法就是解析出来属性值的地方。 protected ColumnCache getColumnCache(SFunction<T, ?> colu...

2024-04-08

289

资源下载

更多资源

Mario

马里奥是站在游戏界顶峰的超人气多面角色。马里奥靠吃蘑菇成长，特征是大鼻子、头戴帽子、身穿背带裤，还留着胡子。与他的双胞胎兄弟路易基一起，长年担任任天堂的招牌角色。

Nacos

Nacos /nɑ:kəʊs/ 是 Dynamic Naming and Configuration Service 的首字母简称，一个易于构建 AI Agent 应用的动态服务发现、配置管理和AI智能体管理平台。Nacos 致力于帮助您发现、配置和管理微服务及AI智能体应用。Nacos 提供了一组简单易用的特性集，帮助您快速实现动态服务发现、服务配置、服务元数据、流量管理。Nacos 帮助您更敏捷和容易地构建、交付和管理微服务平台。

Spring

Spring框架（Spring Framework）是由Rod Johnson于2002年提出的开源Java企业级应用框架，旨在通过使用JavaBean替代传统EJB实现方式降低企业级编程开发的复杂性。该框架基于简单性、可测试性和松耦合性设计理念，提供核心容器、应用上下文、数据访问集成等模块，支持整合Hibernate、Struts等第三方框架，其适用范围不仅限于服务器端开发，绝大多数Java应用均可从中受益。

Sublime Text

Sublime Text具有漂亮的用户界面和强大的功能，例如代码缩略图，Python的插件，代码段等。还可自定义键绑定，菜单和工具栏。Sublime Text 的主要功能包括：拼写检查，书签，完整的 Python API ， Goto 功能，即时项目切换，多选择，多窗口等等。Sublime Text 是一个跨平台的编辑器，同时支持Windows、Linux、Mac OS X等操作系统。