mysql binlog数据恢复

要谈数据恢复问题，先不得不说一下数据的重要性。可以说，一个企业最核心的就是数据了，我一个老师曾说，不管你什么架构，一切都是为数据服务的，数据是一个企业最核心的价值。不过出现问题的话，数据一旦被删除，每过一秒，都将对企业造成巨大的损失。最近，还真的发生了一起删库跑路事件，微盟7*24小时紧急恢复数据，商家赔付1.5亿。（图1）

想想，如果我们能够快速对数据进行恢复，那不就可以把损失降低，甚至到最低。我们优先要做的，就是找到一种可以快速恢复数据的方式，将恢复时间尽可能缩短。

这里我们只拿MySQL数据库来举例，binlog是二进制日志，用于记录用户对数据库操作的SQL语句。各个从库的同步，也是依靠binlog日志，它是非常重要的日志。其他关系型数据库，也大同小异，利用Oracle数据库，我们可以利用归档日志。

那么，我们该如何进行数据恢复呢？我认为，这还得看你是怎么备份的。接下来我们就简单讨论下主流的备份方案。

一个是全量备份，它适用量级较小的库，全量恢复快。举个例子，每天0点我们进行全量备份，如果我们想恢复7号0点之前的数据，毫无疑问，直接把7号的备份的全量恢复就行了。不过有一种情况是，如果我们想恢复7号0:00~23:59的数据，该怎么办呢？那么就只能利用binlog来进行恢复了。（图2）

还有一种主流的备份方案是全量+增量备份，这种方案适用于量级较大的库，节约磁盘空间，全量恢复慢。举个例子来说，每月1号0点，我们进行一次全量备份，后续每天0点进行增量备份。如果我们想恢复7号0点之前的数据，可以看出，只需要先恢复1号的全量数据，加上2号~7号的增量数据就可以了。不过有一种情况是，如果我们想恢复7号0:00~23:59的数据呢？那么就只能利用binlog来进行恢复了。（图3）

今天的分享里，我们暂时不去讨论全量，或者增量的恢复数据的效率问题，主要讲一讲如何利用binlog进行恢复。如果想利用binlog，我认为你不得不了解下binlog的三种模式，这里我们以5.7.21版本为例，注意：5.7.7之前的版本，默认格式为Statement Level，之后默认为Row Level。

先来看下Statement Level模式，它是基于SQL语句的复制，只会将SQL语句记录到binlog中。因为这种模式只存储SQL，没有真正的数据，所以无法进行数据恢复，生产环境一般不用这种模式。

再来看下Row Level模式，它是基于行的复制，并且会将每一条数据的变化记录到binlog中，是没有SQL语句的。这种我们可以解析出真正的数据，生产中建议采用Row Level模式，当然它也有一些缺点，比如记录数据时产生大量binlog，面对存储过程可能出现不一致问题。

要说的第三种模式是Mixed模式，它是混合模式复制，默认格式是Statement，某些情况下会切换到Row格式。

说完了模式，模式该如何进行查询呢？可以通过binlog_format参数来获取。（code1）

mysql> show variables like ‘binlog_format‘;
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | ROW   |
+---------------+-------+
1 row in set (0.00 sec)

我们使用的是MySQL 5.7.21版本的数据库，这里面的参数是经我单独修改过的，一般线上系统，也是采用的这种模式。（code1）

OK，搞清楚了这些基础知识后，接下来我们就可以对binlog进行解析与恢复了，一起来看下。

首先来看解析，那怎么理解它呢？它的意思就是将二进制binlog格式，解析成SQL语句的形式，这些SQL是数据库操作的正向结果。我们先来建立一些基础数据。（code2）

root@localhost(test) 14:23:31> CREATE TABLE `t1` (
    ->   `id` int NOT NULL AUTO_INCREMENT,
    ->   `name` varchar(60) DEFAULT NULL,
    ->   PRIMARY KEY (`id`)
    -> ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.02 sec)

root@localhost(test) 14:23:31> insert into t1(name) values(‘ljp1‘),(‘ljp2‘);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

root@localhost(test) 14:23:31> select * from t1;
+----+------+
| id | name |
+----+------+
|  1 | ljp1 |
|  2 | ljp2 |
+----+------+
2 rows in set (0.00 sec)

这里我们建立了一张表t1，它里面包含两个字段，并插入两行数据。接下来，我们对它进行解析，可以看到，时间为：14:23:31 时刻插入了两行数据。（code2）

我们根据时间找到对应的binlog，找到包含14:23:31的binlog日志，就可以看到是mysql-bin.000009文件。（code3）

[root@localhost binlog]# ll mysql-bin.000009
-rw-r----- 1 mysql mysql  837 Mar  2 14:23 mysql-bin.000009

这个文件是二进制文件，我们可以利用MySQL 自带工具mysqlbinlog，来进行解析，看看具体内容，是否与我们写入的信息一致。（code4）

[root@10-0-4-162 binlog]# /usr/local/mysql/bin/mysqlbinlog --base64-output=decode-rows -v mysql-bin.000009
......
# at 259
......
CREATE TABLE `t1` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(60) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8
......
### INSERT INTO `test`.`t1`
### SET
###   @1=1
###   @2=‘ljp1‘
### INSERT INTO `test`.`t1`
### SET
###   @1=2
###   @2=‘ljp2‘
COMMIT/*!*/;
......

