写在前面
调研一件工具之前,在心里一定要明白,自己要实现的是什么功能,带着自己的问题,去找寻答案才能学到新的知识点!
需求背景
1)线上数据库有一类ip表,用来记录每天用户对网站的访问记录情况,然后将其按照时间段整合到一起,做数据分析工作,整合的时间不同,最后形成的数据表大小亦是不同,小点千万级,大点可能亿级,现有需求将半年前的用户记录删除,如何安全删除?
2)系统做了升级,现要将原来的数据迁移至新服务器上同名数据库中,如何保证安全迁移,而不造成导入时形成大量I/O,导致系统负载过高,用户、研发等部门可以正常使用,特别是在业务高峰期间?
pt-archiver简介
数据库归档和清理工具,可以实现的功能有
1、清理线上过期数据
2、清理过期数据,并把数据归档到本地归档表中,或者远端归档服务器
3、两张表之间的数据不完全相同,希望合并。此时加--ignore --replace选项,可以轻松实现
4、导出线上数据,到线下数据作处理
根据我的需求,调研此工具可以实现所需功能,这也正是本文始点:带着问题找答案,但作者只用到了1、2的功能,至于3、4的功能在这里只做一个记录,待之后有需求后,再做使用
需求实现
1.1直接删除
DELETE FROM `ip_limit_20170701_bak` WHERE id>1000
最简单的方式,但如果数据表很大,可能造成大量的磁盘I/O、系统负载等,如果还存在slave,还会造成严重的从库延迟等问题
1.2按照索引条件批量删除
DELETE FROM `ip_limit_20170701_bak` WHERE id>1000 LIMIT 10000
较安全的删除方式,可以保证磁盘I/O、系统负载、从库延迟等处于可控范围内,但数据量巨大,手动操作较为繁琐
1.3使用工具删除
[root@backup home]# pt-archiver --version [pt工具请自行安装]
pt-archiver 3.0.10
[root@backup ~]# time
pt-archiver --source u=root,p=123456,S=/tmp/mysql.sock,P=3306,D=test,t=ip_limit_20170701_bak --no-check-charset --progress 10000 --where 'id BETWEEN 1000 AND 494731' --limit 10000 --txn-size 1000 --bulk-delete --sleep 5 --purge --statistics
解释:删除test库,ip_limit_20170701_bak表数据,不做字符集检查,删除条件是 id BETWEEN 1000 AND 494731,每次取出10000行进行处理,每处理1000行则进行一次提交,每次处理10000行后显示处理信息,每完成一次处理sleep 5s
2.1mysqldump方式
第一种:将源数据库dump成sql文件,传输到目标服务器,再进行数据导入
第二种:直接在目标服务器dump源数据库的sql,再进行数据导入
但,无论采用哪种方式数据迁移,数据表越大,在数据传输的东西,都会对带宽造成极大的消耗,在查看流量监控图的时候,可以清楚的看到毛尖;再者,在数据导入的时候,势必会造成巨大的I/O消耗,系统负载也会飙升,对数据库也会造成巨大的冲击
2.2使用工具删除
迁移数据时保留源数据:
[root@backup ~]#time pt-archiver --source u=root,p=123456,S=/tmp/mysql.sock,P=3306,D=test,t=ip_limit_20170701 --dest u=root,p=root,h=192.168.32.199,P=3306,D=ceshi,t=ip_limit_20170701 --charset=utf8 --progress 10000 --where 'id>=1' --limit 10000 --txn-size 1000 --sleep 5 --no-delete --bulk-insert --statistics
迁移数据时不保留源数据:
[root@backup ~]#time pt-archiver --source u=root,p=123456,S=/tmp/mysql.sock,P=3306,D=test,t=ip_limit_20170701 --dest u=root,p=root,h=192.168.32.199,P=3306,D=ceshi,t=ip_limit_20170701 --charset=utf8 --progress 10000 --where 'id>=1' --limit 10000 --txn-size 1000 --sleep 5 --bulk-delete --statistics
常用参数
--source=d DSN specifying the table to archive from (required) 目标节点
--where=s WHERE clause to limit which rows to archive (required)
--purge Purge instead of archiving; allows omitting --file and --dest 删除source数据库的相关匹配记录
--progress=i Print progress information every X rows 每处理多少行显示一次信息
--limit=i Number of rows to fetch and archive per statement (default 1) 每次取出多少行处理
--[no]check-charset Ensure connection and table character sets are the same (default yes) 不检查字符集
--txn-size=i Number of rows per transaction (default 1) 每多少行提交一次
--bulk-delete Delete each chunk with a single statement (implies --commit-each) 并行删除
--statistics Collect and print timing statistics 结束后输出统计信息
最后一条数据不删除/不迁移BUG问题
平滑删除、迁移数据时,最后一条数据都不会被删除/迁移,已被证明为pt-archiver BUG,需要修改下pt-archiver代码
[root@backup ~]# vim /usr/bin/pt-archiver
原代码:
6401 $first_sql .= " AND ($col < " . $q->quote_val($val) . ")";
修改后:
6401 $first_sql .= " AND ($col <= " . $q->quote_val($val) . ")";
这些都是作者在工作中遇到的实际工作后引发的一些思考,并做的相关实践,对于其中理解不到位或者根本就是错误之处,望请下方留言,不胜感激!
转载于:https://blog.51cto.com/20131104/2347810