So You Just Deleted Your Production Database - What Now
It doesn’t matter how it happened, it doesn’t matter why, blame can (and probably will) be placed later. What matters now is that your production MySQL database was just deleted from the filesystem while the MySQL daemon was running. The good news is that the server, somehow, is still running fine, and the system is still up. The bad news is that the directory where MySQL stores the database is now empty.
Take a deep breath, you are running Linux, here is what to do:
1. Do not shut down MySQL. If you do you lose all your data.
2. Schedule downtime for your application: You won’t get out of this unscathed.
3. Find a new home for your server, preferably a new server or a new virtual machine. If you run VMware or another virtualization setup, you can even clone your existing database server.
4. During downtime, move the IP address from the broken MySQL server over to the new server. Give your old server a temporary IP address.
5. Use mysqldump to get the data out of your broken, mysteriously missing database into a text file you can move.
6. Copy the text file over to the new server, import it into the new database, and restart your application. You are good to go.
Now that the crises has been averted, lets talk about why that worked, and the narrow opportunity you had to save the database.
The database was running, accepting queries and responding properly. There was no cache, which means the data was being read and written to the disk, but the file that it was being written to was gone. How could that be? To answer that question we have to dig deep into the gritty depths of how the Linux filesystem works.
When the file was deleted from the disk, what was actually deleted was last remaining pointer to the file’s inode. An inode is a data structure that holds metadata about a file, like the file’s permissions, and, importantly, the location of the actual data on the disk. Data on a disk does not look like a filesystem with folders and directories, it just looks like a stream of bytes. What normally happens when the last hard link to an inode is removed is that the inode is removed and the location of the data on the disk is lost, free for the operating system to overwrite it. In the case of the database, the last hard link was removed, but the MySQL daemon was still accessing the data, which means that a file descriptor was kept in the /proc filesystem. So the data was there, still available, but as soon as MySQL was stopped, or if the server was rebooted, the inode would be gone, taking with it the last known location of the data on disk.
It may be tempting to try to recover the database out of the /proc filesystem using lsof, as described in this excellent Linux.com article. Don’t bother. The minute you attempt to copy the database file without first shutting down MySQL it will become corrupt.
As a last ditch effort, a little knowledge of the Linux filesystem can save the day, but not without taking a few hits along the way. So, remember, always test your backups, always know what the command you are about to type actually does, not just what you think it does.
Of course, this is all hypothetical. It never happened to me. No, never.