MySQL数据归档

随着MySQL数据表越来越大，需要对历史数据按时间点做一次归档，重建归档表，再清除旧表中已经归档的数据，提高数据库的性能。下面介绍一款比较好的工具：pt-archiver。

MySQL数据归档

方案描述

适用范围：MySQL平台：Linux常用参数：limit1000每次取1000行数据用pt-archive处理；SELECT/*!40001SQL_NO_CACHE*/FORCEINDEX(`PRIMARY`)FROMWHEREORDERBY`id`LIMIT1000txn-size 2000 2000行是一个事务；where‘id 设置操作条件；progress5000 每处理5000行输出一次处理信息；statistics 输出执行过程及最后的操作统计。（只要不加上–quiet，默认情况下pt-archive都会输出执行过程的）charset=UTF8 指定字符集为UTF8；bulk-delete 批量删除source上的旧数据；DELETEFROM WHERE LIMIT1000bulk-insert 批量插入数据到dest主机；LOADDATALOCALINFILE INTOTABLEreplace 将insertinto语句改成replace写入到dest库；sleep1 每次归档了limit个行记录后的休眠1秒；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）。前提：所使用用户拥有足够的权限（以下使用root），并且用户允许连接到数据库；pt-archiver操作的表必须有主键。

清理数据

直接清理原表中的历史数据。

pt-archiver–source h=192.168.56.51,D=test,t=user_basic_info,u=root,p=xxxxxxxxxx –where”VALID_PERIOD –purge–limit=2000 –no-check-charset–txn-size=2000 –bulk-delete –statistics–primary-key-only –progress2000

归档不清理数据

归档表结构需和原表结构保持一致（为提升归档速度，可暂时不创建索引，待数据归档完成后进行索引的创建。）将192.168.56.51上test.user_basic_info归档到192.168.56.52上的user_basic_info_bak。

pt-archiver–source h=192.168.56.51,D=test,t=user_basic_info,u=root,p=xxxxxxxxxx –desth=192.168.56.52,D=test,t=user_basic_info_bak,u=root,p=xxxxxxxxxxx –where 1=1 –limit=1000–no-check-charset–txn-size=1000–bulk-insert–no-delete–statistics–primary-key-only–progress1000–local

归档并清理数据

pt-archiver–source h=192.168.56.51,D=test,t=user_basic_info,u=root,p=xxxxxxxxxx –desth=192.168.56.52,D=test,t=user_basic_info_bak,u=root,p=xxxxxxxxxxx –where 1=1 –limit=1000–no-check-charset–sleep1–txn-size=1000–bulk-delete–statistics–primary-key-only–progress1000–local

报错：DBD::mysql::stexecute failed:Loadinglocal data is disabled;thismustbeenabledonboththeclientandserversides[forStatement”LOAD DATA LOCAL INFILE ?INTO TABLE `test`.`user_basic_info`(`id`)”withParamValues:0=/tmp/WJZ3qiQ3ISpt-archiver]at/bin/pt-archiverline6876.处理方式：root@localhost[test]>showvariableslike%INFILE%;+—————+——-+|Variable_name| Value|+—————+——-+| local_infile|OFF|+—————+——-+root@localhost[test]>setgloballocal_infile=1;root@localhost[test]>showvariableslike%INFILE%;+—————+——-+|Variable_name| Value|+—————+——-+总结：pt-archiver是一个十分高效的表数据归档工具，归档数据可以分批进行事务处理，减少性能消耗；对于跨实例或者跨服务器的表数据归档，pt-archiver可以运行在目标端服务器，因为生成的临时文件是在工具执行所在的服务器；对于大表的过期数据的批量删除也可以通过pt-archiver指定选项–purge进行处理。

本文作者：赵栋辉（上海新炬王翦团队）本文来源：“IT那活儿”公众号

原创文章，作者：小编小本本，如若转载，请注明出处：https://www.benjiyun.com/yunzhujiyunwei/vps-yunwei/6423.html