Recovering from a bad UPDATE statement


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.




  1. XL says

    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

Leave a Reply

Your email address will not be published. Required fields are marked *