在 MySQL 中提交后有什么办法可以回滚吗？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14016278/
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
Is there any way to rollback after commit in MySQL?
提问by Johnny Lim
I did a big mistake that I updated a table without 'where' clause in MySQL :'(
我犯了一个大错误，我在 MySQL 中更新了一个没有“where”子句的表:'(
It is auto-committed.
它是自动提交的。
Is there any way to rollback from it?
有没有办法从它回滚？
采纳答案by Bohemian
No, there's no query that will "undo" a committed data-modifying query.
不，没有查询可以“撤消”已提交的数据修改查询。
If you have a backup of the database, you can restore the backup and use DBA tools (in MySQL's case, it's mysqlbinlog) to "replay" all data-modifying queries from the logs since the backup back to the database, but skip over the problem query.
如果您有数据库的备份，您可以恢复备份并使用 DBA 工具（在 MySQL 的情况下，它是mysqlbinlog）从备份回数据库后“重放”日志中的所有数据修改查询，但跳过问题查询。
If you don't have a backup and all logs since the that backup, there's nothing you can do to recover the data.
如果您没有备份和自该备份以来的所有日志，您将无法恢复数据。
回答by Kyle
Look up transaction logs. I'll update with more info but the logs may only stay around for a limited time so time is of the essence!
查找事务日志。我会更新更多信息，但日志可能只保留有限的时间，所以时间至关重要！
See: http://dev.mysql.com/doc/refman/5.0/en/point-in-time-recovery.html
请参阅：http: //dev.mysql.com/doc/refman/5.0/en/point-in-time-recovery.html
回答by Srinivash Arumugam
If you have enabled mysql binlog and also it is of ROW based format, then you can get the value for each row before & after the update. Using that you can restore the table's state.
如果您启用了 mysql binlog 并且它是基于 ROW 的格式，那么您可以在更新前后获取每一行的值。使用它可以恢复表的状态。
