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:

Shell

1

2

3

4

5

6

CREATE TABLE`t1`(

`c1`int(11)NOTNULLAUTO_INCREMENT,

`c2`varchar(10)NOTNULL,

PRIMARY KEY(`c1`)

)ENGINE=InnoDB;

INSERT INTO`t1`(`c2`)VALUES('michael'),('peter'),('aamina');

We run an accidental UPDATE statement that changes a row:

Shell

1

UPDATE`t1`SET`c2`='tom'WHERE`c1`=2;

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

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:

Related

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.

5 Comments

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.

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