pt-online-schema-change 和 pt-archiver 使用方法

短命女 2022-05-26 02:05 249阅读 0赞

Percona Toolkit 包含多种用于 MySQL 数据库管理的工具。

下面介绍常用的 pt-online-schema-change  和  pt-archiver 搭配 RDS MySQL 的使用方法。

注：

*  pt-online-schema-change 和 pt-archiver 工具均须指定 --no-version-check 选项方能搭配 RDS MySQL 实例使用。
 *  本文样例使用 Percona Toolkit 2.2.17 版本测试。
 *  本文样例仅做为样例使用，不承担任何因此示范导致的问题责任。具体操作手册请参考 Percona Toolkit 的相关文档。

## 1. pt-online-schema-change  ##

pt-online-schema-change 提供在线修改表结构等功能，搭配 RDS for MySQL 5.5 使用可以避免在修改表结构的过程中阻塞应用对表数据的访问。

由于 RDS MySQL 5.6 支持 online-ddl 功能，可以直接在业务低峰期进行操作，也可以结合 pt-online-schema-change 使用。

注：

*  无论 RDS MySQL 5.5 还是 5.6 版本，也无论是否使用 pt-online-schema-change，在修改表结构过程中都有可能遇到等待表元数据锁的情况（waiting for table metadata lock）。如果出现这种情况，请参考 [RDS MySQL 表上 Metadata lock 的产生和处理][RDS MySQL _ Metadata lock]
 *  表及数据维护操作请在业务低峰期进行。

样例用表：

