之前的文章里已经写了使用TokuDB进行数据压缩,准确的说,但 TokuDB并非是归档,而是对数据进行压缩,使得数据库在保存大量数据时有更好的表现,这节就是描述一个数据库归档方案。
经过调研,发现一个比较好的工具: pt-archiver。
1、原理
通过给定的–where条件,查询出表中需要删除或者归档记录的主键信息,然后通过强制主键索引的方式,小批量(最小可以做到1行)的方式删除或者归档,最大化的减小归档或者删除操作对DB性能的影响。
2、能做什么
1、清理线上过期数据;
2、导出线上数据,到线下数据作处理;
3、清理过期数据,并把数据归档到本地归档表中,或者远端归档服务器。
3、操作示例
1、全表归档,不删除原表数据,非批量插入
pt-archiver \
--source h=10.73.129.187,P=3306,u=backup_user,p='xxx',D=test123,t=c1 \
--dest h=10.73.129.188,P=3306,u=backup_user,p='xxx',D=test123,t=c1 \
--charset=UTF8 --where '1=1' --progress 10000 --limit=10000 --txn-size 10000 --statistics --no-delete
2、全表归档,不删除原表数据,批量插入
pt-archiver \
--source h=10.73.129.187,P=3306,u=backup_user,p='xxx',D=test123,t=c1 \
--dest h=10.73.129.188,P=3306,u=backup_user,p='xxx',D=test123,t=c1 \
--charset=UTF8 --where '1=1' --progress 10000 --limit=10000 --txn-size 10000 --bulk-insert --bulk-delete --statistics --no-delete
3、全表归档,删除原表数据,非批量插入,非批量删除
pt-archiver \
--source h=10.73.129.187,P=3306,u=backup_user,p='xxx',D=test123,t=c1 \
--dest h=10.73.129.188,P=3306,u=backup_user,p='xxx',D=test123,t=c1 \
--charset=UTF8 --where '1=1' --progress 10000 --limit=10000 --txn-size 10000 --statistics --purge
4、全表归档,删除原表数据,批量插入,批量删除
pt-archiver \
--source h=10.73.129.187,P=3306,u=backup_user,p='xxx',,D=test123,t=c1 \
--dest h=10.73.129.188,P=3306,u=backup_user,p='xxx',D=test123,t=c1 \
--charset=UTF8 --where '1=1' --progress 10000 --limit=10000 --txn-size 10000 --bulk-insert --bulk-delete --statistics --purge
4、参数解释
常用参数 | 解释 |
--where ‘id<3000’ | 设置操作条件 |
--limit 10000 | 每次取1000行数据给pt-archive处理 |
--txn-size 1000 | 设置1000行为一个事务提交一次 |
--progress 5000 | 每处理5000行输出一次处理信息 |
--statistics | 结束的时候给出统计信息:开始的时间点,结束的时间点,查询的行数,归档的行数,删除的行数,以及各个阶段消耗的总的时间和比例,便于以此进行优化。只要不加上–quiet,默认情况下pt-archive都会输出执行过程的 |
--charset=UTF8 | 指定字符集为UTF8 |
--no-delete | 表示不删除原来的数据,注意:如果不指定此参数,所有处理完成后,都会清理原表中的数据 |
--bulk-delete | 批量删除source上的旧数据 |
--bulk-insert | 批量插入数据到dest主机 (看dest的general log发现它是通过在dest主机上LOAD DATA LOCAL INFILE插入数据的) |
--purge | 删除source数据库的相关匹配记录 |
--local | 不把optimize或analyze操作写入到binlog里面(防止造成主从延迟巨大) |
--analyze=ds | 操作结束后,优化表空间(d表示dest,s表示source)。默认情况下,pt-archiver操作结束后,不会对source、dest表执行analyze或optimize操作,因为这种操作费时间,并且需要你提前预估有足够的磁盘空间用于拷贝表。一般建议也是pt-archiver操作结束后,在业务低谷手动执行analyze table用以回收表空间 |