percona-toolkit系列工具（二）之pt-archiver使用介绍

一、介绍
Percona Toolkit是Percona公司开发的高级命令行工具的集合，用于执行各种MySQL、MongoDB和系统任务(我们这里只讲关于mysql的)，这些任务太难或太复杂，无法手动执行。 这些工具是运维人员一次性脚本的理想替代品，因为它们经过专业开发、正式测试和充分记录。它们也是完全独立的，因此安装快速简单。

percona官网：https://www.percona.com/
pt工具文档：https://docs.percona.com/percona-toolkit/

二、下载安装
2.1、下载


、
或者选择rpm包（操作系统不要选择linux generic才有rpm包）

本文档使用tar包安装

2.2、安装
# 1、安装依赖包
yum install perl-DBI perl-DBD-MySQL perl-Time-HiRes perl-Digest-MD5 -y

# 2、解压tar包即可使用，无需其他操作，可自行配置环境变量
[root@ob9 bin]# /opt/percona-toolkit-3.6.0/bin/pt-archiver --version
pt-archiver 3.6.0
[root@ob9 bin]# /opt/percona-toolkit-3.6.0/bin/pt-query-digest --version
pt-query-digest 3.6.0
三、pt-archiver的使用教程
3.1、介绍
# 1、pt-archiver的作用
用来归档历史数据到其他表或文件中，可以归档到本数据库，也可以归档到另一个mysql数据库，比如专门的历史库。
也可以用来删除数据，mysql直接delete大量数据会造成大事务堵塞，此时可以使用该工具来删除数据。该工具有参数控制执行多少条数据可以commit一次。

# 2、pt-archiver常用参数介绍
pt-archiver [OPTIONS] --source DSN --dest DSN --where WHERE

解释：
--source表示源端数据库的连接方式（DSN格式），
例如 h=host,u=user,p=password,D=database,t=table 
--dest表示目标端数据库连接方式（DSN格式）,如果是删除数据而不是归档数据，则不用指定--dest
--where 指定查询过滤条件，否则全表归档或者删除

options是一些参数：
--where 指定查询过滤条件，例如 --where "id < 10"
--bulk-delete              Delete each chunk with a single statement (implies --commit-each)
注释：归档或者删除数据时如果不加这个参数，就会一条sql只删除一条数据，然后执行N多条sql，加了这个参数就能批量delete，每次delete的条数由--limit参数决定，指定这个参数就不需要指定--commit-each参数了

--bulk-insert              Insert each chunk with LOAD DATA INFILE (implies --bulk-delete --commit-each)
注释：归档时目标端数据库数据每次写入的数据条数，并且是load data 方式写入数据的，不是insert 语句；不指定这个参数就会使用insert 语句一条sql插入一条数据

--charset=s  指定字符集
--[no]check-charset        Ensure connection and table character sets are the same (default yes)
--dry-run                  Print queries and exit without doing anything
注释：不想在数据库实际执行命令，只是想知道如果执行了实际数据库中跑的是哪些sql语句

--file=s                   File to archive to, with DATE_FORMAT()-like formatting
--limit=i                  Number of rows to fetch and archive per statement (default 1)
--txn-size=i               Number of rows per transaction (default 1)
--commit-each              Commit each set of fetched and archived rows (disables --txn-size)
--no-delete                该参数表示不删除源端已归档的数据，不加这个参数默认会删除
--purge                    Purge instead of archiving; allows omitting --file and --dest
注释：该参数表示只删除数据，不进行归档，如果不指定该参数，则表示进行归档，此时必须指定归档目的端--dest参数或者--file参数

