MySQL 日誌管理詳解

大綱
一、日誌分類
二、日誌詳解

註：MySQL版本，Mysql-5.5.32(不同版本的mysql變數有所不同)

一、日誌分類
錯誤記錄檔
查詢日誌
慢查詢日誌
二進位日誌
中繼日誌
交易記錄
滾動日誌

二、日誌詳解
1.錯誤記錄檔
說明：在對應的資料目錄中，以主機名稱+.err命名的檔案，錯誤記錄檔記錄的資訊類型：
記錄了伺服器運行中產生的錯誤資訊

記錄了服務在啟動和停止是所產生的資訊

在從伺服器上如果啟動了複製進程的時候，複製進程的資訊也會被記錄

記錄event錯誤記錄檔

配置mysql的主設定檔：
log_error = /mydata/data/mysql.test.com.err #指定錯誤記錄檔的位置，預設是在資料目錄下，這個位置mysql使用者必須有寫入權限
log_warning = {0|1} #預設開啟，伺服器運行中的警告日誌也會記錄在錯誤記錄檔中

2.查詢日誌
說明：對除了慢查日誌中記錄的查詢資訊都將記錄下來,這將對伺服器主機產生大量的壓力，所以對於繁忙的伺服器應該關閉這個日誌
與查詢日誌相關的變數，
1234 log = {ON|OFF} #是否啟用查詢日誌，該指令在mysq5.6中已廢棄
general_log = {ON|OFF} #啟動或關閉查詢日誌，預設是關閉的
general_log_file = /mydata/data/mysql.log #指定查詢日誌的位置，預設在資料目錄下
log_output = {TABLE|FILE|NONE} #指定存放查詢日誌的位置，可以放在檔案中，也可以放在資料庫的表中，放在表中比放在檔案中更容易查看

3.慢查詢日誌
說明：預設為關閉狀態，記錄下來查詢時間超過設定時間長度的查詢，這些查詢日誌將被慢查日誌記錄下來
配置mysql的主設定檔：
slow_query_log  = {ON | OFF} #是否開啟慢慢查詢日誌，預設是關閉的 
slow_query_log_file = /mydata/data/mysql-slow.log #慢查詢日誌的存放位置，預設在資料目錄下
log_query_time = 10 #定義預設的時間長度，預設時間長度為10秒
log_query_not_using_indexes = {ON|OFF} #設定是否將沒有使用索引的查詢操作記錄到慢查詢日誌 
log_output = {TABLE|FILE|NONE} #定義一般查詢日誌和慢查詢日誌的儲存方式，可以是TABLE、FILE、NONE，也可以是TABLE及FILE的組合(用逗號隔開)，預設為FILE。如果組合中出現了NONE，那麼其它設定都將失效，同時，無論是否啟用日誌功能，也不會記錄任何相關的日誌資訊

4.二進位日誌
說明：預設開啟，精確的記錄了使用者對資料庫中的資料進行操作的命令和操作的資料對象。 
二進位記錄檔的作用：
提供了增量備份的功能

提供了資料基於時間點的恢複，這個恢複的時間點可以由使用者控制

為mysql的複製架構提供基礎，將這主伺服器的二進位日誌複製到從伺服器上並執行同樣的操作，就可將資料進行同步

二進位日誌格式：
基於語句 statement

基於行 row

混合方式 mixed

二進位日誌事件：
position 基於位置

datetime 基於時間

二進位日誌的查看與刪除方式：
mysql>show master status; 查看當前正在使用的二進位日誌 
mysql>show binlog events in 'mysql-bin.000001'; 查看二進位日誌記錄的事件[from position]   
mysql>flush logs; 二進位日誌滾動   
mysql>show binary logs; 查看所有二進位日誌   
mysql>purge binary logs to 'mysql-bin.000003'; 刪除二進位日誌

