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> SHOW GLOBAL VARIABLES LIKE '%general_log%';

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

| Variable_name      | Value                                |

| general_log           | OFF                                  |

| general_log_file     | /data/mysql/localhost.log |

mysql> SHOW GLOBAL VARIABLES LIKE '%log_output%';

| Variable_name  | Value  |

| log_output       | FILE     |

四、慢查詢日志：

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> SHOW BINARY LOGS;

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

| Log_name                   | File_size    |

| mysqld-binlog.000001 |       143   |

| mysqld-binlog.000002 |       120   |

檢視目前正在使用的是哪一個二進制日志檔案：

mysql> SHOW MASTER STATUS;

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

| File                             | Position   | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |

| mysqld-binlog.000002 |      120   |                       |                            |                              |

##做個操作後再次檢視：

mysql> use jiaowu;

Database changed

mysql> INSERT INTO students (Name,Age) VALUES ('stu1',24);

Query OK, 1 row affected (0.07 sec)

| mysqld-binlog.000002 |      394   |                      |                             |                              |

##可以看到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)

##檢視删除情況：

| mysqld-binlog.000006 |       171   |

| mysqld-binlog.000007 |       171   |

| mysqld-binlog.000008 |       120   |

六、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