mysql的各種日誌

mysql錯誤日誌：

錯誤日誌記錄的事件：

a)、伺服器啟動關閉過程中的資訊

b)、伺服器執行過程中的錯誤資訊

c)、事件偵錯程式執行一個事件時間生的資訊

d)、在從伺服器上啟動從伺服器程式時產生的資訊

log_error引數指定錯誤日誌存放路徑以及檔名。如果不指定檔案，預設檔名是[host_name].err，儲存路徑為%datadir%。

mysql> show variables like "%log_error%";

+------------+-------------------------------------------------------+

| Variable_name | Value                                        |

+------------+-------------------------------------------------------+

| log_error     | D:\Quarkp2p\mysql-5.6.19-winx64\data\QF-dbadmin-02.err   |

+------------+-------------------------------------------------------+

1 row in set (0.00 sec)

mysql> show variables like "%datadir%";

+---------------+---------------------------------------+

| Variable_name   | Value                         |

+---------------+---------------------------------------+

| datadir          | D:\Quarkp2p\mysql-5.6.19-winx64\data\    |

+---------------+---------------------------------------+

1 row in set (0.00 sec)

錯誤日誌中記錄的資訊分為三類：[Note]，[warning]，[Error]。可以透過設定log_warnings來控制警告資訊是否被記錄，預設值為1表示記錄，為0表示禁用。如果大於1，則對於連線失敗、新連線拒絕等型別的訊息也會被寫入到錯誤日誌中去。

mysql> show variables like "%log_warning%";

+---------------+-------+

| Variable_name   | Value   |

+---------------+-------+

| log_warnings    | 1       |

+---------------+-------+

1 row in set (0.00 sec)

 

mysql的查詢日誌：

查詢日誌記錄查詢語句與啟動時間，建議不是在除錯環境下不要開啟查詢日誌，因為會不斷佔據你的磁碟空間，並會產生大量的IO。

查詢日誌有兩種，分別是慢查詢日誌（Slow Query Log）和通用查詢日誌（General Query Log）。而且mysql的查詢日誌不僅可以記錄到檔案，還能自動儲存到MySql資料庫中的表物件裡。

1、慢查詢日誌：

指的是所有查詢語句執行時間超過系統變數long_query_time（預設值是10秒）指定的引數值，並且訪問的記錄數超過系統變數min_examined_row_limit（預設值是0條）的數量的語句。這裡的執行時間是不包括初始化表鎖的開銷。

慢查詢日誌中語句記錄和順序有可能跟執行順序不同，因為每條語句執行完並且釋放了鎖資源之後，mysqld才會將符合條件的sql語句寫入到慢查詢日誌中去。

開啟和禁用慢查詢日誌透過以下兩個系統引數，這兩個引數可以動態修改：

slow_query_log  指定是否輸出慢查詢日誌，1表示輸出，0表示不輸出，預設為0。

slow_query_log_file   指定日誌檔案儲存路徑和檔名，如果沒有指定，預設檔名為[host_name]-slow.log，儲存路徑為%datadir%。

mysql> show variables like "%slow_query_log%";

+------------------+---------------------------------------------------------+

| Variable_name      | Value                                                     |

+------------------+---------------------------------------------------------+

| slow_query_log     | OFF                                                       |

| slow_query_log_file  | D:\Quarkp2p\mysql-5.6.19-winx64\data\QF-dbadmin-02-slow.log |

+------------------+---------------------------------------------------------+

2 rows in set (0.00 sec)

全域性禁用/開啟慢查詢日誌:

SET GLOBAL slow_query_log='OFF';

SET GLOBAL slow_query_log='ON';

其他引數：

long_query_time

log_short_format

log_slow_admin_statements

log_queries_not_using_indexes

log_throttle_queries_not_using_indexes

log_slow_slave_statements

mysqld判斷一條sql語句是否需要被記錄到慢查詢日誌時做的判斷步驟如下：

1）判斷執行的是查詢語句，還是管理性語句

2）查詢語句執行的時間達到或者超過了long_query_time引數值，或者是符合log_queries_not_using_indexes條件

3）查詢的記錄量達到了min_examined_row_limit引數值

4）查詢語句不違反log_throttle_queries_not_using_indexes設定的值

如果慢查詢日誌非常大，可以用mysql自帶的mysqldumpslow命令，或者其他第三方工具檢視分析。

