Mysql binlog回滚数据

Mysql binlog回滚数据

43 次访问
· mysql

MySQL binlog 回滚数据

一、背景

错误 SQL 操作了 update、delete 等，需要回滚数据

二、准备测试数据

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

四、数据回滚

查询 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

（一）、方案一：找到删除语句，手机还原为插入语句

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，不会对已有的数据造成其他损坏

**缺点：**不适合一些大量数据的还原

（二）、方案二： 导入还原的 SQL

网上的说法是使用 mysqlbinlog 工具导出的 SQL，重新再导入 MySQL：MySQL -u root -p test < rec.sql

没有验证过，请自行百度

# mysql