前言

MySQL 的日志記錄了運行的各種信息，是 MySQL 事務(wù)、性能、數(shù)據(jù)容災(zāi)、異常排查等的基礎(chǔ)。本文將介紹 MySQL 一些關(guān)鍵日志的作用和原理。

---

MySQL InnoDB 引擎重要的三個日志：

日志	說明
redo log	重做日志，保證事務(wù)的持久性
undo log	回滾日志，來保證事務(wù)的原子性
binlog	MySQL 的主從復(fù)制中同步數(shù)據(jù)

一、binlog

1. 簡介

概述

binlog 記錄 DDL 和 DML 語句，但不包括 SELECT 、 SHOW 等語句，簡單說只要發(fā)上了表結(jié)構(gòu)變化或表數(shù)據(jù)更新，都會產(chǎn)生 binlog 日志。

特點

undo log 是二進制邏輯日志，記錄內(nèi)容是語句的原始邏輯，屬于Server層，和引擎無關(guān)。只在事務(wù)提交時才寫入，適用于數(shù)據(jù)備份和主從復(fù)制。

作用

1. 災(zāi)難時的數(shù)據(jù)恢復(fù)；
2. MySQL 的主從復(fù)制。

所在位置

通常默認的MySQL數(shù)據(jù)目錄為 /var/lib/mysql 。

2. 記錄格式

日志格式	記錄內(nèi)容
Statement	記錄進行數(shù)據(jù)修改 SQL 語句。
Row	記錄每一行的數(shù)據(jù)變更，占用較多空間。（默認）
Mixed	前兩者混合，判斷是否可能引起數(shù)據(jù)不一致：可能則用 Row 否則用 Statement

3. 寫入機制

事務(wù)執(zhí)行過程中，先把日志寫到 binlog cache 。
事務(wù)提交的時候，再把 binlog cache 寫到 binlog 文件中。

binlog cache 是為了保證一個事務(wù)的所有操作能夠不被拆開，一次性寫入 bin log 。
binlog cache 大小受 binlog_cache_size 參數(shù)控制。
binlog cache 寫入策略受 sync_binlog 參數(shù)控制。

4. 日志操作命令

4.1 查看啟動情況

show variables like'%log_bin%';

4.2 日志查看

命令

日志是二進制存儲的，無法直接讀取，需要通過 mysqlbinlog 命令查看。

語法

mysqlbinlog [參數(shù)選項] logfilename

選項含義

• -d ：指定數(shù)據(jù)庫名稱，只列出指定的數(shù)據(jù)庫相關(guān)操作。；
• -o ：忽略掉日志中的前n行命令；
• -v ：將行事件（數(shù)據(jù)變更）重構(gòu)為SQL語句；
• -w ：將行事件（數(shù)據(jù)變更）重構(gòu)為SQL語句，并輸出注樣信息；

4.3 日志刪除

對于比較繁忙的業(yè)務(wù)系統(tǒng)，每天生成的 binlog 數(shù)據(jù)巨大，如果長時間不清除，將會占用大量磁盤空間�？梢酝ㄟ^以下幾種方式清理日志：

指令	含義
reset master	刪除全部日志
purge master logs to 'binlog.xxx'	刪除xxx編號之前的日志
purge master logs before 'yyyy-mm-dd hh:mm:ss'	刪除引號時間之前產(chǎn)生的日志
show variables like '%binlog_expire_logs_seconds%';	配置日志過期時間，到期自動刪除

二、redo log

1. 簡介

概述

redo log ，重做日志，記錄的是事務(wù)提交時數(shù)據(jù)頁的物理修改。

特點

物理日志，InnoDB存儲引擎獨有的，保證數(shù)據(jù)的持久性與完整性。記錄內(nèi)容是“在某個數(shù)據(jù)頁上做了什么修改”，在事務(wù)過程中是不斷寫入。
大小是固定的，前面的內(nèi)容會被覆蓋。

2. 寫入機制

1. 當客戶端 提交數(shù)據(jù)修改 時，會先去 Buffer Pool 獲取數(shù)據(jù)，若沒有則查詢出來放入 Buffer Pool ；

2. 生成 redo log 放入 Redolog Buffer ，記錄數(shù)據(jù)頁的物理變化，此時 redo log 的狀態(tài)是 prepare ；

3. 事務(wù)提交后 ，將 Redolog Buffer 中的 redo log 刷新到磁盤持久化存儲，此時 redo log 的狀態(tài)是 commit 。

這樣即使 Buffer Pool 中的臟頁刷新到磁盤時出錯，恢復(fù)時也可以通過 redo log 日志進行重新刷新。

臟頁：當內(nèi)存數(shù)據(jù)頁跟磁盤數(shù)據(jù)頁內(nèi)容不一致的時候，我們稱這個內(nèi)存頁為“臟頁”。

WAL：先寫日志，再寫磁盤的思想，叫做 WAL(Write Ahead Logging) 。

3. 對比 binlog

對比維度	redo log	bin log
日志類型	物理日志	二進制邏輯日志
寫入時機	事務(wù)過程中是不斷寫入	只在事務(wù)提交時才寫入
位置	InnoDB 磁盤中	MySQL 的 Server 層
空間	固定空間，超出則覆蓋	追加寫入，可生成多份文件

4. 兩階段提交

了解了上面的 binlog 和 redo log 以后，你會發(fā)現(xiàn)， MySQL在執(zhí)行更新操作的過程中，一次事務(wù)的完成均會記錄著兩個文件，區(qū)別見上面的對比表格。那么問題來了，兩個文件到底是哪個先存？以及寫入的時機有什么不同？

