[原创]pt-archiver 归档历史数据及参数详解
目录
- 1. 背景
- 2. 操作步骤
- 2.1. 确认数据归档条件，此次操作开发按照非主键列 server_time 按时间进行删除并保存，需要转化为主键列条件。
- 2.2. 由于历史表文件较大，按月归档、删除，便于操作及后期查询数据，在目标库中，每个月份创建一张 duplicate 表。
- 2.3. 参照 pt-archiver 工具参数及使用示例，预先编辑好归档命令（参照“5. 场景示例” 中的 “A.”）。
- 2.4. 执行归档命令按月归档历史数据。
- 2.5. 归档完毕后，使用 where 条件去源数据，对比原表与归档表数据行是否一致。
- 2.6. 确认归档数据一致后，对归档表备份导出。
- 2.7. 参照 pt-archiver 工具参数及使用示例，预先编辑好删除历史数据命令（参照“5. 场景示例” 中的 “B.”）。
- 2.8. 或者使用脚本进行删除操作。
- 2.9. 备份完成后，执行删除历史数据操作。
- 3. 工具介绍
- 4. 工具参数
- 5、场景示例
1. 背景
12月5日开始，某线上实例每天报警从库延迟。
经排查发现，延迟时段的 tps 并不高，只是对 matomo_log_link_visit_action 表有 1k多的 DML，但该表文件大小已达 60G，远超单表最大 10G 的规范要求。
该实例为点击流数据库，该表记录的主要记录用户的链接访问行为。
经过与开发负责人沟通，需要对该表进行归档历史数据操作，并保留1个月内的数据。
2. 操作步骤
2.1. 确认数据归档条件，此次操作开发按照非主键列 server_time 按时间进行删除并保存，需要转化为主键列条件。
show create table matomo_log_link_visit_action\G
select max(idlink_va) from matomo_log_link_visit_action where server_time='2018-04-30 23:59:59';
select max(idlink_va) from matomo_log_link_visit_action where server_time='2018-05-31 23:59:59';
select max(idlink_va) from matomo_log_link_visit_action where server_time='2018-06-30 23:59:59';
select max(idlink_va) from matomo_log_link_visit_action where server_time='2018-07-31 23:59:59';
select max(idlink_va) from matomo_log_link_visit_action where server_time='2018-08-31 23:59:59';
select max(idlink_va) from matomo_log_link_visit_action where server_time='2018-09-30 23:59:59';
select max(idlink_va) from matomo_log_link_visit_action where server_time='2018-10-31 23:59:59';
2.2. 由于历史表文件较大，按月归档、删除，便于操作及后期查询数据，在目标库中，每个月份创建一张 duplicate 表。
create table visit_action_4 like matomo_log_link_visit_action;
create table visit_action_5 like matomo_log_link_visit_action;
create table visit_action_6 like matomo_log_link_visit_action;
create table visit_action_7 like matomo_log_link_visit_action;
create table visit_action_8 like matomo_log_link_visit_action;
create table visit_action_9 like matomo_log_link_visit_action;
create table visit_action_10 like matomo_log_link_visit_action;
2.3. 参照 pt-archiver 工具参数及使用示例，预先编辑好归档命令（参照“5. 场景示例” 中的 “A.”）。
# 4月归档
pt-archiver --source h=127.0.0.1,P=3306,u=superadmin,p='xxx',D=xxx,t=matomo_log_link_visit_action --charset 'UTF8' --dest h=127.0.0.1,P=3306,u=xxx,p='xxx',D=xxx,t=visit_action_4 --no-version-check --where "idlink_va <= 4383363" --statistics --no-delete --bulk-insert --progress 5000 --limit=500 --txn-size=100 >> xxx--matomo_log_link_visit_action--visit_action_4.log &
# 5月归档
pt-archiver --source h=127.0.0.1,P=3306,u=superadmin,p='xxx',D=xxx,t=matomo_log_link_visit_action --charset 'UTF8' --dest h=127.0.0.1,P=3306,u=xxx,p='xxx',D=xxx,t=visit_action_5 --no-version-check --where "idlink_va <= 26473975 and idlink_va > 4383363" --statistics --no-delete --bulk-insert --progress 5000 --limit=500 --txn-size=100 >> xxx--matomo_log_link_visit_action--visit_action_5.log &
# 6月归档
pt-archiver --source h=127.0.0.1,P=3306,u=superadmin,p='xxx',D=xxx,t=matomo_log_link_visit_action --charset 'UTF8' --dest h=127.0.0.1,P=3306,u=xxx,p='xxx',D=xxx,t=visit_action_6 --no-version-check --where "idlink_va <= 51504119 and idlink_va > 26473975" --statistics --no-delete --bulk-insert --progress 5000 --limit=500 --txn-size=100 >> xxx--matomo_log_link_visit_action--visit_action_6.log &
# 7月归档
pt-archiver --source h=127.0.0.1,P=3306,u=superadmin,p='xxx',D=xxx,t=matomo_log_link_visit_action --charset 'UTF8' --dest h=127.0.0.1,P=3306,u=xxx,p='xxx',D=xxx,t=visit_action_7 --no-version-check --where "idlink_va <= 75811899 and idlink_va > 51504119" --statistics --no-delete --bulk-insert --progress 5000 --limit=500 --txn-size=100 >> xxx--matomo_log_link_visit_action--visit_action_7.log &
# 8月归档
pt-archiver --source h=127.0.0.1,P=3306,u=superadmin,p='xxx',D=xxx,t=matomo_log_link_visit_action --charset 'UTF8' --dest h=127.0.0.1,P=3306,u=xxx,p='xxx',D=xxx,t=visit_action_8 --no-version-check --where "idlink_va <= 121711398 and idlink_va > 75811899" --statistics --no-delete --bulk-insert --progress 5000 --limit=500 --txn-size=100 >> xxx--matomo_log_link_visit_action--visit_action_8.log &
# 9月归档
pt-archiver --source h=127.0.0.1,P=3306,u=superadmin,p='xxx',D=xxx,t=matomo_log_link_visit_action --charset 'UTF8' --dest h=127.0.0.1,P=3306,u=xxx,p='xxx',D=xxx,t=visit_action_9 --no-version-check --where "idlink_va <= 150953368 and idlink_va > 121711398" --statistics --no-delete --bulk-insert --progress 5000 --limit=500 --txn-size=100 >> xxx--matomo_log_link_visit_action--visit_action_9.log &
# 10月归档
pt-archiver --source h=127.0.0.1,P=3306,u=superadmin,p='xxx',D=xxx,t=matomo_log_link_visit_action --charset 'UTF8' --dest h=127.0.0.1,P=3306,u=xxx,p='xxx',D=xxx,t=visit_action_10 --no-version-check --where "idlink_va <= 206555065 and idlink_va > 150953368" --statistics --no-delete --bulk-insert --progress 5000 --limit=500 --txn-size=100 >> xxx--matomo_log_link_visit_action--visit_action_10.log &
2.4. 执行归档命令按月归档历史数据。
2.5. 归档完毕后，使用 where 条件去源数据，对比原表与归档表数据行是否一致。
--4月
select count(*) from matomo_log_link_visit_action where idlink_va <= 4383363;
select count(*) from visit_action_4 where idlink_va <= 4383363;
--5月
select count(*) from matomo_log_link_visit_action where idlink_va <= 4383363;
select count(*) from visit_action_4 where idlink_va <= 4383363;
--6月
select count(*) from matomo_log_link_visit_action where idlink_va <= 4383363;
select count(*) from visit_action_4 where idlink_va <= 4383363;
--7月
select count(*) from matomo_log_link_visit_action where idlink_va <= 4383363;
select count(*) from visit_action_4 where idlink_va <= 4383363;
--8月
select count(*) from matomo_log_link_visit_action where idlink_va <= 4383363;
select count(*) from visit_action_4 where idlink_va <= 4383363;
--9月
select count(*) from matomo_log_link_visit_action where idlink_va <= 4383363;
select count(*) from visit_action_4 where idlink_va <= 4383363;
--10月
select count(*) from matomo_log_link_visit_action where idlink_va <= 4383363;
select count(*) from visit_action_4 where idlink_va <= 4383363;
2.6. 确认归档数据一致后，对归档表备份导出。
mysqldump -S xxx/mysql.sock --single-transaction --master-data=2 --set-gtid-purged=OFF --no-create-info xxx visit_action_4 > /data/backup/xxx_history/xxx--visit_action_4.sql.bak
mysqldump -S xxx/mysql.sock --single-transaction --master-data=2 --set-gtid-purged=OFF --no-create-info xxx visit_action_5 > /data/backup/xxx_history/xxx--visit_action_5.sql.bak
mysqldump -S xxx/mysql.sock --single-transaction --master-data=2 --set-gtid-purged=OFF --no-create-info xxx visit_action_6 > /data/backup/xxx_history/xxx--visit_action_6.sql.bak
mysqldump -S xxx/mysql.sock --single-transaction --master-data=2 --set-gtid-purged=OFF --no-create-info xxx visit_action_7 > /data/backup/xxx_history/xxx--visit_action_7.sql.bak
mysqldump -S xxx/mysql.sock --single-transaction --master-data=2 --set-gtid-purged=OFF --no-create-info xxx visit_action_8 > /data/backup/xxx_history/xxx--visit_action_8.sql.bak
mysqldump -S xxx/mysql.sock --single-transaction --master-data=2 --set-gtid-purged=OFF --no-create-info xxx visit_action_9 > /data/backup/xxx_history/xxx--visit_action_9.sql.bak
mysqldump -S xxx/mysql.sock --single-transaction --master-data=2 --set-gtid-purged=OFF --no-create-info xxx visit_action_10 > /data/backup/xxx_history/xxx--visit_action_10.sql.bak
2.7. 参照 pt-archiver 工具参数及使用示例，预先编辑好删除历史数据命令（参照“5. 场景示例” 中的 “B.”）。
pt-archiver --source h=127.0.0.1,P=3306,u=xxx,p='xxx',D=xxx,t=matomo_log_link_visit_action --charset UTF8 --no-version-check --where="idlink_va <= 206555065" --progress=5000 --limit=500 --purge --bulk-delete --commit-each --sleep=1 --statistics >> xxx--matomo_log_link_visit_action--archive_10.log &
2.8. 或者使用脚本进行删除操作。
#!/bin/bash
USER=xxx
PWD="xxx"
SOCK=xxx/mysql.sock
DB=xxx
TB=matomo_log_link_visit_action
MAX_ID=206555065
NUM=1000
PK=idlink_va
MY_CLI="/usr/local/bin/mysql -u${USER} -p${PWD} -S${SOCK} ${DB}"
for ((i=i;i<206556;i++));do
${MY_CLI} -e "delete from ${TB} where ${PK} <= ${MAX_ID} limit ${NUM};"
sleep 0.2;
echo $i;
done
2.9. 备份完成后，执行删除历史数据操作。
3. 工具介绍
-
因有归档数据的要求，故选用 pt-archiver 工具进行操作。
-
pt-archiver 是 percona toolkit 工具集中的工具，主要用于数据删除、数据归档、数据迁移等场景。
4. 工具参数
4.1. 连接数据库的参数
| varable | Meaning |
|---|---|
| --source=d | DSN specifying the table to archive from (required) |
| --dest=d | DSN specifying the table to archive to |
| -h , --host=s | Connect to host |
| -P , --port=i | Port number to use for connection |
| -S , --socket=s | Socket file to use for connection |
| -u , --user=s | User for login if not current user |
| -p , --password=s | Password to use when connecting |
| -D , --database=s | Database that contains the table |
| t | Table to archive from/to |
| -A , --charset=s | Default character set |
| F | Only read default options from the given file |
| L | Explicitly enable LOAD DATA LOCAL INFILE |
| a | Database to USE when executing queries |
| b | If true, disable binlog with SQL_LOG_BIN |
| i | Index to use |
| m | Plugin module name |
4.2. 常用参数
| variable | meaning | example |
|---|---|---|
| --[no]version-check | Check for the latest version of Percona Toolkit, MySQL, and other programs (default yes) WHERE clause to limit which rows to archive (required) | --no-version-check目前为止，发现部分pt工具对阿里云RDS操作必须加这个参数 |
| --where=s | WHERE clause to limit which rows to archive (required) | 设置操作条件 |
| --statistics | Collect and print timing statistics | 输出执行过程及最后的操作统计 |
| --no-delete | Do not delete archived rows | 不删除已经归档的 rows |
| --bulk-insert | Insert each chunk with LOAD DATA INFILE (implies --bulk-delete --commit-each) | 批量插入数据到dest主机(看 dest 的 general log 发现它是通过在dest主机上 LOAD DATA LOCAL INFILE 插入数据的) |
| --progress=i | Print progress information every X rows | --progress=5000 每处理 5000 rows 输出一次处理信息 |
| --limit=i | Number of rows to fetch and archive per statement (default 1) | --limit=500每次取 500 rows 数据给 pt-archiver 处理 |
| --txn-size=i | Number of rows per transaction (default 1) | --txn-size=100 设置 100 rows 为一个事务提交一次 |
| --sleep=i | Sleep time between fetches | --sleep=1每次归档了 limit 个行记录后的休眠1秒（单位为秒） |
| --bulk-delete | Delete each chunk with a single statement (implies --commit-each) | 批量删除 source 上的旧数据(例如每次 1000 rows 的批量删除操作) |
| --replace | Causes INSERTs into --dest to be written as REPLACE | 将insert into 语句改成 replace 写入到 dest 库 |
| --file=s | File to archive to, with DATE_FORMAT()-like formatting | --file '/root/test.txt' 导出文件的路径 |
| --purge | Purge instead of archiving; allows omitting --file | 删除 source 数据库的相关匹配记录 |
| --header | Print column header at top of --file | 输入列名称到首行（和--file一起使用） |
| --[no]check-columns | Ensure --source and --dest have same columns (default yes) | 检验 dest 和 source 的表结构是否一致，不一致自动拒绝执行（不加这个参数也行。默认就是执行检查的） |
| --chekc-interval=m | If --check-slave-lag is given, this defines how long the tool pauses each time it discovers that a slave is lagging (default 1s). Optional suffix s=seconds, m=minutes, h=hours, d=days; if no suffix, s is used. | 默认1s检查一次 |
| --local | Do not write OPTIMIZE or ANALYZE queries to binlog | 不把 optimize 或 analyze 操作写入到 binlog 里面（防止造成主从延迟巨大） |
| --retries | Number of retries per timeout or deadlock (default 1) | 超时或者出现死锁的话，pt-archiver 进行重试的间隔（默认1s） |
| --analyze=s | Run ANALYZE TABLE afterwards on --source and/or --dest | 操作结束后，优化表空间 |
5、场景示例
A. 复制数据到其他（实例、库、表），且不删除source的数据(指定字符集)：
pt-archiver --source h=127.0.0.1,P=3306,u=xxx,p='xxx',D=xxx,t=matomo_log_link_visit_action --charset 'UTF8' \
--dest h=127.0.0.1,P=3306,u=xxx,p='xxx',D=xxx,t=visit_action_4 --no-version-check \
--where="idlink_va <= 4383363" \
--statistics --no-delete --bulk-insert --progress=5000 --limit=500 --txn-size=100 >> xxx--matomo_log_link_visit_action--visit_action_4.log &
B. 删除旧数据：
pt-archiver --source h=127.0.0.1,P=3306,u=xxx,p='xxx',D=xxx,t=matomo_log_link_visit_action \
--charset 'UTF8' --no-version-check --where "idlink_va <= 4383363" \
--progress=5000 --limit=500 --purge --bulk-delete --commit-each --sleep=1 --statistics >> xxx--matomo_log_link_visit_action--archive_4.log &
C. 导出数据到文件
pt-archiver --source h=127.0.0.1,P=3306,u=xxx,p='xxx',D=xxx,t=matomo_log_link_visit_action \
--where "idlink_va <= 4383363" \
--file ‘xxx/2018-04.txt‘ \
--statistics --no-delete --bulk-insert --progress=5000 --limit=500 --txn-size=100 >> xxx--matomo_log_link_visit_action--visit_action_4.log &
D. 导出数据到文件并删除数据库的相关行：
pt-archiver --source h=127.0.0.1,P=3306,u=xxx,p='xxx',D=xxx,t=matomo_log_link_visit_action \
--charset 'UTF8' --no-version-check --where "idlink_va <= 4383363" \
--file ‘xxx/2018-04.txt‘ \
--progress=5000 --limit=500 --purge --bulk-delete --commit-each --sleep=1 --statistics >> xxx--matomo_log_link_visit_action--archive_4.log &
作者： do康解U
出处： https://www.cnblogs.com/David-domain/>
关于作者：专注数据库、Python 开发、ROS、深度学习，请多多赐教！
本文版权归作者和博客园共有，欢迎转载，但未经作者同意必须保留此段声明，且在文章页面明显位置给出, 原文链接 如有问题， 可邮件（iamkuboy@163.com）咨询.
