首页 > 代码库 > 【MySQL】binlog_format以及binlog事务记录分析

【MySQL】binlog_format以及binlog事务记录分析

2024-07-23 10:23:06   222人阅读

MySQL官方对于binlog_format参数的说明：

http://dev.mysql.com/doc/refman/5.5/en/binary-log-setting.html

binlog_format可以动态修改，官网对于动态修改主库配置时提醒谨慎操作，会导致复制关系异常。

【主库flush logs，binlog_format=‘STATEMENT‘】
【从库flush logs，binlog_format=‘MIXED‘】

【主库】# at 107#140919 14:17:50 server id 16024  end_log_pos 178       Query   thread_id=13    exec_time=0     error_code=0SET TIMESTAMP=1411107470/*!*/;BEGIN/*!*/;# at 178#140919 14:17:50 server id 16024  end_log_pos 281       Query   thread_id=13    exec_time=0     error_code=0use jiangxu/*!*/;SET TIMESTAMP=1411107470/*!*/;delete from t1 where c1 in (50,51,52)/*!*/;# at 281#140919 14:17:50 server id 16024  end_log_pos 353       Query   thread_id=13    exec_time=0     error_code=0SET TIMESTAMP=1411107470/*!*/;COMMIT/*!*/;【从库】# at 107#140919 14:17:50 server id 16024  end_log_pos 178       Query   thread_id=13    exec_time=253   error_code=0SET TIMESTAMP=1411107470/*!*/;BEGIN/*!*/;# at 178#140919 14:17:50 server id 16024  end_log_pos 281       Query   thread_id=13    exec_time=253   error_code=0use `jiangxu`/*!*/;SET TIMESTAMP=1411107470/*!*/;delete from t1 where c1 in (50,51,52)/*!*/;# at 281#140919 14:17:50 server id 16024  end_log_pos 353       Query   thread_id=13    exec_time=253   error_code=0SET TIMESTAMP=1411107470/*!*/;COMMIT/*!*/;

 

【主库flush logs，binlog_format=‘MIXED‘】
【从库flush logs，binlog_format=‘MIXED‘】

【主库】# at 107#140919 14:28:52 server id 16024  end_log_pos 178       Query   thread_id=1     exec_time=0     error_code=0SET TIMESTAMP=1411108132/*!*/;BEGIN/*!*/;# at 178#140919 14:28:52 server id 16024  end_log_pos 281       Query   thread_id=1     exec_time=0     error_code=0use jiangxu/*!*/;SET TIMESTAMP=1411108132/*!*/;delete from t1 where c1 in (40,41,42)/*!*/;# at 281#140919 14:28:52 server id 16024  end_log_pos 308       Xid = 14COMMIT/*!*/;【从库】# at 353#140919 14:28:52 server id 16024  end_log_pos 412       Query   thread_id=1     exec_time=253   error_code=0SET TIMESTAMP=1411108132/*!*/;BEGIN/*!*/;# at 412#140919 14:28:52 server id 16024  end_log_pos 515       Query   thread_id=1     exec_time=253   error_code=0SET TIMESTAMP=1411108132/*!*/;delete from t1 where c1 in (40,41,42)/*!*/;# at 515#140919 14:28:52 server id 16024  end_log_pos 542       Xid = 25540COMMIT/*!*/;

 

【主库flush logs，binlog_format=‘ROW‘】
【从库flush logs，binlog_format=‘MIXED‘】

