Mysql通过binlog恢复数据

背景：

前段时间公司搞项目，到了转测阶段，之前本来都是开发和测试共用一个数据库，新来的测试大姐不肯，说要经常清测试数据呢，好吧，单独又给她搞了一个测试库。过了两天，一大早，大姐在群里面就说了：不好意思，一不小心搞错了，把你们开发库数据全清了。我和我的同事们：？？？？

 

环境配置：

mysql5.6.46

 

binlog：

简单通俗来讲，其实就是一个记录所有增删改操作记录的日志。我们可以通过它来对误操作的数据进行恢复，当然还可以用来进行主从数据库的同步操作。

 

binlog的三种模式：

①statement：记录每一条修改数据的sql。

优点：日志文件比较小，节约io操作，性能较好。

缺点：只记录执行语句，所以还需要保证在主从执行的得到相同的结果。所以准确性差。

②row：保存哪条记录被修改。

优点：准确性强。

缺点:日志文件比较大。

③mixed：兼顾前两者的优点。

 

实际操作：

① 查看binlog有没有开启：

在mysql中输入命令：SHOW VARIABLES LIKE 'log_bin%'; 

来查看当前数据库有没有开启Binlog模式，我们看到下图中的log_bin是ON表示已经开启，如果是OFF，那我们则需要去Mysql的配置文件中进行配置。

图1.

配置binlog：

首先打开mysql的配置文件：vi /etc/my.cnf  

图2.

我们会看到上图中的内容，在里面添加一条：log-bin=mysql-bin，即可开启binlog模式。

 

② 设置binlog模式：

首先，我们通过命令：SHOW VARIABLES LIKE 'binlog%'; 查看下当前binlog的模式。

图3.

还是刚才那个配置文件，我们在配置文件中添加一行：binlog_format="ROW"，即可设置成row模式，其他模式同理，也是在这里设置添加。

图4.

 

③ 查看binlog日志：

首先，我们在Mysql中输入命令：SHOW MASTER LOGS;  可以看到下面有这些binlog的日志文件，

通过命令：SHOW MASTER STATUS;可以查看当前是在哪个日志文件中，

通过命令：FLUSH LOGS; 可以截断日志文件，重新定向到新的日志文件中，我们在实际操作的时候，每次操作binlog恢复前，都需要执行下此命令，能够保证之前的日志文件不会再有新的日志在到这个文件中，影响恢复。

然后，可以根据命令：show variables like '%datadir%';  查询出这些日志文件保存的路径

我们在服务器，cd 到这个目录下，这些文件确实存在。但是这些文件是二进制文件，用cat/vi这些命令是无法正常查看的。

这时候就需要我们的mysqlbinlog这个命令登场了，

首先，我们在服务器中输入命令：mysqlbinlog /usr/local/mysql/data/mysql-bin.000012;  这边报错了，字面意思是没设置字符集，我们可以直接忽略，通过在命令后面增加 --no-defaults参数即可

输入命令：mysqlbinlog --no-defaults /usr/local/mysql/data/mysql-bin.000012;  可以看到有如下的一个文件信息。

当然我们还可以在mysql中输入命令：SHOW BINLOG EVENTS IN 'mysql-bin.000012';

这样也可以看到binlog中记录的一些事件：

其中，server_id =1，由于我们没有设置，就是表示就是默认主机，Pos我的理解就表示的是一个偏移指针，就类似于一个时间节点，在这个时间节点完成了哪些操作。event_type就表示事件类型，xid事务，query查询，write rows表示插入数据，delete_rows删除数据，都是比较好辨认出来的。

 

④ 通过binlog进行数据恢复：

我们现在拿一张表做实验，首先，新建个表，里面初始有4条测试数据。

我们先在mysql中，执行：FLUSH LOGS;   把之前的记录截断掉，再通过命令：SHOW MASTER LOGS; 查看下当前的日志，

mysql-bin.000014也就是我们刚生成的日志文件

这时候，我们去操作表数据，先给这张表插入一条数据，

这时候里面会多出个test5的记录，这时候，我们模拟误操作，把test5直接删除。这时候，我们想去恢复test5的数据，应该怎么做呢？

