mysql binlog三种模式的区别（row，statement，mixed）

数据库版本：mysql-5.7.32

statement: 

执行sql：update log_user set name = 'admin3';
############以下内容为mysql-bin.000002的新增内容#############
# at 745
#201218  8:04:11 server id 1000  end_log_pos 810 CRC32 0xa7a54b83 	Anonymous_GTID	last_committed=2	sequence_number=3	rbr_only=no
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 810
#201218  8:04:11 server id 1000  end_log_pos 895 CRC32 0x84f89bd2 	Query	thread_id=4	exec_time=0	error_code=0
SET TIMESTAMP=1608278651/*!*/;
BEGIN
/*!*/;
# at 895
#201218  8:04:11 server id 1000  end_log_pos 1010 CRC32 0xf19f717d 	Query	thread_id=4	exec_time=0	error_code=0
SET TIMESTAMP=1608278651/*!*/;
update log_user set name = 'admin3'
/*!*/;
# at 1010
#201218  8:04:11 server id 1000  end_log_pos 1041 CRC32 0x4546120a 	Xid = 18
COMMIT/*!*/;

row：

查看日志命令：mysqlbinlog --no-defaults mysql-bin.000001 

解密查看日志命令：mysqlbinlog --no-defaults --base64-output=decode-rows -v mysql-bin.000001  

执行sql：update log_user set name = 'admin';
#############以下内容为mysql-bin.000001的新增内容##################
# at 594
#201218  7:52:23 server id 1000  end_log_pos 659 CRC32 0x213628bf 	Anonymous_GTID	last_committed=1	sequence_number=2	rbr_only=yes
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 659
#201218  7:52:23 server id 1000  end_log_pos 734 CRC32 0x73358ea8 	Query	thread_id=4	exec_time=0	error_code=0
SET TIMESTAMP=1608277943/*!*/;
BEGIN
/*!*/;
# at 734
#201218  7:52:23 server id 1000  end_log_pos 816 CRC32 0x3bc608b7 	Table_map: `hepburn`.`log_user` mapped to number 108
# at 816
#201218  7:52:23 server id 1000  end_log_pos 1003 CRC32 0xb9c95076 	Update_rows: table id 108 flags: STMT_END_F

BINLOG '
t1/cXxPoAwAAUgAAADADAAAAAGwAAAAAAAEAB2hlcGJ1cm4ACGxvZ191c2VyAAsDEgMPDw8PDw8P
DxEA/QL9Av0C/QL9Av0C/QL9AvoHtwjGOw==
t1/cXx/oAwAAuwAAAOsDAAAAAGwAAAAAAAEAAgAL/wD4AgAAAJmcMQvlDycAAAAAAAAFAGFk
bWluIABweGczcGRjcG11K3psKzdUbXliVXFWLzd1dGZ5RXJ1TwUAYWRtaW4AAAEAMAAAAPgCAAAA
mZwxC+UPJwAAAAAAAAYAYWRtaW4yIABweGczcGRjcG11K3psKzdUbXliVXFWLzd1dGZ5RXJ1TwUA
YWRtaW4AAAEAMAAAdlDJuQ==
'/*!*/;
# at 1003
#201218  7:52:23 server id 1000  end_log_pos 1034 CRC32 0xc032a2e5 	Xid = 17
COMMIT/*!*/;
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;

两者日志主要不一样的是（其他为各自方式的表明）为：

statement:

SET TIMESTAMP=1608278651/*!*/;
update log_user set name = 'admin3'


---------------手动分割线------------------
row:


BINLOG '
t1/cXxPoAwAAUgAAADADAAAAAGwAAAAAAAEAB2hlcGJ1cm4ACGxvZ191c2VyAAsDEgMPDw8PDw8P
DxEA/QL9Av0C/QL9Av0C/QL9AvoHtwjGOw==
t1/cXx/oAwAAuwAAAOsDAAAAAGwAAAAAAAEAAgAL/wD4AgAAAJmcMQvlDycAAAAAAAAFAGFk
bWluIABweGczcGRjcG11K3psKzdUbXliVXFWLzd1dGZ5RXJ1TwUAYWRtaW4AAAEAMAAAAPgCAAAA
mZwxC+UPJwAAAAAAAAYAYWRtaW4yIABweGczcGRjcG11K3psKzdUbXliVXFWLzd1dGZ5RXJ1TwUA
YWRtaW4AAAEAMAAAdlDJuQ==

可以明显看出statement直接记录的sql语句和一个时间戳，row是记录了一行的数据

 

mixed 这个模式为row，statement的结合，mysql根据执行的具体SQL来决定日志形式

执行sql：update log_user set name = 'admin';
#############以下内容为mysql-bin.000004的新增内容##################

# at 451
#201218  9:05:18 server id 1000  end_log_pos 516 CRC32 0xcff0d5bb 	Anonymous_GTID	last_committed=1	sequence_number=2	rbr_only=no
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 516
#201218  9:05:18 server id 1000  end_log_pos 601 CRC32 0xe14d5dfc 	Query	thread_id=4	exec_time=0	error_code=0
SET TIMESTAMP=1608282318/*!*/;
BEGIN
/*!*/;
# at 601
#201218  9:05:18 server id 1000  end_log_pos 715 CRC32 0xc0c00e0f 	Query	thread_id=4	exec_time=0	error_code=0
SET TIMESTAMP=1608282318/*!*/;
update log_user set name = 'admin'
/*!*/;
# at 715
#201218  9:05:18 server id 1000  end_log_pos 746 CRC32 0xc3536784 	Xid = 19
COMMIT/*!*/;

试了增删改查 日志都是statement形式

 

没做太多测试 具体的优劣就不再说了。这里只是能简单的展示出日志的明显区别。

参考：https://segmentfault.com/a/1190000023076034

mysql面试题：https://cloud.tencent.com/developer/article/1618515?from=information.detail.%E9%AB%98%E6%80%A7%E8%83%BDmysql%20pdf