mysql的日誌管理

標籤：

日誌操作是資料庫維護中最重要的手段之一,記錄檔會記錄MySQL伺服器的各種資訊,所以當MySQL伺服器遭到意外損壞時,不僅可以通過記錄檔來查看出錯的原因,而且還可以通過記錄檔進行資料恢複。

MYSQL的記錄檔分為二進位日誌,錯誤記錄檔,通用查詢日誌,慢查詢日誌。除了二進位檔案外,其他記錄檔都是文字檔。預設情況下,MySQL只會啟動錯誤記錄檔檔案,而其他記錄檔則需要手動啟動才可以被啟動。

二進位日誌:該記錄檔會以二進位形式記錄資料庫的各種操作,但是不記錄查詢語句。

錯誤記錄檔:該記錄檔會記錄MySQL伺服器啟動,關閉和運行時出錯等資訊。

通用查詢日誌:該日誌記錄MySQL伺服器的啟動和關閉資訊,用戶端的串連資訊,更新資料記錄SQL語句和查詢資料記錄SQL語句。

慢查詢日誌:記錄執行時間超過指定時間的各種操作,通過工具分析慢日誌可以定位MySQL伺服器效能瓶頸所在。

使用日誌有優點也有缺點。開機記錄後,雖然可以實現對MySQL伺服器進行維護,但是會降低MySQL軟體的執行速度。

1. 操作二進位日誌

二進位日誌(BinLog)詳細記錄了資料庫額變化情況,即SQL語句中的DDL和DML語句,但是不包括資料記錄查詢操作。通過二進位記錄檔,可以詳細瞭解MySQL資料庫中進行了哪些操作。

操作二進位檔案包括啟動二進位日誌,查看二進位日誌,停止二進位日誌和刪除二進位日誌。

1.1 啟動二進位日誌

預設情況下,二進位日誌是關閉的,如果想啟動二進位日誌,可以通過設定MySQL伺服器的設定檔my.ini來實現,具體內容如下:

[mysqld]log-bin[=dir\[filename]]

參數dir用來指定二進位檔案的儲存路徑,參數filename用來指定二進位檔案的檔案名稱,具體格式為filename.number，其中number的格式為000001,000002,000003等。

在具體啟動二進位日誌時,如果沒有設定參數dir和filename,二進位記錄檔將使用預設名字主機名稱-bin.number,儲存到預設目錄——資料庫資料檔案裡。

預設配置:

[mysqld]log-bin

配置存放路徑:

[mysqld]log-bin=d:\mysqllog\binlog

1.2 查看二進位日誌

文法如下：

mysqlbinlog filename.number

1.3 停止二進位檔案

可以直接到my.ini裡面刪除[mysqld]組裡的log-bin內容即可。也可以通過命令SET實現暫停二進位日誌,具體命令內容如下:

SET SQL_LOG_BIN=0SET SQL_LOG_BIN=1

SQL_LOG_BIN的值為0時表示暫停二進位日誌功能,SQL_LOG_BIN的值為1時表示重新開啟二進位日誌功能。需要注意的是只有擁有SUPER許可權的使用者,才可以執行SET語句。

1.4 刪除二進位檔案

如果要刪除二進位檔案，需要通過執行命令RESET MASTER,PURGE MASTERLOGS TO和PURGE MASTERLOGS BEFORE來實現。

文法如下:

RESET MASTER;

執行上述命令可以刪除所有二進位檔案。

PURGE MASTER LOGS TO filename.number

執行上述命令，可以刪除編號小於number的所有二進位記錄檔。

PURGE MASTER LOGS BEFORE ‘yyyy-mm-dd hh:MM:ss’

執行上述命令，可以刪除指定時間（yyyy-mm-dd hh:MM:ss）之前所建立的所有二進行記錄檔。

2. 操作錯誤記錄檔

2.1 啟動錯誤記錄檔

文法如下:

[mysqld]log-error[=dir\[filename]]

