再来谈谈如何从binlog文件恢复误update的数据，模拟Oracle的闪回功能

原创锁钥 2023-09-19

368

看腻文章了就来听听视频演示吧：https://www.bilibili.com/video/BV19m4y1V7jA/

传统处理：全量备份+增量binlog备份
模拟Oracle闪回：前提是binlog_format=ROW

drop table t_student;
create table t_student(id int,name varchar(18),class int,score varchar(18));
insert into t_student values(1,'a',1,66),(2,'b',1,58),(3,'c',2,86),(4,'d',2,78);
update t_student set score='failure';
复制

原理：binlog的ROW模式记录update语句where所有列，调整binlog记录列保留修改前的值即可恢复
步骤：

查找误操作的binlog文件内容
binlog内容处理，转为可执行的SQL语句
执行SQL恢复达到回滚效果

# 查看当前biglog日志所在的点
mysql> show master logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000012 |      8736 |
| mysql-bin.000013 |       154 |
+------------------+-----------+
2 rows in set (0.00 sec)
# binlog查找到语句
[root@db01 data]# mysqlbinlog --no-defaults -v -v --base64-output=decode-rows mysql-bin.000013 | grep -B 15 'failure' | more
/*!*/;
# at 818
#230910 10:55:40 server id 3306  end_log_pos 876 CRC32 0x1ae07a0e       Table_map: `mdb`.`t_student` mapped to number
 127
# at 876
#230910 10:55:40 server id 3306  end_log_pos 1044 CRC32 0x279913de      Update_rows: table id 127 flags: STMT_END_F
### UPDATE `mdb`.`t_student`
### WHERE
###   @1=1 /* INT meta=0 nullable=1 is_null=0 */
###   @2='a' /* VARSTRING(54) meta=54 nullable=1 is_null=0 */
###   @3=1 /* INT meta=0 nullable=1 is_null=0 */
###   @4='66' /* VARSTRING(54) meta=54 nullable=1 is_null=0 */
### SET
###   @1=1 /* INT meta=0 nullable=1 is_null=0 */
###   @2='a' /* VARSTRING(54) meta=54 nullable=1 is_null=0 */
###   @3=1 /* INT meta=0 nullable=1 is_null=0 */
###   @4='failure' /* VARSTRING(54) meta=54 nullable=1 is_null=0 */
### UPDATE `mdb`.`t_student`
### WHERE
###   @1=2 /* INT meta=0 nullable=1 is_null=0 */
###   @2='b' /* VARSTRING(54) meta=54 nullable=1 is_null=0 */
###   @3=1 /* INT meta=0 nullable=1 is_null=0 */
###   @4='58' /* VARSTRING(54) meta=54 nullable=1 is_null=0 */
### SET
###   @1=2 /* INT meta=0 nullable=1 is_null=0 */
###   @2='b' /* VARSTRING(54) meta=54 nullable=1 is_null=0 */
###   @3=1 /* INT meta=0 nullable=1 is_null=0 */
###   @4='failure' /* VARSTRING(54) meta=54 nullable=1 is_null=0 */
### UPDATE `mdb`.`t_student`
### WHERE
###   @1=3 /* INT meta=0 nullable=1 is_null=0 */
###   @2='c' /* VARSTRING(54) meta=54 nullable=1 is_null=0 */
###   @3=2 /* INT meta=0 nullable=1 is_null=0 */
###   @4='86' /* VARSTRING(54) meta=54 nullable=1 is_null=0 */
### SET
###   @1=3 /* INT meta=0 nullable=1 is_null=0 */
###   @2='c' /* VARSTRING(54) meta=54 nullable=1 is_null=0 */
###   @3=2 /* INT meta=0 nullable=1 is_null=0 */
###   @4='failure' /* VARSTRING(54) meta=54 nullable=1 is_null=0 */
### UPDATE `mdb`.`t_student`
### WHERE
###   @1=4 /* INT meta=0 nullable=1 is_null=0 */
###   @2='d' /* VARSTRING(54) meta=54 nullable=1 is_null=0 */
###   @3=2 /* INT meta=0 nullable=1 is_null=0 */
###   @4='78' /* VARSTRING(54) meta=54 nullable=1 is_null=0 */
### SET
###   @1=4 /* INT meta=0 nullable=1 is_null=0 */
###   @2='d' /* VARSTRING(54) meta=54 nullable=1 is_null=0 */
###   @3=2 /* INT meta=0 nullable=1 is_null=0 */
###   @4='failure' /* VARSTRING(54) meta=54 nullable=1 is_null=0 */
复制

