如何使用 mysql 二进制日志从删除数据库命令恢复？
如何恢复使用“drop database”命令删除的 mysql 数据库？ 我可以访问二进制日志，这应该使这种类型的回滚成为可能。
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.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（5）
为了补充 Kent Fredric 的答案，如果您自数据库创建以来正在使用二进制日志记录，则可以回滚
drop database命令。Just to complement Kent Fredric's answer, you CAN rollback the
drop databasecommand if you are using binary logging since the database's creation.如果您没有数据库的备份，那么您就不走运了。 删除数据库是永久性的。
If you don't have a backup of the database, you're out of luck. Droping a database is permanent.
假设您有一个备份，二进制日志保存自该备份以来发生的事情。 使用 mysqlbinlog 实用程序，您可以执行以下操作：
mysqlbinlog the_log_file > update.sql
虽然我认为您可能必须编辑该文件以删除您不想再次执行的任何内容（例如删除数据库语句）。
祝你好运！
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!
没有备份就没有派对。
所以我会回答：
为了永远不会再出现你的情况，请安装超简单且超强大的automysqlbackup（如果你在linux上）：
sudo apt-get install automysqlbackup
配置它：
sudo nano /etc/default/automysqlbackup
然后上传它内容自动发送到 dropbox、ubuntu 之一或类似的。
只有这样才能幸福地生活:)
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 :)
文档很糟糕。 它暗示 DROP DATABASE 是可恢复的，但只有在我不熟悉的奇怪条件下 http://dev.mysql.com/doc/refman/5.0/en/binary-log.html
根据文档，二进制日志只是基于给定参考点执行的一系列命令。 因此，当您执行“DROP DATABASE”时，而不是“哦，他正在删除数据库，我们现在应该备份以防万一”，它只是将“DROP DATABASE”写入最后一个二进制日志。 恢复并不像倒放磁带那么简单。
您需要做的是从最近一次已知的状态恢复数据库，并应用该恢复点和 DROP 命令之间发生的二进制日志。
http://dev.mysql.com/doc/refman /5.0/en/recovery-from-backups.html
如何确定使用哪些二进制日志，尚不清楚。
没有什么比完整的文件系统备份更好的了。 你至少应该有这些可以依靠。
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.