[大数据]MySQL 数据归档清理方法

前言:

????????本文主要介绍MySQL数据归档清理方法，用到的清理归档工具为MySQL自带的原生工具mysqldump以及percona公司的pt-archive。

??????? pt-archive是由PERCONA公司所开发的轻量级数据归档清理工具，可以进行用户数据清理，本地数据归档，远程数据归档工作，并且对数据库的性能最小化的影响，批量的对数据进行操作，避免大事务以及大范围锁的发生。

pt-archive工具安装使用:

• 从官方下载链接: https://www.percona.com/downloads/percona-toolkit/LATEST/? 下载percona-toolkit工具

• 安装相关的依赖包

  yum -y install perl-DBI perl-DBD-MySQL perl-Time-HiRes perl-IO-Socket-SSL

• 解压安装包percona-toolkit-3.3.1_x86_64.tar.gz就可以使用

pt-archive使用限制:

• 注意:进行数据清理归档操作的表，一定要有主键！

• 采用bulk的方式是使用load data infile方式，数据库参数local_infile要设置为on，并且会在/tmp下面生成*-archiver的临时文件，通常文件不会很大，根据每次批量处理的大小生成

• 不建议一次执行同时包含数据归档以及数据清理，如果出现归档数据的失败并且数据刚好又被清理，可能导致数据丢失，建议先单独进行数据归档，确认归档没问题之后，再进行数据的清理purge、

• 采用bulk的方式，插入主键，唯一键冲突会被忽略，程序不会提示任何报错，也不会终止，所以需要确保插入数据主键不冲突以及对插入后的数据进行检查。

数据归档方法:

归档到库上的其他表

如果小表(rows<=100W),使用以下方式归档表:

#注意表一定要有主键,并且如果过滤的数据不超过全表的10%，过滤条件要有索引
create table user.table_bak like user.table;
insert into user.table_bak select * from user.table where id<=1000000;

如果表(rows>100W),使用pt-archive方式归档表:

--source 源端地址，--dest 目标地址，对于归档到同一个库上的，IP地址用户一样
h:ip地址,P端口，D数据库名称,t表名,u用户名p 密码 
i 指定索引, 如果过滤的数据不超过全表的10%，可以手动指定索引，因为默认会强制使用primary key
--where设置过滤条件例如(id<=100000),对于关联其他表的写法--where 
'exists (select 1 from test10000 where test10000.id=test.id) '
--bulk-insert 批量insert 
--limit每1000行处理一次
--commit-each 每1000行提交一次
--primary-key-only 只能使用主键进行扫描
--progress 每处理1000行打印一次进度信息
--statistics 显示运行统计信息
--charset 字符集
./pt-archiver --source h=192.168.2.240,P=3306,D=test,t=test,u=test,p=test --dest h=192.168.2.240,P=3306,D=test,t=test20220628,u=test,p=test --where 'id<=1000000'  --bulk-insert  --no-safe-auto-increment --check-charset --check-columns --no-delete --limit=1000 --commit-each --progress 1000 --statistics  --charset=utf8

如果表(rows>100W),使用存储过程方式插入表，批量每1000行提交一次，表一定要有主键，因为批量处理是根据主键进行排序提交的

---设置新的结束符
DELIMITER $$
---创建批量insert存储过程
CREATE PROCEDURE batchinsert()
BEGIN
  SET @id_start := 0;
  SET @id_end :=0;
  set @id_min :=0;
  set @id_max :=0;
  SELECT @id_min :=min(id),@id_max := MAX(id)
      FROM test.test
      WHERE createtime<'2022-07-11 17:30:00'
    ;
  set @id_start :=@id_min;
  loop_label: LOOP
    SELECT @id_end :=max(id)
    FROM (
      SELECT id
      FROM test.test
      WHERE test.id >=@id_start and createtime<'2022-07-11 17:30:00'
      order BY id
      LIMIT 1000
    ) AS tmp;
    IF @id_end IS NULL THEN
     LEAVE loop_label;
    END IF;
  IF (@id_end = @id_max and @id_start=@id_end ) THEN
      insert test.test_bak 
      select * from test WHERE id=@id_end
      AND createtime<'2022-07-11 17:30:00';
      SELECT @id_end;
      LEAVE loop_label;
  END IF;
    insert test.test_bak 
    select * from test WHERE id >=@id_start
    AND id <@id_end
  AND createtime<'2022-07-11 17:30:00';
    SELECT @id_end;
  SET @id_start := @id_end;
  END LOOP;
END$$
---还原设置结束符
DELIMITER ;
---执行批量处理过程
CALL batchinsert();
---删除批量过程
DROP PROCEDURE `batchinsert`;

归档到远程库上的其他表:

