pt-archiver

说明

将一个表的数据导入到另一个表或文件中。

语法:

pt-archiver [OPTIONS]

pt-archiver从MySQL表中抓取记录。-source和-dest参数使用DSN语法;如果COPY为yes， -dest默认值为-source中指定的值，也就是可以公用(如果值相同的话)。

常用参数：

  • –limit 10000 每次取1000行数据用pt-archive处理，Number of rows to fetch and archive per statement.
  • –txn-size 1000 设置1000行为一个事务提交一次，Number of rows pertransaction.
  • –where ‘id<3000’ 设置操作条件
  • –progress 5000 每处理5000行输出一次处理信息
  • –statistics 输出执行过程及最后的操作统计。（只要不加上–quiet，默认情况下pt-archive都会输出执行过程的）
  • –charset=UTF8 指定字符集为UTF8
  • –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检查一次
  • –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用以回收表空间。

具体例子

例子1：表到表

将主机：192.168.20.66上的数据库：test中的表cms_areas的前1000行拷贝到主机192.168.20.5上的数据库：lei中表

注意：要保证两个数据库的默认值字符集相同，如果不同则会报错(可以通过指定​​--no-check-charset​​选项不检查字符)，目标表要先创建表结构。

#字符集不同
Character set mismatch: --source DSN uses latin1, table uses utf8.  You can disable this check by specifying --no-check-charset.

#目标表不存在
DBD::mysql::db selectrow_hashref failed: Table 'lei.cms_areas_bak' doesn't exist [for Statement "SHOW CREATE TABLE `lei`.`cms_areas_bak`"] at /bin/pt-archiver line 1923.

结果：

[root@BigData ~]# pt-archiver --source h=192.168.20.66,u=root,p=123,P=3306,D=test,t=cms_areas --dest h=192.168.20.5,u=root,p=Sanshi_408,P=3306,D=lei,t=cms_areas_bak --where "1=1" --limit 1000 --commit-each  --no-check-charset --charset=UTF8
*******************************************************************
 Using the default of SSL_verify_mode of SSL_VERIFY_NONE for client
 is deprecated! Please set SSL_verify_mode to SSL_VERIFY_PEER
 possibly with SSL_ca_file|SSL_ca_path for verification.
 If you really don't want to verify the certificate and keep the
 connection open to Man-In-The-Middle attacks please set
 SSL_verify_mode explicitly to SSL_VERIFY_NONE in your application.
*******************************************************************
  at /bin/pt-archiver line 4907.
*******************************************************************
 Using the default of SSL_verify_mode of SSL_VERIFY_NONE for client
 is deprecated! Please set SSL_verify_mode to SSL_VERIFY_PEER
 possibly with SSL_ca_file|SSL_ca_path for verification.
 If you really don't want to verify the certificate and keep the
 connection open to Man-In-The-Middle attacks please set
 SSL_verify_mode explicitly to SSL_VERIFY_NONE in your application.
*******************************************************************
  at /bin/pt-archiver line 4907.

# A software update is available:

查看目标表数据：

mysql> select count(*) from cms_areas_bak;
+----------+
| count(*) |
+----------+
|     3509 |
+----------+
1 row in set (0.00 sec)

此时原表中的数据就没了，被迁移到了目标表。

注意：表到表要指定字符集，否则可能会造成中文为？。

例子2:表到表和文件

可以将数据同时迁移到表和文件中，无法单独迁移到文件中。​​--header​​表示在文件头部显示列名。

[root@BigData ~]# pt-archiver --source h=192.168.20.5,u=root,p=Sanshi_408,P=3306,D=lei,t=cms_areas_bak --dest h=192.168.20.66,u=root,p=123,P=3306,D=test,t=cms_areas --file '/var/log/archive/%Y-%m-%d-%D.%t' --where "1=1" --limit 1000 --commit-each  --no-check-charset --header

文件内容

[root@BigData ~]# more /var/log/archive/2019-07-26-lei.cms_areas_bak
id

更多例子

删除老数据(单独的删数据操作不用指定字符集)：

pt-archiver \
--source h=localhost,u=root,p=1234,P=3306,D=test,t=t \
--no-check-charset --where 'a<=376'

复制数据到其他mysql实例，且不删除source的数据(指定字符集)：

pt-archiver \
--source h=localhost,u=root,p=1234,P=3306,D=test,t=t1\
--dest h=192.168.2.12,P=3306,u=archiver,p=archiver,D=test,t=t1_bak \
--progress 5000 --where 'mc_id<=125' \
--statistics --charset=UTF8 --limit=10000 --txn-size 1000 --no-delete

复制数据到其他mysql实例，并删source上的旧数据(指定字符集)：

pt-archiver \
--source h=localhost,u=root,p=1234,P=3306,D=test,t=t1 \
--dest h=192.168.2.12,P=3306,u=archiver,p=archiver,D=test,t=t1_his \
--progress 5000 --where "CreateDate <'2017-05-01 00:00:00' " \
--statistics --charset=UTF8 --limit=10000 --txn-size 1000 --bulk-delete

复制数据到其他mysql实例，不删除source数据，但是使用批量插入dest上新的数据(指定字符集)：

pt-archiver \
--source h=localhost,u=archiver,p=archiver,P=3306,D=test,t=t1 \
--dest h=192.168.2.12,P=3306,u=archiver,p=archiver,D=test,t=t1_his \
--progress 5000 --where "c <'2017-05-01 00:00:00' " \
--statistics --charset=UTF8 --limit=10000 --txn-size 1000 --no-delete  --bulk-insert

测试用的一张只有3列元素的表，共计9万行数据。使用bulk-insert用时7秒钟。而常规insert用时40秒。

导出数据到文件：

pt-archiver \
--source h=10.0.20.26,u=root,p=1234,P=3306,D=test,t=t \
--file '/root/test.txt' \
--progress 5000 --where 'a<12000' \
--no-delete --statistics --charset=UTF8 --limit=10000 --txn-size 1000

导出数据到文件并删除数据库的相关行：

pt-archiver \
--source h=10.0.20.26,u=root,p=1234,P=3306,D=test,t=t \
--file '/root/test.txt' \
--progress 5000 --where 'a<12000' \
--statistics --charset=UTF8 --limit=10000 --txn-size 1000 --purge

更多信息，请查看官方文档：​​地址​​