MySQL日誌管理

一、日誌型別：

MySQL有幾個不同的日誌檔案，可以幫助你找出mysqld內部發生的事情：
日誌檔案記入檔案中的資訊型別
錯誤日誌記錄啟動、執行或停止時出現的問題。
查詢日誌記錄建立的客戶端連線和執行的語句。
二進位制日誌記錄所有更改資料的語句。主要用於複製和即時點恢復。
慢日誌記錄所有執行時間超過long_query_time秒的所有查詢或不使用索引的查詢。
事務日誌記錄InnoDB等支援事務的儲存引擎執行事務時產生的日誌。
預設情況下，所有日誌建立於mysqld資料目錄中。透過重新整理日誌，你可以強制 mysqld來關閉和重新開啟日誌檔案（或者在某些情況下切換到一個新的日誌）。當你執行一個FLUSH LOGS語句或執行mysqladmin flush-logs或mysqladmin refresh時，出現日誌重新整理。如果你正使用MySQL複製功能，從複製伺服器將維護更多日誌檔案，被稱為接替日誌。

二、錯誤日誌：

錯誤日誌主要記錄如下幾種日誌：
? 伺服器啟動和關閉過程中的資訊
? 伺服器執行過程中的錯誤資訊
? 事件排程器執行一個時間是產生的資訊
? 在從伺服器上啟動從伺服器程式是產生的資訊

錯誤日誌定義：