使用pt-archive方式归档表，批量每1000行提交一次:
--source 源端地址，--dest 目标地址 
h ip地址,P端口，D数据库名称,t表名,u用户名p 密码 
--where过滤条件必须要设置，全表归档则设置1=1
--bulk-insert 批量insert 
--limit每1000行处理一次
--commit-each 每1000行提交一次
--progress 每处理1000行打印一次进度信息
--statistics 显示运行统计信息
--charset 字符集
--no-safe-auto-increment不保留自增列最大的那一行数据
./pt-archiver --source h=192.168.2.240,P=3306,D=test,t=test,u=test,p=test --dest h=192.168.2.241,P=3306,D=test,t=test20220628,u=test,p=test --where '1=1'  --bulk-insert  --no-safe-auto-increment --check-charset --check-columns --no-delete --limit=1000 --commit-each  --no-safe-auto-increment  --progress 1000 --statistics  --charset=utf8

归档到本地文件上：

使用pt-archive方式归档表,导出来的格式是表里面的行数据:

--source 源端地址 
h ip地址,P端口，D数据库名称,t表名,u用户名p 密码
--where设置过滤条件例如(id<=100000),对于关联其他表的写法--where 
'exists (select 1 from test10000 where test10000.id=test.id) '
--bulk-insert 批量insert 
--limit每1000行处理一次
--commit-each 每1000行提交一次
--primary-key-only 只能使用主键进行扫描
--progress 每处理1000行打印一次进度信息
--statistics 显示运行统计信息
--charset 字符集
--file本地文件
./pt-archiver --source h=192.168.2.240,P=3306,D=test,t=test,u=test,p=test --where ' exists (select 1 from test10000 where test10000.id=test.id) '  --no-safe-auto-increment --check-charset --check-columns --no-delete --limit=1000 --commit-each --progress 1000 --statistics  --charset=utf8 --file '/tmp/test_%Y%m%d-%H%i%s-%D.%t'

使用mysqldump导出到本地，格式是insert语句格式

mysqldump  -uroot -p123456 -P3306 -S /opt/mysql/data/mysql.sock  --set-gtid-purged=OFF --master-data=2 --single-transaction --add-drop-table=false  --triggers --routines --events test test --where=" id > 3800000"  >/tmp/test_data.sql

使用select into outfile导出，导出来的格式是表里面的行数据

select * into outfile '/tmp/test_data.txt' fields terminated by ',' from test.test where id<=1000000 ;

数据清理方法:

如果表(操作rows<=100W),使用delete方式清理表:

#注意表一定要有主键,并且如果过滤的数据不超过全表的10%，过滤条件要有索引
delete from user.table_bak where id<=1000000;

如果表(操作rows>100W),使用pt-archive方式清理表，批量每1000行提交一次

--source 源端地址 
h:ip地址,P端口，D数据库名称,t表名,u用户名p 密码 
i 指定索引, 如果过滤的数据不超过全表的10%，可以手动指定索引，因为默认会强制使用primary key
--where设置过滤条件例如(id<=100000),对于关联其他表的写法--where 
'exists (select 1 from test10000 where test10000.id=test.id) '
--bulk-insert 批量insert 
--limit每1000行处理一次
--commit-each 每1000行提交一次
--primary-key-only 只能使用主键进行扫描
--progress 每处理1000行打印一次进度信息
--statistics 显示运行统计信息
--charset 字符集
./pt-archiver --source h=192.168.2.240,P=3306,D=test,t=test,u=test,p=test,i=ind1_test  --where "createtime>date('2022-06-29')" --bulk-delete --limit 1000 --commit-each --purge  --no-safe-auto-increment --check-charset --check-columns --progress 1000 --statistics  

如果表(操作rows>100W),使用存储过程方式删除表，批量每1000行提交一次，表一定要有主键，因为批量处理是根据主键进行排序提交的

---设置新的结束符
DELIMITER $$
---创建批量delete存储过程
CREATE PROCEDURE batchdelete()
BEGIN
  SET @id_start := 0;
  SET @id_end :=0;
  set @id_min :=0;
  set @id_max :=0;
  SELECT @id_min :=min(id),@id_max := MAX(id)
      FROM test.test
      WHERE createtime<'2022-07-11 16:32:00'
    ;
  set @id_start :=@id_min;
  loop_label: LOOP
    SELECT @id_end :=max(id)
    FROM (
      SELECT id
      FROM test.test
      WHERE test.id >=@id_start and createtime<'2022-07-11 16:32:00'
      order BY id
      LIMIT 1000
    ) AS tmp;
  IF @id_end IS NULL THEN
     LEAVE loop_label;
    END IF;
  IF (@id_end = @id_max and @id_start=@id_end ) THEN
    delete from test.test
      WHERE id = @id_end
    AND createtime<'2022-07-11 16:32:00';
      SELECT @id_end;
      LEAVE loop_label;
  END IF;
    delete from test.test
    WHERE id >=@id_start
    AND id < @id_end
  AND createtime<'2022-07-11 16:32:00';
SELECT @id_end;
SET @id_start := @id_end;
  END LOOP;
END$$
---还原设置结束符
DELIMITER ;
---执行批量处理过程
CALL batchdelete();
---删除批量过程
DROP PROCEDURE `batchdelete`;