Number of MySQL table columns affects binlog size with row-based reolication

 binlog_row_image


Introduced
5.6.2
Command-Line Format
--binlog-row-image=image_type
System Variable
Name
binlog_row_image=image_type
Variable Scope
Global, Session
Dynamic Variable
Yes
Permitted Values
Type
enumeration
Default
full
Valid Values
full (Log all columns)
minimal (Log only changed columns, and columns needed to identify rows)
noblob (Log all columns, except for unneeded BLOB and TEXT columns)



In MySQL row-based replication, each row change event contains two images, a “before” image whose columns are matched against when searching for the row to be updated, and an “after” image containing the changes. Normally, MySQL logs full rows (that is, all columns) for both the before and after images. However, it is not strictly necessary to include every column in both images, and we can often save disk, memory, and network usage by logging only those columns which are actually required.


Note

When deleting a row, only the before image is logged, since there are no changed values to propagate following the deletion. When inserting a row, only the after image is logged, since there is no existing row to be matched. Only when updating a row are both the before and after images required, and both written to the binary log.


For the before image, it is necessary only that the minimum set of columns required to uniquely identify rows is logged. If the table containing the row has a primary key, then only the primary key column or columns are written to the binary log. Otherwise, if the table has a unique key all of whose columns are NOT NULL, then only the columns in the unique key need be logged. (If the table has neither a primary key nor a unique key without any NULL columns, then all columns must be used in the before image, and logged.) In the after image, it is necessary to log only the columns which have actually changed.

In MySQL 5.6, you can cause the server to log full or minimal rows using the binlog_row_image system variable. This variable actually takes one of three possible values, as shown in the following list:



full: Log all columns in both the before image and the after image.


minimal: Log only those columns in the before image that are required to identify the row to be changed; log only those columns in the after image that are actually changed.


noblob: Log all columns (same as full), except for BLOB and TEXT columns that are not required to identify rows, or that have not changed.





Note

This variable is not supported by MySQL Cluster; setting it has no effect on the logging of NDB tables. (Bug #16316828)


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.   

================================================================

 Length Name
    ------ ----
  72251750 server1-bin.000001
   4381411 server1-bin.000002 <-- update t1 set last_modified=last_modified + interval 1 second;
    788494 server1-bin.000003 <-- update t2 set last_modified=last_modified + interval 1 second;
    229497 server1-bin.000004 <-- create table t3 (id integer auto_increment primary key, flag char(1));
    458175 server1-bin.000005 <-- update t3 set flag='y';
       810 server1-bin.000006 <-- alter table t3 add column c1 char(200) default 'a';alter table t3 add column c2 char(200) default 'b';alter table t3 add column c3 char(200) default 'c';
    850512 server1-bin.000007 <-- update t3 set c1='c',c2='b',c3='a';
    850512 server1-bin.000008 <-- update t3 set flag='z';
       194 server1-bin.000009
       297 server1-bin.index

mysql> create database test;
mysql> use test
mysql> create table t1 (id integer, c1 char(200), c2 char(200), c3 char(200), last_modified timestamp(6));
mysql> alter table t1 modify column id int auto_increment primary key;
mysql> create table t2 (id integer auto_increment primary key, last_modified timestamp(6));

mysql> flush logs;


mysql> update t1 set last_modified=last_modified + interval 1 second;
Query OK, 32550 rows affected (0.54 sec)
Rows matched: 32550  Changed: 32550  Warnings: 0
mysql> flush logs;

mysql> update t2 set last_modified=last_modified + interval 1 second;
Query OK, 32689 rows affected (0.36 sec)
Rows matched: 32689  Changed: 32689  Warnings: 0
mysql> flush logs;


mysql> create table t3 (id integer auto_increment primary key, flag char(1));
mysql> insert into t3 (flag) select 'x' from t1;
Query OK, 32550 rows affected (0.33 sec)
Records: 32550  Duplicates: 0  Warnings: 0
mysql> flush logs;

mysql> update t3 set flag='y';
Query OK, 32550 rows affected (0.87 sec)
Rows matched: 32550  Changed: 32550  Warnings: 0
mysql> flush logs;


mysql> alter table t3 add column c1 char(200) default 'a';
Query OK, 0 rows affected (0.58 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> alter table t3 add column c2 char(200) default 'b';
Query OK, 0 rows affected (0.98 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> alter table t3 add column c3 char(200) default 'c';
Query OK, 0 rows affected (1.46 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> flush logs;


mysql> select * from t3 limit 1\G
*************************** 1. row ***************************
  id: 1flag: y
  c1: a
  c2: b
  c3: c
1 row in set (0.00 sec)

mysql> update t3 set c1='a',c2='b',c3='c';
Query OK, 0 rows affected (0.11 sec)
Rows matched: 32550  Changed: 0  Warnings: 0
mysql> update t3 set c1='c',c2='b',c3='a';
Query OK, 32550 rows affected (1.02 sec)
Rows matched: 32550  Changed: 32550  Warnings: 0
mysql> flush logs;

mysql> update t3 set flag='z';
Query OK, 32550 rows affected (0.35 sec)
Rows matched: 32550  Changed: 32550  Warnings: 0
mysql> flush logs;
Query OK, 0 rows affected (0.02 sec)