如何使用 mysql 二进制日志从 drop database 命令恢复？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/88800/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How do I restore from a drop database command using a mysql binary log?
提问by azl
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.
如何恢复使用“drop database”命令删除的 mysql 数据库？我可以访问二进制日志，这应该使这种类型的回滚成为可能。
回答by Kent Fredric
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
文档很烂。它暗示 DROP DATABASE 是可恢复的，但仅在奇怪的情况下我不熟悉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.
根据 Docs，binlog 只是基于给定参考点执行的一系列命令。所以当你执行“DROP DATABASE”时，而不是“哦，他正在删除数据库，我们现在应该备份以防万一”它只是在最后一个二进制日志中写入了一个“DROP DATABASE”。恢复并不像倒放磁带那么简单。
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.
您需要做的是从最后一个已知良好状态恢复数据库，并应用在该恢复点和 DROP 命令之间发生的二进制日志。
http://dev.mysql.com/doc/refman/5.0/en/recovery-from-backups.html
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.
没有什么比拥有完整的文件系统备份更好的了。你至少应该有这些可以回退。
回答by willurd
If you don't have a backup of the database, you're out of luck. Droping a database is permanent.
如果您没有数据库备份，那您就不走运了。删除数据库是永久性的。
回答by itsmatt
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 实用程序，您可以执行以下操作：
mysqlbinlog the_log_file > update.sql
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).
虽然我认为您可能必须编辑该文件以删除您不想再次执行的任何内容（例如 drop database 语句）。
Good luck!
祝你好运！
回答by Pitto
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):
为了不再遇到这种情况，请安装超简单且超强大的 automysqlbackup（如果您使用的是 linux）：
sudo apt-get install automysqlbackup
sudo apt-get install automysqlbackup
configure it:
配置它：
sudo nano /etc/default/automysqlbackup
须藤纳米 /etc/default/automysqlbackup
Then upload its content automatically to dropbox, ubuntu one or similar.
然后将其内容自动上传到 dropbox、ubuntu 或类似的。
Only then live happily :)
只有这样才能快乐地生活:)
回答by melloc
Just to complement Kent Fredric's answer, you CAN rollback the drop databasecommand if you are using binary logging since the database's creation.
只是为了补充 Kent Fredric 的回答，drop database如果您自数据库创建以来使用二进制日志记录，则可以回滚命令。
