摘要: 最近由于福建开机广告生产环境的广告日志备份表主键 (int 类型) 达到上限(21 亿多), 不能再写入数据, 需要重新清空下该表并将主键重置, 但由于表里有 8 亿多记录的数据量, 使用重置命令及 DDL 命令执行地非常慢, 所以采取删除物理表结构文件的方式来进行快速清空表表数据!
前言
1, 本文介绍是在 MySQL 5.5.29 版本进行的操作, 其他的版本的没有试过, 有兴趣的可以自己尝试去试下!
2, 本文介绍的是删除 frm 和 idb 文件, 同时不破坏原表结构的清空数据的方式!
一, 数据背景及系统介绍
为更好说明问题, 首先介绍下我们系统的数据流转的过程
step1: 日志入库. API 向机顶盒 / EPG 提供广告接口, 采集广告请求日志, 当 STB 访问 EPG,EPG 调用广告请求接口获取广告策略, 并写入到报表数据库的[广告请求数据原始表 t_ad_req_log] 中;
step2: 存储过程备份日志表数据. 由于每天产生的广告请求数据量有 2000 多万, 对于 etl 汇总时抽取有压力, 所以通过存储过程将 7 天以前的数据备份到[广告请求数据备份表 t_ad_req_log_back] 中,[广告请求数据原始表 t_ad_req_log] 只保留 7 天内的数据, 约 8000 万~ 1 亿 2 千万记录左右;
step3:etl 抽取汇总. 使用 etl 每小时抽取[广告请求数据原始表 t_ad_req_log] 的上一时段的数据, 抽取, 分析, 汇总写入报表数据库的[广告 / 广告位按时段汇总表] 里面;
step4: 定时删除备份日志表数据. 每隔一段时间检查下数据 etl 汇总后的数据是否有问题, 确认无误数据没问题后才将[广告请求数据备份表 t_ad_req_log_back] 清空, 因为该表占用空间实在太大了, 不清空隔一段时间就会收到磁盘空间报警短信!
本次就是有近 2 个月没有清空数据, 发现就有近 9 亿条记录(为什么这里不用 select count(*) 语句来查询? 是因为执行这样一条语句 10 来分钟都没出结果, 才用的 explain 来查看下表中总数据记录)
占用磁盘空间也是高得吓人, 120G
当然不仅仅是因为占用磁盘过高, 更重要的原因是, 该表的主键值达到 int 类型的上限值 2147483646 了(21 亿多), 这使得最新的备份数据不能继续写入到该备份表了
所以, 确认汇总表没有数据缺失后, 急需清空该备份表的数据, 并重置下主键
二, 为什么不用 DDL
通过上面确认[广告请求数据备份表 t_ad_req_log_back] 表数据可删后, 当务之急就是要尽快清空数据, 无疑最先想到的就是使用使用 ALTER 重置主键, 执行命令:
ALTER TABLE t_ad_req_log AUTO_INCREMENT= 1;
开个小会, 结果 36 分钟过去没有出现结果, 尴尬了, 执行线程查询命令:
show processlist;
发现一直在 "copy to tmp table", 无奈, 停下来试试执行 drop 命令:
DROP TABLE t_ad_req_log;
喝杯咖啡, 执行了 40 分钟还没删掉, 快急出翔了.
什么叫无能为力? 什么叫难以启齿的柔弱? 大概就是 MySQL DDL 遇到 9 亿级表结构的时候了吧!
于是不得已另选它法 -- 删除表文件 ibd 和 frm 方式!
三, 3 分钟删除
当然, 找到该表的文件, 一键 rm -rf 删除还是贼爽的, 轻轻松松不用 3 分钟.
不过因为不知道这么删会不会有什么关联影响, 于是就先到测试服务器做了个测试, 还是遇到了些问题, 记录下步骤后, 到现网删除 9 亿级记录数据清空数据也是用了不到 3 分钟? 具体操作如下:
3.1 表结构备份
先在测试数据库创建一个与现网[广告请求数据备份表 t_ad_req_log_back] 一样的表, 然后将备份表的表文件 t_ad_req_log_back.frm 和 t_ad_req_log_back.ibd 拷贝到现网 / home 目录下, 这一步的操作主要是为了后面解决建表时出现的 "Table `t_ad_req_log_back` already exists" 问题
3.2 删掉现网表文件
切换到 mysql 的 data 目录下, 执行删除命令:
rm -rf t_ad_req_log_back.*
再一看, 磁盘空间并没有释放, 还是 276G
于是使用 lsof 命令查看文件信息:
lsof -n|grep deleted
发现删除程序还存活
继续使用 kill -9 27713 命令该进程, 磁盘空间得到释放
3.3 重新建表
由于表文件已经被删掉, 该表也就不存在, 使用 show 命令查看也确实没有发现这个表了
于是想继续建表, 发现报错, 报错信息一直是 "Table `t_ad_req_log_back` already exists"
Google 了一下, 才找到原因, 原来是:
由于直接删除了表的物理文件 但 mysql 的信息库 information_schema 或 mysql 库对该表的信息还存在, 也就是说 InnoDB 格式的表索引都保存在 ibdata1 这个文件中, 虽然物理文件被删除, 但是 ibdata1 中的索引没有删除, 所以数据库认为该表已经存在, 导致创建失败, 也就说直接 rm -rf 表文件破坏了表结构.
难道这个表名就无法再用了吗?
当然可以, j 记得吗? 我们最先做了个表结构备份, 现在它派上用场了, 将 / home 目录下的两个表文件 t_ad_req_log_back.frm 和 t_ad_req_log_back.ibd 拷贝到数据库的 data 的目录下, 发现表存在了
执行 desc 命令发现又报错 "Table `t_ad_req_log_back` already exists", 这是怎回事呢?
再一看用户权限不对,
改变用户权限
- chmod 660 t_ad_req_log_back.frm
- chown -R mysql:mysql t_ad_req_log_back.frm
然后再 drop 表, 删除之后会发现该数据库的 data 目录下的物理文件夹中还存在 t_ad_req_log_back.ibd 文件, 删除该文件, 然后再次建表就 ok 了
至此, 9 亿级表数据被清空了, 同时表结构也没有被破坏!
来源: https://www.cnblogs.com/zishengY/p/8781747.html