mysql binlog_format 适时修改

--mysql允许在session或者global级别动态设置binlog_format的值，做在更新很多行时，可以设置 binlog_format = 'STATEMENT' 以加快数据应用到备库上
 A session that makes many small changes to the database might want to use row-based logging.
 A session that performs updates that match many rows in the WHERE clause might want to use statement-based logging because it will be more efficient to log a few statements than many rows.
 Some statements require a lot of execution time on the master, but result in just a few rows being modified. It might therefore be beneficial to replicate them using row-based logging.

MariaDB [test]>  show variables like 'BINLOG_FORMAT';                        
+---------------+-----------+
| Variable_name | Value     |
+---------------+-----------+
| binlog_format | STATEMENT |
+---------------+-----------+
MariaDB [test]> insert into t select seq,concat('rudy',seq) from seq_1_to_10;
Query OK, 10 rows affected (0.44 sec)
--此时binlog中的内容是sql形式的
[root@rudy_01 3307]# mysqlbinlog binlog.000010 
BEGIN
/*!*/;
# at 706
#160201 15:46:43 server id 11  end_log_pos 834  Query   thread_id=29    exec_time=0     error_code=0
SET TIMESTAMP=1454312803/*!*/;
insert into t select seq,concat('rudy',seq) from seq_1_to_10
/*!*/;
# at 834
#160201 15:46:43 server id 11  end_log_pos 861  Xid = 263
COMMIT/*!*/;

MariaDB [test]> set session binlog_format='ROW';
Query OK, 0 rows affected (0.00 sec)
MariaDB [test]>  show variables like 'BINLOG_FORMAT';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | ROW   |
+---------------+-------+
MariaDB [test]> insert into t select seq,concat('rudy',seq) from seq_1_to_10;
Query OK, 10 rows affected (0.44 sec)

--修改 binlog_format = 'row' 后，其binlog的内容如下
[root@rudy_01 3307]# mysqlbinlog binlog.000010 
BEGIN
/*!*/;
# at 899
#160201 15:47:10 server id 11  end_log_pos 942  Table_map: `test`.`t` mapped to number 30
# at 942
#160201 15:47:10 server id 11  end_log_pos 1082         Write_rows: table id 30 flags: STMT_END_F
BINLOG '
fg2vVhMLAAAAKwAAAK4DAAAAAB4AAAAAAAEABHRlc3QAAXQAAgMPAh4AAw==
fg2vVhcLAAAAjAAAADoEAAAAAB4AAAAAAAEAAv/8AQAAAAVydWR5MfwCAAAABXJ1ZHky/AMAAAAF
cnVkeTP8BAAAAAVydWR5NPwFAAAABXJ1ZHk1/AYAAAAFcnVkeTb8BwAAAAVydWR5N/wIAAAABXJ1
ZHk4/AkAAAAFcnVkeTn8CgAAAAZydWR5MTA=
'/*!*/;
# at 1082
#160201 15:47:10 server id 11  end_log_pos 1109         Xid = 266
COMMIT/*!*/;


--注意在以下情况下就不允许动态修改binlog_format的值了
? From within a stored function or a trigger
? If the session is currently in row-based replication mode and has open temporary tables
--对于innodb的表，如果其事务隔离级别是READ COMMITTED or READ UNCOMMITTED，则binlog_format不能设置成STATEMENT
If you are using InnoDB tables and the transaction isolation level is READ COMMITTED or READ UNCOMMITTED, only row-based logging can be used. 
MariaDB [test]> set tx_isolation='READ-COMMITTED';
Query OK, 0 rows affected (0.00 sec)
MariaDB [test]>  show variables like 'BINLOG_FORMAT';                        
+---------------+-----------+
| Variable_name | Value     |
+---------------+-----------+
| binlog_format | STATEMENT |
+---------------+-----------+
1 row in set (0.00 sec)

MariaDB [test]> insert into t select seq,concat('rudy',seq) from seq_1_to_10;
ERROR 1665 (HY000): Cannot execute statement: impossible to write to binary log since BINLOG_FORMAT = STATEMENT and at least one table uses a storage engine limited to row-based logging. 
InnoDB is limited to row-logging when transaction isolation level is READ COMMITTED or READ UNCOMMITTED.

--在有临时表存在时，不建议改变binlog_format的值
Switching the replication format at runtime is not recommended when any temporary tables exist