回答這兩個問題之前，需要先考慮另外一個問題， 這兩個文件能否各存各的 ，會出問題嗎？

答案是：不可以，會出現(xiàn) 兩個文件中數(shù)據(jù)不一致 的問題，可能導(dǎo)致 主從數(shù)據(jù)庫數(shù)據(jù)不一致 。

根據(jù) redo log 的特點，在事務(wù)過程中是不斷寫入，而 binlog 只在事務(wù)提交時才寫入，如果我們對某條數(shù)據(jù)執(zhí)行了 age 更改為 18 的操作，此時原 age 為 17， redo log 已經(jīng)寫入了數(shù)據(jù)，而 undolog 還沒寫入之前數(shù)據(jù)庫崩潰了。

緊接著數(shù)據(jù)庫重啟后進行恢復(fù)，主數(shù)據(jù)庫根據(jù) redo log 恢復(fù)數(shù)據(jù)為 age = 18 ，而從數(shù)據(jù)庫根據(jù) binlog 日志進行同步 age = 17 ，這時就出現(xiàn)了不一致問題。

接著我們回答一下開始的兩個問題，為了避免上述問題的產(chǎn)生，InnoDB存儲引擎使用 兩階段提交 方案：

1. 生成 redo log 放入 Redolog Buffer ，記錄數(shù)據(jù)頁的物理變化，此時 redo log 的狀態(tài)是 prepare ；

2. 事務(wù)提交后 ，并且， binlog 寫入成功后，再將 Redolog Buffer 中的 redo log 刷新到磁盤持久化存儲，此時 redo log 的狀態(tài) commit ；

3. 進行數(shù)據(jù)恢復(fù)時，若 redo log 的 狀態(tài)是 prepare ，則有兩種情況：

   1. binlog 為空則進行數(shù)據(jù)回滾；
   2. binlog 不為空，代表事務(wù)已 commit ，進行數(shù)據(jù)恢復(fù)，這個一般發(fā)生在 binlog 寫入成功，但是 redo log 更改狀態(tài)失敗時。

三、undo log

1. 簡介

概述

undo log ，回滾日志，事務(wù)執(zhí)行時，用于記錄數(shù)據(jù)被修改前的信息，在異常發(fā)生時，會對已經(jīng)執(zhí)行的操作進行回滾。

作用

1. 異�；貪L，保證事務(wù)的原子性；
2. 版本鏈用于 MVCC 機制中；

特點

undo log 是邏輯日志，可以認為：

1. 當 delete 一條數(shù)據(jù)時，它會插入一條對應(yīng)的 insert 記錄；
2. 當 update 一條記錄時，它會插入一條對象相反的記錄。

當執(zhí)行回滾時，就可以讀取其中的記錄進行操作。

分類

1. 新增時 ： 指在 insert 中產(chǎn)生的日志。這樣的記錄只對事務(wù)本身可見，對其他事務(wù)不可見，故可以在事務(wù)提交后直接刪除。
2. 修改時： update 或 delete 中產(chǎn)生的日志。該日志可能要作用于 MVCC 機制，因此不能在事務(wù)提交時就進行刪除。提交時放入 undo log 版本鏈，使用后刪除。

2. 版本鏈

不同事務(wù)或者相同事務(wù)對同一條記錄進行修改，會使該記錄的 undo log 生成一條記錄版本的鏈表，鏈表頭部是最新的舊記錄，鏈表尾部是最早的舊記錄。

隱藏字段	含義
DB_TRX_ID	表示最后一次插入或修改該行的事務(wù) ID
DB_ROLL_PTR	回滾指針，指向該行的 undo log，若該行未被更新，則為空

上述事務(wù)能夠看到的版本鏈上的哪條歷史數(shù)據(jù)，是由 MVCC 的 ReadView 來決定。

四、錯誤日志

最重要的日志之一，記錄了當 mysqld.log 啟動和停止時，以及服務(wù)器在運行過程中發(fā)生任何嚴重錯誤時的相關(guān)信息，當數(shù)據(jù)庫出現(xiàn)故障無法使用時，建議先看此日志。

日志默認打開，默認存放目錄 /var/log/ ，默認文件名 mysqld.log 。

如果找不到，可執(zhí)行 show variables like '%log_error%' 查看。

五、查詢?nèi)罩?

該日志記錄了客戶端所有的操作語句，默認關(guān)閉，開啟需做以下配置：

1. 修改 /etc/my.cnf 文件；
2. 設(shè)置 general_log = 1 ，1 表示開啟，0 表示關(guān)閉；
3. 設(shè)置日志的文件名， general_log_file = mysql_query.log ，未指定默認為 host_name.log 。

六、慢查詢?nèi)罩?

該日志記錄了所有執(zhí)行時間超過參數(shù) long_query_time ，且所記錄數(shù)不小于 min_examined_row_limit 的所有 SQL 語句。默認關(guān)閉，開啟需以下配置（根據(jù)所需）：

1. 修改 /etc/my.cnf 文件；
2. 設(shè)置 show_query_log = 1 ，1 表示開啟，0 表示關(guān)閉；
3. 設(shè)置 long_query_time = 2 ，未指定默認為 10 秒；
4. 設(shè)置 long_show_admin_statements = 1 ，開啟記錄執(zhí)行慢的管理語句；
5. 設(shè)置 long_queries_not_using_indexes = 1 ，開啟記錄執(zhí)行較慢且未使用索引的語句；

---

參考

[1] B 站. 黑馬鄧老師. MySQL數(shù)據(jù)庫入門到精通.