【主库】# at 107#140919 14:41:21 server id 16024  end_log_pos 178       Query   thread_id=1     exec_time=0     error_code=0SET TIMESTAMP=1411108881/*!*/;BEGIN/*!*/;# at 178# at 225#140919 14:41:21 server id 16024  end_log_pos 225       Table_map: `jiangxu`.`t1` mapped to number 33#140919 14:41:21 server id 16024  end_log_pos 275       Delete_rows: table id 33 flags: STMT_END_F### DELETE FROM jiangxu.t1### WHERE###   @1=30 /* INT meta=0 nullable=0 is_null=0 */###   @2=‘a‘ /* VARSTRING(15) meta=15 nullable=1 is_null=0 */### DELETE FROM jiangxu.t1### WHERE###   @1=31 /* INT meta=0 nullable=0 is_null=0 */###   @2=‘a‘ /* VARSTRING(15) meta=15 nullable=1 is_null=0 */### DELETE FROM jiangxu.t1### WHERE###   @1=32 /* INT meta=0 nullable=0 is_null=0 */###   @2=‘a‘ /* VARSTRING(15) meta=15 nullable=1 is_null=0 */# at 275#140919 14:41:21 server id 16024  end_log_pos 302       Xid = 13COMMIT/*!*/;【从库】# at 542#140919 14:41:21 server id 16024  end_log_pos 601       Query   thread_id=1     exec_time=253   error_code=0SET TIMESTAMP=1411108881/*!*/;BEGIN/*!*/;# at 601# at 648#140919 14:41:21 server id 16024  end_log_pos 648       Table_map: `jiangxu`.`t1` mapped to number 1168#140919 14:41:21 server id 16024  end_log_pos 698       Delete_rows: table id 1168 flags: STMT_END_F### DELETE FROM `jiangxu`.`t1`### WHERE###   @1=30 /* INT meta=0 nullable=0 is_null=0 */###   @2=‘a‘ /* VARSTRING(15) meta=15 nullable=1 is_null=0 */### DELETE FROM `jiangxu`.`t1`### WHERE###   @1=31 /* INT meta=0 nullable=0 is_null=0 */###   @2=‘a‘ /* VARSTRING(15) meta=15 nullable=1 is_null=0 */### DELETE FROM `jiangxu`.`t1`### WHERE###   @1=32 /* INT meta=0 nullable=0 is_null=0 */###   @2=‘a‘ /* VARSTRING(15) meta=15 nullable=1 is_null=0 */# at 698#140919 14:41:21 server id 16024  end_log_pos 725       Xid = 25542COMMIT/*!*/;DELIMITER ;# End of log fileROLLBACK /* added by mysqlbinlog */;/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;

 

【主库flush logs，binlog_format=‘MIXED‘】
【从库flush logs，binlog_format=‘ROW‘】

注意动态修改从库binlog_format因为原session是MIXED，所以需要重建复制关系线程。
【主库】# at 554#140919 16:44:50 server id 16024  end_log_pos 625       Query   thread_id=3     exec_time=0     error_code=0SET TIMESTAMP=1411116290/*!*/;BEGIN/*!*/;# at 625#140919 16:44:50 server id 16024  end_log_pos 728       Query   thread_id=3     exec_time=0     error_code=0SET TIMESTAMP=1411116290/*!*/;delete from t1 where c1 in (13,14,15)/*!*/;# at 728#140919 16:44:50 server id 16024  end_log_pos 755       Xid = 44COMMIT/*!*/;【从库】# at 296#140919 16:44:50 server id 16024  end_log_pos 355       Query   thread_id=3     exec_time=253   error_code=0SET TIMESTAMP=1411116290/*!*/;BEGIN/*!*/;# at 355# at 402#140919 16:44:50 server id 16024  end_log_pos 402       Table_map: `jiangxu`.`t1` mapped to number 1168#140919 16:44:50 server id 16024  end_log_pos 452       Delete_rows: table id 1168 flags: STMT_END_F### DELETE FROM `jiangxu`.`t1`### WHERE###   @1=13 /* INT meta=0 nullable=0 is_null=0 */###   @2=‘a‘ /* VARSTRING(15) meta=15 nullable=1 is_null=0 */### DELETE FROM `jiangxu`.`t1`### WHERE###   @1=14 /* INT meta=0 nullable=0 is_null=0 */###   @2=‘a‘ /* VARSTRING(15) meta=15 nullable=1 is_null=0 */### DELETE FROM `jiangxu`.`t1`### WHERE###   @1=15 /* INT meta=0 nullable=0 is_null=0 */###   @2=‘a‘ /* VARSTRING(15) meta=15 nullable=1 is_null=0 */# at 452#140919 16:44:50 server id 16024  end_log_pos 479       Xid = 25570COMMIT/*!*/;

 

 

在binlog_format=MIXED时候，哪种改变会被按照ROW进行记录到binlog，参考官方说明：

http://dev.mysql.com/doc/refman/5.5/en/binary-log-mixed.html

所以看得出在MIXED的状态下基本上对于主库的DDL操作无法按照行变化进行记录。

对于行变化的日志记录磁盘IO肯定要比语句复制消耗要大，可以根据架构酌情配置。

 

【MySQL】binlog_format以及binlog事务记录分析


联系
我们