MySQL误删数据不慌，还有的救

在软件开发与数据库管理的日常中，程序bug或误执行SQL语句导致的数据错误更新是一个不容忽视的问题。这类问题不仅可能导致业务数据的混乱，还可能对企业造成严重的经济损失。幸运的是，数据库系统为我们提供了一种强大的数据恢复机制——binlog（Binary Log），它能够记录所有修改数据的SQL语句，为我们在数据遭遇不测时提供了一线生机。本文将详细介绍如何通过解析binlog来恢复因程序bug或误执行SQL导致的数据错误更新或删除。

binlog基础

什么是binlog？

binlog是MySQL数据库中的一种二进制日志文件，它记录了数据库中所有修改数据的SQL语句，包括增删改操作。binlog的主要作用是用于数据库的主从复制和数据恢复。

binlog的类型

MySQL的binlog有三种格式：STATEMENT、ROW和MIXED。

STATEMENT：记录每一条会修改数据的SQL语句本身。
ROW：记录每一行数据的变化，不记录SQL语句本身。
MIXED：默认情况下使用STATEMENT格式，但在某些特殊情况下会自动切换为ROW格式。

通过binlog恢复数据理论基础

准备工作

确认binlog已开启：确保MySQL的binlog功能已开启，且binlog类型为ROW。
定位问题时间点：确定误执行的时间范围，以便在binlog中找到相关的记录。
误执行的SQL语句：确定误执行的SQL语句，以便在binlog中找到相关的记录。
备份当前数据：在进行数据恢复前，务必备份当前的数据状态，以防恢复过程中出现意外。

恢复步骤

查找binlog文件：根据问题时间点，定位到包含误操作的binlog文件。
解析binlog：使用MySQL提供的mysqlbinlog工具解析binlog文件，找到异常的SQL语句。
```
mysqlbinlog --no-defaults -v -v --base64-output=DECODE-ROWS mysql-bin.000001 > binlog.out
```
在生成的SQL文件中搜索关键词，定位到误执行的SQL语句。
编写恢复脚本：根据解析出的binlog内容，编写一个反向操作的SQL脚本，用于撤销误执行的SQL影响的数据修改。例如，如果错误地更新了某个字段的值，可以在恢复脚本中将其改回原来的值。
执行恢复脚本：在确保备份了当前数据后，执行恢复脚本。
验证恢复结果：检查恢复后的数据是否与预期一致，确保业务数据恢复正常。

实战场景一：update语句不加where条件误操作数据恢复

事件描述

程序BUG导致一张表的所有数据被逻辑删除（is_deleted字段被更新为1），该程序BUG是由于执行了一条UPDATE语句，WHERE条件设置有误引起，如下：

UPDATE `test`.`t1` SET is_deleted = 1;

根据开发同事的反馈，该SQL是在2025-09-05 14:48左右执行的。

诉求

恢复程序BUG导致的错误更新is_deleted字段的值。

解析binlog

根据此前的理论知识，binlog记录每一行数据的变化。那么我们可以通过解析binlog，拿到这条SQL所影响的数据，它记录修改前、后的数据。

获取binlog文件：

如果是云数据，看不到最新的日志，需要执行：flush logs

执行binlog解析命令：

因为明确知道SQL的执行时间，我这里加了--start-datetime参数，减少数据量。2025-09-05 14:48左右执行的，我这里往前推了几分钟。因为时区问题，减去8小时。

mysqlbinlog --no-defaults -v -v --base64-output=DECODE-ROWS --start-datetime="2025-09-05 06:40:00" mysqlbin.000391 > binlog.out

查找关键字

UPDATE `test`.`t1`

