一、认识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属性(隔离性用mvcc和锁来实现),而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