MySQL 日誌(錯誤日誌、二進位制日誌、查詢日誌、慢查詢日誌)

NO IMAGE

MySQL 中有 4 中不同的日誌,分別是錯誤日誌、二進位制日誌(binlog 日誌)、查詢日誌和,慢查詢日誌,應該充分利用這些日誌對資料庫進行各種維護和調優。

一、錯誤日誌

錯誤日誌時 MySQL 中最重要的日誌之一,它記錄了當 mysqld 啟動和停止時,以及伺服器在執行過程中發生任何嚴重錯誤時的相關資訊。當資料庫出現任何故障導致無法正常使用時,可以首先檢視此日誌。

二、二進位制日誌

二進位制日誌(binlog) 記錄了所有 DDL 語句和 DML 語句,但是不包括資料查詢語句。語句以“事件”的形式儲存,它描述了資料的更改過程。此日誌對於災難時的資料恢復起著極其重要的作用。

1. 日誌的位置和格式

當用 –log-bin[=file_name] 選項啟動時,mysqld 開始將資料變更情況寫入日誌檔案。如果滅有給出 file_name 值,預設名為主機名後面跟“-bin”。如果給出了檔名,但沒有包含路徑,則檔案預設被寫入引數 datadir(資料目錄)指定的目錄。

二進位制日誌的格式分為 3 種:statement、row、mixed,可以在啟動時通過引數 –binlog_format 進行設定,這 3 種格式的區別如下:

STATEMENT

mysql 5.1 之前的版本都採用這種方式,日誌中記錄的都是語句(statement),每一條對資料造成修改的sql語句都會記錄到日誌中,通過 mysqlbinlog 工具,可以清晰的看到每條語句的文字。主從複製時,從庫(slave)會將日誌解析為原文字,並在從庫中重新執行一次。這種格式的優點的日誌記錄清晰易讀,日誌量少,對 I/O 影響較小。缺點是在某些情況下 slave 的日誌複製會出錯。

ROW

mysql 5.1.11 之後,出現了這種新的日誌格式,它將每一行的變更記錄到日誌中,而不是記錄sql語句,比如一個簡單的跟新sql:
update emp set name=’abc’
如果是 statement 格式,日誌中會記錄一行 sql 文字;
如果是 row 格式,由於是對全表進行更新,也就是每一行記錄都會發生改變,如果是一個 100 萬行的大表,則日誌中會記錄 100萬條記錄的變化情況。日誌量大大增加。

這種格式的優點是會記錄每一行資料變化的細節,不會出現某些情況下無法複製的情況,缺點是日誌量大,對 I/O 影響較大。

MIXED

這是目前 MySQL 預設的日誌格式,即混合了 statement 和 row 兩種日誌。預設情況下采用 statement,但在一些特殊情況下采用 row 來進行記錄,比如
採用 NDB 儲存引擎,此時對錶的 DML 語句全部採用 row;
客戶端使用了臨時表;
客戶端採用了不確定函式,比如 current_user() 等;

因為這種不確定函式在主從中得到的值可能不同,導致主從資料產生不一致。mixed 格式能儘量利用兩種模式的優點,而避開他們的缺點。

注意:可以在 global 和 session 級別對 binlog_format 進行日誌格式設定,但一定要謹慎操作,確保從庫的複製能夠正常進行。

2. 日誌的讀取

由於日誌以二進位制方式儲存,不能直接讀取,需要用 mysqlbinlog 工具來檢視,對於 statement 格式的檔案可以用工具直接檢視,對於 row 格式的要加 -v 或 -vv 引數進行讀取。

3. 日誌的刪除

對於比較繁忙的系統,每天產生大量日誌,這些日誌如果長時間不清除,將會對磁碟空間帶來極大的浪費,因此,需要定期刪除日誌。

(1) 刪除所有日誌

執行 “reset master;” 命令將刪除所有 binlog 日誌,新日誌編號從“000001”開始

(2) 刪除指定序號之前的日誌檔案

執行 “purge master logs to ‘mysql-bin.‘”命令,將刪除編號之前的所有日誌(不刪除命令中指定的檔案)

(3) 刪除指定日期前的日誌

執行 “purge master log before ‘yyyy-mm-dd hh24:mi:ss’” 命令將刪除指定日期前的所有日誌

(4) 修改配置檔案,自動刪除

在配置檔案的[mysqld]中設定引數 expire_logs_days=#, 此引數的含義是設定日誌的過期天數,過了指定的天數後日志將會被自動刪除。

4. 其他選項

二進位制日誌記錄了資料的變化過程,對於資料的完整性和安全性起著非常重要的作用。因此,MySQL 還提供了一些其他引數選項來進行更小粒度的管理

