HOW GEEK!

geek notes for advice seekers

MySQL: Restore a dropped database

NOTE BEFORE TRYING ANYTHING DO A BACKUP OF ALL DATABASES. 1 MISTAKE CAN BE A BIG PROBLEM, IMAGINE 2 MISTAKES AT THE SAME TIME!!

You need bin logs for this tutorial, if you didn’t have them enabled, then you have to look somewhere else..

Today i accidentally “dropped” the database of a site i host thanks to my lazyness and the use and the brilliant and intuitive software called phpmyadmin, which i usually NEVER use.

What i did is a simple drop database mysite;

I had a backup from may 21 at 07:40 (check carefully the filetime of your last backup).

Then i went to /var/log/mysql/

Did ls -la to check the time of the logs:

-rw-rw—-  1 mysql adm   66005373 mai 21 06:25 mysql-bin.001036
-rw-rw—-  1 mysql adm   38683086 mai 22 06:25 mysql-bin.001037
-rw-rw—-  1 mysql adm   48038277 mai 23 06:25 mysql-bin.001038
-rw-rw—-  1 mysql adm   40780613 mai 24 06:25 mysql-bin.001039
-rw-rw—-  1 mysql adm   42856471 mai 25 06:25 mysql-bin.001040
-rw-rw—-  1 mysql adm   48990369 mai 26 06:25 mysql-bin.001041
-rw-rw—-  1 mysql adm   54580611 mai 27 06:25 mysql-bin.001042
-rw-rw—-  1 mysql adm   49505245 mai 28 06:25 mysql-bin.001043
-rw-rw—-  1 mysql adm   57696248 mai 29 06:25 mysql-bin.001044
As i knew i deleted the database on may 29 near 18:10, i ran this command:
mysqlbinlog –start-datetime=”2010-05-21 07:40:00″ –stop-datetime=”2010-05-29 18:05:00″ -d mysite mysql-bin.0010* > /tmp/replay.sql
Then i recreated my dropped database:
create database mysite;
Put the old backup:
mysql -umysite -pmysite mysite < mysite.sql
And applied the replay sql right after:
mysql -umysite -pmysite mysite < /tmp/replay.sql
And everything came back magically! Hope it helps!

, , , , ,

One Response to “MySQL: Restore a dropped database”

Leave a Reply

Your email address will not be published.