首先，我们回到mysql，继续执行FLUSH LOGS; 然后查看日志，这时候文件14已经被截断了，它就不会再增加新的日志了，这样就保证了我们恢复的时候，不会受影响

我们去到服务器，执行命令：mysqlbinlog --no-defaults /usr/local/mysql/data/mysql-bin.000014;

我们可以看到这些信息，你可能也已经看出了一些关键字内容了，write_rows表示我们插入的test5记录，delete_rows表示我们删除的test5记录，左边的at表示的其实就是之前说过的pos位置。在268的时候，我们插入了test5，而在766的时候，我们执行了删除记录，是不是一目了然了。

下面这个命令是本文的核心操作，就是去恢复数据用的：

mysqlbinlog --no-defaults --start-position=268 --stop-position=695 /usr/local/mysql/data/mysql-bin.000014 | mysql -u root -p

start-position表示恢复起始点，stop-position表示恢复结束点，注意：起始和结束点要选择的正确，要定在插入前，和删除前，要不然是恢复不了数据的。

除此之外，我们还可以指定开始和结束时间，来恢复数据，比如下面这种命令：

mysqlbinlog --no-defaults  --start-datetime='2020-07-11 10:55:10' --stop-datetime='2020-07-11 10:56:16' /usr/local/mysql/data/mysql-bin.000014 | mysql -u root -p

执行完成后，我们就可以看到，我们的表记录，test5又回来了。恢复成功！

 

⑤ 配合定时任务导出sql脚本：

上面已经演示过我们使用binlog如何进行数据恢复了，但是再实际工作中，我们还需要定时去执行sql脚本，去减少binlog恢复的工作量。

首先，我们定义一个shell脚本文件，mysql_backup.sh，里面内容如下：

#!/bin/bash
db_name1='community'
backup_dir='/home/program/community/backup'
current_time=$(date '+%Y-%m-%d-%H')
/usr/local/mysql/bin/mysqldump --defaults-extra-file=$backup_dir/my_mysql.cnf --default-character-set=utf8 -uroot -proot $db_name1 | gzip > $backup_dir/community_backup_$current_time.sql.gz

这边db_name1指定的是数据库名称，你可以换成自己的，--defaults-extra-file指定的是配置文件，gzip导出指定路径的sql脚本压缩包。注意：所有路径尽量使用绝对路径，要不然在执行的时候会有问题，找不到命令文件。

配置文件my_mysql_cnf的内容如下：

[mysqldump]
max_allowed_packet=400M
host=127.0.0.1
user=root
password=root
[mysql]
host=127.0.0.1
user=root
password=root

我的文件结构如下所示：

配置完成后，我们就可以使用./mysql_backup.sh看一下，命令是否能够正常执行。

这边有个坑：就是在生成文件的时候，文件名后面会有?这种东西，这种一般是由于windows和linux换行编码不一致导致的，尽量我们在linux的vi里面去编辑脚本，我这边提供一个命令行，能够快速解决这个问题：

sed -i 's/\r$//' mysql_backup.sh

最后我们要使用Linux的crontab设置定时任务，首先执行：crontab -e打开一个类似vi编辑器的页面。在里面添加定时任务：

0 0,6,12,18 * * * sh /home/program/community/backup/mysql_backup.sh

意思就是每天0点，6点，12点，18点各执行一次。

编辑完，我们:wq保存，可以再执行crontab -l看一下定时任务内容：

这个地方，需要注意的是，在刚才我们shell脚本中，再三强调要输入绝对路径，就是因为这个地方的定时任务，如果你不设置绝对路径，他去执行的时候，会找不到命令，导致无法正常导出脚本。

一切准备就绪后，我们就可以看到图中，生成文件的效果了

结束语：

通过binlog恢复mysql的误操作数据，还是非常实用的技术，配合好定时任务生成的脚本文件，基本能应对实际工作中的误操作问题。比如，我们在上午9点的时候，误删了一张表，我们就可以先去找到时间点最近的脚本文件，比如我这边是早上6点的，先还原一部分数据，然后再通过binlog恢复误删的表数据。