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 1000	422.352
–bulk-delete –limit 1000 –commit-each	46.646
–bulk-delete –limit 5000 –commit-each	46.111
–bulk-delete –limit 1000 –commit-each –bulk-insert	7.650
–bulk-delete –limit 5000 –commit-each –bulk-insert	6.540
–bulk-delete –limit 1000 –bulk-insert	47.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` where (((`dept_no` >= 'd001'))) and (((`dept_no` <= 'd003'))) and (1=1) limit 3;

# 提交
commit;

# 删除第二批数据
select /*!40001 sql_no_cache */ `dept_no` from `employees`.`departments` force index(`primary`) where (1=1) and ((`dept_no` >= 'd003')) order by `dept_no` limit 3;
delete from `employees`.`departments` where (((`dept_no` >= 'd004'))) and (((`dept_no` <= 'd006'))) and (1=1); limit 3
commit;


# 删除第三批数据
select /*!40001 sql_no_cache */ `dept_no` from `employees`.`departments` force index(`primary`) where (1=1) and ((`dept_no` >= 'd006')) order by `dept_no` limit 3;
delete from `employees`.`departments` where (((`dept_no` >= 'd007'))) and (((`dept_no` <= 'd009'))) and (1=1) limit 3;
commit;

# 删除最后一批数据
select /*!40001 sql_no_cache */ `dept_no` from `employees`.`departments` force index(`primary`) where (1=1) and ((`dept_no` >= 'd009')) order by `dept_no` limit 3;
commit;

在业务代码中，如果我们有类似的删除需求，不妨借鉴下 pt-archiver 的实现方式。

（2）将数据归档到文件中

数据除了能归档到数据库，也可归档到文件中。

具体命令如下：

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 --file '/tmp/%y-%m-%d-%d.%t'

指定的是 –file ，而不是 –dest。

文件名使用了日期格式化符号，支持的符号及含义如下：

%d    day of the month, numeric (01..31)
%h    hour (00..23)
%i    minutes, numeric (00..59)
%m    month, numeric (01..12)
%s    seconds (00..59)
%y    year, numeric, four digits
%d    database name
%t    table name

生成的文件是 csv 格式，后续可通过 load data infile 命令加载到数据库中。

如何避免主从延迟

无论是数据归档还是删除，对于源库，都需要执行 delete 操作。

很多人担心，如果删除的记录数太多，会造成主从延迟。

事实上，pt-archiver 本身就具备了基于主从延迟来自动调节归档（删除）操作的能力。

如果从库的延迟超过 1s（由 –max-lag 指定）或复制状态不正常，则会暂停归档（删除）操作，直到从库恢复。

默认情况下，pt-archiver 不会检查从库的延迟情况。

如果要检查，需通过 –check-slave-lag 显式设置从库的地址，譬如，

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 --primary-key-only --purge --check-slave-lag h=192.168.244.20,p=3306,u=pt_user,p=pt_pass

这里只会检查 192.168.244.20 的延迟情况。

如果有多个从库需要检查，需将 –check-slave-lag 指定多次，每次对应一个从库。

常用参数

–analyze

在执行完归档操作后，执行 analyze table 操作。

后面可接任意字符串，如果字符串中含有 s ，则会在源库执行 analyze 操作。

如果字符串中含有 d ，则会在目标库执行 analyze 操作。

如果同时带有 d 和 s ，则源库和目标库都会执行 analyze 操作。如，

--analyze ds

–optimize

在执行完归档操作后，执行 optimize table 操作。

用法同 –analyze 类似。

–charset

指定连接（connection）字符集。

在 mysql 8.0 之前，默认是 latin1。

在 mysql 8.0 中，默认是 utf8mb4 。

注意，这里的默认值与 mysql 服务端字符集 character_set_server 无关。

若显式设置了该值，pt-archiver 在建立连接后，会首先执行 set names ‘charset_name’ 操作。

–[no]check-charset

检查源库（目标库）连接（connection）字符集和表的字符集是否一致。

如果不一致，会提示以下错误：

character set mismatch: --source dsn uses latin1, table uses gbk.  you can disable this check by specifying --no-check-charset.

这个时候，切记不要按照提示指定  –no-check-charset 忽略检查，否则很容易导致乱码。

针对上述报错，可将 –charset 指定为表的字符集。

注意，该选项并不是比较源库和目标库的字符集是否一致。

–[no]check-columns

检查源表和目标表列名是否一致。

注意，只会检查列名，不会检查列的顺序、列的数据类型是否一致。

–columns

归档指定列。

在有自增列的情况下，如果源表和目标表的自增列存在交集，可不归档自增列，这个时候，就需要使用 –columns 显式指定归档列。

–dry-run

只打印待执行的 sql，不实际执行。

常用于实际操作之前，校验待执行的 sql 是否符合自己的预期。

–ignore

使用 insert ignore 归档数据。

–no-delete

不删除源库的数据。

–replace

使用 replace 操作归档数据。

–[no]safe-auto-increment

在归档有自增主键的表时，默认不会删除自增主键最大的那一行。

这样做，主要是为了规避 mysql 8.0 之前自增主键不能持久化的问题。

在对全表进行归档时，这一点需要注意。

如果需要删除，需指定 –no-safe-auto-increment 。

–source

给出源端实例的信息。

除了常用的选项，其还支持如下选项：

• a：指定连接的默认数据库。

• b：设置 sql_log_bin=0 。

  如果是在源库指定，则 delete 操作不会写入到 binlog 中。

  如果是在目标库指定，则 insert 操作不会写入到 binlog 中。

• i：设置归档操作使用的索引，默认是主键。

–progress

显示进度信息，单位行数。

如 –progress 10000，则每归档（删除）10000 行，就打印一次进度信息。

time                elapsed   count
2022-03-06t18:24:19       0       0
2022-03-06t18:24:20       0   10000
2022-03-06t18:24:21       1   20000

第一列是当前时间，第二列是已经消耗的时间，第三列是已归档（删除）的行数。

总结

前面，我们对比了归档操作中不同参数的执行时间。

其中，–bulk-delete –limit 1000 –commit-each –bulk-insert 是最快的。不指定任何批量操作参数是最慢的。

但在使用 –bulk-insert 时要注意 ，如果导入的过程中出现问题，pt-archiver 是不会提示任何错误的。

常见的错误有主键冲突，数据和目标列的数据类型不一致。

如果不使用 –bulk-insert，而是通过默认的 insert 操作来归档，大部分错误是可以识别出来的。

譬如，主键冲突，会提示以下错误。

dbd::mysql::st execute failed: duplicate entry 'd001' for key 'primary' [for statement "insert into `employees`.`departments`(`dept_no`,`dept_name`) values (?,?)" with paramvalues: 0='d001', 1='marketing'] at /usr/local/bin/pt-archiver line 6772.

导入的数据和目标列的数据类型不一致，会提示以下错误。

dbd::mysql::st execute failed: incorrect integer value: 'marketing' for column 'dept_name' at row 1 [for statement "insert into `employees`.`departments`(`dept_no`,`dept_name`) values (?,?)" with paramvalues: 0='d001', 1='marketing'] at /usr/local/bin/pt-archiver line 6772.

当然，数据和类型不一致，能被识别出来的前提是归档实例的 sql_mode 为严格模式。

如果待归档的实例中有 mysql 5.6 ，我们其实很难将归档实例的 sql_mode 开启为严格模式。

因为 mysql 5.6 的 sql_mode 默认为非严格模式，所以难免会产生很多无效数据，譬如时间字段中的 0000-00-00 00:00:00 。

这种无效数据，如果插入到开启了严格模式的归档实例中，会直接报错。

从数据安全的角度出发，最推荐的归档方式是：

• 先归档，但不删除源库的数据。
• 比对源库和归档库的数据是否一致。
• 如果比对结果一致，再删除源库的归档数据。

其中，第一步和第三步可通过 pt-archiver 搞定，第二步可通过 pt-table-sync 搞定。

相对于边归档边删除的这种方式，虽然麻烦不少，但相对来说，更安全。

到此这篇关于mysql 中如何归档数据的实现方法的文章就介绍到这了,更多相关mysql  归档数据内容请搜索www.887551.com以前的文章或继续浏览下面的相关文章希望大家以后多多支持www.887551.com！