40.MySQL binlog_format 三种二进制日志格式的区别

1.binlog_format 日志格式的值：ROW,STATEMENT,MIXED 

STATEMENT:

可以看到DML,DDL都是明文记录日志的。
对主从复制的影响：
1）对有些参数，在不同的服务器和不同的时间，执行结果不一样，会导致主从不一致。
2）特别是一些函数：uuid(),user(),时间函数now()
3)性能问题
4）数据异常。

ROW:

binlog_format='row' 时:DDL 时是明文,DML是加密的。

MIXED:DDL 时是明文,DML是明文的。

MIXED:

binlog_format='mixed'  DML/DDL 都是明文的。

2.具体如下

(1)ROW:

# at 194
#230218 15:56:59 server id 1311  end_log_pos 259 CRC32 0xf3e754b6     GTID    last_committed=0    sequence_number=1    rbr_only=no
SET @@SESSION.GTID_NEXT= 'daf4393d-22e8-11ed-a240-000c296a9b44:56'/*!*/;
# at 259
#230218 15:56:59 server id 1311  end_log_pos 379 CRC32 0x934079a8     Query    thread_id=3    exec_time=0    error_code=0
use `test2`/*!*/;
SET TIMESTAMP=1676707019/*!*/;
SET @@session.pseudo_thread_id=3/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=1436549152/*!*/;
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 test1 (id int,name varchar(20))
/*!*/;
# at 379
#230218 15:57:28 server id 1311  end_log_pos 444 CRC32 0x015f312d     GTID    last_committed=1    sequence_number=2    rbr_only=yes
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
SET @@SESSION.GTID_NEXT= 'daf4393d-22e8-11ed-a240-000c296a9b44:57'/*!*/;
# at 444
#230218 15:57:28 server id 1311  end_log_pos 517 CRC32 0x0dff1240     Query    thread_id=3    exec_time=0    error_code=0
SET TIMESTAMP=1676707048/*!*/;
BEGIN
/*!*/;
# at 517
# at 597
#230218 15:57:28 server id 1311  end_log_pos 649 CRC32 0x140ebc7c     Table_map: `test2`.`test1` mapped to number 110
# at 649
#230218 15:57:28 server id 1311  end_log_pos 714 CRC32 0x5d0bcb32     Write_rows: table id 110 flags: STMT_END_F

BINLOG '
6ITwYxMfBQAANAAAAIkCAAAAAG4AAAAAAAEABXRlc3QyAAV0ZXN0MQACAw8CFAADfLwOFA==
6ITwYx4fBQAAQQAAAMoCAAAAAG4AAAAAAAEAAgAC//wBAAAABHhzcTH8AgAAAAR4c3Ey/AMAAAAE
eHNxMzLLC10=
'/*!*/;
# at 714
#230218 15:57:28 server id 1311  end_log_pos 745 CRC32 0xd78abbea     Xid = 30
COMMIT/*!*/;

(2)STATEMENT

create table test2(id int,name varchar(10))
/*!*/;
# at 3639
#230218 16:09:00 server id 1311  end_log_pos 3704 CRC32 0x23815667     GTID    last_committed=12    sequence_number=13    rbr_only=no
SET @@SESSION.GTID_NEXT= 'daf4393d-22e8-11ed-a240-000c296a9b44:68'/*!*/;
# at 3704
#230218 16:09:00 server id 1311  end_log_pos 3783 CRC32 0x8ba9061f     Query    thread_id=6    exec_time=0    error_code=0
SET TIMESTAMP=1676707740/*!*/;
BEGIN
/*!*/;
# at 3783
#230218 16:09:00 server id 1311  end_log_pos 3912 CRC32 0x0e430443     Query    thread_id=6    exec_time=0    error_code=0
SET TIMESTAMP=1676707740/*!*/;
insert into test2 values(1,'xxx1'),(2,'xx2'),(3,'xxx3')
/*!*/;
# at 3912
#230218 16:09:00 server id 1311  end_log_pos 3943 CRC32 0xe4947acb     Xid = 88
COMMIT/*!*/;
# at 3943
#230218 16:09:00 server id 1311  end_log_pos 4008 CRC32 0x17ee9fe7     GTID    last_committed=13    sequence_number=14    rbr_only=no
SET @@SESSION.GTID_NEXT= 'daf4393d-22e8-11ed-a240-000c296a9b44:69'/*!*/;
# at 4008
#230218 16:09:00 server id 1311  end_log_pos 4087 CRC32 0xe3b62630     Query    thread_id=6    exec_time=0    error_code=0
SET TIMESTAMP=1676707740/*!*/;
BEGIN
/*!*/;
# at 4087
#230218 16:09:00 server id 1311  end_log_pos 4201 CRC32 0x3f761ee0     Query    thread_id=6    exec_time=0    error_code=0
SET TIMESTAMP=1676707740/*!*/;
update test2 set name='xxx33' where id=3
/*!*/;
# at 4201
#230218 16:09:00 server id 1311  end_log_pos 4232 CRC32 0xe273dd87     Xid = 89
COMMIT/*!*/;
# at 4232
#230218 16:09:14 server id 1311  end_log_pos 4297 CRC32 0x8b24c992     GTID    last_committed=14    sequence_number=15    rbr_only=no
SET @@SESSION.GTID_NEXT= 'daf4393d-22e8-11ed-a240-000c296a9b44:70'/*!*/;
# at 4297
#230218 16:09:14 server id 1311  end_log_pos 4376 CRC32 0xd1382810     Query    thread_id=6    exec_time=0    error_code=0
SET TIMESTAMP=1676707754/*!*/;
BEGIN
/*!*/;
# at 4376
#230218 16:09:14 server id 1311  end_log_pos 4478 CRC32 0x0451563a     Query    thread_id=6    exec_time=0    error_code=0
SET TIMESTAMP=1676707754/*!*/;
delete from test2 where id=3

