MySQL使用binlog日志回滚操作失误的数据的操作教程

目录

• 一、背景
• 二、准备测试数据
• 1. 创建测试表
• 2. 创建测试数据
• 三、模拟误操作
• 四、数据回滚
• （一）方案一：云数据库恢复
• （二）方案二：手动恢复
• 1. 查询 binlog 日志
• 2. 找到删除语句，手动还原为插入语句
• （三）方案三: 导入还原的 sql（未测试，慎用）
• 总结

一、背景

在日常开发运维中，我们可能会出现一些操作失误的情况，比如使用了错误的 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 语句如下：

/*js!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'
### DELEjsTE 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 0x90bwww.devze.com858f7     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回滚数据的资料请关注编程javascript客栈(www.devze.com)其它相关文章！