MySQL异常中断产生的#sql前缀开头的临时表如何删除？

不长叶子的树

1.  现象

巡检时发现服务器磁盘空间不足，通过查看大文件进行筛选是发现有几个#sql开头的文件,且存在超过100G及10G以上的文件。


2. 原因

如果MySQL在一个 ALTER TABLE操作(ALGORITHM=INPLACE)的中间退出，那么可能会留下一个占用系统空间的临时表。例如，在对一张表（大表）添加索引时中途中断、磁盘不足导致异常或正在添加索引时实例被kill等等情况所致。
注意： 此类表空间文件不能直接rm -f的方式物理删除，因为该信息记录在ibdata的共享表空间里，直接删除后，后续实例重启时会出现错误。

3. 处理方法

3.1 同时存在.frm 和.ibd名称相同的文件如果 #sql-*.ibd 和 #sql-*.frm两个文件都存在数据目录里的话，可以直接drop table。但注意删除时候表名的变化。

/* 直接删除，表名前加#mysql50  */
root@testdb 01:42:57> DROP TABLE `#mysql50##sql-ib87-856498050`;

注意： #mysql50#前缀是MySQL 5.1中引入的文件名安全编码。另外，表名因不符合命名规范，想要执行该脚本需要将表名用反引号括起来。

3.2  创建新表方式删除
因为本例中没有存在.frm 和.ibd名称相同的文件的情况，因此采用创建一张与ibd表空间对应的结构（字段名及索引）一致的表，然后将frm文件拷贝为和ibd一致的文件，再进行删除。

下面处理截图中#sql-ib1516-2335726735.ibd文件，步骤如下：
a）创建一张与#sql-ib1516-2335726735相同的表
root@testdb 08:47:35>create table company20191216 like  company;
Query OK, 0 rows affected (0.05 sec)
root@testdb 08:48:59>exit
Bye
b）拷贝为#sql-ib1516-2335726735.frm的定义文件
[root@db4 testdb]# cp -p  company20191216.frm  \#sql-ib1516-2335726735.frm

c)  删除表
因为上一步拷贝时使用-p的方式，即权限和原文件权限一致，属主及group均为mysql,因此可以直接在数据库里读取删除，如果权限不对，必须先修改文件权限。
root@testdb 08:49:54>drop table `#mysql50##sql-ib1516-2335726735`;
Query OK, 0 rows affected (6.65 sec)
此时，135G的文件就已经删掉了（其实另一个文件#sql-821_2.frm文件也一并删了）


注： 删除这种100G的表不建议直接删除，而是通过创建硬链接的方式处理。

3.3  修改frm文件名与ibd文件名一致

上一步中删除ibd文件时，其中一个frm也自动删除了。为此，尝试通过修改frm文件名和ibd文件名一致的方式处理。但要注意，由于不确定是否结构一致，修改后可能异常，但如果没有暴力处理，通常均可以成功。如下：
a)  修改frm文件名与ibd文件名一致
[root@db4 testdb]# mv \#sql-a846_2.frm  \#sql-ib1570-121877015.frm

b）删除表
root@testdb 01:41:06>DROP TABLE `#mysql50##sql-ib1570-121877015`;
Query OK, 0 rows affected (1.70 sec)


done!
其实还有其他的方式处理，大家可以自行测试。
参考资料：官方文档https://dev.mysql.com/doc/refman ... oting-datadict.html

来自：https://www.cnblogs.com/gjc592

不长叶子的树

如果直接在系统里删除#sql- 文件，会在mysql启动日志里产生错误提示：
2025-01-20T23:49:15.828354Z 0 [ERROR] InnoDB: Operating system error number 2 in a file operation.
2025-01-20T23:49:15.829354Z 0 [ERROR] InnoDB: Cannot open datafile for read-only: '.\2025\#sql-ib199-3235459888.ibd' OS error: 71
2025-01-20T23:49:15.831354Z 0 [ERROR] InnoDB: Could not find a valid tablespace file for `2025/#sql-ib199-3235459888`. Please refer to http://dev.mysql.com/doc/refman/ ... oting-datadict.html for how to resolve the issue.


需要注意的是，文件名前要记得加上 #mysql50# 前缀，否则会出现unknown table 错误提示


.frm 文件名需要修改与 .ibd一致，如果有多个不同文件名，可以多次尝试看匹配的是哪个。