mysql binlog日志恢复数据

前言：

在我们的工作中，mysql数据丢失或者误删是一件非常严重的问题，而作为一个合格的运维工程师，一但遇到这种问题，其实也是很头疼的，但还是能够去恢复这些数据的，下面我就来介绍如何去利用binlog日志去恢复数据。

步骤一

（1）我们需要先去查看我们的binlog日志是否是正常打开的，在8.0当中是默认打开的，而在其他版本就不清楚是不是正常打开的，我们需要先去查看。这里显示on就是打开了

mysql>show variables like %log_bin%;
+---------------------------------+-----------------------------+
| Variable_name                   | Value                       |
+---------------------------------+-----------------------------+
| log_bin                         | ON                          |
| log_bin_basename                | /var/lib/mysql/binlog       |
| log_bin_index                   | /var/lib/mysql/binlog.index |
| log_bin_trust_function_creators | OFF                         |
| log_bin_use_v1_row_events       | OFF                         |
| sql_log_bin                     | ON                          |
+---------------------------------+-----------------------------+

步骤二

（1）在这里我们可以看到是正常打开的，下一步我们模拟一个新的环境删除数据后是如何恢复的，我们这里先用show master logs;查看一下我们的binlog日志的位置是在哪里，可以看到是一个最新的位置。

mysql>show master logs;
+---------------+-----------+-----------+
| Log_name      | File_size | Encrypted |
+---------------+-----------+-----------+
| binlog.000001 |       156 | No        |
+---------------+-----------+-----------+
1 row in set (0.00 sec)

（2）现在我需要往里面随便写东西，创建库也好创建表也好，再随便插入一点数据就ok了。

mysql>create database txt_name_ppp;#创建库
mysql>use txt_name_ppp;#进入库
mysql>create table gqh01(id int not null,name varchar(18),age int);#创建表
mysql>insert into gqh01 values(1,"zhangshang",22);#插入数据
mysql>insert into gqh01 values(2,"lisi",33);#插入数据
mysql>insert into gqh01 values(3,"zhaolei",21);#插入数据

（3）在这里我们创建了一个库和表还在里面插入了三条数据，现在我需要删除这些数据，来达到模拟丢失数据的场景。

mysql>drop database txt_name_ppp;
直接删除数据库！

步骤三

（1）我们在来查看下我们的binlog日志现在的位置是什么位置

show master logs;
+---------------+-----------+-----------+
| Log_name      | File_size | Encrypted |
+---------------+-----------+-----------+
| binlog.000001 |      1750 | No        |
+---------------+-----------+-----------+
1 row in set (0.00 sec)

（2）可以查看到位置是1750，说明我们刚刚的一切操作都已经记录到了里面，现在我们需要再创建一个binlog日志，来作为一个恢复数据的存放位置。

mysql> flush logs;
新建新的binlog日志

可以看到多了一个binlog.000002

show master logs;
+---------------+-----------+-----------+
| Log_name      | File_size | Encrypted |
+---------------+-----------+-----------+
| binlog.000001 |      1794 | No        |
| binlog.000002 |       156 | No        |
+---------------+-----------+-----------+
2 rows in set (0.00 sec)

步骤四

刚刚在介绍就说了binlog日志记录了我们大大小小的操作，所以我们现在要去到binlog日志存放的位置去看我们binlog日志，并找出我们创建库的位置是第几行，删除库的位置是第几行，找到这两个位置，我们就可以进行恢复数据了。

（1）目录位置可以看我们步骤一里面的内容我的是/var/lib/mysql/binlog.000001我们打开binlog.000001文件你会发现是无法查看的，是因为binlog日志的格式不是utf8，是二进制文件。我们需要使用到**“mysqlbinlog**“这个命令去查看

mysqlbinlog --set-charset=utf8  binlog.000001 > tmp.sql
#这里是将格式改成utf8，然后再重定向到tmp.sql这个临时文件，这样我们就可以直接使用cat命令去查看

（2）可以看到我创建的txt_name_ppp这个表的位置是233行

可以看到我删除表的命令是在第1622

（3）现在我们确定了开始位置和结算书位置了，现在开始恢复。

mysqlbinlog -v binlog.000001 --start-position=233 --stop-position=1622 | mysql -uroot -p'gqh1234567890'

注意项

🔔1. 如果我们binlog日志比较大的情况下，按照我这种一页一页去翻是比较慢的，而很费眼睛，我们需要去加过滤group，去查我们重要的位置。
2. 我们尽量用的是哪个用户去创建的这些库这些表，我们就尽量用哪个用户去恢复他，不然会出现没有权限的问题，你也可以用超级管理员去恢复。

最后，希望这篇文章可以帮助到正在学习或者正在工作中的你，也希望大家可以说出自己的建议，在评论区我们互相讨论互相学习。再见