EmergencyEMERGENCY? Get 24/7 Help Now!

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

Percona’s widely read Percona Data Performance blog highlights our expertise in enterprise-class software, support, consulting and managed services solutions for both MySQL® and MongoDB® across traditional and cloud-based platforms. The decades of experience represented by our consultants is found daily in numerous and relevant blog posts.

Besides specific database help, the blog also provides notices on upcoming events and webinars.
Want to get weekly updates listing the latest blog posts? Subscribe to our blog now! Submit your email address below and we’ll send you an update every Friday at 1pm ET.

No, thank you. Please do not ask me again.