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.
Edit (July 20, 2011): Check the alternative method of MySQL Drop Database Recovery submitted by Mark, below in comments!
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!
I just dropped one database instead of a table… 🙁 fortunately it was not an important database! 🙂
Enjoy recovery! 🙂
Hello, I just accidentally dropped my database on my local wamp server. It was lost and I felt all hope was gone, but immediately i searched on the internet to find solution, but i did not get. however, i recovered it and here is how i did it. I went to the folder where phpmyadmin stores the raw data of my databases in my case “C:\wamp\bin\mysql\mysql5.1.32\data” then i noticed that the folder for my dropped database was not there, which means it is gone forever (not really). What i did next was to right-click inside the “data” folder window and i selected “properties” and clicked the “Previous Versions” tab. In that tab there was a list of previous versions with dates, so i selected the one i wanted, in my case i selected the previous version of the “data” folder which was 2 hours earlier. then I clicked “Restore” then it restored back the folder. During the process you might get a pop-up that says restore can not complete cos the folder is used by another process, don’t worry, just go and stop the local server service and click “try again”. I hope this helps someone
Mark, thank you for sharing this cool method with us! 😉 I edited article to point to your alternative method…
Actually what Mark said was just due to the system restore point.
I had not created restore point and now i don’t know how would i recover my database which I dropped accidentally :'(
@Mark
omg mark you are THE MAN! I was feeling this near death experience and thanks to your method I can breath again!
@Teddy you should totally make more emphasis to Mark’s method for windows users,
WHAAAAAAAAAAAAAAAAAAAAAAT!!!!!!
Thanks to you guys this is my greatest and most enjoyable post EVER!
@ Mark: Dude you more than simply ROCK!
@ Teddy: just thank you for having started the post, otherwise I would be just saddened by my lost work!
Could you PLEASE put up a step by step and more detailed solution for the incremental time recovery solution using binary files….I am not a beginner but I still had a hard time getting all of what you were saying…and plus even for a beginner it would be a good way to change level by learning some complicated stuff don’t you think?
Anyway, personally I am EXTREMLY grateful for your post and would really appreciate a more detailed solution like the one here
http://www.ibsteam.net/blog/web-development/how-import-sql-file-within-wamp-environment-using-command-line
Mark, thank you !!!
“Hello, I just accidentally dropped my database on my local wamp server. It was lost and I felt all hope was gone, but immediately i searched on the internet to find solution, but i did not get. however, i recovered it and here is how i did it. I went to the folder where phpmyadmin stores the raw data of my databases in my case “C:\wamp\bin\mysql\mysql5.1.32\data” then i noticed that the folder for my dropped database was not there, which means it is gone forever (not really). What i did next was to right-click inside the “data” folder window and i selected “properties” and clicked the “Previous Versions” tab. In that tab there was a list of previous versions with dates, so i selected the one i wanted, in my case i selected the previous version of the “data” folder which was 2 hours earlier. then I clicked “Restore” then it restored back the folder. During the process you might get a pop-up that says restore can not complete cos the folder is used by another process, don’t worry, just go and stop the local server service and click “try again”. I hope this helps someone”
Hiii
i try to delete table form php MySQL but I accidently clicked “drop” database, how can i recover it back i don’t have any backup. this is very impotent because i am new for database. this is live data on server. please help me.
Please please help me for this act i shell be thankfull, plzzzz help me…
Great article! Saved my life!
mysqlbinlog –database option can pick specific database to restore from. I also used –stop-datetime option to not repeat my mistakes when I pipe the binlog.