How to recover deleted tablespace?

Sometimes, MySQL tablespace file(s) might be deleted by mistake, e.g. delete the shared tablespace (ibdata1) or an individual tablespace (table_name.ibd).

In this post I will show you how to recover those files (on Linux OS) having only one condition, MySQL service should still be running. If MySQL service stopped after deleting that file, this method will not work, so it is extremely important to act as quick as possible to avoid data loss.

The following is a simple table creation (innodb_file_per_table is enabled) and the records count inside that table:

SQL> SHOW CREATE TABLE t\G
*************************** 1. row ***************************
       Table: t
Create Table: CREATE TABLE `t` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=23 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

SQL> SELECT COUNT(*) FROM t;
+----------+
| COUNT(*) |
+----------+
|       22 |
+----------+
1 row in set (0.02 sec)

Now, lets delete the individual tablespace for that table:

shell> rm -rf /var/lib/mysql/test/t.ibd

At this time, we can still select and modify that table!!

SQL> INSERT INTO t VALUES (NULL);
Query OK, 1 row affected (0.00 sec)

SQL> SELECT COUNT(*) FROM t;
+----------+
| COUNT(*) |
+----------+
|       23 |
+----------+
1 row in set (0.00 sec)

To be more accurate, rm does not actually delete the file, rather it removes the directory entry pointing to the file's inode. The inode - and in consequence the file - will be removed only if this is the last reference, but as long as the MySQL server process has the file opened, there is another reference which is the open file handle (that's why the "mysqld" server process must still be running).

To list the opened files we can use the Linux command lsof (we filter the output to get only the deleted tablespace information):

shell> lsof |grep t.ibd
COMMAND     PID       USER   FD      TYPE             DEVICE     SIZE/OFF       NODE NAME
mysqld    11401      mysql   25uW     REG                7,0        98304    1010691 /var/lib/mysql/test/t.ibd (deleted)

The file has a tag of (deleted) which means that the directory entry pointing to the file's inode was deleted but there is another reference(s) to that inode, otherwise it won't be listed by the above command.
Now the question is, how can we get the on-disk path to that opened file if the directory entry was removed?

We can use the "/proc" interface to the running processes and their file handles by the following formula:

• File path = /proc/PID/fd/FD-number

According to the above formula and using the output of the "lsof" command, the file we just deleted is located here:

shell> ll /proc/11401/fd/25 
lrwx------ 1 mysql mysql 64 Oct 28 16:14 /proc/11401/fd/25 -> /var/lib/mysql/test/t.ibd (deleted)

To make sure that this is the on-disk path for the file we deleted, check the reference: it still points to the original path.

How can we recover that file??

• First, we should make sure that no other queries are modifying that table:

  SQL> LOCK TABLE t READ; 
  Query OK, 0 rows affected (0.00 sec)

• Then we copy the data blocks (/proc/11401/fd/25) to a new file (we use the original file path) and change the ownership to the MySQL system user (mysql):

  shell> cp /proc/11401/fd/25 /var/lib/mysql/test/t.ibd
  shell> chown mysql:mysql /var/lib/mysql/test/t.ibd

• Restart MySQL service (if we didn't restart MySQL service directly after recovering the tablespace all changes on that table will still be redirected to the open file handle not the just recovered copy and thus will be lost after the restart):

  shell> service mysql restart
  ..... SUCCESS!
  ..... SUCCESS!

• The tablespace is now recovered and we can modify the table normally:

  SQL> SELECT COUNT(*) FROM t;
  +----------+
  | COUNT(*) |
  +----------+
  |       23 |
  +----------+
  1 row in set (0.00 sec)
  
  SQL> INSERT INTO t VALUES (NULL);
  Query OK, 1 row affected (0.00 sec)
  
  SQL> INSERT INTO t VALUES (NULL);
  Query OK, 1 row affected (0.00 sec)
  
  SQL> select COUNT(*) from t;
  +----------+
  | COUNT(*) |
  +----------+
  |       25 |
  +----------+
  1 row in set (0.00 sec)

Notes:

• We can use the same procedures above to recover the shared tablespace (ibdata1) but we should lock all tables before the recovery process by using the SQL command "FLUSH TABLES WITH READ LOCK;"
• If the MySQL server had to deal with more files (.frm, .ibd, .MYI, .MYD, ...) than its "open_file_limit", it might happen that it will close this handle. In that case, the file will also cease to exist, even though the process is still running and that's why it is extremely important to act as quick as possible.
• The same procedure can be used to recover MyISAM files (.MYI and .MYD) but note that the file handle will be released if "FLUSH TABLES;" SQL command was executed.
• The same procedure can be used as well to recover binary logs, general logs, ... etc but note that the file handle will be released if "FLUSH LOGS;" SQL command was executed.
• This method can be used to recover any deleted file on Linux not only MySQL files but if the file's inode has other references (lsof).

Real life case:

One of our customers was enabling the general query log on his production system, he noticed that the file was continuously growing and to not to consume the available free disk space on his server he removed that file by "rm /path/to/general_query.log". However, the available free space was still being consumed while he couldn't see the general log file. The customer thought that the file was deleted but in fact, the file handle was still opened by MySQL server process.
To get the problem solved we only issued the SQL command "FLUSH LOGS;" - which the customer should have issued after removing the file - then the file handle was closed, thus the inode was deleted and the consumed disk space freed back to the system.