[mysql] 归档工具pt-archiver,binlog格式由mixed变成row

pt-archiver官方地址:https://www.percona.com/doc/percona-toolkit/3.0/pt-archiver.html

介绍:归档数据,比如将一年前的数据备份到其他mysql实例+删除一年前的数据,原表只保留最近一年,更好的方案我觉得应该是对大表做分区表。更多详细介绍参考官方或者其他博客。

常用的参数

pt-archiver --source h=$source_host,P=$source_port,u=$source_user,p=$source_pass,D=$source_db,t=$table \
--dest h=$dest_host,P=$dest_port,u=$dest_user,p=$dest_pass,D=$dest_db,t=$dest_table \
--where ''$condition2' <= '$max_id'' \
--progress 10000 --file "/data/%Y-%m-%d-%D.%t" --limit=1000 --commit-each --bulk-insert --bulk-delete --no-bulk-delete-limit --statistics --charset=utf8 \
--max-lag=300 --check-interval=5 \
--check-slave-lag h=$slave1_host,P=$slave1_port,u=$slave1_user,p=$slave1_pass
#--check-slave-lag h=$slave2_host,P=$slave2_port,u=$slave2_user,p=$slave2_pass

--source和--dest 指定源库和目标库的信息

--where 限制条件

--limit 没有--bulk-delete时,只是select获取id时会在sql语句跟上limit,有--bulk-delete时会在delete语句时跟上limit

--progress 归档过程中每完成10000条时报告进展

--statistics 归档完列出统计信息

--charset 字符集,没有指定时,可能数据写到--file之中会变成乱码

--max-lag --check-interval --check-slave-lag都是归档过程中监控从库延迟的情况

 

说说我遇到的问题,一开始的命令如下,引发的问题就是:主库磁盘io异常高,从库延迟跟不上。(我的环境是mysql版本5.7.24-log,主库binlog_format格式为mixed,binlog_row_image默认FULL)
网上搜索了也有类似的问题,因为--bulk-delete和--limit参数,开启gen_log可以看到实际的sql大致为delete from $table where id > 100 and id < 1100 limit 1000;limit语句会导致数据库写binlog时binlog_format更改为ROW,两篇文章的解决方案都是去掉--bulk-delete。

--progress 10000 --file "/data/%Y-%m-%d-%D.%t" --limit=1000 --txn-size=10000 --bulk-insert --bulk-delete --statistics --charset=utf8

但是去掉--bulk-delete之后(同时也不能加--bulk-insert),最终在gen_log和binlong的sql语句变成delete from $table where id =1100,执行1万条delete时commit一次,--limit此时作用只是在select语句阶段一次性获取1000个id,等于执行10次select+1万次delete+1次commit。

但是这种方案显然也不适合,因为我们的大表需要删除将近1000万条记录。

--progress 10000 --file "/data/%Y-%m-%d-%D.%t" --limit=1000 --txn-size=10000 --statistics --charset=utf8

既然binlog为变ROW格式,导致大量的日志写到系统文件上,那么就把binlog禁止掉。查了官网,可通过修改DSN参数,If true, disable binlog with SQL_LOG_BIN,目标库关闭binlog同理。

pt-archiver --source h=$source_host,P=$source_port,u=$source_user,p=$source_pass,D=$source_db,t=$table,b=true

但这是个蛋疼的方案,你删完主库还得删从库。在mixed改变成ROW格式时,就想官方不可能没考虑个这个问题。再次翻看官网,找到了--no-bulk-delete-limit参数;

此时gen_log的语句大致为delete from $table where id > 100 and id < 1010;后面没有跟limit;最终到binlog也就不会变成ROW格式。

--progress 10000 --file "/data/%Y-%m-%d-%D.%t" --limit=1000 --txn-size=10000 --bulk-insert --bulk-delete --no-bulk-delete-limit --statistics --charset=utf8

由于生产环境删除时从库延迟还是挺大,所以将--txn-size替换为--commit-each牺牲性能换取延迟。

还有经常看到网友反馈pt-archiver的问题是,在处理最后一行时,工具并不归档。解决方案大多都是修改源码,重新编译安装pt-archiver。

其实官网也提供了相应的参数来解决--nosafe-auto-increment,听说这是mysql的bug,到8.0版本才解决这个问题。

 

还有一些可能用到的参数,比如:要删除的记录过多,不能在白天删除,业务低峰期又不能一次性删完(我们就这么蛋疼的环境,大表8000万记录,字段多,索引多,白天非高峰期,删除的速度也就100万条/小时)

此时就需要工具运行一定时间后,退出进程。

--run-time其实就可以很好地解决,但是我们的环境再次打脸,因为需要归档的不止是一张表,所以我用shell写了一些for循环,再将当月的记录归档到对应月份的表(目标库上建立了类似table_201901、table_201902),

--run-time只能退出单个pt-archiver进程,并不能退出整个shell,所以就只能cron判断进程是否存在,再kill。

或者往cron指定时间添加个pt-archiver --stop,该命令会在系统上添加文件/tmp/pt-archiver-sentinel。pt-archiver一旦检测有这个文件就会退出,我的shell之中for就会遇到多次退出,还好不影响最终结果。

--why-quit会打印退出的原因

--run-time
type: time

Time to run before exiting.

Optional suffix s=seconds, m=minutes, h=hours, d=days; if no suffix, s is used.

--why-quit
Print reason for exiting unless rows exhausted.

Causes pt-archiver to print a message if it exits for any reason other than running out of rows to archive.
This can be useful if you have a cron job with --run-time specified, for example,
and you want to be sure pt-archiver is finishing before running out of time.

--sentinel
type: string; default: /tmp/pt-archiver-sentinel

Exit if this file exists.

The presence of the file specified by --sentinel will cause pt-archiver to stop archiving and exit.
The default is /tmp/pt-archiver-sentinel. You might find this handy to stop cron jobs gracefully if necessary. See also --stop.

--stop
Stop running instances by creating the sentinel file.

Causes pt-archiver to create the sentinel file specified by --sentinel and exit. 
This should have the effect of stopping all running instances which are watching the same sentinel file.

 

posted @ 2019-04-16 18:11  金枪语  阅读(395)  评论(0编辑  收藏  举报