MySQL更新資料時,日誌(redo log、binlog)執行流程

.SegmentFault發表於2022-01-29

1:背景

專案需要做Es和資料庫的同步,而手動在程式碼中進行資料同步又是Es的一些不必要的資料同步操作和業務邏輯耦合,所以使用的了讀取mysql的binlog日誌的方式進行同步Es的資料。

問題1:根據binlog同步資料的時候會不會出現業務邏輯利用事務運算元據的時候,當事務還沒有提交的時候,是否能夠讀到binlog,也就是binlog的寫入時機(是事務提交的之前寫,還是事務提交後寫)。
問題2:如果事務提交之前寫入binlog,那麼事務提交之前,事務回滾,那麼binlog又會出現什麼情況?

首先我們做一下實驗(前提是必須要開啟binlog),SQL初始化語句

create table user
(
  id     bigint     not null auto_increment
      primary key,
  name   varchar(64) null,
  status tinyint(1) null
);
insert into user (name,status)values ('張三',1);
set autocommit = 0;

 

2.1:事務提交對binlog的影響

我們先確定一下MySQL執行更新SQL語句之後,執行commit命令前後,binlog會有什麼變化。

然後我們先使用 show master status 看一下binlog的位置

 

 

然後我們執行一下更新語句

update user set status = status + 1;

再次使用 show master status 檢視發現並沒有Position並沒有變,執行commit命令之後,發現Position由141169變為了141506,說明從庫只有在主庫提交之後才能讀到主庫寫入的binlog日誌。

 

 

2.2:事務回滾對binlog的影響

我們再確定一下MySQL執行更新SQL語句之後,執行rollback命令前後,binlog會有什麼變化。

然後我們先使用 show master status 看一下binlog的位置

 

 

然後我們執行一下更新語句

update user set status = status + 1;

再次使用 show master status 檢視發現並沒有Position並沒有變,執行rollback命令之後,發現Position仍然沒有變化,說明事務回滾之後binlog並不會寫入磁碟。

 

 

 

難道binlog是在事務提交之後才寫入磁碟的嘛?那redo log 又是什麼時候寫入磁碟的呢?有上面的問題又引發一系列的問題,帶著這些問題,我們來進行mysql日誌的深入學習。

3:MySQL更新資料的執行流程

首先我們要先了解一下當我們做一條資料的更新操作的時候,資料庫的底層到底是如何執行的?

MySQL更新資料執行流程:

1:判斷資料頁是否在記憶體中,若為否,則從磁碟讀取資料到記憶體中,返回資料行
2:若是資料頁在記憶體中,則直接返回資料行
3:執行資料更新操作
4:資料寫入記憶體,同時redolog寫入到記憶體
5:執行commit操作(此commit是SQL命令操作,而不是資料的commit狀態)
6:執行commit命令之後,則進行兩段提交操作。
6.1:寫入記憶體中的redolog到磁碟中,此時redolog處於prepare狀態
6.2:寫入binlog到磁碟
6.3:提交事務,此時事務處於commit狀態
7:結束。

注:以上操作為引數innodb_flush_log_at_trx_commit 為1和sync_binlog為1的時候。

對應MySQL的更新語句執行流程圖,如圖1-1。

 

 

redo log:被稱之為重做日誌,是在資料庫發生意外時,進行資料恢復,redo log會備份是事務執行過程中的修改資料。

binlog: 被稱為歸檔日誌,是一個二進位制格式的檔案,用於記錄使用者對資料庫更新的SQL語句資訊,格式分為(statement、row、mixed)

redo log 和binlog的差異如下表:

redo logbinlog
InnoDB引擎 MySQL Serve
物理日誌 邏輯日誌
迴圈寫入 追加寫入

MySQL執行commit命令之後是使用兩段提交的辦法來保證事物的原子行的,至於為什麼使用兩段提交,而不是其他的提交方式,由於篇幅有限,不做多餘解釋,請參考自行查詢資料。

到目前為止,關於上面binlog的問題也就迎刃而解了,

對於問題一:MySQL中binlog在事務提交之前會寫入redo log和binlog到記憶體中,在執行commit命令之後進行兩段提交操作,將redo log和binlog寫入磁碟,因此在事務提交之前不能讀取到binlog日誌(前提binlog沒有進行被動刷盤)。

對於問題二:binlog是在執行commit命令之後進行的刷盤,但是是在事務在commit狀態之前寫入的磁碟。根據上面的實驗可以看出,事務回滾對於binlog並沒有什麼影響。

小結:在執行commit命令前,執行更新資料到記憶體之後,那麼就會寫入redo log和binlog到 redo log buffer和binlog buffer中,當執行了commit命令之後,就是進行兩段提交操作,然後進行redo log和binlog寫入磁碟操作。

關於MySQL的日誌刷盤機制是由引數innodb_flush_log_at_trx_commit 和sync_binlog控制的,具體請參考下一篇文章。

 

4:commit命令和commit狀態區別解釋

我們上面說的commit命令是指MySQL語法中的commit命令,用於提交事務,一般跟 begin/start transaction 配對使用。

而我們圖中用到的這個“commit 步驟”,指的是事務提交過程中的一個小步驟,也是最後一步。當這個步驟執行完成後,這個事務就提交完成了。

“commit 命令”執行的時候,會包含“commit 步驟”。

 

相關文章