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

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

Binlog

一、簡(jiǎn)介

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

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

作用：

1. 災(zāi)難時(shí)的數(shù)據(jù)恢復(fù)；

2. MySQL 的主從復(fù)制。

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

二、記錄格式

三、寫(xiě)入機(jī)制

事務(wù)執(zhí)行過(guò)程中，先把日志寫(xiě)到binlog cache。

事務(wù)提交的時(shí)候，再把binlog cache寫(xiě)到binlog文件中。

四、日志操作命令

查看啟動(dòng)情況

show variables like'%log_bin%';

日志查看

命令：日志是二進(jìn)制存儲(chǔ)的，無(wú)法直接讀取，需要通過(guò)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ǔ)句，并輸出注樣信息；

日志刪除

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

redo log

一、簡(jiǎn)介

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

特點(diǎn)：物理日志，InnoDB存儲(chǔ)引擎獨(dú)有的，保證數(shù)據(jù)的持久性與完整性。記錄內(nèi)容是“在某個(gè)數(shù)據(jù)頁(yè)上做了什么修改”，在事務(wù)過(guò)程中是不斷寫(xiě)入。

大小是固定的，前面的內(nèi)容會(huì)被覆蓋。

二、寫(xiě)入機(jī)制

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

   
   

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

   
   

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

   
   

   
   

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

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

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

三、對(duì)比Binlog

四、兩階段提交

了解了上面的binlog和redo log以后，你會(huì)發(fā)現(xiàn)， MySQL在執(zhí)行更新操作的過(guò)程中，一次事務(wù)的完成均會(huì)記錄著兩個(gè)文件，區(qū)別見(jiàn)上面的對(duì)比表格。

那么問(wèn)題來(lái)了，兩個(gè)文件到底是哪個(gè)先存？以及寫(xiě)入的時(shí)機(jī)有什么不同？

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

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

根據(jù)redo log的特點(diǎn)，在事務(wù)過(guò)程中是不斷寫(xiě)入，而binlog只在事務(wù)提交時(shí)才寫(xiě)入。

如果我們對(duì)某條數(shù)據(jù)執(zhí)行了age 更改為 18的操作，此時(shí)原 age 為 17，redo log已經(jīng)寫(xiě)入了數(shù)據(jù)，而undolog還沒(méi)寫(xiě)入之前數(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)了不一致問(wèn)題。

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

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

   
   

2. 事務(wù)提交后，并且，binlog寫(xiě)入成功后，再將Redolog Buffer中的redo log刷新到磁盤(pán)持久化存儲(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寫(xiě)入成功，但是redo log更改狀態(tài)失敗時(shí)。

undo log

一、簡(jiǎn)介

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

作用：

1. 異常回滾，保證事務(wù)的原子性；

2. 版本鏈用于MVCC機(jī)制中；

特點(diǎn)：

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

2. 當(dāng)update一條記錄時(shí)，它會(huì)插入一條對(duì)象相反的記錄。

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

分類：

二、版本鏈

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

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

錯(cuò)誤日志

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

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

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

查詢?nèi)罩?/strong>

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

1. 修改/etc/my.cnf文件；

2. 設(shè)置general_log = 1，1 表示開(kāi)啟，0 表示關(guān)閉；

3. 設(shè)置日志的文件名，general_log_file = mysql_query.log，未指定默認(rèn)為host_name.log。

慢查詢?nèi)罩?/strong>

該日志記錄了所有執(zhí)行時(shí)間超過(guò)參數(shù)long_query_time，且所記錄數(shù)不小于min_examined_row_limit的所有 SQL 語(yǔ)句。

默認(rèn)關(guān)閉，開(kāi)啟需以下配置（根據(jù)所需）：

1. 修改/etc/my.cnf文件；

2. 設(shè)置show_query_log = 1，1 表示開(kāi)啟，0 表示關(guān)閉；

3. 設(shè)置long_query_time = 2，未指定默認(rèn)為 10 秒；

4. 設(shè)置long_show_admin_statements = 1，開(kāi)啟記錄執(zhí)行慢的管理語(yǔ)句；

5. 設(shè)置long_queries_not_using_indexes = 1，開(kāi)啟記錄執(zhí)行較慢且未使用索引的語(yǔ)句；

通過(guò)對(duì) MySQL 各類關(guān)鍵日志的深入了解，我們清晰認(rèn)識(shí)到它們?cè)跀?shù)據(jù)庫(kù)運(yùn)行的各個(gè)環(huán)節(jié)發(fā)揮的關(guān)鍵作用。無(wú)論是數(shù)據(jù)的持久保存、事務(wù)的正確執(zhí)行，還是問(wèn)題的排查解決，日志都不可或缺。在今后的 MySQL 使用和管理中，合理運(yùn)用這些日志知識(shí)，能讓數(shù)據(jù)庫(kù)運(yùn)行更加穩(wěn)定高效。

想了解更多相關(guān)技術(shù)小分享可以上藍(lán)隊(duì)云官網(wǎng)查閱，更多技術(shù)問(wèn)題，也可以直接咨詢。同時(shí)，藍(lán)隊(duì)云整理了運(yùn)維必備的工具包免費(fèi)分享給大家使用，需要的朋友可以直接咨詢。