源文将持續更新,請點選此處閱讀原文
一、問題描述:
有一次在MySQL從庫上執行binlog_format=row之後,發現binlog的格式咋還是statement,并沒有變化為binlog_format,查閱MySQL官方手冊發現binlog_format的确是個動态修改的參數(官方手冊請點選此處),這是為啥呢?
二、假想測試:
# 測試表結構
CREATE TABLE t(id INT NOT NULL DEFAULT '0');
- 官方文檔錯誤?畢竟我曾經的确給官方文檔提出一個BUG,并且MySQL官網很快給修改了。。
結論:官方文檔并沒有欺騙我,的确是可以動态修改的。。。# 主庫上測試 # 修改主庫的binlog_format set global binlog_format=statement; set session binlog_format=statement; insert into t values(6); # 檢視binlog # at 441 #161206 20:40:13 server id 2263333 end_log_pos 542 CRC32 0x3eeb2569 Query thread_id=1215 exec_time=0 error_code=0 use `fianna`/*!*/; SET TIMESTAMP=1481028133/*!*/; insert into t values(6) /*!*/; # at 542
- 莫非從庫上binlog_format并不是動态修改型參數?
結論:從庫上的binlog_format也是動态修改參數。。# 從庫上測試 # 修改從庫binlog_format set global binlog_format=statement; set session binlog_format=statement; insert into t values(11); # 檢視binlog # at 984 #161206 20:41:31 server id 2273333 end_log_pos 1032 CRC32 0xa3865eac Rows_query # insert into t values(11) # at 1032
- 難道從庫上從主庫複制來的SQL才會導緻binlog_format沒有修改?
結論:好像binlog_format對于主從複制的SQL并沒有影響。。# 主從同時測試 # 修改主庫binlog_format set global binlog_format=row; set session binlog_format=row; # 修改從庫binlog_format set global binlog_format=row; set session binlog_format=row; # 主庫執行 insert into t values(5); # 主庫binlog # at 740 #161206 20:43:00 server id 2263333 end_log_pos 780 CRC32 0x0ab3fb46 Write_rows: table id 3523 flags: STMT_END_F BINLOG ' VLJGWB0liSIALwAAALYCAACAABdpbnNlcnQgaW50byB0IHZhbHVlcyg2KeLjoRw= VLJGWBMliSIALgAAAOQCAAAAAMMNAAAAAAEABmZpYW5uYQABdAABAwABl8Yg5A== VLJGWB4liSIAKAAAAAwDAAAAAMMNAAAAAAEAAgAB//4GAAAARvuzCg== '/*!*/; ### INSERT INTO `fianna`.`t` ### SET ### @1=5 /* INT meta=0 nullable=1 is_null=0 */ # at 780 # 從庫binlog # at 984 #161206 20:43:01 server id 2273333 end_log_pos 1032 CRC32 0xa3865eac Rows_query # insert into t values(5) # at 1032
- 如果restart slave,binlog_format是否有影響呢?
結論:restart slave之後,binlog_format立即動态被修改。# 主從同時測試 # 修改主庫binlog_format set global binlog_format=row; set session binlog_format=row; # 修改從庫binlog_format set global binlog_format=row; set session binlog_format=row; stop slave; start slave; # 主庫執行 insert into t values(2); # 主庫binlog # at 998 #161206 20:53:00 server id 2263333 end_log_pos 998 CRC32 0x0ab3fb46 Write_rows: table id 3523 flags: STMT_END_F BINLOG ' VLJGWB0liSIALwAAALYCAACAABdpbnNlcnQgaW50byB0IHZhbHVlcyg2KeLjoRw= VLJGWBMliSIALgAAAOQCAAAAAMMNAAAAAAEABmZpYW5uYQABdAABAwABl8Yg5A== VLJGWB4liSIAKAAAAAwDAAAAAMMNAAAAAAEAAgAB//4GAAAARvuzCg== '/*!*/; ### INSERT INTO `fianna`.`t` ### SET ### @1=2 /* INT meta=0 nullable=1 is_null=0 */ # at 998 # 從庫binlog # at 1078 #161206 20:53:01 server id 2273333 end_log_pos 1118 CRC32 0x81a97c17 Write_rows: table id 47129 flags: STMT_END_F BINLOG ' F7RGWB01sCIAMAAAAAgEAACAABhpbnNlcnQgaW50byB0IHZhbHVlcygxMSmsXoaj F7RGWBM1sCIALgAAADYEAAAAABm4AAAAAAEABmZpYW5uYQABdAABAwAB3jnZ6w== F7RGWB41sCIAKAAAAF4EAAAAABm4AAAAAAEAAgAB//4LAAAAF3ypgQ== '/*!*/; ### INSERT INTO `fianna`.`t` ### SET ### @1=2 /* INT meta=0 nullable=1 is_null=0 */ # at 1118
三、問題總結
主從複制中,從庫的binlog_format動态修改後,并不能立即于來自于主庫的SQL(即複制SQL)立即生效,需要在從庫執行
stop slave;``start slave;
之後,方可生效。