作者：韶梦
背景
在 MySQL 5.6 和 5.7 版本中，DDL（数据定义语言）操作可能会因各种原因失败，导致数据库中留下残留文件。这些残留文件不仅会占用磁盘空间，还可能引发数据不一致或其他潜在问题。这类残留文件的清理方法在网上有很多教程，但有些不正确，有些不全面。本文主要参考社区文档《InnoDB 数据字典故障排除》，结合完整的演示过程，为您提供一个全面、清晰的清理指南，帮助您有效识别并安全清理这些残留文件，确保数据库的稳定运行。
非原子 DDL 残留文件
DDL 操作主要用于对数据库的结构进行修改，例如创建、修改或删除表、索引等。InnoDB 存储引擎支持 online DDL，执行 DDL 操作过程并不阻塞其他 DML 操作，这其中需要临时文件来确保数据一致性。在未支持原子的数据字典的 MySQL 5.6/5.7 版本，如果这些操作未成功完成，可能会导致数据字典与实际文件不一致，从而产生无法简单删除的残留文件。
残留文件根据起因和访问报错可以分为以下 4 类：
1.1. 缺失 .frm（Orphan Table）
CREATE TABLE 操作失败，错误日志提示表已在 InnoDB 数据字典中存在，但 .frm 文件缺失。
InnoDB: Error: table test/parent already exists in InnoDB internal InnoDB: data dictionary. Have you deleted the .frm file InnoDB: and not used DROP TABLE? Have you used DROP DATABASE InnoDB: for InnoDB tables in MySQL version <= 3.23.43? InnoDB: See the Restrictions section of the InnoDB manual. InnoDB: You can drop the orphaned table inside InnoDB by InnoDB: creating an InnoDB table with the same name in another InnoDB: database and moving the .frm file to the current database. InnoDB: Then MySQL thinks the table exists, and DROP TABLE will InnoDB: succeed.
此时，相应的.ibd 文件可能存在也可能不存在，但不影响表的状态，后面的清理方法也是相同的。
1.2. 缺失 .ibd（ Missing Datafile）
启动时发现 .ibd 文件缺失，但 .frm 文件还在
[ERROR] InnoDB: Operating system error number 2 in a file operation. [ERROR] InnoDB: The error means the system cannot find the path specified. [ERROR] InnoDB: Cannot open datafile for read-only: './test/t1.ibd' OS error: 71 [Warning] InnoDB: Ignoring tablespace `test/t1` because it could not be opened.
1.3. 残留中间表（Orphan Intermediate Tables）
在 ALTER TABLE 操作（ALGORITHM=INPLACE）失败后，可能会生成一个以 #sql-ib 命名的中间表。
可以通过查询 INFORMATION_SCHEMA.INNODB_SYS_TABLES 表，查找以 #sql 开头的表名。
SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES WHERE NAME LIKE '%#sql%';
+----------+----------------------------+------+--------+-------+-------------+------------+---------------+ | TABLE_ID | NAME | FLAG | N_COLS | SPACE | FILE_FORMAT | ROW_FORMAT | ZIP_PAGE_SIZE | +----------+----------------------------+------+--------+-------+-------------+------------+---------------+ | 26 | test/#sql-ib22-1322016439 | 1 | 5 | 12 | Antelope | Compact | 0 | +----------+----------------------------+------+--------+-------+-------------+------------+---------------+ 1 rows in set (0.01 sec)
1.4. 残留临时表（Orphan Temporary Tables）
在 ALTER TABLE 操作（ALGORITHM=COPY）失败后，可能会生成一个以 #sql- 命名的临时表。同样可以通过查询 INFORMATION_SCHEMA.INNODB_SYS_TABLES 表，查找以 #sql 开头的表名。
清理残留文件
给出完整清理步骤前，让我们从目标倒推清理步骤。清理有两方面目标，一是防止实例数据字典中有残留表定义，导致无法创建同名新表，二是避免残留文件占用磁盘空间。后者直接将残留文件删除即可，前者则需要在实例中通过 DROP TABLE 的方式清理表定义。基于这两个目标，我们的清理步骤和关键如下：
步骤 |
关键 |
|
1 |
确定要删除的表的名字。 |
针对 DDL 产生的中间表、临时表，需要通过查询系统表确认其表名。 |
2 |
保证磁盘中有相应目录下以表名命名的 frm 文件。找一张已存在的表，或者创建一张新表，拷贝其 frm 到相应目录下，改为问题表的名字 |
不需要表结构相同，但需要是独立表空间文件的 .frm，不能是分区表的 .frm。 |
3 |
使用 DROP TABLE 语句删除该表。 |
针对 DDL 产生的中间表、临时表，使用特殊前缀#mysql50#的表名进行删除。 |
4 |
在磁盘中删除残留文件。 |
4 类残留文件的清理都可以按照以上步骤进行，具体来说：
- 缺失 .frm（Orphan Table）：表名就是报错里的表名
- 缺失 .ibd（ Missing Datafile）：最为简单，因为 .frm 还存在，直接跳到第 3 步即可
- 残留中间表（Orphan Intermediate Tables）和残留临时表（Orphan Temporary Tables）：
- 第 1 步：通过 SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES WHERE NAME LIKE '%#sql%';确认其表名，例如 test/#sql-ib22-1322016439；
- 第 2 步：推荐通过残留文件名中 ib 后面的表 id 确认其原始表，直接使用原始表的 .frm 文件；
- 第 3 步：删除表需要加特殊前缀#mysql50#，例如 DROP TABLE `test`.`#mysql50##sql-ib22-1322016439`;
分区表残留文件
实际情况可能更加复杂，比如分区表 DDL 失败残留的中间表，但只要遵循清理原则即可。接下来在 MySQL 5.6 上进行实验演示。5.7 版本情况基本一致，唯一区别是 5.7 没有 .par 文件。
准备数据
1. 创建原始表
CREATE TABLE t1 ( id INT NOT NULL AUTO_INCREMENT, word VARCHAR(64) DEFAULT NULL, PRIMARY KEY (id) ) ENGINE=InnoDB PARTITION by hash(`id`) PARTITIONS 4; DELIMITER // CREATE PROCEDURE insert_data(IN i INT) BEGIN WHILE i>0 DO INSERT INTO t1(word) VALUES(MD5(RAND())); SET i=i-1; END WHILE; END// DELIMITER ; CALL insert_data(100);
文件状态：可以看到一个 4 分区的表，拥有 1 个记录表结构的 .frm、（5.6 版本特有）1 个记录分区信息的 .par 和 4 个保存分区数据的 .ibd。
╰─$ ll total 400K -rw-r----- 1 shimengchu.smc users 8.4K Feb 21 16:05 t1.frm -rw-r----- 1 shimengchu.smc users 32 Feb 21 16:05 t1.par -rw-r----- 1 shimengchu.smc users 96K Feb 21 16:05 t1#P#p0.ibd -rw-r----- 1 shimengchu.smc users 96K Feb 21 16:05 t1#P#p1.ibd -rw-r----- 1 shimengchu.smc users 96K Feb 21 16:05 t1#P#p2.ibd -rw-r----- 1 shimengchu.smc users 96K Feb 21 16:05 t1#P#p3.ibd
2. 注入代码致其 DDL 过程中实例 crash，位置在创建中间表之后但未进行表结构变更之前。
static bool mysql_inplace_alter_table(...) { ... table->file->ha_prepare_inplace_alter_table(altered_table, ha_alter_info); DBUG_EXECUTE_IF("ib_ddl_crash_with_orphan", DBUG_SUICIDE();); ... table->file->ha_inplace_alter_table(altered_table, ha_alter_info); ... }
set session debug="+d,ib_ddl_crash_with_orphan"; alter table t1 add column b int;
文件状态：可以看到产生的中间表的相关文件和原始表相同，但表名以 #sql 开头，且 .frm 的名字和 .ibd 不同。
╰─$ ll total 800K -rw-r----- 1 shimengchu.smc users 8.5K Feb 21 16:08 #sql-db77_1.frm -rw-r----- 1 shimengchu.smc users 32 Feb 21 16:08 #sql-db77_1.par -rw-r----- 1 shimengchu.smc users 96K Feb 21 16:08 #sql-ib38-3436953065.ibd -rw-r----- 1 shimengchu.smc users 96K Feb 21 16:08 #sql-ib39-3436953066.ibd -rw-r----- 1 shimengchu.smc users 96K Feb 21 16:08 #sql-ib40-3436953067.ibd -rw-r----- 1 shimengchu.smc users 96K Feb 21 16:08 #sql-ib41-3436953068.ibd -rw-r----- 1 shimengchu.smc users 8.4K Feb 21 16:05 t1.frm -rw-r----- 1 shimengchu.smc users 32 Feb 21 16:05 t1.par -rw-r----- 1 shimengchu.smc users 96K Feb 21 16:05 t1#P#p0.ibd -rw-r----- 1 shimengchu.smc users 96K Feb 21 16:05 t1#P#p1.ibd -rw-r----- 1 shimengchu.smc users 96K Feb 21 16:05 t1#P#p2.ibd -rw-r----- 1 shimengchu.smc users 96K Feb 21 16:05 t1#P#p3.ibd
清理过程
- 查询系统表确认表名
可以发现，分区表 DDL 残留的中间表与普通表最大的不同就是有多个 .ibd 文件且在数据字典内将这些视为不同的表，故清理时，也需要将每个.ibd 文件视为单独的表进行清理。
MySQL [test]> SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES WHERE NAME LIKE '%#sql%'; +----------+---------------------------+------+--------+-------+-------------+------------+---------------+ | TABLE_ID | NAME | FLAG | N_COLS | SPACE | FILE_FORMAT | ROW_FORMAT | ZIP_PAGE_SIZE | +----------+---------------------------+------+--------+-------+-------------+------------+---------------+ | 42 | test/#sql-ib38-3436953065 | 1 | 6 | 28 | Antelope | Compact | 0 | | 43 | test/#sql-ib39-3436953066 | 1 | 6 | 29 | Antelope | Compact | 0 | | 44 | test/#sql-ib40-3436953067 | 1 | 6 | 30 | Antelope | Compact | 0 | | 45 | test/#sql-ib41-3436953068 | 1 | 6 | 31 | Antelope | Compact | 0 | +----------+---------------------------+------+--------+-------+-------------+------------+---------------+ 4 rows in set (0.01 sec)
- 构建 .frm 文件
不能直接使用分区表的 .frm，需要另外构建一个独立表空间的 .frm。
CREATE TABLE t2 (`a` int NOT NULL) ENGINE=innodb;
cp t2.frm \#sql-ib38-3436953065.frm cp t2.frm \#sql-ib39-3436953066.frm cp t2.frm \#sql-ib40-3436953067.frm cp t2.frm \#sql-ib41-3436953068.frm
- 删除表
DROP TABLE `#mysql50##sql-ib38-3436953065`; DROP TABLE `#mysql50##sql-ib39-3436953066`; DROP TABLE `#mysql50##sql-ib40-3436953067`; DROP TABLE `#mysql50##sql-ib41-3436953068`;
文件状态
╰─$ ll total 524K -rw-r----- 1 shimengchu.smc users 8.5K Feb 21 16:08 #sql-db77_1.frm -rw-r----- 1 shimengchu.smc users 32 Feb 21 16:08 #sql-db77_1.par -rw-r----- 1 shimengchu.smc users 8.4K Feb 21 16:05 t1.frm -rw-r----- 1 shimengchu.smc users 32 Feb 21 16:05 t1.par -rw-r----- 1 shimengchu.smc users 96K Feb 21 16:16 t1#P#p0.ibd -rw-r----- 1 shimengchu.smc users 96K Feb 21 16:16 t1#P#p1.ibd -rw-r----- 1 shimengchu.smc users 96K Feb 21 16:16 t1#P#p2.ibd -rw-r----- 1 shimengchu.smc users 96K Feb 21 16:16 t1#P#p3.ibd -rw-r----- 1 shimengchu.smc users 8.4K Feb 21 16:24 t2.frm -rw-r----- 1 shimengchu.smc users 96K Feb 21 16:24 t2.ibd
- 删除残留文件
分区中间表的 .frm 和 .par 预期内的没有随着 DROP TABLE 删除，需要手动删除。
rm -f \#sql-db77_1.*
总结
通过本文的指南，您可以更安全地处理 MySQL 5.6 和 5.7 版本中 DDL 失败后的残留文件，有效避免数据丢失和数据库不一致的问题。如果有任何疑问，可参考 MySQL 官方文档《InnoDB 数据字典故障排除》获取更多信息。
在8.0中，由于使用了原子的数据字典，就不会出现这种残余文件了，强烈推荐用户升级到 MySQL 8.0 版本。
欢迎访问阿里云云数据库 RDS MySQL 详情页了解更多信息：https://www.aliyun.com/product/rds/mysql
