binlog的三种模式
binlog的三种模式
成都创新互联主营稷山网站建设的网络公司,主营网站建设方案,成都app软件开发公司,稷山h5小程序制作搭建,稷山网站营销推广欢迎稷山等地区企业咨询
statement模式
特点:
(1)此模式不支持RU,RC隔离级别;
(2)binglog日志文件中上一个事物的结束点是下一个事物的开始点;
(3)DML,DDL语句都会明文显示;
(4)对一些系统函数不能准确复制或者不能复制,如load_file()、uuid()、user()、found_rows()、sysdate(),注意(now()可以复制; )
(5)主库执行delete from t1 where c1=xxx limit 1,statement模式下,从库也会这么执行,可能导致删除的不是同一行数据
(6)主库有id=1和id=10两行数据,从库有id=1,2,3,10这四行数据,主库执行delete from t1 where id<10命令,从库删除过多数据;
什么场景会用到statement模式:
(1)一次更新大量数据,如二十万数据,否则在复制的时候,从库可能会追的太慢,导致延时;
(2)使用pt-table-checksum工具时会使用到statement模式;
例1: set tx_isolation='repeatable-read'; set binlog_format='statement'; flush logs; create table t10(c1 int,c2 varchar(50)); insert into t10 values(1,now()); insert into t10 values(2,now()); insert into t10 values(3,sysdate()); insert into t10 values(4,uuid()); update t10 set c2='bbb' where c1=1; [root@Darren2 logs]# MySQLbinlog mysql-bin.000022 ...... create table t10(c1 int,c2 varchar(50)) BEGIN /*!*/; # at 532 #170408 14:40:49 server id 330622 end_log_pos 649 CRC32 0xe5cfc853 Query thread_id=55 exec_time=0 error_code=0 SET TIMESTAMP=1491633649/*!*/; --先设置timestamp,从库复制的时候也会执行这条SQL,这就是now()函数为什么可以复制的原因 insert into t10 values(1,now()) insert into t10 values(2,now()) insert into t10 values(3,sysdate()) insert into t10 values(4,uuid()) /*!*/; # at 1550 #170408 14:40:49 server id 330622 end_log_pos 1581 CRC32 0x5aaa5377 Xid = 1755 COMMIT/*!*/; # at 1581 #170408 14:40:49 server id 330622 end_log_pos 1646 CRC32 0xc2da517f GTID last_committed=5 sequence_number=6 SET @@SESSION.GTID_NEXT= '83373570-fe03-11e6-bb0a-000c29c1b8a9:11328'/*!*/; # at 1646 #170408 14:40:49 server id 330622 end_log_pos 1729 CRC32 0x943df058 Query thread_id=55 exec_time=0 error_code=0 SET TIMESTAMP=1491633649/*!*/; BEGIN /*!*/; # at 1729 #170408 14:40:49 server id 330622 end_log_pos 1841 CRC32 0xb443cf1e Query thread_id=55 exec_time=0 error_code=0 SET TIMESTAMP=1491633649/*!*/; update t10 set c2='bbb' where c1=1 /*!*/; # at 1841 #170408 14:40:49 server id 330622 end_log_pos 1872 CRC32 0xd06c40f5 Xid = 1756 COMMIT/*!*/; SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/; DELIMITER ; # End of log file /*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/; /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/; 主库: root@localhost [testdb]>select * from t10; +------+--------------------------------------+ | c1 | c2 | +------+--------------------------------------+ | 1 | bbb | | 2 | 2017-04-08 14:40:49 | | 3 | 2017-04-08 14:40:49 | | 4 | 4d76efa5-1c26-11e7-bc58-000c29c1b8a9 | +------+--------------------------------------+ 从库: root@localhost [testdb]>select * from t10; +------+--------------------------------------+ | c1 | c2 | +------+--------------------------------------+ | 1 | bbb | | 2 | 2017-04-08 14:40:49 | | 3 | 2017-04-14 13:12:19 | | 4 | ef119323-20d0-11e7-aef6-000c29565380 | +------+--------------------------------------+ 可以发现,statument日志格式下,由于使用了一些函数导致主从数据不一致; 例2: update这个事物的开始是insert这个事物结束的点at1581; update结束的点是commit之后的点at1842; [root@Darren2 logs]# mysqlbinlog --start-position=1581 --stop-position=1842 mysql-bin.000022; ...... BEGIN /*!*/; # at 1729 #170408 14:40:49 server id 330622 end_log_pos 1841 CRC32 0xb443cf1e Query thread_id=55 exec_time=0 error_code=0 use `testdb`/*!*/; SET TIMESTAMP=1491633649/*!*/; update t10 set c2='bbb' where c1=1 /*!*/; # at 1841 #170408 14:40:49 server id 330622 end_log_pos 1872 CRC32 0xd06c40f5 Xid = 1756 COMMIT/*!*/; SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/; DELIMITER ; # End of log file /*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/; /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/; 例3: 当查看commit之前的position点时,会看到rollback状态,说明这个截取的事物不完整: [root@Darren2 logs]# mysqlbinlog --start-position=1581 --stop-position=1841 mysql-bin.000022; BEGIN /*!*/; # at 1729 #170408 14:40:49 server id 330622 end_log_pos 1841 CRC32 0xb443cf1e Query thread_id=55 exec_time=0 error_code=0 use `testdb`/*!*/; SET TIMESTAMP=1491633649/*!*/; update t10 set c2='bbb' where c1=1 /*!*/; ROLLBACK /* added by mysqlbinlog */ /*!*/; SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/; DELIMITER ; # End of log file /*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/; /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
row模式
(1)相对statement更加安全;
(2)在表有主键的情况下复制更加快;
(3)系统的特殊函数也能复制;
(4)更少的锁,只有行锁;
(5)binlog文件比较大,如单语句更新20万行数据,可能要半小时,也有可能把主库跑挂;
(6)无法从binog看见用户执行的SQL语句(mysql 5.6后通过设置binlog_rows_query_log_events=on,日志格式为row中的binlog日志中看到执行过得SQL语句。)
(7)5.7默认的日志模式为row;
(8)DDL语句明文显示,DML语句加密显示;
(9)DML经过base64加密,需要使用参数--base64-output=decode-rows --verbose;
(10)update修改的语句可以看到历史旧数据;
例1: set tx_isolation='repeatable-read'; set binlog_format='row'; flush logs; create table t10(c1 int,c2 varchar(50)); insert into t10 values(1,now()); insert into t10 values(2,now()); insert into t10 values(3,sysdate()); insert into t10 values(4,uuid()); update t10 set c2='bbb' where c1=1; 不加参数只能看到create,alter,drop等DDL语句: mysqlbinlog mysql-bin.000023 带参数查看: [root@Darren2 logs]# mysqlbinlog -vv --base64-output=decode-rows mysql-bin.000023 ...... create table t10(c1 int,c2 varchar(50)) ### INSERT INTO `testdb`.`t10` ### SET ### @1=1 ### @2='2017-04-08 15:11:41' ### INSERT INTO `testdb`.`t10` ### SET ### @1=2 ### @2='2017-04-08 15:11:41' ### INSERT INTO `testdb`.`t10` ### SET ### @1=3 ### @2='2017-04-08 15:11:41' ### INSERT INTO `testdb`.`t10` ### SET ### @1=4 ### @2='9d96b424-1c2a-11e7-bc58-000c29c1b8a9' ### UPDATE `testdb`.`t10` ### WHERE ### @1=1 ### @2='2017-04-08 15:11:41' ### SET ### @1=1 ### @2='bbb' 例2:开启binlog_rows_query_log_events参数,会显示执行的SQL语句,这个参数默认关闭,不显示执行的SQL root@localhost [testdb]>set binlog_rows_query_log_events=on; [root@Darren2 logs]# mysqlbinlog -vv --base64-output=decode-rows mysql-bin.000024 ...... create table t10(c1 int,c2 varchar(50)) # insert into t10 values(1,now()) ### INSERT INTO `testdb`.`t10` ### SET ### @1=1 /* INT meta=0 nullable=1 is_null=0 */ ### @2='2017-04-08 15:26:09' /* VARSTRING(150) meta=150 nullable=1 is_null=0 */ # insert into t10 values(2,now()) ### INSERT INTO `testdb`.`t10` ### SET ### @1=2 /* INT meta=0 nullable=1 is_null=0 */ ### @2='2017-04-08 15:26:09' /* VARSTRING(150) meta=150 nullable=1 is_null=0 */ # at 1033 # insert into t10 values(3,sysdate()) ### INSERT INTO `testdb`.`t10` ### SET ### @1=3 /* INT meta=0 nullable=1 is_null=0 */ ### @2='2017-04-08 15:26:09' /* VARSTRING(150) meta=150 nullable=1 is_null=0 */ # insert into t10 values(4,uuid()) ### INSERT INTO `testdb`.`t10` ### SET ### @1=4 /* INT meta=0 nullable=1 is_null=0 */ ### @2='a2b570b8-1c2c-11e7-bc58-000c29c1b8a9' /* VARSTRING(150) meta=150 nullable=1 is_null=0 */ # update t10 set c2='bbb' where c1=1 ### UPDATE `testdb`.`t10` ### WHERE ### @1=1 /* INT meta=0 nullable=1 is_null=0 */ ### @2='2017-04-08 15:26:09' /* VARSTRING(150) meta=150 nullable=1 is_null=0 */ ### SET ### @1=1 /* INT meta=0 nullable=1 is_null=0 */ ### @2='bbb' /* VARSTRING(150) meta=150 nullable=1 is_null=0 */
mixed模式
特点:
(1)innodb引擎,如果隔离级别是RU、RC,则mixed模式会转成Row模式存储;
(2)mixed模式下,在以下几种情况会自动将binlog的模式有SBR转化成RBR模式:
当更新一个NDB表时;
当函数包含uuid()函数时;
2个及以上包含auto_increment字段的表被更新时;
视图中必须要求使用RBR时,如创建视图时使用了uuid()函数;
例1:当隔离级别是read-committed时,mixed模式会转化成row模式存储: set tx_isolation='read-committed'; set binlog_format='mixed'; flush logs; create table t10(c1 int,c2 varchar(50)); insert into t10 values(1,now()); insert into t10 values(2,now()); insert into t10 values(3,sysdate()); insert into t10 values(4,uuid()); update t10 set c2='bbb' where c1=1; [root@Darren2 logs]# mysqlbinlog -vv --base64-output=decode-rows mysql-bin.000028 ...... ### UPDATE `testdb`.`t10` ### WHERE ### @1=1 /* INT meta=0 nullable=1 is_null=0 */ ### @2='2017-04-08 18:34:08' /* VARSTRING(150) meta=150 nullable=1 is_null=0 */ ### SET ### @1=1 /* INT meta=0 nullable=1 is_null=0 */ ### @2='bbb' /* VARSTRING(150) meta=150 nullable=1 is_null=0 */ ...... 例2:当隔离级别是repeatable-read时,mixed模式会转化成statement模式存储 set tx_isolation='repeatable-read'; set binlog_format='mixed'; flush logs; create table t10(c1 int,c2 varchar(50)); insert into t10 values(1,now()); insert into t10 values(2,now()); insert into t10 values(3,sysdate()); insert into t10 values(4,uuid()); update t10 set c2='bbb' where c1=1; [root@Darren2 logs]# mysqlbinlog mysql-bin.000029 ...... update t10 set c2='bbb' where c1=1 ......
当前题目:binlog的三种模式
标题网址:http://ybzwz.com/article/psipec.html