首页 > mysql > mysql查看binlog format格式及查看选项
2017
06-08

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 参数。


最常用举例:mysqlbinlog /usr/local/my3306/binlog.000031 --base64-output=DECODE-ROWS -vv


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

image.png

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


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

image.png

--3.只加--verbose参数

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

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

image.png

同时加 --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

image.png

加 --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语句对应表的元数据信息(字段的数据类型等)

image.png

以上也就是为什么我们经常会通过一下的语句来分析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语句:

image.png

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

本文》有 0 条评论

留下一个回复