MySQL不會丟失數據的秘密，就藏在它的7種日志里
本文轉載自微信公眾號「程序員內點事」，作者程序員內點事。轉載本文請聯系程序員內點事公眾號。
進入正題前先簡單看看MySQL的邏輯架構，相信我用的著。
MySQL邏輯架構
MySQL的邏輯架構大致可以分為三層：
- 第一層：處理客戶端連接、授權認證，安全校驗等。
- 第二層：服務器server層，負責對SQL解釋、分析、優化、執行操作引擎等。
- 第三層：存儲引擎，負責MySQL中數據的存儲和提取。
我們要知道MySQL的服務器層是不管理事務的，事務是由存儲引擎實現的，而MySQL中支持事務的存儲引擎又屬InnoDB使用的最為廣泛，所以后續文中提到的存儲引擎都以InnoDB為主。
MySQL數據更新流程
記住! 記住! 記住! 上邊這張圖，她是MySQL更新數據的基礎流程，其中包括redo log、bin log、undo log三種日志間的大致關系，好了閑話少說直奔主題。
redo log(重做日志)
redo log屬于MySQL存儲引擎InnoDB的事務日志。
MySQL的數據是存放在磁盤中的，每次讀寫數據都需做磁盤IO操作，如果并發場景下性能就會很差。為此MySQL提供了一個優化手段，引入緩存Buffer Pool。這個緩存中包含了磁盤中部分數據頁(page)的映射，以此來緩解數據庫的磁盤壓力。
當從數據庫讀數據時，首先從緩存中讀取，如果緩存中沒有，則從磁盤讀取后放入緩存;當向數據庫寫入數據時，先向緩存寫入，此時緩存中的數據頁數據變更，這個數據頁稱為臟頁，Buffer Pool中修改完數據后會按照設定的更新策略，定期刷到磁盤中，這個過程稱為刷臟頁。
MySQL宕機
如果刷臟頁還未完成，可MySQL由于某些原因宕機重啟，此時Buffer Pool中修改的數據還沒有及時的刷到磁盤中，就會導致數據丟失，無法保證事務的持久性。
為了解決這個問題引入了redo log，redo Log如其名側重于重做!它記錄的是數據庫中每個頁的修改，而不是某一行或某幾行修改成怎樣，可以用來恢復提交后的物理數據頁，且只能恢復到最后一次提交的位置。
redo log用到了WAL(Write-Ahead Logging)技術，這個技術的核心就在于修改記錄前，一定要先寫日志，并保證日志先落盤，才能算事務提交完成。
有了redo log再修改數據時，InnoDB引擎會把更新記錄先寫在redo log中，在修改Buffer Pool中的數據，當提交事務時，調用fsync把redo log刷入磁盤。至于緩存中更新的數據文件何時刷入磁盤，則由后臺線程異步處理。
注意：此時redo log的事務狀態是prepare，還未真正提交成功，要等bin log日志寫入磁盤完成才會變更為commit，事務才算真正提交完成。
這樣一來即使刷臟頁之前MySQL意外宕機也沒關系，只要在重啟時解析redo log中的更改記錄進行重放，重新刷盤即可。
大小固定
redo log采用固定大小，循環寫入的格式，當redo log寫滿之后，重新從頭開始如此循環寫，形成一個環狀。
那為什么要如此設計呢?
因為redo log記錄的是數據頁上的修改，如果Buffer Pool中數據頁已經刷磁盤后，那這些記錄就失效了，新日志會將這些失效的記錄進行覆蓋擦除。
上圖中的write pos表示redo log當前記錄的日志序列號LSN(log sequence number)，寫入還未刷盤，循環往后遞增;check point表示redo log中的修改記錄已刷入磁盤后的LSN，循環往后遞增，這個LSN之前的數據已經全落盤。
write pos到check point之間的部分是redo log空余的部分(綠色)，用來記錄新的日志;check point到write pos之間是redo log已經記錄的數據頁修改數據，此時數據頁還未刷回磁盤的部分。當write pos追上check point時，會先推動check point向前移動，空出位置(刷盤)再記錄新的日志。
注意：redo log日志滿了，在擦除之前，需要確保這些要被擦除記錄對應在內存中的數據頁都已經刷到磁盤中了。擦除舊記錄騰出新空間這段期間，是不能再接收新的更新請求的，此刻MySQL的性能會下降。所以在并發量大的情況下，合理調整redo log的文件大小非常重要。
crash-safe
因為redo log的存在使得Innodb引擎具有了crash-safe的能力，即MySQL宕機重啟，系統會自動去檢查redo log，將修改還未寫入磁盤的數據從redo log恢復到MySQL中。
MySQL啟動時，不管上次是正常關閉還是異常關閉，總是會進行恢復操作。會先檢查數據頁中的LSN，如果這個 LSN 小于 redo log 中的LSN，即write pos位置，說明在redo log上記錄著數據頁上尚未完成的操作，接著就會從最近的一個check point出發，開始同步數據。
簡單理解，比如：redo log的LSN是500，數據頁的LSN是300，表明重啟前有部分數據未完全刷入到磁盤中，那么系統則將redo log中LSN序號300到500的記錄進行重放刷盤。
undo log(回滾日志)
undo log也是屬于MySQL存儲引擎InnoDB的事務日志。
undo log屬于邏輯日志，如其名主要起到回滾的作用，它是保證事務原子性的關鍵。記錄的是數據修改前的狀態，在數據修改的流程中，同時會記錄一條與當前操作相反的邏輯日志到undo log中。
我們舉個栗子：假如更新ID=1記錄的name字段，name原始數據為小富，現改name為程序員內點事
事務執行update X set name = 程序員內點事 where id =1語句時，先會在undo log中記錄一條相反邏輯的update X set name = 小富 where id =1記錄，這樣當某些原因導致服務異常事務失敗，就可以借助undo log將數據回滾到事務執行前的狀態，保證事務的完整性。
那可能有人會問：同一個事物內的一條記錄被多次修改，那是不是每次都要把數據修改前的狀態都寫入undo log呢?
答案是不會的!
undo log只負責記錄事務開始前要修改數據的原始版本，當我們再次對這行數據進行修改，所產生的修改記錄會寫入到redo log，undo log負責完成回滾，redo log負責完成前滾。
回滾
未提交的事務，即事務未執行commit。但該事務內修改的臟頁中，可能有一部分臟塊已經刷盤。如果此時數據庫實例宕機重啟，就需要用回滾來將先前那部分已經刷盤的臟塊從磁盤上撤銷。
前滾
未完全提交的事務，即事務已經執行commit，但該事務內修改的臟頁中只有一部分數據被刷盤，另外一部分還在buffer pool緩存上，如果此時數據庫實例宕機重啟，就需要用前滾來完成未完全提交的事務。將先前那部分由于宕機在內存上的未來得及刷盤數據，從redo log中恢復出來并刷入磁盤。
數據庫實例恢復時，先做前滾，后做回滾。
如果你仔細看過了上邊的 MySQL數據更新流程圖 就會發現，undo log、redo log、bin log三種日志都是在刷臟頁之前就已經刷到磁盤了的，相互協作最大限度保證了用戶提交的數據不丟失。
bin log(歸檔日志)
bin log是一種數據庫Server層(和什么引擎無關)，以二進制形式存儲在磁盤中的邏輯日志。bin log記錄了數據庫所有DDL和DML操作(不包含 SELECT 和 SHOW等命令，因為這類操作對數據本身并沒有修改)。
默認情況下，二進制日志功能是關閉的。可以通過以下命令查看二進制日志是否開啟：
- mysql> SHOW VARIABLES LIKE 'log_bin';
- +---------------+-------+
- | Variable_name | Value |
- +---------------+-------+
- | log_bin | OFF |
- +---------------+-------+
bin log也被叫做歸檔日志，因為它不會像redo log那樣循環寫擦除之前的記錄，而是會一直記錄日志。一個bin log日志文件默認最大容量1G(也可以通過max_binlog_size參數修改)，單個日志超過最大值，則會新創建一個文件繼續寫。
- mysql> show binary logs;
- +-----------------+-----------+
- | Log_name | File_size |
- +-----------------+-----------+
- | mysq-bin.000001 | 8687 |
- | mysq-bin.000002 | 1445 |
- | mysq-bin.000003 | 3966 |
- | mysq-bin.000004 | 177 |
- | mysq-bin.000005 | 6405 |
- | mysq-bin.000006 | 177 |
- | mysq-bin.000007 | 154 |
- | mysq-bin.000008 | 154 |
bin log日志的內容格式其實就是執行SQL命令的反向邏輯，這點和undo log有點類似。一般來說開啟bin log都會給日志文件設置過期時間(expire_logs_days參數，默認永久保存)，要不然日志的體量會非常龐大。
- mysql> show variables like 'expire_logs_days';
- +------------------+-------+
- | Variable_name | Value |
- +------------------+-------+
- | expire_logs_days | 0 |
- +------------------+-------+
- 1 row in set
- mysql> SET GLOBAL expire_logs_days=30;
- Query OK, 0 rows affected
bin log主要應用于MySQL主從模式(master-slave)中，主從節點間的數據同步;以及基于時間點的數據還原。
主從同步
通過下圖MySQL的主從復制過程，來了解下bin log在主從模式下的應用。
- 用戶在主庫master執行DDL和DML操作，修改記錄順序寫入bin log;
- 從庫slave的I/O線程連接上Master，并請求讀取指定位置position的日志內容;
- Master收到從庫slave請求后，將指定位置position之后的日志內容，和主庫bin log文件的名稱以及在日志中的位置推送給從庫;
- slave的I/O線程接收到數據后，將接收到的日志內容依次寫入到relay log文件最末端，并將讀取到的主庫bin log文件名和位置position記錄到master-info文件中，以便在下一次讀取用;
- slave的SQL線程檢測到relay log中內容更新后，讀取日志并解析成可執行的SQL語句，這樣就實現了主從庫的數據一致;
基于時間點還原
我們看到bin log也可以做數據的恢復，而redo log也可以，那它們有什么區別?
- 層次不同：redo log 是InnoDB存儲引擎實現的，bin log 是MySQL的服務器層實現的，但MySQL數據庫中的任何存儲引擎對于數據庫的更改都會產生bin log。
- 作用不同：redo log 用于碰撞恢復(crash recovery)，保證MySQL宕機也不會影響持久性;bin log 用于時間點恢復(point-in-time recovery)，保證服務器可以基于時間點恢復數據和主從復制。
- 內容不同：redo log 是物理日志，內容基于磁盤的頁Page;bin log的內容是二進制，可以根據binlog_format參數自行設置。
- 寫入方式不同：redo log 采用循環寫的方式記錄;binlog 通過追加的方式記錄，當文件大小大于給定值后，后續的日志會記錄到新的文件上。
- 刷盤時機不同：bin log在事務提交時寫入;redo log 在事務開始時即開始寫入。
bin log 與 redo log 功能并不沖突而是起到相輔相成的作用，需要二者同時記錄，才能保證當數據庫發生宕機重啟時，數據不會丟失。
relay log(中繼日志)
relay log日志文件具有與bin log日志文件相同的格式，從上邊MySQL主從復制的流程可以看出，relay log起到一個中轉的作用，slave先從主庫master讀取二進制日志數據，寫入從庫本地，后續再異步由SQL線程讀取解析relay log為對應的SQL命令執行。
slow query log
慢查詢日志(slow query log): 用來記錄在 MySQL 中執行時間超過指定時間的查詢語句，在 SQL 優化過程中會經常使用到。通過慢查詢日志，我們可以查找出哪些查詢語句的執行效率低，耗時嚴重。
出于性能方面的考慮，一般只有在排查慢SQL、調試參數時才會開啟，默認情況下，慢查詢日志功能是關閉的。可以通過以下命令查看是否開啟慢查詢日志：
- mysql> SHOW VARIABLES LIKE 'slow_query%';
- +---------------------+--------------------------------------------------------+
- | Variable_name | Value |
- +---------------------+--------------------------------------------------------+
- | slow_query_log | OFF |
- | slow_query_log_file | /usr/local/mysql/data/iZ2zebfzaequ90bdlz820sZ-slow.log |
- +---------------------+--------------------------------------------------------+
通過如下命令開啟慢查詢日志后，我發現 iZ2zebfzaequ90bdlz820sZ-slow.log 日志文件里并沒有內容啊，可能因為我執行的 SQL 都比較簡單沒有超過指定時間。
- mysql> SET GLOBAL slow_query_log=ON;
- Query OK, 0 rows affected
上邊提到超過 指定時間 的查詢語句才算是慢查詢，那么這個時間閾值又是多少嘞?我們通過 long_query_time 參數來查看一下，發現默認是 10 秒。
- mysql> SHOW VARIABLES LIKE 'long_query_time';
- +-----------------+-----------+
- | Variable_name | Value |
- +-----------------+-----------+
- | long_query_time | 10.000000 |
- +-----------------+-----------+
這里我們將 long_query_time 參數改小為 0.001秒再次執行查詢SQL，看看慢查詢日志里是否有變化。
- mysql> SET GLOBAL long_query_time=0.001;
- Query OK, 0 rows affected
果然再執行 SQL 的時，執行時間大于 0.001秒，發現慢查詢日志開始記錄了。
慢查詢日志
general query log
一般查詢日志(general query log)：用來記錄用戶的所有操作，包括客戶端何時連接了服務器、客戶端發送的所有SQL以及其他事件，比如 MySQL 服務啟動和關閉等等。MySQL服務器會按照它接收到語句的先后順序寫入日志文件。
由于一般查詢日志記錄的內容過于詳細，開啟后 Log 文件的體量會非常龐大，所以出于對性能的考慮，默認情況下，該日志功能是關閉的，通常會在排查故障需獲得詳細日志的時候才會臨時開啟。
我們可以通過以下命令查看一般查詢日志是否開啟，命令如下：
- mysql> show variables like 'general_log';
- +---------------+-------+
- | Variable_name | Value |
- +---------------+-------+
- | general_log | OFF |
- +---------------+-------+
下邊開啟一般查詢日志并查看日志存放的位置。
- mysql> SET GLOBAL general_log=on;
- Query OK, 0 rows affected
- mysql> show variables like 'general_log_file';
- +------------------+---------------------------------------------------+
- | Variable_name | Value |
- +------------------+---------------------------------------------------+
- | general_log_file | /usr/local/mysql/data/iZ2zebfzaequ90bdlz820sZ.log |
- +------------------+---------------------------------------------------+
執行一條查詢 SQL 看看日志內容的變化。
- mysql> select * from t_config;
- +---------------------+------------+---------------------+---------------------+
- | id | remark | create_time | last_modify_time |
- +---------------------+------------+---------------------+---------------------+
- | 1325741604307734530 | 我是廣播表 | 2020-11-09 18:06:44 | 2020-11-09 18:06:44 |
- +---------------------+------------+---------------------+---------------------+
我們看到日志內容詳細的記錄了所有執行的命令、SQL、SQL的解析過程、數據庫設置等等。
一般查詢日志
error log
錯誤日志(error log): 應該是 MySQL 中最好理解的一種日志，主要記錄 MySQL 服務器每次啟動和停止的時間以及診斷和出錯信息。
默認情況下，該日志功能是開啟的，通過如下命令查找錯誤日志文件的存放路徑。
- mysql> SHOW VARIABLES LIKE 'log_error';
- +---------------+----------------------------------------------------------------+
- | Variable_name | Value |
- +---------------+----------------------------------------------------------------+
- | log_error | /usr/local/mysql/data/LAPTOP-UHQ6V8KP.err |
- +---------------+----------------------------------------------------------------+
注意：錯誤日志中記錄的可并非全是錯誤信息，像 MySQL 如何啟動 InnoDB 的表空間文件、如何初始化自己的存儲引擎，初始化 buffer pool 等等，這些也記錄在錯誤日志文件中。
總結
MySQL作為我們工作中最常接觸的中間件，熟練使用只算是入門，如果要在簡歷寫上一筆精通，還需要深入了解其內部工作原理，而這7種日志也只是深入學習過程中的一個起點，學無止境，兄嘚干就完了!
