成都龙泉建设发展有限公司网站,门户网站建设项目招标,网页设计模板网,品牌网线有哪些看腻了文章就来听听视频演示吧#xff1a;https://www.bilibili.com/video/BV1cV411A7iU/
delete忘加where条件#xff08;模拟Oracle闪回#xff09;
操作基本等同于上篇#xff1a;再来谈谈如何从binlog文件恢复误update的数据#xff0c;模拟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 id2;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语句
[rootdb01 data]# mysqlbinlog --no-defaults -v -v --base64-outputdecode-rows mysql-bin.000013 | sed -n /### DELETE FROM mdb.t_student/,/COMMIT/p deltbl_data.txt
[rootdb01 data]# cat deltbl_data.txt
### DELETE FROM mdb.t_student
### WHERE
### 13 /* INT meta0 nullable1 is_null0 */
### 2c /* VARSTRING(54) meta54 nullable1 is_null0 */
### 32 /* INT meta0 nullable1 is_null0 */
### 486 /* VARSTRING(54) meta54 nullable1 is_null0 */
### DELETE FROM mdb.t_student
### WHERE
### 14 /* INT meta0 nullable1 is_null0 */
### 2d /* VARSTRING(54) meta54 nullable1 is_null0 */
### 32 /* INT meta0 nullable1 is_null0 */
### 478 /* VARSTRING(54) meta54 nullable1 is_null0 */
# at 2508
#230910 11:44:32 server id 3306 end_log_pos 2539 CRC32 0x7be20ca3 Xid 571
COMMIT/*!*/;转换成标准SQL
[rootdb01 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
[rootdb01 data]# cat instbl_data.sql
INSERT INTO mdb.t_student
SELECT3 ,c ,2 ,86 ;
INSERT INTO mdb.t_student
SELECT4 ,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: 0Query OK, 1 row affected (0.00 sec)
Records: 1 Duplicates: 0 Warnings: 0mysql 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)
