标签：binlog 改后 -- INTO 误删 INSERT sed sql ###

mysql结合binlog实现数据误删误改后的数据恢复

测试数据：

建表

CREATE TABLE `student` (
`id` int NOT NULL AUTO_INCREMENT,
`name` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL,
`sex` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL,
`age` int DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

数据：

INSERT INTO `student` VALUES (1, '张三', '男', 26);
INSERT INTO `student` VALUES (2, '李四', '男', 22);
INSERT INTO `student` VALUES (3, '王五', '男', 17);
INSERT INTO `student` VALUES (4, '张莉', '女', 23);
INSERT INTO `student` VALUES (5, '李彤', '女', 20);
INSERT INTO `student` VALUES (6, '王丹丹', '女', 18);

效果如下：

一、执行delete后数据的恢复

删除一条数据

delete from student where id= 6

执行以下命令：

mysqlbinlog --no-defaults --database=test --start-datetime="2023-03-29 09:01:00" --stop-datetime="2023-03-29 09:56:00" /usr/local/mysql/mysql8.0/data/binlog.000009 > /home/rollback.sql

start-datetime 　　　　　　　　执行删除sql的开始时间（不知道写个大概就行）

stop-datetime 　　　　　　　　执行删除sql的开始时间

binlog.000009 　　　　　　　　选择自己电脑上的binlog文件

/home/rollback.sql  　　　　　　将这部分日志导出到该目录下的一个sql文件

效果如图所示：

 横线部分是该表执行的操作

上图方框圈起来的内容下面会用到

mysqlbinlog --no-defaults --database=test --base64-output=decode-rows -vv --start-position=4681 --stop-position=4804 binlog.000009 >a1.sql

start-positi　　on和stop-position就是上图方框的两个值

效果如图所示：

 cat a1.sql | 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-4]=//g' > result.txt

代码解析：

cat a1.sql 　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　   打开上一步导出的a1.sql文件

sed -n '/### /p　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　以###开头的行

sed 's/### //g;s/\/\*.*/,/g;s/DELETE FROM/INSERT INTO/g;s/WHERE/SELECT/g;'　　　　 删除###，删除/**/部分内容，将DELETE FROM替换为INSERT INTO，将WHERE替换成SELECT

sed -r 's/(@4.*),/\1;/g'　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　在@4行最后一行加上；号（根据上图方框中的set上面一行自己是多少就改为多少，我这里是4）

sed 's/@[1-4]=//g'　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　  将@1-4=部分删除（我这里最大值是4，根据自己情况更改最大值）

效果如图所示：

 根据导出来的代码，然后执行sql进行数据恢复就可以了

二、执行update后数据的恢复

我把id为6的学生名更改后如下所示：

 mysqlbinlog --no-defaults --database=test --start-datetime="2023-03-29 09:01:00" --stop-datetime="2023-03-29 10:56:00" /usr/local/mysql/mysql8.0/data/binlog.000009 > /home/rollback.sql

和删除第一步操作一样，改一下stop-datetime时间就好

 mysqlbinlog --no-defaults --database=test --base64-output=decode-rows -vv --start-position=5306 --stop-position=5455 binlog.000009 >a1.sql

和删除第二步操作一样，改一下start-position和stop-position值就好

效果如图所示：

 sed '/WHERE/{:a;N;/SET/!ba;s/\([^\n]*\)\n\(.*\)\n\(.*\)/\3\n\2\n\1/}' a1.sql | sed 's/### //g;s/\/\*.*/,/g' | sed  /@4/s/,//g | sed '/WHERE/{:a;N;/@4/!ba;s/,/AND/g};s/#.*//g;s/COMMIT,//g' > d.txt

sed '/WHERE/{:a;N;/SET/!ba;s/\([^\n]*\)\n\(.*\)\n\(.*\)/\3\n\2\n\1/}' a1.sql　　　　读取a1.sql文件并将where 、set位置对调

 sed 's/### //g;s/\/\*.*/,/g'　　　　　　　　　　　　　　　　　　　　　　　　删除所有###并将/**/替换成英文逗号

sed  /@4/s/,//g　　　　　　　　　　　　　　　　　　　　　　　　　　　　 去除@=4最后面的逗号（自己最后一行是什么数据就写几）

sed '/WHERE/{:a;N;/@4/!ba;s/,/AND/g};s/#.*//g;s/COMMIT,//g' 　　　　　　　 将where后面的逗号替换成and，#替换成空串，commit替换成空串

执行后效果如下：

 将方框内的代码复制出来：将@数字替换成对应字段名id

UPDATE `test`.`student` SET id = 6,NAME = '王丹丹',sex = '女',age = 18
WHERE
id = 6 AND NAME = '王婷婷' AND sex = '女' AND age = 18

执行sql语句进行恢复数据

 

标签：binlog,改后,--,INTO,误删,INSERT,sed,sql,###
From： https://www.cnblogs.com/zuouncle/p/17268414.html