檔案系統中查看二進位日誌的命令：
mysqlbinlog
相關選項， 
--start-position #開始位置   
--stop-position #結束位置   
--start-datetime 'yyyy-mm-dd hh:mm:ss'; #開始時間   
--stop-datetime ''; #結束時間

配置mysql的主設定檔： 
123456789 sql_log_bin = {ON|OFF} #用於控制二進位日誌資訊是否記錄進記錄檔。預設為ON，表示啟用記錄功能。使用者可以在會話層級修改此變數的值，但其必須具有SUPER許可權
binlog_cache_size = 32768 #預設值32768 Binlog Cache 用於在開啟了二進位日誌（binlog）記錄功能的環境，是 MySQL 用來提高binlog的記錄效率而設計的一個用於短時間內臨時緩衝binlog資料的記憶體地區。一般來說，如果我們的資料庫中沒有什麼大事務，寫入也不是特別頻繁，2MB～4MB是一個合適的選擇。但是如果我們的資料庫大事務較多，寫入量比較大，可與適當調高binlog_cache_size。同時，我們可以通過binlog_cache_use 以及 binlog_cache_disk_use來分析設定的binlog_cache_size是否足夠，是否有大量的binlog_cache由於記憶體大小不夠而使用臨時檔案（binlog_cache_disk_use）來緩衝了 
binlog_stmt_cache_size = 32768 #當非事務語句使用二進位日誌緩衝，但是超出binlog_stmt_cache_size時，使用一個臨時檔案來存放這些語句   
log_bin = mysql-bin #指定binlog的位置，預設在資料目錄下
binlog-format = {ROW|STATEMENT|MIXED} #指定二進位日誌的類型，預設為MIXED。如果設定了二進位日誌的格式，卻沒有啟用二進位日誌，則MySQL啟動時會產生警告日誌資訊並記錄於錯誤記錄檔中。 
sync_binlog = 10 #設定多久同步一次二進位日誌至磁碟檔案中，0表示不同步，任何正數值都表示對二進位每多少次寫操作之後同步一次。當autocommit的值為1時，每條語句的執行都會引起二進位日誌同步，否則，每個事務的提交會引起二進位日誌同步   
max_binlog_cache_size = {4096 .. 18446744073709547520} #二進定日誌緩衝空間大小，5.5.9及以後的版本僅應用於事務緩衝，其上限由max_binlog_stmt_cache_size決定。   
max_binlog_stmt_cache_size = {4096 .. 18446744073709547520} #二進定日誌緩衝空間大小，5.5.9及以後的版本僅應用於事務緩衝   
expire_log_days = {0..99} #設定二進位日誌的到期天數，超出此天數的二進位記錄檔將被自動刪除。預設為0，表示不啟用到期自動刪除功能。如果啟用此功能，自動刪除工作通常發生在MySQL啟動時或FLUSH日誌時

註：一般建議將binlog日誌與資料檔案分開存放，不但可以提高mysql效能，還可以增加安全性！
5.中繼日誌
說明：主要是在mysql伺服器的中從架構中的從伺服器上用到的，當從伺服器想要和主伺服器進行資料的同步時，從伺服器將主伺服器的二進位記錄檔拷貝到己的主機上放在中繼日誌中，然後調用SQL線程按照拷中繼記錄檔中的二進位記錄檔執行以便就可達到資料的同步
開啟的方法：（只在從伺服器上開啟） 
配置mysql的主設定檔：   