把语句对应binlog导出

[root@db01 data]# mysqlbinlog --no-defaults -v -v --base64-output=decode-rows mysql-bin.000013 | sed -n '/# at 876/,/COMMIT/p' > tbl_data.txt
[root@db01 data]# cat tbl_data.txt 
# at 876
#230910 10:55:40 server id 3306  end_log_pos 1044 CRC32 0x279913de      Update_rows: table id 127 flags: STMT_END_F
### UPDATE `mdb`.`t_student`
### WHERE
###   @1=1 /* INT meta=0 nullable=1 is_null=0 */
###   @2='a' /* VARSTRING(54) meta=54 nullable=1 is_null=0 */
###   @3=1 /* INT meta=0 nullable=1 is_null=0 */
###   @4='66' /* VARSTRING(54) meta=54 nullable=1 is_null=0 */
### SET
###   @1=1 /* INT meta=0 nullable=1 is_null=0 */
###   @2='a' /* VARSTRING(54) meta=54 nullable=1 is_null=0 */
###   @3=1 /* INT meta=0 nullable=1 is_null=0 */
###   @4='failure' /* VARSTRING(54) meta=54 nullable=1 is_null=0 */
### UPDATE `mdb`.`t_student`
### WHERE
###   @1=2 /* INT meta=0 nullable=1 is_null=0 */
###   @2='b' /* VARSTRING(54) meta=54 nullable=1 is_null=0 */
###   @3=1 /* INT meta=0 nullable=1 is_null=0 */
###   @4='58' /* VARSTRING(54) meta=54 nullable=1 is_null=0 */
### SET
###   @1=2 /* INT meta=0 nullable=1 is_null=0 */
###   @2='b' /* VARSTRING(54) meta=54 nullable=1 is_null=0 */
###   @3=1 /* INT meta=0 nullable=1 is_null=0 */
###   @4='failure' /* VARSTRING(54) meta=54 nullable=1 is_null=0 */
### UPDATE `mdb`.`t_student`
### WHERE
###   @1=3 /* INT meta=0 nullable=1 is_null=0 */
###   @2='c' /* VARSTRING(54) meta=54 nullable=1 is_null=0 */
###   @3=2 /* INT meta=0 nullable=1 is_null=0 */
###   @4='86' /* VARSTRING(54) meta=54 nullable=1 is_null=0 */
### SET
###   @1=3 /* INT meta=0 nullable=1 is_null=0 */
###   @2='c' /* VARSTRING(54) meta=54 nullable=1 is_null=0 */
###   @3=2 /* INT meta=0 nullable=1 is_null=0 */
###   @4='failure' /* VARSTRING(54) meta=54 nullable=1 is_null=0 */
### UPDATE `mdb`.`t_student`
### WHERE		-- 误操作之前的数据
###   @1=4 /* INT meta=0 nullable=1 is_null=0 */
###   @2='d' /* VARSTRING(54) meta=54 nullable=1 is_null=0 */
###   @3=2 /* INT meta=0 nullable=1 is_null=0 */
###   @4='78' /* VARSTRING(54) meta=54 nullable=1 is_null=0 */
### SET			-- 误操作之后的数据
###   @1=4 /* INT meta=0 nullable=1 is_null=0 */
###   @2='d' /* VARSTRING(54) meta=54 nullable=1 is_null=0 */
###   @3=2 /* INT meta=0 nullable=1 is_null=0 */
###   @4='failure' /* VARSTRING(54) meta=54 nullable=1 is_null=0 */
# at 1044
#230910 10:55:40 server id 3306  end_log_pos 1075 CRC32 0xe84469d8      Xid = 564
COMMIT/*!*/;
复制