可以看到，利用MySQL自带工具mysqlbinlog，我们就可以轻松对binlog进行解析了，部分无关内容我已经删除，正向解析后得到的SQL，与我们先前执行的SQL一致，并且时间点对应也是一致的。如果你只是想看某人到底在数据里修改了什么，通过这个工具，就可以轻松搞定了。（code4）

那么问题来了，我们的目的是为了恢复数据，比如，将前面insertSQL解析成deleteSQL语句，因为insert可能是误插入数据。如果你数据量较少，我们可以利用前面的方式进行正向解析，手动修改成deleteSQL就可以。

但是，如果生产系统，binlog设置为2GB，解析后SQL可能有上千万行，这个量级我们手动修改短时间内是无法完成的。那么有没有一种可以直接生成反向SQL语句的方式呢？答案是有的，这样我们就可以 直接将insertSQL解析成deleteSQL了，从而实现数据的快速恢复。为了实现这个效果，这里我将介绍两个工具：一个是binlog-rollback，一个是MyFlash。

我们先来用binlog-rollback工具试试，这个工具就是一个perl脚本，binlog-rollback.pl，开始进行恢复。（code5）

[root@localhost ~]# perl binlog-rollback.pl -f ‘/u01/mysql/3306/log/binlog/mysql-bin.000009‘ -h 127.0.0.1 -u ‘root‘ -p ‘xxx‘ -P 3306 -i -o ‘/tmp/t.sql‘

[root@localhost ~]# more /tmp/t.sql 
DELETE FROM `test`.`t1` WHERE `id`=2 AND `name`=‘ljp2‘;
DELETE FROM `test`.`t1` WHERE `id`=1 AND `name`=‘ljp1‘;

可以看到，我们之前是insert两行数据，这里反向解析成了delete，达到了我们的目的。同样也可以想象 当你误删除了数据，反解SQL就会是insertSQL了，这样就达到了恢复数据的目的。但是这个工具我们也看到，是需要输入用户名密码的。（code5）

接下来我们再来试试MyFlash，这个工具是由美团大佬开源的。我们同样来进行反解SQL试试。（code6）

[root@localhost MyFlash-master]# ./binary/flashback --binlogFileNames=/u01/mysql/3306/log/binlog/mysql-bin.000009

[root@localhost MyFlash-master]# mysqlbinlog --base64-output=decode-rows -v binlog_output_base.flashback
......
### DELETE FROM `test`.`t1`
### WHERE
###   @1=1
###   @2=‘ljp1‘
### DELETE FROM `test`.`t1`
### WHERE
###   @1=2
###   @2=‘ljp2‘
......

可以看到，这个工具与前面讲到的工具不太一样，首先反解binlog的时候，不需要输入用户名密码，并且生成binlog_output_base.flashback文件，这个文件是反解后的二进制文件。生成这种文件进行恢复的好处就是，要比binlog-rollback工具生成SQL语句方式 快很多。同时，我们也进行了查看，确实与我们之前执行的正好相反，由insert两行数据，生成最后的delete两行数据，从而达到数据恢复的目的。（code6）

再深入一步，为了帮助我们可以更快地恢复数据，这里我来延伸讲一下，可以看到，通过binlog恢复，其实还是比较麻烦的。接下来引入一个新的概念：延迟从库，来看下简单的架构是什么样子的。（图4）

其中红色部分，就是需要我们单独增加的从库，对它进行延迟处理，比如延迟24小时。当有人误操作了，DBA会在24小时内接到通知，由于延迟从库还是24小时之前的数据，登陆这个延迟从库，直接查询就可以了。（图4）

今天的分享到这里就结束了，最后我来给你总结一下。开头部分，我先是讲了数据快速恢复的必要性，核心就在于：如何在最短的时间内 进行数据恢复，避免企业损失。

但是如何做能够快速恢复，这与你的备份方式相关。就这个点来说，我们讲了一般的备份策略，包括全量备份、增量备份。由于今天主要是讲解binlog的恢复方式，因此我进一步讲了binlog的三种模式：Statement Level、Row Level、Maxed模式。一般线上数据库系统，采用Row Level模式。

这种模式下，我们拿MySQL 5.7.21做了举例讲解，插入两条数据，进行了正向、反向恢复。正向直接利用MySQL自带工具mysqlbinlog，反向利用binlog-rollback、美团的MyFlash。这两种工具是有本质区别的，binlog-rollback直接生成SQL，对于我们更加友好，但当数据量较大时，美团MyFlash是比较快的。当然今天只介绍了常用的两种工具，业内工具还是很多的，大家可以去GitHub上搜索。

最后，我延伸讲了一下，为了更快地恢复数据，我们可以引入延迟从库，对从库进行延迟处理，这也能够从另一个角度 进行数据的快速恢复。（图5）

好，希望我的分享可以帮助到你，也希望你在下方的留言区和我参与讨论。

mysql binlog数据恢复

标签：stat   混合   duplicate   反向解析   并且   roo   用户名   --   inno