欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页

mysql利用二进制日志binlog恢复误删除操作

程序员文章站 2022-05-11 19:52:37
...

什么是二进制日志:

         二进制日志就是记录着mysql数据库中的一些写入性操作,比如一些增删改,但是,不包括查询!

二进制日志有哪些功能:

         二进制日志有着数据复制和数据恢复的功能,可以将一些误删除的数据恢复

注意:

          开启二进制日志会有1%的性能消耗!

查看二进制日志是否开启:

show variables like 'log_bin%';

mysql利用二进制日志binlog恢复误删除操作

开启二进制日志 :

我是将二进制日志放在/usr/local/mysqlData/log_bin下面的

注意:需要将文件夹的权限给mysql,不然会报错:

chown -R mysql:mysql log_bin/

vim /etc/my.cnf ,加入下面两个配置:

log-bin=/usr/local/mysqlData/log_bin/logbin
server-id=1

然后重启mysql服务器,这时候查看发现二进制日志已经开启

mysql利用二进制日志binlog恢复误删除操作

查看所有的binlog日志列表:

 show master logs;

mysql利用二进制日志binlog恢复误删除操作

刷新二进制日志:

刷新二进制日志其实也就是将当前日志截断,然后生成一张新的二进制日志文件,

重启mysql服务也会重新生成一个binlog日志。

flush logs;

mysql利用二进制日志binlog恢复误删除操作

如图所示,刷新之后又新建了一个logbin.000002的日志。

重置(清空)二进制日志文件:

reset master

mysql利用二进制日志binlog恢复误删除操作

如上图所示,reset之后又重新生成了二进制文件,从头开始。

模拟数据误删除如何恢复

下面建一个mdc库里面建一张test表插入两条数据,如下图所示:

mysql利用二进制日志binlog恢复误删除操作

然后备份mdc库的test表:

mysqlData]# mysqldump -uroot -p'aaa@qq.com' mdc test --single-transaction --master-data=1 --flush-logs > `date +%F`-mdc_test.sql;

然后继续插入几条数据:

insert into mdc.test values (3,'xiaomao'),(4,'lisi');

mysql利用二进制日志binlog恢复误删除操作

然后删除整张表:

drop table mdc.test;

 mysql利用二进制日志binlog恢复误删除操作

然后查看二进制日志,发现下面有三个二进制文件:

 mysql利用二进制日志binlog恢复误删除操作

使用mysqlbinlog 查看具体文件信息: 

mysqlbinlog logbin.000003

mysql利用二进制日志binlog恢复误删除操作

但是这样看上去比较难受,我们可以把二进制日志文件导出成普通文件查看:

mysqlbinlog --base64-output=DECODE-ROWS -v mysql-bin.000002 > mysqlbin.sql

mysql利用二进制日志binlog恢复误删除操作

由上面看出000002文件里记录了添加操作,000003里面记录了drop操作。 

现在恢复数据:

我们在使用mysqldump恢复的时候最好关闭binlog,因为恢复过程本身就是批量导入的过程,也会产生大量的binlog,其实这些binlog是无用的,会减慢恢复的速度。

关闭binlog恢复数据步骤:

登录到mysql中,临时关闭binlog:

set sql_log_bin=0;

mysql利用二进制日志binlog恢复误删除操作

然后使用source 命令恢复数据,注意:必须在mysql中执行恢复,因为这个关闭binlog是临时的,只在当前会话起作用。

source /usr/local/mysqlData/2019-10-24-mdc_test.sql

mysql利用二进制日志binlog恢复误删除操作

查询数据,发现备份前的数据已经恢复了,但是备份之后的数据没恢复。

mysql利用二进制日志binlog恢复误删除操作

下面就根据logbin恢复后添加的两条数据,

找出关键字的行数:

mysqlbinlog --no-defaults logbin.000002 | cat -n | grep -iw 'drop'

mysql利用二进制日志binlog恢复误删除操作

我们发现drop语句在logbin.000003文件的52行

然后查出drop语句的position

mysqlbinlog --no-defaults logbin.000003 | cat -n | sed -n '30,52p';

mysql利用二进制日志binlog恢复误删除操作

发现我们在493的位置删除了这个表,所以我们需要恢复的binlog文件有两个,一个是logbin.000002整个文件以及logbin000003文件到493为止。

​mysqlbinlog logbin.000002 --stop-position="493" logbin.000003 | mysql -uroot aaa@qq.com

​

mysql利用二进制日志binlog恢复误删除操作

 然后查看数据:

mysql利用二进制日志binlog恢复误删除操作

发现数据已经恢复回来了!大功告成 

如果需要恢复某一段的数据,则可以使用--start-position参数,如下图所示:

​mysqlbinlog --start-position="154" --stop-position="493" logbin.000003 | mysql -uroot aaa@qq.com

​