pt-archiver数据归档的使用以及案例（批量demo）

前言

  公司需要针对当前订单库中的表数据进行归档以及清除部分日志表，基于这个场景最终选择使用pt-archiver作为数据归档清除工具，本文包括基础安装，使用过程中遇到的问题及最终跑批shell脚本

pt-archiver为 percona-toolkit的数据库归档组件
percona-toolkit是一组高级命令行工具的集合，用来执行各种通过手工执行非常复杂和麻烦的mysql和系统任务，包括：

1. 检查master和slave数据的一致性

2. 有效地对记录进行归档

3. 查找重复的索引

4. 对服务器信息进行汇总

5. 分析来自日志和tcpdump的查询

6. 当系统出问题的时候收集重要的系统

官方文档

pt-archiver (percona.com)

下载安装

下载地址：官方下载地址

或者在服务器通过wget 执行下载

wget “https://www.percona.com/downloads/percona-toolkit/3.4.0/binary/tarball/percona-toolkit-3.0.3_x86_64.tar.gz”

具体安装流程参考下载及安装博文

疑难杂症

异常部分内容 an't locate Digest/MD5.pm in @INC (@INC contains: 
解决：yum -y install perl-Digest-MD5 

 Run 'perl -MDBI' to see the directories that Perl searches for DBI.  If DBI is not installed, try:
  Debian/Ubuntu  apt-get install libdbi-perl
  RHEL/CentOS    yum install perl-DBI
  OpenSolaris    pkg install pkg:/SUNWpmdbi

解决： yum install perl-DBD-MySQL
 yum install perl-DBI

pt-archiver执行报错，一定要检查命令中的空格，符号。

参数详解

常用参数	含义
--source/--dest	源端/目标端
h/D/t/u/p	主机IP/数据库名/表名/用户/密码
--where	操作条件
--(no-)check-charset	检查连接的字符集与表的字符集是否一致.
--limit  X	每次取X行数据用pt-archive处理
--txn-size X	设置X行为一个事务提交一次
--progress X	每处理X行输出一次处理信息
--statistics	输出执行过程及最后的操作统计
--bulk-delete	批量删除source上的旧数据
--bulk-insert	批量插入数据到dest主机(实际LOAD DATA插入)
--replace	将insert into 语句改成replace写入到dest库
--purge	删除source数据库的相关匹配记录
--file	输出为本地文件 %d    Day of the month, numeric (01..31) %H    Hour (00..23) %i    Minutes, numeric (00..59) %m    Month, numeric (01..12) %s    Seconds (00..59) %Y    Year, numeric, four digits %D    Database name %t    Table name
--header	本地文件头部加入列名

简单示例

-- 清除指定条件表的数据

pt-archiver  --source h=127.0.0.1,P=3306,u=test,p=test,D=order,t=order_test  --no-check-charset  --purge  --where  'create_time<"2021-05-01 00:00:00"'  --progress5000  --limit=1000  --bulk-delete  --txn-size=1000  --statistics --analyze=s
 

#不检查字符集，匹配删除源数据，条件为create_time，5000条打印一次执行日志，每次抓取1000条，批量删除，1000条提交一次事务，打印执行过程和结果，清理源数据空间

-- 归档数据-》order_test表一部分数据迁移order_test_his表

pt-archiver \
  --source h=127.0.0.1,P=3306,u=root,p=8cc924*0f5C319d0Lca2014,D=order,t=scc_order_info \
  --dest h=127.0.0.1,P=3306,u=root,p=8cc924*0f5C319d0Lca2014,D=order,t=scc_order_info_his \
  --no-check-charset \
  --purge \
  --where "order_id < 945429613360300032" \
  --progress=5000 \
  --limit=1000 \
  --txn-size=1000 \
  --statistics \
  --analyze=s 

#不检查字符集，匹配删除源数据，条件为orderId，5000条打印一次执行日志，每次抓取1000条，1000条提交一次事务，打印执行过程和结果，清理源数据空间

shell批量执行备份清除脚本

脚本解释：

脚本分为备份和清除，清除会根据创建时间来进行清除

备份首先会去查询主表中设定时间中最大的订单id（使用主键索引），然后后续条件根据订单id来进行迁移-》需要保证订单的生成是有序的，如果使用的哈希主键，则根据其他条件进行迁移

