前言:
????????本文主要介绍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`;
|