MySQL系列之日志彙總：redo log、undo log、binlog、errorlog、slow query log、general log、relay log

概述

MySQL中至少有7種日志檔案：

• 重做日志（redo log）
• 復原日志（undo log）
• 二進制日志（binlog）
• 錯誤日志（errorlog）
• 慢查詢日志（slow query log）
• 一般查詢日志（general log）
• 中繼日志（relay log）。

MySQL Server Log有4種：Error Log、General Query Log、Binary Log 和 Slow Query Log。其中重做日志和復原日志與事務操作息息相關，二進制日志也與事務操作有一定的關系。注：老版本MySQL還有更新日志（Update Log），​​參考​​，被二進制日志替代。

redo log

作用：

確定事務的持久性。防止在發生故障的時間點，尚有髒頁未寫入磁盤，在重新開機MySQL服務時，可根據redo log進行重做，進而達到事務的持久性這一特性。

内容：

實體格式的日志，記錄的是實體資料頁面的修改的資訊，其redo log是順序寫入redo log file的實體檔案中去的。

建立時間：

事務開始之後就産生redo log，redo log的落盤并不是随着事務的送出才寫入的，而是在事務的執行過程中，便開始寫入redo log檔案中。

删除時間：

當對應事務的髒頁寫入到磁盤之後，redo log的使命完成，重做日志占用的空間就可以重用（被覆寫）。

對應的實體檔案：

預設情況下，對應的實體檔案位于資料庫的data目錄下的​​

​ib_logfile1, ib_logfile2​

​​，​

​innodb_log_group_home_dir​

​​ 指定日志檔案組所在的路徑，預設​

​./​

​，表示在資料庫的資料目錄下。

redolog的大小是固定的，MySQL中可通過修改以下2個配置參數，redolog采用循環寫的方式記錄，當寫到結尾時，會回到開頭循環寫日志：

​

​innodb_log_file_size​

​：檔案大小

​

​innodb_log_files_in_group​

​：檔案數量，預設2

write pos表示日志目前記錄的位置，當ib_logfile_4寫滿後，會從ib_logfile_1從頭開始記錄；check point表示将日志記錄的修改寫進磁盤，完成資料落盤，資料落盤後checkpoint會将日志上的相關記錄擦除掉，即write pos->checkpoint之間的部分是redo log空着的部分，用于記錄新的記錄，checkpoint->write pos之間是redo log待落盤的資料修改記錄。當writepos追上checkpoint時，得先停下記錄，先推動checkpoint向前移動，空出位置記錄新的日志。

​

​innodb_mirrored_log_groups​

​：指定日志鏡像檔案組的數量，預設1

redo log是在事務開始之後逐漸寫盤的。重做日志是在事務開始之後逐漸寫入重做日志檔案，而不一定是事務送出才寫入重做日志緩存，原因，重做日志有一個緩存區​

​innodb_log_buffer​

​​，其預設大小為8M，Innodb存儲引擎先将重做日志寫入​

​innodb_log_buffer​

​​中。

以下三種情況觸發​​

​innodb_log_buffer​

​日志重新整理到磁盤：

1. Master Thread 每秒一次執行重新整理​

   ​innodb_log_buffer​

   ​到重做日志檔案
2. 每個事務送出時會将重做日志重新整理到重做日志檔案
3. 當重做日志緩存可用空間 少于一半時，重做日志緩存被重新整理到重做日志檔案

重做日志通過不止一種方式寫入到磁盤，尤其是對于第一種方式，​

​innodb_log_buffer​

​​到​

​redo log​

​是Master Thread線程的定時任務。重做日志的寫盤，并不一定是随着事務的送出才寫入重做日志檔案的，而是随着事務的開始，逐漸開始的。

即使某個事務還沒有送出，Innodb存儲引擎仍然每秒會将重做日志緩存重新整理到重做日志檔案。

這可以很好地解釋再大的事務的送出（commit）的時間也是很短暫的。《MySQL技術内幕 Innodb 存儲引擎》

undo log

作用：

儲存事務發生之前的資料的一個版本，可以用于復原，同時可以提供多版本并發控制下的讀（MVCC），也即非鎖定讀

内容：

邏輯格式的日志，在執行undo時，僅僅是将資料從邏輯上恢複至事務之前的狀态，而不是從實體頁面上操作實作的，這一點是不同于redo log的。

建立時間：

事務開始之前，将目前的版本生成undo log，undo 也會産生 redo 來保證undo log的可靠性。

删除時間：

