MySQL提升筆記（3）日志文件詳解

在MySql數據庫和InnoDB存儲引擎中，有很多種文件，如：參數文件、日志文件、socket文件、pid文件、MySQL表結構文件、存儲引擎文件。

本節重點關注日志文件，MySQL的復制、事務等重要功能都和日志文件相關。日志文件主要包括錯誤日志文件、二進制日志文件、慢查詢日志文件、查詢日志文件、重做日志文件等。其中重做日志文件是InnoDB引擎文件。

1、日志文件介紹

1.1、錯誤日志（error log）

錯誤日志文件對MySQL的啟動、運行、關閉過程進行了記錄，是定位MySQL問題的第一把鑰匙。

1.2、慢查詢日志（slow query log）

慢查詢日志是用來記錄執行時間超過 long_query_time 這個變量定義的時長的查詢語句。通過慢查詢日志，可以查找出哪些查詢語句的執行效率很低，以便進行優化。

1.3、一般查詢日志（general log）

一般查詢日志記錄了所有對MySql數據庫請求的信息，無論請求是否正確執行。

默認情況下，general log 是關閉的，開啟通用查詢日志會增加很多磁盤 I/O， 所以如非出于調試排錯目的，不建議開啟通用查詢日志。

1.4、二進制日志（bin log）

關于二進制日志，它記錄了數據庫所有執行的DDL和DML語句（除了數據查詢語句select、show等），以事件形式記錄并保存在二進制文件中。

log_bin：指定binlog是否開啟及文件名稱。

server_id：指定服務器唯一ID，開啟binlog 必須設置此參數。

binlog_format：指定binlog模式，建議設置為ROW。

max_binlog_size：控制單個二進制日志大小，當前日志文件大小超過此變量時，執行切換動作。

expire_logs_days：控制二進制日志文件保留天數，默認值為0，表示不自動刪除，可設置為0~99。

二進制日志文件是非常重要的日志文件，建議開啟，二進制日志主要有這么幾個作用：

恢復：利用二進制文件恢復數據，原理是取出日志的操作記錄，重新執行

復制：和恢復原理類似，一般分為主庫和從庫。

審計: 通過觀察二進制文件，可以判斷是否存在對數據庫有危險性的操作。

1.5、重做日志（redo log）

對于InnoDB，重做日志至關重要，因為它們記錄了對于InnoDB存儲引擎的事務日志。

當實例或介質失敗時，如數據庫由于所在主機斷電導致實例失敗，InnoDB存儲引擎就會恢復到斷電前的時刻，以此來保證數據的完整性。

InnoDB引擎對數據的更新，是先將更新記錄寫入redo log日志，然后會在系統空閑的時候或者是按照設定的更新策略再將日志中的內容更新到磁盤之中。這就是所謂的預寫式技術（Write Ahead logging）。這種技術可以大大減少IO操作的頻率，提升數據刷新的效率。

寫入重做日志文件也不是直接寫，而是先寫入一個重做日志緩沖，然后按照一定的條件順序寫入日志文件。

redo log日志的大小是固定的，為了能夠持續不斷的對更新記錄進行寫入，在redo log日志中設置了兩個標志位置，checkpoint和write_pos，分別表示記錄擦除的位置和記錄寫入的位置。redo log日志的數據寫入示意圖可見下圖。

當write_pos標志到了日志結尾時，會從結尾跳至日志頭部進行重新循環寫入。所以redo log的邏輯結構并不是線性的，而是可看作一個圓周運動。write_pos與checkpoint中間的空間可用于寫入新數據，寫入和擦除都是往后推移，循環往復的。

當write_pos追上checkpoint時，表示redo log日志已經寫滿。這時不能繼續執行新的數據庫更新語句，需要停下來先刪除一些記錄，執行checkpoint規則騰出可寫空間。

checkpoint規則：checkpoint觸發后，將buffer中臟數據頁和臟日志頁都刷到磁盤。

同樣是記錄事務日志，和bin log有什么不同呢？

bin log會記錄所有與數據庫有關的日志記錄，包括InnoDB、MyISAM等存儲引擎的日志，而redo log只記InnoDB存儲引擎的日志。

記錄的內容不同，bin log記錄的是關于一個事務的具體操作內容，即該日志是邏輯日志。而redo log記錄的是關于每個頁（Page）的更改的物理情況。

寫入的時間不同，bin log僅在事務提交前進行提交，也就是只寫磁盤一次。而在事務進行的過程中，卻不斷有redo ertry被寫入redo log中。

寫入的方式也不相同，redo log是循環寫入和擦除，bin log是追加寫入，不會覆蓋已經寫的文件。

1.6、回滾日志（undo log）

提到了redo log，這里在簡單了解一下回滾日志（undo log）。

回滾日志同樣也是InnoDB引擎提供的日志，顧名思義，回滾日志的作用就是對數據進行回滾。當事務對數據庫進行修改，InnoDB引擎不僅會記錄redo log，還會生成對應的undo log日志；如果事務執行失敗或調用了rollback，導致事務需要回滾，就可以利用undo log中的信息將數據回滾到修改之前的樣子。

