How to recover accidentally deleted MySQL database files

Recently I stumbled over a posting on the German MySQL Forum from a user that accidentally removed all table files from a MySQL Server's data directory with a misbehaving shell script. He was surprised to find out that the server happily continued to serve requests and his web site was still fully operational, even though /var/lib/mysql/<database> was completely emtpy! The reason for this in a nutshell: the rm command only removed the reference to the table files from the database directory, the files itself were not removed from the file system yet as the mysqld process still had the files opened. So as long as a process keeps a file open, the kernel will not release the disk space occupied by the file and it will remain intact, albeit no longer visible.

Of course, the user was now desperate to recover the deleted tables files and was asking for help. Fortunately the recovery in this case is pretty simple. You should first shut down your application to avoid further activitiy on the affected database. Important: you must not shut down the MySQL Server, as this would close the last open reference to the table files! Now you can simply use mysqldump --opt <database> > database.sql to perform an SQL dump of the deleted tables. As the MySQL server still can access the open table files, the dump will contain the entire content and can then be used to restore the database again. Now you should restart the MySQL server so it closes the still open file descriptors of the deleted tables files. Alternatively, you could use DROP TABLE <table> or DROP DATABASE <database> to properly remove the references, in case you don't want to shut down the entire server. Now you can restore your missing tables from the SQL dump as usual and can restart your application!

Note that this trick only works on table files that were removed on the file system level, not after you used DROP TABLE/DATABASE, so it's not a magic undo function for these commands - only restoring from a recent backup (e.g. performed with mylvmbackup, hint, hint) will help in this case. In addition to that the MySQL server must have had opened the tables before. A freshly started MySQL server has not opened any table files apart from the ones in the mysql system database.

By the way, there is a related article "Bring back deleted files with lsof" on Linux.com that covers the subject of recovering deleted (but still open) files on a more general level and also provides some more background information about the Linux internals. Worth a read!