mysql误删数据快速恢复

MySQL数据恢复技巧

最新推荐文章于 2025-08-03 04:45:00 发布

风澍

最新推荐文章于 2025-08-03 04:45:00 发布

阅读量5.2k

点赞数

分类专栏： mysql

mysql 专栏收录该内容

14 篇文章

订阅专栏

本文详细介绍如何使用MySQL的binlog功能进行数据恢复，包括binlog的三种格式及其配置，以及使用binlog2sql和mysqlbinlog工具的具体步骤。适用于后端研发人员在遇到产品需求临时修改线上数据时，不慎误删或修改数据后的快速恢复。

原文：https://www.jianshu.com/p/c9a2fe3f4534

相信后端研发的同学在开发过程经常会遇到产品临时修改线上数据的需求，如果手法很稳那么很庆幸可以很快完成任务，很不幸某一天突然手一抖把表里的数据修改错误或者误删了，这个时候你会发现各种问题反馈接踵而来。如果身边有BDA或者有这方面经验的同事那么可以很快解决这个问题，如果没有那么希望这篇文章可以帮到你。

binglog介绍

首先第一步保证mysql已经开启binlog，查看命令：

show variables like '%log_bin%'

mysql binlog分三种格式 :

Statement : 会在binlog中记录每一条执行修改数据的sql语句的相关信息，优点是不需要记录每一行的变化，减少了binlog日志量，节约了IO
Row : 会在binlog中记录每一修改语句的详细信息，包括数据在修改之前和修改之后的数据的具体信息，好处是会清晰记录每一条修改的详细信息，不好的地方是会产生大量日志
Mixed ：这种格式实际上就是Statement和Row的结合体，如果遇到表结构变更就会以Statement来记录，如果涉及语句修改那么就以Row格式记录

这里的binlog格式推荐row，my.cnf 的配置可参考 :

server_id = 1001
log_bin = /var/log/mysql/mysql-bin.log
max_binlog_size = 1G
binlog_format = row
binlog_row_image = full

我们来模拟一些数据:

CREATE TABLE `user` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(125) NOT NULL DEFAULT '' COMMENT '名称',
  `age` tinyint(3) unsigned NOT NULL DEFAULT '0' COMMENT '年龄',
  `sex` tinyint(3) unsigned NOT NULL DEFAULT '0' COMMENT '性别',
  `deleted` tinyint(4) unsigned DEFAULT '0',
  `created` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户表测试';

INSERT INTO `user` (`id`, `name`, `age`, `sex`, `deleted`, `created`)
VALUES
    (1, '小王', 21, 1),
    (2, '小张', 22, 1),
    (3, '小红', 22, 0),
    (4, '小楠', 23, 0),
    (5, '小柱', 25, 1);

然后我们把数据全部删掉

delete from `user`

数据恢复方法一 :

使用开源框架binlog2sql : https://github.com/danfengcao/binlog2sql
好处是成熟，稳定，上手难度比较低且可直接生成可执行sql，示例：

python /binlog2sql/binlog2sql.py --flashback -h127.0.0.1 -P3306 -uroot -p'123456' -dlocal -tuser --start-file='mysql-bin.000038' --sql-type=DELETE --start-datetime='2017-12-17 19:39:33' --stop-datetime='2017-12-17 19:40:01' >/**/data6.sql

解析后的结果大概是这样

INSERT INTO `local`.`user`(`name`, `created`, `deleted`, `age`, `sex`, `id`) VALUES ('小柱', '2017-12-18 13:21:52', 0, 25, 1, 5); #start 1890 end 2244 time 2017-12-19 09:20:26
INSERT INTO `local`.`user`(`name`, `created`, `deleted`, `age`, `sex`, `id`) VALUES ('小楠', '2017-12-18 13:21:52', 0, 23, 0, 4); #start 1890 end 2244 time 2017-12-19 09:20:26
INSERT INTO `local`.`user`(`name`, `created`, `deleted`, `age`, `sex`, `id`) VALUES ('小红', '2017-12-18 13:21:52', 0, 22, 0, 3); #start 1890 end 2244 time 2017-12-19 09:20:26
INSERT INTO `local`.`user`(`name`, `created`, `deleted`, `age`, `sex`, `id`) VALUES ('小张', '2017-12-18 13:21:52', 0, 28, 1, 2); #start 1890 end 2244 time 2017-12-19 09:20:26
INSERT INTO `local`.`user`(`name`, `created`, `deleted`, `age`, `sex`, `id`) VALUES ('小王', '2017-12-18 13:21:52', 0, 21, 1, 1); #start 1890 end 2244 time 2017-12-19 09:20:26

参数--sql-type建议加上，因为可能会有其他类型语句生成干扰了执行结果
如果是线上阿里云或者其他产品建议先去管理后台找到事发时间的binlog日志下载下来，先在测试环境验证数据回滚结果.

数据恢复方法二:

当线上数据出现错误的时候首先可以询问具体操作人记录时间点，这个时候可以借助mysql自带的binlog解析工具mysqlbinlog，具体位置在mysql安装目录**/mysql/bin/下，示例：

mysqlbinlog --base64-output=decode-rows -v --start-datetime="2017-12-15 17:48:49" --stop-datetime="2017-12-16 23:59:49" /usr/local/mysql/mysql-bin.000038 >/**/data.sql

如果是阿里云rds或者其他产品可通过远程方式解析

mysqlbinlog --no-defaults -u账号 -p密码 -h ***.rds.aliyuncs.com --read-from-remote-server mysql-bin.000180 --base64-output=decode-rows -v > /data.sql

这里因为binlog文件默认是通过base64编码过的，所以需要加上--base64-output=decode-rows -v
解析后的格式大概是这样的 :

### DELETE FROM `local`.`user`
### WHERE
###   @1=1
###   @2='小王'
###   @3=21
###   @4=1
###   @5=0
###   @6='2017-12-18 13:21:52'
### DELETE FROM `local`.`user`
### WHERE
###   @1=2
###   @2='小张'
###   @3=28
###   @4=1
###   @5=0
###   @6='2017-12-18 13:21:52'
....

仔细查看这种格式文件，发现这种格式文件并不能直接执行，但是在where条件后面记录了被删除之前的原始数据，需要借助sed、awk把SQL文本转换成真正的SQL。或者当你在遇到开源框架解决不了的情况下，可以根据具体场景尝试手动把这种格式的文件解析成可执行的sql语句。

作者：qtshe
链接：https://www.jianshu.com/p/c9a2fe3f4534
來源：简书
简书著作权归作者所有，任何形式的转载都请联系作者获得授权并注明出处。