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

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

Binlog

一、簡介

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

特點(diǎn)：undo log是二進(jìn)制邏輯日志，記錄內(nèi)容是語句的原始邏輯，屬于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。

二、記錄格式

三、寫入機(jī)制

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

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

四、日志操作命令

查看啟動情況

show variables like'%log_bin%';

日志查看

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

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

選項(xiàng)含義：

-d：指定數(shù)據(jù)庫名稱，只列出指定的數(shù)據(jù)庫相關(guān)操作。；

-o：忽略掉日志中的前n行命令；

-v：將行事件（數(shù)據(jù)變更）重構(gòu)為SQL語句；

-w：將行事件（數(shù)據(jù)變更）重構(gòu)為SQL語句，并輸出注樣信息；

日志刪除

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

redo log

一、簡介

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

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

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

二、寫入機(jī)制

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

   
   

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

   
   

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

   
   

   
   

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

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

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

三、對比Binlog

四、兩階段提交

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

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

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

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

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

如果我們對某條數(shù)據(jù)執(zhí)行了age 更改為 18的操作，此時(shí)原 age 為 17，redo log已經(jīng)寫入了數(shù)據(jù)，而undolog還沒寫入之前數(shù)據(jù)庫崩潰了。

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

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

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

   
   

2. 事務(wù)提交后，并且，binlog寫入成功后，再將Redolog Buffer中的redo log刷新到磁盤持久化存儲，此時(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

一、簡介

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

作用：

1. 異?；貪L，保證事務(wù)的原子性；

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

特點(diǎn)：

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

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

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

分類：

二、版本鏈

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

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

錯(cuò)誤日志

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

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

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

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

該日志記錄了客戶端所有的操作語句，默認(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)罩?/strong>

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

默認(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í)行慢的管理語句；

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

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

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