mysql binlog格式分析

一、Binlog类型

binlog的格式有三种，这也反应了mysql的复制技术：基于SQL语句的复制(statement-based replication, SBR)，基于行的复制(row-based replication, RBR)，混合模式复制(mixed-based replication, MBR)。相应地，binlog的格式也有三种：

STATEMENT

ROW

MIXED

使用命令查看mysql binlog的格式

mysql>showvariables like 'binlog_format'  

 

使用mysqlbinlog解析的binlog：

MIXED（STATEMENT）：

# at 193（开始位置）

#110708 10:03:06（时间截） server id（产生该事件的服务id） 1  end_log_pos（日志的结束位置） 280  Query（事件类型）  thread_id=10    exec_time=0     error_code=0

SETTIMESTAMP=1310090586/*!*/;

insert into tvalues(17)

/*!*/;

 

ROW模式：

BEGIN

/*!*/;

# at 174

# at 214

#110708 10:49:22server id 1  end_log_pos 214   Table_map: `test`.`t` mapped to number 14

#110708 10:49:22server id 1  end_log_pos 248   Write_rows: table id 14 flags: STMT_END_F

 

BINLOG '

MnAWThMBAAAAKAAAANYAAAAAAA4AAAAAAAEABHRlc3QAAXQAAQMAAQ==

MnAWThcBAAAAIgAAAPgAAAAAAA4AAAAAAAEAAf/+MgAAAA==

'/*!*/;

# at 248

#110708 10:49:22server id 1  end_log_pos 317   Query  thread_id=1     exec_time=0     error_code=0

SETTIMESTAMP=1310093362/*!*/;

COMMIT

 

STATEMENT是基于sql语句级别的binlog,每一条修改数据的sql都会被保存到binlog里；ROW是基于行级别的,他会记录每一行记录的变化,就是将每一行的修改都记录到binlog里面,记录的非常详细，但sql语句并没有在binlog里,在replication里面也不会因为存储过程触发器等造成Master-Slave数据不一致的问题,但是有个致命的缺点日志量比较大.由于要记录每一行的数据变化,当执行update语句后面不加where条件的时候或alter table的时候,产生的日志量是相当的大。MIXED:在默认情况下是statement,但是在某些情况下会切换到row状态，如当一个DML更新一个ndb引擎表，或者是与时间用户相关的函数等。在主从的情况下，主机上now,uuid函数执行在备机执行不一致，所以对于这种情况就必须把STATEMENT模式更改为ROW模式，因为ROW模式会直接写值而不是写语句。同样ROW模式还可以减少从机的相关计算，如在主机中存在统计写入等操作时，从机就可以免掉该计算把值直接写入从机。当然从从业务角度来说，ROW还有其深远意义。

 

二、binlog记录

每个binlog的开始都是由4个字节：fe 62 69 6e，组成的魔数（后面三个字节就是bin）。

然后接下来的就是一条记录的内容它包括：Common-Header，这部分不同版本的大小不一样，4.0以上的都是19个字节。在这个之后就是BODY。

Common-Header格式：（单位：字节）

Timestamp(4)

Type(1)

Server_id(4)

Total_size(4)

End_log_pos(4)

Flag(2)

Timestamp:从1970开始

Type:此log event type如FORMAT_DESCRIPTION_EVENT、QUERY、LOAD_EVENT等，其中每个binlog的第一条记录的类型都是FORMAT_DESCRIPTION_EVENT，它记录了该binlog的相关信息，如版本，这些信息对于后序分析binlog记录是有用的，所以对于任务要读取binlog的内容的工具都必须先读取第一条记录。QUERY包括我们经常操作的如：create,drop,update,insert等。

Server_id：创建这个事件的server id。防止循环主从导致的主机被从写。The master's server id (is preserved in therelay log; used to prevent from infinite loops in circular replication).

Total_size：该记录的大小，包括common_header及body。

End_log_pos：此下一条记录的开始位置。也是此条记录结束位置的上一个字节。

Flag：标志位。

 

QUERY类型的记录：

QUERY类型的记录除了开始的common-header之外，在body的开头是一个Post-header，然后之后才是真正的body内容。

Query Post-Header：（单位字节）

Thread_id(4)

Exec_time(4)

Db_len(1)

Error_code(2)

Status_var_len(2)

Thread_id：is used to distinguish temporary tables that belong to differentclients.

Exec_time：The time from whenthe query started to when it was logged in the binlog, in seconds.QUERY到达到这个binlog事件生成的时间间隔。

Db_len：当前数据库的名称长度。

Error_code：执行出错的错误号。