#-------------------------------------------------------------------------------- 
#脚本名称：  pt-archiver.sh 
#脚本功能：  归档大表到历史表 
#更 新 人：   m
#更新日期：  2022-07-21 
#-------------------------------------------------------------------------------- 
  
#EXEC_DATE=`date +%Y-%m-%d` 
#TABLE_DATE=`date +%Y%m%d` 
EXEC_DATE="2021-05-01" 
#TABLE_DATE="20170513" 
FILE_NAME=`basename $0 .sh` 
cat /dev/null >pt_archiver_table.sh
#链接库的账号密码
USER="test" 
PASSWORD="test" 
IP="127.0.0.1"

DELETE_FLITER_FIELD_CREATE_TIME=create_time
DELETE_FLITER_FIELD_ORDER=order_id
#得到数据文件和源表名值 
SOURCE_DB="order"
DEST_DB="order_his"

#需要备份的表  
sicherungen_table=(sale_order order_info order_sku order_erp_sku)
  
#需要删除的表 
clean_table=(order_log order_o2o_log)

#打印基础参数
echo "当前执行参数打印：EXEC_DATE:$EXEC_DATE,FILE_NAME:$FILE_NAME}" >> $FILE_NAME.log 

  
#获取当前时间最大的的orderId
max_order_id=$(mysql -h${IP} -P3306 -u${USER} -p${PASSWORD} -D ${SOURCE_DB} -e "select order_id from order_info where create_time <=\""${EXEC_DATE}" 00:00:00\" order by order_id desc limit 1;"|tail -1)
echo "获取当前时间最大的的orderId:$max_order_id" >> $FILE_NAME.log 
  
for i in ${sicherungen_table[*]}
do       
mysql -h$IP -P3306 -u$USER -p$PASSWORD -D $SOURCE_DB -e "create table if not exists ${i}_his like $i"
if [ $? -ne 0 ] ;then 
echo "ERROR:create table $HISTABLE_NAME error!" >> $FILE_NAME.log
exit 1 
fi 

#生成备份脚本并执行
echo "pt-archiver \
 --source h=$IP,P=3306,u=$USER,p=$PASSWORD,D=$SOURCE_DB,t=$i \
 --dest h=$IP,P=3306,u=$USER,p=$PASSWORD,D=$DEST_DB,t=${i}_his\
 --no-check-charset \
 --purge \
 --where \"order_id < $max_order_id\" \
 --progress=5000 \
 --limit=1000 \
 --txn-size=1000 \
 --statistics \
 --analyze=s" > pt_archiver_table.sh  
 
echo "#$i表备份数据脚本生成成功">> $FILE_NAME.log
cat pt_archiver_table.sh >> $FILE_NAME.log

echo "---------------------------------------------------------------pt-archiver 执行备份数据 表:$i --->开始" >> $FILE_NAME.log
sh pt_archiver_table.sh >> $FILE_NAME.log
echo "---------------------------------------------------------------pt-archiver 执行备份数据结束" >> $FILE_NAME.log

done 

#生成清除脚本并执行
for i in ${clean_table[*]}
do
echo "pt-archiver \
 --source h=$IP,P=3306,u=$USER,p=$PASSWORD,D=$SOURCE_DB,t=$i \
 --no-check-charset \
 --purge \
 --where  'create_time<\"$EXEC_DATE 00:00:00\"' \
 --progress=5000 \
 --limit=1000 \
 --bulk-delete \
 --txn-size=1000 \
 --statistics\
 --analyze=s"  > pt_archiver_table.sh  
 
echo "#$i表清除历史数据脚本生成成功" >> $FILE_NAME.log
cat pt_archiver_table.sh >> $FILE_NAME.log

echo "---------------------------------------------------------------pt-archiver 执行清除数据 表:$i --->开始" >> $FILE_NAME.log
sh pt_archiver_table.sh >> $FILE_NAME.log
echo "---------------------------------------------------------------pt-archiver 执行清除数据结束" >> $FILE_NAME.log

done

echo "任务全部结束！！！！！！！！！！！！！！开始清除pt_archiver_table.sh内容" >> $FILE_NAME.log
cat /dev/null >pt_archiver_table.sh

学习引用相关

下载及安装博文

常用参数详解

目录

前言

官方文档

下载安装

疑难杂症

参数详解

简单示例

shell批量执行备份清除脚本

学习引用相关下载及安装博文

---