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