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:
|
1 2 3 4 5 6 |
CREATE TABLE `t1` ( `c1` int(11) NOT NULL AUTO_INCREMENT, `c2` varchar(10) NOT NULL, PRIMARY KEY (`c1`) ) ENGINE=InnoDB; INSERT INTO `t1` (`c2`) VALUES ('michael'), ('peter'), ('aamina'); |
We run an accidental UPDATE statement that changes a row:
|
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
# at 464 #121019 16:10:42 server id 1 end_log_pos 532 Query thread_id=1 exec_time=0 error_code=0 SET TIMESTAMP=1350677442/*!*/; BEGIN /*!*/; # at 532 #121019 16:10:42 server id 1 end_log_pos 638 Query thread_id=1 exec_time=0 error_code=0 SET TIMESTAMP=1350677442/*!*/; UPDATE `t1` SET `c2` = 'tom' WHERE `c1` = 2 /*!*/; # at 638 #121019 16:10:42 server id 1 end_log_pos 665 Xid = 19 COMMIT/*!*/; |
Now let’s see what it looks like if you were using the Binary Logging format of ROW:
|
1 2 3 4 5 6 7 8 9 |
# at 665 #121019 16:12:34 server id 1 end_log_pos 733 Query thread_id=1 exec_time=0 error_code=0 SET TIMESTAMP=1350677554/*!*/; BEGIN /*!*/; # at 733 # at 777 #121019 16:12:34 server id 1 end_log_pos 777 Table_map: `test`.`t1` mapped to number 39 #121019 16:12:34 server id 1 end_log_pos 827 Update_rows: table id 39 flags: STMT_END_F |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
BINLOG ' MrSBUBMBAAAALAAAAAkDAAAAACcAAAAAAAEABHRlc3QAAnQxAAIDDwIKAAA= MrSBUBgBAAAAMgAAADsDAAAAACcAAAAAAAEAAv///AIAAAADdG9t/AIAAAAFdmFkaW0= '/*!*/; ### UPDATE test.t1 ### WHERE ### @1=2 /* INT meta=0 nullable=0 is_null=0 */ ### @2='tom' /* VARSTRING(10) meta=10 nullable=0 is_null=0 */ ### SET ### @1=2 /* INT meta=0 nullable=0 is_null=0 */ ### @2='vadim' /* VARSTRING(10) meta=10 nullable=0 is_null=0 */ # at 827 #121019 16:12:34 server id 1 end_log_pos 854 Xid = 21 COMMIT/*!*/; |
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:
|
1 |
UPDATE `t1` SET `c2` = 'matt'; |
And when we examine the binary log (still in binlog_format=ROW):
|
1 2 3 4 5 6 7 8 9 |
# at 854 #121019 16:13:38 server id 1 end_log_pos 922 Query thread_id=1 exec_time=0 error_code=0 SET TIMESTAMP=1350677618/*!*/; BEGIN /*!*/; # at 922 # at 966 #121019 16:13:38 server id 1 end_log_pos 966 Table_map: `test`.`t1` mapped to number 39 #121019 16:13:38 server id 1 end_log_pos 1062 Update_rows: table id 39 flags: STMT_END_F |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 |
BINLOG ' crSBUBMBAAAALAAAAMYDAAAAACcAAAAAAAEABHRlc3QAAnQxAAIDDwIKAAA= crSBUBgBAAAAYAAAACYEAAAAACcAAAAAAAEAAv///AEAAAAHbWljaGFlbPwBAAAABG1hdHT8AgAA AAV2YWRpbfwCAAAABG1hdHT8AwAAAAZhYW1pbmH8AwAAAARtYXR0 '/*!*/; ### UPDATE test.t1 ### WHERE ### @1=1 /* INT meta=0 nullable=0 is_null=0 */ ### @2='michael' /* VARSTRING(10) meta=10 nullable=0 is_null=0 */ ### SET ### @1=1 /* INT meta=0 nullable=0 is_null=0 */ ### @2='matt' /* VARSTRING(10) meta=10 nullable=0 is_null=0 */ ### UPDATE test.t1 ### WHERE ### @1=2 /* INT meta=0 nullable=0 is_null=0 */ ### @2='vadim' /* VARSTRING(10) meta=10 nullable=0 is_null=0 */ ### SET ### @1=2 /* INT meta=0 nullable=0 is_null=0 */ ### @2='matt' /* VARSTRING(10) meta=10 nullable=0 is_null=0 */ ### UPDATE test.t1 ### WHERE ### @1=3 /* INT meta=0 nullable=0 is_null=0 */ ### @2='aamina' /* VARSTRING(10) meta=10 nullable=0 is_null=0 */ ### SET ### @1=3 /* INT meta=0 nullable=0 is_null=0 */ ### @2='matt' /* VARSTRING(10) meta=10 nullable=0 is_null=0 */ # at 1062 #121019 16:13:38 server id 1 end_log_pos 1089 Xid = 22 COMMIT/*!*/; |
As an exercise to the reader you would then need to:
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.
