學習看一個gtid模式的binlog
配置文件
[mysqld]
####: for binlog
binlog_format =row # row
binlog_error_action =abort_server # abort_server
log_bin =D:\\tools\mysql_5.7.36\\mysql-5.7.36-winx64\\binlog\\mysql-bin # off
binlog_rows_query_log_events =on # off
log_slave_updates =on # off
expire_logs_days = 2
binlog_cache_size =65536 # 65536(64k)
binlog_checksum =CRC32 # CRC32
sync_binlog =1 # 1
slave_preserve_commit_order =ON # OFF
server_id =297
gtid_executed_compression_period =1000 # 1000
gtid_mode =on # off
enforce_gtid_consistency =on # off
binlog_rows_query_log_events =on
執(zhí)行SQL如下
mysql> begin;select now();
Query OK, 0 rows affected (0.00 sec)
+---------------------+
| now() |
+---------------------+
| 2022-03-10 09:39:29 |
+---------------------+
1 row in set (0.00 sec)
mysql> insert into test values(10,'test');select now();
Query OK, 1 row affected (0.02 sec)
+---------------------+
| now() |
+---------------------+
| 2022-03-10 09:39:50 |
+---------------------+
1 row in set (0.00 sec)
mysql> commit;select now();
Query OK, 0 rows affected (0.00 sec)
+---------------------+
| now() |
+---------------------+
| 2022-03-10 09:40:43 |
+---------------------+
1 row in set (0.00 sec)
mysql>
binlog記錄信息
mysqlbinlog -vv xxxx-000001 通過這個命令來查詢binlog信息
以下解釋在binlog里面?zhèn)渥ⅰ?/p>
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#220310 9:36:30 server id 297 end_log_pos 123 CRC32 0xf295b2a8 Start: binlog v 4, server v 5.7.36-log created 220310 9:36:30 at startup
# Warning: this binlog is either in use or was not closed properly.
ROLLBACK/*!*/;
BINLOG '
HlYpYg8pAQAAdwAAAHsAAAABAAQANS43LjM2LWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAeViliEzgNAAgAEgAEBAQEEgAAXwAEGggAAAAICAgCAAAACgoKKioAEjQA
AaiylfI=
'/*!*/;
# at 123
#220310 9:36:30 server id 297 end_log_pos 194 CRC32 0x28c16622 Previous-GTIDs
# 9bb60b7a-9df4-11ec-944e-b4a9fcb9227b:1-2
# at 194
#220310 9:40:43 server id 297 end_log_pos 259 CRC32 0x5dcce782 GTID last_committed=0 sequence_number=1 rbr_only=yes
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
SET @@SESSION.GTID_NEXT= '9bb60b7a-9df4-11ec-944e-b4a9fcb9227b:3'/*!*/;
# at 259
#220310 9:39:50 server id 297 end_log_pos 331 CRC32 0xfb63ce11 Query thread_id=2 exec_time=0 error_code=0
SET TIMESTAMP=1646876390/*!*/;
SET @@session.pseudo_thread_id=2/*!*/;
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 gbk *//*!*/;
SET @@session.character_set_client=28,@@session.collation_connection=28,@@session.collation_server=8/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
BEGIN
/*!*/;
# at 331
#220310 9:39:50 server id 297 end_log_pos 389 CRC32 0x4cf98aa5 Rows_query
# insert into test values(10,'test')
# at 389
#220310 9:39:50 server id 297 end_log_pos 439 CRC32 0xd137dbb0 Table_map: `test`.`test` mapped to number 108
# at 439
#220310 9:39:50 server id 297 end_log_pos 484 CRC32 0xf777fdc0 Write_rows: table id 108 flags: STMT_END_F
BINLOG '
5lYpYh0pAQAAOgAAAIUBAACAACJpbnNlcnQgaW50byB0ZXN0IHZhbHVlcygxMCwndGVzdCcppYr5
TA==
5lYpYhMpAQAAMgAAALcBAAAAAGwAAAAAAAEABHRlc3QABHRlc3QAAgMPAhQAA7DbN9E=
5lYpYh4pAQAALQAAAOQBAAAAAGwAAAAAAAEAAgAC//wKAAAABHRlc3TA/Xf3
'/*!*/;
### INSERT INTO `test`.`test`
### SET
### @1=10 /* INT meta=0 nullable=1 is_null=0 */
### @2='test' /* VARSTRING(20) meta=20 nullable=1 is_null=0 */
# at 484
#220310 9:40:43 server id 297 end_log_pos 515 CRC32 0xcdb4745f Xid = 7
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*/;
下面我每針對每一個event來進行解析
- binlog日志頭信息
這里面記錄了每一個新的binlog頭的信息盏触，暫時不需要關心驯遇，可以看到里面有一些數(shù)據(jù)庫版本信息等玄渗。
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#220310 9:36:30 server id 297 end_log_pos 123 CRC32 0xf295b2a8 Start: binlog v 4, server v 5.7.36-log created 220310 9:36:30 at startup
# Warning: this binlog is either in use or was not closed properly.
ROLLBACK/*!*/;
BINLOG '
HlYpYg8pAQAAdwAAAHsAAAABAAQANS43LjM2LWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAeViliEzgNAAgAEgAEBAQEEgAAXwAEGggAAAAICAgCAAAACgoKKioAEjQA
AaiylfI=
'/*!*/;
- Previous_gtid_log_event
記錄當前事務上一個gtid的event
# at 123
#220310 9:36:30 server id 297 end_log_pos 194 CRC32 0x28c16622 Previous-GTIDs
# 9bb60b7a-9df4-11ec-944e-b4a9fcb9227b:1-2
- gtid_log_event
記錄當前事務gtid信息的event魔策，注意看時間和commit的時間一樣碎税，所以可以得出润努，gtid信息是在事務提交的時候給分配的情连。
last_committed=0 ,這個值代表當前一個組內(nèi)可以一起commit的最大sequence_number的值，這個值一致的事務可以在從庫并行復制炭菌。
commit-parent 模式：last_committed在binlog組提交的時候共同的last_committed值才會并行復制罪佳。
commit-order 模式：會通過主鍵和unique key 哈希為一個哈希值，存在哈希表中黑低，在并行復制的時候赘艳，即使不在同一個binlog組克握，只要新提交的值的哈希不在哈希表蕾管，就代表不沖突，就可以一起并行復制菩暗。
sequence_number=1 旷坦，這個值代表在每一個binlog開始額度時候每個事務的遞增值，初始值是1，每次加1誊薄。
rbr_only=yes 假如該值是yes.就是基于row格式，否則就包含statement格式昙衅。
# at 194
#220310 9:40:43 server id 297 end_log_pos 259 CRC32 0x5dcce782 GTID last_committed=0 sequence_number=1 rbr_only=yes
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
SET @@SESSION.GTID_NEXT= '9bb60b7a-9df4-11ec-944e-b4a9fcb9227b:3'/*!*/;
- Query_log_event
這里設置了一些session級別的值著瓶，并且記錄的begin的語句
# at 259
#220310 9:39:50 server id 297 end_log_pos 331 CRC32 0xfb63ce11 Query thread_id=2 exec_time=0 error_code=0
SET TIMESTAMP=1646876390/*!*/;
SET @@session.pseudo_thread_id=2/*!*/;
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 gbk *//*!*/;
SET @@session.character_set_client=28,@@session.collation_connection=28,@@session.collation_server=8/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
BEGIN
/*!*/;
- row_query_log_event
這里記錄了事務執(zhí)行的原始SQL語句，需要在配置文件開啟binlog_rows_query_log_event=on 才可以看到。
# at 331
#220310 9:39:50 server id 297 end_log_pos 389 CRC32 0x4cf98aa5 Rows_query
# insert into test values(10,'test')
- table_map_event
事務操作對應的表id
# at 389
#220310 9:39:50 server id 297 end_log_pos 439 CRC32 0xd137dbb0 Table_map: `test`.`test` mapped to number 108
- write_rows_event
代表insert的事務操作記錄和數(shù)據(jù)記錄
# at 439
#220310 9:39:50 server id 297 end_log_pos 484 CRC32 0xf777fdc0 Write_rows: table id 108 flags: STMT_END_F
BINLOG '
5lYpYh0pAQAAOgAAAIUBAACAACJpbnNlcnQgaW50byB0ZXN0IHZhbHVlcygxMCwndGVzdCcppYr5
TA==
5lYpYhMpAQAAMgAAALcBAAAAAGwAAAAAAAEABHRlc3QABHRlc3QAAgMPAhQAA7DbN9E=
5lYpYh4pAQAALQAAAOQBAAAAAGwAAAAAAAEAAgAC//wKAAAABHRlc3TA/Xf3
'/*!*/;
### INSERT INTO `test`.`test`
### SET
### @1=10 /* INT meta=0 nullable=1 is_null=0 */
### @2='test' /* VARSTRING(20) meta=20 nullable=1 is_null=0 */
- xid_event
xid_event表示事務提交，就是commit狭姨。
xid在redo和binlog都會有，從而崩潰恢復的時候绑莺，判斷binlog假如有xix 就提交纺裁，沒有就回滾欺缘。
# at 484
#220310 9:40:43 server id 297 end_log_pos 515 CRC32 0xcdb4745f Xid = 7
COMMIT/*!*/;
每個event的產(chǎn)生時間
gtid_log_event ,xid_event 是在commit的時候產(chǎn)生的。
gtid_log_event,row_query_log_event,table_map_event,write_rows_enent 是在執(zhí)行insert語句產(chǎn)生的丛肢。
