Mysql 日誌管理

標籤：

mysql日誌——記錄著mysql資料庫運行期間的操作和資訊

日誌作用
(1)當資料庫遭到意外損害，可以通過記錄檔來查詢出錯原因
(2)利用記錄檔進行資料恢複

日誌分類
(1)二進位日誌:以二進位檔案的形式記錄了資料庫中的操作，但是不記錄查詢語句
(2)錯誤記錄檔:記錄Mysql伺服器的啟動、關閉和運行錯誤等資訊
(3)慢查詢日誌:記錄執行時間超過指定時間的操作
(4)通用查詢日誌:記錄使用者的所有操作，包括啟動和關閉MySQL服務、更新語句、查詢語句等

日誌設定
(1)除了二進位日誌，其他都是文字檔
(2)記錄檔通常儲存在Mysql資料目錄下
(3)預設只啟動了錯誤記錄檔功能，其他需要手工啟動
(4)但開機記錄功能會降低Mysql的執行速度，因為一條操作寫進日誌中是要花時間的

 

二進位日誌
(1) 以二進位檔案的形式記錄了資料庫中的操作，但是不記錄查詢語句
(2) 也叫變更日誌(update log)，主要用於記錄資料庫的變化
(3) 啟動與設定二進位日誌:在Mysql的設定檔中,log-bin=DIR/filename
                                   DIR是存放二進位日誌的目錄；
                                   每啟動一次Mysql，該目錄下就會產生一個filename.00000x的檔案；
                                   目錄下還有一個filename.index的檔案，用於儲存所有二進位檔案清單；
                                   如果我們沒有設定DIR和filename，則預設在資料目錄下以hostname-bin.00000x命名

# cat /etc/my.cnf|egrep "log-bin"log-bin = /data/mysqldb/binlog/mysql-bin# ls /data/mysqldb/binlog/mysql-bin.000001 mysql-bin.000003 mysql-bin.000005 mysql-bin.000007 mysql-bin.indexmysql-bin.000002 mysql-bin.000004 mysql-bin.000006 mysql-bin.000008　　

(4)臨時停止與啟動二進位日誌

-> set sql_log_bin=0; 停止-> set sql_log_bin=1; 啟動

(5)注意：二進位記錄檔與資料庫資料檔案最好不要放在同一塊硬碟上，如果存放資料檔案的硬碟壞了，可以用另一塊硬碟的二進位日誌來恢複資料

(6)查看二進位日誌:

# mysqlbinlog mysql-bin.000001

(7)刪除二進位日誌：

//刪除所有->reset master;//刪除00004之前的所有->purge master logs to ‘mysql-bin.000004‘;//刪除指定日期時間之前的所有->purge master logs to ‘2016-08-10 15:00:00‘;

(8)使用二進位日誌還原資料庫

# mysqlbinlog mysql-bin.000001 | mysql -u root -p# mysqlbinlog mysql-bin.000002 | mysql -u root -p這條命令可以理解為:使用mysqlbinlog讀取二進位記錄檔然後使用mysql命令還原到資料庫中注意還原時必須是編號小的先還原

(9)使用二進位記錄檔的pos號|時間點恢複

//先執行3條操作-> create database 2test-> create database alvin-> drop database 2test//查看二進位日誌，觀察每一個命令的起始/結束pos號，和起始/結束時間# mysqlbinlog mysql-bin.000001# at 120#160813 15:13:53 server id 706 end_log_pos 217 CRC32 0x781f816e Query    thread_id=21    exec_time=0    error_code=0SET TIMESTAMP=1471072433/*!*/;SET @@session.pseudo_thread_id=21/*!*/;SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;SET @@session.sql_mode=1075838976/*!*/;SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;/*!\C utf8 *//*!*/;SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=33/*!*/;SET @@session.lc_time_names=0/*!*/;SET @@session.collation_database=DEFAULT/*!*/;create database 2test/*!*/;# at 217#160813 15:14:11 server id 706 end_log_pos 314 CRC32 0x0fde9820 Query    thread_id=21    exec_time=0    error_code=0SET TIMESTAMP=1471072451/*!*/;create database alvin/*!*/;//想回到什麼哪條命令時的狀態，可以指定起始/結束pos號恢複# mysqlbinlog --start-position="120" --stop-position="217" mysql-bin.000001 |mysql -u root -p
//日誌也會記錄下這條恢複語句，我們可以看到這條命令的時間和原來那條一模一樣# mysqlbinlog mysql-bin.000001# at 401#160813 15:13:53 server id 706 end_log_pos 498 CRC32 0x3a899ca5 Query    thread_id=21    exec_time=619    error_code=0SET TIMESTAMP=1471072433/*!*/;create database 2test/*!*/;

//同理也可以用時間點來恢複
# mysqlbinlog –start-datetime=”2016-08-13 15:13:53” –stop-datetime=”2016-08-13 15:14:11” mysql-bin.000001 | mysql –uroot –p 

 

錯誤記錄檔
(1)主要用於記錄Mysql服務的開啟、關閉和錯誤資訊,若服務啟動不成功第一件事應該就是去看這個錯誤記錄檔
(2)錯誤記錄檔是預設開啟的，且錯誤記錄檔無法被關閉
(3)設定錯誤記錄檔：在設定檔中，log-error=DIR/filename

# cat /etc/my.cnf |egrep "log-error"log-error = /data/mysqldb/log/mysql-error.log# ls /data/mysqldb/logmysql-error.log 

(4)刪除錯誤記錄檔

mysqladmin -u root -p flush-logs# lsmysql-error.log mysql-slow_query.log

 

通用查詢日誌
(1)用來記錄使用者的所有操作:啟動和關閉mysql、更新語句、查詢語句
(2)啟用和設定通用查詢日誌:設定檔中，log=DIR/filename
(3)刪除通用查詢日誌:

# mysqladmin -u root -p flush-logs
或# echo >access.log

 

慢查詢日誌
(1)記錄執行時間超過指定時間的執行語句
(2)啟動和設定慢查詢日誌:在設定檔中

log-slow-queries=DIR/filenamelong_query_time=n   //設定時間為n秒,預設為10s

(3)刪除慢查詢日誌:

# mysqladmin -u root -p flush-logs 

 

Mysql 日誌管理