Menu
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.
632
mysql> SHOW BINARY LOGS; To determine the name of the current binary log file, issue the following statement: mysql> SHOW MASTER STATUS; The mysqlbinlog utility converts the events in the binary log files from binary format to text so that they can be viewed or applied.
If a database backup doesn't exist, a dropped table can be recovered from SQL database data and transaction log files. When it comes to recovery from a database in the Simple recovery model, it might fail as the Drop table transaction might have been overwritten. Even then, recovery is possible from the MDF file.
The binary log contains “events” that describe database changes such as table creation operations or changes to table data. It also contains events for statements that potentially could have made changes (for example, a DELETE which matched no rows), unless row-based logging is used.
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.
113
If you don't have a backup of the database, you're out of luck. Droping a database is permanent.
39
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
