http://www.cnblogs.com/roverliang/p/6414457.html

MySQL 日志系統

做過大型系統的都知道，日志的作用不用小覷，往往到了項目中后期，對項目進行優化升級都是依據日志做出升級優化的決策的。那么學習MySQL，日志部分當然不能錯過。我們面試中實際應用的所談到的優化都是要從日志中得出來的。系統的學習mysql的日志，有助于我們準確的定位問題，提高自己的工作水平。此外，后面的一系列日志會重點從DBA的運維方面進行著手，系統的去理解MySQL各方面的配置，做到知己知彼，讓MySQL成為自己得心應手的數據倉庫。

一、MySQL的日志類型

默認情況下，所有的MySQL日志以文件的方式存放在數據庫根目錄下：

[root@roverliang data]# pwd/usr/local/webserver/extend_lib/mysql/data[root@roverliang data]# lsauto.cnf  ibdata1  ib_logfile0  ib_logfile1  mysql  mytest  performance_schema  roverliang  roverliang.err  roverliang.pid  test
MySQL的日志類型有以下幾種：
1.  錯誤日志（error）,MySQL服務實例啟動、運行或者停止等相關信息。2.  普通查詢日志（general）,MySQL服務實例運行的所有SQL語句或者MySQL命令。3.  二進制日志（binary）,對數據庫執行的所有更新語句，不包括select 和show語句。4.  慢查詢日志（slow），執行時間超過long_query_time 設置值的SQL語句，或者沒有使用索引的SQL語句。
二、MySQL日志緩存
一個高速、穩定、可靠的系統，緩存在其中必定起著至關重要的作用。MySQL日志處理也使用了緩存機制。MySQL日志最初存放在MySQL服務器的內存中，若超過指定的存儲容量，內存中的日志則寫（或者刷新flush）到外存中，以數據庫表或者以文件的方式永遠的保存在硬盤中。
三、MySQL錯誤日志（error log）
MySQL的錯誤日志主要記錄MySQL服務實例每次啟動、停止的詳細信息，以及MySQL實例運行過程中產生的警告或者錯誤信息。和其他的日志不同，MySQL的error日志必須開啟，無法關閉。
默認情況下，錯誤日志的文件名為： 主機名.err。 但error 日志并不會記錄所有的錯誤信息，只有MySQL服務實例運行過程中發聲的關鍵錯誤（critical）才會被記錄下來。
mysql> show variables like 'log_error'/G*************************** 1. row ***************************Variable_name: log_errorValue: /usr/local/webserver/extend_lib/mysql/data/roverliang.err1 row in set (0.02 sec)
四、MySQL普通查詢日志(general log)
MySQL普通查詢日志記錄MySQL服務實例所有的操作，如select、update、insert、delete等操作，無論該操作是否成功執行。還有MySQL客戶機與MySQL服務端連接及斷開的相關信息，無論連接成功還是失敗。與MySQL普通查詢日志有關的參數有三個。
[]()general_log
mysql> show variables like 'general_log';+---------------+-------+| Variable_name | Value |+---------------+-------+| general_log   | OFF   |+---------------+-------+1 row in set (0.01 sec)
可以通過 set @@global.general_log = 1 的方式來開啟普通查詢日志。
mysql> set @@global.general_log =1;mysql> show variables like 'general_log';+---------------+-------+| Variable_name | Value |+---------------+-------+| general_log   | ON    |+---------------+-------+
但是通過這種方式修改MySQL的變量只會在當前的MySQL實例運行期間生效，一旦MySQL重啟，則會重新恢復到默認的狀態。永久生效的方式是修改mysql 的my.cnf 文件。在配置文件后添加：
general_log = 1 
general_log_file
普通查詢日志一旦開啟，MySQL服務實例將自動創建普通查詢日志文件，general_log_file參數設置了普通查詢日志文件的物理位置。如下：
mysql> show variables like 'general_log_file';+------------------+-----------------------------------------------------------+| Variable_name    | Value                                                     |+------------------+-----------------------------------------------------------+| general_log_file | /usr/local/webserver/extend_lib/mysql/data/roverliang.log |+------------------+-----------------------------------------------------------+
注意：由于普通查詢日志幾乎記錄了MySQL的所有操作，對于數據訪問頻繁的數據庫服務器而言，如果開啟MySQL的普通查詢日志將會大幅度的降低數據庫的性能，因此建議關閉普通查詢日志。只有在特殊時期，如需要追蹤某些特殊的查詢日志，可以臨時打開普通的查詢日志。
log_output
log_output參數設置了普通查詢日志以及慢查詢日志的內容存儲到數據庫表中。 可以使用set @@global.log_output='table' 將普通查詢日志及慢查詢日志存入mysql系統數據庫中的general 表 以及slow_log 表中。值得注意的是這兩個表的存儲引擎為CSV,此后查看新的普通查詢日志內容時便可以使用SQL語句;
set @@global.log_output = 'table';mysql> show variables like 'log_output';+---------------+-------+| Variable_name | Value |+---------------+-------+| log_output    | TABLE |+---------------+-------+
五、MySQL慢查詢日志（slow log）
慢查詢日志的相關問題，面試的時候面試官非常青睞聊這方面的問題。以前只能夠高談闊論MySQL主從架構，以及從各個方面優化MySQL，但是平時并沒有真正的去了解過慢查詢如何去開啟以及相關的配置。
使用MySQL慢查詢日志可以有效的跟蹤 執行時間過長 或者 沒有使用索引的查詢語句。這種包括select 語句，update語句，delete語句，以及insert語句，為優化查詢提供幫助。與普通查詢日志不同的另一個區別在于，慢查詢日志只包含成功執行過的查詢語句。與MySQL慢查詢日志有關的參數有5個。
1、slow_query_log
slow_query_log 設置慢查詢日志是否開啟。
mysql> show variables like 'slow_query_log';+----------------+-------+| Variable_name  | Value |+----------------+-------+| slow_query_log | OFF   |+----------------+-------+
2、slow_query_log_file
慢查詢日志一旦開啟，MySQL實例將自動創建慢查詢日志文件。slowquerylog_file 所指定的文件，存放慢查詢日志內容。 修改方法和上文所示的一致。直接到my.cnf 文件中進行編輯。
3、long_query_time
long_query_time 設置了慢查詢的時間閾值。默認閾值是10s。
4、log_quries_not_using_indexes
log_quries_not_using_indexes 是否將不使用索引的查詢語句記錄到慢查詢日志中，無論查詢速度有多快。
mysql> set @@global.log_queries_not_using_indexes=1;mysql> show variables like 'log_queries_not_using_indexes';+-------------------------------+-------+| Variable_name                 | Value |+-------------------------------+-------+| log_queries_not_using_indexes | ON    |+-------------------------------+-------+
5、log_output
設置了普通查詢日志以及慢查詢日志的輸出形式，值有兩個file、table;
六、MySQL慢查詢日志查看
log_output參數可以設置慢查詢日志的輸出形式。默認為FILE,可以設置為TABLE;
mysql> desc mysql.slow_log;+----------------+---------------------+| Field          | Type                |+----------------+---------------------+| start_time     | timestamp           || user_host      | mediumtext          || query_time     | time                || lock_time      | time                || rows_sent      | int(11)             || rows_examined  | int(11)             || db             | varchar(512)        || last_insert_id | int(11)             || insert_id      | int(11)             || server_id      | int(10) unsigned    || sql_text       | mediumtext          || thread_id      | bigint(21) unsigned |+----------------+---------------------+
其中: lock_time表示該SQL執行時被鎖阻塞的時間。 rows_send表示執行SQL后返回的內容行數。rows_examined表示該SQL執行時實際掃描的記錄條數。
但是使用TABLE來存儲慢查詢日志并不常見，業務量較大的情況下，對于系統的主服務會有影響。我們可以使用FILE 的方式來進行日志存儲。安裝MySQL的時候在MySQL的bin目錄下已經默認安裝了mysqldumpslow.pl工具來進行慢查詢的日志分析。 window下使用這個工具，可能需要折騰些配置，這不在本文的介紹范圍內，學系統服務，還是移步linux吧。 linux下的命令以及工具都可以使用 命令本身 + --help 的選項來查看幫助文檔。
-s 表示按照何種方式排序
子選項: c、t、l、r
c : SQL執行的次數t : 執行時間l : 鎖等待時間r : 返回數據條數at、al、ar 是對應 t l r 的平均值。 -t :表示返回前 N 條記錄。
-g： grep 縮寫。包含模糊匹配
常用法如下：
//返回訪問次數最多的20條SQL語句./mysqldumpslow -s c -t 20 /usr/local/webserver/extend_lib/mysql/data/roverliang-slow.log //返回return記錄數最多的20條SQL語句./mysqldumpslow -s r -t 20 /usr/local/webserver/extend_lib/mysql/data/roverliang-slow.log //返回含有like的SQL語句./mysqldumpslow -g 'like' 20 /usr/local/webserver/extend_lib/mysql/data/roverliang-slow.log 
七、二進制日志（binary）
二進制日志和前面提到的幾種日志不同，二進制不能直接通過cat或者less 文本查看器查看。需要借助專業的工具。二進制日志主要記錄數據庫的變化情況，因此可以用作主從庫的同步。內容主要包括數據庫所有的更新操作，use語句、insert語句、delete語句、update語句、create語句、alter語句、drop語句。用一句更簡潔易懂的話概括就是：所有涉及數據變動的操作，都要記錄進二進制日志中。
啟動二進制日志 使用 show variables like 'log_bin'/G 來查看二進制日志是否開啟。
mysql> show variables like 'log_bin'/G*************************** 1. row ***************************Variable_name: log_bin        Value: OFF1 row in set (0.00 sec)mysql> set @@global.log_bin=1;ERROR 1238 (HY000): Variable 'log_bin' is a read only variablemysql> 
看到log_bin 默認是不開啟的，并且是個只讀的變量，需要在my.cnf中配置，然后重啟MySQL。service mysql restart 重啟MySQL后，在data目錄會發現生成了一個1.000001的文件。實際上每次MySQL重啟，在目錄下都會生成一個這樣的文件，文件名依次遞增。此外，MySQL還會在該目錄下創建一個二進制日志的索引文件，可以通過命令show variables like 'log_bin_index'/G來查看索引文件的位置，然后使用cat命令看下。會發現，里面記錄著二進制文件的相對路徑。
查看二進制日志 可以使用MySQL 中自帶的工具。具體位置在mysql的bin目錄下。 mysqlbinlog命令的常用選項：
-s                          以精簡的方式顯示日志內容-v                          以詳細的方式顯示日志內容-d=數據庫名                  只顯示指定數據庫的日志內容-o=n                        忽略日志中前n行MySQL命令-r=file                    將指定內容寫入指定文件--start-datetime                              顯示指定時間范圍內的日志內容--stop-datetime         --start-position                                    顯示指定位置間隔內的日志內容--stop-position     
獲取當前使用的二進制日志文件
mysql> show master status;+----------+----------+--------------+------------------+-------------------+| File     | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |+----------+----------+--------------+------------------+-------------------+| 1.000002 |      120 |              |                  |                   |+----------+----------+--------------+------------------+-------------------+1 row in set (0.00 sec)
使用二進制日志恢復數據
語法很簡單：
mysqlbinlog -s 1.000001 | mysql -h 192.168.1.188 -u root -p
mysqlbinlog 之后可以跟 --start-datetime 、--stop-datetime 、start-position、stop-position 等參數。
--start-datetime 、--stop-datetime 這兩個參數可以基于時間點進行數據恢復；
start-position 、stop-position 可以進行操作點更加細化的進行數據恢復；
MySQL二進制日志相關參數
mysql> show variables like '%binlog%';+-----------------------------------------+----------------------+| Variable_name                           | Value                |+-----------------------------------------+----------------------+| binlog_cache_size                       | 32768                || binlog_checksum                         | CRC32                || binlog_direct_non_transactional_updates | OFF                  || binlog_error_action                     | IGNORE_ERROR         || binlog_format                           | STATEMENT            || binlog_gtid_simple_recovery             | OFF                  || binlog_max_flush_queue_time             | 0                    || binlog_order_commits                    | ON                   || binlog_row_image                        | FULL                 || binlog_rows_query_log_events            | OFF                  || binlog_stmt_cache_size                  | 32768                || binlogging_impossible_mode              | IGNORE_ERROR         || innodb_api_enable_binlog                | OFF                  || innodb_locks_unsafe_for_binlog          | OFF                  || max_binlog_cache_size                   | 18446744073709547520 || max_binlog_size                         | 1073741824           || max_binlog_stmt_cache_size              | 18446744073709547520 || simplified_binlog_gtid_recovery         | OFF                  || sync_binlog                             | 0                    |+-----------------------------------------+----------------------+
max_binlog_size
maxbinlogsize 單個二進制日志文件的大小。如果超過該值，則生成新的文件，后綴名+1；
binlog_cache_size
binlogcachesize 內存中存放二進制日志的緩存大小
sync_binlog
sync_binlog 緩存中寫入幾次二進制日志，開始同步刷新到外存（硬盤）中。
log_slave_updates
logslvaeupdates 用于主從復制
二進制日志的清理
原則上要先將準備清理的日志通過物理備份的方式備份到其他存儲設備上，永久留存。 然后推薦使用下面兩種風險較小的清理方式： 第一種：
purge master logs before '2017-02-16 00:00:00';
第二種： 直接在MySQL的配置文件my.cnf 中設置expire_logs_days參數來設置二進制文件的過期天數，過期的二進制文件將會被自動刪除。建議在刪除前另啟一個周期計劃任務，定期去備份二進制任務。免得一些數據若干天才發現出現了差錯，而二進制日志被自動刪除了。
expire_logs_days=90
八 、InnoDB 事務日志
MySQL會最大程度的利用緩存，從而提高數據的訪問效率。那么換一句話來說，任何高性能的系統都必須利用到緩存，從各個層面來講，緩存都發揮了巨大的作用。再上升到一個高度提煉一下：緩存和隊列是實現高性能的必走之路。那么對于數據庫來說這個卻是個很棘手的問題，要保證數據更高效率的讀取和存儲，所以要利用到緩存。但是要保證數據的一致性，則必須保證所有的數據都必須準確無誤的存儲到數據庫中，及時發生意外，也要保證數據可恢復。我們知道InnoDB是一個事務安全的存儲引擎，而一致性是事務ACID中的一個重要特性。InnoDB存儲引擎主要是通過InnoDB事務日志實現數據一致性的，InnoDB事務日志包括重做（redo）日志，以及回滾（undo）日志。
InnoDB事務日志與前面提高的日志不同，InnoDB事務日志由InnoDB存儲引擎自行維護，而且內容不能被數據庫管理員讀取。
重做日志（redo）
重做日志主要是記錄已經全部完成的事務,即執行了commit的日志，在默認情況下重做日志的值記錄在iblogfile0 以及iblogfile1重做日志中。
[root@roverliang data]# pwd/usr/local/webserver/mysql/data[root@roverliang data]# ls ib*ibdata1  ib_logfile0  ib_logfile1
回滾日志（undo）
回滾日志主要記錄已經部分完成并且寫入硬盤的未完成事務，默認情況情況下，回滾日志的信息記錄在表空間文件，共享表空間文件ibdata1或者獨享表空間未見ibd中。
由上圖我們可以知道，回滾日志默認是記錄在ibdta1中的。我的mysql系統版本為：5.6.24.
Checkpoint機制
MySQL服務器崩潰后，重新啟動MySQL服務時，由于重做日志（redo）與回滾日志（undo）日志的存在，InnoDB通過回滾日志（undo）日志將所有已部分完成并寫入硬盤的未完成事務進行回滾操作（rollback）。然后將重做日志（undo）日志中的事務全部重新執行一遍即可恢復所有的數據。但是數據量過大，為了縮短恢復的時間InnoDB引入了Checkpoint 機制。
臟頁（dirty page）
當事務需要修改某條記錄是，InnoDB首先將該數據所在的數據塊從外存中讀取到硬盤中，事務提交后，InnoDB修改數據頁中的記錄，這時緩存的數據頁已經和外存中的數據塊已經不一樣了，此時緩存中的數據頁稱為臟頁（dirty page）,臟頁刷新到外存中，變為干凈頁（clean page）。
備注：一個內存頁默認為4K，或者4K的倍數。你可以把內存想象成一本可以擦洗的書，每次MySQL讀數據的時候，向內存申請幾張干凈的書頁，然后書寫上去。當數據刷新到硬盤之后，這些數據頁馬上被擦除，供其他程序使用。
日志序列號（log sequence number）
日志序列號（LSN）是日志空間中每條日志的結束點，用字節偏移量表示，在Checkpoin和恢復時使用。
Checkpoint 機制原理 假設在某個時間點，所有的臟頁（dirty page）都被刷新到了硬盤上，這個時間點之前的所有重做日志（redo）就不需要重做了.系統就就下這個時間點重做日志的結尾位置作為Checkpoint,Checkpoint之前的重做日志也就不需要再重做了，可以放心的刪除掉。為了更好的利用重做日志（redo）的空間,InnoDb采用輪循的策略使用重做日志空間，因此InnoDB的重做日志文件至少為2個。通過Checkpoint 機制，通過重做日志（redo）將數據庫崩潰時已經完成但還沒有來得及將緩存中已經修改但還未完全寫入外存的事務進行重做（undo）操作，即可保證數據的一致性，也可以縮短恢復時間。
InnoDB重做日志(redo)的參數
innodb_log_buffer_size: 設置了重做日志緩存的大小。innodb_log_files_in_group : 設置了日志文件組中重做日志（redo）日志的數量。innodb_log_file_size: 設置了重做日志文件的大小，文件越大，恢復起來耗時越長。innodb_mirrored_log_groups: 重做日志鏡像文件組數量，只能設置為1.innodb_log_group_home_dir： 設置日志文件組存放的目錄，默認在數據庫根目錄下。
InnoDB回滾日志（undo）的參數
innodb_undo_directory: 設置回滾日志存放的目錄。innodb_undo_logs :設置回滾日志的回滾段大小，默認為128kinnodb_undo_tablespace:設置了回滾日志由多少個回滾日志文件組成，默認為0.
Warning 特別注意：安裝MySQL后需要在my.cnf中設置回滾日志的參數，如果創建數據庫以后再設置回滾日志的參數，MySQL就會報錯，并且回滾日志建好后，就不能再次修改或者增加。
九 、日志文件備份
備份的時候可以使用flush logs，關閉當前的所有日志文件，然后產生新的日志文件。關閉日志文件后，可以采用物理方式備份。 另外flush logs可以添加具體的日志類型：
flush error logsflush general logsflush binary logsflush slow logs本人博客所有文章，均為原創。部分文章中或引用相關資料，但均已著明來源出處。可隨意轉載、分享，但需加本文鏈接，以及版權說明。