一生摯友redo log、binlog《死磕MySQL系列 二》

一生摯友redo log、binlog

系列文章

前言

一、redo log

二、如何根據項目情況設置innodb_log_file_size

二、binlog

三、什么是兩階段提交

四、為什么需要兩階段提交

五、《孔乙己》讓你明白redo log是什么

六、redo log參數詳解

系列文章

原來一條select語句在MySQL是這樣執行的《死磕MySQL系列 一》

一生摯友redo log、binlog《死磕MySQL系列 二》

前言

上期根據一條查詢語句查詢流程分析MySQL的整體架構。同樣，本期也使用一條查詢SQL語句來做引子。可以肯定的是，查詢語句執行的流程更新語句同樣也會執行。

因此本期的著重點就不在MySQL架構圖上，文章標題也給出了大家重點，就是要了解redo log、binlog。

一、redo log

第一步，創建一個表 user，主鍵是 id，下面是創建語句。

CREATE TABLE `user` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(255) NOT NULL, `age` tinyint(4) NOT NULL, `time` int(11) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

1

2

3

4

5

6

7

8

插入一條數據

insert into user (`name`,`age`,`time`) values ("咔咔","25",unix_timestamp(now()))

1

若要將插入的這條數據的age改為26，則需要執行語句

update user set age = 26 where id = 1;

1

第一期文章中提到一條查詢語句的執行流程，該流程與更新語句相同。這里將那幅圖拿過來在熟悉一下。

每個模塊的功能可以回到第一期文章去查看。

在MySQL8.0中redo log、binlog日志文件都位于/var/lib/mysql此目錄下，如圖

文件名為ib_logfile的是重做日志，undo開頭的就是回滾日志，對于回滾日志后期進行詳細的討論。

redo log（重做日志）是實現事務持久性必備要素，當一個事務提交后，并非直接修改數據庫的數據，而是首先保證在 redo log中記錄相關的操作。

Innodb存儲引擎中的redo log大小是固的，上圖顯示配置了一組兩個文件，每個文件大小默認為48M，使用innodb_log_file_size參數來控制單個文件大小，在MySQL5.6.8以及之后版本都默認為48M。

然后redo log可以記錄48M的操作，redo log是一個閉環的循環寫。所設定的文件個數和文件大小不再增加。

write pos將記錄當前位置，同時向后移動，在ib-log-file-3文件末尾后，然后返回ib-logfilg-0文件開始寫。

check point記錄的是當前擦除的位置，要使文件循環寫入，必須一邊擦除。清楚數據的前提是要將記錄更新到數據文件。

上面的綠色部分就是可寫的部分，假設如果 writepos追上了 checkpoint，那該怎么辦？

你必須理解write pos的推進是因為在執行更新操作，這樣就不能再執行更新操作，直到記錄更新到數據文件，然后check point進行擦除后才可以繼續執行更新操作。

對于innodb_log_file_size的設置也是有一些計算規則的，下面將為你介紹。

若innodb_log_file_size設置太小，將導致redo log文件頻繁切換，頻繁的觸發數據庫的檢查點（check point），導致記錄更新到數據文件的次數增加，從而影響IO性能。

同樣，如果有一個大的事務，并且所有 redo log日志都已寫滿，但是還沒有完成，將導致日志無法切換，從而導致 MySQL直接堵死。

innodb_log_file_size設置太大，雖然極大地提高了 IO性能，但是在 MySQL重啟或宕機時，恢復時間會因為 redo log文件過大而延長。而這種恢復時間通常是無法控制的。

在設置合理的redo log大小和數量后，Innodb能夠保證，即使數據庫發生異常重啟，以前提交的記錄也不會丟失，這一點也稱為crash-safe。

在這里，對crash-safe的理解先不提及它是什么，后面的文章會讓你明白。

二、如何根據項目情況設置innodb_log_file_size

對于參數innodb_log_files_in_group設置3~4個就夠用了，不用進行優化。

著重討論innodb_log_file_size的大小設置或優化設置。

在 MySQL8.0之前，通常是計算在一段時間內生成的事務日志(redo log)大小，而 MySQL日志文件最小應承載一小時的業務日志量。

此處的一段時間必須視自己的業務情況而定，外界有用1分鐘的日志量也有1小時的日志量來計算。

首先看一下 MySQL客戶端的一個命令 pager，在 MySQL日常操作中，通過設置 pager的顯示方式，可以大大提高工作效率。

