您现在的位置是：网站首页> 编程资料编程资料

MySQL 中如何归档数据的实现方法_Mysql_

2023-05-26 384人已围观

简介 MySQL 中如何归档数据的实现方法_Mysql_

归档，在 MySQL 中，是一个相对高频的操作。

它通常涉及以下两个动作：

  • 迁移。将数据从业务实例迁移到归档实例。
  • 删除。从业务实例中删除已迁移的数据。

在处理类似需求时，都是开发童鞋提单给 DBA，由 DBA 来处理。

于是，很多开发童鞋就好奇，DBA 都是怎么执行归档操作的？归档条件没有索引会锁表吗？安全吗，会不会数据删了，却又没归档成功？

针对这些疑问，下面介绍 MySQL 中的数据归档神器 - pt-archiver。

本文主要包括以下几部分：

  • 什么是 pt-archiver
  • 安装
  • 简单入门
  • 实现原理
  • 批量归档
  • 不同归档参数之间的速度对比
  • 常见用法
  • 如何避免主从延迟
  • 常用参数

什么是 pt-archiver

pt-archiver 是 Percona Toolkit 中的一个工具。

Percona Toolkit 是 Percona 公司提供的一个 MySQL 工具包。工具包里提供了很多实用的 MySQL 管理工具。

譬如，我们常用的表结构变更工具 pt-online-schema-change ，主从数据一致性校验工具 pt-table-checksum 。

毫不夸张地说，熟练使用 Percona Toolkit 是 MySQL DBA 必备的技能之一。

安装

Percona Toolkit 下载地址：https://www.percona.com/downloads/percona-toolkit/LATEST/

官方针对多个系统提供了现成的软件包。

我常用的是 Linux - Generic 二进制包。

下面以 Linux - Generic 版本为例，看看它的安装方法。

# cd /usr/local/ # wget https://downloads.percona.com/downloads/percona-toolkit/3.3.1/binary/tarball/percona-toolkit-3.3.1_x86_64.tar.gz --no-check-certificate # tar xvf percona-toolkit-3.3.1_x86_64.tar.gz # cd percona-toolkit-3.3.1 # yum install perl-ExtUtils-MakeMaker perl-DBD-MySQL perl-Digest-MD5 # perl Makefile.PL # make # make install

简单入门

首先，我们看一个简单的归档 Demo。

测试数据

