一、MySQL binlog介绍

MySQL的binlog文件记录了数据的每次变动，事务执行过程中会先把日志写到binlog cache中，等事务提交的时候再把cache写到binlog文件，最后fsync落盘并清空cache（redolog也有着同样的落盘机制，所以也叫做双写）。一个事务的 binlog不能被拆开，不论事务多大也要确保一次性写入binlog。每个线程都会申请一块binlog cache，内存空间大小由参数 binlog_cache_size控制。如果超过参数设置的大小就要暂存到磁盘。而write和fsync的时机由参数sync_binlog决定（双1参数之1）可以详细到哪个时间点执行了哪些语句，有了这些语句就可以对数据进行还原。binlog格式分为了三种：

1、statement（标准模式，简称SBR）

保存的是每一条修改数据的SQL语句本身，然后在从服务器上会执行相同的语句来同步数据。日志记录量比较少，但是对于一些执行结果不明确的语句可能会造成主从数据不一致

2、row（行模式，简称RBR）

最安全的复制模式，将数据的具体改变记录在日志中，记录的日志量大。row不会因为某些会产生随机数的SQL而让主从数据不一致（比如包含了时间函数的语句），但是如果有对BLOB这样的大字段进行操作，row会比statement更耗时，因为row会记录BLOB字段的具体值，而不单单是生成数据的语句。row格式的二进制日志无法直接阅读，需要通过mysqlbinlog --base64-output=decode-rows转码

3、mixed（混合模式，简称MBR）

statement和row的结合模式，当基于statement的语句无法精确复制时，就自动采用行模式进行复制

delete * from test; #假设这条SQL语句删除了100万条数据
# STATEMENT语句模式仅记录这一条SQL语句
# ROW行模式会记录执行删除时的每一个语句，会记录100万次

由于binlog是二进制文件，必须使用mysqlbinlog工具或者登录MySQL客户端后使用show binlog events命令才可查看，不能使用vi等文本工具进行查看和修改。每个Binlog开头都会有4个字节的魔术数，所以对Binlog进行解析的时候都是从#at4 开始的，经过解析后的日志内容如下图：

二、binlog日志内容的查看

1、show binlog events

使用show binlog event命令可以比较快速的看到每个position的大概操作，在查看的时候Event_type为Query的语句就是引起数据库内容变化的SQL，Pos和End_log_pos字段内容是语句的开始和结束位置，具体的SQL在Info字段中。由于每个DDL本身就是一个事件，所以能直接在Info中看到，而DML语句都是由BEGIN开头，COMMIT结束，所以Pos信息应该以Begin为准，End_log_pos则是看COMMIT的。如下图1596处的Position执行了truncate语句，要使用该binlog进行数据恢复的话就需要恢复到1512处

#也支持show relaylog events命令查看relaylog
mysql > show binlog | relaylog events in 'master-bin.000002';                     #显示指定二进制日志内容
mysql > show binlog | relaylog events in 'master-bin.000002' limit5;              #显示N条指定二进制日志内容
mysql > show binlog | relaylog events in 'master-bin.000002' from 3390 limit5;    #从指定位置开始显示日志

2、mysqlbinlog

该工具是解析binlog的利器，可以同时解析多个binlog，在使用binlog还原时也需要依赖它，包含选项如下：

· -v |-vv：如果不加-v的话只能看到行格式本身但是看不到SQL，-v可以显示出执行过的SQL但是不显示binlog_rows_query_log_events参数所附带的内容，加 -vv则可以打印binlog_rows_query_log_events参数效果

· --base64-output=decode-rows：如果binlog为row格式必须使用该选项对日志进行解析，恢复数据时不能加该选项

· --include-gtids：只解析指定的 GTID 的事务，比如--include-gtids='143c2270-3b22-11ea-9dea-14feb5dc2c77:2-8'就是2-8的事务

· --exclude-gtids：不解析指定的 GTID 的事务

· --start-datetime | --stop-datetime：指定时间节点，需要注意的是--stop-datetime所指定的时间节点并不包含该时间执行的事务，而是前一个事务，在进行还原的时候需要注意正确的位置

· --start-position | --stop-position：指定binlog位置节点，需要注意的是--stop-position所指定的位置节点并不包含该位置本身，而是前一个事务，在进行还原的时候需要注意正确的位置

· --skip-gtids=true：是否保留GTID信息，如果为true表示恢复数据时将这些事务看作新事务，会生成新的GTID。常用于目标数据库和源数据库有相同GTID信息的情况，需要指定为true跳过这些GTID，否则会因为这些GTID已经在源数据库执行过了而不会执行（根据GTID的特性，同一个GTID只能执行一次）。如果是恢复到其他实例并且不包含源实例的 GTID 信息，那么使用或者不使用都可以恢复成功

· -d | --database：从二进制日志文件中过滤出某个库的日记信息

mysqlbinlog命令示例

