mysql binlogrowimage_mysql binlog_row_image的选择

其含义为

The default value is full. In MySQL 5.5 and earlier, full row images are always used for both before images and after images. If you need to replicate from a MySQL 5.6 (or later) master to a slave running a previous version of MySQL, the master should always use this value.

When using minimal or noblob, deletes and updates are guaranteed to work correctly for a given table if and only if the following conditions are true for both the source and destination tables:

All columns must be present and in the same order; each column must use the same data type as its counterpart in the other table.

The tables must have identical primary key definitions.

(In other words, the tables must be identical with the possible exception of indexes that are not part of the tables' primary keys.)

If these conditions are not met, it is possible that the primary key column values in the destination table may prove insufficient to provide a unique match for a delete or update. In this event, no warning or error is issued; the master and slave silently diverge, thus breaking consistency.

Setting this variable has no effect when the binary logging format is STATEMENT. When binlog_format is MIXED, the setting for binlog_row_image is applied to changes that are logged using row-based format, but this setting no effect on changes logged as statements.

Setting binlog_row_image on either the global or session level does not cause an implicit commit; this means that this variable can be changed while a transaction is in progress without affecting the transaction.

一般来说，我们认为主从配置的时候，表结构一定是相同的，否则肯定就借助第三方的逻辑复制工具了。所以，对于某些update很频繁的系统，尤其是金融行业大量宽表设计的时候，这个值应该设置为minimal以最大化性能。

为FULL时，使用mysqlbinlog，我们可以看到bin log里面的内容如下：

BEGIN

/*!*/;

# at428854671# at428856248#170112 15:18:34 server id 1 end_log_pos 428856432 Table_map: `db_act`.`tb_act_stock_quotation` mapped to number 123# at428856432#170112 15:18:34 server id 1 end_log_pos 428857168 Update_rows: table id 123flags: STMT_END_F

###UPDATE`db_act`.`tb_act_stock_quotation`

###WHERE###@1=9875###@2=20170112###@3='溢多利'###@4='300381'###@5=2###@6=28.390000000###@7=23.230000000###@8=0.000000000000###@9=25.810000000###@10=0.000000000###@11=0.000000000###@12=25.810000000###@13=0.000000000###@14=0.000000000###@15=0.00###@16=0.0000###@17=0.000000000###@18=0.000000000###@19=0.000000000000###@20='0'###@21=11###@22=1###@23=93730###@24=0.000000000###@25=0.00###@26=0.000000000###@27=0.00###@28=0.000000000###@29=0.00###@30=0.000000000###@31=0.00###@32=0.000000000###@33=0.00###@34=0.000000000###@35=0.00###@36=0.000000000###@37=0.00###@38=0.000000000###@39=0.00###@40=0.000000000###@41=0.00###@42=0.000000000###@43=0.00###SET###@1=9875###@2=20170112###@3='溢多利'###@4='300381'###@5=2###@6=28.390000000###@7=23.230000000###@8=0.000000000000###@9=25.810000000###@10=0.000000000###@11=0.000000000###@12=25.810000000###@13=0.000000000###@14=0.000000000###@15=0.00###@16=0.0000###@17=0.000000000###@18=0.000000000###@19=0.000000000000###@20='0'###@21=11###@22=1###@23=151834###@24=0.000000000###@25=0.00###@26=0.000000000###@27=0.00###@28=0.000000000###@29=0.00###@30=0.000000000###@31=0.00###@32=0.000000000###@33=0.00###@34=0.000000000###@35=0.00###@36=0.000000000###@37=0.00###@38=0.000000000###@39=0.00###@40=0.000000000###@41=0.00###@42=0.000000000###@43=0.00# at428857168#170112 15:18:34 server id 1 end_log_pos 428857195 Xid = 719452532

COMMIT/*!*/;

当改成MINIMAL时，其中的内容如下：

BEGIN

/*!*/;

# at2818# at4075#170113 10:16:10 server id 1 end_log_pos 4154 Table_map: `db_act`.`tb_act_t0traderthresholdinfo` mapped to number 116# at4154#170113 10:16:10 server id 1 end_log_pos 4589 Update_rows: table id 116flags: STMT_END_F

###UPDATE`db_act`.`tb_act_t0traderthresholdinfo`

###WHERE###@1=890###SET###@5=-8750.4083###@6=101610###@7='1'###UPDATE`db_act`.`tb_act_t0traderthresholdinfo`

###WHERE###@1=891###SET###@5=-860.0700###@6=101610###@7='1'###UPDATE`db_act`.`tb_act_t0traderthresholdinfo`

###WHERE###@1=892###SET###@5=2562.3800###@6=101610###@7='1'###UPDATE`db_act`.`tb_act_t0traderthresholdinfo`

###WHERE###@1=893###SET###@5=-318.7600###@6=101610###@7='1'###UPDATE`db_act`.`tb_act_t0traderthresholdinfo`

###WHERE###@1=894###SET###@5=-1475.2600###@6=101610###@7='1'###UPDATE`db_act`.`tb_act_t0traderthresholdinfo`

###WHERE###@1=895###SET###@5=84.8950###@6=101610###@7='1'###UPDATE`db_act`.`tb_act_t0traderthresholdinfo`

###WHERE###@1=896###SET###@5=-112.0700###@6=101610###@7='1'###UPDATE`db_act`.`tb_act_t0traderthresholdinfo`

###WHERE###@1=897###SET###@5=620.0000###@6=101610###@7='1'###UPDATE`db_act`.`tb_act_t0traderthresholdinfo`

###WHERE###@1=898###SET###@5=-19.2300###@6=101610###@7='1'###UPDATE`db_act`.`tb_act_t0traderthresholdinfo`

###WHERE###@1=899###SET###@5=131.8100###@6=101610###@7='1'###UPDATE`db_act`.`tb_act_t0traderthresholdinfo`

###WHERE###@1=900###SET###@5=-247.5800###@6=101610###@7='1'###UPDATE`db_act`.`tb_act_t0traderthresholdinfo`

###WHERE###@1=901###SET###@5=245.8000###@6=101610###@7='1'###UPDATE`db_act`.`tb_act_t0traderthresholdinfo`

###WHERE###@1=902###SET###@5=232.1000###@6=101610###@7='1'# at4589# at4968#170113 10:16:10 server id 1 end_log_pos 5047 Table_map: `db_act`.`tb_act_t0traderthresholdinfo` mapped to number 116# at5047#170113 10:16:10 server id 1 end_log_pos 5091 Update_rows: table id 116flags: STMT_END_F

###UPDATE`db_act`.`tb_act_t0traderthresholdinfo`

###WHERE###@1=894###SET###@7='3'# at5091#170113 10:16:10 server id 1 end_log_pos 5118 Xid = 771

COMMIT/*!*/;

# at5118#170113 10:16:10 server id 1 end_log_pos 5179 Anonymous_GTID last_committed=4 sequence_number=7

SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;

# at5179#170113 10:16:10 server id 1 end_log_pos 5257 Query thread_id=3 exec_time=0 error_code=0

SET TIMESTAMP=1484273770/*!*/;BEGIN

/*!*/;

# at5257# at5474#170113 10:16:10 server id 1 end_log_pos 5554 Table_map: `db_sys`.`tb_sys_task` mapped to number 108# at5554#170113 10:16:10 server id 1 end_log_pos 5616 Update_rows: table id 108flags: STMT_END_F

###UPDATE`db_sys`.`tb_sys_task`

###WHERE###@1=27###SET###@5='1'###@9=20170113###@10=101610###@11=20170113###@12=101610# at5616#170113 10:16:10 server id 1 end_log_pos 5643 Xid = 888

COMMIT/*!*/;

可以发现，只记录了PK和修改的字段，虽然可以大大减少日志大小，但是也要注意，可能会导致一些非标准操作被跳过了，比如某些操作禁用了bin log导致值发生了变化，在FULL模式下，因为进行完整的行image匹配，所以会出错，但是在MINIMAL下，则不会出现失败的情况，但是逻辑可能会出错(如果两个事务更改了不同的字段，一个禁用了bin log的话)。

最近因为风控剥离到备库，使用了replicate-rewrite-db和replicate-wild-do-table(因为某些规范的原因)被搞死了，每天有异常数据，看来这特征还是不够稳定，而且5.6好像就不生效。