EmergencyEMERGENCY? Get 24/7 Help Now!

Recovering from a bad UPDATE statement

 | October 19, 2012 |  Posted In: Insight for DBAs, Insight for Developers, MySQL


Did you just run an UPDATE against your 10 million row users table without a WHERE clause?  Did you know that in MySQL 5.5 that sometimes you can recover from a bad UPDATE statement?  This is possible if you are running in binlog_format=ROW !

Imagine this scenario:

We run an accidental UPDATE statement that changes a row:

If we examine this UPDATE using the Binary Logging format of STATEMENT the entry would look like:

Now let’s see what it looks like if you were using the Binary Logging format of ROW:

Of interest is that the old and new versions of the changed column is stored in the binary log — in this example in @2.

This example looks trivial to fix when it is just one row changed — you don’t need anything fancy to recover from this failure case and can just run another UPDATE reversing the old and new values and be done.  But like I started out saying, what if this UPDATE statement affected all rows in your very important big table? It might look more like this:

And when we examine the binary log (still in binlog_format=ROW):

As an exercise to the reader you would then need to:

  • Stop database activity (set read_only or put up a holding page on your web server, etc) so that changes are no longer hitting your database. You need time to think.
  • Take a database backup if you can.
  • Identify which binary log has the offending UPDATE statement.
  • Parse the binary log using mysqlbinlog -vv  and match on the old version of @1 to the new version of @1, old version of @2 to the new version of @2, etc.
  • Reverse the entries row entries and create new UPDATE statements that filter on the new version and SET the old version.

I hope this helps someone out there!

Note: this recovery example ONLY works when binlog_format=ROW — MIXED doesn’t help as in my testing as MySQL chooses to binlog UPDATEs in STATEMENT format when in MIXED mode.


Michael Coburn

Michael joined Percona as a Consultant in 2012 after having worked with high volume stock photography websites and email service provider platforms. With a foundation in Systems Administration, Michael enjoys working with SAN technologies and high availability solutions. A Canadian, Michael currently lives in Costa Rica with his wife, two children, and two dogs.


  • Hi, I add this feature for mysqlbinlog, you can see: http://bugs.mysql.com/bug.php?id=65178, or http://mysql.taobao.org/index.php/Patch_source_code#Add_flashback_feature_for_mysqlbinlog
    This patch is based Percona 5.5.18

    I add a option “-B/–flashback” for mysqlbinlog, it can do the same thing like you said.

    you can try my patch. 🙂

  • Thanks for the link P. Linux! Have you tried to submit this feature request to Percona Server as well?


  • yes, this is a quite obvious exploitation of the fact that row based binlog stores the before image of a modified (deleted or updated) row in the binlog.

    However this will soon be history. MySQL 5.6 adds a new variable binlog_row_image:


    When this is set to “minimal”, the binlog will only contain the PK field of the before image (assuming each table has a PK, which is pretty reasonable).

    This new behavior is meant as an optimization, which it certainly is. Just imagine a mass delete from a wide table. Full before image would need much more I/O bandwidth for logging and log shipping than logging just the PK. One however must be aware that enabling this new feature has side effects.

    PS: for MySQL cluster the above trick will not work anyway. It defaults to log minimal before images (–ndb-log-updated-only defaults to TRUE) and what’s more: it defaults to log updates as inserts (–ndb-log-update-as-write defaults to TRUE). See http://dev.mysql.com/doc/refman/5.5/en/mysql-cluster-replication-conflict-resolution.html

Leave a Reply