Recovering a Deleted InnoDB .ibd File and Detecting Missing Tablespace in MySQL

Background : An operational mistake removed the .ibd file of a MySQL table (sbtest1) in a production environment without a replica. The article explores how to handle this situation.

Scenario Reproduction : In a single‑instance MySQL‑5.7.28, a test table with 1 million rows is created using sysbench. The table file sbtest1.ibd is then deleted with rm while the server remains running.

[root@localhost ~]# sysbench --mysql-host=10.186.65.84 --mysql-port=8484 --mysql-user=xiao --mysql-password=xiao --mysql-db=test --db-driver=mysql   --mysql_storage_engine=innodb --test=/usr/local/sysbench1.0/share/sysbench/oltp_insert.lua --table_size=1000000 --threads=1 --tables=1 prepare
... (output omitted for brevity) ...

After deletion, the table still returns correct row counts, and mysql-error.log shows no errors.

File Recovery :

Use lsof | grep sbtest1.ibd to locate the open file descriptor (e.g., /proc/1933/fd/78 ).

Copy the deleted file back to its original location: cp /proc/1933/fd/78 /data/mysql/data/8484/test/sbtest1.ibd .

Adjust ownership: chown actiontech-mysql:actiontech-mysql sbtest1.ibd .

Restart MySQL: systemctl restart mysqld_8484.service .

# lsof |grep sbtest1.ibd
# cp /proc/1933/fd/78 /data/mysql/data/8484/test/sbtest1.ibd
# chown actiontech-mysql:actiontech-mysql sbtest1.ibd
# systemctl restart mysqld_8484.service

Post‑restart checks with sysbench confirm that the table is fully functional again.

Detecting a Missing .ibd File :

Various DDL operations produce errors that reveal the missing tablespace:

Adding or dropping a column: ALTER TABLE sbtest1 ADD COLUMN name VARCHAR(15); → ERROR 1030 (HY000): Got error 44 from storage engine .

Adding or dropping an index: No immediate error, but the operation succeeds because the index metadata is stored in the .frm file.

OPTIMIZE TABLE: Returns an error note and logs Cannot rename './test/sbtest1.ibd' ... because the source file does not exist .

ALTER TABLE ... ENGINE=INNODB: Fails with the same error 44.

Modifying a column definition: ALTER TABLE sbtest1 MODIFY COLUMN pad CHAR(70); → ERROR 1025 (HY000): Error on rename of './test/sbtest1' ... (errno: 197) .

All corresponding error messages appear in mysql-error.log , confirming that the .ibd file is missing.

The article concludes that while MySQL can continue serving reads/writes on a table whose .ibd file has been deleted (as long as the file descriptor remains open), any operation that forces InnoDB to recreate or rename the tablespace will immediately expose the problem.