前言
MySQL 的日誌記錄了執行的各種資訊，是 MySQL 事務、效能、資料容災、異常排查等的基礎。本文將介紹 MySQL 一些關鍵日誌的作用和原理。
MySQL InnoDB 引擎重要的三個日誌：
| 日誌 | 說明 |
|---|---|
| redo log | 重做日誌，保證事務的永續性 |
| undo log | 回滾日誌，來保證事務的原子性 |
| binlog | MySQL 的主從複製中同步資料 |
一、binlog
1. 簡介
概述
binlog記錄DDL 和 DML語句，但不包括SELECT、SHOW 等語句，簡單說只要發上了表結構變化或表資料更新，都會產生binlog日誌。
特點
undo log是二進位制邏輯日誌，記錄內容是語句的原始邏輯，屬於Server層，和引擎無關。只在事務提交時才寫入，適用於資料備份和主從複製。
作用
- 災難時的資料恢復；
- MySQL 的主從複製。
所在位置
通常預設的MySQL資料目錄為/var/lib/mysql。
2. 記錄格式
| 日誌格式 | 記錄內容 |
|---|---|
| Statement | 記錄進行資料修改 SQL 語句。 |
| Row | 記錄每一行的資料變更，佔用較多空間。（預設） |
| Mixed | 前兩者混合，判斷是否可能引起資料不一致：可能則用Row 否則用Statement |
3. 寫入機制
事務執行過程中，先把日誌寫到binlog cache。
事務提交的時候，再把binlog cache寫到binlog檔案中。
binlog cache是為了保證一個事務的所有操作能夠不被拆開，一次性寫入bin log。
binlog cache大小受binlog_cache_size引數控制。
binlog cache寫入策略受sync_binlog引數控制。
4. 日誌操作命令
4.1 檢視啟動情況
show variables like'%log_bin%';
4.2 日誌檢視
命令
日誌是二進位制儲存的，無法直接讀取，需要透過mysqlbinlog命令檢視。
語法
mysqlbinlog [引數選項] logfilename
選項含義
-d：指定資料庫名稱，只列出指定的資料庫相關操作。；-o：忽略掉日誌中的前n行命令；-v：將行事件（資料變更）重構為SQL語句；-w：將行事件（資料變更）重構為SQL語句，並輸出注樣資訊；
4.3 日誌刪除
對於比較繁忙的業務系統，每天生成的binlog資料巨大，如果長時間不清除，將會佔用大量磁碟空間。可以透過以下幾種方式清理日誌：
| 指令 | 含義 |
|---|---|
| reset master | 刪除全部日誌 |
| purge master logs to 'binlog.xxx' | 刪除xxx編號之前的日誌 |
| purge master logs before 'yyyy-mm-dd hh:mm:ss' | 刪除引號時間之前產生的日誌 |
| show variables like '%binlog_expire_logs_seconds%'; | 配置日誌過期時間，到期自動刪除 |
二、redo log
1. 簡介
概述
redo log，重做日誌，記錄的是事務提交時資料頁的物理修改。
特點
物理日誌，InnoDB儲存引擎獨有的，保證資料的永續性與完整性。記錄內容是“在某個資料頁上做了什麼修改”，在事務過程中是不斷寫入。
大小是固定的，前面的內容會被覆蓋。
2. 寫入機制
-
當客戶端提交資料修改時，會先去
Buffer Pool獲取資料，若沒有則查詢出來放入Buffer Pool； -
生成
redo log放入Redolog Buffer，記錄資料頁的物理變化，此時redo log的狀態是prepare； -
事務提交後，將
Redolog Buffer中的redo log重新整理到磁碟持久化儲存，此時redo log的狀態是commit。
這樣即使Buffer Pool中的髒頁重新整理到磁碟時出錯，恢復時也可以透過redo log日誌進行重新重新整理。
髒頁：當記憶體資料頁跟磁碟資料頁內容不一致的時候，我們稱這個記憶體頁為“髒頁”。
WAL：先寫日誌，再寫磁碟的思想，叫做
WAL(Write Ahead Logging)。
3. 對比 binlog
| 對比維度 | redo log | bin log |
|---|---|---|
| 日誌型別 | 物理日誌 | 二進位制邏輯日誌 |
| 寫入時機 | 事務過程中是不斷寫入 | 只在事務提交時才寫入 |
| 位置 | InnoDB 磁碟中 | MySQL 的 Server 層 |
| 空間 | 固定空間，超出則覆蓋 | 追加寫入，可生成多份檔案 |
4. 兩階段提交
瞭解了上面的binlog和redo log以後，你會發現， MySQL在執行更新操作的過程中，一次事務的完成均會記錄著兩個檔案，區別見上面的對比表格。那麼問題來了，兩個檔案到底是哪個先存？以及寫入的時機有什麼不同？
回答這兩個問題之前，需要先考慮另外一個問題，這兩個檔案能否各存各的，會出問題嗎？
答案是：不可以，會出現兩個檔案中資料不一致的問題，可能導致主從資料庫資料不一致。
根據redo log的特點，在事務過程中是不斷寫入，而binlog只在事務提交時才寫入，如果我們對某條資料執行了age 更改為 18的操作，此時原 age 為 17，redo log已經寫入了資料，而undolog還沒寫入之前資料庫崩潰了。
緊接著資料庫重啟後進行恢復，主資料庫根據redo log恢復資料為age = 18，而從資料庫根據binlog日誌進行同步age = 17，這時就出現了不一致問題。
接著我們回答一下開始的兩個問題，為了避免上述問題的產生，InnoDB儲存引擎使用兩階段提交方案：
-
生成
redo log放入Redolog Buffer，記錄資料頁的物理變化，此時redo log的狀態是prepare； -
事務提交後，並且，
binlog寫入成功後，再將Redolog Buffer中的redo log重新整理到磁碟持久化儲存，此時redo log的狀態commit； -
進行資料恢復時，若
redo log的狀態是prepare，則有兩種情況：binlog為空則進行資料回滾；binlog不為空，代表事務已commit，進行資料恢復，這個一般發生在binlog寫入成功，但是redo log更改狀態失敗時。
三、undo log
1. 簡介
概述
undo log，回滾日誌，事務執行時，用於記錄資料被修改前的資訊，在異常發生時，會對已經執行的操作進行回滾。
作用
- 異常回滾，保證事務的原子性；
- 版本鏈用於
MVCC機制中；
特點
undo log是邏輯日誌，可以認為：
- 當
delete一條資料時，它會插入一條對應的insert記錄； - 當
update一條記錄時，它會插入一條物件相反的記錄。
當執行回滾時，就可以讀取其中的記錄進行操作。
分類
- 新增時 ： 指在
insert中產生的日誌。這樣的記錄只對事務本身可見，對其他事務不可見，故可以在事務提交後直接刪除。 - 修改時：
update或delete中產生的日誌。該日誌可能要作用於MVCC機制，因此不能在事務提交時就進行刪除。提交時放入undo log版本鏈，使用後刪除。
2. 版本鏈
不同事務或者相同事務對同一條記錄進行修改，會使該記錄的undo log生成一條記錄版本的連結串列，連結串列頭部是最新的舊記錄，連結串列尾部是最早的舊記錄。
| 隱藏欄位 | 含義 |
|---|---|
| DB_TRX_ID | 表示最後一次插入或修改該行的事務 ID |
| DB_ROLL_PTR | 回滾指標，指向該行的 undo log，若該行未被更新，則為空 |
上述事務能夠看到的版本鏈上的哪條歷史資料，是由MVCC的ReadView來決定。
四、錯誤日誌
最重要的日誌之一，記錄了當mysqld.log啟動和停止時，以及伺服器在執行過程中發生任何嚴重錯誤時的相關資訊，當資料庫出現故障無法使用時，建議先看此日誌。
日誌預設開啟，預設存放目錄/var/log/，預設檔名mysqld.log。
如果找不到，可執行show variables like '%log_error%'檢視。
五、查詢日誌
該日誌記錄了客戶端所有的操作語句，預設關閉，開啟需做以下配置：
- 修改
/etc/my.cnf檔案； - 設定
general_log = 1，1 表示開啟，0 表示關閉； - 設定日誌的檔名，
general_log_file = mysql_query.log，未指定預設為host_name.log。
六、慢查詢日誌
該日誌記錄了所有執行時間超過引數long_query_time，且所記錄數不小於min_examined_row_limit的所有 SQL 語句。預設關閉，開啟需以下配置（根據所需）：
- 修改
/etc/my.cnf檔案； - 設定
show_query_log = 1，1 表示開啟，0 表示關閉； - 設定
long_query_time = 2，未指定預設為 10 秒； - 設定
long_show_admin_statements = 1，開啟記錄執行慢的管理語句； - 設定
long_queries_not_using_indexes = 1，開啟記錄執行較慢且未使用索引的語句；
參考
[1] B 站. 黑馬鄧老師. MySQL資料庫入門到精通.