2.2 查看錯誤記錄檔

文字檔，可直接用文本工具查看

2.3 刪除錯誤記錄檔

mysqladmin –u root –p flush-logs

3. 操作通用查詢日誌

通用查詢日誌主要用來記錄使用者關於MySQL伺服器的所有操作,包含MySQL伺服器的啟動和關閉資訊,用戶端的串連資訊,更新資料記錄SQL語句和查詢資料記錄SQL語句。

3.1 啟動通用查詢日誌

[mysqld]log [=dir\[filename]]

以上方式需要重啟MySQL伺服器才可以設定生效,還有一種方式通過設定MySQL的環境變數進行動態控制通用查詢日誌的開啟與關閉。通過設定環境變數general_log進行通用查詢日誌的動態控制,而不需要重啟MySQL伺服器,操作如下:

set global general_log=on;show variables like ‘%general_log%‘ \G;

3.2 查看通用查詢日誌

文字檔，可直接用文本工具查看

3.3 停止通用查詢日誌

[mysqld]#log [=dir\[filename]]

上述需要重啟MySQL伺服器，動態設定停止通用查詢日誌的另外一種方式：

set global general_log=off;

3.4 刪除通用查詢日誌

mysqladmin –u root –p flush-logs

4. 操作慢查詢日誌

慢查詢日誌記錄執行時間超過指定時間的各種操作,通過工具分析慢日誌可以定位MySQL伺服器效能瓶頸所在。

4.1 開啟慢查詢日誌

[mysqld]log-slow-queries[=dir\[filename]]long_query_time=n

上述需要重啟MySQL伺服器，另外一種方式動態設定如下:

#通過設定環境變數slow_query_log進行慢查詢日誌的動態控制，on表示開啟set global  slow_query_log=on;#設定慢查詢日誌最大允許的時間，單位為秒set global long_query_time=3;

4.2 查看慢查詢日誌

文字檔，可直接用文本工具查看

4.3 分析慢查詢日誌

對應的工具為mysqldumpslow.pl，常用參數如下：

“-s”為分析慢查詢日誌時指定排序參數，可選的有：“al”表示平均鎖定時間，

“ar”表示平均返回記錄數，“at“表示平均查詢時間。“-t“參數表示只顯示指定的行數。

#分析慢查詢日誌

C:\Program Files\MySQL\MySQL Server 5.5\bin>mysqldumpslow.pl  -s at -t 1 "C:\Documents and Settings\All Users\Application Data\MySQL\MySQL Server 5.5\data\MHX0JCJYUC7PDAA-slow.log”

“-s at”表示將分析的結果按平均查詢時間排序，“-t 1”表示只顯示合格第1條。

注意：在分析慢查詢日誌時，mysqldumpslow.pl為一perl語言編寫的指令碼，執行該指令碼需要對應的perl語言環境，perl環境的安裝包可以在http://www.perl.org/下載。

4.4 停止慢查詢日誌

[mysqld]#log-slow-queries[=dir\[filename]]#long_query_time=n

注釋掉即可停止，但上述需要重啟MySQL伺服器，另外一種方式動態設定如下:

#通過設定環境變數slow_query_log進行慢查詢日誌的動態控制，off表示關閉set global  slow_query_log=off;

4.5 刪除慢查詢日誌

通過Windows的刪除命令直接將滿查詢日誌刪除,然後使用以下命令重新建立對應檔案,文法如下:

刪除慢查詢記錄檔:

C:\Program Files\MySQL\MySQL Server 5.5\bin>del  C:\Documents and Settings\All Users\Application Data\MySQL\MySQL Server 5.5\data\MHX0JCJYUC7PDAA-slow.log

重新重新整理慢查詢日誌:

mysqladmin –u root –p flush-logs

執行上述命令,MySQL伺服器會建立一個新的慢查詢日誌,此時慢查詢日誌為空白。

 

mysql的日誌管理