relay-log = file_name #指定中繼日誌的位置和名字，預設為host_name-relay-bin。也可以使用絕對路徑，以指定非資料目錄來儲存中繼日誌   
relay-log-index = file_name #指定中繼日誌的名字的索引檔案的位置和名字，預設為資料目錄中的host_name-relay-bin.index
relay-log-info-file = file_name #設定中繼服務用於記錄中繼資訊的檔案，預設為資料目錄中的relay-log.info
relay_log_purge = {ON|OFF} #設定對不再需要的中繼日誌是否自動進行清理。預設值為ON
relay_log_space_limit = 0 #設定用於儲存所有中繼記錄檔的可用空間大小。預設為0，表示不限定。最大值取決於系統平台位元
max_relay_log_size = {4096..1073741824} #設定從伺服器上中繼日誌的體積上限，到達此限度時其會自動進行中繼日誌滾動。此參數值為0時，mysqld將使用max_binlog_size參數同時為二進位日誌和中繼日誌設定記錄檔體積上限

6.交易記錄

說明：詳細的記錄了在什麼時間發生了什麼時候，在哪個時間對哪些資料進行了改變，能後實現事件的重放，一般只記錄對資料進行改變的操作，對於讀操作一般不進行記錄。
事物日誌為資料庫伺服器實現以下功能： 
(1).將隨機IO轉換為順序IO，大大的提高了資料庫的效能，儲存的資料可能存在在磁碟的不同位置，降低了資料的讀取和操作效能。轉換為順序IO的原理為，先將資料存放在記錄檔中，然後由RDBSM的後台將日誌中的資料存放到磁碟上，這樣就保證了儲存的資料是連續的。   
(2).為事件重放提供基礎，交易記錄詳細的記錄了時間發生的時間以及操作的資料對象，事務進程可以根據這些資訊進行時間重放。   
預設的交易記錄檔有兩個，位於資料目錄下以ibdata+number結尾的數字，我們可以對交易記錄的位置、檔案大小、增長方式進行定義，定義的方法如下：   
這裡以使用支援事務的Innodb儲存引擎為例，
配置mysql的主設定檔：
innodb_data_home_dir = /mydata/data  #InnoDB所有共用資料表空間資料檔案的目錄路徑，預設在資料目錄下
innodb_data_file_path = ibdata1:1024M  #指定InnoDB的各個資料檔案及其大小，檔案多於一個時彼此間用分號隔開 
innodb_data_file_path = ibdata2:50M:autoextend  #定義資料大小的增長方式
innodb_log_group_home_dir = /mydata/data #設定InnoDB重要記錄檔的儲存目錄。在預設使用InnoDB日誌相關的所有變數時，其預設會在資料目錄中建立兩個大小為5MB的名為ib_logfile0和ib_logfile1的記錄檔
innodb_log_files_in_group = {2 .. 100} #設定日誌組中記錄檔的個數。InnoDB以迴圈的方式使用這些記錄檔。預設值為2
innodb_log_file_size = {108576 .. 4294967295} #設定日誌組中每個記錄檔的大小，單位是位元組，預設值是5MB。較為明智的取值範圍是從1MB到緩衝池體積的1/n，其中n表示日誌組中記錄檔的個數。記錄檔越大，在緩衝池中需要執行的檢查點刷寫操作就越少，這意味著所需的I/O操作也就越少，然而這也會導致較慢的故障恢複速度
innodb_log_buffer_size = {262144 .. 4294967295} #設定InnoDB用於輔助完成記錄檔寫操作的日誌緩衝區大小，單位是位元組，預設為8MB。較大的事務可以藉助於更大的日誌緩衝區來避免在事務完成之前將日誌緩衝區的資料寫入記錄檔，以減少I/O操作進而提升系統效能。因此，在有著較大事務的應用情境中，建議為此變數設定一個更大的值

7.滾動日誌
說明：只要是針對二進位日誌進行滾動的，對某個類型的記錄檔滾動一次就產生一個新的相對應的記錄檔，通過這種方法保證記錄檔的特定大小，從而保證伺服器在對記錄檔查詢��有較高的響應能力。
滾動二進位日誌的命令：
1 mysql> FLUSH LOGS;

三、總結
通過上面的學習，大家應該對mysql日誌管理有所瞭解，^_^……！

推薦閱讀：

MySQL InnoDB 管理和備份二進位日誌