前提条件:
mysql :data_row_format=row
mysql> show variables like '%image%';
+------------------+-------+
| Variable_name | Value |
+------------------+-------+
| binlog_row_image | FULL |
+------------------+-------+
实例操作过程=========================
create table student(id int,name varchar(20),class int ,score varchar(20));
insert into student values(1,'a',1,'failure');
insert into student values(3,'b',1,'failure'),(5,'c',2,'failure'),(7,'d',2,'failure');
insert into student values(9,'e',3,'failure'),(11,'f',3,'failure'),(13,'g',4,'failure');
insert into student values(15,'h',4,'failure');
mysql> select * from student;
+----+------+-------+---------+
| id | name | class | score |
+----+------+-------+---------+
| 1 | a | 1 | failure |
| 3 | b | 1 | failure |
| 5 | c | 2 | failure |
| 7 | d | 2 | failure |
| 9 | e | 3 | failure |
| 11 | f | 3 | failure |
| 13 | g | 4 | failure |
| 15 | h | 4 | failure |
+----+------+-------+---------+
8 rows in set (0.00 sec)
mysql>
mysql>
mysql>
mysql> update student set score=35;
Query OK, 8 rows affected (0.01 sec)
Rows matched: 8 Changed: 8 Warnings: 0
mysql>
mysql>
mysql> select * from student;
+----+------+-------+-------+
| id | name | class | score |
+----+------+-------+-------+
| 1 | a | 1 | 35 |
| 3 | b | 1 | 35 |
| 5 | c | 2 | 35 |
| 7 | d | 2 | 35 |
| 9 | e | 3 | 35 |
| 11 | f | 3 | 35 |
| 13 | g | 4 | 35 |
| 15 | h | 4 | 35 |
+----+------+-------+-------+
8 rows in set (0.00 sec)
------恢复步骤:
1) /usr/local/mysql/bin/mysqlbinlog -v -v --base64-output=decode-rows /data/mysql/3306/log/mysql-bin.000006 | grep -B15 -A 10 -i 'failure'
2) /usr/local/mysql/bin/mysqlbinlog -v -v --base64-output=decode-rows /data/mysql/3306/log/mysql-bin.000006 |sed -n '/# at 351/,/COMMIT/p' >/tmp/1.txt
3) sed '/WHERE/{:a;N;/SET/!ba;s/([^
]*)
(.*)
(.*)/3
2
1/}' 1.txt | sed -r '/WHERE/{:a;N;/@4/!ba;s/### @2.*//g}'|sed 's/### //g;s//*.*/ ,/g' | sed '/WHERE/{:a;N;/@1/!ba;s/ ,/;/g};s/#.*//g;s/COMMIT ,//g' | sed '/^$/d' > recover.sql
4)sed -i 's/@1/id/g;s/@2/name/g;s/@3/class/g;s/@4/score/g' recover.sql
5) sed -i -r 's/(score=.*),/1/g' recover.sql
6) mysql > source </tmp/recover.sql