--progress=i               Print progress information every i rows
# 每处理多少行数据就会打印一个信息出来
--quick-delete             Adds the QUICK modifier to DELETE statements
--retries=i                Number of retries per timeout or deadlock (default 1)
--primary-key-only         Primary key columns only
--sleep=i                  Sleep time between fetches
--statistics               Collect and print timing statistics
# 命令执行结束会打印出个总的统计信息出来
3.2、归档测试
数据库开启general_log，查看执行命令时数据库中是怎样执行sql的
点击查看代码 
# 造几条数据
insert into t1 values(1,'aa',10,'2024-12-03 12:10:00'),(2,'bb',15,'2024-12-03 12:15:00'),(3,'cc',20,'2024-12-03 12:20:00'),(4,'dd',25,'2024-12-03 12:25:00'),(5,'ee',30,'2024-12-03 12:30:00'),(6,'ff',35,'2024-12-03 12:35:00'),(7,'gg',40,'2024-12-03 12:40:00'),(8,'hh',45,'2024-12-03 12:45:00');



/opt/percona-toolkit-3.6.0/bin/pt-archiver --source h=192.168.167.160,u=root,p=111111,D=test1,t=t1 --dest h=192.168.167.149,u=root,p=111111,D=test1,t=t1 --where "create_at < '2024-12-03 12:35:00'" --limit 2 --commit-each  --charset=utf8mb4 
源端
2024-12-03T08:17:51.606903Z	   23 Query	SELECT /*!40001 SQL_NO_CACHE */ `id`,`name`,`age`,`create_at` FROM `test1`.`t1` FORCE INDEX(`PRIMARY`) WHERE (create_at < '2024-12-03 12:35:00') ORDER BY `id` LIMIT 2
2024-12-03T08:17:51.609541Z	   23 Query	DELETE FROM `test1`.`t1` WHERE (`id` = '1')
2024-12-03T08:17:51.610809Z	   23 Query	SELECT 'pt-archiver keepalive'
2024-12-03T08:17:51.611755Z	   23 Query	DELETE FROM `test1`.`t1` WHERE (`id` = '2')
2024-12-03T08:17:51.613267Z	   23 Query	commit
2024-12-03T08:17:51.615134Z	   23 Query	SELECT /*!40001 SQL_NO_CACHE */ `id`,`name`,`age`,`create_at` FROM `test1`.`t1` FORCE INDEX(`PRIMARY`) WHERE (create_at < '2024-12-03 12:35:00') AND ((`id` >= '2')) ORDER BY `id` LIMIT 2
2024-12-03T08:17:51.616524Z	   23 Query	DELETE FROM `test1`.`t1` WHERE (`id` = '3')
2024-12-03T08:17:51.617628Z	   23 Query	DELETE FROM `test1`.`t1` WHERE (`id` = '4')
2024-12-03T08:17:51.619115Z	   23 Query	commit
2024-12-03T08:17:51.620872Z	   23 Query	SELECT /*!40001 SQL_NO_CACHE */ `id`,`name`,`age`,`create_at` FROM `test1`.`t1` FORCE INDEX(`PRIMARY`) WHERE (create_at < '2024-12-03 12:35:00') AND ((`id` >= '4')) ORDER BY `id` LIMIT 2
2024-12-03T08:17:51.622148Z	   23 Query	DELETE FROM `test1`.`t1` WHERE (`id` = '5')
2024-12-03T08:17:51.623617Z	   23 Query	commit
2024-12-03T08:17:51.625050Z	   23 Query	SELECT /*!40001 SQL_NO_CACHE */ `id`,`name`,`age`,`create_at` FROM `test1`.`t1` FORCE INDEX(`PRIMARY`) WHERE (create_at < '2024-12-03 12:35:00') AND ((`id` >= '5')) ORDER BY `id` LIMIT 2
2024-12-03T08:17:51.626024Z	   23 Query	commit

