接着聊聊如何从binlog文件恢复误delete的数据，模拟Oracle的闪回功能 |
您所在的位置：网站首页 › oracle恢复delete的数据 › 接着聊聊如何从binlog文件恢复误delete的数据，模拟Oracle的闪回功能 |
|
看腻了文章就来听听视频演示吧：https://www.bilibili.com/video/BV1cV411A7iU/ delete忘加where条件（模拟Oracle闪回）操作基本等同于上篇：再来谈谈如何从binlog文件恢复误update的数据，模拟Oracle的回滚功能 原理：binlog的ROW模式将记录的delete语句内容转换成insert语句 步骤： 查找误操作的binlog文件内容binlog内容处理，转为可执行的SQL语句执行SQL恢复达到回滚效果 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) # 模拟误删数据 delete from t_student where id>2; mysql> select * from t_student; +------+------+-------+-------+ | id | name | class | score | +------+------+-------+-------+ | 1 | a | 1 | 66 | | 2 | b | 1 | 58 | +------+------+-------+-------+ 2 rows in set (0.00 sec)binlog查找到误delete语句 [root@db01 data]# mysqlbinlog --no-defaults -v -v --base64-output=decode-rows mysql-bin.000013 | sed -n '/### DELETE FROM `mdb`.`t_student`/,/COMMIT/p' > deltbl_data.txt [root@db01 data]# cat deltbl_data.txt ### DELETE FROM `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 */ ### DELETE FROM `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 */ # at 2508 #230910 11:44:32 server id 3306 end_log_pos 2539 CRC32 0x7be20ca3 Xid = 571 COMMIT/*!*/;转换成标准SQL [root@db01 data]# cat deltbl_data.txt | sed -n '/###/p' | sed 's/### //g;s/\/\*.*/,/g;s/DELETE FROM/INSERT INTO/g;s/WHERE/SELECT/g;' | sed -r 's/(@4.*),/\1;/g' | sed 's/@[1-9]=//g' > instbl_data.sql [root@db01 data]# cat instbl_data.sql INSERT INTO `mdb`.`t_student` SELECT 3 , 'c' , 2 , '86' ; INSERT INTO `mdb`.`t_student` SELECT 4 , 'd' , 2 , '78' ;恢复 mysql> select * from t_student; +------+------+-------+-------+ | id | name | class | score | +------+------+-------+-------+ | 1 | a | 1 | 66 | | 2 | b | 1 | 58 | +------+------+-------+-------+ 2 rows in set (0.00 sec) mysql> source /mysqldata/data/instbl_data.sql Query OK, 1 row affected (0.00 sec) Records: 1 Duplicates: 0 Warnings: 0 Query OK, 1 row affected (0.00 sec) Records: 1 Duplicates: 0 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系列和奇技操作： 先来聊聊MySQL的binlog文件解析 接着说说mysqlbinlog解析工具如何做数据恢复 再来谈谈如何从binlog文件恢复误update的数据，模拟Oracle的闪回功能 接着聊聊如何从binlog文件恢复误delete的数据，模拟Oracle的闪回功能 借用binlog2sql工具轻松解析MySQL的binlog文件，再现Oracle的闪回功能 再来介绍另一个binlog文件解析的第三方工具my2sql 顺带来聊聊MySQL误删ibdata数据文件的恢复 MySQL大表直接复制文件的copy方式 |
| CopyRight 2018-2019 办公设备维修网 版权所有 豫ICP备15022753号-3 |