首頁 >資料庫 >mysql教程 >Mysql中錯誤日誌、binlog日誌、查詢日誌、慢查詢日誌簡介

Mysql中錯誤日誌、binlog日誌、查詢日誌、慢查詢日誌簡介

黄舟原創: 2017-03-01 14:05:411646瀏覽

前言

資料庫的日誌是幫助資料庫管理員，追蹤分析資料庫曾經發生的各種事件的有力依據，mysql中提供了錯誤日誌、binlog日誌(二進位日誌)、查處日誌、慢查詢日誌。在此，我力求解決以下問題：各日誌的作用是什麼？怎樣去控制這些日誌呢？怎樣去使用這些日誌提供的資訊呢？

錯誤日誌

1.錯誤日誌作用

##錯誤日誌記錄了mysql啟動和停止時，以及伺服器運行過程中發生任何嚴重性錯誤的相關資訊。當資料庫出現任何故障導致無法啟動時候，例如mysql啟動異常，我們可先檢查此日誌。在mysql中，錯誤日誌日誌（還有其他日誌），不僅可以儲存在檔案中，當然還可以儲存到資料的表中，至於實現方式，筆者也正在研究中···

2.錯誤日誌控制與使用

1.設定

透過log-error=[file-name]來設定（在mysql的設定檔中），如果沒有指定file_name，mysqld使用錯誤日誌名稱為host_name.err(host_name為主機名稱)，並預設在參數datadir（保存資料的目錄）指定的目錄中寫入日誌檔案。

例如我本地使用的是WampServer整合環境

##其中log-error= D:/wamp/logs/mysql.log

#如下圖

如果我將log-error註解掉（#log-error=D:/wamp/logs/mysql.log），重新啟動伺服器，則可以查看到錯誤日誌檔案在datadir指定的目錄下

##2.查看錯誤日誌

#錯誤日誌的格式：時間[錯誤等級] 錯誤訊息

#如果你覺得透過mysql設定檔來定位錯誤日誌所在位置比較麻煩，你完全可以透過再客戶端透過指令來查看錯誤日誌所在位置

使用指令式：show variables like 'log_error';

以下是mysql啟動日誌

################# ########

二進位日誌

#1.作用

##二進位日誌（又叫binlog日誌）記錄了所有的DDL（資料定義語言）語句和DML（資料操作語言）語句，但是不包含資料查詢語句，語句是以「事件」的形式保存的，它描述資料更改的過程。此日誌的兩個主要功能是：資料的復原與資料的複製。

#資料的復原：MySQL本身俱備資料備份與復原功能。例如，我們每天午夜12:00進行資料的備份。如果某天，下午13:00,資料庫發生故障，導致資料庫內容遺失。我們可以透過二進位日誌解決這個問題。解決想法是，可以先將前一天午夜12:00的資料備份檔案還原到資料庫，然後再使用二進位日誌回覆從前一天午夜12:00到當天13:00對資料庫的操作。

資料複製：MySQL支援主從伺服器間的資料複製功能，並透過此功能實現資料庫的冗餘機制以確保資料庫的可用性和提高資料庫德性能。 MySQL正是透過二進位日誌實現資料的傳遞。主伺服器上的二進位日誌內容會被傳送到各個從伺服器上，並在每個從伺服器上執行，從而保證了主從伺服器之間資料的一致性。

2.二進位日誌控制與使用

1.開啟

##在預設情況下，mySQL不會記錄二進位日誌。怎樣才能開啟MySQL的二進位日誌記錄功能呢？

我們可以透過MySQL的設定檔來控制MySQL啟動二進位日誌記錄功能。透過修改參數log-bin=[base_name]來啟動MySQL二進位日誌。 mySQL會將修改的資料庫內容的語句記錄到以base_name-bin.0000x為名的日誌檔案中，其中bin代表binary，後綴00000x代表二進位日誌檔案的順序，每次啟動Mysql，日誌檔案順序會自動加1.如果base_name沒有定義，MySQL將使用pid-file參數設定的值作為二進位日誌檔案的基礎名字。

#例如我將log-bin檔案名稱定為mybinlog，那麼將會在D:/wamp/bin/mysql/mysql5 .6.17/data目錄下，產生mybinlog.00000x的二進位日誌檔。

二進位日誌檔案如下圖

透過使用show variables like'log_bin'檢查bin-log日誌是否開啟。

2.檢視

MySQL二進位日誌主要是供MySQL內部使用的，並不是為了資料庫管理員閱讀使用，因此，二進位日誌與其他日誌一個重要的不同是，二進位檔案的格式不是文字格式，其內容不能透過記事本直接查看，為了方便管理員管理，MySQL提供了mysqlbinlog工具查看二進位日誌內容。

例如：mysqlbinlog D:\wamp\bin\mysql\mysql5.6.17\data\mybinlog.000003

執行結果如下：

#現在我們來做一個測試，看看bin日誌是否記錄了我更新資料庫的操作

例如我將資料表t2中id2=2的那行記錄的id1改為5。然後在查詢二進位日誌文件，看是否記錄了我的操作。

結果和明顯，二進位檔案記錄了我修改資料庫的操作，並且還記錄了我是修改那個資料庫裡面的數據，至於我查詢語句，它並沒有記錄。

