日志文件對於一個服務器來說是非常重要的，它記錄著服務器的運行信息，許多操作都會寫日到日志文件，通過日志文件可以監視服務器的運行狀態及查看服務器的性能，還能對服務器進行排錯與故障處理，MySQl中有六種不同類型的日志。
一、日志種類
———–> 1，錯誤日志：記錄啟動、運行或停止時出現的問題，一般也會記錄警告信息。
———–> 2，一般查詢日志：記錄建立的客戶端連接和執行的語句。
———–> 3，慢查詢日志：記錄所有執行時間超過long_query_time秒的所有查詢或不使用索引的查詢，可以幫我們定位服務器性能問題。
———–> 4，二進制日志：任何引起或可能引起數據庫變化的操作，主要用於復制和即時點恢復。
———–> 5，中繼日志：從主服務器的二進制日志文件中復制而來的事件，並保存為的日志文件。
———–> 6，事務日志：記錄InnoDB等支持事務的存儲引擎執行事務時產生的日志。
MySQL中對於日志文件的環境比變量非常多，可以使用以下命令來查看：
mysql> show global variables like '%log%'; +-----------------------------------------+-----------------------------------------+ | Variable_name | Value | +-----------------------------------------+-----------------------------------------+ | back_log | 50 | | binlog_cache_size | 32768 | | binlog_direct_non_transactional_updates | OFF | | binlog_format | MIXED | | binlog_stmt_cache_size | 32768 | | expire_logs_days | 0 | | general_log | OFF | | general_log_file | /mydata/data1/localhost.log | | innodb_flush_log_at_trx_commit | 1 | | innodb_locks_unsafe_for_binlog | OFF | | innodb_log_buffer_size | 8388608 | | innodb_log_file_size | 5242880 | | innodb_log_files_in_group | 2 | | innodb_log_group_home_dir | ./ | | innodb_mirrored_log_groups | 1 | | log | OFF | | log_bin | ON | | log_bin_trust_function_creators | OFF | | log_error | /mydata/data1/localhost.localdomain.err | | log_output | FILE | | log_queries_not_using_indexes | OFF | | log_slave_updates | OFF | | log_slow_queries | OFF | | log_warnings | 1 | | max_binlog_cache_size | 18446744073709547520 | | max_binlog_size | 1073741824 | | max_binlog_stmt_cache_size | 18446744073709547520 | | max_relay_log_size | 0 | | relay_log | | | relay_log_index | | | relay_log_info_file | relay-log.info | | relay_log_purge | ON | | relay_log_recovery | OFF | | relay_log_space_limit | 0 | | slow_query_log | OFF | | slow_query_log_file | /mydata/data1/localhost-slow.log | | sql_log_bin | ON | | sql_log_off | OFF | | sync_binlog | 0 | | sync_relay_log | 0 | | sync_relay_log_info | 0 | +-----------------------------------------+-----------------------------------------+ 41 rows in set (0.00 sec)
二、日志功能
1，錯誤日志
錯誤日志主要記錄如下幾種日志：
———–> 服務器啟動和關閉過程中的信息
———–> 服務器運行過程中的錯誤信息
———–> 事件調度器運行一個事件時產生的信息
———–> 在從服務器上啟動從服務器進程時產生的信息
錯誤日志定義：
可以用–log-error [ = file_name ]選項來指定mysqld保存錯誤日志文件的位置。如果沒有給定file_name值，mysqld使用錯誤日志名host_name.err 並在數據目錄中寫入日志文件。如果你執行FLUSH LOGS，錯誤日志用-old重新命名後綴並且mysqld創建一個新的空日志文件。(如果未給出–log-error選項，則不會重新命名）。
錯誤日志一般有以上兩個變量可以定義：
錯誤日志文件：log_error
啟用警告信息：log_warnings （默認啟用）
mysql> show global variables like 'log_error'; +---------------+-----------------------------------------+ | Variable_name | Value | +---------------+-----------------------------------------+ | log_error | /mydata/data1/localhost.localdomain.err | +---------------+-----------------------------------------+ 1 row in set (0.00 sec) mysql> show global variables like 'log_warnings'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | log_warnings | 1 | +---------------+-------+ 1 row in set (0.00 sec)
2、一般查詢日志
啟動開關：general_log={ON|OFF}
日志文件變量：general_log_file [ =/PATH/TO/file]
全局日志開關：log={ON|OFF} 該開關打開後，所有日志都會被啟用
記錄類型：log_output={TABLE|FILE|NONE}
log_output定義了日志的輸出格式，可以是表，文件，若設置為NONE，則不啟用日志，因此，要啟用通用查詢日志，需要至少配置general_log=ON，log_output={TABLE|FILE}。而general_log_file如果沒有指定，默認名是host_name.log。由於一般查詢使用量比較大，啟用寫入日志文件，服務器的I/O操作較多，會大大降低服務器的性能，所以默認為關閉的。
mysql> show global variables like 'general_log'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | general_log | OFF | +---------------+-------+ 1 row in set (0.00 sec) mysql> show global variables like 'general_log_file'; +------------------+-----------------------------+ | Variable_name | Value | +------------------+-----------------------------+ | general_log_file | /mydata/data1/localhost.log | +------------------+-----------------------------+ 1 row in set (0.01 sec)
可以使用以下命令開啟general_log：
mysql> set global general_log=1; Query OK, 0 rows affected (0.00 sec) mysql> show global variables like 'general_log'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | general_log | ON | +---------------+-------+ 1 row in set (0.00 sec)
3、慢查詢日志
查詢超時時間：long_query_time
啟動慢查日志：log_slow_queries={YES|NO}
啟動慢查日志 : slow_query_log
日志記錄文件：slow_query_log_file [= file_name ]
MySQL如果啟用了slow_query_log=ON選項，就會記錄執行時間超過long_query_time的查詢（初使表鎖定的時間不算作執行時間）。日志記錄文件如果沒有給出file_name值， 默認為主機名，後綴為-slow.log。如果給出了文件名，但不是絕對路徑名，文件則寫入數據目錄。
mysql> show global variables like '%slow_query_log%'; +---------------------+----------------------------------+ | Variable_name | Value | +---------------------+----------------------------------+ | slow_query_log | OFF | | slow_query_log_file | /mydata/data1/localhost-slow.log | +---------------------+----------------------------------+ 2 rows in set (0.00 sec)
默認沒有啟用慢查詢，為了服務器調優，建議開啟。
mysql> show global variables like 'long_query_time'; +-----------------+-----------+ | Variable_name | Value | +-----------------+-----------+ | long_query_time | 10.000000 | +-----------------+-----------+ 1 row in set (0.00 sec)
超長時間默認為10秒，超過了即為慢查詢。
4，二進制日志
二進制日志啟動開關：log-bin [= file_name]
在5.6及以上版本一定要手動指定。5.6以下版本默認file_name為$datadir/mysqld-binlog,二進制日志用於記錄所有更改數據的語句，主要用於復制和即時點恢復。二進制日志的主要目的是在數據庫存在故障時，恢復時能夠最大可能地更新數據庫（即時點恢復），因為二進制日志包含備份後進行的所有更新,二進制日志還用於在主復制服務器上記錄所有將發送給從服務器的語句。
查看二進制日志的工具為：mysqlbinlog
二進制日志的格式：
——> 基於語句: statement
——> 基於行: row
——> 混合方式: mixed
由於基於語句和基於行的日志格式都有自己的好處，MySQL使用的二進制日志文件是混合方式的二進制日志，內置策略會自動選擇最佳的格式。
二進制日志事件：
——> 產生的時間：starttime
——> 相對位置：position
二進制日志文件：
——> 索引文件
——> 二進制日志文件
在數據目錄下有一個mysql-bin.index便是索引文件，以mysql-bin開頭並以數字結尾的文件為二進制日志文件。
日志的滾動：
MySQL的滾動方式與其他日志不太一樣，滾動時會創建一個新的編號大1的日志用於記錄最新的日志，而原日志名字不會被改變。每次重啟MySQL服務，日志都會自動滾動一次。
另外如果需要手動滾動，則使用命令：
mysql> flush logs; Query OK, 0 rows affected (0.02 sec) mysql> show master status; #查看當前正在使用的二進制文件 +------------------+----------+--------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +------------------+----------+--------------+------------------+ | mysql-bin.000033 | 107 | | | +------------------+----------+--------------+------------------+ 1 row in set (0.00 sec)
其默認的position是從107開始的。
mysql> show binary logs; #查看所有的二進制文件
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000020 | 107 |
| mysql-bin.000021 | 107 |
| mysql-bin.000022 | 50676 |
| mysql-bin.000023 | 150 |
| mysql-bin.000024 | 621 |
| mysql-bin.000025 | 107 |
| mysql-bin.000026 | 4509 |
| mysql-bin.000027 | 150 |
| mysql-bin.000028 | 150 |
| mysql-bin.000029 | 150 |
| mysql-bin.000030 | 357 |
| mysql-bin.000031 | 107 |
| mysql-bin.000032 | 150 |
| mysql-bin.000033 | 107 |
+------------------+-----------+
14 rows in set (0.00 sec)
mysql> use mysql_shiyan;
Database changed
mysql> insert into department(dpt_name) values('feiyu');
Query OK, 1 row affected (0.00 sec)
mysql> show master status;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000033 | 331 | | |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
插入數據後，position已經發生改變了。
mysql> insert into department(dpt_name) values('feiyu1'); #再插入一條數據
Query OK, 1 row affected (0.00 sec)
mysql> show binlog events in 'mysql-bin.000033'; #查看二進制文件中記錄的內容
+------------------+-----+-------------+-----------+-------------+-------------------------------------------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+------------------+-----+-------------+-----------+-------------+-------------------------------------------------------------------------+
| mysql-bin.000033 | 4 | Format_desc | 1 | 107 | Server ver: 5.5.36-log, Binlog ver: 4 |
| mysql-bin.000033 | 107 | Query | 1 | 183 | BEGIN |
| mysql-bin.000033 | 183 | Query | 1 | 304 | use `mysql_shiyan`; insert into department(dpt_name) values('feiyu') |
| mysql-bin.000033 | 304 | Xid | 1 | 331 | COMMIT /* xid=12 */ |
| mysql-bin.000033 | 331 | Query | 1 | 407 | BEGIN |
| mysql-bin.000033 | 407 | Query | 1 | 529 | use `mysql_shiyan`; insert into department(dpt_name) values('feiyu1') |
| mysql-bin.000033 | 529 | Xid | 1 | 556 | COMMIT /* xid=14 */ |
+------------------+-----+-------------+-----------+-------------+-------------------------------------------------------------------------+
7 rows in set (0.00 sec)
mysql> show binlog events in 'mysql-bin.000033' from 407; #也可以從某個位置查看二進制文件
+------------------+-----+------------+-----------+-------------+-------------------------------------------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+------------------+-----+------------+-----------+-------------+-------------------------------------------------------------------------+
| mysql-bin.000033 | 407 | Query | 1 | 529 | use `mysql_shiyan`; insert into department(dpt_name) values('feiyu1') |
| mysql-bin.000033 | 529 | Xid | 1 | 556 | COMMIT /* xid=14 */ |
+------------------+-----+------------+-----------+-------------+-------------------------------------------------------------------------+
2 rows in set (0.00 sec)
mysql> purge binary logs to 'mysql-bin.000025'; #刪除某個序號之前的日志文件
Query OK, 0 rows affected (0.04 sec)
mysql> show binary logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000025 | 107 |
| mysql-bin.000026 | 4509 |
| mysql-bin.000027 | 150 |
| mysql-bin.000028 | 150 |
| mysql-bin.000029 | 150 |
| mysql-bin.000030 | 357 |
| mysql-bin.000031 | 107 |
| mysql-bin.000032 | 150 |
| mysql-bin.000033 | 556 |
+------------------+-----------+
9 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' ：結束時間
←#4#root@localhost ~ →mysqlbinlog --start-position 407 --stop-position 556 mysql-bin.000033 /*!40019 SET @@session.max_insert_delayed_threads=0*/; /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/; DELIMITER /*!*/; mysqlbinlog: File 'mysql-bin.000033' not found (Errcode: 2) DELIMITER ; # End of log file ROLLBACK /* added by mysqlbinlog */; /*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
5，中繼日志
這個後面會講到。
6，事務日志
事務性存儲引擎用於保證（ACID）原子性、一致性、隔離性和持久性；其不會立即寫到數據文件中，而是寫到事務日志中。
innodb_flush_log_at_trx_commit:
———–> 0: 每秒同步，並執行磁盤flush操作；
———–> 1：每事務同步，並執行磁盤flush操作；
———–> 2: 每事務同步，但不執行磁盤flush操作；
mysql> show global variables like 'innodb_flush_log_at_trx_commit'; +--------------------------------+-------+ | Variable_name | Value | +--------------------------------+-------+ | innodb_flush_log_at_trx_commit | 1 | +--------------------------------+-------+ 1 row 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。
