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

相关文章

来自专栏转载gongluck的CSDN博客

_CrtSetDbgFlag

_CrtSetDbgFlag 若要了解有关 Visual Studio 2017 RC 的最新文档,请参阅 Visual Studio 2017 RC 文档。...

3039
来自专栏程序你好

使用Java Streams(流)查询数据库

在本文中,您将了解如何编写纯Java应用程序,这些应用程序能够使用来自现有数据库的数据,而无需编写一行SQL(或类似的语言,如HQL),也无需花费大量时间将所有...

532
来自专栏c#开发者

Oracle 开放源代码项目

Oracle 开放源代码项目 这是无数个可扩展、使用以及构建于 Oracle 技术的开放源代码项目中的一个简短的示例。如果您有自己喜欢的开放源代码项目未在此处列...

4808
来自专栏抠抠空间

MySQL 之 视图、触发器、存储过程、函数、事物与数据库锁

浏览目录: 1.视图 2.触发器 3.存储过程 4.函数 5.事物 6.数据库锁 7.数据库备份 1.视图 视图:是一个虚拟表,其内容由查询定义。同真实的表...

3287
来自专栏开源优测

[快学Python3]PyMySQL库

概述 本文主要讲解如何使用pymysql库进行MySQL的管理操作。 主要讲解如何使用pymysql实现增删改查动作,并附上对应的示例。 安装pymysql p...

3439
来自专栏搜云库

Mycat 读写分离 数据库分库分表 中间件 安装部署,及简单使用

MyCat是一个开源的分布式数据库系统,是一个实现了MySQL协议的服务器,前端用户可以把它看作是一个数据库代理,用MySQL客户端工具和命令行访问,而其后端可...

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

关于db link权限分配的苦旅(二)(r7笔记第45天)

在几天前说过关于db link分配权限的问题,当时也折磨了我好一会儿 关于db link权限分配的苦旅(一) (r7笔记第42天) ? 大体的情况还是在11...

3204
来自专栏IT派

如何用Python 编写知乎爬虫?So easy!

在爬虫系统中,待抓取 URL 队列是很重要的一部分。待抓取 URL 队列中的 URL 以什么样的顺序排列也是一个很重要的问题,因为这涉及到先抓取那个页面,后抓取...

1030
来自专栏分布式系统和大数据处理

写入数据到Hive表(命令行)

搭建好Hadoop和Hive的运行环境之后,首先考虑到的,就是如何将数据写入到HIVE中。这篇文章将简单、快速地介绍如何通过命令行的方式,使用insert......

2393
来自专栏Java3y

Oracle总结【视图、索引、事务、用户权限、批量操作】

前言 在Oracle总结的第一篇中,我们已经总结了一些常用的SQL相关的知识点了…那么本篇主要总结关于Oralce视图、序列、事务的一些内容… 在数据库中,我们...

3244

扫码关注云+社区