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!