看到有类似的INSERT INTO access_log.access_log VALUES(NULL,CONNECTION_ID(),NOW(),USER(),CURRENT_USER())这里的一些函数最好用row模式,因为主从复制的时候,uuid已经now()等会造成时间延迟,故而为了数据一致性,statement格式不是最佳选择。
4.2 REPEATABLE-READ和MIXED测试结果my.cnf里面修改设置:
transaction_isolation = REPEATABLE-READ binlog_format=MIXED重启mysql数据库后,录入测试数据:
mysql> insert into z4 select 3; Query OK, 1 row affected (0.00 sec) Records: 1 Duplicates: 0 Warnings: 0 mysql> exit查看binlog数据,会看到insert into z4 select 3这条sql记录,表明在mixed模式下,解析出来的sql是正常的,有些now()已经uuid的直接解析成row格式了,如下所示:
[root@mysql5.6.12 binlog_new]# /usr/local/mysql/bin/mysqlbinlog --base64-output=DECODE-ROWS -v mysql-bin.000007 ... # at 274 #151211 16:21:02 server id 72 end_log_pos 368 CRC32 0x156a1c51 Write_rows: table id 70 flags: STMT_END_F ### INSERT INTO `access_log`.`access_log` ### SET ### @1=10551 ### @2=1 ### @3=1449822062 ### @4='park_user@192.168.121.243' ### @5='park_user@192.168.%' # at 368 #151211 16:21:02 server id 72 end_log_pos 399 CRC32 0x8254defe Xid = 3 COMMIT/*!*/; # at 399 #151211 16:21:25 server id 72 end_log_pos 478 CRC32 0xe252f5c7 Query thread_id=2 exec_time=0 error_code=0 SET TIMESTAMP=1449822085/*!*/; BEGIN /*!*/; # at 478 #151211 16:21:25 server id 72 end_log_pos 575 CRC32 0x34308ad6 Query thread_id=2 exec_time=0 error_code=0 use `test`/*!*/; SET TIMESTAMP=1449822085/*!*/; insert into z4 select 3 /*!*/; # at 575 #151211 16:21:25 server id 72 end_log_pos 606 CRC32 0x67c460eb Xid = 61 COMMIT/*!*/; DELIMITER ; # End of log file ROLLBACK /* added by mysqlbinlog */; /*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/; /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/; [root@mysql5.6.12 binlog_new]#