MySQL数据误删或者误更新如何恢复(详细步骤，一看就会)

目录

• 本篇文章适用场景
• • 一、下载MyFlash工具
  • 二、误删数据恢复
  • • 先检查MySQL有没有开启binlog日志
    • 演示误删除数据
    • 利用MyFlash工具 反写SQL
    • 利用mysqlbinlog 执行反写的sql二进制文件
    • 恢复完成
  • 三、误更新数据恢复
  • • 演示误更新数据
    • 查看binlog最近的更新记录 ，确定起始、结束位置
    • 利用MyFlash工具 反写SQL
    • 利用mysqlbinlog 执行反写的sql二进制文件
    • 恢复完成
    • 补充点 flush logs
  • 四、警告

本篇文章适用场景

①、测试环境少量近期误删除或者误更新的数据恢复。
②、测试环境少量从库数据不一致问题。

备注：大量数据的恢复或者复制还是需要使用备份数据,例如使用mysqldump或者Mydumper、mysqlshell。(本篇文章对此不做讨论)

警告：数据恢复为DBA专业人员负责处理的事情，本文章仅为开发人员测试环境恢复近期误操作的少量数据提供参考。
请对生产环境数据心存敬畏~

一、下载MyFlash工具

# 创建文件夹 mkdir /web cd /web # 下载压缩包 wget https://codeload.github.com/Meituan-Dianping/MyFlash/zip/master # 安装编译相关软件 yum install gcc -y yum install glib2 glib2-devel -y # 解压缩包 unzip master # 进入软件目录 cd /web/MyFlash-master # 编译 sh build.sh

编译完成后：

验证：

cd /web/MyFlash-master/binary ./flashback --help

输入：

MyFlash工具 安装完成

二、误删数据恢复

先检查MySQL有没有开启binlog日志

SHOW VARIABLES LIKE 'log_%';

如果发现数据库未开启binlog，那么这次恢复数据的旅程到此结束~

下面进入亡羊补牢时刻 (如果已经开启了 跳过这个步骤)。
打开MySQL的binlog日志：

# 找到MySQL的配置文件(一般情况下这样都能找到，找不到就要去问DBA或者运维了) find / -name my.cnf

一般情况下都是在 下面这个目录

添加配置：

vi /etc/my.cnf

添加一下配置

# 如果有集群这个id不用重复 server_id =6 # binlog日志位置 log_bin =/web/mysql/binlog/mysql-bin # binlog日志格式 binlog_format =row

重启MysQL

# 如果 systemctl 无法重启 就直接进入mysqld所在的目录重启 systemctl restart mysqld

演示误删除数据

先了解一些命令 后续要用到

# 查看所有binglog日志 SHOW MASTER LOGS; # 当前使用的日志 show master status; # 查看日志记录 show binlog events in '日志文件名';

下面开始演示：
先看下当前使用的binlog日志是哪个

# 当前使用的日志 show master status;

可以看到我目前的数据库使用的是 mysql-bin.000003 这个binlog日志

现在我有一张表 t_ph_uc_login

假如我误删了 t_ph_uc_login 表中的某条数据
这里我删除 id为 10f7a6c619e14b228df0e226bd84db5c 的数据

DELETE FROM `t_ph_uc_login` WHERE id = '10f7a6c619e14b228df0e226bd84db5c';

此时再查已经查不到这条数据了：

一般情况下我们并不知道到底删除的是哪条数据，更不知道删除数据的id。

我一开始说的前提是这种方法适合最近误操作的数据恢复。

下面我们去查看binlog日志。

# 查看日志记录 SHOW BINLOG EVENTS IN 'mysql-bin.000003';

我查到了3258行，经常更新的表一定比这个数值大得多。所以我们要查看最后几十行就行。

这里利用分页查看最后几百条数据：

SHOW BINLOG EVENTS IN 'mysql-bin.000003' LIMIT 300 OFFSET 3000;

我们可以在最后几行中找到 删除相关的语句，并记录下事务开始设置时的位置 (Pos列的数字)，和事务提交时的位置(End_log_pos列的数字)。
**注意图中蓝色标注框 **
开始： 11138303
结束： 11138917

利用MyFlash工具 反写SQL

把delete语句反写成insert语句

cd /web/MyFlash-master/binary ./flashback --databaseNames="phoenix-saas" --tableNames="t_ph_uc_login" --sqlTypes="delete" --start-position=11138303 --stop-position=11138917 --binlogFileNames=/web/mysql/binlog/mysql-bin.000003 --outBinlogFileNameBase=/web/recover.log

