数据库版本: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形式
没做太多测试 具体的优劣就不再说了。这里只是能简单的展示出日志的明显区别。