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

什么是二进制日志：

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

二进制日志有哪些功能：

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

注意：

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

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

show variables like 'log_bin%';

开启二进制日志 :

我是将二进制日志放在/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服务器，这时候查看发现二进制日志已经开启

查看所有的binlog日志列表：

 show master logs;

刷新二进制日志：

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

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

flush logs;

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

重置(清空）二进制日志文件：

reset master

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

模拟数据误删除如何恢复

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

然后备份mdc库的test表:

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

然后继续插入几条数据:

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

然后删除整张表:

drop table mdc.test;

 

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

 

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

mysqlbinlog logbin.000003

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

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

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

现在恢复数据:

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

关闭binlog恢复数据步骤:

登录到mysql中，临时关闭binlog：

set sql_log_bin=0;

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

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

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

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

找出关键字的行数：

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

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

然后查出drop语句的position

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

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

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

​

 然后查看数据:

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

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

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

​