资讯专栏INFORMATION COLUMN

上云采购季！| 2核2G4M爆款云服务器低至59元/年，更有多台、长期优惠，快来选购！

立即前往
首页/文章专栏/MySQL数据归档

MySQL数据归档

IT那活儿 / 2615人阅读
MySQL数据归档

点击上方“IT那活儿”公众号，关注后了解更多内容，不管IT什么活儿，干就完了！！！


  

随着MySQL数据表越来越大，需要对历史数据按时间点做一次归档，重建归档表，再清除旧表中已经归档的数据，提高数据库的性能。

下面介绍一款比较好的工具：pt-archiver





方案描述



适用范围：

  • MySQL
  • 平台：Linux

常用参数：

  • limit 1000       每次取1000行数据用pt-archive处理；
    SELECT /*!40001 SQL_NO_CACHE */ FORCE INDEX(`PRIMARY`) FROM WHERE ORDER BY `id` LIMIT 1000
  • txn-size  2000   2000行是一个事务；
  • where ‘id<1000‘   设置操作条件；
  • progress 5000    每处理5000行输出一次处理信息；
  • statistics        输出执行过程及最后的操作统计。（只要不加上--quiet，默认情况下pt-archive都会输出执行过程的）
  • charset=UTF8    指定字符集为UTF8；
  • bulk-delete      批量删除source上的旧数据；
    DELETE FROM  WHERE  LIMIT 1000
  • bulk-insert      批量插入数据到dest主机；
    LOAD DATA LOCAL INFILE  INTO TABLE
  • replace           将insert into 语句改成replace写入到dest库；
  • sleep 1           每次归档了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 <2021-01-01"  --purge 
--limit=2000  --no-check-charset 
--txn-size=2000  
--bulk-delete   --statistics 
--primary-key-only  --progress 2000
归档不清理数据：
归档表结构需和原表结构保持一致（为提升归档速度，可暂时不创建索引，待数据归档完成后进行索引的创建。）将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=xxxxxxxxx
x --dest 
h=192.168.56.52,D=test,t=user_basic_info_bak,u=root,p=xxxxx
xxxxxx  --where 1=1  --limit=1000 --no-check-charset 
--txn-size=1000 --bulk-insert --no-delete --statistics -
-primary-key-only --progress 1000 --local
归档并清理数据：
归档表结构需和原表结构保持一致（为提升归档速度，可暂时不创建索引，待数据归档完成后进行索引的创建。）将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 --dest 
h=192.168.56.52,D=test,t=user_basic_info_bak,u=root,p=xxxxx
xxxxxx --where 1=1  --limit=1000 --no-check-charset --
sleep 1 --txn-size=1000 --bulk-delete --statistics --
primary-key-only --progress 1000 --local

报错：

DBD::mysql::st execute failed: Loading local data is 
disabled; this must be enabled on both the client and server 
sides [for Statement "LOAD DATA LOCAL INFILE ? INTO TABLE 
`test`.`user_basic_info`(`id`)" with ParamValues: 
0=/tmp/WJZ3qiQ3ISpt-archiver] at /bin/pt-archiver line 6876.
处理方式：
root@localhost [test]>show variables like %INFILE%;
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| local_infile | OFF |
+---------------+-------+
root@localhost [test]>set global local_infile=1;
root@localhost [test]>show variables like %INFILE%;
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| local_infile | ON |
+---------------+-------+

总结：

  • pt-archiver是一个十分高效的表数据归档工具，归档数据可以分批进行事务处理，减少性能消耗；
  • 对于跨实例或者跨服务器的表数据归档，pt-archiver可以运行在目标端服务器，因为生成的临时文件是在工具执行所在的服务器；
  • 对于大表的过期数据的批量删除也可以通过pt-archiver指定选项--purge进行处理。

本文作者：赵栋辉（上海新炬王翦团队）

本文来源：“IT那活儿”公众号

云服务器 GPU云服务器 数据归档 邮件归档数据 数据库归档 数据归档解决方案

文章版权归作者所有，未经允许请勿转载,若此文章存在违规行为，您可以联系管理员删除。

转载请注明本文地址：https://www.ucloud.cn/yun/129351.html

相关文章

  • MySQL - 扩展性 2 扩展策略：氪金氪脑任君选

    摘要：另一个与之相反的方法是将任务分配的多台机器上，这通常被称为水平扩展或者向外扩展。向上扩展向上扩展也叫垂直扩展意味着购买更多性能强悍的机器。考虑清楚归档系统中的解除归档策略。总结向上氪金，向外氪脑。 如果将应用的所有数据简单地放在一台 MySQL 服务器实例上，就不用谈什么扩展性了。但是业务能稳定持续的增长，那么应用肯定会碰到性能瓶颈。 对于很多类型的应用而言，购买更高性能的机器能解决一...

    NikoManiac 评论0 收藏0

  • MySQL - 扩展性 2 扩展策略：氪金氪脑任君选

    摘要：另一个与之相反的方法是将任务分配的多台机器上，这通常被称为水平扩展或者向外扩展。向上扩展向上扩展也叫垂直扩展意味着购买更多性能强悍的机器。考虑清楚归档系统中的解除归档策略。总结向上氪金，向外氪脑。 如果将应用的所有数据简单地放在一台 MySQL 服务器实例上，就不用谈什么扩展性了。但是业务能稳定持续的增长，那么应用肯定会碰到性能瓶颈。 对于很多类型的应用而言，购买更高性能的机器能解决一...

    Chao 评论0 收藏0

发表评论

登陆后可评论

0条评论

IT那活儿

男|高级讲师
我要私信

TA的文章

阅读更多
最新活动
阅读需要支付1元查看
<