mysql使用pt-arcgiver归档

本文介绍了如何使用pt-arcgiver对mysql数据进行归档。

简介

1
pt-arcgiver是percona工具集的一员,是归档MySQL大表数据的最佳轻量级工具之一。

使用场景

1
2
3
4
(1)清理线上过期数据;
(2)清理过期数据,并把数据归档到本地归档表中,或者远端归档服务器;
(3)两张表之间的数据不完全相同,希望合并。此时加上–ignore或–replace选项,可以轻松实现
(4)导出线上数据,到线下数据作处理

参数

用法: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用以回收表空间。

测试

条件

pt-archiver操作的表必须有主键。因为从mysql的general log中可以看出,pt-archiver是根据主键一行一行的删除。

数据归档

(1)创建归档表

1
2
3
4
5
6
7
8
9
10
11
12
13
14
注意,归档表需要自己创建,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)全表归档

1
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

数据清理

(1)查看数据大小

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
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)清除数据

1
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)查看删除结果

1
2
3
4
5
6
7
MariaDB [test]> select count(*) from hosts_bak;
+----------+
| count(*) |
+----------+
|      476 |
+----------+
1 row in set (0.00 sec)

(4)查看General_log,了解删除机制。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
开启 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参数。

数据导出

(1)查看数据大小

1
2
3
4
5
6
7
MariaDB [test]> select count(*) from hosts_bak;
+----------+
| count(*) |
+----------+
|    446 |
+----------+
1 row in set (0.00 sec)

(2)导出数据文件

1
2
3
4
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)恢复数据

1
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)查看备份数据

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
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的值。

ulysses wechat
订阅+