Emergency

Recovering from a bad UPDATE statement


Posted on:

|

By:


PREVIOUS POST
NEXT POST
Share Button

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.

 

Share Button
PREVIOUS POST
NEXT POST


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.



Categories:
Insight for DBAs, Insight for Developers, MySQL


Comments

Leave a Reply

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