Description:
http://dev.mysql.com/doc/refman/5.6/en/replication-formats.html
The documentation on this page states "The logging format in a running MySQL server is controlled by setting the binlog_format server system variable...setting the variable globally requires a restart of the server to take effect".
In fact, the server switches seamlessly between statement and row-based replication modes. No restart is required. All clients that connect thereafter will immediately start using the new mode.
How to repeat:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.6.20-log MySQL Community Server (GPL)
Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> select @@global.binlog_format, @@session.binlog_format;
+------------------------+-------------------------+
| @@global.binlog_format | @@session.binlog_format |
+------------------------+-------------------------+
| STATEMENT | STATEMENT |
+------------------------+-------------------------+
1 row in set (0.00 sec)
mysql> create table test.tbl (id int primary key, col int);
Query OK, 0 rows affected (0.25 sec)
mysql> insert test.tbl (id, col) values (1, 123);
Query OK, 1 row affected (0.01 sec)
mysql> update test.tbl set col=col*2;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> drop table test.tbl;
Query OK, 0 rows affected (0.00 sec)
mysql> set global binlog_format=ROW;
Query OK, 0 rows affected (0.00 sec)
mysql> select @@global.binlog_format, @@session.binlog_format;
+------------------------+-------------------------+
| @@global.binlog_format | @@session.binlog_format |
+------------------------+-------------------------+
| ROW | STATEMENT |
+------------------------+-------------------------+
1 row in set (0.00 sec)
mysql> connect
Connection id: 3
Current database: *** NONE ***
mysql> select @@global.binlog_format, @@session.binlog_format;
+------------------------+-------------------------+
| @@global.binlog_format | @@session.binlog_format |
+------------------------+-------------------------+
| ROW | ROW |
+------------------------+-------------------------+
1 row in set (0.00 sec)
mysql> create table test.tbl (id int primary key, col int);
Query OK, 0 rows affected (0.01 sec)
mysql> insert test.tbl (id, col) values (1, 123);
Query OK, 1 row affected (0.00 sec)
mysql> update test.tbl set col=col*2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> drop table test.tbl;
Query OK, 0 rows affected (0.00 sec)
--
# mysqlbinlog mysqld-bin.000001
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#141113 3:06:03 server id 1 end_log_pos 120 CRC32 0x45454827 Start: binlog v 4, server v 5.6.20-log created 141113 3:06:03 at startup
# Warning: this binlog is either in use or was not closed properly.
ROLLBACK/*!*/;
BINLOG '
GyBkVA8BAAAAdAAAAHgAAAABAAQANS42LjIwLWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAbIGRUEzgNAAgAEgAEBAQEEgAAXAAEGggAAAAICAgCAAAACgoKGRkAASdI
RUU=
'/*!*/;
# at 120
#141113 3:06:27 server id 1 end_log_pos 241 CRC32 0x5272ae22 Query thread_id=2 exec_time=0 error_code=0
SET TIMESTAMP=1415847987/*!*/;
SET @@session.pseudo_thread_id=2/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=1073741824/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C utf8 *//*!*/;
SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=8/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
create table test.tbl (id int primary key, col int)
/*!*/;
# at 241
#141113 3:06:34 server id 1 end_log_pos 316 CRC32 0x996c70ec Query thread_id=2 exec_time=0 error_code=0
SET TIMESTAMP=1415847994/*!*/;
BEGIN
/*!*/;
# at 316
#141113 3:06:34 server id 1 end_log_pos 427 CRC32 0x42b06845 Query thread_id=2 exec_time=0 error_code=0
SET TIMESTAMP=1415847994/*!*/;
insert test.tbl (id, col) values (1, 123)
/*!*/;
# at 427
#141113 3:06:34 server id 1 end_log_pos 458 CRC32 0xe3aa7469 Xid = 5
COMMIT/*!*/;
# at 458
#141113 3:06:36 server id 1 end_log_pos 533 CRC32 0x9e556a57 Query thread_id=2 exec_time=0 error_code=0
SET TIMESTAMP=1415847996/*!*/;
BEGIN
/*!*/;
# at 533
#141113 3:06:36 server id 1 end_log_pos 632 CRC32 0xb1377aac Query thread_id=2 exec_time=0 error_code=0
SET TIMESTAMP=1415847996/*!*/;
update test.tbl set col=col*2
/*!*/;
# at 632
#141113 3:06:36 server id 1 end_log_pos 663 CRC32 0xfc47f945 Xid = 6
COMMIT/*!*/;
# at 663
#141113 3:06:46 server id 1 end_log_pos 782 CRC32 0xc862d644 Query thread_id=2 exec_time=0 error_code=0
SET TIMESTAMP=1415848006/*!*/;
DROP TABLE `test`.`tbl` /* generated by server */
/*!*/;
# at 782
#141113 3:07:30 server id 1 end_log_pos 903 CRC32 0xbafc0e23 Query thread_id=3 exec_time=0 error_code=0
SET TIMESTAMP=1415848050/*!*/;
create table test.tbl (id int primary key, col int)
/*!*/;
# at 903
#141113 3:07:34 server id 1 end_log_pos 971 CRC32 0x4c8f5605 Query thread_id=3 exec_time=0 error_code=0
SET TIMESTAMP=1415848054/*!*/;
BEGIN
/*!*/;
# at 971
#141113 3:07:34 server id 1 end_log_pos 1018 CRC32 0xca5958f8 Table_map: `test`.`tbl` mapped to number 71
# at 1018
#141113 3:07:34 server id 1 end_log_pos 1062 CRC32 0x7ca62cae Write_rows: table id 71 flags: STMT_END_F
BINLOG '
diBkVBMBAAAALwAAAPoDAAAAAEcAAAAAAAEABHRlc3QAA3RibAACAwMAAvhYWco=
diBkVB4BAAAALAAAACYEAAAAAEcAAAAAAAEAAgAC//wBAAAAewAAAK4spnw=
'/*!*/;
# at 1062
#141113 3:07:34 server id 1 end_log_pos 1093 CRC32 0xf9e79101 Xid = 13
COMMIT/*!*/;
# at 1093
#141113 3:07:39 server id 1 end_log_pos 1161 CRC32 0x02db19f7 Query thread_id=3 exec_time=0 error_code=0
SET TIMESTAMP=1415848059/*!*/;
BEGIN
/*!*/;
# at 1161
#141113 3:07:39 server id 1 end_log_pos 1208 CRC32 0x8d1ef751 Table_map: `test`.`tbl` mapped to number 71
# at 1208
#141113 3:07:39 server id 1 end_log_pos 1262 CRC32 0x08933306 Update_rows: table id 71 flags: STMT_END_F
BINLOG '
eyBkVBMBAAAALwAAALgEAAAAAEcAAAAAAAEABHRlc3QAA3RibAACAwMAAlH3Ho0=
eyBkVB8BAAAANgAAAO4EAAAAAEcAAAAAAAEAAgAC///8AQAAAHsAAAD8AQAAAPYAAAAGM5MI
'/*!*/;
# at 1262
#141113 3:07:39 server id 1 end_log_pos 1293 CRC32 0x1551002e Xid = 14
COMMIT/*!*/;
# at 1293
#141113 3:07:49 server id 1 end_log_pos 1412 CRC32 0xc9eaca60 Query thread_id=3 exec_time=0 error_code=0
SET TIMESTAMP=1415848069/*!*/;
DROP TABLE `test`.`tbl` /* generated by server */
/*!*/;
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
Suggested fix:
Remove the text about a restart being required. You may wish to clarify that other active connections will not pick up the change until they reconnect.
