詳解MySQL四種Log機制 以日誌稽核解析追蹤事件

·log-bin：設定相關紀錄儲存的檔案位置。
·binlog_format：設定要儲存的格式，提供了Statement|Row|mixed型式。

▲圖3 my.cnf設定範例。

在設定完成後，必須重新啟動MySQL伺服器，設定值方能生效。重新啟動MySQL伺服器後，可登入MySQL資料庫，使用更動表格指令，如圖4中的update指令。執行update指令後，就會在「/tmp/mysqld-bin.log」檔案內記錄已被更動的相關資訊。

▲圖4 使用update指令更動表格。

由於該檔案為二進位檔並非文字檔，所以無法直接讀取該檔案。但可利用mysqlbinlog程式來讀取該檔，使用以下方法即能讀取該二進位檔案的內容：

查詢日誌

在上述二進位日誌的紀錄，並不包括使用者查詢（Select）的動作，如果想要記錄所有的資料庫動作，必須使用查詢日誌的功能。

但要特別注意的是，啟用此選項時，由於會記錄大量的查詢紀錄動作，因此可能造成大量的磁碟存取，以至於拖慢系統效率。啟用查詢日誌動作時，必須在「my.cnf」設定如下選項（#為註解說明），相關組態設定如圖5所示：

▲圖5 在my.cnf內設定組態。

由於查詢日誌是以純文字格式記錄，因此可以簡易地查詢該檔案，如圖6所示即為查詢日誌的範例。

▲圖6 查詢日誌內容。

慢查詢日誌

很多時候，由於不適當的SQL指令往往會造成資料庫效能低下，因為大量的資源都用來處理這些不適當的SQL指令。

在這種情況下，可利用啟動「慢查詢日誌」記錄功能，來追蹤是否有超出所設定執行時間的SQL指令，藉此找出執行時間過長的SQL指令。

啟動「慢查詢日誌」功能，需要設定下列組態：

·log_slow_queries：設定儲存慢查詢日誌的檔案 位置。
·long_query_time：設定執行時間的門檻值，一旦超過此門檻值，就會記錄該SQL指令的資訊，單位為秒。

以圖7為例，即是設定將執行時間超過1秒的SQL指令記錄到「/tmp/mysqld_slow.log」檔案內。

▲圖7 將執行時間超過1秒的SQL指令記錄到「/tmp/mysqld_slow.log」檔案內。

在重新啟動MySQL伺服器後，可簡單地執行一個超過1秒的SQL指令來測試慢查詢紀錄，如果一切正常，在「/tmp/mysqld_slow.log」檔案內應該可看到如圖8所示的紀錄內容，可看出執行超過1秒的SQL指令。

▲ 圖8 在「/tmp/mysqld_slow.log」檔案內找出執行超過1秒的SQL指令。

平心而論，MySQL伺服器提供了多樣化的系統日誌，但在稽核（Audit）日誌上的表現就差強人意了。

例如，想知道那一個來源IP針對某個資料庫（Database）上的資料庫表格（Table）做了那些的資料庫動作，如新增（Insert）、刪除（Delete）或修改（Update），或曾登入MySQL資料庫成功或失敗的資訊時，這些稽核日誌就不是MySQL伺服器所能夠完整提供。 對此，本文特別介紹兩個MySQL UDF程式來彌補MySQL資料庫在稽核日誌支援上面的不足，如下所述：

·audit_login：以json格式記錄登入MySQL資料庫成功或失敗的紀錄。
·mysql-audit：記錄個別資料庫中的資料庫表格新增、刪除及修改動作的紀錄。

安裝audit_login模組

先說明如何安裝及使用audit_login軟體。由於安裝audit_login必須與MySQL資料庫軟體的原始共同編譯，因此先下載MySQL資料庫的原始碼，這裡使用5.5.32版本並連結至「https://github.com/outbrain/audit_login」網址下載audit_login程式（檔名設為audit_login.zip）。

相關套件的原始碼均下載完成後，再繼續以下的安裝步驟：