How to recover deleted tablespace?

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:

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):

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:

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):

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.

Navigation

Trademarks

RSSLinkedInTwitterXingFacebookFlickrGoogle+Follow @fromdual
MySQL, InnoDB and Oracle are registered trademarks of Oracle Corp. MariaDB is a trademark of Monty Program AB.
All other trademarks are property of their respective owners. Other product or company names mentioned may be trademarks or trade names of their respective owner.