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

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

Binlog

一、簡介

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

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

作用：

1. 災難時的數(shù)據(jù)恢復；

2. MySQL 的主從復制。

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

二、記錄格式

三、寫入機制

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

事務提交的時候，再把binlog cache寫到binlog文件中。

四、日志操作命令

查看啟動情況

show variables like'%log_bin%';

日志查看

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

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

選項含義：

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

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

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

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

日志刪除

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

redo log

一、簡介

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

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

大小是固定的，前面的內容會被覆蓋。

二、寫入機制

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

   
   

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

   
   

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

   
   

   
   

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

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

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

三、對比Binlog

四、兩階段提交

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

那么問題來了，兩個文件到底是哪個先存？以及寫入的時機有什么不同？

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

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

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

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

緊接著數(shù)據(jù)庫重啟后進行恢復，主數(shù)據(jù)庫根據(jù)redo log恢復數(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. 事務提交后，并且，binlog寫入成功后，再將Redolog Buffer中的redo log刷新到磁盤持久化存儲，此時redo log的狀態(tài)是commit；

   
   

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

1. binlog為空則進行數(shù)據(jù)回滾；

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

undo log

一、簡介

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

作用：

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

2. 版本鏈用于MVCC機制中；

特點：

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

2. 當update一條記錄時，它會插入一條對象相反的記錄。

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

分類：

二、版本鏈

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

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

錯誤日志

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

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

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

查詢日志

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

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

2. 設置general_log = 1，1 表示開啟，0 表示關閉；

3. 設置日志的文件名，general_log_file = mysql_query.log，未指定默認為host_name.log。

慢查詢日志

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

默認關閉，開啟需以下配置（根據(jù)所需）：

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

2. 設置show_query_log = 1，1 表示開啟，0 表示關閉；

3. 設置long_query_time = 2，未指定默認為 10 秒；

4. 設置long_show_admin_statements = 1，開啟記錄執(zhí)行慢的管理語句；

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

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

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