mysql> show create table employees.departments\G *************************** 1. row *************************** Table: departments Create Table: CREATE TABLE `departments` ( `dept_no` char(4) NOT NULL, `dept_name` varchar(40) NOT NULL, PRIMARY KEY (`dept_no`), UNIQUE KEY `dept_name` (`dept_name`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci 1 row in set (0.00 sec) mysql> select * from employees.departments; +---------+--------------------+ | dept_no | dept_name | +---------+--------------------+ | d009 | Customer Service | | d005 | Development | | d002 | Finance | | d003 | Human Resources | | d001 | Marketing | | d004 | Production | | d006 | Quality Management | | d008 | Research | | d007 | Sales | +---------+--------------------+ 9 rows in set (0.00 sec)

下面，我们将 employees.departments 表的数据从 192.168.244.10 归档到 192.168.244.128。

具体命令如下：

pt-archiver --source h=192.168.244.10,P=3306,u=pt_user,p=pt_pass,D=employees,t=departments --dest h=192.168.244.128,P=3306,u=pt_user,p=pt_pass,D=employees,t=departments --where "1=1"

命令行中指定了三个参数。

  • --source：源库（业务实例）的 DSN。

    DSN 在 Percona Toolkit 中比较常见，可理解为目标实例相关信息的缩写。

    支持的缩写及含义如下：

    缩写 含义 === ============================================= A 默认的字符集 D 库名 F 只从给定文件中读取配置信息，类似于MySQL中的--defaults-file P 端口 S 用于连接的socket文件 h 主机名 p 密码 t 表名 u 用户名

  • --dest：目标库（归档实例）的 DSN。

  • --where：归档条件。"1=1"代表归档全表。

实现原理

下面结合 General log 的输出看看 pt-archiver 的实现原理。

源库日志

2022-03-06T10:58:20.612857+08:00 10 Query SELECT /*!40001 SQL_NO_CACHE */ `dept_no`,`dept_name` FROM `employees`.`departments` FORCE INDEX(`PRIMARY`) WHERE (1=1) ORDER BY `dept_no` LIMIT 1 2022-03-06T10:58:20.613451+08:00 10 Query DELETE FROM `employees`.`departments` WHERE (`dept_no` = 'd001') 2022-03-06T10:58:20.620327+08:00 10 Query commit 2022-03-06T10:58:20.628409+08:00 10 Query SELECT /*!40001 SQL_NO_CACHE */ `dept_no`,`dept_name` FROM `employees`.`departments` FORCE INDEX(`PRIMARY`) WHERE (1=1) AND ((`dept_no` >= 'd001')) ORDER BY `dept_no` LIMIT 1 2022-03-06T10:58:20.629279+08:00 10 Query DELETE FROM `employees`.`departments` WHERE (`dept_no` = 'd002') 2022-03-06T10:58:20.636154+08:00 10 Query commit ...

目标库日志

2022-03-06T10:58:20.613144+08:00 18 Query INSERT INTO `employees`.`departments`(`dept_no`,`dept_name`) VALUES ('d001','Marketing') 2022-03-06T10:58:20.613813+08:00 18 Query commit 2022-03-06T10:58:20.628843+08:00 18 Query INSERT INTO `employees`.`departments`(`dept_no`,`dept_name`) VALUES ('d002','Finance') 2022-03-06T10:58:20.629784+08:00 18 Query commit ...

结合源库和目标库的日志，可以看到，

  • pt-archiver 首先会从源库查询一条记录，然后再将该记录插入到目标库中。

    目标库插入成功，才会从源库中删除这条记录。

    这样就能确保数据在删除之前，一定是归档成功的。

  • 仔细观察这几个操作的执行时间，其先后顺序如下。

    （1）源库查询记录。

    （2）目标库插入记录。

    （3）源库删除记录。

    （4）目标库 COMMIT。

    （5）源库 COMMIT。

    这种实现借鉴了分布式事务中的两阶段提交算法。

  • --where 参数中的 "1=1" 会传递到 SELECT 操作中。

    "1=1" 代表归档全表，也可指定其它条件，如我们常用的时间。

  • 每次查询都是使用主键索引，这样即使归档条件中没有索引，也不会产生全表扫描。

  • 每次删除都是基于主键，这样可避免归档条件没有索引导致全表被锁的风险。

批量归档

如果使用 Demo 中的参数进行归档，在数据量比较大的情况下，效率会非常低，毕竟 COMMIT 是一个昂贵的操作。

所以在线上，我们通常都会进行批量操作。

具体命令如下：

pt-archiver --source h=192.168.244.10,P=3306,u=pt_user,p=pt_pass,D=employees,t=departments --dest h=192.168.244.128,P=3306,u=pt_user,p=pt_pass,D=employees,t=departments --where "1=1" --bulk-delete --limit 1000 --commit-each --bulk-insert

相对于之前的归档命令，这条命令额外指定了四个参数，其中，

  • --bulk-delete：批量删除。

  • --limit：每批归档的记录数。

  • --commit-each：对于每一批记录，只会 COMMIT 一次。

  • --bulk-insert：归档数据以 LOAD DATA INFILE 的方式导入到归档库中。

看看上述命令对应的 General log 。

源库

2022-03-06T12:13:56.117984+08:00 53 Query SELECT /*!40001 SQL_NO_CACHE */ `dept_no`,`dept_name` FROM `employees`.`departments` FORCE INDEX(`PRIMARY`) WHERE (1=1) ORDER BY `dept_no` LIMIT 1000 ... 2022-03-06T12:13:56.125129+08:00 53 Query DELETE FROM `employees`.`departments` WHERE (((`dept_no` >= 'd001'))) AND (((`dept_no` <= 'd009'))) AND (1=1) LIMIT 1000 2022-03-06T12:13:56.130055+08:00 53 Query commit

目标库

2022-03-06T12:13:56.124596+08:00 51 Query LOAD DATA LOCAL INFILE '/tmp/hitKctpQTipt-archiver' INTO TABLE `employees`.`departments`(`dept_no`,`dept_name`) 2022-03-06T12:13:56.125616+08:00 51 Query commit：

注意：

  • 如果要执行 LOAD DATA LOCAL INFILE 操作，需将目标库的 local_infile 参数设置为 ON。

  • 如果不指定 --bulk-insert 且没指定 --commit-each，则目标库的插入还是会像 Demo 中显示的那样，逐行提交。

  • 如果不指定 --commit-each，即使表中的 9 条记录是通过一条 DELETE 命令删除的，但因为涉及了 9 条记录，pt-archiver 会执行 COMMIT 操作 9 次。目标库同样如此。

  • 在使用 --bulk-insert 归档时要注意，如果导入的过程中出现问题，譬如主键冲突，pt-archiver 是不会提示任何错误的。

不同归档参数之间的速度对比

下表是归档 20w 数据，不同参数之间的执行时间对比。

归档参数执行时间（s）
不指定任何批量相关参数850.040
--bulk-delete --limit 1000422.352
--bulk-delete --limit 1000 --commit-each46.646
--bulk-delete --limit 5000 --commit-each46.111
--bulk-delete --limit 1000 --commit-each --bulk-insert7.650
--bulk-delete --limit 5000 --commit-each --bulk-insert6.540
--bulk-delete --limit 1000 --bulk-insert47.273

通过表格中的数据，我们可以得出以下几点：

  • 第一种方式是最慢的。

    这种情况下，无论是源库还是归档库，都是逐行操作并提交的。

  • 只指定 --bulk-delete --limit 1000 依然很慢。

    这种情况下，源库是批量删除，但 COMMIT 次数并没有减少。

    归档库依然是逐行插入并提交的。

  • --bulk-delete --limit 1000 --commit-each

    相当于第二种归档方式，源库和目标库都是批量提交的。

  • --limit 1000 和 --limit 5000 归档性能相差不大。

  • --bulk-delete --limit 1000 --bulk-insert 与 --bulk-delete --limit 1000 --commit-each --bulk-insert 相比，没有设置 --commit-each。

    虽然都是批量操作，但前者会执行 COMMIT 操作 1000 次。

    由此来看，空事务并不是没有代价的。

其它常见用法

（1）删除数据

删除数据是 pt-archiver 另外一个常见的使用场景。

具体命令如下：

pt-archiver --source h=192.168.244.10,P=3306,u=pt_user,p=pt_pass,D=employees,t=departments --where "1=1" --bulk-delete --limit 1000 --commit-each --purge --primary-key-only

命令行中的 --purge 代表只删除，不归档。

指定了 --primary-key-only ，这样，在执行 SELECT 操作时，就只会查询主键，不会查询所有列。

接下来，我们看看删除命令相关的 General log 。

为了直观地展示 pt-archiver 删除数据的实现逻辑，实际测试时将 --limit 设置为了 3。

# 开启事务 set autocommit=0; # 查看表结构，获取主键 SHOW CREATE TABLE `employees`.`departments`; # 开始删除第一批数据 # 通过 FORCE INDEX(`PRIMARY`) 强制使用主键 # 指定了 --primary-key-only，所以只会查询主键 # 这里其实无需获取所有满足条件的主键值，只取一个最小值和最大值即可。 SELECT /*!40001 SQL_NO_CACHE */ `dept_no` FROM `employees`.`departments` FORCE INDEX(`PRIMARY`) WHERE (1=1) ORDER BY `dept_no` LIMIT 3; # 基于主键进行删除，删除的时候同时带上了 --where 指定的删除条件，以避免误删 DELETE FROM `employees`.`departments`
                
                

                

                                            
上一篇：关于k8s环境部署mysql主从的问题_Mysql_

                                                                
下一篇：MySQL实现JDBC详细步骤_Mysql_

相关内容

点击排行

本栏推荐

猜你喜欢

-六神源码网