但是undo log不redo log不一樣，它屬于邏輯日志。它對SQL語句執行相關的信息進行記錄。當發生回滾時，InnoDB引擎會根據undo log日志中的記錄做與之前相反的工作。比如對于每個數據插入操作（insert），回滾時會執行數據刪除操作（delete）；對于每個數據刪除操作（delete），回滾時會執行數據插入操作（insert）；對于每個數據更新操作（update），回滾時會執行一個相反的數據更新操作（update），把數據改回去。undo log由兩個作用，一是提供回滾，二是實現MVCC。

2、更新語句執行

接下來結合一條更新語句的執行，來進一步理解bin log和redo log這兩種重要日志。

update t set c=c+1 where ID=2;

2.1、更新語句執行流程

我們來看執行器和InnoDB引擎在執行這個簡單的update語句時的內部流程。

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

執行器拿到引擎給的行數據，把這個值加上1，比如原來是N，現在就是N+1，得到新的一行數據，再調用引擎接口寫入這行新數據。

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

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

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

update語句的執行流程圖如下：

2.2、兩階段提交

這條語句的執行過程中，redo log的寫入分成了兩個步驟分成了prepare和commit兩個階段進行提交，這就是所謂的兩階段提交。為什么要用兩階段提交呢？

先用通俗的說法：

比如小明去超市里買一瓶可樂：

小明：老板給我來瓶可樂！透心涼心飛揚的那個。

老板：機器掃一下可樂，告訴小明這瓶可樂2塊5，給錢（記錄 redo log，事務處于prepare狀態）

收錢放入錢箱（記錄 binlog，事務實際是否完成的根本依據，處于待標記commit階段）

然后讓小明把可樂拿走（redo log 狀態標為 commit，表示該事務邏輯閉環）。到這里，代表一筆交易結束。

等算賬前再把這一天賣東西的交易信息一起同步到數據庫。

可見，如果收錢之前（prepare階段，步驟3）交易被打斷，回過頭來處理此次交易，發現只有記了小黑板但沒有收錢，則交易失敗，刪掉小黑板上的記錄（回滾）；

如果收了錢后（commit階段 或 待commit階段，步驟4 || 5）交易被打斷，然后回過頭發現系統上有記錄（prepare）而且錢箱有本次收入（bin log），則說明本次交易有效，補充修改commit狀態，更新到庫存中。

用正式一點的語言來描述。

如果不用兩階段提交，要么就是先寫完redo log再寫binlog，或者采用反過來的順序。我們看看這兩種方式會有什么問題。

仍然用前面的update語句來做例子。假設當前ID=2的行，字段c的值是0，再假設執行update語句過程中在寫完第一個日志后，第二個日志還沒有寫完期間發生了crash，會出現什么情況呢？

先寫redo log后寫binlog。假設在redo log寫完，binlog還沒有寫完的時候，MySQL進程異常重啟。由于我們前面說過的，redo log寫完之后，系統即使崩潰，仍然能夠把數據恢復回來，所以恢復后這一行c的值是1。

但是由于binlog沒寫完就crash了，這時候binlog里面就沒有記錄這個語句。因此，之后備份日志的時候，存起來的binlog里面就沒有這條語句。

然后你會發現，如果需要用這個binlog來恢復臨時庫的話，由于這個語句的binlog丟失，這個臨時庫就會少了這一次更新，恢復出來的這一行c的值就是0，與原庫的值不同。

先寫binlog后寫redo log。如果在binlog寫完之后crash，由于redo log還沒寫，崩潰恢復以后這個事務無效，所以這一行c的值是0。但是binlog里面已經記錄了“把c從0改成1”這個日志。所以，在之后用binlog來恢復的時候就多了一個事務出來，恢復出來的這一行c的值就是1，與原庫的值不同。

可以看到，如果不使用“兩階段提交”，那么數據庫的狀態就有可能和用它的日志恢復出來的庫的狀態不一致。

你可能會說，這個概率是不是很低，平時也沒有什么動不動就需要恢復臨時庫的場景呀？

其實不是的，不只是誤操作后需要用這個過程來恢復數據。當你需要擴容的時候，也就是需要再多搭建一些備庫來增加系統的讀能力的時候，現在常見的做法也是用全量備份加上應用binlog來實現的，這個“不一致”就會導致你的線上出現主從數據庫不一致的情況。

簡單說，redo log和binlog都可以用于表示事務的提交狀態，而兩階段提交就是讓這兩個狀態保持邏輯上的一致。

2.3、日志落盤

保證事務成功，日志必須落盤，這樣，數據庫crash后，就不會丟失某個事務的數據了

innodb_flush_log_at_trx_commit 這個參數設置成 1 的時候，表示每次事務的 redo log 都直接持久化到磁盤。這樣可以保證 MySQL 異常重啟之后數據不丟失。

sync_binlog 這個參數設置成 1 的時候，表示每次事務的 binlog 都持久化到磁盤。這樣可以保證 MySQL 異常重啟之后 binlog 不丟失。

參考：

【1】：《高性能MySQL》

【2】：極客時間 《MySQL實戰45講》

【3】：《MySQL技術內幕 InnoDB存儲引擎》

【4】：聽我講完redo log、binlog原理，面試官老臉一紅

【5】：為了讓你徹底弄懂 MySQL 事務日志，我通宵肝出了這份圖解！

【6】：MySQL中幾種常見的日志

【7】：Mysql學習筆記——重做日志與歸檔日志

【8】：淺析MySQL事務中的redo與undo

MySQL 數據庫

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

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