Mysql Binlog

mysql

一、认识binlog

mysql binlog即mysql二进制日志或者归档日志，属于逻辑日志，记录了会引起数据库状态变化的操作信息，比如用户执行的DDL和DML语句，但是不包含select、show等查询语句，binlog中以事件(event)的形式保存这些操作的信息，还包含了事件的时间、开始以及结束位置。

在mysql的三层结构中，binlog属于server层的日志，也就是说binlog与存储引擎无关，不管是事务引擎innodb还是非事务引擎myisam都会产生binlog。对于事务表来说，一个事务可能包含多个事件，但是只有在提交事务的时候（commit前）才会一次性将这些事件写入文件（但可能还未刷盘，刷盘与sync_binlog参数有关），而对于非事务表来说每次执行完语句都会直接写入。

binlog主要用于数据复制和恢复，属于mysql的CDC方案(change data capture)。

二、binlog详解

binlog日志包括两类文件:

• binlog索引文件（文件名后缀为.index）：binlog的索引文件是一个文本文件，可以直接查看，其中内容为当前所有有效的的binlog文件列表
• binlog文件（文件名后缀为.00000*）：记录数据库所有的DDL和DML语句事件

binlog索引文件和binlog文件的命名方式可以通过参数进行设置，自行查询。

binlog文件的结构：
每个binlog文件以一个4字节的魔数开头，接着是一组Event，每个Event包含header和data两个部分，header记录了Event的创建时间，位置、服务器等信息，data部分记录的是该Event的具体内容，如具体数据的修改；

binlog文件中的第一个Event用于描述binlog文件的格式版本，这个格式就是event写入binlog文件的格式，第一个event的类型固定为format_description_event（mysql5.0之后版本），其余的Event按照第一个Event的格式版本写入，最后一个Event用于说明下一个binlog文件。如图所示：

binlog中所有event类型可以参考官方文档：https://dev.mysql.com/doc/internals/en/event-classes-and-types.html

binlog中事件记录的格式：
binlog中事件记录的格式取决于用户配置的记录格式。mysql支持三种格式类型，可以通过binlog-format参数进行指定：

• STATEMENT：基于SQL语句的复制（statement-based replication, SBR）
  这种格式记录的是用户执行的具体的sql语句，而不会记录数据的变化，优点是可以极大的减少binlog日志量，但是缺点也很明显，记录sql语句意味着只能在mysql中回放，而不能用于异构的系统。即使是在mysql中回放，也有一些特定的函数（比如uuid()或一些时间相关的函数）依赖于特定的环境，相同的sql语句有不同的执行结果，造成两边数据不一致。所以一般都不推荐使用这种格式。
• ROW：基于行的复制（row-based replication, RBR）
  这种格式对于DDL依然记录sql语句，但是对于DML则不再记录具体的sql语句，而记录数据的具体变化，缺点是会产生大量的binlog日志内容，但是优点无可比拟，因为它记录所有数据的变化细节，可以用于任何场景下的数据复制，一般推荐的格式也是这种。
• MIXED：混合模式复制（mixed-based replication, MBR）
  mixed模式就是statement和row模式的混合，在这种模式下一般的语句修改任然会使用statement格式也就是sql语句来保存，但是对于一些特定情况会使用row格式来存储，MySQL会根据执行的每一条具体的sql语句来区分对待记录的日志形式，也就是在Statement和Row之间选择一种。这种格式看起来是综合了statement和row模式各自的优点，是一种完美的记录格式，但其实不是的，使用这种模式仍然只能在mysql中回放binlog，不能用于异构系统的复制或同步。

三、与binlog密切相关的参数

与binlog相关的参数有很多，但是个人认为最重要的只有以下4个：

