Mysql 通过 binlog日志 恢复数据(数据搞丢看过来)

1.查看binlog是否开启

#查看binlog是否开启
show variables like '%log_bin%'; 

 2.锁表,防止数据被污染(可根据需求,不阻塞业务的情况)

#锁表,防止数据被污染
lock tables 表名 read;

3.查询最新的binlog 获取binlog日志名称 下一步需要用到

show master status;

4.查看binlog 日志 

show binlog events in 'binlog.000067';

5.确定恢复范围后,执行如下命令,回滚到结束时数据状态 

mysqlbinlog  --start-position='起始Pos' --stop-position='结束Pos' D:/Develop/Mysql/mysql-8.0.27-winx64/data/binlog.000067 | mysql -uroot -p

6.例子(数据库误删后恢复)

6.1因为是非重要步骤,仅为突出效果,为了方便操作我就不再命令行中创建数据库了,直接通过navicat工具创建

 6.2刷新后可见到刚刚创建的数据库log-test

 6.3由于没有数据,我们添加点假数据,还是为了节约时间,我们用老办法,navicat可视化创建一张user表及添加多条测试数据进去

6.4数据准备好了,我们来模仿误操作的情况,执行dorp命令删除掉该数据库(删库执行有风险-千万注意执行环境)

drop database log-test;

由于含有非法字符,所以必须使用` `符号

 刷新navicat工具,发现数据库已被删除

到目前为止删库已经操作结束,接下来就是如何通过binlog日志恢复回来了

6.5 查询最新的binlog 获取binlog日志名称 

SHOW MASTER status;

得到当前最新binlog日志为binlog.000067 (该文件为屋里文件,可在mysql安装目录下查看)

 物理文件如图所示

6.6查看该binlog 日志 

注意图中日志细节

由此我们要想恢复数据库,也就是必须回滚到执行 删除数据库前

由此我们可以得到 起始的pos 和 结束的Pos

6.7已经确定恢复范围,执行命令,回滚到结束时数据状态 

mysqlbinlog --no-defaults --start-position=5847 --stop-position=6974 D:/Develop/Mysql/mysql-8.0.27-winx64/data/binlog.000067 | mysql -uroot -p

--start-position=起始的pos

--stop-position=结束的Pos

D:/Develop/Mysql/mysql-8.0.27-winx64/data/binlog.000067 binlog所处位置的路径

这个命令需要在mysql 命令行中执行,我们进入到mysql 安装目录的 bin 目录

发现mysqlbinlog.exe 可执行文件

执行命令 并且输入 mysql 密码

 到此数据库已经恢复成功,我们还是为了方便使用navicat刷新查看 

数据库及其表数据 均已经恢复好了

7.例子(数据库中表数据 误操作后恢复)

7.1我们还是按照这张表操作,增加数据及age字段

 执行update操作,模拟误操作导致真实数据被覆盖 (忘记加where条件,导致影响行数超出预期)

 7.2模拟操作失误,将所有用户年龄都改为了同一个值

目前数据库数据内容如下

 到目前为止误操作导致表数据异常已经操作结束,接下来就是如何通过binlog日志恢复回来了

7.3老规矩先查询最新的binlog 获取binlog日志名称

7.4然后查看该binlog 日志 

注意日志细节

我们目前的预期结果是,恢复到误操作user 表 age字段前,也就是误操作产生的日志记录的上一条COMMIT

那么我们可以由此获取到的起始的pos 和 结束的Pos

7.5通过mysql命令行我们可以执行命令 

mysqlbinlog --no-defaults --start-position=17639 --stop-position=18038 D:/Develop/Mysql/mysql-8.0.27-winx64/data/binlog.000067 | mysql -uroot -p

我们可以看出当前只是恢复了最后一条数据,显然这样恢复是达不到预期结果的

7.6预期结果出现偏差(小结) 

照目前的表现证明,在现有的数据基础上,只是重新执行了一次 --start-position=17639 到 --stop-position=18038 期间记录的操作,

按照这个逻辑的话,也就是我们还要再此基础上在找到上两次操作,首次添加第一条(张三)数据age的起始Pos - 然后到误操作前一条的结束Pos(王五),扩大区间范围,即可恢复成功。

不过在不知道自己操作了几步的情况下,很难找到对应的起始Pos 到 结束Pos, 这个时候就需要前面提到的,发现操作失误后,赶紧进行锁表操作,防止进一步弄脏数据

我们就简单粗暴点,换种思路,也就是我们摘除掉最后一次的误操作日志,Pos从创建表后首次操作开始 到 误操作前

mysqlbinlog --no-defaults --start-position=5847 --stop-position=18038 D:/Develop/Mysql/mysql-8.0.27-winx64/data/binlog.000067 | mysql -uroot -p

 7.7 执行报异常

执行后出现异常,无法创建数据库,因为数据库存在!!! 这个时候如果日志区间中包含创建数据库,必须要先删除掉数据库才可。 包含数据库 与 表 均如此会抛出异常

7.8重新确定区间 

我们跳过创库 -跳过创表等操作后 开始

mysqlbinlog --no-defaults --start-position=6283 --stop-position=18038 D:/Develop/Mysql/mysql-8.0.27-winx64/data/binlog.000067 | mysql -uroot -p

--start-position=6283 创建表后首操作

--stop-position=18038 误操作前一条操作

我们查看表,数据已经恢复到误操作前。

 8.总结:

选中区间 只是重新执行了一次 --start-position=17639 到 --stop-position=18038 期间记录的操作(见案例7.6偏差

文章仅供参考,问题也比较多,还有几个情况没考虑。

比如 日志不单单是这个表的日志,还夹杂着其他表,日志内按照执行时间顺序记录,如果区间中包含其他表操作,那么其他表也会被重新执行,会造成脏数据。

有新的思路 或者 解决这个问题的方式 以及 产生的影响未考虑到的,欢迎评论区一起交流。

评论 1
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值