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.

 

5 Comments
Oldest
Newest Most Voted
Inline Feedbacks
View all comments
P.Linux

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. 🙂

0
mablevi

very good!

0
Michael Coburn

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

https://launchpad.net/percona-server

0
P.Linux

Hi Michael ,

I have submitted to MySQL bug system.
I open a feature request to Percona Server now, Thanks!

0
XL

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:

http://dev.mysql.com/doc/refman/5.6/en/replication-options-binary-log.html#sysvar_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

0