2、通用查詢日誌：

這個日誌可以記錄mysqld程式所做的幾乎所有操作，包括sql語句執行，資料庫物件管理，客戶端的連線和斷開。

這個日誌的最大功能是審計。

開啟和禁用通用查詢日誌透過以下兩個系統引數，這兩個引數可以動態修改：

general_log   指定是否輸出慢查詢日誌，1表示輸出，0表示不輸出，預設為0。

general_log_file   指定日誌檔案儲存路徑和檔名，如果沒有指定，預設檔名為[host_name].log，儲存路徑為%datadir%。

mysql> show variables like "%general_log%";

+------------------+--------------------------------------------------------+

| Variable_name      | Value                                                  |

+------------------+--------------------------------------------------------+

| general_log         | OFF                                                    |

| general_log_file     | D:\Quarkp2p\mysql-5.6.19-winx64\data\QF-dbadmin-02.log   |

+------------------+--------------------------------------------------------+

2 rows in set (0.00 sec)

全域性禁用/開啟通用日誌:

SET GLOBAL general_log='OFF';

SET GLOBAL general_log='ON';

會話級禁用/開啟通用日誌:

SET sql_log_off='OFF';

SET sql_log_off='ON';

通用查詢日誌檔案中語句出現的順序，是按照mysqld接收的順序。

3、配置查詢日誌：

在mysql服務啟動時指定--log-output選項，可以決定查詢日誌是儲存在作業系統中的檔案裡，還是儲存在資料庫系統中的專用表。

--log-output  可選值有三個：table，輸出到表，對應的表有general_log和slow_log

                            file，輸出到日誌檔案

                            none，不輸出查詢日誌

--log-output可以設定多個值。

預設情況下，日誌表使用CSV引擎，5.1以後，日誌表也可以修改為MyIsam引擎。

日誌表（general_log和slow_log）支援create table，alter table，drop table，truncate table，rename，check。不支援lock tables，insert，update，delete操作，日誌表的增刪改查均由mysql服務內部自己進行。            

flush tables with read lock以及設定全域性系統變數read_only，對日誌表無效。

日誌表的寫操作不會計入二進位制日誌，有複製環境的話，日誌表的內容也不會被複制到其他Slaves節點。

用flush tables或flush logs來重新整理日誌表或者日誌檔案。

日誌表不允許建立分割槽。

 

二進位制日誌檔案：

透過二進位制日誌檔案，可以實現兩個重要的功能：用於複製，用於恢復。

啟用二進位制日誌檔案，在mysql服務啟動時新增引數--log-bin=[base_name]。如果不指定引數值，預設檔名為[host_name]-bin.log，儲存路徑為%datadir%。

mysql> show variables like "%log_bin%";

+--------------------------+-------------------------------------------------+

| Variable_name              | Value                                             |

+--------------------------+-------------------------------------------------+

| log_bin                    | ON                                               |

| log_bin_basename           | D:\Quarkp2p\mysql-5.6.19-winx64\data\mysql-bin      |

| log_bin_index               | D:\Quarkp2p\mysql-5.6.19-winx64\data\mysql-bin.index |

|log_bin_trust_function_creators |ON                         

| log_bin_use_v1_row_events   | OFF                                               |

| sql_log_bin                 | ON                                               |

+--------------------------+-------------------------------------------------+

6 rows in set (0.00 sec) 

二進位制日誌檔案不會只有一個，從序號1開始起，每次啟動mysql服務或者重新整理日誌時，生成一個新的二進位制日誌檔案。而且單個日誌檔案不可能無限增長，當漲到引數max_binlog_size指定的大小時，就會建立新的二進位制日誌檔案。但是也有可能日誌檔案比max_binlog_size指定的值要大，假如有執行的事務很大，所有的事務資訊必須寫到一個日誌檔案中去。

為了跟蹤二進位制日誌檔案的狀態，mysql服務建立了一個和二進位制日誌檔案同名，但是副檔名為.index的二進位制日誌索引檔案。這個檔案中包含所有可供使用的二進位制日誌檔案。

如果擁有super許可權的使用者執行操作前，執行了set sql_log_bin=0命令，則會禁止其執行的語句生產二進位制日誌。

--binlog-do-db和--binlog-ignore-db兩個選項，表示指定那些庫記錄或者不記錄二進位制日誌，這兩個選項每次只能設定一個值，如果有多個庫，反覆設定多條。

