MySQL 大表的清理

一、事件背景

一个大表里面存储有数据28亿，大表结构是hash 100个分区,占用空间1.8T，里面保留了2年多的数据，数据文件大小持续在增大，目前MySQL数据目录datadir占用磁盘95%，无法通过drop分区回收空间,计划保留最新3个月数据。

二、实施步骤

在凌晨业务低峰期执行

1.新增同结构新表

注意：create table like 语句会丢失 auto 值

create table rb_test_new like rb_test;

2.改表名

alter table rb_test renmae to rb_test_old;
alter table rb_test_new rename to rb_test;

3.导出数据

找一个有足够空间的磁盘目录导出最新3个月数据。注：这个导出时间很长。

SELECT * INTO OUTFILE '/opt/mysql/backup/import_export/rb_test_3.txt'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
FROM rb_test_old where entry_date > '2022-09-16 00:00:00';

4.drop 大表

drop table rb_test_old;

注意：巨大的表drop会hang住数据库，IO会占用大。也可以考虑通过给数据文件创建硬链接来删除。
硬链接删表步骤：

1)创建硬链接

ln rb_test_old#P#P_RB_test_01.ibd  rb_test_old#P#P_RB_test_01.ibd.hdlk
ln rb_test_old#P#P_RB_test_02.ibd  rb_test_old#P#P_RB_test_02.ibd.hdlk
............................................
ln rb_test_old#P#P_RB_test_100.ibd  rb_test_old#P#P_RB_test_100.ibd.hdlk

2)drop 表

drop table rb_test_old;

3)删除真实数据文件

rm rb_test_old#P#P_RB_test_*.ibd.hdlk

5.导入数据

1)打开参数

show variables like 'local_infile';

返回结果是on,否则修改参数命令

set global local_infile=on;

2)加快导入

SET GLOBAL foreign_key_checks=0;
SET GLOBAL unique_checks=0;
SET GLOBAL innodb_flush_log_at_trx_commit=0;

3)清理binlog空间

导入数据会暴增binlog日志，随时监控binlog目录，保证占有率不要超过100%
清理1天前的binlog

purge master logs before date_sub(now(),interval 1 day);

4）导入数据

load data local infile '/opt/mysql/backup/import_export/rb_test_3.txt' ignore into table rb_test fields terminated by ',' enclosed by '"' lines terminated by '\n';commit;

5）恢复参数

SET GLOBAL foreign_key_checks=1;
SET GLOBAL unique_checks=1;
SET GLOBAL innodb_flush_log_at_trx_commit=1;

三、总结

1.预期会存储过亿数据的大表，为了后期的数据清理，建议建表为时间字段为范围分区结构，方便通过drop 过期时间的分区来回收空间。
2.若是通过delete来清理数据的，长期的delete和insert会导致表带有大量碎片，容易造成表损坏，建议一段时间后通过optimize整理表空间，回收碎片空间。
3.大表操作，会影响到在线业务，建议在业务低峰期执行。
4.大表操作，会锁表，所以对于大表的操作，可以考虑通过rename的方式，用新的空表去接收数据，旧表数据再导入补齐数据，适用大表清理，大表添加修改字段，添加索引等ddl操作。
5.大量数据的导入，会导致binlog目录占用暴增，注意监控binlog目录的占用率。
6.drop 巨大的表，会hang住实例，若是担心业务会受到影响，可以考虑通过对数据文件创建硬链接来删除。
7.做好MySQL实例数据目录占用的监控，超过80%就人工干预处理。