必须先确认log_bin=ON且binlog_format=ROW,否则无法精准还原单行变更;执行SHOW VARIABLES LIKE 'log_bin'和SHOW VARIABLES LIKE 'binlog_format'验证,不满足则需修改my.cnf并重启。

确认 binlog 是否开启且格式为 ROW
MySQL 误删单表后想靠 binlog 恢复,第一关是看有没有可用的 binlog。不是所有实例都默认开启,也不是开了就一定能用。
执行 SHOW VARIABLES LIKE 'log_bin';,返回 ON 才说明开启;再查 SHOW VARIABLES LIKE 'binlog_format';,必须是 ROW —— 如果是 STATEMENT 或 MIXED,DELETE 语句可能只记录逻辑 SQL,无法还原被删的具体行数据。
-
log_bin = OFF:彻底没戏,只能从最近一次全量备份 + 应用增量备份(如 xtrabackup 的增量)恢复,无法精确到时间点 -
binlog_format = STATEMENT:DELETE FROM t WHERE id > 100这类语句在 binlog 里就是原样记录,你不知道实际删了哪几行,无法反向构造 INSERT - 即使格式是 ROW,也要确认
binlog_row_image = FULL(5.6.2+ 默认),否则可能缺失前镜像(before image),导致无法识别“删的是什么”
定位误删操作在 binlog 中的位置和时间戳
别急着解析整个 binlog 文件,先缩小范围。你需要两个关键信息:误删操作发生的大致时间、以及它所在的 binlog 文件名。
查当前 binlog 列表:SHOW BINARY LOGS;,结合系统时间(date 命令或应用日志中的报错时间)判断哪个文件包含误操作。然后用 mysqlbinlog 解析:
mysqlbinlog --base64-output=DECODE-ROWS -v --start-datetime="2024-04-10 14:22:00" --stop-datetime="2024-04-10 14:23:00" /var/lib/mysql/mysql-bin.000012
重点找 DELETE_ROWS_EVENT,并核对 @1=xxx @2=yyy 字段是否匹配你那张表的字段顺序和值——这是确认删的是不是目标表的唯一依据。
- 别依赖
--database参数过滤,ROW 格式下 event 不带库名,跨库操作时容易漏掉 - 如果误删是通过存储过程或事件触发的,binlog 里可能只有 CALL 语句,看不到具体 DELETE,得往前翻调用链
- 时间范围宁宽勿窄,但太宽会导致输出巨大;建议先用
--base64-output=DECODE-ROWS -v看结构,再加--verbose查字段值
提取并反转 DELETE_ROWS_EVENT 生成 INSERT 语句
ROW 格式 binlog 记录的是“删除前的完整行”,所以能反向还原。但 mysqlbinlog 默认输出的是人类可读的注释格式(含 # at xxx 和 ### DELETE FROM...),不能直接执行。
安装、修复、更新或打包一个基于 Python 和 PyMySQL 实现的 Windows `mysql` 命令。适用于用户希望在 PowerShell/CMD 中输入 `mysql` 时重定向到 Python 实现、需要交互式输入 MySQL 连接信息、需要同步到当前用户本地 bin 目录,或需要将该能力封装成可公开复用的 Codex skill。
正确做法是:用 mysqlbinlog --base64-output=DECODE-ROWS -v 输出后,手动提取 ### INSERT INTO `db`.`t` 开头的逆向语句(注意是 INSERT,不是 DELETE),或者用脚本清洗。示例片段:
### DELETE FROM `test`.`user` ### WHERE ### @1=5 ### @2='alice' ### @3='alice@example.com'
对应应生成:
INSERT INTO `test`.`user` VALUES (5, 'alice', 'alice@example.com');
- 字段顺序必须严格对应表定义(
SHOW CREATE TABLE t确认),尤其是有自增、TIMESTAMP DEFAULT CURRENT_TIMESTAMP 的列,别漏掉或错位 - 如果表有外键或触发器,恢复前建议临时禁用:
SET FOREIGN_KEY_CHECKS=0;、SET SQL_LOG_BIN=0;(后者防止恢复操作又被写入 binlog) - 不要用
mysqlbinlog --flashback(非官方补丁),生产环境不推荐;也别信“自动解析工具”,手工核对每一行更稳妥
恢复时跳过误删事务,避免二次污染
你真正要的不是“把删掉的数据插回去”,而是让数据库状态回到误删之前——这意味着要跳过那个 DELETE 事务本身,而不是在它之后补数据。否则并发写入可能导致主键冲突、重复记录或违反业务约束。
标准做法是:找到误删事务的起始 position(# at 12345),用 mysqlbinlog 截取从上一个事务末尾到该 position 之间的内容,导入备用库验证无误后,再在生产库上按需重放:
mysqlbinlog --stop-position=12345 /var/lib/mysql/mysql-bin.000012 | mysql -u root -p
注意:这个 position 是“误删事务开始前”的位置,不是 DELETE event 的位置。
- 用
--start-position和--stop-position比时间更精准,因为同一秒内可能有多个事务 - 恢复前务必在隔离环境(如从库或测试实例)回放一遍,检查数据一致性,尤其关注时间戳、自增 ID、JSON 字段等易出问题的类型
- 如果误删后还有其他合法写入,不能简单截断到误删前,得把误删事务跳过,再把后续合法事务重放——这需要人工拆分 binlog 流,复杂度陡增
ROW 格式 + 完整 binlog 是精确恢复的前提,但真正难的不是技术动作,是快速准确定位事务边界、识别哪些 event 属于同一个逻辑操作、以及处理好外键/约束/并发写入带来的副作用。线上恢复永远比本地测试多三层不确定性。

















