Suppose, you have a black day, unintentionaly confirmed DROP DATABASE command and your hardly filled tables has gone, forever! Is there any chance to recover dropped database and get your valuable tables back again? This tutorial tries answer this question.
It’s 10:23 p.m. and I’m still working on a project for my customer. It’s just an update for some components and some manual database maintenace (except that automatic one). He must have it ready tomorow, so I’m under pressure and tired a bit. Well, I’m just going to remove one temporary table and work is done. Using phpMyAdmin, I select the database, table and click Drop tab to delete the table. Confirmation window pops up, clicking on Yes and work is done. WORK IS DONE! But WHAT exactly is DONE? No, I don’t wanna see it! It may not be true! Instead of the table, whole database has gone! Ooooh, shit! I didn’t notice that I confirmed DROP DATABASE instead of DROP TABLE! My tiredness was out in a second and blood pressure fired up to get me ready to solve this fatal mistake.
So, database has gone and what am I going to do now? What are my chances to get all database stuff back? I have automatic backups for all databases set up on midnight, but there were a lot of entries made on this database since last midnight backup. How will I get back the data since midnight till now? Due to the pretty traffic, couple of thousands entries are missing! Oh my God, somebody is going to kill me!
Well, calm down man and try to think how to get back all lost stuff! As I mentioned earlier, thanks God, I have midnight backups scheduled. But this was not enough to fix all damages. While I was googling a solution, I found something what I remember from the past. The MySQL binary logs! Yes, I remember the time when I was setting up the MySQL installation and deciding on binary logging turn on. Finally I’ve made the right decision then and added the log-bin option into the MySQL config file (my.ini). These two short words with dash between them saved my life now!
Now, I’ve felt I’m close to the light at the end of that dark tunnel. I checked official MySQL manual for Binary Log, then mysqlbinlog – utility for processing binary log files and Using Backups for Recovery. After that I was convinced that my problem is going to be solved soon!
As described in these three MySQL manual pages, first I recovered the data from last midnight backup, so I had the data till last midnight definitely back.
[root@host /]# mysql < database-20090713.sql
Then, I located proper MySQL bin log (it was the last one in this case, but you can find it by the date of creation and last update). Since the location depends on your MySQL setup, use any search tool (grep for *NIX like systems or regular Search on Windows, with binlog keyword) to locate it. Using following command I’ve got the data from specific datetime (last backup) on the screen:
[root@host /]# mysqlbinlog --start-datetime="2009-07-14 00:00:00" host-binlog.000008
The output was thousands of entries and MySQL statements, but I recognized some of those are proper ones. Now, the last step was necesary:
[root@host /]# mysqlbinlog --start-datetime="2009-07-14 00:00:00" host-binlog.000008 | mysql
Yes, the stated above command looks exactly like previous one except that the output of mysqlbinlog utility is redirected to the mysql engine, which take over statements of the output and processes them. The result is all entries from last midnight till the catastrophe time recovered! Mission is completed, lost data back and life looks much nicer again!
Notice: If you can’t see the redirection to mysql in syntax highlighter box above – scrollbar missing – click on the view source icon (mouse over the code above) to view full source.
In this tutorial we showed ourselves the situation when inadvertently whole MySQL database was dropped by using DROP DATABASE command. This caused the loss of all data stored in the database forever, at the first sight. But at the second sight (or third one), we can see that MySQL Binary Log files in conjuction with regular scheduled backups can recover our lost data by using backup reload and mysqlbinlog utility.
So, never give up, always backup databases and never be lazy to set up MySQL engine properly. You NEVER know whe you will need it! AND ALWAYS READ ALERTS BEFORE YOU CONFIRM SOME OPERATION!
Notice: This MySQL DROP DATABASE Recovery procedure works on both *NIX like and Windows systems. I realise that not all details are covered, but readers are required to have sufficient knowledge of MySQL and system administration. Due to some risky steps, this is not recommended to be performed in real operation by beginners.
Edit (July 20, 2011): Check the alternative method of MySQL Drop Database Recovery submitted by Mark, below in comments!