看腻了文章就来听听视频演示吧: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系列和奇技操作:文章来源:https://www.toymoban.com/news/detail-728878.html
先来聊聊MySQL的binlog文件解析
接着说说mysqlbinlog解析工具如何做数据恢复
再来谈谈如何从binlog文件恢复误update的数据,模拟Oracle的闪回功能
接着聊聊如何从binlog文件恢复误delete的数据,模拟Oracle的闪回功能
借用binlog2sql工具轻松解析MySQL的binlog文件,再现Oracle的闪回功能
再来介绍另一个binlog文件解析的第三方工具my2sql
顺带来聊聊MySQL误删ibdata数据文件的恢复
MySQL大表直接复制文件的copy方式文章来源地址https://www.toymoban.com/news/detail-728878.html
到了这里,关于接着聊聊如何从binlog文件恢复误delete的数据,模拟Oracle的闪回功能的文章就介绍完了。如果您还想了解更多内容,请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章,希望大家以后多多支持TOY模板网!