注意：上面数据库名称，表名称，sql类型 要根据自身需求改动，输出的文件也可以根据需求改动，我这里就输出在/web目录下了

尤其需要注意 --start-position 和 -stop-position 的数值 如果填的不对 很可能会报下面的错
如果报了下面的错就要好好检查下 --start-position 和 -stop-position 的数值 取的对不对了

Segmentation fault (core dumped)

如果执行成功 就能看到下面红框的文件：

由于MyFlash工具反写的数据也是二进制文件，所以还需要使用MySQL自带的 mysqlbinlog工具来执行

这里还可以用mysqlbinlog把二进制文件recover.log.flashback 解析成文本文件看下(这一步仅仅是看看 恢复数据可跳过这步)

# 先找一下mysqlbinlog在哪 find / -name mysqlbinlog # 我的mysqlbinlog在/web/mysql/bin/mysqlbinlog文件夹中 cd /web/mysql/bin # 解析查看二进制文件recover.log.flashback mysqlbinlog -v /web/recover.log.flashback

可以看到MyFlash工具 已经把
上面我执行的delete语句反写成INSERT语句了

DELETE FROM `t_ph_uc_login` WHERE id = '10f7a6c619e14b228df0e226bd84db5c';

利用mysqlbinlog 执行反写的sql二进制文件

最后一步利用mysqlbinlog 工具执行 反写的二进制文件 recover.log.flashback

# 先到 mysqlbinlog 所在的文件夹 cd /web/mysql/bin # 恢复数据 mysqlbinlog -v /web/recover.log.flashback | mysql -u用户名 -p密码

注意上面要输入自己数据库的用户名和密码

如果不报错 就说明数据恢复完成了。

如果报错下面的错：

ERROR 1782 (HY000) at line 17: @@SESSION.GTID_NEXT cannot be set to ANONYMOUS when @@GLOBAL.GTID_MODE = ON.

使用下面的语句查看是否开启了 全局事务ID (GTID) 功能

SELECT @@GLOBAL.GTID_MODE;

下图就是开启了。

可以 通过参数 --skip-gtids 跳过，不把gtid信息写到binlog中。不过如果数据库是多主或者一主多从的情况可能从库会出现数据不一致。

# --skip-gtids 跳过gtid信息 mysqlbinlog -v /web/recover.log.flashback --skip-gtids | mysql -u用户名 -p密码

恢复完成

执行完成后 再查询被删除的数据，可以发现已经恢复了。

三、误更新数据恢复

基本流程和上面误删除的恢复流程一致

演示误更新数据

还是拿id为 10f7a6c619e14b228df0e226bd84db5c 这条数据测试：
误更新前 user_id = 04f1fd53a4554e3fb5c9a40463a4ea4c

开始误更新

UPDATE `t_ph_uc_login` SET user_id = '小明' WHERE id = '10f7a6c619e14b228df0e226bd84db5c';

误更新后：

假如我们不知道 误更新之前的user_id 是什么

查看binlog最近的更新记录 ，确定起始、结束位置

SHOW MASTER STATUS;

SHOW BINLOG EVENTS IN 'mysql-bin.000004'

这里再细说下 找position的技巧吧

这次是更新 那就先找 Update_rows ，找到后 往上找 INFO列的 BEGIN，再往上一行, SET @@SESSION.GTID_NEXT 设置全局事务ID的这行就是这次更新事务的始位置。开始position就确定了。
开始： 1159

顺着Update_rows 再往下找，找到INFO列的 最近的一个COMMIT，这行的 End_log_pos列就是 结束position

结束： 1929

利用MyFlash工具 反写SQL

这里update 还是会被反写成update

cd /web/MyFlash-master/binary ./flashback --databaseNames="phoenix-saas" --tableNames="t_ph_uc_login" --sqlTypes="update" --start-position=1159 --stop-position=1929 --binlogFileNames=/web/mysql/binlog/mysql-bin.000004 --outBinlogFileNameBase=/web/recover.log

利用mysqlbinlog 执行反写的sql二进制文件

# --skip-gtids 跳过gtid信息 mysqlbinlog -v /web/recover.log.flashback --skip-gtids | mysql -u用户名 -p密码

恢复完成

再查一下 可以看到 user_id 已经恢复了

补充点 flush logs

如果当前数据库 还有大量连接正在更新 可以执行flush logs 重新生成新的binlog日志

比如现在的日志名是001 执行 flush logs 后 ，会生成一个002的文件 ，并且当前会使用002文件记录
那你再去找001文件的position时 就不会受到干扰了

四、警告

非专业DBA请勿在生产环境操作上述过程~