MySQL数据库系列(九)：MySQL之binlog数据恢复和回滚

1. MySQL的binlog

数据库的操作中会存在误操作导致数据的问题，主要是误删操作，对数据进行恢复或者回滚就是一个亟待解决的问题。在MySQL内提供了一个日志机制，对数据库的更新操作进行记录。但是需要注意的是，MySQL部分存储引擎是不支持事务，部分是支持的。在支持事务的存储引擎中，当事务成功提交后才会记录日志。

1.1 binlog启停状态

通过show variables like 'log_bin'命令来查看当前数据库是否开启了binlog功能。执行结果如下：

• ON：表示已经开启
• OFF：表示未开启

1.2 binlog开启配置

在my.cnf配置文件中[mysqld]内新增以下配置：

[mysqld]
# 指定binlog的格式
binlog_format = mixed
# 日志存储的位置
log-bin=/var/lib/mysql/mysql-bin
#设置binlog清理时间
expire_logs_days = 7
#binlog每个日志文件大小
max_binlog_size = 100m
#binlog缓存大小
binlog_cache_size = 4m
#最大binlog缓存大小
max_binlog_cache_size = 512m
#binlog刷新到磁盘的时机
sync_binlog=0
# 配置服务ID
server-id=1

通过上面配置修改后，重新启动MySQL数据库，再使用show variables like 'log_bin'命令验证一下，执行的结果即是开启状态。

sync_binlog参数需要着重说明一下，官方推荐使用值是0。这个参数对MySQL的影响是什么？

sync_binlog=n，在进行n次事务提交以后，Mysql将执行一次fsync之类的磁盘同步指令，通知文件系统将binlog文件缓存刷新到磁盘。

当事务提交后，Mysql仅仅是将binlog_cache中的数据写入binlog文件（还在内存中），但不执行fsync之类的磁盘，同步指令通知文件系统将缓存刷新到磁盘，而让文件系统自行决定什么时候来做同步，这个是性能最好的。

Mysql中默认的设置是sync_binlog=0，即不做任何强制性的磁盘刷新指令，这时性能是最好的，但风险也是最大的。一旦系统Crash（如断电），在文件系统缓存中的所有binlog信息都会丢失。

1.3 binlog的三种格式

在上面binlog的配置中包含了binlog的格式，其实binlog的格式主要分为三种，分别是STATEMENT、ROW、MIXED。

1.3.1 STATMENT模式

基于SQL语句的复制(statement-based replication, SBR)，每一条会修改数据的sql语句会记录到binlog中。

• 优点：不需要记录每一条SQL语句与每行的数据变化，这样子binlog的日志也会比较少，减少了磁盘IO，提高性能。
• 缺点：在某些情况下会导致master-slave中的数据不一致。

比如：delete from t where a>=4 and t_modified<='2018-11-10' limit 1;在主库执行这个语句的时候，如果使用的是a索引，会删除(4,4,'2018-11-10')这条记录，如果使用的是t_modified的索引则会删除insert into t values(5,5,'2018-11-09');所以在执行这条sql语句的时候提示：Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. The statement is unsafe because it uses a LIMIT clause. This is unsafe because the set of rows included cannot be predicted.
由于statement格式下，记录到binlog里的是语句原文，因此可能会出现这样一种情况：在主库执行这条SQL语句的时候，用的是索引a；而在备库执行这条SQL语句的时候，却使用了索引t_modified。

因此，MySQL认为这样写是有风险的。sleep()函数， last_insert_id()，以及user-defined functions(udf)等也会出现问题。

1.3.2 ROW模式

基于行的复制(row-based replication, RBR)格式：不记录每一条SQL语句的上下文信息，仅需记录哪条数据被修改了，修改成了什么样子了。

• 优点：不会出现某些特定情况下的存储过程、或function、或trigger的调用和触发无法被正确复制的问题。
• 缺点：会产生大量的日志，尤其是alter table的时候会让日志暴涨。

1.3.3 MIXED模式

混合模式复制(mixed-based replication, MBR)：以上两种模式的混合使用，一般的复制使用STATEMENT模式保存binlog，对于STATEMENT模式无法复制的操作使用ROW模式保存binlog，MySQL会根据执行的SQL语句选择日志保存方式。（推荐使用MIXED模式）

1.4 查看日志

binlog是采用的二进制，直接使用vi或者vim命令是无法看到具体内容的。具体实现如下：

查看日志文件

先执行show master status;命令，查看当前的日志文件。

show master status;

结果如下：

执行show binlog events in 'mysql-bin.000001';命令查看日志具体的内容。（此命令支持limit分页查询）

show binlog events in 'mysql-bin.000001';

结果如下：

1.5 恢复数据和回滚数据

恢复数据和回滚数据存在一定的差异，恢复的本质是将原有的插入语句再执行一遍，而回滚则是回退到删除之前的状态。一般在执行恢复数据操作的时候，都是使用MySQL自带的mysqlbinlog命令，而回滚数据，更多的是使用第三方组件binlog2sql。

但是根据实际情况，mysqlbinlog和binlog2sql都是可以实现恢复数据和回滚数据。总结异同点如下：

• mysqlbinlog在恢复的时候，是指定对应的起始点和结束点，而binlog2sql是使用python将起始点和结束点内的内容转化为一行一行的SQL语句，可视化效果更好；
• binlog2sql需要在配置文件中配置binlog_format = row和binlog_row_image = full，对条件要求更为苛刻，根据最佳实践，都推荐使用binlog_format = mixed，着实使binlog2sql有点尴尬；
• mysqlbinlog是MySQL自带的组件，在MySQL的各个版本都可以使用，但是目前根据binlog2sql官方文档中是对MySQL5.6, 5.7进行了测试，目前很多公司都在切换使用MySQL8，后期的支撑是否稳定可用，不太清楚。

1.6 mysqlbinlog恢复数据

当我们执行一条删除动作的时候，再看一下日志文件就会多出来一条记录。如图：

可以看到多了一个delete事件，这里注意下binlog中每个事件都有一个begin和commit，我们后面进行恢复或回滚的时候开始和结束的pos都是取的事件整体的开始点和结束点。比如上面删除事件的开始点其实是1934，结束点则是2168。

mysqlbinlog是mysql自带的命令，一般是在mysql安装目录下的bin目录里。因为我们是恢复数据，所以要找到已经删除语句的对应写入事件，将该事件再重新执行一遍即可。

从图中找到这条数据的插入时的开始点和结束点，分别是738和972。
此时使用mysqlbinlog命令进行恢复，将开始点738到结束点972的操作再执行一遍，命令如下：

mysqlbinlog --no-defaults /var/lib/mysql/mysql-bin.000001 --start-position=738 --stop-position=972  | mysql -uroot -p mysql_binlog_test

执行结束，再查看数据库，就会将之前删除的数据回复了。

1.7 binlog2sql

具体细节可以看binlog的官方解释：https://github.com/danfengcao/binlog2sql