MySQL归档 pt-archiver 工具

共计 9239 个字符，预计需要花费 24 分钟才能阅读完成。

# tar -zxvf percona-toolkit-2.2.17.tar.gz
# yum -y install perl perl-IO-Socket-SSL perl-DBD-MySQL perl-Time-HiRes perl-TermReadKey
# yum -y install perl-Digest-MD5

[root@linuxidc bin]# pwd
/data/soft/percona-toolkit-2.2.17/bin

pt-archiver– 将表数据归档到另一个表或文件中
删除或归档一张大表，导出文件等，可以进行主从同步数据
[mysql@mysqlt1 bin]$ ./pt-archiver –help
Archive all rows from oltp_server to olap_server and to a file:
pt-archiver –source h=oltp_server,D=test,t=tbl –dest h=olap_server \
–file ‘/var/log/archive/%Y-%m-%d-%D.%t’ \
–where “1=1” –limit 1000 –commit-each
Purge (delete) orphan rows from child table:
pt-archiver –source h=host,D=db,t=child –purge \
–where ‘NOT EXISTS(SELECT * FROM parent WHERE col=child.col)’

参数说明：注意：至少需要指定 –dest,–file,–purge 其中的一个
–ignore and –replace are mutually exclusive.
–txn-size and –commit-each are mutually exclusive.
–low-priority-insert and –delayed-insert are mutually exclusive.
–share-lock and –for-update are mutually exclusive.
–analyze and –optimize are mutually exclusive.
–no-ascend and –no-delete are mutually exclusive.

–source：指定要归档表的信息，兼容 DSN 选项
—source h=my_server,D=my_database,t=my_tbl
b：如果为 true，则使用 SQL_LOG_BIN 禁用 binlog–If true, disable binlog with SQL_LOG_BIN.
i：进行操作时，被指定使用的索引 –Index to use.
h=host,D=database,t=table,u=user,p=password,P=port,S=socket
A:Default character set
L:Explicitly enable LOAD DATA LOCAL INFILE.
m:Plugin module name
–source D=test,t=test1,m=My::Module1 –dest m=My::Module2,t=test2
–analyze:Run ANALYZE TABLE afterwards on –source and/or –dest ###–analyze=ds
–ascend-first:Ascend only first column of index ### 升序索引优化，提供最左索引（多列主键）的升序。
–no-ascend:Do not use ascending index optimization.
–ask-pass:Prompt for a password when connecting to MySQL. ## 连接 mysql 时输入密码
–buffer：缓冲区输出到 –file 并在提交时刷新，每次事务提交禁止刷写到磁盘，有操作系统决定刷写。该参数可以提高刷写到文件的性能，但崩溃可能会有数据丢失。
–commit-each：Commit each set of fetched and archived rows (disables –txn-size).## 控制事务大小，每次提取、归档就提交。禁用 –txn-size
–config：以逗号分隔的配置文件列表; 如果指定，则必须是命令行上的第一个选项
–delayed-insert：Add the DELAYED modifier to INSERT statements## 在 insert 后面添加 delayed，延迟写入
–dry-run：Print queries and exit without doing anything## 打印查询并退出而不做任何事情
–file：File to archive to, with DATE_FORMAT()-like formatting
%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
Example:-file ‘/var/log/archive/%Y-%m-%d-%D.%t’
–for-update：Adds the FOR UPDATE modifier to SELECT statements.
–ignore：insert 语句加入 ignore
–no-delete：不要删除存档的行，默认会删除。不允许 –no-ascend，因为启用它们都会导致无限循环。
–progress：每多少行打印进度信息：打印当前时间，已用时间以及每 X 行存档的行数
–purge：清除而不是归档; 允许省略 –file 和 –dest。如果只想清除行，请考虑使用 –primary-key-only 指定表的主键列。这样可以防止无缘无故地从服务器获取所有列
–quick-delete：delete 语句里添加 quick
–replace：replace into 代替 insert into
–statistics：收集并打印时间统计信息
–txn-size：每个事务的行数，默认 1。指定每个事务的大小（行数）。0 完全禁用事务。在 pt-archiver 处理这么多行之后，如果指定该参数，它会提交 –source 和 –dest，并刷新 –file 给出的文件。
–where：指定 WHERE 子句以限制存档的行。子句里不要包含单词 WHERE，不需要 WHERE 子句，请使用 –where 1=1。如
–where ‘ts < current_date – interval 90 day’
–limit：限制检索要归档的行的 SELECT 语句返回的行数，默认是 1。这可能会导致与其他查询的更多争用，具体取决于存储引擎，事务隔离级别和 –for-update 等选项。
–bulk-delete：使用单个 DELETE 语句批量删除每个行块。该语句删除块的第一行和最后一行之间的每一行，隐含 –commit-each. 批量删除 source 上的旧数据
–bulk-insert: 批量插入数据到 dest 主机
–charset：-A，设置默认字符集
–why-quit：除非行耗尽，否则打印退出原因
使用场景
注意：归档的表大小写敏感，表必须至少有一个索引（Cannot find an ascendable index in table）。
这里需要注意的是，根据自增 id 进行归档的话，默认最大的 id 不会进行归档，需要添加参数：–no-safe-auto-increment 才能对最大 id 进行处理。
–where: 删除表中指定的数据，根据自己的需求限定，全部删除就给 1 = 1 即可
–statistics: 打印出整个归档过程的统计信息
–limit: 每次 fecth 多少行数据，类似游标获取，默认为 1。增改该值，有助于加速归档 –limit 10000 每次取 1000 行数据给 pt-archive 处理
–progress: 打印导出过程中的信息，当前时间，当前一共耗费多少时间，当前 fetch 数据行数，–progress 5000 每处理 5000 行输出一次处理信息
–txn-size: 每个事物提交的数据行数，批量提交。增加该值可以提升归档性能。–txn-size 1000 设置 1000 行为一个事务提交一次
–local: 不把 optimize 或 analyze 操作写入到 binlog 里面（防止造成主从延迟巨大）
–analyze=ds: 操作结束后，优化表空间（d 表示 dest，s 表示 source）, 默认情况下，pt-archiver 操作结束后，不会对 source、dest 表执行 analyze 或 optimize 操作

