mysql日誌詳細解析

MySQL日誌：

主要包含：錯誤日誌、查詢日誌、慢查詢日誌、事務日誌、二進位制日誌；

日誌是mysql資料庫的重要組成部分。日誌檔案中記錄著mysql資料庫執行期間發生的變化；也就是說用來記錄mysql資料庫的客戶端連線狀況、SQL語句的執行情況和錯誤資訊等。當資料庫遭到意外的損壞時，可以透過日誌檢視檔案出錯的原因，並且可以透過日誌檔案進行資料恢復。

錯誤日誌

在mysql資料庫中，錯誤日誌功能是預設開啟的。並且，錯誤日誌無法被禁止。預設情況下，錯誤日誌儲存在mysql資料庫的資料檔案中。錯誤日誌檔案通常的名稱為hostname.err。其中，hostname表示伺服器主機名。

錯誤日誌資訊可以自己進行配置的，錯誤日誌所記錄的資訊是可以透過log-error和log-warnings來定義的，其中log-err是定義是否啟用錯誤日誌的功能和錯誤日誌的儲存位置，log-warnings是定義是否將警告資訊也定義至錯誤日誌中。預設情況下錯誤日誌大概記錄以下幾個方面的資訊：伺服器啟動和關閉過程中的資訊（未必是錯誤資訊，如mysql如何啟動InnoDB的表空間檔案的、如何初始化自己的儲存引擎的等等）、伺服器執行過程中的錯誤資訊、事件排程器執行一個事件時產生的資訊、在從伺服器上啟動伺服器程式時產生的資訊。

下面我們來定義mysql錯誤日誌的功能：

一般而言，日誌級別的定義沒有回話變數都只是在全域性級別下進行定義。

其中，log_error可以直接定義為檔案路徑，也可以為ON|OFF；log_warings只能使用1|0來定義開關啟動。

更改錯誤日誌位置可以使用log_error來設定形式如下：

解析：其中，DIR引數指定錯誤日誌的路徑filename引數是錯誤日誌的名稱，沒有指定該引數時預設為主機名。重啟mysql伺服器即可生效。

檢視mysql錯誤日誌：

刪除錯誤日誌：

在mysql5.5.7之前：資料庫管理員可以刪除很長時間之前的錯誤日誌，以保證mysql伺服器上的硬碟空間。mysql資料庫中，可以使用mysqladmin命令開啟新的錯誤日誌。mysqladmin命令的語法如下：mysqladmin –u root –pflush-logs也可以使用登入mysql資料庫中使用FLUSHLOGS語句來開啟新的錯誤日誌。

在mysql5.5.7之後：伺服器將關閉此項功能。只能使用重新命名原來的錯誤日誌檔案，手動沖洗日誌建立一個新的：方式如下：

更多資訊請查閱官方文件：http://dev.mysql.com/doc/refman/5.5/en/error-log.html

查詢日誌：

預設情況下查詢日誌是關閉的。由於查詢日誌會記錄使用者的所有操作，其中還包含增刪查改等資訊，在併發操作大的環境下會產生大量的資訊從而導致不必要的磁碟IO，會影響mysql的效能的。如若不是為了除錯資料庫的目的建議不要開啟查詢日誌。

檢視查詢日誌是否開啟：

擴充解析：日誌的輸出位置一般有三種方式：file(檔案)，table(表)，none(不儲存)；其中前兩個輸出位置可以同時定義，none表示是開啟日誌功能但是記錄日誌資訊。file就是透過general_log_file |/mydata/data/stu18.log 等方式定義的，而輸出位置定義為表時檢視日誌的內容方式為：

慢查詢日誌：

慢查詢日誌是用來記錄執行時間超過指定時間的查詢語句。透過慢查詢日誌，可以查詢出哪些查詢語句的執行效率很低，以便進行最佳化。一般建議開啟，它對伺服器效能的影響微乎其微，但是可以記錄mysql伺服器上執行了很長時間的查詢語句。可以幫助我們定位效能問題的。

檢視慢查詢日誌的定義：

啟動和設定慢查詢日誌：

1、透過配置檔案my.cnf中的log-slow-queries選項可以開啟慢查詢日誌；形式如下：

其中，DIR引數指定慢查詢日誌的儲存路徑；filename引數指定日誌的檔名，生成日誌檔案的完成名稱為filename-slow.log。如果不指定儲存路徑，慢查詢日誌預設儲存到mysql資料庫的資料檔案下，如果不指定檔名，預設檔名為hostname-slow.log。

2、透過登入mysql伺服器直接定義，方式如下：

首先要有全域性許可權；然後執行mysql>set global slow_query_log=1;

時間預設超過多少的稱為慢查詢日誌？

一般都是透過long_query_time選項來設定這個時間值，時間以秒為單位，可以精確到微秒。如果查詢時間超過了這個時間值（預設為10秒），這個查詢語句將被記錄到慢查詢日誌中。檢視伺服器預設時間值方式如下：

註釋：其中這個慢查詢時間並不是只表示語句自身執行超過10秒還包含由於其他資源被徵用造成阻塞的查詢執行時間或其他原因等都被記錄到慢查詢中。所以這個慢查的時長表示從查詢開始到查詢結束中間包含可能的任何原因所經歷的所有時間。