(3)MIXED 

create table test3(id int,name varchar(10))
/*!*/;
# at 376
#230218 16:11:56 server id 1311  end_log_pos 441 CRC32 0x5844a0d4     GTID    last_committed=1    sequence_number=2    rbr_only=no
SET @@SESSION.GTID_NEXT= 'daf4393d-22e8-11ed-a240-000c296a9b44:73'/*!*/;
# at 441
#230218 16:11:56 server id 1311  end_log_pos 520 CRC32 0xc6eaf0b6     Query    thread_id=7    exec_time=0    error_code=0
SET TIMESTAMP=1676707916/*!*/;
BEGIN
/*!*/;
# at 520
#230218 16:11:56 server id 1311  end_log_pos 649 CRC32 0x1c312f87     Query    thread_id=7    exec_time=0    error_code=0
SET TIMESTAMP=1676707916/*!*/;
insert into test3 values(1,'xxx1'),(2,'xx2'),(3,'xxx3')
/*!*/;
# at 649
#230218 16:11:56 server id 1311  end_log_pos 680 CRC32 0xf27ec902     Xid = 112
COMMIT/*!*/;
# at 680
#230218 16:11:56 server id 1311  end_log_pos 745 CRC32 0x323e7d4f     GTID    last_committed=2    sequence_number=3    rbr_only=no
SET @@SESSION.GTID_NEXT= 'daf4393d-22e8-11ed-a240-000c296a9b44:74'/*!*/;
# at 745
#230218 16:11:56 server id 1311  end_log_pos 824 CRC32 0xaef5d099     Query    thread_id=7    exec_time=0    error_code=0
SET TIMESTAMP=1676707916/*!*/;
BEGIN
/*!*/;
# at 824
#230218 16:11:56 server id 1311  end_log_pos 938 CRC32 0x28c01dd2     Query    thread_id=7    exec_time=0    error_code=0
SET TIMESTAMP=1676707916/*!*/;
update test3 set name='xxx33' where id=3
/*!*/;
# at 938
#230218 16:11:56 server id 1311  end_log_pos 969 CRC32 0xf2907e46     Xid = 113
COMMIT/*!*/;
# at 969
#230218 16:11:59 server id 1311  end_log_pos 1034 CRC32 0x31852dac     GTID    last_committed=3    sequence_number=4    rbr_only=no
SET @@SESSION.GTID_NEXT= 'daf4393d-22e8-11ed-a240-000c296a9b44:75'/*!*/;
# at 1034
#230218 16:11:59 server id 1311  end_log_pos 1137 CRC32 0x4ecc42c1     Query    thread_id=7    exec_time=0    error_code=0
SET TIMESTAMP=1676707919/*!*/;
alter table test3 add age int
/*!*/;
# at 1137
#230218 16:12:05 server id 1311  end_log_pos 1202 CRC32 0x857b39ea     GTID    last_committed=4    sequence_number=5    rbr_only=no
SET @@SESSION.GTID_NEXT= 'daf4393d-22e8-11ed-a240-000c296a9b44:76'/*!*/;
# at 1202
#230218 16:12:05 server id 1311  end_log_pos 1281 CRC32 0xdc134a95     Query    thread_id=7    exec_time=0    error_code=0
SET TIMESTAMP=1676707925/*!*/;
BEGIN
/*!*/;
# at 1281
#230218 16:12:05 server id 1311  end_log_pos 1383 CRC32 0x1cbbb316     Query    thread_id=7    exec_time=0    error_code=0
SET TIMESTAMP=1676707925/*!*/;
delete from test3 where id=3
/*!*/;
# at 1383
#230218 16:12:05 server id 1311  end_log_pos 1414 CRC32 0xffc28a66     Xid = 116
COMMIT/*!*/;
 

3.总结

由此可见：

1.ROW,格式的内容都是加密的不可以直接查看,适合于主从复制。

2.STATEMENT,MIXED 格式的binlog解析后可以查看具体的SQL语句。