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, 如果操作的表上没有索引的情况下,从库端每个行的操作都会变成全表扫描,容易造成主从复制延时,这种情况会在之后讨论。