• log_bin：用于打开或者关闭binlog，只有打开之后mysql才会记录binlog
• binlog_format：用于设置binlog中事件的记录格式，可以设置为statement、row、mixed中的一种，按实际需要进行设置
• binlog_row_image：这个参数是与row模式配合使用的，这个参数默认是FULL，也就是记录完整的前镜像(before image)和后镜像(after image)，所谓前镜像和后镜像值某一行操作前和操作后的值。这个参数还可以设置为minimal和noblob，设置为minimal表示前镜像中值记录唯一识别列(主键、唯一键)的值，后镜像也只记录修改过的列，未修改的列则步进行记录。如果设置为noblob，就和full模式一样，仍然记录完整的前后镜像，但是如果含有blob、text这样的大字段，只要大字段不是唯一识别列且没有进行修改就不会记录，如果修改过了还是会记录的。
• sync_binlog：这个参数可以是0到n的任意数值，这个参数控制binlog的刷盘机制：sync_binlog=0，表示MySQL不控制binlog的刷新，由文件系统自己控制它的缓存的刷新，也就是文件系统空闲时再刷新，理论上这种情况性能最好，但是一旦mysql宕机，没有刷盘的binlog都将丢失；sync_binlog=1表示每次事务提交前都进行binlog刷盘，这种设置是最安全的，不会造成binlog的丢失，但也是性能最低的；sync_binlog=n(n>1)表示每n个事务提交再进行刷盘，所以也有丢失binlog的风险。一般推荐将sync_binlog设置为1。

四、binlog和事务日志的关系

事务日志指innodb的redo log和undo log，binlog是mysql server层面的日志，与存储引擎无关，任何存储引擎的表都会产生binlog；而事务日志是innodb存储引擎特有的；

innodb中redo log是物理日志，记录的是对数据页的操作；undo log是逻辑日志，记录的是回滚所需要的操作，比如一条insert，undo log对应到delete，undo log也会产生redo log。binlog是逻辑日志，记录的是用户对表的逻辑操作。

事务日志主要用来实现数据库的崩溃恢复，以及实现事务的ACD属性（隔离性用锁来实现），而binlog主要用于数据复制或者逻辑恢复。

4.1 binlog与redo log的一致性

当我们执行一个事务的时候，对于redo log和undo log，不会等到commit的时候一次性写入，而是边执行边写入的；对于binlog则是在commit前一次性写入的，写入之前都在缓存当中。
这是两个完全不同的过程，试想一下如果先写redo log后写binlog，假设在redo log写完binlog还没有写完的时候，MySQL进程异常重启，由于redo log已经写完，所以仍然能够把数据恢复回来，但是binlog没有记录这条语句，binlog中就缺失了一个事务；如果先写binlog后写redo log，在binlog写完而redo log没写完就crash，崩溃恢复以后这个事务被回滚，binlog中就多记录了一个事务，用binlog进行同步就会出现问题；

为了使redo log和binlog保持一致，mysql使用了两阶段提交的方式。事务总是begin...commit的过程，在最后提交事务的commit命令发出去的时候，redo log也需要记录commit命令的日志，但是这commit事件的日志记录被拆成了两个阶段：prepare和commit(此commit指两阶段提交的commit，上面的commit指事务的commit)，也就是说redo log先记录2PC的XID，并打上prepare的标志，然后等待binlog写入，binlog在记录事务的时候也会记录XID，binlog写完之后redo log进入2PC的commit阶段，也就将prepare标志改为commit标志，一旦改完意味着事务已经提交，这个时候binlog和redo log都已经写入。如果在prepare阶段mysql宕机，重启恢复的时候先通过XID判断对应的binlog是否存在，如果存在，则可以继续提交事，如果不存在则需要回滚。如果恢复的时候发现redo log都没有进入prepare阶段则直接回滚。

五、mysqlbinlog命令详解

使用mysqlbinlog工具可以查看binlog的具体内容，也可以进行mysql的逻辑备份与还原（还原只能到库级别，不能到表级别）
详细的使用方式见官网：https://dev.mysql.com/doc/refman/8.0/en/mysqlbinlog.html