共计 8290 个字符，预计需要花费 21 分钟才能阅读完成。
这篇文章主要讲解了“什么是 MySQL binlog”，文中的讲解内容简单清晰，易于学习与理解，下面请大家跟着丸趣 TV 小编的思路慢慢深入，一起来研究和学习“什么是 MySQL binlog”吧！
1.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 模式更安全，可以清楚记录每行数据修改的细节。
2.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 也有很多，下面介绍下部分常用的语句：
SQL 语句语句含义 show master status 查看当前最新的一个 binlog 日志的编号名称，及最后一个事件结束的位置 show binary logs 查看目前保留的所有 binlog 日志列表及大小 flush logs 刷新 binlog，此刻开始产生一个新编号的 binlog 日志文件 purge binary logs before‘2020-07-01 12:00:00’手动清理指定时间之前的 binlog 日志 purge binary logs to‘binlog.000012’将指定日志文件之前的日志清除 reset master 清空所有的 binlog 日志，慎用 show binlog events in‘binlog.000030’查看指定的 binlog 日志 eventshow binlog events in‘binlog.000030’from 931 从指定的位置开始，查看指定的 binlog 日志 show binlog events in‘binlog.000030’from 931 limit 2 从指定的位置开始，查看指定的 binlog 日志，限制查询的 enent 数 3. 解析 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
在指定库中恢复指定位点间的操作
感谢各位的阅读，以上就是“什么是 MySQL binlog”的内容了，经过本文的学习后，相信大家对什么是 MySQL binlog 这一问题有了更深刻的体会，具体使用情况还需要大家实践验证。这里是丸趣 TV，丸趣 TV 小编将为大家推送更多相关知识点的文章，欢迎关注！
