归档,在 MySQL 中,是一个相对高频的操作。
它通常涉及以下两个操作:
[En]
It usually involves the following two actions:
- 迁移。将数据从业务实例迁移到归档实例。
- 删除。从业务实例中删除已迁移的数据。
在处理类似需求时,都是开发童鞋提单给 DBA,由 DBA 来处理。
于是,很多开发童鞋就好奇,DBA 都是怎么执行归档操作的?归档条件没有索引会锁表吗?安全吗,会不会数据删了,却又没归档成功?
针对这些疑问,下面介绍 MySQL 中的数据归档神器 – pt-archiver。
本文主要包括以下几个部分:
[En]
This paper mainly includes the following parts:
- 什么是 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/
官员们已经为多个系统提供了现成的软件包。
[En]
Officials have provided off-the-shelf software packages for multiple systems.
我常用的是 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"
在命令行上指定了三个参数。
[En]
Three parameters are specified on the command line.
- –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
...
结合源库和目标库的日志,您可以看到
[En]
Combining the logs of the source and target libraries, you can see
- pt-archiver 首先会从源库查询一条记录,然后再将该记录插入到目标库中。 目标库插入成功,才会从源库中删除这条记录。 这样就能确保数据在删除之前,一定是归档成功的。
- 仔细观察这几个操作的执行时间,其先后顺序如下。 (1)源库查询记录。 (2)目标库插入记录。 (3)源库删除记录。 (4)目标库 COMMIT。 (5)源库 COMMIT。 这种实现借鉴了分布式事务中的两阶段提交算法。
- –where 参数中的 “1=1” 会传递到 SELECT 操作中。 “1=1” 代表归档全表,也可指定其它条件,如我们常用的时间。
- 每次查询都是使用主键索引,这样即使归档条件中没有索引,也不会产生全表扫描。
- 每次删除都是基于主键,这样可避免归档条件没有索引导致全表被锁的风险。
批量归档
如果使用 Demo 中的参数进行归档,在数据量比较大的情况下,效率会非常低,毕竟 COMMIT 是一个昂贵的操作。
所以在网上,我们通常做批量操作。
[En]
So online, we usually do batch operations.
具体命令如下:
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
与前面的存档命令相比,该命令指定了四个额外的参数,其中
[En]
Compared to the previous archive command, this command specifies four additional parameters, of which
- –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 是不会提示任何错误的。
不同归档参数的速度比较
[En]
Speed comparison between different archiving parameters
下表是归档 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
从表格中的数据中,我们可以得出以下几点:
[En]
From the data in the table, we can draw the following points:
- 第一种方式是最慢的。 这种情况下,无论是源库还是归档库,都是逐行操作并提交的。
- 只指定 –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,所以只会查询主键
事实上,不需要获取满足条件的所有主键值,只需取一个最小值和最大值。<details><summary>*<font color='gray'>[En]</font>*</summary>*<font color='gray'>In fact, there is no need to get all the primary key values that meet the conditions, just take a minimum and maximum value.</font>*</details>
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)将数据归档到文件中
数据不仅可以存档到数据库,还可以存档到文件。
[En]
Data can be archived not only to a database, but also to a file.
具体命令如下:
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。
文件名使用日期格式符号,支持的符号和含义如下:
[En]
The file name uses date formatting symbols, and the supported symbols and meanings are as follows:
%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 操作。
很多人担心删除的记录太多,会造成主从延迟。
[En]
Many people worry that if too many records are deleted, it will cause master-slave delay.
事实上,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
undefined
–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)字符集和表的字符集是否一致。
如果不一致,则会提示以下错误:
[En]
If it is inconsistent, the following error is prompted:
Character set mismatch: --source DSN uses latin1, table uses gbk. You can disable this check by specifying --no-check-charset.
这个时候,切记不要按照提示指定 –no-check-charset 忽略检查,否则很容易导致乱码。
针对上述报错,可将 –charset 指定为表的字符集。
请注意,此选项不会比较源库和目标库的字符集是否一致。
[En]
Note that this option does not compare whether the character sets of the source and target libraries are consistent.
–[no]check-columns
检查源表和目标表列名称是否匹配。
[En]
Check that the source and destination table column names match.
请注意,只检查列名,而不检查列的顺序和数据类型是否一致。
[En]
Note that only the column names are checked, not whether the order and data type of the columns are consistent.
–columns
归档指定列。
在有自增列的情况下,如果源表和目标表的自增列存在交集,可不归档自增列,这个时候,就需要使用 –columns 显式指定归档列。
–dry-run
只打印待执行的 SQL,不实际执行。
常用于实际操作之前,校验待执行的 SQL 是否符合自己的预期。
–ignore
使用 INSERT IGNORE 归档数据。
–no-delete
不删除源库的数据。
–replace
使用 REPLACE 操作归档数据。
–[no]safe-auto-increment
当存档具有自增量主键的表时,默认情况下不会删除具有最大自增量主键的行。
[En]
When archiving a table with a self-increment primary key, the row with the largest self-increment primary key is not deleted by default.
这样做,主要是为了规避 MySQL 8.0 之前自增主键不能持久化的问题。
在归档整个表时需要注意这一点。
[En]
This needs to be noted when archiving the entire table.
如果需要删除,需指定 –no-safe-auto-increment 。
–source
给出源端实例的信息。
除常用选项外,它还支持以下选项:
[En]
In addition to the common options, it also supports the following options:
- a:指定连接的默认数据库。
- b:设置 SQL_LOG_BIN=0 。 如果是在源库指定,则 DELETE 操作不会写入到 Binlog 中。 如果是在目标库指定,则 INSERT 操作不会写入到 Binlog 中。
- i:设置归档操作使用的索引,默认是主键。
–progress
按单位行数显示进度信息。
[En]
Displays progress information in lines per unit.
如 –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
第一列是当前时间,第二列是消耗的时间,第三列是已存档(删除)的行数。
[En]
The first column is the current time, the second column is the time consumed, and the third column is the number of rows that have been archived (deleted).
总结
之前,我们比较了归档操作中不同参数的执行时间。
[En]
Earlier, we compared the execution time of different parameters in the archive operation.
其中,–bulk-delete –limit 1000 –commit-each –bulk-insert 是最快的。不指定任何批量操作参数是最慢的。
但在使用 –bulk-insert 时要注意 ,如果导入的过程中出现问题,pt-archiver 是不会提示任何错误的。
常见错误是主键冲突以及数据和目标列之间的数据类型不一致。
[En]
Common errors are primary key conflicts and data types inconsistencies between the data and the target column.
如果不使用 –bulk-insert,而是通过默认的 INSERT 操作来归档,大部分错误是可以识别出来的。
例如,主键冲突将提示以下错误。
[En]
For example, a primary key conflict will prompt the following error.
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.
导入的数据与目标列的数据类型不一致,提示如下错误。
[En]
The imported data is inconsistent with the data type of the target column, which prompts the following error.
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 。
如果将该无效数据插入到打开了严格模式的归档实例中,则会直接报告错误。
[En]
If this invalid data is inserted into an archive instance with strict mode turned on, an error will be reported directly.
从数据安全的角度来看,最推荐的归档方式是:
[En]
From the perspective of data security, the most recommended archiving method is:
- 先归档,但不删除源库的数据。
- 比对源库和归档库的数据是否一致。
- 如果比对结果一致,再删除源库的归档数据。
其中,第一步和第三步可通过 pt-archiver 搞定,第二步可通过 pt-table-sync 搞定。
与这种边存档边删除的方式相比,虽然麻烦,但相对安全。
[En]
Compared with this way of archiving while deleting, although it is troublesome, it is relatively safer.
Original: https://www.cnblogs.com/ivictor/p/16001965.html
Author: iVictor
Title: MySQL 中如何归档数据
原创文章受到原创版权保护。转载请注明出处:https://www.johngo689.com/505568/
转载文章受原作者版权保护。转载请注明原作者出处!