當事務送出之後，undo log并不能立馬被删除，而是放入待清理的連結清單，由purge線程判斷是否由其他事務在使用undo段中表的上一個事務之前的版本資訊，決定是否可以清理undo log的日志空間。

對應的實體檔案：

MySQL5.6之前，undo表空間位于共享表空間的復原段中，共享表空間的預設的名稱是ibdata，位于資料檔案目錄中。

MySQL5.6之後，undo表空間可以配置成獨立的檔案，但是提前需要在配置檔案中配置，完成資料庫初始化後生效且不可改變undo log檔案的個數

如果初始化資料庫之前沒有進行相關配置，那麼就無法配置成獨立的表空間。

MySQL5.7之後的獨立undo表空間配置參數如下：

innodb_undo_directory = /data/undospace/ –undo獨立表空間的存放目錄
innodb_undo_logs = 128 –復原段為128KB
innodb_undo_tablespaces = 4 –指定有4個undo log檔案      

如果undo使用的共享表空間，這個共享表空間中又不僅僅是存儲undo的資訊，共享表空間的預設為與MySQL的資料目錄下面，其屬性由參數​

​innodb_data_file_path​

​​配置：​

​innodb_data_file_path=ibdata1:1G:autoextend​

​

其他：

undo是在事務開始之前儲存的被修改資料的一個版本，産生undo日志時，同樣會伴随類似于保護事務持久化機制的redolog的産生。

預設情況下undo檔案是保持在共享表空間的，也即ibdatafile檔案中，當資料庫中發生一些大的事務性操作時，要生成大量的undo資訊，全部儲存在共享表空間中的。

是以共享表空間可能會變的很大，預設情況下，也就是undo 日志使用共享表空間的時候，被“撐大”的共享表空間是不會也不能自動收縮的。

是以，mysql5.7之後的“獨立undo 表空間”的配置就顯得很有必要了。

redo log vs undo log

同：

異：

redo log 記錄資料的實體變化，binlog記錄資料的邏輯變化；

redo log的作用是為持久化而生的。寫完記憶體，如果資料庫挂了，那我們可以通過redo log來恢複記憶體還沒來得及刷到磁盤的資料，将redo log加載到記憶體裡邊，那記憶體就能恢複到挂掉之前的資料了。

binlog

​​官方文檔​​，binlog是MySQL server層維護的一種二進制日志，與InnoDB引擎中的redo/undo log是完全不同的日志。

binlog記錄資料庫表結構和表資料變更，比如update/delete/insert/truncate/create，為複制和恢複而生的，哪怕整個資料庫都被删除。主從伺服器需要保持資料的一緻性，通過binlog來同步資料。

作用：

1. 複制：從庫利用主庫上的binlog進行重播，實作主從同步，達到master-slave資料一緻的目的
2. 恢複：通過mysqlbinlog工具實作基于時間點的資料恢複

建立時間：

事務送出時，一次性将事務中的SQL語句（一個事務可能對應多個SQL語句）按照一定的格式記錄到binlog中。

redo log并不一定是在事務送出時重新整理到磁盤，redo log是在事務開始之後就開始逐漸寫入磁盤。

是以對于事務的送出，即便是較大的事務，送出（commit）都是很快的，但是在開啟bin_log的情況下，對于較大事務的送出，可能會變得比較慢一些。

這是因為binlog是在事務送出的時候一次性寫入的造成的，這些可以通過測試驗證。

删除時間：

binlog預設保留時間由參數​​

​expire_logs_days​

​​配置，也就是說對于非活動的日志檔案，在生成時間超過expire_logs_days配置的天數之後，會被自動删除。​

​show variables like '%expire_logs_days%';​

​

參數

通過指令檢視binlog相關的參數：​

​show variables like '%binlog%';​

​

1. ​

   ​binlog_cache_size​

   ​​：在事務過程中容納binlog SQL語句的緩存大小；binlog緩存是伺服器支援事務存儲引擎并且伺服器啟用二進制日志(—log-bin選項)的前提下為每個Session配置設定的記憶體；

   主要是用來提高binlog的寫速度；可以通過MySQL的以下2個狀态變量來判斷目前的binlog_cache_size的狀況：​​

   ​binlog_cache_use​

   ​​（使用緩沖區存放binlog的次數）和​

   ​binlog_cache_disk_use​

   ​（使用臨時檔案存放binlog的次數）。
2. ​

   ​binlog_stmt_cache_size​

   ​​：發生事務時非事務語句的緩存的大小，可通過MySQL以下2個狀态變量來判斷目前的​

   ​binlog_stmt_cache_size​

   ​​的狀況：​

   ​binlog_stmt_cache_use​

   ​​（緩沖區存放binlog的次數）和​

   ​binlog_stmt_cache_disk_use​

   ​（臨時檔案存放binlog的次數）。