目前，要查看 sequence在一分鐘之內的值，您就可以執行 pager grep sequence，它對mysql> show engine innodb status\ G select sleep (60); show engine innodbstatus\ G;返回的結果。

禁止 pager設置執行 nopager，如果不執行該命令，則只有等到下一次重新啟動該命令才會失效。

此處咔咔是在虛擬機上做的操作，可以看到一分鐘內是沒有任何操作，所以值前后相同，你可以在測試服務器做測試。

這樣計算出來的select (后邊數據-前面的數據)/1024/1024*60 asMB_per_hour;值是一個小時后 redo log的大小

但是用這種方法計算一定是不合適的，在一分鐘內業務繁忙或者業務空閑時間計算出的值都會產生較大誤差。

合適的方法是在一天中確定幾個時間點，用一個腳本定時執行，然后記錄相應的值，再取平均值，計算出的誤差將減至最小。

什么是 sequece?

當每個 binlog生成時，該值從1開始，然后遞增，每增加一個事務， sequenumber就加上1。

二、binlog

您可以從總體上了解到 MySQL架構分為兩層，一個是 server層，另一個是存儲引擎層。

server層當然是負責功能方面的，而存儲引擎層則負責處理與存儲相關的操作。

而且上面提到的redo log是Innodb存儲引擎層特有的，其它存儲引擎是不具備的，而server層也有自己的日志記錄，就是將要聊到的binlog。

redo log和binlog的區別

redo log是Innodb引擎特有的，而binlog是MySQLserver層特有的，所有引擎都可以使用。

redo log是物理日志，它記錄的是一條更新操作所做的修改，binlog是邏輯日志，記錄的是一條更新語句執行邏輯

redo log是循環寫的，并且空間是固定的，比如上面配置4個1GB的redo log文件，binlog是追加寫的，這個文件寫完了，換下一個文件，不會覆蓋以前的日志。這也就是你經常看到只要你有完整的binlog文件就可以給你恢復到你想要的數據。

MySQL為什么會有倆份日志呢？

在沒有Innodb存儲引擎之前，MySQL默認存儲引擎是MyIsam，但MyIsam是沒有重啟恢復能力的，binlog日志也僅用于歸檔。

Innodb是另一家公司以插件的形式引入到Mysql，既然binlog沒有重啟恢復的能力，那么我就使用redo log來實現重啟恢復的功能。

這就導致了當你使用Innodb存儲引擎時會寫倆份日志。

三、什么是兩階段提交

對redo log、binlog有了一定的認識后再來看看一條更新語句的執行流程。

update user set age = age + 1 where id = 1;

執行器先到引擎層找到id = 1這一行，由于ID是主鍵，所以會在主鍵索引樹找到這一行。如果ID=2這一行所在的數據頁本來就在內存中，就直接返回給執行器。否則，需要先從磁盤中讀入內存，然后再返回。

執行器拿到存儲引擎返回id = 2結果后，給age加上1，原來是25，現在就是26,在調用引擎接口寫入這行新數據。

引擎將這行數據先更新到內存中，同時將這個更新操作記錄到redo log中，此時redo log處于prepare狀態。然后告知執行器執行完成了，隨時可以提交事務。

接著執行器生成這個操作的binlog，并把binlog寫入磁盤。

執行器調用引擎的提交事務接口，引擎把剛剛寫入的redo log改成提交commit狀態，更新完成。

到這里你應該就清晰了，一條更新SQL會先寫redo log再寫binlog，這也就是標題為什么叫一生摯友redo log、binlog。

四、為什么需要兩階段提交

是為了讓redo log跟binlog兩份日志之間的邏輯一致，看下面倆種情況。

先寫redo log后寫binlog

更新語句為age = age +1

將數據寫入redo log，MySQL進程異常重啟

此時binlog還沒有開始寫

系統重啟后進行數據恢復此時的值為26

需要搭建從庫時需要拿binlog進行恢復數據，但此時age = age +1 這行的操作是沒有記錄到binlog的

那么此時的從庫就會少這一次的更新，恢復出來的age依然是25，造成于主庫數據不一致。

先寫binlog后寫redo log

更新語句為age = age +1

將數據寫入binlog，MySQL異常重啟

此時redo log 還沒寫

MySQL系統重啟，這個更新操作是對于redo log是不存在的，所以重啟后的值依然是25

但binlog 中的值已將是26了

需要搭建從庫時，從庫的值是26，主庫的值是25，造成主從數據不一致

所以說，如果不使用兩階段提交，那么原庫和用它的binlog日志恢復出來的庫數據是不一致的。

五、《孔乙己》讓你明白redo log是什么