CREATE TABLE `x` ( `id` int(11) NOT NULL AUTO_INCREMENT, `password` varchar(10) DEFAULT NULL, `recommend_level` double(5,0) DEFAULT NULL, `name` varchar(30) DEFAULT '101' COMMENT 'change', PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4;

### 1.1 增加字段 ###

pt-online-schema-change --no-version-check --execute --alter "add column c1 int" h=xxx.mysql.rds.aliyuncs.com,P=3306,u=jacky,p=xxx,D=jacky,t=x # h=xxx.mysql.rds.aliyuncs.com -- RDS 实例地址 # P=3306 -- RDS 实例端口 # u=jacky -- RDS 实例用户 # p=xxx -- RDS 实例用户密码 # D=jacky -- RDS 实例数据库 # t=x -- RDS 实例表名称

![1][]

![1 1][]

### 1.2 删除字段 ###

pt-online-schema-change --no-version-check --execute --alter "drop column c1" h=xxx.mysql.rds.aliyuncs.com,P=3306,u=jacky,p=xxx,D=jacky,t=x

![1 2][]

### 1.3 修改字段类型 ###

pt-online-schema-change --no-version-check --execute --alter "modify column c1 bigint unsigned" h=xxx.mysql.rds.aliyuncs.com,P=3306,u=jacky,p=xxx,D=jacky,t=x

![1 3][]

### 1.4 添加索引 ###

pt-online-schema-change --no-version-check --execute --alter "add key idx_c1 (c1)" h=xxx.mysql.rds.aliyuncs.com,P=3306,u=jacky,p=xxx,D=jacky,t=x

![1 4][]

### 1.5 删除索引 ###

pt-online-schema-change --no-version-check --execute --alter "drop key idx_c1" h=xxx.mysql.rds.aliyuncs.com,P=3306,u=jacky,p=xxx,D=jacky,t=x

![1 5][]

## 2. pt-archiver ##

pt-archiver 是 Percona 官方提供的归档工具，用于归档大型表中的记录到另一个表或文件。

样例用表：

CREATE TABLE `my_tab` ( `id` bigint(11) NOT NULL AUTO_INCREMENT, `areaID` varchar(50) DEFAULT NULL, `area` varchar(60) DEFAULT NULL COMMENT '中文注释测试', `father` varchar(12) DEFAULT NULL, PRIMARY KEY (`id`), KEY `idx_area` (`area`) ) ENGINE=InnoDB AUTO_INCREMENT=3162 DEFAULT CHARSET=utf8 ROW_FORMAT=COMPACT KEY_BLOCK_SIZE=8;

### 2.1 归档到操作系统文件 ###

pt-archiver --source h=xxx.mysql.rds.aliyuncs.com,P=3306,u=jacky,p=xxx,D=jacky,t=my_tab --charset=utf8 --file '/var/tmp/%Y-%m-%d-%D.%t' --where "id > 3008" --limit 1000 --commit-each --no-version-check # h=xxx.mysql.rds.aliyuncs.com -- RDS 实例地址 # P=3306 -- RDS 实例端口 # u=jacky -- RDS 实例用户 # p=xxx -- RDS 实例用户密码 # D=jacky -- RDS 实例数据库 # t=my_tab -- RDS 实例表名称 # --source -- 指定要被归档的数据源 # --charset=utf8 -- 使用的字符集，需与表字符集一致；否则指定 --no-check-charset 参数 # --file -- 指定目标操作系统文件名 # --where "id > 3008" -- 指定 where 过滤条件，过滤出要归档的数据 # --limit 1000 -- 每条语句读取和归档的数据行数，默认是 1 # --commit-each -- 每次获取和归档数据后，commit提交 # --no-version-check -- 不做版本检查，RDS MySQL 必须设置

归档后，源表数据减少 150 行。

![1 6][]

归档操作系统文件中，包含 150 行数据。

![1 7][]

### 2.2 归档到另外一个表中 ###

可以将数据归档到同实例不同库下的表中，也可以将数据归档到不同实例下的表中。

归档操作前，目标表要存在。

#### 2.2.1 归档到同实例不同库下的表 ####

pt-archiver --source h=rds01.mysql.rds.aliyuncs.com,P=3306,u=jacky,p=xxx,D=jacky,t=my_tab --charset=utf8 --dest h=rds01.mysql.rds.aliyuncs.com,P=3306,u=jacky,p=xxx,D=my_db,t=my_tab --where "id > 2000" --limit 1000 --commit-each --no-version-check # --dest -- 指定归档到的目标表

目标表中增加了 1008 行数据。

![1 8][]

源表中减少了 1008 行数据，剩余 2000 行数据。

![1 9][]

#### 2.2.2 归档到不同实例下的表 ####

pt-archiver --source h=rds01.mysql.rds.aliyuncs.com,P=3306,u=jacky,p=xxx,D=jacky,t=my_tab --charset=utf8 --dest h=rds02.mysql.rds.aliyuncs.com,P=3306,u=jacky,p=xxx,D=jacky,t=my_tab --where "id > 500" --limit 1000 --commit-each --no-version-check

目标表中增加了 1500 行数据。

![1 10][]

源表中减少了 1500 行数据，剩余 500 行数据。

![1 11][]

#### 2.2.3 通过 bulk insert 加速归档过程 ####

当需要归档的数据量很大，比如第一次做归档的时候，可以考虑通过尝试 bulk insert 的方式来加速归档过程。

pt-archiver --source h=rds01.mysql.rds.aliyuncs.com,P=3306,u=jacky,p=xxx,D=rd_test,t=large_tab_04 --charset=utf8 --dest h=rds02.mysql.rds.aliyuncs.com,P=3306,u=jacky,p=xxx,D=jacky,t=large_tab_04 --where "1=1" --limit 5000 --commit-each --bulk-insert --no-version-check --statistics # --bulk-insert -- 批量插入数据，会自动启用 --bulk-delete --commit-each，每次插入行数通过--limit 选项指定 # --statistics -- 显示 pt-archiver 本次操作的统计信息。

*  [数据库][Link 1]
 *  [MySQL][]

[RDS MySQL _ Metadata lock]: https://link.juejin.im/?target=https%3A%2F%2Fwww.atatech.org%2Farticles%2F67885
[1]: https://user-gold-cdn.xitu.io/2017/4/24/eec959019c6db2cef70b25bac9ac3f93.png?imageView2/0/w/1280/h/960/format/webp/ignore-error/1
[1 1]: https://user-gold-cdn.xitu.io/2017/4/24/c3c0c4675dc97b0ed3adb87656b53b75.png?imageView2/0/w/1280/h/960/format/webp/ignore-error/1
[1 2]: https://user-gold-cdn.xitu.io/2017/4/24/2365c70c1dd5fc2f79c3467244ed6630.png?imageView2/0/w/1280/h/960/format/webp/ignore-error/1
[1 3]: https://user-gold-cdn.xitu.io/2017/4/24/4b8b5deec27d209865f4f62f8dbc551e.png?imageView2/0/w/1280/h/960/format/webp/ignore-error/1
[1 4]: https://user-gold-cdn.xitu.io/2017/4/24/57e3a64b095dfc0e6f7eb9df9294a172.png?imageView2/0/w/1280/h/960/format/webp/ignore-error/1
[1 5]: https://user-gold-cdn.xitu.io/2017/4/24/5c2b3223b3bdf5d63520a9b36a118d14.png?imageView2/0/w/1280/h/960/format/webp/ignore-error/1
[1 6]: https://user-gold-cdn.xitu.io/2017/4/24/f14a14d04e164a9729da0b0663d87f8d.png?imageView2/0/w/1280/h/960/format/webp/ignore-error/1
[1 7]: https://user-gold-cdn.xitu.io/2017/4/24/87a251edb6be996d2bafc3ffadd9d59a.png?imageView2/0/w/1280/h/960/format/webp/ignore-error/1
[1 8]: https://user-gold-cdn.xitu.io/2017/4/24/b0209e30db4badb1b679580f1111b1ba.png?imageView2/0/w/1280/h/960/format/webp/ignore-error/1
[1 9]: https://user-gold-cdn.xitu.io/2017/4/24/b35b846c2b509e71542fc5e501ee1ca8.png?imageView2/0/w/1280/h/960/format/webp/ignore-error/1
[1 10]: https://user-gold-cdn.xitu.io/2017/4/24/f7588f7248ff3d6d6b2f80a76e1fa770.png?imageView2/0/w/1280/h/960/format/webp/ignore-error/1
[1 11]: https://user-gold-cdn.xitu.io/2017/4/24/a67fdb81f3243d31a937f31c3b26fbc1.png?imageView2/0/w/1280/h/960/format/webp/ignore-error/1
[Link 1]: https://juejin.im/tag/%E6%95%B0%E6%8D%AE%E5%BA%93
[MySQL]: https://juejin.im/tag/MySQL