binlog event解析

學(xué)習(xí)看一個(gè)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 通過這個(gè)命令來查詢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*/;

下面我每針對(duì)每一個(gè)event來進(jìn)行解析

  • binlog日志頭信息

這里面記錄了每一個(gè)新的binlog頭的信息，暫時(shí)不需要關(guān)心，可以看到里面有一些數(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

記錄當(dāng)前事務(wù)上一個(gè)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
    記錄當(dāng)前事務(wù)gtid信息的event，注意看時(shí)間和commit的時(shí)間一樣，所以可以得出，gtid信息是在事務(wù)提交的時(shí)候給分配的。

last_committed=0 ,這個(gè)值代表當(dāng)前一個(gè)組內(nèi)可以一起commit的最大sequence_number的值，這個(gè)值一致的事務(wù)可以在從庫并行復(fù)制。

commit-parent 模式：last_committed在binlog組提交的時(shí)候共同的last_committed值才會(huì)并行復(fù)制。
commit-order 模式：會(huì)通過主鍵和unique key 哈希為一個(gè)哈希值，存在哈希表中，在并行復(fù)制的時(shí)候，即使不在同一個(gè)binlog組，只要新提交的值的哈希不在哈希表，就代表不沖突，就可以一起并行復(fù)制。

sequence_number=1 ，這個(gè)值代表在每一個(gè)binlog開始額度時(shí)候每個(gè)事務(wù)的遞增值，初始值是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

這里設(shè)置了一些session級(jí)別的值，并且記錄的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
    這里記錄了事務(wù)執(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

事務(wù)操作對(duì)應(yīng)的表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的事務(wù)操作記錄和數(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表示事務(wù)提交，就是commit。
    xid在redo和binlog都會(huì)有，從而崩潰恢復(fù)的時(shí)候，判斷binlog假如有xix 就提交，沒有就回滾。
# at 484
#220310  9:40:43 server id 297  end_log_pos 515 CRC32 0xcdb4745f        Xid = 7
COMMIT/*!*/;

每個(gè)event的產(chǎn)生時(shí)間

gtid_log_event ,xid_event 是在commit的時(shí)候產(chǎn)生的。

gtid_log_event,row_query_log_event,table_map_event,write_rows_enent 是在執(zhí)行insert語句產(chǎn)生的。

最后編輯于 ：
?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請(qǐng)聯(lián)系作者
【社區(qū)內(nèi)容提示】社區(qū)部分內(nèi)容疑似由AI輔助生成，瀏覽時(shí)請(qǐng)結(jié)合常識(shí)與多方信息審慎甄別。
平臺(tái)聲明：文章內(nèi)容（如有圖片或視頻亦包括在內(nèi)）由作者上傳并發(fā)布，文章內(nèi)容僅代表作者本人觀點(diǎn)，簡書系信息發(fā)布平臺(tái)，僅提供信息存儲(chǔ)服務(wù)。

相關(guān)閱讀更多精彩內(nèi)容

友情鏈接更多精彩內(nèi)容