-
再来谈谈如何从binlog文件恢复误update的数据，模拟Oracle的闪回功能
看腻文章了就来听听视频演示吧：https://www.bilibili.com/video/BV19m4y1V7jA/
传统处理：全量备份+增量binlog备份
模拟Oracle闪回：前提是binlog_format=ROWdrop 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';- 1
- 2
- 3
- 4
原理：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 */- 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
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
- 45
- 46
- 47
- 48
- 49
- 50
- 51
- 52
- 53
- 54
- 55
- 56
- 57
- 58
- 59
- 60
- 61
把语句对应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/*!*/;- 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
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
- 45
- 46
- 47
- 48
- 49
- 50
- 51
把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
- 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
- 30
- 31
- 32
- 33
- 34
替换@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 ;- 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
- 30
- 31
- 32
- 33
- 34
- 35
恢复
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)- 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
- 30
- 31
- 32
- 33
- 34
MySQL的binlog系列和奇技操作：
先来聊聊MySQL的binlog文件解析
接着说说mysqlbinlog解析工具如何做数据恢复
再来谈谈如何从binlog文件恢复误update的数据，模拟Oracle的闪回功能
接着聊聊如何从binlog文件恢复误delete的数据，模拟Oracle的闪回功能
借用binlog2sql工具轻松解析MySQL的binlog文件，再现Oracle的闪回功能
再来介绍另一个binlog文件解析的第三方工具my2sql
顺带来聊聊MySQL误删ibdata数据文件的恢复
MySQL大表直接复制文件的copy方式 -
相关阅读:
【开源】渔具租赁系统 JAVA+Vue.js+SpringBoot+MySQL
Unity中控制摄像机跟踪游戏角色（插值柔和追踪+旋转）
呼吸系统药物--平喘药
限制某一个月的选择
【毕业设计】基于stm32的便携式U盘设计与实现 - stm32制作U盘
蒸散发与植被总初级生产力的区域数据下载、处理与显示
Armadillo矩阵库在Visual Studio软件C++环境中的配置方法
Java8中的日期时间API
gcc/g++的使用
2024年保安员职业资格考试真题分享
- 原文地址：https://blog.csdn.net/suoyue_py/article/details/133483686
