连仕彤博客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 条评论
登录 后参与评论

相关文章

来自专栏维C果糖

史上最简单的 MySQL 教程(二十七)「连接查询(下)」

连接查询:将多张表(大于等于 2 张表)按照某个指定的条件进行数据的拼接,其最终结果记录数可能有变化,但字段数一定会增加。

3186
来自专栏社区的朋友们

MySQL 入门常用命令大全(上)

作为一个 MySQL 的初学者,在短短的几个月中接触了一下,记录了一下工作中用到的 SQL 语句以及未来可能会用到的 MySQL 知识点,作为日后的参考手册。因...

9841
来自专栏杨建荣的学习笔记

MySQL中GTID的几个限制和解决方案

现在我看待一个技术,总是会换一种角度来看,在他能实现什么的基础上,我更喜欢看他不能做什么,为什么不能这么做。 比如MySQL GTID在5.6试水,...

7676
来自专栏csxiaoyao

mysql 命令完全总结

4507
来自专栏王磊的博客

Microsoft SQL Server 2005 提供了一些工具来监控数据库

--WL 09-07-03 /*Microsoft SQL Server 2005 提供了一些工具来监控数据库。方法之一是动态管理视图。动态管理视图 (DMV)...

3515
来自专栏Ken的杂谈

CentOS 7下InfluxDB部署与使用入门

InfluxDB里存储的数据被称为时间序列数据,InfluxDB存储方式跟传统关系型数据库不同的是:传统关系型数据库通过数据库+表+字段组织数据,InfluxD...

7924
来自专栏Java架构沉思录

MySQL存储引擎知多少

MySQL是我们经常使用的数据库处理系统(DBMS),不知小伙伴们有没有注意过其中的“存储引擎”(storage_engine)呢?有时候面试题中也会问道MyS...

962
来自专栏james大数据架构

SQL之收集SQL Server线程等待信息

要知道线程等待时间是制约SQL Server效率的重要原因,这一个随笔中将学习怎样收集SQL Server中的线程等待时间,类型等信息,这些信息是进行数据库优化...

2037
来自专栏奇梦博客

CentOS下mysql数据库常用命令总结 MySQL 参数配置

1441
来自专栏李智的专栏

Mysql学习(基本指令、语句)

  1) 数值   int //int(3)与长度无关,不够3位前面补0,默认看不见     float   2) 字符串 ...

752

扫码关注云+社区

领取腾讯云代金券