连仕彤博客Mysql数据库归档利器之pt-archiver

前言

数据库管理员或者运维人员经常需定期对数据进行归档和清除,我们可以使用percona的pt-archiver工具能完成这一功能,使得数据归档变得方便简单。

归档之前准备

pt-archiver归档前,需要先建立归档表(备份表)且表结构要一样。 pt-archiver操作的表必须有主键。

1.查询表、数据信息

MySQL [pttest1]> show table status like 'demo_table'\G;
*************************** 1. row ***************************
           Name: demo_table
         Engine: InnoDB
        Version: 10
     Row_format: Compact
           Rows: 7964647
 Avg_row_length: 75
    Data_length: 601882624
Max_data_length: 0
   Index_length: 0
      Data_free: 37432066048
 Auto_increment: NULL
    Create_time: 2018-04-27 05:42:56
    Update_time: NULL
     Check_time: NULL
      Collation: utf8_general_ci
       Checksum: NULL
 Create_options: 
        Comment: 
1 row in set (0.03 sec)

2.查看表DDL(表结构)

MySQL [pttest1]> show create table demo_table;
+------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table      | Create Table                                                                                                                                                                                                                                               |
+------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| demo_table | CREATE TABLE `demo_table` (
  `url` varchar(200) NOT NULL COMMENT 'url',
  `src` varchar(50) NOT NULL DEFAULT '',
  `count` bigint(20) NOT NULL DEFAULT '0',
  `day` date NOT NULL,
  PRIMARY KEY (`url`,`day`,`src`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

3.建立归档表

CREATE TABLE `demo_table_bak` (
  `url` varchar(200) NOT NULL COMMENT 'url',
  `src` varchar(50) NOT NULL DEFAULT '',
  `count` bigint(20) NOT NULL DEFAULT '0',
  `day` date NOT NULL,
  PRIMARY KEY (`url`,`day`,`src`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

pt-archiver常用参数

--limit10000       每次取1000行数据用pt-archive处理,Number of rows to fetch and archive per statement.
--txn-size  1000   设置1000行为一个事务提交一次,Number of rows pertransaction.
--where‘id<3000‘   设置操作条件
--progress5000     每处理5000行输出一次处理信息
--statistics       输出执行过程及最后的操作统计。(只要不加上--quiet,默认情况下pt-archive都会输出执行过程的)
--charset=UTF8     指定字符集为UTF8
--bulk-delete      批量删除source上的旧数据(例如每次1000行的批量删除操作)
--bulk-insert      批量插入数据到dest主机 (看dest的general log发现它是通过在dest主机上LOAD DATA LOCAL INFILE插入数据的)
--replace          将insert into 语句改成replace写入到dest库
--sleep120         每次归档了limit个行记录后的休眠120秒(单位为秒)
--file ‘/root/test.txt‘:数据存放的文件,最好指定绝对路径,文件名可以灵活地组合
--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)
 默认情况下,pt-archiver操作结束后,不会对source、dest表执行analyze或optimize操作,因为这种操作费时间,并且需要你提前预估有足够的磁盘空间用于拷贝表。一般建议也是pt-archiver操作结束后,在业务低谷手动执行analyze table用以回收表空间。

归档常用方法

归档(复制)数据到归档表,且不删除源表的数据(指定字符集),删除源数据的话把--no-delete选项换成--purge就可以了。

[root@db-work dbback]# pt-archiver --source h=localhost,u=root,p=qQSVdqzBUlT5TbeI,P=3306,D=pttest1,t=demo_table --dest h=localhost,P=3306,u=root,p=qQSVdqzBUlT5TbeI,D=pttest_bak,t=demo_table_bak  --progress 5000 --where "day < '2017-12-01'"  --statistics --charset=UTF8 --limit=10000 --txn-size 1000 --no-delete
TIME                ELAPSED   COUNT
2018-04-27T07:09:22       0       0
2018-04-27T07:09:23       1    5000
2018-04-27T07:09:24       2   10000
2018-04-27T07:09:25       3   15000
2018-04-27T07:09:26       4   20000
2018-04-27T07:09:27       5   25000
2018-04-27T07:09:28       6   30000
2018-04-27T07:09:29       7   35000
2018-04-27T07:09:30       8   40000
2018-04-27T07:09:31       9   45000
2018-04-27T07:09:32      10   50000
2018-04-27T07:09:33      11   55000
2018-04-27T07:09:34      12   60000
2018-04-27T07:09:35      13   65000
2018-04-27T07:09:36      14   70000
2018-04-27T07:09:37      15   75000
2018-04-27T07:09:38      16   80000
2018-04-27T07:09:39      17   85000
2018-04-27T07:09:40      18   90000
2018-04-27T07:09:41      19   95000
2018-04-27T07:09:42      20  100000
2018-04-27T07:09:44      21  105000
2018-04-27T07:09:45      22  110000
2018-04-27T07:09:46      24  115000
2018-04-27T07:09:47      25  120000
2018-04-27T07:09:48      26  125000
2018-04-27T07:09:49      27  130000
2018-04-27T07:09:50      28  135000
2018-04-27T07:09:51      29  140000
2018-04-27T07:09:53      31  145000
2018-04-27T07:09:54      32  150000
2018-04-27T07:09:55      33  155000
2018-04-27T07:09:56      34  160000
2018-04-27T07:09:57      35  165000
2018-04-27T07:09:58      36  170000
2018-04-27T07:10:00      37  175000
2018-04-27T07:10:01      38  180000
2018-04-27T07:10:02      39  185000
2018-04-27T07:10:02      40  190000
2018-04-27T07:10:03      41  195000
2018-04-27T07:10:04      42  200000
2018-04-27T07:10:05      43  205000
2018-04-27T07:10:06      44  210000
2018-04-27T07:10:08      46  215000
2018-04-27T07:10:09      47  220000
2018-04-27T07:10:10      48  225000
2018-04-27T07:10:11      49  230000
2018-04-27T07:10:12      50  235000
2018-04-27T07:10:13      51  240000
2018-04-27T07:10:14      52  245000
2018-04-27T07:10:15      53  250000
2018-04-27T07:10:17      54  255000
2018-04-27T07:10:18      55  260000
2018-04-27T07:10:19      57  265000
2018-04-27T07:10:20      58  270000
2018-04-27T07:10:21      59  275000
2018-04-27T07:10:22      60  280000
2018-04-27T07:10:23      61  285000
2018-04-27T07:10:24      62  290000
2018-04-27T07:10:25      63  295000
2018-04-27T07:10:26      64  300000
2018-04-27T07:10:27      65  305000
2018-04-27T07:10:28      66  310000
2018-04-27T07:10:29      67  315000
2018-04-27T07:10:30      68  320000
2018-04-27T07:10:31      69  325000
2018-04-27T07:10:32      70  330000
2018-04-27T07:10:34      71  335000
2018-04-27T07:10:35      72  340000
2018-04-27T07:10:36      73  345000
2018-04-27T07:10:37      75  350000
2018-04-27T07:10:38      76  355000
2018-04-27T07:10:39      77  360000
2018-04-27T07:10:40      78  365000
2018-04-27T07:10:41      79  370000
2018-04-27T07:10:42      80  375000
2018-04-27T07:10:43      81  380000
2018-04-27T07:10:45      82  385000
2018-04-27T07:10:46      83  390000
2018-04-27T07:10:47      84  395000
2018-04-27T07:10:48      85  400000
2018-04-27T07:10:49      87  405000
2018-04-27T07:10:50      88  410000
2018-04-27T07:10:51      89  415000
2018-04-27T07:10:52      90  420000
2018-04-27T07:10:53      91  425000
2018-04-27T07:10:54      92  430000
2018-04-27T07:10:55      93  435000
2018-04-27T07:10:56      94  440000
2018-04-27T07:10:57      95  445000
2018-04-27T07:10:58      96  450000
2018-04-27T07:10:59      97  455000
2018-04-27T07:11:00      98  458522
Started at 2018-04-27T07:09:22, ended at 2018-04-27T07:11:00
Source: A=UTF8,D=pttest1,P=3306,h=localhost,p=...,t=demo_table,u=root
Dest:   A=UTF8,D=pttest_bak,P=3306,h=localhost,p=...,t=demo_table_bak,u=root
SELECT 458522
INSERT 458522
DELETE 0
Action         Count       Time        Pct
inserting     458522    60.4543      61.66
select            47     9.7850       9.98
commit           918     1.1202       1.14
other              0    26.6915      27.22

参考资料: 1. 码迷 2. zengxuewen2045的博客 3. For DBA

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏我和未来有约会

Kit 3D 更新

Kit3D is a 3D graphics engine written for Microsoft Silverlight. Kit3D was inita...

2516
来自专栏转载gongluck的CSDN博客

cocos2dx 打灰机

#include "GamePlane.h" #include "PlaneSprite.h" #include "BulletNode.h" #include...

5386
来自专栏大内老A

The .NET of Tomorrow

Ed Charbeneau(http://developer.telerik.com/featured/the-net-of-tomorrow/) Exciti...

31310
来自专栏张善友的专栏

Silverlight + Model-View-ViewModel (MVVM)

     早在2005年,John Gossman写了一篇关于Model-View-ViewModel模式的博文,这种模式被他所在的微软的项目组用来创建Expr...

2948
来自专栏陈仁松博客

ASP.NET Core 'Microsoft.Win32.Registry' 错误修复

今天在发布Asp.net Core应用到Azure的时候出现错误InvalidOperationException: Cannot find compilati...

4828
来自专栏闻道于事

js登录滑动验证,不滑动无法登陆

js的判断这里是根据滑块的位置进行判断,应该是用一个flag判断 <%@ page language="java" contentType="text/html...

6768
来自专栏我和未来有约会

Silverlight第三方控件专题

这里我收集整理了目前网上silverlight第三方控件的专题,若果有所遗漏请告知我一下。 名称 简介 截图 telerik 商 RadC...

3985
来自专栏Golang语言社区

【Golang语言社区】GO1.9 map并发安全测试

var m sync.Map //全局 func maintest() { // 第一个 YongHuomap := make(map[st...

4698
来自专栏Ceph对象存储方案

Luminous版本PG 分布调优

Luminous版本开始新增的balancer模块在PG分布优化方面效果非常明显,操作也非常简便,强烈推荐各位在集群上线之前进行这一操作,能够极大的提升整个集群...

3105
来自专栏跟着阿笨一起玩NET

c#实现打印功能

2702

扫码关注云+社区