26

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?

Johnny Lim
  • 5,623
  • 8
  • 38
  • 53
  • 2
    No. You have a backup, I presume? – Michael Berkowski Dec 24 '12 at 02:04
  • Unfortunately I don't have any backup :'( – Johnny Lim Dec 24 '12 at 02:08
  • 4
    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. – Matt Dec 24 '12 at 02:08
  • I feel your pain after doing the same thing. I guess you learn by your mistakes. – Ivan Bilan Nov 24 '15 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. – Mickael Bergeron Néron Jun 22 '20 at 03:00

3 Answers3

15

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.

Bohemian
  • 412,405
  • 93
  • 575
  • 722
  • Can you suggest any DBA tool which can do it? – Johnny Lim Dec 24 '12 at 03: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. – Johnny Lim Dec 24 '12 at 06:50
  • @JohnnyLim a little late in responding I know, but the tool to use for mysql is [mysqlbinlog](https://dev.mysql.com/doc/refman/5.5/en/mysqlbinlog.html) (answer updated) – Bohemian Jun 10 '15 at 18:08
1

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

Kyle
  • 1,019
  • 1
  • 10
  • 19
  • What are the names of transaction logs? ib_logfile0, ib_logfile1? – Johnny Lim Dec 24 '12 at 03:09
  • Unfortunately I don't use binary logging, either :'( And recovery from binary log needs base backup which the binary log doesn't have, right? – Johnny Lim Dec 24 '12 at 04:41
  • 5
    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. – Bohemian Dec 24 '12 at 08:08
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.

Srinivash Arumugam
  • 1
  • 1