Is there any way to rollback after commit in MySQL?

Asked 24/12, 2012 at 2:1 Answered 4/12, 2014 at 6:15

Solved mysql sql-update rollback autocommit

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?

Precis answered 24/12, 2012 at 2:1 Comment(5)

No. You have a backup, I presume? – Ptyalin 24/12, 2012 at 2:4

Unfortunately I don't have any backup :'( – Precis 24/12, 2012 at 2:8

Oops! Well this is a good lesson to learn :) If this was a manual query, which it sounds like, try using tools like the MySQL Workbench which has a safety switch feature, that prevents mistakes like updating without a "where" clause. – Chellman 24/12, 2012 at 2:8

I feel your pain after doing the same thing. I guess you learn by your mistakes. – Pismire 24/11, 2015 at 11:19

As simple as it sounds... writing the WHERE part before the UPDATE part is always a good idea to avoid cases like that. Another trick is to first write a SELECT to see which rows is about to be updated, and then change the SELECT to an UPDATE after your confirmed that the query is going to update the correct rows. – Songstress 22/6, 2020 at 3:0

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.

Inspirit answered 24/12, 2012 at 3:1 Comment(3)

Can you suggest any DBA tool which can do it? – Precis 24/12, 2012 at 3:10

I got the same answer, 'No.' from my company's DBA. But I doubt your second statement is possible without backup and binary log. – Precis 24/12, 2012 at 6:50

@JohnnyLim a little late in responding I know, but the tool to use for mysql is mysqlbinlog (answer updated) – Inspirit 10/6, 2015 at 18:8

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

Tesstessa answered 24/12, 2012 at 2:5 Comment(3)

What are the names of transaction logs? ib_logfile0, ib_logfile1? – Precis 24/12, 2012 at 3:9

Unfortunately I don't use binary logging, either :'( And recovery from binary log needs base backup which the binary log doesn't have, right? – Precis 24/12, 2012 at 4:41

Yes, that's why I said it's like surgery. You need a complete backup and all logs since then. It's quite involved and takes a long time. You only do it if mission critical data was accidentally deleted. That's why regular (at least weekly) complete backups should be taken. And the DBA should practice this kind of scenario so it can be done quickly and confidently if needed. – Inspirit 24/12, 2012 at 8:8

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.

Wilkens answered 4/12, 2014 at 6:15 Comment(0)

Recommended topics

Hot tags