可以用--log-error[=file_name]選項來指定mysqld儲存錯誤日誌檔案的位置。如果沒有給定file_name值，mysqld使用錯誤日誌名host_name.err 並在資料目錄中寫入日誌檔案。如果你執行FLUSH LOGS，錯誤日誌用-old重新命名字尾並且mysqld建立一個新的空日誌檔案。(如果未給出--log-error選項，則不會重新命名）。
檢視當前錯誤日誌配置：

mysql> SHOW GLOBAL VARIABLES LIKE '%log_error%';
+---------------+---------------------------------------+
| Variable_name | Value                                                  |
+---------------+---------------------------------------+
| log_error          | /data/mysql/localhost.localdomain.err |
+---------------+---------------------------------------+
1 row in set (0.45 sec)
是否記錄警告日誌：
mysql> SHOW GLOBAL VARIABLES LIKE '%log_warnings%';
+---------------+-------+
| Variable_name | Value   |
+---------------+-------+
| log_warnings    | 1         |
+---------------+-------+
1 row in set (0.00 sec)
三、通用查詢日誌
?
    啟動開關：general_log={ON|OFF}
? 日誌檔案變數：general_log_file[=/PATH/TO/file]
? 全域性日誌開關：log={ON|OFF}   該開關開啟後，所有日誌都會被啟用
? 記錄型別：log_output={TABLE|FILE|NONE}

因此，要啟用通用查詢日誌，需要至少配置general_log=ON，log_output={TABLE|FILE}。而general_log_file如果沒有指定，預設名是host_name.log。
看看上述幾個值的預設配置：

四、慢查詢日誌：

MySQL如果啟用了slow_query_log=ON選項，就會記錄執行時間超過long_query_time的查詢（初使表鎖定的時間不算作執行時間）。日誌記錄檔案為slow_query_log_file[=file_name]，如果沒有給出file_name值，預設為主機名，字尾為-slow.log。如果給出了檔名，但不是絕對路徑名，檔案則寫入資料目錄。
預設與慢查詢相關變數：
mysql> SHOW GLOBAL VARIABLES LIKE '%slow_query_log%';
+----------------------------+--------------------------------+
| Variable_name                     | Value                                        |
+----------------------------+--------------------------------+
| slow_query_log                    | OFF                                          |
| slow_query_log_file              | /data/mysql/localhost-slow.log |
+----------------------------+--------------------------------+
伺服器引數設定方法與通用查詢日誌相同，不做解釋。
預設沒有啟用慢查詢，為了伺服器調優，建議開啟。
mysql> SET GLOBAL slow_query_log=ON;
Query OK, 0 rows affected (1.45 sec)
##如果要長久生效，則需要在配置檔案中定義。
那麼多久算是慢呢？
如果查詢時長超過long_query_time的定義值（預設10秒），即為慢查詢：
mysql> SHOW GLOBAL VARIABLES LIKE 'long_query_time';
+-----------------+-----------+
| Variable_name    | Value         |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+

五、二進位制日誌：

二進位制日誌啟動開關：log-bin [= file_name]
在5.6及以上版本一定要手動指定。5.6以下版本預設file_name為$datadir/mysqld-binlog
二進位制日誌用於記錄所有更改資料的語句。主要用於複製和即時點恢復。
檢視二進位制日誌的工具為：mysqlbinlog
二進位制日誌包含了所有更新了資料或者已經潛在更新了資料（例如，沒有匹配任何行的一個DELETE）的所有語句。語句以“事件”的形式儲存，它描述資料更改。二進位制日誌還包含關於每個更新資料庫的語句的執行時間資訊。它不包含沒有修改任何資料的語句。
二進位制日誌的主要目的是在資料庫存在故障時，恢復時能夠最大可能地更新資料庫（即時點恢復），因為二進位制日誌包含備份後進行的所有更新。二進位制日誌還用於在主複製伺服器上記錄所有將傳送給從伺服器的語句。
那麼二進位制日誌是記錄執行的語句還是執行後的結果資料呢？
第一種情況：
加入一個表有10萬行資料，而現在要執行一個如下語句將amount欄位的值全部在原來的基礎上增加1000：

UPDATE sales.january SET amount=amount+1000；

此時如果要記錄執行後的結果資料的話，日誌會非常大。
因此在這種情況下應記錄執行語句。這種方式就是基於語句的二進位制日誌。
第二種情況：
如果向某個欄位插入的是當前的時間呢？如下：
INSERT INTO tb SET Birthdate=CURRENT_TIME();
此時就不能記錄語句了，因為不同時間執行的結果是不一樣的。這是應該記錄這一行的值，這種就是基於行（row）的二進位制日誌。
在有些情況，可能會結合兩種方式來記錄，這種叫做混合方式的二進位制日誌。
二進位制日誌記錄時間：
預設情況下，並不是每次寫入時都將二進位制日誌與硬碟同步。因此如果作業系統或機器(不僅僅是MySQL伺服器)崩潰，有可能二進位制日誌中最後的語句丟失了。要想防止這種情況，你可以使用sync_binlog全域性變數(1是最安全的值，但也是最慢的)，使二進位制日誌在每N次二進位制日誌寫入後與硬碟同步。
對非事務表的更新執行完畢後立即儲存到二進位制日誌中。對於事務表，例如BDB或InnoDB表，所有更改表的更新(UPDATE、DELETE或INSERT) 被快取起來，直到伺服器接收到COMMIT語句。在該點，執行完COMMIT之前，mysqld將整個事務寫入二進位制日誌。當處理事務的執行緒啟動時，它為緩衝查詢分配binlog_cache_size大小的記憶體。如果語句大於該值，執行緒則開啟臨時檔案來儲存事務。執行緒結束後臨時檔案被刪除。
二進位制日誌的管理：
? 日誌的滾動：
在my.cnf中設定max_binlog_size = 200M，表示限制二進位制日誌最大尺寸為200M，超過200M後進行滾動。MySQL的滾動方式與其他日誌不太一樣，滾動時會建立一個新的編號大1的日誌用於記錄最新的日誌，而原日誌名字不會被改變。
每次重啟MySQL服務，日誌都會自動滾動一次。
另外如果需要手動滾動，則使用命令：

mysql> FLUSH LOGS;
?
日誌的檢視：
檢視有哪些二進位制日誌檔案：

mysql> INSERT INTO students (Name,Age) VALUES ('stu1',24);
Query OK, 1 row affected (0.07 sec)

##可以看到Position（位置）已經改變。
檢視二進位制日誌內容：

mysql> SHOW BINLOG EVENTS IN 'mysqld-binlog.000002';

+----------------------+-----+-------------+-----------+-------------+------------------------------------------------------------------+
| Log_name             | Pos | Event_type | Server_id | End_log_pos | Info                                                             |
+----------------------+-----+-------------+-----------+-------------+------------------------------------------------------------------+
| mysqld-binlog.000002 |   4 | Format_desc |         1 |         120 | Server ver: 5.6.13-log, Binlog ver: 4                            |
| mysqld-binlog.000002 | 120 | Query       |         1 |         203 | BEGIN                                                            |
| mysqld-binlog.000002 | 203 | Intvar      |         1 |         235 | INSERT_ID=11                                                     |
| mysqld-binlog.000002 | 235 | Query       |         1 |         363 | use `jiaowu`; INSERT INTO students (Name,Age) VALUES ('stu1',24) |
| mysqld-binlog.000002 | 363 | Xid         |         1 |         394 | COMMIT /* xid=13 */                                              |
+----------------------+-----+-------------+-----------+-------------+------------------------------------------------------------------+
5 rows in set (0.01 sec)

##該語句還可以加上Position（位置），指定顯示從哪個Position（位置）開始：

mysql> SHOW BINLOG EVENTS IN 'mysqld-binlog.000002' FROM 203;
+----------------------+-----+------------+-----------+-------------+------------------------------------------------------------------+
| Log_name             | Pos | Event_type | Server_id | End_log_pos | Info                                                             |
+----------------------+-----+------------+-----------+-------------+------------------------------------------------------------------+
| mysqld-binlog.000002 | 203 | Intvar     |         1 |         235 | INSERT_ID=11                                                     |
| mysqld-binlog.000002 | 235 | Query      |         1 |         363 | use `jiaowu`; INSERT INTO students (Name,Age) VALUES ('stu1',24) |
| mysqld-binlog.000002 | 363 | Xid        |         1 |         394 | COMMIT /* xid=13 */                                              |
+----------------------+-----+------------+-----------+-------------+------------------------------------------------------------------+
3 rows in set (0.00 sec)

使用命令mysqlbinlog檢視二進位制日誌內容：
基本語法：

mysqlbinlog [options] log-files
常用options（選項）：
--start-position ：開始位置
--stop-position ：結束位置
--start-datetime 'yyyy-mm-dd hh:mm:ss' ：開始時間
--stop-datetime 'yyyy-mm-dd hh:mm:ss' ：結束時間
示例：
[root@localhost mysql]# mysqlbinlog --start-datetime '2013-09-26 23:30:00' mysqld-binlog.000002
[root@localhost mysql]# mysqlbinlog --start-position 203 --stop-position 389 mysqld-binlog.000002
使用二進位制日誌還原資料：
使用mysqlbinlog讀取需要的日誌內容，使用標準輸入重定向到一個sql檔案，然後在mysql伺服器上匯入即可，如下：

[root@localhost mysql]# mysqlbinlog mysqld-binlog.000002 >/root/temp_date.sql

資料的還原後面部落格會詳細介紹。
刪除二進位制日誌檔案：
二進位制日誌檔案不能直接刪除的，如果使用rm等命令直接刪除日誌檔案，可能導致資料庫的崩潰。
必須使用命令PURGE刪除日誌，語法如下：
PURGE { BINARY | MASTER } LOGS
{ TO 'log_name' | BEFORE datetime_expr }
示例：
##檢視當前所有日誌檔案

[root@localhost mysql]# ll mysqld-binlog.0*
-rw-rw----. 1 mysql mysql 445 9月 22 15:13 mysqld-binlog.000002
-rw-rw----. 1 mysql mysql 171 9月 22 15:13 mysqld-binlog.000003
-rw-rw----. 1 mysql mysql 171 9月 22 15:13 mysqld-binlog.000004
-rw-rw----. 1 mysql mysql 171 9月 22 15:13 mysqld-binlog.000005
-rw-rw----. 1 mysql mysql 171 9月 22 15:13 mysqld-binlog.000006
-rw-rw----. 1 mysql mysql 171 9月 22 15:13 mysqld-binlog.000007
-rw-rw----. 1 mysql mysql 120 9月 22 15:13 mysqld-binlog.000008

##刪除mysqld-binlog.000006以前的日誌檔案：

mysql> PURGE BINARY LOGS TO 'mysqld-binlog.000006';
Query OK, 0 rows affected (0.02 sec)

##檢視刪除情況：

[root@localhost mysql]# ll mysqld-binlog.0*
-rw-rw----. 1 mysql mysql 171 9月 22 15:13 mysqld-binlog.000006
-rw-rw----. 1 mysql mysql 171 9月 22 15:13 mysqld-binlog.000007
-rw-rw----. 1 mysql mysql 120 9月 22 15:13 mysqld-binlog.000008

mysql> SHOW BINARY LOGS;

+----------------------+-----------+
| Log_name                   | File_size    |
+----------------------+-----------+
| mysqld-binlog.000006 |       171   |
| mysqld-binlog.000007 |       171   |
| mysqld-binlog.000008 |       120   |
+----------------------+-----------+
3 rows in set (0.00 sec)
六、MySQL中日誌相關常用的伺服器變數說明：

expire_logs_days={0..99}
設定二進位制日誌的過期天數，超出此天數的二進位制日誌檔案將被自動刪除。預設為0，表示不啟用過期自動刪除功能。如果啟用此功能，自動刪除工作通常發生在MySQL啟動時或FLUSH日誌時。作用範圍為全域性，可用於配置檔案，屬動態變數。
general_log={ON|OFF}
設定是否啟用查詢日誌，預設值為取決於在啟動mysqld時是否使用了--general_log選項。如若啟用此項，其輸出位置則由--log_output選項進行定義，如果log_output的值設定為NONE，即使用啟用查詢日誌，其也不會記錄任何日誌資訊。作用範圍為全域性，可用於配置檔案，屬動態變數。
general_log_file=FILE_NAME
查詢日誌的日誌檔名稱，預設為“hostname.log"。作用範圍為全域性，可用於配置檔案，屬動態變數。
binlog-format={ROW|STATEMENT|MIXED}
指定二進位制日誌的型別，預設為STATEMENT，建議更改為MIXED。如果設定了二進位制日誌的格式，卻沒有啟用二進位制日誌，則MySQL啟動時會產生警告日誌資訊並記錄於錯誤日誌中。作用範圍為全域性或會話，可用於配置檔案，且屬於動態變數。
log={YES|NO}
是否啟用記錄所有語句的日誌資訊於一般查詢日誌(general query log)中，預設通常為OFF。MySQL 5.6已經棄用此選項。
log-bin={YES|NO}
是否啟用二進位制日誌，如果為mysqld設定了--log-bin選項，則其值為ON，否則則為OFF。其僅用於顯示是否啟用了二進位制日誌，並不反應log-bin的設定值。作用範圍為全域性級別，屬非動態變數。
log_bin_trust_function_creators={TRUE|FALSE}
此引數僅在啟用二進位制日誌時有效，用於控制建立儲存函式時如果會導致不安全的事件記錄二進位制日誌條件下是否禁止建立儲存函式。預設值為0，表示除非使用者除了CREATE ROUTING或ALTER ROUTINE許可權外還有SUPER許可權，否則將禁止建立或修改儲存函式，同時，還要求在建立函式時必需為之使用DETERMINISTIC屬性，再不然就是附帶READS SQL DATA或NO SQL屬性。設定其值為1時則不啟用這些限制。作用範圍為全域性級別，可用於配置檔案，屬動態變數。
log_error=/PATH/TO/ERROR_LOG_FILENAME
定義錯誤日誌檔案。作用範圍為全域性或會話級別，可用於配置檔案，屬非動態變數。
log_output={TABLE|FILE|NONE}
定義一般查詢日誌和慢查詢日誌的儲存方式，可以是TABLE、FILE、NONE，也可以是TABLE及FILE的組合(用逗號隔開)，預設為TABLE。如果組合中出現了NONE，那麼其它設定都將失效，同時，無論是否啟用日誌功能，也不會記錄任何相關的日誌資訊。作用範圍為全域性級別，可用於配置檔案，屬動態變數。
log_query_not_using_indexes={ON|OFF}
設定是否將沒有使用索引的查詢操作記錄到慢查詢日誌。作用範圍為全域性級別，可用於配置檔案，屬動態變數。
log_slave_updates
用於設定複製場景中的從伺服器是否將從主伺服器收到的更新操作記錄進本機的二進位制日誌中。本引數設定的生效需要在從伺服器上啟用二進位制日誌功能。
log_slow_queries={YES|NO}
是否記錄慢查詢日誌。慢查詢是指查詢的執行時間超出long_query_time引數所設定時長的事件。MySQL 5.6將此引數修改為了slow_query_log。作用範圍為全域性級別，可用於配置檔案，屬動態變數。
log_warnings=#
設定是否將警告資訊記錄進錯誤日誌。預設設定為1，表示啟用；可以將其設定為0以禁用；而其值為大於1的數值時表示將新發起連線時產生的“失敗的連線”和“拒絕訪問”類的錯誤資訊也記錄進錯誤日誌。
long_query_time=#
設定區別慢查詢與一般查詢的語句執行時間長度。這裡的語句執行時長為實際的執行時間，而非在CPU上的執行時長，因此，負載較重的伺服器上更容易產生慢查詢。其最小值為0，預設值為10，單位是秒鐘。它也支援毫秒級的解析度。作用範圍為全域性或會話級別，可用於配置檔案，屬動態變數。
max_binlog_cache_size{4096 .. 18446744073709547520}
二進定日誌快取空間大小，5.5.9及以後的版本僅應用於事務快取，其上限由max_binlog_stmt_cache_size決定。作用範圍為全域性級別，可用於配置檔案，屬動態變數。
max_binlog_size={4096 .. 1073741824}
設定二進位制日誌檔案上限，單位為位元組，最小值為4K，最大值為1G，預設為1G。某事務所產生的日誌資訊只能寫入一個二進位制日誌檔案，因此，實際上的二進位制日誌檔案可能大於這個指定的上限。作用範圍為全域性級別，可用於配置檔案，屬動態變數。
max_relay_log_size={4096..1073741824}
設定從伺服器上中繼日誌的體積上限，到達此限度時其會自動進行中繼日誌滾動。此引數值為0時，mysqld將使用max_binlog_size引數同時為二進位制日誌和中繼日誌設定日誌檔案體積上限。作用範圍為全域性級別，可用於配置檔案，屬動態變數。
innodb_log_buffer_size={262144 .. 4294967295}
設定InnoDB用於輔助完成日誌檔案寫操作的日誌緩衝區大小，單位是位元組，預設為8MB。較大的事務可以藉助於更大的日誌緩衝區來避免在事務完成之前將日誌緩衝區的資料寫入日誌檔案，以減少I/O操作進而提升系統效能。因此，在有著較大事務的應用場景中，建議為此變數設定一個更大的值。作用範圍為全域性級別，可用於選項檔案，屬非動態變數。
innodb_log_file_size={108576 .. 4294967295}
設定日誌組中每個日誌檔案的大小，單位是位元組，預設值是5MB。較為明智的取值範圍是從1MB到快取池體積的1/n，其中n表示日誌組中日誌檔案的個數。日誌檔案越大，在快取池中需要執行的檢查點刷寫操作就越少，這意味著所需的I/O操作也就越少，然而這也會導致較慢的故障恢復速度。作用範圍為全域性級別，可用於選項檔案，屬非動態變數。
innodb_log_files_in_group={2 .. 100}
設定日誌組中日誌檔案的個數。InnoDB以迴圈的方式使用這些日誌檔案。預設值為2。作用範圍為全域性級別，可用於選項檔案，屬非動態變數。
innodb_log_group_home_dir=/PATH/TO/DIR
設定InnoDB重做日誌檔案的儲存目錄。在預設使用InnoDB日誌相關的所有變數時，其預設會在資料目錄中建立兩個大小為5MB的名為ib_logfile0和ib_logfile1的日誌檔案。作用範圍為全域性級別，可用於選項檔案，屬非動態變數。
innodb_support_xa={TRUE|FLASE}
儲存引擎事務在儲存引擎內部被賦予了ACID屬性，分散式(XA)事務是一種高層次的事務，它利用“準備”然後“提交”(prepare-then-commit)兩段式的方式將ACID屬性擴充套件到儲存引擎外部，甚至是資料庫外部。然而，“準備”階段會導致額外的磁碟刷寫操作。XA需要事務協調員，它會通知所有的參與者準備提交事務(階段1)。當協調員從所有參與者那裡收到“就緒”資訊時，它會指示所有參與者進行真正的“提交”操作。
此變數正是用於定義InnoDB是否支援兩段式提交的分散式事務，預設為啟用。事實上，所有啟用了二進位制日誌的並支援多個執行緒同時向二進位制日誌寫入資料的MySQL伺服器都需要啟用分散式事務，否則，多個執行緒對二進位制日誌的寫入操作可能會以與原始次序不同的方式完成，這將會在基於二進位制日誌的恢復操作中或者是從伺服器上建立出不同原始資料的結果。因此，除了僅有一個執行緒可以改變資料以外的其它應用場景都不應該禁用此功能。而在僅有一個執行緒可以修改資料的應用中，禁用此功能是安全的並可以提升InnoDB表的效能。作用範圍為全域性和會話級別，可用於選項檔案，屬動態變數。
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，表示不限定。最大值取決於系統平臺位數。作用範圍為全域性級別，可用於選項檔案，屬非動態變數。
slow_query_log={ON|OFF}
設定是否啟用慢查詢日誌。0或OFF表示禁用，1或ON表示啟用。日誌資訊的輸出位置取決於log_output變數的定義，如果其值為NONE，則即便slow_query_log為ON，也不會記錄任何慢查詢資訊。作用範圍為全域性級別，可用於選項檔案，屬動態變數。
slow_query_log_file=/PATH/TO/SOMEFILE
設定慢查詢日誌檔案的名稱。預設為hostname-slow.log，但可以透過--slow_query_log_file選項修改。作用範圍為全域性級別，可用於選項檔案，屬動態變數。
sql_log_bin={ON|OFF}
用於控制二進位制日誌資訊是否記錄進日誌檔案。預設為ON，表示啟用記錄功能。使用者可以在會話級別修改此變數的值，但其必須具有SUPER許可權。作用範圍為全域性和會話級別，屬動態變數。
sql_log_off={ON|OFF}
用於控制是否禁止將一般查詢日誌類資訊記錄進查詢日誌檔案。預設為OFF，表示不禁止記錄功能。使用者可以在會話級別修改此變數的值，但其必須具有SUPER許可權。作用範圍為全域性和會話級別，屬動態變數。
sync_binlog=#
設定多久同步一次二進位制日誌至磁碟檔案中，0表示不同步，任何正數值都表示對二進位制每多少次寫操作之後同步一次。當autocommit的值為1時，每條語句的執行都會引起二進位制日誌同步，否則，每個事務的提交會引起二進位制日誌同步。建議設定為1。

資料整理網路

mysql日誌管理

相關文章