8

How can I restore a mysql database that was dropped using a "drop database" command? I have access to binary logs which should make this type of rollback possible.

Oli
  • 235,628
  • 64
  • 220
  • 299
azl
  • 93
  • 1
  • 1
  • 6

5 Answers5

9

Documentation Sucks. It alludes to DROP DATABASE being recoverable, but only in odd conditions i'm not familiar with http://dev.mysql.com/doc/refman/5.0/en/binary-log.html

According to Docs, binlogs are just a sequence of commands executed based on a given reference point. So that when you did "DROP DATABASE", instead of going "Oh, hes droppping the database, we should back up now just in case" it merely wrote a "DROP DATABASE" to the last binlog. Recovery is not as simple as playing the tape backwards.

What you need to do is recover the database from a last-known-good, and apply the binlogs that happened between that recover point and the DROP command.

http://dev.mysql.com/doc/refman/5.0/en/recovery-from-backups.html

How one determines which binlogs to use tho, unclear.

There is nothing better than having full file system backups. And you should at least have these to fall back to.

Kent Fredric
  • 56,416
  • 14
  • 107
  • 150
5

If you don't have a backup of the database, you're out of luck. Droping a database is permanent.

willurd
  • 11,745
  • 5
  • 28
  • 23
5

Assuming you had a backup, the binary log holds the stuff that has happened since that backup. Using the mysqlbinlog utility you could do something like:

mysqlbinlog the_log_file > update.sql

Though I think you might have to edit that file to remove anything you didn't want to execute again (like the drop database statement).

Good luck!

itsmatt
  • 31,265
  • 10
  • 100
  • 164
0

No backup no party.

So I'll answer:

To never be in your situation again install the ultra simple and ultra powerful automysqlbackup (if you're on linux):

sudo apt-get install automysqlbackup

configure it:

sudo nano /etc/default/automysqlbackup

Then upload its content automatically to dropbox, ubuntu one or similar.

Only then live happily :)

Pitto
  • 8,229
  • 3
  • 42
  • 51
0

Just to complement Kent Fredric's answer, you CAN rollback the drop database command if you are using binary logging since the database's creation.

melloc
  • 833
  • 7
  • 13