MySQL: Restore a dropped database

May 29, 2010 kevin Administration, General, 1

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

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

create database mysite;

mysql -umysite -pmysite mysite < mysite.sql

mysql -umysite -pmysite mysite < /tmp/replay.sql

drop database, mysql, mysql drop database, mysql dropped database, restore drop database, restore dropped database

Daemon Tools error: Unable to validate the installation Dailymotion Videowall WordPress Widget Plugin