· 解析指定binlog或relaylog所有内容

mysqlbinlog -vvv --base64-output=decode-rows  master.bin.000003  > /tmp/master.bin.000003.txt    #将rows的日志内容解码，可用于数据恢复时查找正确的操作

· 解析指定日期范围内的binlog或relaylog

mysqlbinlog -vvv --base64-output=decode-rows --start-datetime='2018-02-13 11:37:00'  --stop-datetime='2018-02-15 12:00:00' master-bin.000001 > /tmp/master.bin.000001.txt

· 解析指定position范围内的binlog或relaylog

mysqlbinlog  -vvv --base64-output=decode-rows --start-position=3052 master-bin.000002    #显示从3052开始的所有内容  
mysqlbinlog  -vvv --base64-output=decode-rows --start-position=3052 --stop-position=3849 master-bin.000002    #显示3052-3849的内容

· 显示指定某个库的binlog或relaylog，并且对rows格式的日志解码

mysqlbinlog  -vvv --base64-output=decode-rows  --start-datetime='2018-10-12 14:42:00'  --stop-datetime='2018-10-12 14:45:00' --database=test_database mysql-bin.000008 > /tmp/bin.sql

· gtid模式下，导出多个binlog或relaylog文件中的数据

mysqlbinlog --include-gtids='yourgtid:1-100' mysql-bin.00001 mysql-bin.00002 > /tmp/gtid.sql  #假设101的GTID为drop table，就截取到100即可

mysqlbinlog命令结果

建议设置 binlog_rows_query_log_events 参数，用于输出原生的 DML 语句，如果不设置则不能显示 SQL 语句

三、如何正确删除binlog日志

由于二进制日志会随着数据库操作的增多而不断增长，在一段时间后需要删除一些无用的二进制日志。如果直接rm删除可能会破坏binlog的索引文件，所以需要用以下方式进行清除

· reset master命令：虽然可以清空所有binlog文件，但是会导致从库异常，主从架构下无法使用

· expire_logs_days变量：通过该变量可以指定自动删除日期，如果日志过多，在删除时会有IO过高问题，可能导致性能抖动

· purge命令：推荐方法，可以快速删除指定binlog

mysql > PURGE MASTER LOGS TO 'mysql-bin.000003'  #删除binlog到指定的文件为止
mysql > PURGE MASTER LOGS BEFORE '2020-12-21 00:00:00'  #删除指定日期之前的文件

四、使用binlog进行数据恢复

如果不小心删错了库或表，可以使用历史备份与备份之后的binlog日志进行数据的恢复。建议找一台机器恢复数据后再导出到生产环境中

· 模拟事故过程

1、建立一个数据库，并在其中创建一张表并插入数据

mysql > create database student;
mysql > use student;
mysql > create table student(id int,name varchar(10),age int);
mysql > insert into student values ('1','tanglu','28'),('2','zouxiaolu','28'),('3','beibei','28');

2、进行完整的备份，这样之前的数据已经有了备份

mysqldump -u root -p  --master-data=2 student > student.sql

3、再次插入三条数据

mysql> insert into student values('4','doudou','8');
Query OK, 1 row affected (0.01 sec)

mysql> insert into student values('5','mengmeng','28');      
Query OK, 1 row affected (0.00 sec)

mysql> insert into student values('6','haha','10');
Query OK, 1 row affected (0.01 sec)

4、模拟误操作，删除之前表格中的所有数据

mysql> delete from student ;

· 恢复数据过程

1、重启数据库或者执行flush-logs来获得一个新的二进制日志文件，这样故障操作日志都在之前的binlog文件里

mysql > flush logs;

2、用完整备份恢复数据，这样前面3条数据就恢复成功

mysql -u root -p student < student.sql

3、通过备份文件中的change master信息可以查找出全备时刻position位点，这里假设为355。通过mysqlbinlog或者show events 命令查找出误操作SQL的位点或GTID信息（通常误操作都在最后一个binlog文件中）。假设误操作的起始点为1294，所以只需要导出1294之前的语句用于恢复即可，通常是恢复到上一个事务COMMIT之后的位置。需要注意的是在用binlog进行数据恢复时，不能用mysqlbinlog工具解析出日志然后生成SQL文件来直接执行，因为有些语句的执行结果是依赖上下文的，直接执行的结果很可能是错误的。标准做法是用mysqlbinlog工具解析后通过管道传给MySQL执行，如果存在多个二进制日志，不建议一个一个恢复，而是一起导入。如果分两次操作就是开启两个session，如果刚好用到一个临时表，一个session退出，另一个session就会出错。示例如下： 

mysqlbinlog --database=linuxe --start-positon=355 --stop-position=1294 mysql-binlog.000003 | mysql -uroot -p123456;

本站所有文章均为原创，转载请注明出处！小站维护不易，如果对您有所帮助，希望能点击一下站内广告，谢谢！