【MySQL运维】二进制日志binlog的查看、删除等日常管理教程

一、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 开始的，经过解析后的日志内容如下图：

二、MySQL 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位置的起始或者停止位点，如果在解析多个binlog的时候指定了停止位置，那么这个位点只对最后一个binlog生效，前面的binlog文件都是全量解析；如果指定的是开始位置则对第一个文件生效，后续的binlog文件都是全量解析。--stop-position所指定的位置节点并不包含该位置本身，而是前一个事务，在进行还原的时候需要注意正确的位置。如果指定的--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即可

三、MySQL binlog解析结果解读

/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;

# at 4
#221006 13:17:16 server id 30228  end_log_pos 123 CRC32 0x36c38dbb 	Start: binlog v 4, server v 5.7.38-log created 221006 13:17:16 at startup
# Warning: this binlog is either in use or was not closed properly.
ROLLBACK/*!*/;

# at 123
#221006 13:17:16 server id 30228  end_log_pos 194 CRC32 0xa6c475ce 	Previous-GTIDs
# 9e70471a-4175-11ed-b3b9-000c29d026c5:1-184

# at 194
#221006 13:17:36 server id 30228  end_log_pos 259 CRC32 0xe306bc18 	GTID	last_committed=0	sequence_number=1	rbr_only=no
SET @@SESSION.GTID_NEXT= '9e70471a-4175-11ed-b3b9-000c29d026c5:185'/*!*/;

# at 259    #事务起始的位置偏移量
#221006 13:17:36 server id 30228  end_log_pos 356 CRC32 0x0a97bb51 	Query	thread_id=2	exec_time=0	error_code=0  #事务提交时间与结束偏移量
SET TIMESTAMP=1665033456/*!*/;
SET @@session.pseudo_thread_id=2/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=1073741824/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C utf8mb4 *//*!*/;
SET @@session.character_set_client=45,@@session.collation_connection=45,@@session.collation_server=45/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
create database test1            #具体的事务
/*!*/;

# at 356
#221006 13:18:59 server id 30228  end_log_pos 421 CRC32 0xb5403613 	GTID	last_committed=1	sequence_number=2	rbr_only=no
SET @@SESSION.GTID_NEXT= '9e70471a-4175-11ed-b3b9-000c29d026c5:186'/*!*/;

# at 421
#221006 13:18:59 server id 30228  end_log_pos 543 CRC32 0x86db0796 	Query	thread_id=3	exec_time=0	error_code=0
use `test1`/*!*/;
SET TIMESTAMP=1665033539/*!*/;
create table students(id int,name varchar(10))    #具体的事务
/*!*/;
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

建议设置 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'  #删除指定日期之前的文件