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偏差）

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

比如 日志不单单是这个表的日志，还夹杂着其他表，日志内按照执行时间顺序记录，如果区间中包含其他表操作，那么其他表也会被重新执行，会造成脏数据。
————————————————
版权声明：本文为CSDN博主「夏天i（Joker）」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。
原文链接：https://blog.csdn.net/Jokers_lin/article/details/126449539