binlog的三种模式
admin
2023-05-19 18:22:17
0次

binlog的三种模式

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
......


上一篇：科普十款全民牛牛开挂助手,微乐云南麻将作弊器有吗,UU Games辅助挂破解版—教你开挂方法

下一篇：RESTful+MySQL程序安装讲义

相关内容

热门资讯

让数据“说话”、AI“拿主意” 在德赛西威的智能化产线上，AI让流水线告别传统人工模式，精度与效率双双跃升 集成供应链的...
星河动力智神星一号 8 月中下... 8 月 3 日消息，星河动力航天将于近期择机实施“智神星一号（遥一）”商业运载火箭发射任务，今日发布...
伊朗称在霍尔木兹海峡上空击落一... 新华社德黑兰8月3日电　据伊朗媒体3日报道，伊朗伊斯兰革命卫队在霍尔木兹海峡上空击落一架MQ－9无人...
“地表最强男人”，遇难 奇迹没有发生，在经过几天的救援之后，正式消息发出，世界著名登山家尼尔马尔·普尔贾（Nirmal Pu...
精神科医生“十级美颜”证件照刷... 近日，山西医科大学第一医院精神科医生郎小娥的证件照，因美颜效果突出打破大众固有印象而意外出圈。8月1...
凤凰直击日本熊本震区：刚修缮好... 日本熊本强震重创当地历史建筑。位于熊本县八代市的八代宫神社受损惨重。凤凰卫视特派记者蒋文彬采访了神社...
考勤、工资、社保都要查，中国企... 编者按7月23日，欧盟第21轮对俄制裁，将14家中国内地及香港企业列入名单；不到24小时，中国商务部...
两名俄公民在泰国遇害，泰总理道... 综合泰国《曼谷邮报》等媒体8月2日报道，对于两名俄罗斯公民近期在泰国遇害，泰国总理阿努廷作出表态并进...
总书记的人民情怀｜“坚持高质量... 原标题：“坚持高质量发展要成为领导干部政绩观的重要内容”（总书记的人民情怀）本报记者 赵 成 史一棋
马科斯遭痛批“亲美上头”：瞧瞧... 菲律宾主流英文报刊《马尼拉时报》8月3日刊发评论文章，批评菲律宾政府近年来过度依赖美国安全承诺，对华...