前言
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資料庫入門到精通.