把binlog转换成SQL语句

[root@db01 data]# sed '/WHERE/{:a;N;/SET/!ba;s/\([^\n]*\)\n\(.*\)\n\(.*\)/\3\n\2\n\1/}' tbl_data.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' > recovery.sql
[root@db01 data]# cat recovery.sql 
UPDATE `mdb`.`t_student`
SET
  @1=1 ,
  @2='a' ,
  @3=1 ,
  @4='66' ,
WHERE
  @1=1 ;
UPDATE `mdb`.`t_student`
SET
  @1=2 ,
  @2='b' ,
  @3=1 ,
  @4='58' ,
WHERE
  @1=2 ;
UPDATE `mdb`.`t_student`
SET
  @1=3 ,
  @2='c' ,
  @3=2 ,
  @4='86' ,
WHERE
  @1=3 ;
UPDATE `mdb`.`t_student`
SET
  @1=4 ,
  @2='d' ,
  @3=2 ,
  @4='78' ,
WHERE
  @1=4 ;
复制

替换@1、@2、@3、@4对应表字段 id 、name、class、score

sed -i 's/@1/id/g;s/@2/name/g;s/@3/class/g;s/@4/score/g' recovery.sql
sed -i -r 's/(score=.*),/\1/g' recovery.sql
[root@db01 data]# cat recovery.sql 
UPDATE `mdb`.`t_student`
SET
  id=1 ,
  name='a' ,
  class=1 ,
  score='66' 
WHERE
  id=1 ;
UPDATE `mdb`.`t_student`
SET
  id=2 ,
  name='b' ,
  class=1 ,
  score='58' 
WHERE
  id=2 ;
UPDATE `mdb`.`t_student`
SET
  id=3 ,
  name='c' ,
  class=2 ,
  score='86' 
WHERE
  id=3 ;
UPDATE `mdb`.`t_student`
SET
  id=4 ,
  name='d' ,
  class=2 ,
  score='78' 
WHERE
  id=4 ;
复制

恢复

mysql> select * from t_student;
+------+------+-------+---------+
| id   | name | class | score   |
+------+------+-------+---------+
|    1 | a    |     1 | failure |
|    2 | b    |     1 | failure |
|    3 | c    |     2 | failure |
|    4 | d    |     2 | failure |
+------+------+-------+---------+
4 rows in set (0.00 sec)

mysql> source /mysqldata/data/recovery.sql 
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from t_student;
+------+------+-------+-------+
| id   | name | class | score |
+------+------+-------+-------+
|    1 | a    |     1 | 66    |
|    2 | b    |     1 | 58    |
|    3 | c    |     2 | 86    |
|    4 | d    |     2 | 78    |
+------+------+-------+-------+
4 rows in set (0.00 sec)
复制

MySQL的binlog系列和奇技操作：

1) 先来聊聊MySQL的binlog文件解析
 2) 接着说说mysqlbinlog解析工具如何做数据恢复
 3) 再来谈谈如何从binlog文件恢复误update的数据，模拟Oracle的闪回功能
 4) 接着聊聊如何从binlog文件恢复误delete的数据，模拟Oracle的闪回功能
 5) 借用binlog2sql工具轻松解析MySQL的binlog文件，再现Oracle的闪回功能
 6) 再来介绍另一个binlog文件解析的第三方工具my2sql
7) 顺带来聊聊MySQL误删ibdata数据文件的恢复
 8) MySQL大表直接复制文件的copy方式