mysql 查看binlogformat_mysql binlog format 格式及查看选项

MySQL的binlog主要用于数据恢复及主从复制，binlog 通过binlog events记录了对数据库的修改。

binlog有3种记录格式: statement , row 和mixed，通过参数binlog_format配置。

binlog_format=STATEMENT，直接记录原始语句，存在nondeterministic的问题(如AUTO_INCREMENT，UUID等)，因此容易造成主从数据不一致。

binlog_format=ROW ，推荐的配置方式，将对数据修改的SQL语句转换成对应的行改变

binlog_format=MIXED ，默认情况下为STATEMENT，遇到特殊的SQL语句时转换为ROW格式

推荐的安全配置为ROW格式，ROW格式下原始的SQL语句不会直接记录到binlog中，而是通过等价的转化记录最终对行的修改。这样的好处一方面可以保证主从数据的一致性，另外当遇到执行很慢的复杂SQL但最终执行结果只改变了很少的行数据时可以更高效。比如执行了一个很长时间的DML，最终没有修改数据或者只修改了很少的数据，那binlog中只记录对改变行的修改，在从库端回放日志会更高效。

另外，ROW格式下有些类型的语句也是记录为STATEMENT的，例如DDL语句。

通过mysqlbinlog分析ROW格式的binlog时， 通常会用到--base64-output=DECODE-ROWS 以及 --verbose 参数。

mysql的--base64-output选项有3个可选值：AUTO，NEVER，DECODE-ROWS。

AUTO: 当不显示加--base64-output选项时，默认为AUTO方式，原始的记录binlog events的方式。如果要通过binlog恢复数据(mysqlbinlog log_file | mysql -h server_name)，必须使用AUTO方式

NEVER: 不显示binlog statements，遇到ROW格式的binlog直接报错

DECODE-ROWS: 压缩显示row格式events

--verbose: Reconstruct row events and display them as commented SQL statements, with table partition

information where applicable. If this option is given twice (by passing in either "-vv" or "--verbose --

verbose"), the output includes comments to indicate column data types and some metadata, and

informational log events such as row query log events if the binlog_rows_query_log_events

system variable is set to TRUE.

也就是通过伪代码的方式重构出行数据改变的等价的SQL语句

下面通过实验验证一下这两个参数的用法：

MySQL Server version: 5.7.31-log MySQL Community Server (GPL)

mysql> create table test(id int,name varchar(20));

Query OK, 0 rows affected (0.02 sec)

mysql>

mysql>

mysql> flush binary logs;

Query OK, 0 rows affected (0.01 sec)

mysql>

mysql> insert into test values(88,'Hunter');

Query OK, 1 row affected (0.01 sec)

mysql>

在insert一行数据之前，flush binlog,这样最后的binlog只有这一个insert操作。

1.不加任何参数：

mysqlbinlog /usr/local/my3306/binlog.000031

加参数 --base64-output=DECODE-ROWS ，可以看到binlog event直接被压缩看不到了

mysqlbinlog /usr/local/my3306/binlog.000031 --base64-output=DECODE-ROWS

--3.只加--verbose参数

mysqlbinlog /usr/local/my3306/binlog.000031 --verbose 或

mysqlbinlog /usr/local/my3306/binlog.000031 -v

可以看到既显示了原来的binlog events，也生成了转换之后的伪SQL语句

同时加 --base64-output=DECODE-ROWS 和 --verbose，实际上通过以上可以推测出，这种情况下不会显示原始的binlog events, 只会显示生成的sql语句:

mysqlbinlog /usr/local/my3306/binlog.000031 --base64-output=DECODE-ROWS --verbose

或

mysqlbinlog /usr/local/my3306/binlog.000031 --base64-output=DECODE-ROWS --v

加 --base64-output=DECODE-ROWS 和两个 --verbose

mysqlbinlog /usr/local/my3306/binlog.000031 --base64-output=DECODE-ROWS --verbose --verbose 或

mysqlbinlog /usr/local/my3306/binlog.000031 --base64-output=DECODE-ROWS -vv

会显示insert语句对应表的元数据信息(字段的数据类型等)

以上也就是为什么我们经常会通过一下的语句来分析ROW格式的binlog的原因：

mysqlbinlog /usr/local/my3306/binlog.000031 --base64-output=DECODE-ROWS -vv

另外，在ROW格式下，因为记录的是最终的行数据改变，而非原始的SQL statement， 所以一个SQL语句如果操作了N行数据，binlog中会转换为N个binlog events，如下：

mysql> select * from test;

+------+--------+

| id | name |

+------+--------+

| 88 | Hunter |

| 99 | Hunter |

| 77 | Hunter |

+------+--------+

3 rows in set (0.00 sec)

mysql> flush binary logs;

Query OK, 0 rows affected (0.02 sec)

mysql>

mysql> delete from test;

Query OK, 3 rows affected (0.01 sec)

mysql>

一个delete语句删除了3行数据，那在binlog中会记录3个binlog events, 翻译之后会有3个delete语句：

这也就是在主从复制环境下，master上一个语句操作大量数据，在slave上replay binlog时会有大量的events, 如果操作的表上没有索引的情况下，从库端每个行的操作都会变成全表扫描，容易造成主从复制延时，这种情况会在之后讨论。