解析MySQL binlog
一、binlog简介
binlog即binary log，二进制日志文件。它记录了数据库所有执行的ddl和dml语句（除了数据查询语句select、show等），以事件形式记录并保存在二进制文件中。
binlog主要有两个应用场景，一是用于复制，master把它的二进制日志传递给slaves来达到master-slave数据一致的目的。二是用于数据恢复，例如还原备份后，可以重新执行备份后新产生的binlog，使得数据库保持最新状态。除去这两个主要用途外，binlog可以用于异构系统之间数据的交互，binlog完整保存了一条记录的前项和后项记录，可以用dts服务，将mysql数据以准实时的方式抽取到底层数据平台，比如hbase、hive、spark等，打通oltp和olap。
binlog日志可以选择三种模式，分别是 statement、 row、 mixed，下面简单介绍下这三种模式：
- statement：基于sql语句的复制，每一条会修改数据的sql语句会记录到binlog中。该模式下产生的binlog日志量会比较少，但可能导致主从数据不一致。
- row：基于行的复制，不记录每一条具体执行的sql语句，仅需记录哪条数据被修改了，以及修改前后的样子。该模式下产生的binlog日志量会比较大，但优点是会非常清楚的记录下每一行数据修改的细节，主从复制不会出错。
- mixed：混合模式复制，以上两种模式的混合使用，一般的复制使用statement模式保存binlog，对于statement模式无法复制的操作使用row模式保存binlog，mysql会根据执行的sql语句选择日志保存方式。
binlog模式在mysql 5.7.7之前，默认为 statement，在之后的版本中，默认为row。这里建议采用row模式，因为row模式更安全，可以清楚记录每行数据修改的细节。
二、binlog相关参数
binlog默认情况下是不开启的，不过一般情况下，初始化的时候建议在配置文件中增加log-bin参数来开启binlog。
# 配置文件中增加log-bin配置 [mysqld] log-bin = binlog # 不指定路径默认在data目录下，也可以指定路径 [mysqld] log-bin = /data/mysql/logs/binlog # 查看数据库是否开启了binlog show variables like 'log_bin%';
开启binlog后，还需注意一些与binlog相关的参数，下面简单介绍下相关参数：
binlog_format
设置binlog模式，建议设为row。binlog_do_db
此参数表示只记录指定数据库的二进制日志，默认全部记录，一般情况下不建议更改。binlog_ignore_db
此参数表示不记录指定的数据库的二进制日志，同上，一般不显式指定。expire_logs_days
此参数控制二进制日志文件保留天数，默认值为0，表示不自动删除，可设置为0~99。可根据实际情况设置，比如保留15天或30天。mysql8.0版本可用binlog_expire_logs_seconds参数代替。max_binlog_size
控制单个二进制日志大小，当前日志文件大小超过此变量时，执行切换动作。此参数的最大和默认值是1gb，该设置并不能严格控制binlog的大小，尤其是binlog比较靠近最大值而又遇到一个比较大事务时，为了保证事务的完整性，不可能做切换日志的动作，只能将该事务的所有sql都记录进当前日志，直到事务结束。一般情况下可采取默认值。log_bin_trust_function_creators
当二进制日志启用后，此参数就会启用。它控制是否可以信任存储函数创建者，不会创建写入二进制日志引起不安全事件的存储函数。如果设置为0（默认值），用户不得创建或修改存储函数，除非它们具有除create routine或alter routine特权之外的super权限。建议设置为1。sync_binlog
控制mysql服务端将二进制日志同步到磁盘的频率，默认值为1。
设置为0，表示mysql不控制binlog的刷新，由文件系统自己控制它的缓存的刷新；
设置为1，表示每次事务提交，mysql都会把binlog刷下去，这是最安全的设置，但由于磁盘写入次数增加，可能会对性能产生负面影响；
设置为n，其中n为0或1以外的值，在进行n次事务提交以后，mysql将执行一次fsync之类的磁盘同步指令，将binlog文件缓存刷新到磁盘。
推荐设置为1，出于性能考虑也可酌情调整。
关于binlog操作与管理相关的sql也有很多，下面介绍下部分常用的语句：
三、解析binlog内容
前面说过，所有对数据库的修改都会记录在binglog中。但binlog是二进制文件，无法直接查看，想要更直观的观测它就要借助mysqlbinlog命令工具了，下面的内容主要介绍如何使用mysqlbinlog来解析binlog日志内容。
为了故事的顺利发展，我们首先切换下binlog，然后创建测试库、测试表，执行插入数据，更新数据。这些前置操作暂不展示，下面我们来看下如何解析并查看生成的binlog内容：
# 本次解析基于mysql8.0版本，实例已开启gtid，模式为row [root@centos logs]# mysqlbinlog --no-defaults --base64-output=decode-rows -vv binlog.000013 /*!50530 set @@session.pseudo_slave_mode=1*/; /*!50003 set @old_completion_type=@@completion_type,completion_type=0*/; ... ... #200708 16:52:09 server id 1003306 end_log_pos 1049 crc32 0xbcf3de39 query thread_id=85 exec_time=0 error_code=0 xid = 1514 use `bindb`/*!*/; set timestamp=1594198329/*!*/; set @@session.explicit_defaults_for_timestamp=1/*!*/; /*!80013 set @@session.sql_require_primary_key=0*//*!*/; create table `bin_tb` ( `increment_id` int(11) not null auto_increment comment '自增主键', `stu_id` int(11) not null comment '学号', `stu_name` varchar(20) default null comment '学生姓名', `create_time` timestamp not null default current_timestamp comment '创建时间', `update_time` timestamp not null default current_timestamp on update current_timestamp comment '修改时间', primary key (`increment_id`) ) engine=innodb default charset=utf8 comment='测试binlog' /*!*/; # at 1049 #200708 16:52:45 server id 1003306 end_log_pos 1128 crc32 0xf19ea0a9 gtid last_committed=2 sequence_number=3 rbr_only=yes original_committed_timestamp=1594198365741300 immediate_commit_timestamp=1594198365741300 transaction_length=468 /*!50718 set transaction isolation level read committed*//*!*/; # original_commit_timestamp=1594198365741300 (2020-07-08 16:52:45.741300 cst) # immediate_commit_timestamp=1594198365741300 (2020-07-08 16:52:45.741300 cst) /*!80001 set @@session.original_commit_timestamp=1594198365741300*//*!*/; /*!80014 set @@session.original_server_version=80019*//*!*/; /*!80014 set @@session.immediate_server_version=80019*//*!*/; set @@session.gtid_next= '0032d819-2d32-11ea-91b5-5254002ae61f:24883'/*!*/; # at 1128 #200708 16:52:45 server id 1003306 end_log_pos 1204 crc32 0x5b4b03db query thread_id=85 exec_time=0 error_code=0 set timestamp=1594198365/*!*/; begin /*!*/; # at 1204 #200708 16:52:45 server id 1003306 end_log_pos 1268 crc32 0xd4755d50 table_map: `bindb`.`bin_tb` mapped to number 139 # at 1268 #200708 16:52:45 server id 1003306 end_log_pos 1486 crc32 0x274cf734 write_rows: table id 139 flags: stmt_end_f ### insert into `bindb`.`bin_tb` ### set ### @1=1 /* int meta=0 nullable=0 is_null=0 */ ### @2=1001 /* int meta=0 nullable=0 is_null=0 */ ### @3='from1' /* varstring(60) meta=60 nullable=1 is_null=0 */ ### @4=1594198365 /* timestamp(0) meta=0 nullable=0 is_null=0 */ ### @5=1594198365 /* timestamp(0) meta=0 nullable=0 is_null=0 */ ### insert into `bindb`.`bin_tb` ### set ### @1=2 /* int meta=0 nullable=0 is_null=0 */ ### @2=1002 /* int meta=0 nullable=0 is_null=0 */ ### @3='dfsfd' /* varstring(60) meta=60 nullable=1 is_null=0 */ ### @4=1594198365 /* timestamp(0) meta=0 nullable=0 is_null=0 */ ### @5=1594198365 /* timestamp(0) meta=0 nullable=0 is_null=0 */ ... # at 1486 #200708 16:52:45 server id 1003306 end_log_pos 1517 crc32 0x0437e777 xid = 1515 commit/*!*/; ... # at 1596 #200708 16:54:35 server id 1003306 end_log_pos 1681 crc32 0x111539b6 query thread_id=85 exec_time=0 error_code=0 set timestamp=1594198475/*!*/; begin /*!*/; # at 1681 #200708 16:54:35 server id 1003306 end_log_pos 1745 crc32 0x6f0664ee table_map: `bindb`.`bin_tb` mapped to number 139 # at 1745 #200708 16:54:35 server id 1003306 end_log_pos 1939 crc32 0xfafe7ae8 update_rows: table id 139 flags: stmt_end_f ### update `bindb`.`bin_tb` ### where ### @1=5 /* int meta=0 nullable=0 is_null=0 */ ### @2=1005 /* int meta=0 nullable=0 is_null=0 */ ### @3='dsfsdg' /* varstring(60) meta=60 nullable=1 is_null=0 */ ### @4=1594198365 /* timestamp(0) meta=0 nullable=0 is_null=0 */ ### @5=1594198365 /* timestamp(0) meta=0 nullable=0 is_null=0 */ ### set ### @1=5 /* int meta=0 nullable=0 is_null=0 */ ### @2=1005 /* int meta=0 nullable=0 is_null=0 */ ### @3=null /* varstring(60) meta=60 nullable=1 is_null=1 */ ### @4=1594198365 /* timestamp(0) meta=0 nullable=0 is_null=0 */ ### @5=1594198475 /* timestamp(0) meta=0 nullable=0 is_null=0 */ ### update `bindb`.`bin_tb` ### where ### @1=6 /* int meta=0 nullable=0 is_null=0 */ ### @2=1006 /* int meta=0 nullable=0 is_null=0 */ ### @3='fgd' /* varstring(60) meta=60 nullable=1 is_null=0 */ ### @4=1594198365 /* timestamp(0) meta=0 nullable=0 is_null=0 */ ### @5=1594198365 /* timestamp(0) meta=0 nullable=0 is_null=0 */ ### set ### @1=6 /* int meta=0 nullable=0 is_null=0 */ ### @2=1006 /* int meta=0 nullable=0 is_null=0 */ ### @3=null /* varstring(60) meta=60 nullable=1 is_null=1 */ ### @4=1594198365 /* timestamp(0) meta=0 nullable=0 is_null=0 */ ### @5=1594198475 /* timestamp(0) meta=0 nullable=0 is_null=0 */ ... # at 1939 #200708 16:54:35 server id 1003306 end_log_pos 1970 crc32 0x632a82b7 xid = 1516 commit/*!*/; 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中详细记录了每条sql执行产生的变化， 并且包括执行时间、pos位点、server_id等系统值。
关于mysqlbinlog工具的使用技巧还有很多，例如只解析对某个库的操作或者某个时间段内的操作等。简单分享几个常用的语句，更多操作可以参考官方文档。
mysqlbinlog --no-defaults --base64-output=decode-rows -vv binlog.000013 > /tmp/bin13.sql
将解析到的sql导入文件中mysqlbinlog --no-defaults --base64-output=decode-rows -vv --database=testdb binlog.000013
只解析某个库的操作mysqlbinlog --no-defaults --base64-output=decode-rows -vv --start-datetime=“2020-01-11 01:00:00” --stop-datetime=“2020-01-11 23:59:00” binlog.000008
解析指定时间段内的操作mysqlbinlog --no-defaults --base64-output=decode-rows -vv --start-position=204136360 --stop-position=204136499 binlog.000008
解析指定pos位点内的操作mysqlbinlog --no-defaults --start-position=204136360 --stop-position=204136499 binlog.000008 | mysql -uroot -pxxxx testdb
在指定库中恢复指定位点间的操作
四、总结
不知不觉写的挺长了，本文讲述了各类binlog相关知识点，希望你读完会对binlog有更深的认识。其实最重要的还是实践，只有多学多用才能更好的掌握。这么硬核的知识，希望大家用到的时候可以拿来读读，欢迎各位转发分享，让更多人看到。
以上就是解析mysql binlog的详细内容，更多关于mysql binlog的资料请关注其它相关文章！