3. ​

   ​max_binlog_cache_size​

   ​​：和​

   ​binlog_cache_size​

   ​相對應，但是所代表的是binlog能夠使用的最大cache記憶體大小；binlog_cache_size對應的每個Session，max_binlog_cache_size對應所有Session；當執行多語句事務的時候，所有Session的使用的記憶體超過max_binlog_cache_size的值時，系統可能會報出”Multi-statement transaction required more than ‘max_binlog_cache_size’ bytes of storage”的錯誤。
4. ​

   ​max_binlog_stmt_cache_size​

   ​​：同​

   ​max_binlog_cache_size​

   ​類似，非事務語句binlog能夠使用的最大cache記憶體大小。
5. ​

   ​max_binlog_size​

   ​：binlog日志最大值，預設1G。該大小并不能非常嚴格控制binlog大小，當binlog比較靠近尾部而又遇到一個較大事務時，系統為了保證事務的完整性，不可能做切換日志的動作，隻能将該事務的所有SQL都記錄進入目前日志，直到該事務結束。
6. ​

   ​sync_binlog​

   ​​：同步​

   ​binlog_cache​

   ​​中資料到磁盤的頻率。​

   ​sync_binlog=n​

   ​​（可選值區間為0-4294967295），表示當每進行n次事務送出之後，MySQL将進行一次​

   ​fsync​

   ​​之類的磁盤同步指令來将​

   ​binlog_cache​

   ​​中的資料強制寫入磁盤。預設設定為0，MySQL不做​

   ​fsync​

   ​​之類的磁盤同步指令重新整理​

   ​binlog_cache​

   ​​中的資訊到磁盤，而讓FileSystem自行決定什麼時候來做同步，或cache滿之後才同步到磁盤；這種情況下性能最好，風險最大，可能導緻​

   ​binlog_cache​

   ​​中的資料丢失；​

   ​sync_binlog=1​

   ​性能最差，風險最小。
7. ​

   ​binlog_direct_non_transactional_updates​

   ​：
8. ​

   ​innodb_locks_unsafe_for_binlog​

   ​：
9. ​

   ​max_binlog_stmt_cache_size​

   ​：
10. ​

    ​binlog_format​

    ​：設定binlog的格式，可選值：STATEMENT（預設）, ROW, MIXED

日志格式

1. STATEMENT模式

   statement-based replication，SBR。每一條修改資料的SQL都會被記錄到binlog中，slave端再根據SQL語句重制，不會産生大量的binlog資料；

   優點：不需要記錄每一行的變化，減少binlog日志量，節約IO提高性能。

   缺點：為了讓SQL能在slave端正确重制，需要記錄SQL的執行上下文資訊；在複制某些特殊的函數或者功能時會出現問題，比如sleep()函數。

2. ROW模式

   row-based replication，RBR。5.1.5版本的MySQL開始支援，日志中記錄成每一行資料被修改的形式，然後在slave端再對相同的資料進行修改。在ROW模式下bin-log中可以不記錄執行的SQL語句的上下文相關的資訊，隻需要記錄哪條資料被修改成什麼樣，不會因為某些文法複制出現問題（比如function，trigger等）；

   缺點：每行資料的修改都會記錄，最明顯的就是update語句，導緻更新多少條資料就會産生多少事件，使bin-log檔案很大，而複制要網絡傳輸，影響性能。

   新版本的MySQL中對row level模式也被優化，并不是所有的修改都會以row level來記錄，像遇到表結構變更的時候就會以statement模式來記錄，如果sql語句确實就是update或者delete等修改資料的語句，那麼還是會記錄所有行的變更。

3. MIXED模式

   mixed-based replication，MBR。從5.1.8版本開始，MySQL提供Mixed格式，前面兩種模式的結合。在Mixed模式下，一般的語句修改使用statment格式儲存binlog；如一些函數，statement無法完成主從複制的操作，則采用row格式儲存binlog。MySQL會根據執行的每一條具體的SQL語句來區分對待記錄的日志形式，也就是在Statement和Row之間選擇一種。

事件

解析

binlog解析

工具

binlog vs redo log

當資料庫發生當機重新開機後，可通過redo log将未落盤的資料恢複，即保證已經送出的事務記錄不會丢失。

已有redo log，為啥還需要binlog呢？

1. redo log的大小是固定的，日志上的記錄修改落盤後，日志會被覆寫掉，無法用于資料復原/資料恢複等操作
2. redo log是innodb引擎層實作，并不是所有引擎都有

