pt工具之pt-archiver

# 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@hongquan1 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-eachPurge (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=socketA: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=utf8192.168.19.145:mysql version=5.7.22,charset=utf8mb4

1导出到⽂件，不删除源数据2019-03-26T02:02:14 0 0

Cannot find encoding \"utf8mb4\" at /usr/lib/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-charsetTIME ELAPSED COUNT2019-03-26T02:04:10 0 02019-03-26T02:04:10 0 10002019-03-26T02:04:10 0 1000

Started at 2019-03-26T02:04:10, ended at 2019-03-26T02:04:10Source: D=test,P=3306,h=10.15.7.114,p=...,t=t1,u=systemSELECT 1000INSERT 0DELETE 0

Action Count Time Pctselect 1001 0.1258 58.07commit 1001 0.0316 14.57print_file 1000 0.0015 0.68other 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.t12删除，不导出和迁移

[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-charsetTIME ELAPSED COUNT2019-03-26T02:07:49 0 02019-03-26T02:07:50 1 10002019-03-26T02:07:50 1 1000

Started at 2019-03-26T02:07:49, ended at 2019-03-26T02:07:50Source: D=test,P=3306,h=10.15.7.114,p=...,t=t1,u=systemSELECT 1000INSERT 0DELETE 1000

Action Count Time Pctcommit 1001 1.5619 81.38deleting 1000 0.1455 7.58select 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-charsetTIME ELAPSED COUNT2019-03-26T02:20:09 0 02019-03-26T02:20:11 2 10002019-03-26T02:20:11 2 1000

Started at 2019-03-26T02:20:09, ended at 2019-03-26T02:20:11Source: D=test,P=3306,h=10.15.7.114,p=...,t=t1,u=systemDest: D=test,P=3306,h=192.168.19.145,p=...,t=t37,u=systemSELECT 1000INSERT 1000DELETE 0

Action Count Time Pctcommit 2002 1.6199 70.67inserting 1000 0.4391 19.16select 1001 0.1419 6.19other 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 --destu=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-charsetStarted at 2019-03-26T02:22:48, ended at 2019-03-26T02:22:48Source: D=test,P=3306,h=10.15.7.114,p=...,t=t1,u=systemDest: D=test,P=3306,h=192.168.19.145,p=...,t=t37,u=systemSELECT 1000INSERT 1000DELETE 0

Action Count Time Pct

bulk_inserting 1 0.0544 61.78commit 4 0.0034 3.84select 2 0.0015 1.69

print_bulkfile 1000 -0.0017 -1.91other 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-charset6指定条件归档，源表删除，批量（每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-charset7指定索引的归档，不⾛⾃增主键索引。参数：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-charset8有从库的归档

./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=33069不做任何操作,只打印要执⾏的查询语句

[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 ((((? ISNULL AND `a` IS NOT NULL) OR (`a` > ?)))) ORDER BY `a` LIMIT 1000REPLACE 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 --destu=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 COUNT2019-03-26T03:13:10 0 02019-03-26T03:13:11 0 300

Started at 2019-03-26T03:13:10, ended at 2019-03-26T03:13:11Source: D=test,P=3306,h=10.15.7.114,i=a,p=...,t=t1,u=systemDest: D=test,P=3306,h=192.168.19.145,i=a,p=...,t=t37,u=systemSELECT 300INSERT 300DELETE 300

Action Count Time Pctcommit 4 0.0081 23.57

bulk_inserting 2 0.0061 17.83bulk_deleting 2 0.0032 9.38select 3 0.0013 3.74

print_bulkfile 300 -0.0011 -3.28other 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：结束归档后，优化表空间。⽂档参考