來看一個初中九年級語文課文中《孔乙己》這篇文章，就算不記得內容，標題總記得哈！

這個案例也是看丁老師文章中提到的，為什么丁老可以靈活的使用這個案例來講redo log而我們想不到呢？

其本質原因是對知識點沒有理解透徹，使用生活案例來解釋技術是讓人最容易理解并不難遺忘的。

《孔乙己》中的主人公就叫他酒店掌柜，掌柜的有倆件法寶讓比其他老板工作效率高很多。一個是小黑板另一個是賬本。

試想一下如果有客人要賒賬，是直接寫到黑板效率高，還是翻密密麻麻的賬本來的快呢？

掌柜肯定會選擇先記錄到黑板上，等人少或者不忙時再把黑板的記錄寫到賬本中。

反之老板沒有黑板的話，只能在密密麻麻的賬本中先找到賒賬人的名字，如果之前有賒賬記錄追加，找了一遍發現沒有才進行新增。

這個過程不僅繁瑣而且效率低的讓人難以接受，如果酒店客人多老板是記錄不過來的。

同樣，在MySQL中也會存在這個問題，每次執行更新語句都需要先找到那條記錄，然后再更新，整個過程IO成本、查找成本都很高。所以MySQL也利用了酒店掌柜的智慧使用黑板來提升執行效率。

畫一幅圖讓大家能更好的理解掌柜、黑板、在MySQL中的對應關系。

六、redo log參數詳解

事務的持久性就是通過重做日志來實現的。

當提交事務之后，并不是直接修改數據庫的數據的，而是先保證將相關的操作記錄到redo日志中。

數據庫會根據相應的機制將內存的中的臟頁數據刷新到磁盤中。

上圖是一個簡單的重做日志寫入流程。

在上圖中提到倆個陌生概念，Buffer pool、redo log buffer，這個倆個都是Innodb存儲引擎的內存區域的一部分。

而redo log file是位于磁盤位置。

也就說當有DML（insert、update、delete）操作時，數據會先寫入Buffer pool，然后在寫到重做日志緩沖區。

重做日志緩沖區會根據刷盤機制來進行寫入重做日志中。

這個機制的設置參數為innodb_flush_log_at_trx_commit，參數分別為0,1，2

上圖即為重做日志的寫入策略。

當這個參數的值為0的時，提交事務之后，會把數據存放到redo log buffer中，然后每秒將數據寫進磁盤文件

當這個參數的值為1的時，提交事務之后，就必須把redo log buffer從內存刷入到磁盤文件里去，只要事務提交成功，那么redo log就必然在磁盤里了。

當這個參數的值為2的情況，提交事務之后，把redo log buffer日志寫入磁盤文件對應的os cache緩存里去，而不是直接進入磁盤文件，1秒后才會把os cache里的數據寫入到磁盤文件里去。

服務器異常停止對事務如何應對（事務寫入過程）

當參數為0時，前一秒的日志都保存在日志緩沖區，也就是內存上，如果機器宕掉，可能丟失1秒的事務數據。

當參數為1時，數據庫對IO的要求就非常高了，如果底層的硬件提供的IOPS比較差，那么MySQL數據庫的并發很快就會由于硬件IO的問題而無法提升。

當參數為2時，數據是直接寫進了os cache緩存，這部分屬于操作系統部分，如果操作系統部分損壞或者斷電的情況會丟失1秒內的事務數據，這種策略相對于第一種就安全了很多，并且對IO要求也沒有那么高。

小結

關于性能：0>2>1

關于安全：1>2>0

根據以上結論，所以說在MySQL數據庫中，刷盤策略默認值為1，保證事務提交之后，數據絕對不會丟失。

堅持學習、堅持寫作、堅持分享是咔咔從業以來所秉持的信念。愿文章在偌大的互聯網上能給你帶來一點幫助，我是咔咔，下期見。

MySQL 數據庫

版權聲明：本文內容由網絡用戶投稿，版權歸原作者所有，本站不擁有其著作權，亦不承擔相應法律責任。如果您發現本站中有涉嫌抄襲或描述失實的內容，請聯系我們jiasou666@gmail.com 處理，核實后本網站將在24小時內刪除侵權內容。

版權聲明：本文內容由網絡用戶投稿，版權歸原作者所有，本站不擁有其著作權，亦不承擔相應法律責任。如果您發現本站中有涉嫌抄襲或描述失實的內容，請聯系我們jiasou666@gmail.com 處理，核實后本網站將在24小時內刪除侵權內容。