另外：

1. binlog是server層實作，所有引擎都可以使用binlog日志
2. binlog通過追加的方式寫入的，可通過配置參數​

   ​max_binlog_size​

   ​設定每個binlog檔案的大小，當檔案大小大于給定值後，日志會發生滾動，之後的日志記錄到新的檔案上
3. binlog有兩種記錄模式，statement格式的話是記sql語句， row格式會記錄行的内容，記兩條，更新前和更新後都有。

binlog和redo log必須保持一緻，不允許出現binlog有記錄但redo log沒有的情況，反之亦然。在一個事務中，redo log有prepare和commit兩種狀态，是以，在redo log狀态為prepare時記錄binlog可保證兩日志的記錄一緻，

完整的流程圖

參數設定：

1. ​

   ​innodb_flush_log_at_trx_commit​

   ​：設定為1，表示每次事務的redo log都直接持久化到磁盤，保證MySQL重新開機後資料不丢失
2. ​

   ​sync_binlog​

   ​：設定為1，表示每次事務的binlog都直接持久化到磁盤，保證MySQL重新開機後binlog記錄完整

errorlog

記錄​

​mysqld​

​啟動和關閉過程的資訊，啟停slave以及死鎖日志，bug，core dump等錯誤日志。

slow query log

MySQL的慢查詢日志用來記錄在MySQL中響應時間超過門檻值的語句，具體指運作時間超過long_query_time值的SQL，則會被記錄到慢查詢日志中（日志可以寫入檔案或者資料庫表，如果對性能要求高的話，建議寫檔案）。預設情況下，MySQL資料庫是不開啟慢查詢日志的，long_query_time的預設值為10（即10秒，通常設定為1秒）。

查詢慢查詢日志相關參數：​​

​show variables like '%query%';​

​

1. slow_query_log：ON為開啟慢查詢日志
2. slow_query_log_file：指定慢查詢日志儲存到檔案中（預設名為主機名.log）
3. long_query_time：指定慢查詢的門檻值，預設值為10秒。
4. log_queries_not_using_indexes 如果值設定為ON，則會記錄所有沒有利用索引的查詢（注意：如果隻是将log_queries_not_using_indexes設定為ON，而将slow_query_log設定為OFF，此時該設定也不會生效，即該設定生效的前提是slow_query_log的值設定為ON），一般在性能調優的時候會暫時開啟。

如何查詢目前慢查詢語句的個數？

MySQL中有一個變量​​

​Slow_queries​

​​專門記錄目前慢查詢語句的個數：​

​show global status like '%slow%';​

​

檢視慢查詢日志的輸出格式，可選值FILE、TABLE：​

​show variables like '%log_output%';​

​​。

設定慢查詢日志的輸出格式，表為​​

​mysql.slow_log​

​​，英文逗号分隔：​

​set global log_output='TABLE';​

​

​

​myql.slow_log​

​​表資料格式如下：

​​

​select * from mysql.slow_log limit 1;​

​

start_time	user_host	query_time	lock_time	rows_sent	rows_examined	db	last_insert_id	insert_id	server_id	sql_text	thread_id
2018-02-0711:16:55	root[root] @ [121.196.203.51]	00:00:00	00:00:00	13	40	jp_core_db	select pd.lastAuction from Product pd where pd.status = 'O’and pd.auctionStatus = ‘A’	1621

mysql_slow.log檔案資料格式：

​​

​# Time: 180118 14:58:37# User@Host: root[root] @ localhost [] Id: 150# Query_time: 0.000270 Lock_time: 0.000109 Rows_sent: 0 Rows_examined: 6SET timestamp=1516258717;deletefrom user where User='app';​

​ 不管是表還是檔案，都具體記錄：是那條語句導緻慢查詢（sql_text），該慢查詢語句的查詢時間（query_time），鎖表時間（Lock_time），以及掃描過的行數（rows_examined）等資訊。

工具

MySQL自帶慢查詢日志分析工具mysqldumpslow，Perl語言寫的：

​​

​mysqldumpslow –sc –t 10 slow-query.log​

​​

​-s​

​：表示按何種方式排序，​

​c, t, l, r​

​分别按照記錄次數、時間、查詢時間、傳回的記錄數來排序，​

​ac、at、al、ar​

​表示相應的倒序

​

​-t n, top​

​：表示傳回前面多少條資料

​

​-g​

​：正規表達式比對，大小寫不敏感

輸出示例：​

