我要评论一、背景
在日常开发运维中，我们可能会出现一些操作失误的情况，比如使用了错误的 sql 语句对 mysql 数据进行了 update、delete 等操作，失误操作导致数据出现问题，又或者架构设计上没有使用软删除机制，用户误删除了数据需要恢复。
那么如果出现这种情况，应该如何恢复失误/mysql 误删数据时，本文将使用几种常用方法一步一步带你回滚误删/误操的数据
二、准备测试数据
1. 创建测试表
create table jxy_pms.test_model
(
create_time bigint unsigned default 0 null,
update_time bigint unsigned default 0 null,
delete_time bigint unsigned default 0 null,
test_model_id varchar(255) not null
primary key,
name varchar(255) null,
remark varchar(255) null
)
collate = utf8mb4_unicode_ci;
2. 创建测试数据
insert into test_model (test_model_id, name, remark)
values ('1','test1','test-1'),
('2','test2','test-2'),
('3','test3','test-3');
select * from test_model
test_model_id name remark
1 test1 test-1
2 test2 test-2
3 test3 test-3
三、模拟误操作
delete from test_model
四、数据回滚
（一）方案一：云数据库恢复
如果你的 mysql 使用的时云数据库，比如阿里云、腾讯云，那么你可以到云数据库的控制台操作面板进行一键数据回滚，一般都可以选择对应的恢复区间，具体可以咨询对应的云数据库提供商，一般都会提供技术支持
优点：
- 低风险
- 操作简单
- 有云供应商提供技术支持
缺点：
- 恢复精度较低，没办法恢复指定的数据
- 有可能恢复不全，云数据库是定期备份，可能还没来得及备份就被删了
（二）方案二：手动恢复
通过解读 binlog 日志内容，进行解析然后回放数据
binlog 日志：大白话：记录数据库的每个修改操作 sql
所以我们只需要找到对应的 binlog 日志中我们误操作的 sql 数据，然后编写对应的回放sql，就可以回滚数据
实操如下：
1. 查询 binlog 日志
show binary logs; 或 show master status; mysql-bin.000014 80019706 no mysql-bin.000015 1326884 no mysql-bin.000016 3650781 no mysql-bin.000017 81424072 no mysql-bin.000018 46681992 no mysql-bin.000019 1075 no mysql-bin.000020 207322979 no
可以看到最新的日志为：mysql-bin.000020
2. 找到删除语句，手动还原为插入语句
mysqlbinlog --no-defaults --verbose --base64-output=decode-rows --start-datetime='2025-01-16 15:12:00' --stop-datetime='2025-01-16 15:15:00' /var/lib/mysql/mysql-bin.000027 > /home/datavolume/rec.sql
- --base64-output=decode-rows 生成不加密的 sql 文件
- --start-datetime 、stop-datetime 数据操作的时间区间
从 rec.sql 中找到对应的表 test_model 的删除操作，如果找不到，调整下--start-datetime 和 stop-datetime
找到删除的 sql 语句如下：
/*!50530 set @@session.pseudo_slave_mode=1*/; /*!50003 set @old_completion_type=@@completion_type,completion_type=0*/; delimiter /*!*/; ........ /*!*/; # at 207319108 #240919 10:58:30 server id 1 end_log_pos 207319202 crc32 0xe7b80345 table_map: `jxy_pms`.`asynccron_cron_task` mapped to number 306 #240919 10:59:04 server id 1 end_log_pos 207321626 crc32 0x6b3323a9 table_map: `jxy_pms`.`test_model` mapped to number 315 # at 207321626 #240919 10:59:04 server id 1 end_log_pos 207321718 crc32 0xb004dad7 delete_rows: table id 315 flags: stmt_end_f ### delete from `jxy_pms`.`test_model` ### where ### @1='1' ### @2='test1' ### @3='test-1' ### delete from `jxy_pms`.`test_model` ### where ### @1='2' ### @2='test2' ### @3='test-2' ### delete from `jxy_pms`.`test_model` ### where ### @1='3' ### @2='test3' ### @3='test-3' # at 207321718 #240919 10:59:04 server id 1 end_log_pos 207321749 crc32 0x90b858f7 xid = 7486642 commit/*!*/;
然后写个脚本，解析这块 sql，重新翻译为 insert 语句即可
优点：
- 适合一些少量数据的还原
- 能准确还原指定的数据，操作简单
- 重新执行的 sql，不会对已有的数据造成其他损坏
缺点：
- 不适合一些大量数据的还原
- 比较复杂，需要解读 binlog 日志，并且需要编写脚本
（三）方案三: 导入还原的 sql（未测试，慎用）
如果你的数据库不是用的云数据库，没有一键恢复功能，然后你又不想用方案二，觉得太复杂，你可以直接将 binlog 日志直接全部还原
先导出 binlog 日志
mysqlbinlog --no-defaults --verbose --base64-output=decode-rows --start-datetime='2025-01-16 15:12:00' --stop-datetime='2025-01-16 15:15:00' /var/lib/mysql/mysql-bin.000027 > /home/datavolume/rec.sql
这里会导出 rec.sql 文件
导入 sql
mysql -u root -p test < rec.sql
优点：
- 相比较方案二简单一些
- 适合范围恢复
缺点：
- 恢复精度较低，比如你可能只需要恢复 2025-01-16 15:12:00 这个点的某条错误sql，但是这个点可能还有其他操作sql也会被一起恢复，
- 恢复期间的操作数据可能存在丢失
⚠️：本方案没有实际验证过，请自行测试
总结
如果你用的是云数据库，并且恢复的数据量比较大，推荐【方案一】使用云数据库提供的恢复功能（非常适用那么删库跑路的恢复，哈哈哈哈）
如果你只是想要恢复某个个点、某些错误sql，那么可以使用【方案二】
以上就是mysql使用binlog日志回滚操作失误的数据的操作教程的详细内容，更多关于mysql binlog回滚数据的资料请关注代码网其它相关文章！
发表评论