目标端
SELECT CONCAT(/*!40100 @@session.character_set_connection, */ '')
2024-12-03T09:02:57.529277Z	   60 Query	SELECT CONCAT(@@hostname, @@port)
2024-12-03T09:02:57.531436Z	   60 Query	INSERT INTO `test1`.`t1`(`id`,`name`,`age`,`create_at`) VALUES ('1','aa','10','2024-12-03 12:10:00')
2024-12-03T09:02:57.533246Z	   60 Query	INSERT INTO `test1`.`t1`(`id`,`name`,`age`,`create_at`) VALUES ('2','bb','15','2024-12-03 12:15:00')
2024-12-03T09:02:57.534497Z	   60 Query	commit
2024-12-03T09:02:57.537932Z	   60 Query	INSERT INTO `test1`.`t1`(`id`,`name`,`age`,`create_at`) VALUES ('3','cc','20','2024-12-03 12:20:00')
2024-12-03T09:02:57.539214Z	   60 Query	INSERT INTO `test1`.`t1`(`id`,`name`,`age`,`create_at`) VALUES ('4','dd','25','2024-12-03 12:25:00')
2024-12-03T09:02:57.540501Z	   60 Query	commit
2024-12-03T09:02:57.544932Z	   60 Query	INSERT INTO `test1`.`t1`(`id`,`name`,`age`,`create_at`) VALUES ('5','ee','30','2024-12-03 12:30:00')
2024-12-03T09:02:57.546820Z	   60 Query	commit




/opt/percona-toolkit-3.6.0/bin/pt-archiver --source h=192.168.167.160,u=root,p=111111,D=test1,t=t1 --dest h=192.168.167.149,u=root,p=111111,D=test1,t=t1 --where "create_at < '2024-12-03 12:35:00'" --limit 2 --txn-size=2 --charset=utf8mb4 
源端
2024-12-03T09:02:57.531484Z	   25 Query	SELECT /*!40001 SQL_NO_CACHE */ `id`,`name`,`age`,`create_at` FROM `test1`.`t1` FORCE INDEX(`PRIMARY`) WHERE (create_at < '2024-12-03 12:35:00') ORDER BY `id` LIMIT 2
2024-12-03T09:02:57.532989Z	   25 Query	DELETE FROM `test1`.`t1` WHERE (`id` = '1')
2024-12-03T09:02:57.533825Z	   25 Query	SELECT 'pt-archiver keepalive'
2024-12-03T09:02:57.534649Z	   25 Query	DELETE FROM `test1`.`t1` WHERE (`id` = '2')
2024-12-03T09:02:57.536393Z	   25 Query	commit
2024-12-03T09:02:57.538082Z	   25 Query	SELECT /*!40001 SQL_NO_CACHE */ `id`,`name`,`age`,`create_at` FROM `test1`.`t1` FORCE INDEX(`PRIMARY`) WHERE (create_at < '2024-12-03 12:35:00') AND ((`id` >= '2')) ORDER BY `id` LIMIT 2
2024-12-03T09:02:57.539423Z	   25 Query	DELETE FROM `test1`.`t1` WHERE (`id` = '3')
2024-12-03T09:02:57.540692Z	   25 Query	DELETE FROM `test1`.`t1` WHERE (`id` = '4')
2024-12-03T09:02:57.542335Z	   25 Query	commit
2024-12-03T09:02:57.545137Z	   25 Query	SELECT /*!40001 SQL_NO_CACHE */ `id`,`name`,`age`,`create_at` FROM `test1`.`t1` FORCE INDEX(`PRIMARY`) WHERE (create_at < '2024-12-03 12:35:00') AND ((`id` >= '4')) ORDER BY `id` LIMIT 2
2024-12-03T09:02:57.546393Z	   25 Query	DELETE FROM `test1`.`t1` WHERE (`id` = '5')
2024-12-03T09:02:57.547183Z	   25 Query	SELECT /*!40001 SQL_NO_CACHE */ `id`,`name`,`age`,`create_at` FROM `test1`.`t1` FORCE INDEX(`PRIMARY`) WHERE (create_at < '2024-12-03 12:35:00') AND ((`id` >= '5')) ORDER BY `id` LIMIT 2
2024-12-03T09:02:57.548612Z	   25 Query	commit