# at 273068250
#250905  6:48:12 server id 414163  end_log_pos 273068548 CRC32 0x06b6ee0e     Update_rows: table id 5546 flags: STMT_END_F
### UPDATE `test`.`t1`
### WHERE
###   @1=5816 /* LONGINT meta=0 nullable=0 is_null=0 */
###   @2=4956 /* LONGINT meta=0 nullable=1 is_null=0 */
###   @3=0 /* LONGINT meta=0 nullable=1 is_null=0 */
###   @4=0 /* LONGINT meta=0 nullable=1 is_null=0 */
###   @5='风管机' /* VARSTRING(1020) meta=1020 nullable=0 is_null=0 */
###   @6='FGR3.5Pd/C3(S)Nha-N3' /* VARSTRING(1020) meta=1020 nullable=0 is_null=0 */
###   @7=2.0000 /* DECIMAL(12,4) meta=3076 nullable=1 is_null=0 */
###   @8='套' /* VARSTRING(400) meta=400 nullable=1 is_null=0 */
###   @9=NULL /* BLOB/TEXT meta=2 nullable=1 is_null=1 */
###   @10=2 /* INT meta=0 nullable=0 is_null=0 */
###   @11=NULL /* VARSTRING(128) meta=128 nullable=1 is_null=1 */
###   @12=NULL /* VARSTRING(400) meta=400 nullable=1 is_null=1 */
###   @13=0 /* TINYINT meta=0 nullable=1 is_null=0 */
###   @14='2023-04-14 16:18:08' /* DATETIME(0) meta=0 nullable=1 is_null=0 */
###   @15='2023-04-14 16:18:08' /* DATETIME(0) meta=0 nullable=1 is_null=0 */
###   @16=19 /* LONGINT meta=0 nullable=1 is_null=0 */
###   @17=19 /* LONGINT meta=0 nullable=1 is_null=0 */
###   @18='' /* VARSTRING(1020) meta=1020 nullable=1 is_null=0 */
### SET
###   @1=5816 /* LONGINT meta=0 nullable=0 is_null=0 */
###   @2=4956 /* LONGINT meta=0 nullable=1 is_null=0 */
###   @3=0 /* LONGINT meta=0 nullable=1 is_null=0 */
###   @4=0 /* LONGINT meta=0 nullable=1 is_null=0 */
###   @5='风管机' /* VARSTRING(1020) meta=1020 nullable=0 is_null=0 */
###   @6='FGR3.5Pd/C3(S)Nha-N3' /* VARSTRING(1020) meta=1020 nullable=0 is_null=0 */
###   @7=2.0000 /* DECIMAL(12,4) meta=3076 nullable=1 is_null=0 */
###   @8='套' /* VARSTRING(400) meta=400 nullable=1 is_null=0 */
###   @9=NULL /* BLOB/TEXT meta=2 nullable=1 is_null=1 */
###   @10=2 /* INT meta=0 nullable=0 is_null=0 */
###   @11=NULL /* VARSTRING(128) meta=128 nullable=1 is_null=1 */
###   @12=NULL /* VARSTRING(400) meta=400 nullable=1 is_null=1 */
###   @13=1 /* TINYINT meta=0 nullable=1 is_null=0 */
###   @14='2023-04-14 16:18:08' /* DATETIME(0) meta=0 nullable=1 is_null=0 */
###   @15='2023-04-14 16:18:08' /* DATETIME(0) meta=0 nullable=1 is_null=0 */
###   @16=19 /* LONGINT meta=0 nullable=1 is_null=0 */
###   @17=19 /* LONGINT meta=0 nullable=1 is_null=0 */
###   @18='' /* VARSTRING(1020) meta=1020 nullable=1 is_null=0 */
### UPDATE `test`.`t1`
### WHERE
###   @1=5818 /* LONGINT meta=0 nullable=0 is_null=0 */
###   @2=11309 /* LONGINT meta=0 nullable=1 is_null=0 */
###   @3=0 /* LONGINT meta=0 nullable=1 is_null=0 */
###   @4=0 /* LONGINT meta=0 nullable=1 is_null=0 */
###   @5='风管机' /* VARSTRING(1020) meta=1020 nullable=0 is_null=0 */
###   @6='FGR3.5Pd/C3(S)Nha-N3' /* VARSTRING(1020) meta=1020 nullable=0 is_null=0 */
###   @7=1.0000 /* DECIMAL(12,4) meta=3076 nullable=1 is_null=0 */
###   @8='套' /* VARSTRING(400) meta=400 nullable=1 is_null=0 */
###   @9=NULL /* BLOB/TEXT meta=2 nullable=1 is_null=1 */
###   @10=2 /* INT meta=0 nullable=0 is_null=0 */
###   @11=NULL /* VARSTRING(128) meta=128 nullable=1 is_null=1 */
###   @12=NULL /* VARSTRING(400) meta=400 nullable=1 is_null=1 */
###   @13=0 /* TINYINT meta=0 nullable=1 is_null=0 */
###   @14='2023-04-14 16:31:43' /* DATETIME(0) meta=0 nullable=1 is_null=0 */
###   @15='2025-05-29 22:12:25' /* DATETIME(0) meta=0 nullable=1 is_null=0 */
###   @16=493 /* LONGINT meta=0 nullable=1 is_null=0 */
###   @17=16 /* LONGINT meta=0 nullable=1 is_null=0 */
###   @18='' /* VARSTRING(1020) meta=1020 nullable=1 is_null=0 */
### SET
###   @1=5818 /* LONGINT meta=0 nullable=0 is_null=0 */
###   @2=11309 /* LONGINT meta=0 nullable=1 is_null=0 */
###   @3=0 /* LONGINT meta=0 nullable=1 is_null=0 */
###   @4=0 /* LONGINT meta=0 nullable=1 is_null=0 */
###   @5='风管机' /* VARSTRING(1020) meta=1020 nullable=0 is_null=0 */
###   @6='FGR3.5Pd/C3(S)Nha-N3' /* VARSTRING(1020) meta=1020 nullable=0 is_null=0 */
###   @7=1.0000 /* DECIMAL(12,4) meta=3076 nullable=1 is_null=0 */
###   @8='套' /* VARSTRING(400) meta=400 nullable=1 is_null=0 */
###   @9=NULL /* BLOB/TEXT meta=2 nullable=1 is_null=1 */
###   @10=2 /* INT meta=0 nullable=0 is_null=0 */
###   @11=NULL /* VARSTRING(128) meta=128 nullable=1 is_null=1 */
###   @12=NULL /* VARSTRING(400) meta=400 nullable=1 is_null=1 */
###   @13=1 /* TINYINT meta=0 nullable=1 is_null=0 */
###   @14='2023-04-14 16:31:43' /* DATETIME(0) meta=0 nullable=1 is_null=0 */
###   @15='2025-05-29 22:12:25' /* DATETIME(0) meta=0 nullable=1 is_null=0 */
###   @16=493 /* LONGINT meta=0 nullable=1 is_null=0 */
###   @17=16 /* LONGINT meta=0 nullable=1 is_null=0 */
###   @18='' /* VARSTRING(1020) meta=1020 nullable=1 is_null=0 */
……
# at 273068548
#250905  6:48:12 server id 414163  end_log_pos 273068579 CRC32 0xce483344     Xid = 494745070
COMMIT/*!*/;