測試是否可以記錄日誌：

檢視慢查詢日誌：

事務日誌：

事務日誌（InnoDB特有的日誌）可以幫助提高事務的效率。使用事務日誌，儲存引擎在修改表的資料時只需要修改其記憶體複製，再把改修改行為記錄到持久在硬碟上的事務日誌中，而不用每次都將修改的資料本身持久到磁碟。事務日誌採用追加的方式，因此寫日誌的操作是磁碟上一小塊區域內的順序I/O，而不像隨機I/O需要在磁碟的多個地方移動磁頭，所以採用事務日誌的方式相對來說要快得多。事務日誌持久以後，記憶體中被修改的資料在後臺可以慢慢的刷回到磁碟。目前大多數的儲存引擎都是這樣實現的，我們通常稱之為預寫式日誌，修改資料需要寫兩次磁碟。

如果資料的修改已經記錄到事務日誌並持久化，但資料本身還沒有寫回磁碟，此時系統崩潰，儲存引擎在重啟時能夠自動恢復這部分修改的資料。具有的恢復方式則視儲存引擎而定。

檢視事務日誌的定義：

二進位制日誌：

二進位制日誌也叫作變更日誌，主要用於記錄修改資料或有可能引起資料改變的mysql語句，並且記錄了語句發生時間、執行時長、操作的資料等等。所以說透過二進位制日誌可以查詢mysql資料庫中進行了哪些變化。一般大小體積上限為1G。

二進位制開啟狀態：

二進位制日誌相關的引數：

二進位制日誌定義方式：

其一、log_bin可以直接定義為檔案路徑，也可以為ON|OFF。

其二、透過編輯my.cnf中的log-bin選項可以開啟二進位制日誌；形式如下：

其中，DIR引數指定二進位制檔案的儲存路徑；filename引數指定二級制檔案的檔名，其形式為filename.number，number的形式為000001、000002等。每次重啟mysql服務或執行mysql> flush logs;都會生成一個新的二進位制日誌檔案，這些日誌檔案的number會不斷地遞增。除了生成上述的檔案外還會生成一個名為filename.index的檔案。這個檔案中儲存所有二進位制日誌檔案的清單又稱為二進位制檔案的索引。

如果說我們向某個表的某個欄位插入一個資料而這個資料為當前時間(日期時間型)；過段時間將此二進位制檔案應用到另一臺伺服器上資料就會變動從而導致資料的不一致性所以說對於這種非確定性的資料使用預設的語句定義並不是可靠的；

二進位制日誌中常用的定義格式：

1、語句(statement)：預設的記錄格式；

2、行(row)：定義的並非資料本身而是這一行的資料是什麼；

3、混合模式(mixed)：交替使用行和語句、由mysql伺服器自行判斷。

其中基於行的定義格式資料量會大一些但是可以保證資料的精確性。

檢視二進位制日誌：

二進位制日誌的定義方式為二進位制格式；使用此格式可以儲存更多的資訊，並且可以使寫入二進位制日誌的效率更高。但是不能直接使用檢視命令開啟並檢視二進位制日誌。

小擴充套件：二進位制日誌的記錄位置：通常為上一個事件執行結束時間的位置，每一個日誌檔案本身也有自己的後設資料所以說對於當前版本的mysql來說二進位制的開始位置通常為107；

檢視當前二進位制檔案的資訊：

檢視二進位制日誌資訊的命令：

命令列下檢視二進位制日誌：

由於無法使用cat等方式直接開啟並檢視二進位制日誌；所以必須使用mysqlbinlog命令。但是當正在執行mysql讀寫操作時建議不要使用此開啟正在使用的二進位制日誌檔案；若非要開啟可flush logs。mysqlbinlog命令的使用方式：

匯出此資料庫的資訊：

匯入此資料庫的資訊：

刪除二進位制日誌資訊：

二進位制日誌會記錄大量的資訊（其中包含一些無用的資訊）。如果很長時間不清理二進位制日誌，將會浪費很多的磁碟空間。但是，刪除之後可能導致資料庫崩潰時無法進行恢復，所以若要刪除二進位制日誌首先將其和資料庫備份一份，其中也只能刪除備份前的二進位制日誌，新產生的日誌資訊不可刪(可以做即時點還原)。也不可在關閉mysql伺服器之後直接刪除因為這樣可能會給資料庫帶來錯誤的。若非要刪除二進位制日誌需要做如下操作：匯出備份資料庫和二進位制日誌檔案進行壓縮歸檔儲存。刪除二進位制檔案的方法如下：

1、刪除所有的二進位制日誌（不可效仿）：

使用RESET MASTER語句可以刪除所有的二進位制日誌。該語句的形式如下：

解析：首先不建議在生產環境下使用此操作；刪除所有的二進位制日誌後，Mysql將會重新建立新的二進位制日誌。新二進位制日誌的編號從000001開始。

2、根據檔案或時間點來刪除二進位制日誌：

語法形式：

其中TO'log_name'表示把這個檔案之前的其他檔案都刪除掉，也可使用BEFORE datetime_expr指定把哪個時間之前的二進位制檔案刪除了。

由此可以看出這種清理二進位制日誌檔案的方式是非常合理的，不會導致資料庫的錯誤發生。