目标端
SELECT CONCAT(/*!40100 @@session.character_set_connection, */ '')
2024-12-03T09:02:57.529277Z	   60 Query	SELECT CONCAT(@@hostname, @@port)
2024-12-03T09:02:57.531436Z	   60 Query	INSERT INTO `test1`.`t1`(`id`,`name`,`age`,`create_at`) VALUES ('1','aa','10','2024-12-03 12:10:00')
2024-12-03T09:02:57.533246Z	   60 Query	INSERT INTO `test1`.`t1`(`id`,`name`,`age`,`create_at`) VALUES ('2','bb','15','2024-12-03 12:15:00')
2024-12-03T09:02:57.534497Z	   60 Query	commit
2024-12-03T09:02:57.537932Z	   60 Query	INSERT INTO `test1`.`t1`(`id`,`name`,`age`,`create_at`) VALUES ('3','cc','20','2024-12-03 12:20:00')
2024-12-03T09:02:57.539214Z	   60 Query	INSERT INTO `test1`.`t1`(`id`,`name`,`age`,`create_at`) VALUES ('4','dd','25','2024-12-03 12:25:00')
2024-12-03T09:02:57.540501Z	   60 Query	commit
2024-12-03T09:02:57.544932Z	   60 Query	INSERT INTO `test1`.`t1`(`id`,`name`,`age`,`create_at`) VALUES ('5','ee','30','2024-12-03 12:30:00')
2024-12-03T09:02:57.546820Z	   60 Query	commit



/opt/percona-toolkit-3.6.0/bin/pt-archiver --source h=192.168.167.160,u=root,p=111111,D=test1,t=t1 --dest h=192.168.167.149,u=root,p=111111,D=test1,t=t1 --where "create_at < '2024-12-03 12:35:00'" --limit 2 --txn-size=2 --charset=utf8mb4 --no-delete --statistics
源端
SELECT /*!40001 SQL_NO_CACHE */ `id`,`name`,`age`,`create_at` FROM `test1`.`t1` FORCE INDEX(`PRIMARY`) WHERE (create_at < '2024-12-03 12:35:00') ORDER BY `id` LIMIT 2
2024-12-03T09:12:51.074781Z	   26 Query	SELECT 'pt-archiver keepalive'
2024-12-03T09:12:51.077218Z	   26 Query	commit
2024-12-03T09:12:51.077665Z	   26 Query	SELECT /*!40001 SQL_NO_CACHE */ `id`,`name`,`age`,`create_at` FROM `test1`.`t1` FORCE INDEX(`PRIMARY`) WHERE (create_at < '2024-12-03 12:35:00') AND ((`id` > '2')) ORDER BY `id` LIMIT 2
2024-12-03T09:12:51.080260Z	   26 Query	commit
2024-12-03T09:12:51.080649Z	   26 Query	SELECT /*!40001 SQL_NO_CACHE */ `id`,`name`,`age`,`create_at` FROM `test1`.`t1` FORCE INDEX(`PRIMARY`) WHERE (create_at < '2024-12-03 12:35:00') AND ((`id` > '4')) ORDER BY `id` LIMIT 2
2024-12-03T09:12:51.081828Z	   26 Query	SELECT /*!40001 SQL_NO_CACHE */ `id`,`name`,`age`,`create_at` FROM `test1`.`t1` FORCE INDEX(`PRIMARY`) WHERE (create_at < '2024-12-03 12:35:00') AND ((`id` > '5')) ORDER BY `id` LIMIT 2
2024-12-03T09:12:51.083315Z	   26 Query	commit

目标端
SELECT CONCAT(@@hostname, @@port)
2024-12-03T09:12:51.073088Z	   61 Query	INSERT INTO `test1`.`t1`(`id`,`name`,`age`,`create_at`) VALUES ('1','aa','10','2024-12-03 12:10:00')
2024-12-03T09:12:51.074119Z	   61 Query	INSERT INTO `test1`.`t1`(`id`,`name`,`age`,`create_at`) VALUES ('2','bb','15','2024-12-03 12:15:00')
2024-12-03T09:12:51.074558Z	   61 Query	commit
2024-12-03T09:12:51.077384Z	   61 Query	INSERT INTO `test1`.`t1`(`id`,`name`,`age`,`create_at`) VALUES ('3','cc','20','2024-12-03 12:20:00')
2024-12-03T09:12:51.077894Z	   61 Query	INSERT INTO `test1`.`t1`(`id`,`name`,`age`,`create_at`) VALUES ('4','dd','25','2024-12-03 12:25:00')
2024-12-03T09:12:51.078304Z	   61 Query	commit
2024-12-03T09:12:51.080272Z	   61 Query	INSERT INTO `test1`.`t1`(`id`,`name`,`age`,`create_at`) VALUES ('5','ee','30','2024-12-03 12:30:00')
2024-12-03T09:12:51.081378Z	   61 Query	commit