先看一下binlog解析后输出的内容格式：

# at 273068250
#250905  6:48:12 server id 414163  end_log_pos 273068548 CRC32 0x06b6ee0e     Update_rows: table id 5546 flags: STMT_END_F
### UPDATE `test`.`t1`
### WHERE
    （旧值，用来定位被更新的行）
### SET
    （新值，表示更新后的结果）
……（这个事件修改的其他行的内容）
# at 273068548
#250905  6:48:12 server id 414163  end_log_pos 273068579 CRC32 0xce483344     Xid = 494745070
COMMIT/*!*/;

稍做分析

# at 273068250表示这条 binlog 事件的起始位置（offset = 273068250）。
这个表有18个字段，字段的位置以@序号的表示方式。

我们还需要结合这个表的表结构的字段名和binlog的@序号对应起来。

CREATE TABLE `test` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键',                        -- @1
  `project_id` bigint(20) DEFAULT NULL COMMENT '项目id',                         -- @2
  `oppty_info_id` bigint(20) DEFAULT NULL COMMENT '商机id',                      -- @3
  `oppty_info_palmtop_id` bigint(20) DEFAULT NULL COMMENT '掌上通id',
  `device_name` varchar(255) NOT NULL DEFAULT '' COMMENT '分类名称',
  `device_type` varchar(255) NOT NULL DEFAULT '' COMMENT '设备型号',
  `price` decimal(12,4) DEFAULT NULL COMMENT '单价(万)',
  `unit` varchar(100) DEFAULT '' COMMENT '单位',
  `unit_barcode` text COMMENT '机组条码List,[xxx,xxx]',
  `qty` int(11) NOT NULL DEFAULT '0' COMMENT '数量',
  `drawing_no` varchar(32) DEFAULT NULL COMMENT '图号',
  `tool_no` varchar(100) DEFAULT NULL COMMENT '工装编号',
  `is_deleted` tinyint(1) DEFAULT '0' COMMENT '删除状态：1-删除 0-未删除',         -- @13
  `created_time` datetime DEFAULT NULL COMMENT '创建时间',
  `updated_time` datetime DEFAULT NULL COMMENT '更新时间',
  `created_by` bigint(20) DEFAULT NULL COMMENT '创建人',
  `updated_by` bigint(20) DEFAULT NULL COMMENT '更新人',
  `remark` varchar(255) DEFAULT '' COMMENT '备注',                               -- @18
  PRIMARY KEY (`id`),
  KEY `idx_oppty_info_id_deleted` (`oppty_info_id`,`is_deleted`)
) ENGINE=InnoDB AUTO_INCREMENT=146778 DEFAULT CHARSET=utf8mb4 COMMENT='设备清单'