二進位制日誌檔案的格式有三種：基於行格式記錄（row-based logging）、基於語句記錄（statement-based logging）、混合模式記錄（mixed-based logging）。

因為mysql中既有支援事務的儲存引擎，也有不支援事務的儲存引擎，因此在操作基於不同的儲存引擎物件時，二進位制日誌的處理也不一樣。

對於非事務表來說，語句執行後就會立即寫入二進位制日誌檔案中。而對於事務表，則要到等到當前沒有任何鎖定或未提交的資訊時才會寫入二進位制日誌檔案中，以確保日誌被記錄的始終是其執行的順序。

使用mysqlbinlog命令列工具可以檢視二進位制日誌檔案。

binlog_cache_use 顯示了使用binlog_cache_size變數的事務數，包括臨時檔案

binlog_cache_size 指定為執行緒分配多少記憶體空間

binlog_cache_disk_use  顯示了使用臨時檔案的事務數

max_binlog_cache_size  用來限制事務能夠使用的最大快取區（預設為4G，也是最大值，最小值是4096）。如果事務超出這個限制，則執行出錯，事務回滾。

 

事務日誌：

事務日誌檔名為"ib_logfile0"和“ib_logfile1”，預設存放在表空間所在目錄

與事務日誌相關變數：

innodb_log_group_home_dir=/PATH/TO/DIR    #設定InnoDB重做日誌檔案的儲存目錄。在預設使用InnoDB日誌相關的所有變數時，其預設會在資料目錄中建立兩個大小為5MB的名為ib_logfile0和ib_logfile1的日誌檔案。作用範圍為全域性級別，可用於選項檔案，屬非動態變數。

innodb_log_file_size={108576 .. 4294967295}   #設定日誌組中每個日誌檔案的大小，單位是位元組，預設值是5MB。較為明智的取值範圍是從1MB到快取池體積的1/n，其中n表示日誌組中日誌檔案的個數。日誌檔案越大，在快取池中需要執行的檢查點刷寫操作就越少，這意味著所需的I/O操作也就越少，然而這也會導致較慢的故障恢復速度。作用範圍為全域性級別，可用於選項檔案，屬非動態變數。

innodb_log_files_in_group={2 .. 100}   #設定日誌組中日誌檔案的個數。InnoDB以迴圈的方式使用這些日誌檔案。預設值為2。作用範圍為全域性級別，可用於選項檔案，屬非動態變數。

innodb_log_buffer_size={262144 .. 4294967295}   設定InnoDB用於輔助完成日誌檔案寫操作的日誌緩衝區大小，單位是位元組，預設為8MB。較大的事務可以藉助於更大的日誌緩衝區來避免在事務完成之前將日誌緩衝區的資料寫入日誌檔案，以減少I/O操作進而提升系統效能。因此，在有著較大事務的應用場景中，建議為此變數設定一個更大的值。作用範圍為全域性級別，可用於選項檔案，屬非動態變數。

innodb_flush_log_at_trx_commit = 1  #表示有事務提交後，不會讓事務先寫進buffer，再同步到事務日誌檔案，而是一旦有事務提交就立刻寫進事務日誌，並且還每隔1秒鐘也會把buffer裡的資料同步到檔案，這樣IO消耗大，預設值是"1"，可修改為“2”

innodb_locks_unsafe_for_binlog = OFF   #這個變數建議保持OFF狀態，詳細的原理不清楚

innodb_mirrored_log_groups = 1    #事務日誌組儲存的映象數

 

中繼日誌：

在複製環境中產的的日誌資訊

與中繼日誌相關的變數：

log_slave_updates   #用於設定複製場景中的從伺服器是否將從主伺服器收到的更新操作記錄進本機的二進位制日誌中。本引數設定的生效需要在從伺服器上啟用二進位制日誌功能。

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，表示不限定。最大值取決於系統平臺位數。作用範圍為全域性級別，可用於選項檔案，屬非動態變數。

max_relay_log_size={4096..1073741824}    #設定從伺服器上中繼日誌的體積上限，到達此限度時其會自動進行中繼日誌滾動。此引數值為0時，mysqld將使用max_binlog_size引數同時為二進位制日誌和中繼日誌設定日誌檔案體積上限。作用範圍為全域性級別，可用於配置檔案，屬動態變數。