/opt/percona-toolkit-3.6.0/bin/pt-archiver --source h=192.168.167.160,u=root,p=111111,D=test1,t=t1 --dest h=192.168.167.149,u=root,p=111111,D=test1,t=t1 --where "create_at < '2024-12-03 12:35:00'" --limit 2 --txn-size=2 --charset=utf8mb4 --bulk-insert  --statistics
源端
SELECT /*!40001 SQL_NO_CACHE */ `id`,`name`,`age`,`create_at` FROM `test1`.`t1` FORCE INDEX(`PRIMARY`) WHERE (create_at < '2024-12-03 12:35:00') ORDER BY `id` LIMIT 2
2024-12-03T09:20:17.389027Z	   27 Query	SELECT 'pt-archiver keepalive'
2024-12-03T09:20:17.390077Z	   27 Query	commit
2024-12-03T09:20:17.391550Z	   27 Query	SELECT 'pt-archiver keepalive'
2024-12-03T09:20:17.392013Z	   27 Query	DELETE FROM `test1`.`t1` WHERE (((`id` >= '1'))) AND (((`id` <= '2'))) AND (create_at < '2024-12-03 12:35:00') LIMIT 2
2024-12-03T09:20:17.393206Z	   27 Query	SELECT /*!40001 SQL_NO_CACHE */ `id`,`name`,`age`,`create_at` FROM `test1`.`t1` FORCE INDEX(`PRIMARY`) WHERE (create_at < '2024-12-03 12:35:00') AND ((`id` >= '2')) ORDER BY `id` LIMIT 2
2024-12-03T09:20:17.395853Z	   27 Query	commit
2024-12-03T09:20:17.398124Z	   27 Query	SELECT 'pt-archiver keepalive'
2024-12-03T09:20:17.398537Z	   27 Query	DELETE FROM `test1`.`t1` WHERE (((`id` >= '3'))) AND (((`id` <= '4'))) AND (create_at < '2024-12-03 12:35:00') LIMIT 2
2024-12-03T09:20:17.399361Z	   27 Query	SELECT /*!40001 SQL_NO_CACHE */ `id`,`name`,`age`,`create_at` FROM `test1`.`t1` FORCE INDEX(`PRIMARY`) WHERE (create_at < '2024-12-03 12:35:00') AND ((`id` >= '4')) ORDER BY `id` LIMIT 2
2024-12-03T09:20:17.401803Z	   27 Query	SELECT 'pt-archiver keepalive'
2024-12-03T09:20:17.402205Z	   27 Query	DELETE FROM `test1`.`t1` WHERE (((`id` >= '5'))) AND (((`id` <= '5'))) AND (create_at < '2024-12-03 12:35:00') LIMIT 2
2024-12-03T09:20:17.402869Z	   27 Query	SELECT /*!40001 SQL_NO_CACHE */ `id`,`name`,`age`,`create_at` FROM `test1`.`t1` FORCE INDEX(`PRIMARY`) WHERE (create_at < '2024-12-03 12:35:00') AND ((`id` >= '5')) ORDER BY `id` LIMIT 2
2024-12-03T09:20:17.405155Z	   27 Query	commit