提取这条SQL的完整binlog内容：

sed -n '/# at 273068250/,/COMMIT/p' binlog.out > 273068250.out

组装恢复脚本

cat 273068250.out | \
  sed '/WHERE/{:a;N;/SET/!ba;s/\([^\n]*\)\n\(.*\)\n\(.*\)/\3\n\2\n\1/}' | \
  sed -r '/SET/{:a;N;/@18/!ba;s/@14.*//g}' | \
  sed -r '/SET/{:a;N;/@12/!ba;s/@1.*//g}' | \
  sed -r '/WHERE/{:a;N;/@18/!ba;s/@2.*//g}' | \
  sed 's/### //g;s/\/\*.*\*\//,/g' | \
  sed '/WHERE/{:a;N;/@1/!ba;s/,/;/g};s/#.*//g;s/COMMIT.*//g' | \
  sed '/^$/d' | \
  sed 's/@13/is_deleted/g;s/@1/id/g' | \
  sed -r 's/(is_deleted=.*),/\1/g' > 273068250.recovery.sql

逐行解释一下：

sed '/WHERE/{:a;N;/SET/!ba;s/$[^\n]*$\n$.*$\n$.*$/\3\n\2\n\1/}'

/WHERE/{ ... }
只有匹配到包含 WHERE 的行时，才会执行 {} 里的动作。
:a; N; /SET/!ba;
这几条是一个循环结构：
:a → 定义一个标签 a。
N → 把下一行读进来（追加到 pattern space）。
/SET/!ba → 如果当前缓存区里还没有匹配到 SET，就跳回到 a 标签继续读下一行。

作用：一直读，直到读到包含 SET 的那一行，才停止。
这样就能保证 pattern space 中存放的是：

WHERE
...
SET

s/$[^\n]*$\n$.*$\n$.*$/\3\n\2\n\1/
这是一个替换操作，里面有三个分组：
([^\n]*) → 第一行（匹配到换行符前的内容）。
(.*) → 第二行到倒数第二行。
(.*) → 最后一行。

综合起来看，整个命令做的事情就是：

