Is there any way to rollback after commit in MySQL?

I did a big mistake that I updated a table without 'where' clause in MySQL :'(

It is auto-committed.

Is there any way to rollback from it?

Answers


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.

If you don't have a backup and all logs since the that backup, there's nothing you can do to recover the data.


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


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.


Need Your Help

In SQL, is UPDATE always faster than DELETE+INSERT?

sql sql-insert sql-delete

Say I have a simple table that has the following fields:

Best equivalent for IsInteger in SQL Server

sql sql-server regex tsql

What is the best way to determine whether or not a field's value is an integer in SQL Server (2000/2005/2008)?