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?
有什么办法可以让它回滚吗?
3 个解决方案
#1
10
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.
如果您没有备份和所有日志，那么您就无法恢复这些数据。
#2
0
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
#3
0
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，并且它也是基于行格式的，那么您可以在更新前后获取每一行的值。使用它可以恢复表的状态。
#1
10
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.
如果您没有备份和所有日志，那么您就无法恢复这些数据。
#2
0
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
#3
0
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，并且它也是基于行格式的，那么您可以在更新前后获取每一行的值。使用它可以恢复表的状态。