找到 WHERE 那一行开始。
一直往下读，直到 SET 那一行。
然后把这WHERE和SET调换位置。

举个例子

假设输入：

### WHERE
###   @1=123
###   @2=456
### SET
### ...

经过这个 sed 命令处理后，会变成：

### SET
###   @1=123
###   @2=456
### WHERE
### ...

sed -r '/SET/{:a;N;/@18/!ba;s/@14.*//g}'
sed -r '/SET/{:a;N;/@12/!ba;s/@1.*//g}'

这两行一起看，先看第一行。

-r
打开扩展正则（ERE），写法上可以少一些反斜杠。
/SET/{ ... }
只有匹配到包含 SET 的那一行时，才会执行 {} 里的动作。
:a; N; /@18/!ba;
这是一个典型的 sed 循环：
:a → 定义标签 a。
N → 把下一行读入到 pattern space（追加）。
/@18/!ba → 如果没有读到包含 @18 的行，就跳回 a 继续读。
作用：把从 SET 开始到 @18 行为止的整个多行内容都读进来。
s/@14.*//g
在当前累积的 pattern space 中，匹配从@14开始，后面跟任意字符 .*，并替换为空。
作用：删除@14 那行到@18那行的内容。

第二行同样的道理，作用是删除@1那行到@12那行的内容。

假设输入：

### SET
###   @1=5816
###   @2=4956
###   @3=0
###   @4=0
###   @5='风管机'
###   @6='FGR3.5Pd/C3(S)Nha-N3'
###   @7=2.0000
###   @8='套'
###   @9=NULL
###   @10=2
###   @11=NULL
###   @12=NULL
###   @13=0
###   @14='2023-04-14 16:18:08'
###   @15='2023-04-14 16:18:08'
###   @16=19
###   @17=19
###   @18=''
### WHERE
### ...

经过这个 sed 命令处理后，会变成：

### SET
###   @13=0
### WHERE
### ...

sed -r '/WHERE/{:a;N;/@18/!ba;s/@2.*//g}'

作用：删除WHERE后面的@2到@18的行，因为WHERE条件只需要id字段，即@1。

sed 's/### //g;s/\/\*.*\*\//,/g'

作用：### 替换为空，/* ... */替换成逗号。

sed '/WHERE/{:a;N;/@1/!ba;s/,/;/g};s/#.*//g;s/COMMIT.*//g'

作用：

@1那一行的,替换成;
删除有#号的行，删除COMMIT那一行

sed '/^$/d'

作用：删除空白行

sed 's/@13/is_deleted/g;s/@1/id/g'

作用：把@13替换成is_deleted，把@1替换成id

sed -r 's/(is_deleted=.*),/\1/g'

作用：删除is_deleted那一行后面的,

命令组合后的整体效果：

假设输入：

