二进制日志

简介

binlog是mysql server层存储的二进制日志，主要用于记录更新操作：

所有数据库表结构变更DDL（例如create、alter、drop）；
表数据修改DML（insert、update、delete）。

日志类型

binlog日志包含：

二进制日志索引文件（文件后缀名=.index），是个普通文件，文件内容如下：

image
二进制日志文件（文件后缀名=.00000*），用于记录更新操作。

写入流程

当事务表存储引擎时，所有未提交的二进制日志会被记录到一个缓存（通过binlog_cache_size配置缓存大小，默认32K）中去，等待事务提交时直接将缓存中的二进制日志刷新到磁盘二进制文件。

binlog_cache_size是基于会话的，因此设置的大小一定要消息。可以通过binlog_cache_use(写入缓存的次数)、binlog_cache_disk_use(写入临时文件的次数)，判断当前的设置是否合适，太小会写到临时文件影响性能，太大会造成内存浪费。

默认的情况下，二进制日志并不是在每次写的时候都刷新到磁盘中。因此，当数据库发生宕机的时候，就会可能造成数据的丢失，这会给恢复和复制带来问题。因此可以呀通过sync_binlog=N（默认是0）设置写入N次就同步一次到磁盘。

不过即使将sync_binlog设置成1，也会出现问题。当使用InnoDB存储引擎时，在一个事务发出COMMIT操作之前，先将二进制日志写入磁盘，但如果此时提交动作还没有在InnoDB存储redo log写入，并且此时数据库发生了宕机。下次启动时，该事务会被回滚掉，但是二进制日志已经记录了该信息，就会造成复制时数据不一致。因此，需要保证binlog与redo log分布式事务的一致性，可以使用XA事务保证一致性，通过innodb_support_xa=ON（默认开启）开启。

日志格式

binlog格式有三种：row，statement，mixed。

可通过show variables like '%binlog_format%' 查看当前binlog日志格式。

row：非常清晰的记录下每行数据的修改细节，不需要记录上下文相关信息。
- 优点：不会出现某些特定情况下的存储过程、函数、触发器的调用和触发无法被正确复制的问题。
- 缺点：会产生大量的日志，尤其是alter table会记录所有行的变更细节。
statement：记录更新操作的sql语句。
- 优点：相对row格式，减少了日志，节约IO，提高性能。
- 缺点：为了保证sql语句能在slave上正确执行，必须记录上下文信息，以保证所有语句能在slave得到和在master端执行时候相同的结果；另外，主从复制时，存在部分函数（如sleep）及存储过程在slave上会出现与master结果不一致的情况。
mixed：混合格式，实际上是row与statement的结合。mysql会根据执行的每一条具体的sql语句来区分对待记录的日志形式，也就是在statement和row之间选择一种。

新版本的mysql对row格式也被做了优化，并不是所有的修改都会以row格式来记录，像遇到表结构变更的时候就会以statement格式来记录，如果sql语句确实就是update或者delete等修改数据的语句，那么还是会记录所有行的变更；因此，现在一般使用row格式即可。

对于row格式来说，mysql5.6新增binlog_row_image减少了DML操作产生的日志量：

旧版逻辑：当表含有blob或者text类型的字段，update的时候，即使不更新这些字段，也会被记录到binlog中，会导致日志庞大。
5.6新版逻辑：binlog_row_image提供full、minimal、nolob三个可选值。
- full：记录所有字段信息，跟旧版一样。
- minimal：只记录被修改的列信息。
- nolob：记录除了blob和text以外的所有字段。

二进制日志