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

admin • • SQL技巧

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

本文主要为开发人员提供在测试环境中恢复近期误操作的少量数据的方法,首先介绍了如何下载并安装MyFlash工具,然后详细讲解了如何利用该工具和MySQL的binlog日志来恢复误删或误更新的数据,介绍的非常详细,需要的朋友可以参考下。

本篇文章适用场景

①、测试环境少量近期误删除或者误更新的数据恢复。

②、测试环境少量从库数据不一致问题。

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

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

一、下载MyFlash工具

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
# 创建文件夹
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

编译完成后：

2024103009393166

验证：

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

输入：

2024103009393167

MyFlash工具 安装完成。

 

二、误删数据恢复

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

1
SHOW VARIABLES LIKE 'log_%';

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

下面进入亡羊补牢时刻 (如果已经开启了 跳过这个步骤)。

打开MySQL的binlog日志：

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

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

添加配置：

1
vi /etc/my.cnf

添加一下配置

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

重启MysQL

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

演示误删除数据

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

1
2
3
4
5
6
7
8
# 查看所有binglog日志
SHOW MASTER LOGS;
# 当前使用的日志
show master status;
# 查看日志记录
show binlog events in '日志文件名';

下面开始演示：

先看下当前使用的binlog日志是哪个

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

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

现在我有一张表 t_ph_uc_login

假如我误删了 t_ph_uc_login 表中的某条数据

这里我删除 id为 10f7a6c619e14b228df0e226bd84db5c 的数据

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

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

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

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

下面我们去查看binlog日志。

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

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

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

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

我们可以在最后几行中找到 删除相关的语句，并记录下事务开始设置时的位置 (Pos列的数字)，和事务提交时的位置(End_log_pos列的数字)。

**注意图中蓝色标注框 **
开始： 11138303
结束： 11138917

利用MyFlash工具 反写SQL

把delete语句反写成insert语句

1
2
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 的数值 取的对不对了

1
Segmentation fault (core dumped)

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

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

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

1
2
3
4
5
6
# 先找一下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语句了

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

pA5ywtO.png

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

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

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

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

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

如果报错下面的错：

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

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

1
SELECT @@GLOBAL.GTID_MODE;

下图就是开启了。

pA5yU76.png

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

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

恢复完成

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

pA5yN0x.png

三、误更新数据恢复

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

演示误更新数据

还是拿id为 10f7a6c619e14b228df0e226bd84db5c 这条数据测试：

误更新前 user_id = 04f1fd53a4554e3fb5c9a40463a4ea4c

pA5yJXR.png

开始误更新

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

误更新后：

pA5yGc9.png

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

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

1
SHOW MASTER STATUS;

pA5y81J.png

1
SHOW BINLOG EVENTS IN 'mysql-bin.000004'

pA5ylhF.png

这里再细说下 找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

1
2
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二进制文件

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

恢复完成

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

pA5yQtU.png

补充点 flush logs

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

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

四、警告

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

到此这篇关于MySQL数据误删或者误更新如何恢复的文章就介绍到这了。

 

学习资料见知识星球。

以上就是今天要分享的技巧，你学会了吗？若有什么问题，欢迎在下方留言。

快来试试吧，小琥 my21ke007。获取 1000个免费 Excel模板福利​​​​！

更多技巧， www.excelbook.cn

欢迎 加入 零售创新 知识星球，知识星球主要以数据分析、报告分享、数据工具讨论为主；

电商数据分析360°实战攻略！

你将获得：

1、价值上万元的专业的PPT报告模板。

2、专业案例分析和解读笔记。

3、实用的Excel、Word、PPT技巧。

4、VIP讨论群，共享资源。

5、优惠的会员商品。

6、一次付费只需99元，即可下载本站文章涉及的文件和软件。

文章版权声明 1、本网站名称：Excelbook
2、本站永久网址：http://www.excelbook.cn
3、本网站的文章部分内容可能来源于网络，仅供大家学习与参考，如有侵权，请联系站长王小琥进行删除处理。
4、本站一切资源不代表本站立场，并不代表本站赞同其观点和对其真实性负责。
5、本站一律禁止以任何方式发布或转载任何违法的相关信息，访客发现请向站长举报。
6、本站资源大多存储在云盘，如发现链接失效，请联系我们我们会第一时间更新。

THE END
分享
二维码
< <上一篇
下一篇>>