# at 273068250
#250905  6:48:12 server id 414163  end_log_pos 273068548 CRC32 0x06b6ee0e     Update_rows: table id 5546 flags: STMT_END_F
### UPDATE `test`.`t1`
### WHERE
###   @1=5816 /* LONGINT meta=0 nullable=0 is_null=0 */
###   @2=4956 /* LONGINT meta=0 nullable=1 is_null=0 */
###   @3=0 /* LONGINT meta=0 nullable=1 is_null=0 */
###   @4=0 /* LONGINT meta=0 nullable=1 is_null=0 */
###   @5='风管机' /* VARSTRING(1020) meta=1020 nullable=0 is_null=0 */
###   @6='FGR3.5Pd/C3(S)Nha-N3' /* VARSTRING(1020) meta=1020 nullable=0 is_null=0 */
###   @7=2.0000 /* DECIMAL(12,4) meta=3076 nullable=1 is_null=0 */
###   @8='套' /* VARSTRING(400) meta=400 nullable=1 is_null=0 */
###   @9=NULL /* BLOB/TEXT meta=2 nullable=1 is_null=1 */
###   @10=2 /* INT meta=0 nullable=0 is_null=0 */
###   @11=NULL /* VARSTRING(128) meta=128 nullable=1 is_null=1 */
###   @12=NULL /* VARSTRING(400) meta=400 nullable=1 is_null=1 */
###   @13=0 /* TINYINT meta=0 nullable=1 is_null=0 */
###   @14='2023-04-14 16:18:08' /* DATETIME(0) meta=0 nullable=1 is_null=0 */
###   @15='2023-04-14 16:18:08' /* DATETIME(0) meta=0 nullable=1 is_null=0 */
###   @16=19 /* LONGINT meta=0 nullable=1 is_null=0 */
###   @17=19 /* LONGINT meta=0 nullable=1 is_null=0 */
###   @18='' /* VARSTRING(1020) meta=1020 nullable=1 is_null=0 */
### SET
###   @1=5816 /* LONGINT meta=0 nullable=0 is_null=0 */
###   @2=4956 /* LONGINT meta=0 nullable=1 is_null=0 */
###   @3=0 /* LONGINT meta=0 nullable=1 is_null=0 */
###   @4=0 /* LONGINT meta=0 nullable=1 is_null=0 */
###   @5='风管机' /* VARSTRING(1020) meta=1020 nullable=0 is_null=0 */
###   @6='FGR3.5Pd/C3(S)Nha-N3' /* VARSTRING(1020) meta=1020 nullable=0 is_null=0 */
###   @7=2.0000 /* DECIMAL(12,4) meta=3076 nullable=1 is_null=0 */
###   @8='套' /* VARSTRING(400) meta=400 nullable=1 is_null=0 */
###   @9=NULL /* BLOB/TEXT meta=2 nullable=1 is_null=1 */
###   @10=2 /* INT meta=0 nullable=0 is_null=0 */
###   @11=NULL /* VARSTRING(128) meta=128 nullable=1 is_null=1 */
###   @12=NULL /* VARSTRING(400) meta=400 nullable=1 is_null=1 */
###   @13=1 /* TINYINT meta=0 nullable=1 is_null=0 */
###   @14='2023-04-14 16:18:08' /* DATETIME(0) meta=0 nullable=1 is_null=0 */
###   @15='2023-04-14 16:18:08' /* DATETIME(0) meta=0 nullable=1 is_null=0 */
###   @16=19 /* LONGINT meta=0 nullable=1 is_null=0 */
###   @17=19 /* LONGINT meta=0 nullable=1 is_null=0 */
###   @18='' /* VARSTRING(1020) meta=1020 nullable=1 is_null=0 */
# at 273068548
#250905  6:48:12 server id 414163  end_log_pos 273068579 CRC32 0xce483344     Xid = 494745070
COMMIT/*!*/;

处理后输出：

UPDATE `test`.`t1`
SET
  
  is_deleted=0 
  
WHERE
  id=5816 ;

我这个案例中，我只需要恢复一个字段（is_deleted），如果你要恢复多个字段，需要改造一下命令。

实战场景二：delete语句不加where条件误操作数据恢复

事件描述

误操作导致一张表的多条数据被物理删除，该操作是由于执行了一条DELETE语句不加WHERE条件，或WHERE条件有误引起。

DELETE FROM `test`.`t1`;

诉求

恢复误操作删除的数据。

解析binlog

mysqlbinlog --no-defaults -v -v --base64-output=DECODE-ROWS binlog.000029 > binlog.out

查找关键字

DELETE FROM `test`.`t1`