​Count: 66216 Time=0.00s (127s) Lock=0.00s (2s) Rows=1.7 (115074), root[root]@[121.196.200.51]​

​ 解釋：

• Count表示語句出現次數；
• Time表示執行最長時間（累計總耗時）
• Lock表示等待鎖最長時間（累計等待鎖耗時）
• Rows表示發送給用戶端最多的行數（累計發送給用戶端的行數）

general log

記錄建立的用戶端連接配接和執行的語句。日志檔案的存儲格式可以是FILE、TABLE，與slow query log類似。​

​set global log_output='TABLE';​

​​這種設定指令隻對目前session生效，MySQL重新開機失效；如果要永久生效，需配置​

​my.cnf​

​​。

配置：

​​

​general_log=ON​

​​，預設關閉

​​

​general_log_file=/mysql/general.log​

​，配置檔案名（含完整路徑）

​

​mysql.general_log​

​表結構：

Field	Type	Null	Key	Default	Extra
event_time	timestamp	NO	CURRENT_TIMESTAMP	on update CURRENT_TIMESTAMP
user_host	mediumtext	NO	NULL
thread_id	bigint(21) unsigned	NO	NULL
server_id	int(10) unsigned	NO	NULL
command_type	varchar(64)	NO	NULL
argument	mediumtext	NO	NULL

relay log

​​官方文檔：slave-logs-relaylog​​​，MySQL進行主主複制或主從複制時會在配置檔案制定的目錄下面産生相應的relay log，檢視所有relay log相關參數：​

​show variables like '%relay%';​

​

1. ​

   ​max_relay_log_size​

   ​​：标記relay log 允許的最大值，如果該值為0，則預設值為​

   ​max_binlog_size(1G)​

   ​；
2. ​

   ​relay_log​

   ​​：定義relay_log的位置和名稱，如果值為空，則預設位置在資料檔案的目錄，檔案名為​

   ​host_name-relay-bin.nnnnnn​

   ​（By default, relay log file names have the form host_name-relay-bin.nnnnnn in the data directory）；
3. ​

   ​relay_log_index​

   ​​：同​

   ​relay_log​

   ​，定義relay_log的位置和名稱；
4. ​

   ​relay_log_info_file​

   ​​：設定​

   ​relay-log.info​

   ​​的位置和名稱（relay-log.info記錄MASTER的binary_log的恢複位置和relay_log的位置），也可以配置記錄到MySQL庫中的​

   ​slave_relay_log_info​

   ​表中；
5. ​

   ​relay_log_purge​

   ​：是否自動清空不再需要的中繼日志。預設為ON(啟用)。
6. ​

   ​relay_log_recovery​

   ​​：當slave從庫當機後，假如relay-log損壞，導緻一部分中繼日志沒有處理，則自動放棄所有未執行的​

   ​relay_log​

   ​​，并且重新從master上擷取日志，保證relay-log的完整性。預設情況下該功能是關閉的，設定​

   ​relay_log_recovery=1​

   ​​，可在slave從庫上開啟該功能，建議開啟。開啟該參數需同時開啟​

   ​relay_log_purge​

   ​參數。

This variable also interacts with relay-log-purge, which controls purging of logs when they are no longer needed. Enabling the --relay-log-recovery option when relay-log-purge is disabled risks reading the relay log from files that were not purged, leading to data inconsistency, and is therefore not crash-safe.

1. ​

   ​relay_log_space_limit​

   ​：防止中繼日志寫滿磁盤，設定中繼日志最大限額。但此設定存在主庫崩潰，從庫中繼日志不全的情況，不到萬不得已，不推薦使用；
2. ​

   ​sync_relay_log​

   ​​：參考​

   ​sync_binlog​

   ​，預設值10000，可動态修改，建議采用預設值。
3. ​

   ​sync_relay_log_info​

   ​：和sync_relay_log參數一樣，當設定為1時，slave的I/O線程每次接收到master發送過來的binlog日志都要寫入系統緩沖區，然後刷入relay-log.info裡，這樣是最安全的，因為在崩潰的時候，你最多會丢失一個事務，但會造成磁盤的大量I/O。當設定為0時，并不是馬上就刷入relay-log.info裡，而是由作業系統決定何時來寫入，雖然安全性降低，但可以減少大量的磁盤I/O操作。預設值10000，可動态修改，建議采用預設值。
4. ​

   ​relay_log_info_repository​

   ​：輸出TABLE，表示？
5. ​

   ​relay_log_basename​

   ​​：​

   ​relay_log_index​

   ​去掉字尾名

複制過程和relay_log

？？

relay_log vs binlog

日志清理

參考