1、简介
pt-arcgiver是percona工具集的一员，是归档MySQL大表数据的最佳轻量级工具之一。
2、使用场景
（1）清理线上过期数据；
（2）清理过期数据，并把数据归档到本地归档表中，或者远端归档服务器；
（3）两张表之间的数据不完全相同，希望合并。此时加上–ignore或–replace选项，可以轻松实现
（4）导出线上数据，到线下数据作处理
3、参数
用法：pt-archiver [可选项] --source DSN --where WHERE
| 参数 | 描述 |
| --where | 设置操作条件 |
| --limit 1000 | 每次取1000行数据用pt-archive处理。 |
| --txn-size 1000 | 设置1000行为一个事务提交一次。 |
| --progress 5000 | 每处理5000行输出一次处理信息。 |
| --statistics | 结束的时候给出统计信息：开始的时间点， 结束的时间点，查询的行数，归档的行数，删除的行数，以及各个阶段消耗的总的时间和比例，便于以此进行优化。只要不加上--quiet，默认情况下pt-archive都会输出执行过程的。 |
| --charset=UTF8 | 指定字符集为UTF8。 |
| --no-delete | 表示不删除原来的数据，注意：如果不指定此参数，所有处理完成后，都会清理原表中的数据。 |
| --bulk-delete
| 批量删除source上的旧数据(例如每次1000行的批量删除操作)。 |
| --bulk-insert | 批量插入数据到dest主机 (看dest的general log发现它是通过在dest主机上LOAD DATA LOCAL INFILE插入数据的) |
| --replace | 将insert into 语句改成replace写入到dest库。 |
| --sleep 120 | 每次归档了limit个行记录后的休眠120秒（单位为秒）。 |
| --file ‘/root/test.txt’ | 输出为文件。 |
| --purge | 删除source数据库的相关匹配记录。 |
| --header | 输入列名称到首行（和--file一起使用）。 |
| --no-check-charset | 不指定字符集。 |
| --check-columns | 检验dest和source的表结构是否一致，不一致自动拒绝执行（不加这个参数也行。默认就是执行检查的）。 |
| --no-check-columns | 不检验dest和source的表结构是否一致，不一致也执行（会导致dest上的无法与source匹配的列值被置为null或者0）。 |
| --chekc-interval | 默认1s检查一次。 |
| --no-check-columns | 不检验dest和source的表结构是否一致，不一致也执行（会导致dest上的无法与source匹配的列值被置为null或者0）。 |
| --chekc-interval | 默认1s检查一次。 |
| --local | 不把optimize或analyze操作写入到binlog里面（防止造成主从延迟巨大）。 |
| --retries | 超时或者出现死锁的话，pt-archiver进行重试的间隔（默认1s）。 |
| --no-version-check | 目前为止，发现部分pt工具对阿里云RDS操作必须加这个参数。 |
| --analyze=ds | 操作结束后，优化表空间（d表示dest，s表示source）。 |
默认情况下，pt-archiver操作结束后，不会对source、dest表执行analyze或optimize操作，因为这种操作费时间，并且需要你提前预估有足够的磁盘空间用于拷贝表。一般建议也是pt-archiver操作结束后，在业务低谷手动执行analyze table用以回收表空间。
4、测试
4.1条件
pt-archiver操作的表必须有主键。因为从mysql的general log中可以看出，pt-archiver是根据主键一行一行的删除。
4.2数据归档
（1）创建归档表
注意，归档表需要自己创建，pt-archiver不会主动创建归档表。
mysql -uxxx -pxxx servers
show create table hosts;
mysql -uxxx -pxxx test
CREATE TABLE `hosts_bak` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`host` varchar(255) DEFAULT NULL COMMENT '主机IP',
`hostname` varchar(255) DEFAULT NULL COMMENT '主机名称',
`variables` text COMMENT '变量',
`enabled` tinyint(1) DEFAULT NULL COMMENT 'playbook启用状态',
`created_at` datetime NOT NULL COMMENT '创建时间',
`updated_at` datetime NOT NULL COMMENT '修改时间',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
（2）全表归档
pt-archiver --source h=127.0.0.1,u=xxx,p=xxx,D=servers,t=hosts --dest h=127.0.0.1,u=xxx,p=xxx,D=test,t=hosts_bak --where "1=1" --no-check-charset --no-delete --limit=1000 --progress 2000 --statistics
4.3数据清理
（1）查看数据大小
MariaDB [test]> select count(*) from hosts_bak where id<"20";
+----------+
| count(*) |
+----------+
| 17 |
+----------+
1 row in set (0.00 sec)
MariaDB [test]>
MariaDB [test]> select count(*) from hosts_bak;
+----------+
| count(*) |
+----------+
| 493 |
+----------+
1 row in set (0.01 sec)
（2）清除数据
pt-archiver --no-check-charset --source h=127.0.0.1,u=xxx,p=xxx,D=test,t=hosts_bak --where "id<20" --limit=1000 --progress 2000 --purge --statistics
（3）查看删除结果
MariaDB [test]> select count(*) from hosts_bak;
+----------+
| count(*) |
+----------+
| 476 |
+----------+
1 row in set (0.00 sec)
（4）查看General_log，了解删除机制。
开启 general log 将所有到达MySQL Server的SQL语句记录下来。
查看启动状态
MariaDB [test]> show variables like 'general_log';
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id: 60
Current database: test
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| general_log | OFF |
+---------------+-------+
1 row in set (0.01 sec)
开启general_log
MariaDB [test]> set global general_log=on;
Query OK, 0 rows affected (0.03 sec)
查看general_log位置
show variables like 'general_log_file';
+------------------+------------+
| Variable_name | Value |
+------------------+------------+
| general_log_file | general.log |
+------------------+------------+
1 row in set (0.03 sec)
设置日志文件保存位置
MariaDB [test]> set global general_log_file='/tmp/general.log';
设置日志输出类型
MariaDB [test]> set global log_output='file';
Query OK, 0 rows affected (0.00 sec)
执行数据删除
pt-archiver --no-check-charset --source h=127.0.0.1,u=xxx,p=xxx,D=test,t=hosts_bak --where "id<50" --limit=2 --progress 2 --commit-each --purge --statistics --sleep 1
Query OK, 0 rows affected (0.00 sec)
查看general_log日志
181107 14:44:15 61 Connect root@127.0.0.1 as anonymous on test
61 Query set autocommit=0
61 Query SHOW VARIABLES LIKE 'wait\_timeout'
61 Query SET SESSION wait_timeout=10000
61 Query SELECT @@SQL_MODE
61 Query SET @@SQL_QUOTE_SHOW_CREATE = 1/*!40101, @@SQL_MODE='NO_AUTO_VALUE_ON_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'*/
61 Query SHOW VARIABLES LIKE 'version%'
61 Query SHOW ENGINES
61 Query SHOW VARIABLES LIKE 'innodb_version'
61 Query show variables like 'innodb_rollback_on_timeout'
61 Query /*!40101 SET @OLD_SQL_MODE := @@SQL_MODE, @@SQL_MODE := '', @OLD_QUOTE := @@SQL_QUOTE_SHOW_CREATE, @@SQL_QUOTE_SHOW_CREATE := 1 */
61 Query USE `test`
61 Query SHOW CREATE TABLE `test`.`hosts_bak`
61 Query /*!40101 SET @@SQL_MODE := @OLD_SQL_MODE, @@SQL_QUOTE_SHOW_CREATE := @OLD_QUOTE */
61 Query SHOW VARIABLES LIKE 'wsrep_on'
61 Query SHOW VARIABLES LIKE 'wsrep_on'
61 Query SHOW VARIABLES LIKE 'version%'
61 Query SHOW ENGINES
61 Query SHOW VARIABLES LIKE 'innodb_version'
61 Query SELECT MAX(`id`) FROM `test`.`hosts_bak`
61 Query SELECT CONCAT(@@hostname, @@port)
61 Query SELECT /*!40001 SQL_NO_CACHE */ `id`,`host`,`hostname`,`variables`,`enabled`,`created_at`,`updated_at` FROM `test`.`hosts_bak` FORCE INDEX(`PRIMARY`) WHERE (id<50) AND (`id` < '532') ORDER BY `id` LIMIT 2
61 Query DELETE FROM `test`.`hosts_bak` WHERE (`id` = '20')
61 Query SELECT 'pt-archiver keepalive'
61 Query DELETE FROM `test`.`hosts_bak` WHERE (`id` = '21')
61 Query commit
181107 14:44:30 61 Query SELECT /*!40001 SQL_NO_CACHE */ `id`,`host`,`hostname`,`variables`,`enabled`,`created_at`,`updated_at` FROM `test`.`hosts_bak` FORCE INDEX(`PRIMARY`) WHERE (id<50) AND (`id` < '532') AND ((`id` >= '47')) ORDER BY `id` LIMIT 2
61 Query DELETE FROM `test`.`hosts_bak` WHERE (`id` = '48')
61 Query DELETE FROM `test`.`hosts_bak` WHERE (`id` = '49')
61 Query commit
181107 14:44:31 61 Query SELECT /*!40001 SQL_NO_CACHE */ `id`,`host`,`hostname`,`variables`,`enabled`,`created_at`,`updated_at` FROM `test`.`hosts_bak` FORCE INDEX(`PRIMARY`) WHERE (id<50) AND (`id` < '532') AND ((`id` >= '49')) ORDER BY `id` LIMIT 2
61 Query commit
61 Quit
由以上日志可以看出，pt-arcgivers是根据主键一行一行做删除的。可以根据需要加上--bulk-delete做批量删除，做归档时需要同时加上--bulk-insert参数。
4.4数据导出
（1）查看数据大小
MariaDB [test]> select count(*) from hosts_bak;
+----------+
| count(*) |
+----------+
| 446 |
+----------+
1 row in set (0.00 sec)
（2）导出数据文件
pt-archiver --no-check-charset --source h=127.0.0.1,u=xxx,p=xxx,D=test,t=hosts_bak --where "1=1" --limit=10 --progress 10 --commit-each --no-delete --statistics --file="/tmp/archiver.dat"
[root@cathy4 RubyOnRailsWeb]# ll /tmp
总用量 320
-rw-r--r-- 1 root root 178078 11月 7 15:08 archiver.dat
（3）恢复数据
LOAD DATA LOCAL INFILE '/tmp/archiver.dat' INTO TABLE hosts_new FIELDS terminated by '\t' LINES TERMINATED BY '\n' (id,host,hostname,variables,enabled,created_at,updated_at);
（4）查看备份数据
MariaDB [test]> select count(*) from hosts_new;
+----------+
| count(*) |
+----------+
| 445 |
+----------+
1 row in set (0.00 sec)
MariaDB [test]> select max(id) from hosts_bak;
+---------+
| max(id) |
+---------+
| 532 |
+---------+
1 row in set (0.00 sec)
MariaDB [test]> select max(id) from hosts_new;
+---------+
| max(id) |
+---------+
| 522 |
+---------+
1 row in set (0.00 sec)
注意：
备份后的数据会比原数据表少一个最大值；
从general_log的最后一次提交可以看出：
72 Query SELECT /*!40001 SQL_NO_CACHE */ `id`,`host`,`hostname`,`variables`,`enabled`,`created_at`,`updated_at` FROM `test`.`hosts_bak` FORCE INDEX(`PRIMARY`) WHERE (1=1) AND (`id` < '532') AND ((`id` > '522')) ORDER BY `id` LIMIT 10
条件为WHERE (1=1) AND (`id` < '532') AND ((`id` > '522'))。忽略了id=532的值。
PS
下方是我个人订阅号，会一直更新各类技术文章，欢迎关注 ：）
扫一扫
1633
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