目标端
SELECT CONCAT(@@hostname, @@port)
2024-12-03T09:20:17.388517Z	   62 Query	commit
2024-12-03T09:20:17.389603Z	   62 Query	LOAD DATA LOCAL INFILE '/tmp/BE1Vdxp6w3pt-archiver' INTO TABLE `test1`.`t1`CHARACTER SET utf8mb4(`id`,`name`,`age`,`create_at`)
2024-12-03T09:20:17.393835Z	   62 Query	commit
2024-12-03T09:20:17.396289Z	   62 Query	LOAD DATA LOCAL INFILE '/tmp/atLmk7_622pt-archiver' INTO TABLE `test1`.`t1`CHARACTER SET utf8mb4(`id`,`name`,`age`,`create_at`)
2024-12-03T09:20:17.400044Z	   62 Query	LOAD DATA LOCAL INFILE '/tmp/x0oqGt1e7bpt-archiver' INTO TABLE `test1`.`t1`CHARACTER SET utf8mb4(`id`,`name`,`age`,`create_at`)
2024-12-03T09:20:17.403126Z	   62 Query	commit



/opt/percona-toolkit-3.6.0/bin/pt-archiver --source h=192.168.167.160,u=root,p=111111,D=test1,t=t1 --dest h=192.168.167.149,u=root,p=111111,D=test1,t=t1 --where "create_at < '2024-12-03 12:35:00'" --limit 2 --txn-size=4 --charset=utf8mb4 --bulk-insert  --statistics 
源端
2024-12-03T09:36:31.815880Z	   31 Query	SELECT /*!40001 SQL_NO_CACHE */ `id`,`name`,`age`,`create_at` FROM `test1`.`t1` FORCE INDEX(`PRIMARY`) WHERE (create_at < '2024-12-03 12:35:00') ORDER BY `id` LIMIT 2
2024-12-03T09:36:31.837416Z	   31 Query	SELECT 'pt-archiver keepalive'
2024-12-03T09:36:31.839069Z	   31 Query	SELECT 'pt-archiver keepalive'
2024-12-03T09:36:31.839552Z	   31 Query	DELETE FROM `test1`.`t1` WHERE (((`id` >= '1'))) AND (((`id` <= '2'))) AND (create_at < '2024-12-03 12:35:00') LIMIT 2
2024-12-03T09:36:31.840730Z	   31 Query	SELECT /*!40001 SQL_NO_CACHE */ `id`,`name`,`age`,`create_at` FROM `test1`.`t1` FORCE INDEX(`PRIMARY`) WHERE (create_at < '2024-12-03 12:35:00') AND ((`id` >= '2')) ORDER BY `id` LIMIT 2
2024-12-03T09:36:31.843632Z	   31 Query	commit
2024-12-03T09:36:31.846020Z	   31 Query	SELECT 'pt-archiver keepalive'
2024-12-03T09:36:31.846443Z	   31 Query	DELETE FROM `test1`.`t1` WHERE (((`id` >= '3'))) AND (((`id` <= '4'))) AND (create_at < '2024-12-03 12:35:00') LIMIT 2
2024-12-03T09:36:31.847301Z	   31 Query	SELECT /*!40001 SQL_NO_CACHE */ `id`,`name`,`age`,`create_at` FROM `test1`.`t1` FORCE INDEX(`PRIMARY`) WHERE (create_at < '2024-12-03 12:35:00') AND ((`id` >= '4')) ORDER BY `id` LIMIT 2
2024-12-03T09:36:31.849579Z	   31 Query	SELECT 'pt-archiver keepalive'
2024-12-03T09:36:31.849961Z	   31 Query	DELETE FROM `test1`.`t1` WHERE (((`id` >= '5'))) AND (((`id` <= '5'))) AND (create_at < '2024-12-03 12:35:00') LIMIT 2
2024-12-03T09:36:31.850609Z	   31 Query	SELECT /*!40001 SQL_NO_CACHE */ `id`,`name`,`age`,`create_at` FROM `test1`.`t1` FORCE INDEX(`PRIMARY`) WHERE (create_at < '2024-12-03 12:35:00') AND ((`id` >= '5')) ORDER BY `id` LIMIT 2
2024-12-03T09:36:31.852971Z	   31 Query	commit

