binlog_format 为 row ,当 binlog_row_image 为full (默认值)时,所有的
数据列将会在写入事件后被记录到二进制日志中。创建表:
mysql> create table test_rbr_image( id int unsigned not null,
-> col1 int default null,
-> var2 char(3) default null,
-> comment text null);
Query OK, 0 rows affected (0.27 sec)
向表中插入数据行,单只有三个字段有值
mysql> show master status\G
*************************** 1. row ***************************
File: mysql-bin.000029
Position: 423
Binlog_Do_DB:
Binlog_Ignore_DB:
Executed_Gtid_Set:
1 row in set (0.00 sec)
mysql> INSERT into test_rbr_image(id,col1,var2) values(1,2,'3');
Query OK, 1 row affected (0.00 sec)
mysql> commit;
Query OK, 0 rows affected (0.07 sec)
查看日志,数据列的4个值度会出现在二进制日志中,第四列被设置为空
mysqlbinlog --base64-output=decode-rows --verbose --start-position=423 /mysqldata/mysql_data/mysql-bin.000029
………………
### INSERT INTO `huang`.`test_rbr_image`
### SET
### @1=1
### @2=2
### @3='3'
### @4=NULL
# at 676
2、当 binlog_row_image 为 minimal 时,只有发生变化的数据列会被添加到
二进制日志中:
mysql> set session binlog_row_image=minimal;
Query OK, 0 rows affected (0.00 sec)
mysql> show master status\G
*************************** 1. row ***************************
File: mysql-bin.000029
Position: 707
Binlog_Do_DB:
Binlog_Ignore_DB:
Executed_Gtid_Set:
1 row in set (0.00 sec)
mysql> INSERT into test_rbr_image(id) values(4);
Query OK, 1 row affected (0.00 sec)
mysql> commit;
Query OK, 0 rows affected (0.06 sec)
查看二进制日志,这个事件中只有一个列被指定:
### INSERT INTO `huang`.`test_rbr_image`
### SET
### @1=4
# at 954
3、binlog_row_image 为noblob 时,除非那些非文本列或blob 列被添加或修改,否则这些列
的数据在二进制日志中都会被忽略:
mysql> set session binlog_row_image=noblob;
Query OK, 0 rows affected (0.00 sec)
mysql> show master status\G
*************************** 1. row ***************************
File: mysql-bin.000029
Position: 985
Binlog_Do_DB:
Binlog_Ignore_DB:
Executed_Gtid_Set:
1 row in set (0.00 sec)
mysql> insert into test_rbr_image(id ,var2) values(5,'abc');
Query OK, 1 row affected (0.00 sec)
mysql> commit;
Query OK, 0 rows affected (0.05 sec)
查看二进制日志:
### INSERT INTO `huang`.`test_rbr_image`
### SET
### @1=5
### @2=NULL
### @3='abc'
# at 1236