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.
The Catastrophe
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.
The Damages
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!
Situation Analysis
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!
Remedial Action
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.
Summary
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.
Hi,
I just wondering about ur article. I am beginner in mysql. My database name was batchdetails. it was accidentally deleted by drop commend and I can seen it in the ib_logfile1 log file.
My question was, how to modify ur code to get the db. If u explain a bit, it will help me and also other users.
Thank you,
All you have to modify in my code is the database name, the binlog name and the time which the database should be reversed from (the time of accident, let’s say). Is this what you were looking for?
But where i paste this codes?
[root@host /]# mysql < database-20090713.sql
…
Can you tell a step by step procedure?
Ty
You must put it on the command line of the shell, Emanuel… as any other command.
Hi Teddy,
So is there a way to do this kind of restore via the PHP MyAdmin console, I am using shared hosting for my website, and I dont think I have a shell in this subscription as in dedicated / vps…
Thanks,
Ariel
Unfortunately Ariel, I do not have any experience with doing this via PHP MyAdmin and I’m afraid it wouldn’t be possible.
But maybe somebody else here could leave a message if has any idea how to do it your way…
Wait, since it replays all entries in the binlog since start-datetime it will also replay the DROP command you’re trying to fix, right? How do you prevent that?
It was 5:00AM. Stayed up all night to finish a project for a client. There was no more room for any kind of issue but then it happened. I accidentally dropped a database instead of a table.
Already being one day late for the project I thought I was screwed. The client would never trust me anymore.
But then I found this post and I really can’t believe it. It actually worked!
Thanks for the post and thanks to WAMP for setting the bin option in my.ini to ON by default.
I know that hopeless feeling after unwanted deletion very well…
so I’m really happy that my post saved your hair!