前言:
在我们的工作中,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. 我们尽量用的是哪个用户去创建的这些库这些表,我们就尽量用哪个用户去恢复他,不然会出现没有权限的问题,你也可以用超级管理员去恢复。