想要查看前面的笔记请翻阅我的CSDN博客,作者码字不易,喜欢的话点赞,加个关注吧,后期还有很多干货等着你!
使用场景:当前项目用户量剧增,单表存储量过大,需要拆分
介绍pt-archiver
官方文档:https://www.percona.com/doc/percona-toolkit/3.0/pt-archiver.html
使用条件
- 需要表有主键
- 默认迁移完成是会删除源表数据的。
- 从一张表导入到另外一张表,要注意的是新表必须是已经建立好的一样的表结构,不会自动创建表,而且where条件是必须指定的
- 不会自动执行analyze和optimize,有需要可以在迁移完成后低峰期执行
注意:若没有加上参数 --no-safe-auto-increment导出和删除都会少掉最后一条记录(最大的ID值),这个是由于8.0之前重启会根据最大值设定auto-increment,pt放在最大的序列变化而不变更最后一条
若归档过程中,源数据被变更了,可能出现数据不一致,例如id为3的数据,在执行归档时已读取过这部分值,在归档过程中源又被修改了值,那么这个被修改的值是不会被记录到归档表中去的。
可使用–for-update或–share-lock对读取数据锁定不允许修改。但会影响源数据业务。
安装
# 安装依赖
yum install perl-DBI perl-DBD-MySQL perl-Digest-MD5 perl-IO-Socket-SSL perl-TermReadKey
# 下载rpm包
wget https://www.percona.com/downloads/percona-toolkit/3.2.0/binary/redhat/7/x86_64/percona-toolkit-3.2.0-1.el7.x86_64.rpm
# 安装
rpm -ivh percona-toolkit-3.2.0-1.el7.x86_64.rpm
说明
可以使用DSN方式来连接数据库,DSN选项为key=value方式,在等号的两侧不能有空格出现,并且区分大小写,多个选项之前以’,’(逗号)隔开
a 归档操作是在哪个库下进行的,相当于USE操作。
A 指定默认字符集。
b 当值为true时,禁止SQL_LOG_BIN,相当于SQL_LOG_BIN = 0。
D 指定包含需要归档表的数据库。
h 指定连接的主机。
u 指定连接的用户。
p 指定连接需要的密码。
P 指定连接的端口。
S 指定连接的SOCKET文件。
t 指定需要归档的表。
i 指定需要使用的索引。
选项限制
- 至少指定一个选项–dest,–file或–purge。
- –ignore和–replace是互斥的。
- –txn-size和–commit-each是互斥的。
- –low-priority-insert和–delayed-insert是互斥的。
- –share-lock和–for-update是互斥的。
- –analyze和–optimize是互斥的。
- –no-ascend和–no-delete是互斥的。
常用的参数:
--limit10000 每次取1000行数据用pt-archive处理,Number of rows to fetch and archive per statement.
--txn-size 1000 设置1000行为一个事务提交一次,Number of rows pertransaction.
--where'id<3000' 设置操作条件
--progress5000 每处理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库
--sleep120 每次归档了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用以回收表空间。
使用示例:
pt-archiverBug不会迁移max(id)那条数据的解决方法:
参考:http://www.ttlsa.com/mysql/pt-archiver-bug-cannot-migration-max-id-record/
删除老数据(单独的删数据操作不用指定字符集):
/usr/bin/pt-archiver \
--source h=localhost,u=root,p=1234,P=3306,D=test,t=t \
--no-check-charset --where 'a<=376' --limit 10000 --txn-size 1000 --purge
复制数据到其他mysql实例,且不删除source的数据(指定字符集):
/usr/bin/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上的旧数据(指定字符集):
/usr/bin/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
### 官方文档说明:The normal method isto delete every row by its primary key. Bulk deletes might be a lot faster.They also
mightnot be faster if you have a complex WHERE clause.
复制数据到其他mysql实例,不删除source数据,但是使用批量插入dest上新的数据(指定字符集):
/usr/bin/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秒。
导出数据到文件:
/usr/bin/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
导出数据到文件并删除数据库的相关行:
/usr/bin/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
创建脚本
/pydata/software/percona-toolkit-3.0.2/pt-archiver.sh
/pydata/software/percona-toolkit-3.0.2/pt-archiver.def
[root@py-test percona-toolkit-3.0.2]# more pt-archiver.sh
#--------------------------------------------------------------------------------
#脚本名称: pt-archiver.sh
#脚本参数: pt-archiver.def文件
#脚本功能: 归档大表到历史表
#编 写 人: Alaia
#编写日期: 2021年1月8日
#更 新 人:
#更新日期:
#--------------------------------------------------------------------------------
EXEC_DATE=`date +%Y-%m-%d`
TABLE_DATE=`date +%Y%m%d`
#EXEC_DATE="2021-01-08"
#TABLE_DATE="20210108"
FILE_DIR=`dirname $0`
FILE_NAME=`basename $0 .sh`
USER="root"
PASSWORD="dbpasswd"
sed '/^#.*\|^$/d' ${FILE_DIR}/${FILE_NAME}.def >${FILE_DIR}/${FILE_NAME}.tmp
for i in `cat ${FILE_DIR}/${FILE_NAME}.tmp`
do
DEF_DATA_TMP="`echo ${i} |sed s/\ //g`" #去空格,得到一行数据
#得到数据文件和源表名值
SOURCE_DB=`echo ${i} | cut -d "," -f1 | tr "[A-Z]" "[a-z]"`
TABLE_NAME=`echo ${i} | cut -d "," -f2 | tr "[A-Z]" "[a-z]"`
FLITER_FIELD=`echo ${i} | cut -d "," -f3 | tr "[A-Z]" "[a-z]"`
DEST_DB=`echo ${i} | cut -d "," -f4 | tr "[A-Z]" "[a-z]"`
HISTABLE_NAME="${TABLE_NAME}_his${TABLE_DATE}"
WHERE_SQL=`echo "'exec_time<\""${EXEC_DATE}" 00:00:00\"'"`
mysql -u${USER} -p${PASSWORD} -e "create table if not exists ${DEST_DB}.${HISTABLE_NAME} like ${SOURCE_DB}.${TABLE_NAME};"
if [ $? -ne 0 ] ;then
echo "ERROR:create table ${HISTABLE_NAME} error!" >${FILE_DIR}/${FILE_NAME}.log
exit 1
fi
echo " pt-archiver --source h=127.0.0.1,P=3306,u=${USER},p=${PASSWORD},D=${SOURCE_DB},t=${TABLE_NAME} --dest h=127.0.0.1,P=3306,u=${USER},p=${PASSWORD},D=${DEST_DB},t=${HISTABLE_NAME} --no-check-charset --where
'${FLITER_FIELD}<\""${EXEC_DATE}" 00:00:00\"' --progress 5000 --limit=1000 --txn-size=1000 --statistics">pt-archiver-${TABLE_NAME}.sh
sh pt-archiver-${TABLE_NAME}.sh >pt-archiver-${TABLE_NAME}.log
done
exit 0
[root@py-test percona-toolkit-3.0.2]# more pt-archiver.def
##源数据库名,源表名,筛选字段,目标库名
db_source,py_out_export,export_time,db_hist
db_source,py_out_export_result,exec_time,db_hist
##创建每周三定时执行
[root@py-test percona-toolkit-3.0.2]# crontab -e
20 2 * * 3 /pydata/software/percona-toolkit-3.0.2/pt-archiver.sh >/pydata/software/percona-toolkit-3.0.2/pt-archiver.log