前言

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

---

MySQL InnoDB 引擎重要的三個(gè)日志：

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

一、binlog

1. 簡(jiǎn)介

概述

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

特點(diǎn)

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

作用

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

所在位置

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

2. 記錄格式

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

3. 寫入機(jī)制

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

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

4. 日志操作命令

4.1 查看啟動(dòng)情況

show variables like'%log_bin%';

4.2 日志查看

命令

日志是二進(jìn)制存儲(chǔ)的，無法直接讀取，需要通過 mysqlbinlog 命令查看。

語(yǔ)法

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

選項(xiàng)含義

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

4.3 日志刪除

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

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

二、redo log

1. 簡(jiǎn)介

概述

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

特點(diǎn)

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

2. 寫入機(jī)制

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

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

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

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

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

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

3. 對(duì)比 binlog

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

4. 兩階段提交

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

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

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

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

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

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

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

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

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

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

三、undo log

1. 簡(jiǎn)介

概述

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

作用

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

特點(diǎn)

undo log 是邏輯日志，可以認(rèn)為：

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

當(dāng)執(zhí)行回滾時(shí)，就可以讀取其中的記錄進(jìn)行操作。

分類

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

2. 版本鏈

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

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

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

四、錯(cuò)誤日志

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

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

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

五、查詢?nèi)罩?

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

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

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

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

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

---

參考

[1] B 站. 黑馬鄧?yán)蠋? MySQL數(shù)據(jù)庫(kù)入門到精通.