# at 39574418
#250909  5:32:07 server id 1  end_log_pos 39581451 CRC32 0x239d3070     Delete_rows: table id 102 flags: STMT_END_F
### DELETE FROM `test`.`t1`
### WHERE
###   @1=5816 /* LONGINT meta=0 nullable=0 is_null=0 */
###   @2=4956 /* LONGINT meta=0 nullable=1 is_null=0 */
###   @3=0 /* LONGINT meta=0 nullable=1 is_null=0 */
###   @4=0 /* LONGINT meta=0 nullable=1 is_null=0 */
###   @5='风管机' /* VARSTRING(1020) meta=1020 nullable=0 is_null=0 */
###   @6='FGR3.5Pd/C3(S)Nha-N3' /* VARSTRING(1020) meta=1020 nullable=0 is_null=0 */
###   @7=2.0000 /* DECIMAL(12,4) meta=3076 nullable=1 is_null=0 */
###   @8='套' /* VARSTRING(400) meta=400 nullable=1 is_null=0 */
###   @9=NULL /* BLOB/TEXT meta=2 nullable=1 is_null=1 */
###   @10=2 /* INT meta=0 nullable=0 is_null=0 */
###   @11=NULL /* VARSTRING(128) meta=128 nullable=1 is_null=1 */
###   @12=NULL /* VARSTRING(400) meta=400 nullable=1 is_null=1 */
###   @13=0 /* TINYINT meta=0 nullable=1 is_null=0 */
###   @14='2023-04-14 16:18:08' /* DATETIME(0) meta=0 nullable=1 is_null=0 */
###   @15='2023-04-14 16:18:08' /* DATETIME(0) meta=0 nullable=1 is_null=0 */
###   @16=19 /* LONGINT meta=0 nullable=1 is_null=0 */
###   @17=19 /* LONGINT meta=0 nullable=1 is_null=0 */
###   @18='' /* VARSTRING(1020) meta=1020 nullable=1 is_null=0 */
### DELETE FROM `test`.`t1`
### WHERE
###   @1=5818 /* LONGINT meta=0 nullable=0 is_null=0 */
###   @2=11309 /* LONGINT meta=0 nullable=1 is_null=0 */
###   @3=0 /* LONGINT meta=0 nullable=1 is_null=0 */
###   @4=0 /* LONGINT meta=0 nullable=1 is_null=0 */
###   @5='风管机' /* VARSTRING(1020) meta=1020 nullable=0 is_null=0 */
###   @6='FGR3.5Pd/C3(S)Nha-N3' /* VARSTRING(1020) meta=1020 nullable=0 is_null=0 */
###   @7=1.0000 /* DECIMAL(12,4) meta=3076 nullable=1 is_null=0 */
###   @8='套' /* VARSTRING(400) meta=400 nullable=1 is_null=0 */
###   @9=NULL /* BLOB/TEXT meta=2 nullable=1 is_null=1 */
###   @10=2 /* INT meta=0 nullable=0 is_null=0 */
###   @11=NULL /* VARSTRING(128) meta=128 nullable=1 is_null=1 */
###   @12=NULL /* VARSTRING(400) meta=400 nullable=1 is_null=1 */
###   @13=0 /* TINYINT meta=0 nullable=1 is_null=0 */
###   @14='2023-04-14 16:31:43' /* DATETIME(0) meta=0 nullable=1 is_null=0 */
###   @15='2025-05-29 22:12:25' /* DATETIME(0) meta=0 nullable=1 is_null=0 */
###   @16=493 /* LONGINT meta=0 nullable=1 is_null=0 */
###   @17=16 /* LONGINT meta=0 nullable=1 is_null=0 */
###   @18='' /* VARSTRING(1020) meta=1020 nullable=1 is_null=0 */
……
# at 39581451
#250909  5:32:07 server id 1  end_log_pos 39581482 CRC32 0x7d7f2b3a     Xid = 96546
COMMIT/*!*/;

稍做分析

# at 39574418表示这条 binlog 事件的起始位置（offset = 39574418）。
这个表有18个字段，字段的位置以@序号的表示方式。