10.15.7.114 :mysql version=5.6.15,charset=utf8

192.168.19.145:mysql version=5.7.22,charset=utf8mb4

1 导出到文件，不删除源数据
2019-03-26T02:02:14 0 0
Cannot find encoding “utf8mb4” at /usr/lib64/perl5/IO/File.pm line 182.
Cannot open :encoding(utf8mb4) /tmp/2019-03-26-test.t1: Invalid argument

[mysql@mysqlt1 bin]$ ./pt-archiver –source u=system,p=**,h=10.15.7.114,P=3306,D=test,t=t1 –file=/tmp/%Y-%m-%d-%D.%t –where=”1=1″ \
> –no-delete –no-safe-auto-increment –progress=1000 –statistics –no-check-charset
TIME ELAPSED COUNT
2019-03-26T02:04:10 0 0
2019-03-26T02:04:10 0 1000
2019-03-26T02:04:10 0 1000
Started at 2019-03-26T02:04:10, ended at 2019-03-26T02:04:10
Source: D=test,P=3306,h=10.15.7.114,p=…,t=t1,u=system
SELECT 1000
INSERT 0
DELETE 0
Action Count Time Pct
select 1001 0.1258 58.07
commit 1001 0.0316 14.57
print_file 1000 0.0015 0.68
other 0 0.0578 26.68
[mysql@mysqlt1 bin]$ ll /tmp/2019-03-26-test.t1
-rw-rw-r– 1 mysql mysql 11679 Mar 26 02:04 /tmp/2019-03-26-test.t1
2 删除，不导出和迁移
[mysql@mysqlt1 bin]$ ./pt-archiver –source u=system,p=**,h=10.15.7.114,P=3306,D=test,t=t1 –where=”1=1″ –purge \
> –no-safe-auto-increment –progress=1000 –statistics –no-check-charset
TIME ELAPSED COUNT
2019-03-26T02:07:49 0 0
2019-03-26T02:07:50 1 1000
2019-03-26T02:07:50 1 1000
Started at 2019-03-26T02:07:49, ended at 2019-03-26T02:07:50
Source: D=test,P=3306,h=10.15.7.114,p=…,t=t1,u=system
SELECT 1000
INSERT 0
DELETE 1000
Action Count Time Pct
commit 1001 1.5619 81.38
deleting 1000 0.1455 7.58
select 1001 0.1315 6.85
other 0 0.0804 4.19
(system@127.0.0.1:3306) [test]> select count(*) from test.t1;
+———-+
| count(*) |
+———-+
| 0 |
3 全表归档，源表不删除，非批量
DBD::mysql::st execute failed: Duplicate entry ‘1’ for key ‘PRIMARY’ [for Statement “INSERT INTO `test`.`t37`(`id`,`a`,`b`) VALUES (?,?,?)” with ParamValues: 0=’1′, 1=’1′, 2=’1′] at ./pt-archiver line 6563.
‘1’, 1=’1′, 2=’1′] at ./pt-archiver line 6563.
[mysql@mysqlt1 bin]$ ./pt-archiver –source u=system,p=**,h=10.15.7.114,P=3306,D=test,t=t1 –dest u=system,p=**,h=192.168.19.145,P=3306,D=test,t=t37 –where=”1=1″ –progress=1000 –statistics –no-delete –no-check-charset
TIME ELAPSED COUNT
2019-03-26T02:20:09 0 0
2019-03-26T02:20:11 2 1000
2019-03-26T02:20:11 2 1000
Started at 2019-03-26T02:20:09, ended at 2019-03-26T02:20:11
Source: D=test,P=3306,h=10.15.7.114,p=…,t=t1,u=system
Dest: D=test,P=3306,h=192.168.19.145,p=…,t=t37,u=system
SELECT 1000
INSERT 1000
DELETE 0
Action Count Time Pct
commit 2002 1.6199 70.67
inserting 1000 0.4391 19.16
select 1001 0.1419 6.19
other 0 0.0913 3.98
全表归档，源表不删除，批量插入
[mysql@mysqlt1 bin]$ ./pt-archiver –source u=system,p=**,h=10.15.7.114,P=3306,D=test,t=t1 –dest u=system,p=**,h=192.168.19.145,P=3306,D=test,t=t37 \
> –where=”1=1″ –limit=1000 –statistics –bulk-insert –txn-size=1000 –no-delete –no-check-charset
Started at 2019-03-26T02:22:48, ended at 2019-03-26T02:22:48
Source: D=test,P=3306,h=10.15.7.114,p=…,t=t1,u=system
Dest: D=test,P=3306,h=192.168.19.145,p=…,t=t37,u=system
SELECT 1000
INSERT 1000
DELETE 0
Action Count Time Pct
bulk_inserting 1 0.0544 61.78
commit 4 0.0034 3.84
select 2 0.0015 1.69
print_bulkfile 1000 -0.0017 -1.91
other 0 0.0304 34.61
5 全表归档，源表删除，批量插入，批量删除
./pt-archiver –source u=system,p=**,h=10.15.7.114,P=3306,D=test,t=t1 –dest u=system,p=**,h=192.168.19.145,P=3306,D=test,t=t37 \
–where=”1=1″ –limit=1000 –statistics –bulk-insert –bulk-delete –txn-size=1000 –no-delete –no-check-charset
6 指定条件归档，源表删除，批量（每 1000 个插入提交一次）
./pt-archiver –source u=system,p=**,h=10.15.7.114,P=3306,D=test,t=t1 –dest u=system,p=**,h=192.168.19.145,P=3306,D=test,t=t37 \
–where=”id<=49999″ –limit=1000 –statistics –bulk-insert –bulk-delete –txn-size=1000 –no-delete –no-check-charset
7 指定索引的归档，不走自增主键索引。参数：i
./pt-archiver –source u=system,p=**,h=10.15.7.114,P=3306,D=test,t=t1,i=a –dest u=system,p=***,h=192.168.19.145,P=3306,D=test,t=t37 \
–where=”a >=80000 and a<100000″ –limit=1000 –statistics –bulk-insert –bulk-delete –txn-size=1000 –no-delete –no-check-charset
8 有从库的归档
./pt-archiver –source u=system,p=**,h=10.15.7.114,P=3306,D=test,t=t1,i=a –dest u=system,p=**,h=192.168.19.145,P=3306,D=test,t=t37 \
–where=”a >=80000 and a<100000″ –limit=1000 –statistics –bulk-insert –bulk-delete –txn-size=1000 –no-delete –no-check-charset \
–max-lag=1 –check-slave-lag u=system,p=**,h=10.15.7.115,P=3306
9 不做任何操作, 只打印要执行的查询语句
[mysql@mysqlt1 bin]$ ./pt-archiver –source u=system,p=**,h=10.15.7.114,P=3306,D=test,t=t1,i=a –dest u=system,p=**,h=192.168.19.145,P=3306,D=test,t=t37 \> –where=”a >=1000 and a<3000″ –limit=1000 –replace –statistics –txn-size=1000 –no-delete –no-check-charset –dry-run
SELECT /*!40001 SQL_NO_CACHE */ `id`,`a`,`b` FROM `test`.`t1` FORCE INDEX(`a`) WHERE (a >=1000 and a<3000) ORDER BY `a` LIMIT 1000
SELECT /*!40001 SQL_NO_CACHE */ `id`,`a`,`b` FROM `test`.`t1` FORCE INDEX(`a`) WHERE (a >=1000 and a<3000) AND ((((? IS NULL AND `a` IS NOT NULL) OR (`a` > ?)))) ORDER BY `a` LIMIT 1000
REPLACE INTO `test`.`t37`(`id`,`a`,`b`) VALUES (?,?,?)
10 常用的命令：归档到另一个数据库，源表删除，批量删除和插入，每 1000 次修改进行提交。跳过错误并且指定字符集连接
[mysql@mysqlt1 bin]$ ./pt-archiver –source u=system,p=**,h=10.15.7.114,P=3306,D=test,t=t1,i=a –dest u=system,p=**,h=192.168.19.145,P=3306,D=test,t=t37 –no-version-check\
> –where=”a >=500 and a<800″ –ignore –txn-size=200 –limit=200 –bulk-delete –bulk-insert –progress=5000 –statistics –why-quit –no-check-charset
TIME ELAPSED COUNT
2019-03-26T03:13:10 0 0
2019-03-26T03:13:11 0 300
Started at 2019-03-26T03:13:10, ended at 2019-03-26T03:13:11
Source: D=test,P=3306,h=10.15.7.114,i=a,p=…,t=t1,u=system
Dest: D=test,P=3306,h=192.168.19.145,i=a,p=…,t=t37,u=system
SELECT 300
INSERT 300
DELETE 300
Action Count Time Pct
commit 4 0.0081 23.57
bulk_inserting 2 0.0061 17.83
bulk_deleting 2 0.0032 9.38
select 3 0.0013 3.74
print_bulkfile 300 -0.0011 -3.28
other 0 0.0168 48.75
Exiting because there are no more rows.
可以根据实际情况，进行相关参数的调整。另外其他相关参数说明
–ignore 或则 –replace：归档冲突记录跳过或则覆盖，批量插入的时候因为是 load data，索引看不到主键冲突记录的报错。要是非批量插入，则需要添加。
–sleep：指定两次 SELECT 语句的 sleep 时间. 默认是没有 sleep 的。
–why-quit：打印退出的原因, 归档数据正常完成的除外。
–charset=UTF8：指定字符集。
–analyze：结束归档后，优化表空间。

：