mysql结合binlog实现数据误删误改后的数据恢复
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语句进行恢复数据
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】博客园社区专享云产品让利特惠,阿里云新客6.5折上折
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· 一个费力不讨好的项目,让我损失了近一半的绩效!
· 清华大学推出第四讲使用 DeepSeek + DeepResearch 让科研像聊天一样简单!
· 实操Deepseek接入个人知识库
· CSnakes vs Python.NET:高效嵌入与灵活互通的跨语言方案对比
· Plotly.NET 一个为 .NET 打造的强大开源交互式图表库
2022-03-29 hive环境配置(linux)