目标端
2024-12-03T09:36:31.813753Z	   66 Query	SELECT CONCAT(@@hostname, @@port)
2024-12-03T09:36:31.837096Z	   66 Query	LOAD DATA LOCAL INFILE '/tmp/qViLruF3QRpt-archiver' INTO TABLE `test1`.`t1`CHARACTER SET utf8mb4(`id`,`name`,`age`,`create_at`)
2024-12-03T09:36:31.841583Z	   66 Query	commit
2024-12-03T09:36:31.844258Z	   66 Query	LOAD DATA LOCAL INFILE '/tmp/91cAOcIwAlpt-archiver' INTO TABLE `test1`.`t1`CHARACTER SET utf8mb4(`id`,`name`,`age`,`create_at`)
2024-12-03T09:36:31.847794Z	   66 Query	LOAD DATA LOCAL INFILE '/tmp/MoKi6vwDsDpt-archiver' INTO TABLE `test1`.`t1`CHARACTER SET utf8mb4(`id`,`name`,`age`,`create_at`)
2024-12-03T09:36:31.850955Z	   66 Query	commit
结论：

1、不指定--no-delete参数时，归档默认会删除源端已归档的数据，并且是一条delete语句只删除一条数据；
2、要想归档时能批量删除数据，得加上参数--bulk-delete,然后每条delete语句的条数由参数--limit控制；如果想不删除源端数据，需要加上参数--no-delete
3、不加--bulk-insert参数，则归档目的端写入数据时是使用insert 语句一条sql插入一条数据，加上该参数后是使用load data方式插入数据，每个load data 的文件包含的数据条数由--limit控制；
如果使用该参数并且想删除源端数据，则也不需要使用--bulk-delete参数，--bulk-insert包含--bulk--delete参数
4、--commit-each 和--txn-size不要一起使用，建议使用--txn-size，并且值和--limit的值保持一致
5、可以加上--sleep参数减少数据库压力，但是相应地也会增加执行时间，自己取舍

如上所述，归档的命令建议如下：

/opt/percona-toolkit-3.6.0/bin/pt-archiver --source h=192.168.167.160,u=root,p=111111,D=test1,t=t1 --dest h=192.168.167.149,u=root,p=111111,D=test1,t=t1 --where "create_at < '2024-12-03 12:35:00'" --limit 2 --txn-size=2 --charset=utf8mb4 --bulk-insert --sleep=1 --statistics --progress=2

# 如果不想删除源端数据，则再加上--no-delete参数即可
# 一般生产上--limit参数值可以设为几百到几千（根据自身数据库性能而定，表示一条sql会处理多少条数据）
--limit 和--txn-size 和 --progress可以设置成相同的参数值

# --sleep会增加执行时间，但可以减小数据库压力，自己取舍


如上所述，不归档，只删除数据的命令建议如下：

/opt/percona-toolkit-3.6.0/bin/pt-archiver --source h=192.168.167.160,u=root,p=111111,D=test1,t=t1 --where "create_at < '2024-12-03 12:35:00'" --limit 2 --txn-size=2 --bulk-delete --sleep=1 --charset=utf8mb4 --statistics --progress=2 --purge
posted @   有形无形  阅读(32)  评论(0)  编辑  收藏  举报
相关博文：
· percona-toolkit系列工具（一）之下载安装
· percona-toolkit系列工具（三）之pt-osc使用介绍
· percona-toolkit运维工具
· 35.PT(percona-toolkit)常用功能
· mysql pt-archiver
阅读排行：
· Manus重磅发布：全球首款通用AI代理技术深度解析与实战指南
· 被坑几百块钱后，我竟然真的恢复了删除的微信聊天记录！
· 没有Manus邀请码？试试免邀请码的MGX或者开源的OpenManus吧
· 园子的第一款AI主题卫衣上架——"HELLO! HOW CAN I ASSIST YOU TODAY
· 【自荐】一款简洁、开源的在线白板工具 Drawnix
点击右上角即可分享
微信分享提示