3.二進位日誌的刪除

#對應比較方繁忙的系統來說，由於每天產生大量的日誌，這些日子如果長時間不清楚（或轉移），將會對磁碟空間帶來很大的浪費。因此，定期刪除日誌是DBA維護MYSQL資料庫的一個重要的內容。

1.透過reset master 指令

#執行reset master指令，該指令將刪除所有的binlog日誌。新的日誌檔案的編號從000001開始。

2.透過purege master logs to指令

透過執行purge master logs to'base_name.xxxxxx'，刪除'xxxxxx '編號之前的所有日誌。下面我將刪除mybinlog.000003之前的所有日誌。

如下圖：

3.透過purge master logs beffor '時間' 指令#執行

purge master logs beffor '時間' 表示刪除'時間'之前的所有日誌。例如刪除2016-04-01 00:00:00之前的所有日誌，指令如下：

##purge master logs beffor '2016 -04-01 00:00:00';

#4.透過在設定檔中設定參數expire_logs_days

透過設定參數expire_logs_days=#，來指定日誌過期的天數，過了指定的天數，日誌將會被自動刪除，這個是我比較喜歡的方式。例如設定expire_logs_day=3,代表3天後會被自動刪除。

4.二進位日誌重要參數說明

#max_binlog_size：指定單一二進位日誌檔案的最大值，如果超過該值，則產生新的二進位日誌文件，後綴名+1，並記錄到.index檔案中。

binlog_cache_size:快取區大小

sync_binlog:表示沒寫快取多少次就同步到磁碟，如果設定N為1，表示採用同步寫到磁碟的方式寫入二進位檔案。 MySQL中系統預設的設定是sync_binlog=0，也就是不做任何強制性的磁碟刷新指令，這時候的效能是最好的，但是風險也是最大的。因為一旦系統Crash，在binlog_cache中的所有binlog資訊都會被遺失。而當設定為「1」的時候，是最安全但是效能損耗最大的設定。因為設定為1的時候，即使系統Crash，也最多遺失binlog_cache中未完成的一個事務，對實際資料沒有任何實質影響。

binlog-do-db：需要記錄哪些資料庫的日子，預設值為空，表示將所有庫日誌同步到二進位日誌中。

#binlog-ignore-db：需要忽略哪些資料庫的日子

#log-slave-update:建立主從資料庫時候需要配置

######

binglog_format:可選值有statement（記錄邏輯sql語句）、row（記錄表的行更動狀況）、mixed

##5.利用二進位日誌進行資料復原

在前面討論過如果數據出現異常，想將其恢復到在某個時間點的數據，僅僅靠二進制往往是不夠的，我們還需要的是在這個時間點之前備份的資料。

為了方便觀察效果，現在我已經將我的資料庫備份了，此時資料表t1中資料如下：

從現在開始，我需要對資料做一些操作，例如更新或是插入操作，操作後，t1資料如下圖

#此時，如果很不幸運的事情發生了，有駭客闖入進來了，將我的t1表資料全部刪除了，那麼我怎麼得到的駭客刪除之前的資料呢？

#第一步：我需要將我的資料還原到我備份的數據，還原後結果如下：

第二步：我需要利用我二進位日誌檔案還原從資料備份那刻到被駭客攻擊前的那刻的所有資料操作

執行：mysqlbinlog D:\wamp\bin\mysql\mysql5.6.17\data
\mybinlog.000004

分析二進位日誌我們發現在'at 637'行，我們的資料遭到了駭客的攻擊，所有我們只需要還原at 637行之前的所有操作，忽略之後的所有操作即可。

於是我們可以執行下列指令來還原我們的資料：

#mysqlbinlog D:\wamp\bin\mysql\mysql5.6.17\data\mybinlog.000004 --stop-pos=637|mysql -uroot -p**dequan

然後我們再來查看我們t1表的資料

總於大功告成了，但這也提示我們，為了恢復資料的方便性，不僅僅需要開啟二進位日誌，也要定時儲存資料。

關於二進位日誌的補充：

#1.我們也可以使用show binary logs指令查看當前還有哪些二進位日誌。

2.我們可以透過show binlog events日誌記錄的事件

show binlog # events查看的是所有日誌記錄的事件。如果想要查詢某個二進位日誌記錄事件，可以在後面加in+'日誌名稱'，如下圖：

#查詢日誌

1.功能描述

查詢日誌記錄了客戶端得所有語句。可以透過log=[file_name]來指定其位置，和其他日誌一樣，如果沒有指定file_name值，日誌將會寫到datadir所在目錄，預設的檔案名稱師host_name.log,此日誌對系統效能的影響較大，一般不會開啟，在此，不細說。

慢查詢日誌

#1.功能描述

慢查詢日誌是記錄所有執行時間超過參數long_query_time(單位：秒)，的SQL語句日誌。為了獲得表鎖，而等待的時間，不算執行時間。我們可以透過log-slow-queries=[file_name]選項來啟動慢查詢日誌功能。和前面的日誌一樣，如果沒有指定file_name，那麼日誌目錄在datedir目錄下，且預設的名字為host_name-slow.log。