CREATE TABLE `t1` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键',                        -- @1
  `project_id` bigint(20) DEFAULT NULL COMMENT '项目id',                         -- @2
  `oppty_info_id` bigint(20) DEFAULT NULL COMMENT '商机id',                      -- @3
  `oppty_info_palmtop_id` bigint(20) DEFAULT NULL COMMENT '掌上通id',
  `device_name` varchar(255) NOT NULL DEFAULT '' COMMENT '分类名称',
  `device_type` varchar(255) NOT NULL DEFAULT '' COMMENT '设备型号',
  `price` decimal(12,4) DEFAULT NULL COMMENT '单价(万)',
  `unit` varchar(100) DEFAULT '' COMMENT '单位',
  `unit_barcode` text COMMENT '机组条码List,[xxx,xxx]',
  `qty` int(11) NOT NULL DEFAULT '0' COMMENT '数量',
  `drawing_no` varchar(32) DEFAULT NULL COMMENT '图号',
  `tool_no` varchar(100) DEFAULT NULL COMMENT '工装编号',
  `is_deleted` tinyint(1) DEFAULT '0' COMMENT '删除状态：1-删除 0-未删除',
  `created_time` datetime DEFAULT NULL COMMENT '创建时间',
  `updated_time` datetime DEFAULT NULL COMMENT '更新时间',
  `created_by` bigint(20) DEFAULT NULL COMMENT '创建人',
  `updated_by` bigint(20) DEFAULT NULL COMMENT '更新人',
  `remark` varchar(255) DEFAULT '' COMMENT '备注',                               -- @18
  PRIMARY KEY (`id`),
  KEY `idx_oppty_info_id_deleted` (`oppty_info_id`,`is_deleted`)
) ENGINE=InnoDB AUTO_INCREMENT=146778 DEFAULT CHARSET=utf8mb4 COMMENT='设备清单'

提取这条SQL的完整binlog内容：

sed -n '/# at 39574418/,/COMMIT/p' binlog.out > 39574418.out

组装恢复脚本

cat 39574418.out | \
  sed -n '/###/p' | \
  sed 's/### //g;s/\/\*.*\*\//,/g;s/DELETE FROM/INSERT INTO/g;s/WHERE/SELECT/g' | \
  sed -r 's/(@18.*),/\1;/g' | \
  sed -r 's/@[0-9]+=//g' > 39574418.recovery.sql

这个sed还算容易理解，经过处理后的内容如下：

INSERT INTO `test`.`t1`
SELECT
  5816 ,
  4956 ,
  0 ,
  0 ,
  '风管机' ,
  'FGR3.5Pd/C3(S)Nha-N3' ,
  2.0000 ,
  '套' ,
  NULL ,
  2 ,
  NULL ,
  NULL ,
  0 ,
  '2023-04-14 16:18:08' ,
  '2023-04-14 16:18:08' ,
  19 ,
  19 ,
  '' ;
INSERT INTO `test`.`t1`
SELECT
  5818 ,
  11309 ,
  0 ,
  0 ,
  '风管机' ,
  'FGR3.5Pd/C3(S)Nha-N3' ,
  1.0000 ,
  '套' ,
  NULL ,
  2 ,
  NULL ,
  NULL ,
  0 ,
  '2023-04-14 16:31:43' ,
  '2025-05-29 22:12:25' ,
  493 ,
  16 ,
  '' ;
……

觉得太复杂了？来点简单的

上面的操作太复杂了，有没有易用的工具呢？

在github上找到这两个项目，可以大大简化操作。

binlog2sql

binlog2sql

使用Python写的项目，多年没有更新了，可能对MySQL 8.0支持有限。

binlog2sql_go

binlog2sql_go

使用Go写的项目，此项目使用的人不多，可能有BUG，需要你自行测试功能。

使用限制

必须连接MySQL Server，因为字段名是从Server上获取的。解析本地binlog，也是需要连接数据库的。
因为字段名是从Server上获取的，所以如果表结构有变动的话（特别是新增列和删除列），会导致生成的SQL不对或报错。

MySQL误删数据不慌，还有的救

binlog基础

通过binlog恢复数据理论基础

实战场景一：update语句不加where条件误操作数据恢复

实战场景二：delete语句不加where条件误操作数据恢复

觉得太复杂了？来点简单的

添加新评论

最新文章

最近回复

分类

归档

其它