–binlog-do-db=db_name

該選項告訴主伺服器,如果當前的資料庫(即 use 選定的資料庫)是 db_name, 應將更新記錄到二進位制檔案中,其他所有麼有顯式指定的資料庫更新將被忽略,不記錄在日誌中。

–binlog-ignore-db=db_name

該選項告訴主伺服器,如果當前的資料庫(即 use 選定的資料庫) 是 db_name,不應將更新儲存到二進位制日誌中,其他沒有顯式忽略的資料庫都將進行記錄。
如果想記錄或忽略多個資料庫,可以對上面兩個選項分別使用多次。

–innodb-safe-binlog

此選項經常和 –sync-binlog = N (每寫 N 次日誌同步磁碟)一起配合使用,使得事務在日誌中的記錄更加安全。

三、查詢日誌

查詢日誌記錄了客戶端的所有語句,而二進位制日誌不包含只查詢資料的語句。

1. 日誌的位置和格式

查詢日誌和慢查詢日誌都可以選擇儲存在檔案或者表中,使用引數 –log-output[=value]來進行控制,value 值可以是table、file、none 的一個或者多個組合,中間用逗號進行分割,分別表示日誌儲存在表、檔案、不儲存在表和檔案中,這裡的表指的是 mysql 庫中的 general_log (慢查詢日誌是 slow_log) 表。
其中 none 的優先順序最高,比如:
–log-output = table, file 表示日誌可以同時輸出到表和檔案中
–log-output = table, none 由於 none 的優先順序高,表示日誌不儲存在表和檔案中。
如果不顯示設定此引數,則預設輸出到檔案。

可以通過引數 –general_log[={0|1}] 和 –general_log_file=file_name 來進行控制。
–general_log 設定為1或者不帶值都可以啟用查詢日誌;設定為0表示關閉查詢日誌,如果不指定此引數也不會啟用。
如果沒有指定 –general_log_file=file_name 的值,且沒有顯式設定 –log-output 引數,那麼日誌將寫入引數 datadir(資料目錄)指定的路徑下,預設檔名為 host_name.log。

這兩個引數都是 global 型別,可以在系統啟動時或者系統執行時進行動態修改,如果想在 session 級別控制,則通過在 session 中設定引數 sql_log_off 為 on 或者 off。

2. 日誌的讀取

查詢日誌記錄的格式是純文字,所以可以直接進行讀取。

注意: log 日誌中記錄了所有資料庫的操作,對於訪問頻繁的系統,此日誌對系統效能的影響較大,建議一般情況下關閉。

四、 慢查詢日誌

慢查詢日誌記錄了所有執行時間超過引數 long_query_time(單位:秒)設定值並且掃描記錄數不小於 min_examined_row_limit 的所有 sql 語句的日誌(注意:獲得表鎖定的時間不算作執行時間)。long_query_time 預設為 10 秒,最小為 0,精度可以到微秒。

在預設情況下,有兩類常見語句不會記錄到慢查詢日誌:管理語句和不使用索引進行查詢的語句。管理語句包括 alter table、analyze table、check table、create index、drop index、optimize table、repair table。如果要監控這兩類 sql語句,可以分別通過設定引數
–log-slow-admin-statements 和 log_queries_not_using_indexes 進行控制。

1. 檔案位置和格式

通過以下兩個引數開啟慢查詢日誌:

(1) –slow_query_log[={0|1}]

如果不指定值或者指定值為 1 都會開啟慢查詢;

(2) –slow_query_log_file[=file_name]

指定慢查詢日誌的路徑

注意: 可以使用 –log-output 引數來指定日誌的輸出方式,預設會輸出到檔案,當然也可以選擇輸出到表,需要注意的是,如果選擇輸出到表,則表中的記錄只能精確到秒,而日誌檔案中可以精確到微秒。

2. 日誌的讀取

和錯誤日誌、查詢日誌一樣,慢查詢日誌記錄的格式也是純文字,可以被直接讀取。
如果要設定微秒級的慢查詢,可以 set global long_query_time=0.01

如果慢查詢日誌中記錄內容很多,可以使用 mysqldumpslow 工具對慢查詢日誌進行分類彙總。對於 sql 文字完全一致,只是變數不同的語句,mysqldumpslow 將會自動視為同一個語句進行統計,變數值用 N 來代替。這個統計結果將大大增加使用者閱讀慢查詢日誌的效率,迅速定位系統的 sql 瓶頸。

注意: 慢查詢日誌對於發現應用中有效能問題的 sql 很有幫助,正常情況下,開啟此日誌並經常檢視分析。