MySQL的日誌總結

Thinking771470736發表於2020-09-27

MySql

前言

MySQL中有以下日誌檔案，分別是：

　　1：重做日誌（redo log）

　　2：回滾日誌（undo log）

　　3：二進位制日誌（binlog）

　　4：錯誤日誌（errorlog）

　　5：慢查詢日誌（slow query log）

　　6：一般查詢日誌（general log）

　　7：中繼日誌（relay log）。

其中重做日誌和回滾日誌與事務操作息息相關，二進位制日誌也與事務操作有一定的關係，這三種日誌，對理解MySQL中的事務操作有著重要的意義。

一、重做日誌（redo log）

作用：

　　確保事務的永續性。redo日誌記錄事務執行後的狀態，用來恢復未寫入data file的已成功事務更新的資料。防止在發生故障的時間點，尚有髒頁未寫入磁碟，在重啟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 指定日誌檔案組所在的路徑，預設./ ，表示在資料庫的資料目錄下。

　　innodb_log_files_in_group 指定重做日誌檔案組中檔案的數量，預設2

關於檔案的大小和數量，由以下兩個引數配置：

　　innodb_log_file_size 重做日誌檔案的大小。

　　innodb_mirrored_log_groups 指定了日誌映象檔案組的數量，預設1

其他：

　　很重要一點，redo log是什麼時候寫盤的？前面說了是在事物開始之後逐步寫盤的。

　　之所以說重做日誌是在事務開始之後逐步寫入重做日誌檔案，而不一定是事務提交才寫入重做日誌快取，原因就是，重做日誌有一個快取區Innodb_log_buffer，Innodb_log_buffer的預設大小為8M(這裡設定的16M),Innodb儲存引擎先將重做日誌寫入innodb_log_buffer中。

　　然後會通過以下三種方式將innodb日誌緩衝區的日誌重新整理到磁碟

　　Master Thread 每秒一次執行重新整理Innodb_log_buffer到重做日誌檔案。

　　每個事務提交時會將重做日誌重新整理到重做日誌檔案。

　　當重做日誌快取可用空間少於一半時，重做日誌快取被重新整理到重做日誌檔案

　　由此可以看出，重做日誌通過不止一種方式寫入到磁碟，尤其是對於第一種方式，Innodb_log_buffer到重做日誌檔案是Master Thread執行緒的定時任務。

　　因此重做日誌的寫盤，並不一定是隨著事務的提交才寫入重做日誌檔案的，而是隨著事務的開始，逐步開始的。

另外引用《MySQL技術內幕 Innodb 儲存引擎》（page37）上的原話：

　　即使某個事務還沒有提交，Innodb儲存引擎仍然每秒會將重做日誌快取重新整理到重做日誌檔案。

　　這一點是必須要知道的，因為這可以很好地解釋再大的事務的提交（commit）的時間也是很短暫的。

二、回滾日誌（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配置。

其他：

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

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

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

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

三、二進位制日誌（binlog）：

作用：

　　用於複製，在主從複製中，從庫利用主庫上的binlog進行重播，實現主從同步。

　　用於資料庫的基於時間點的還原。

內容：

　　邏輯格式的日誌，可以簡單認為就是執行過的事務中的sql語句。

　　但又不完全是sql語句這麼簡單，而是包括了執行的sql語句（增刪改）反向的資訊，也就意味著delete對應著delete本身和其反向的insert；update對應著update執行前後的版本的資訊；insert對應著delete和insert本身的資訊。

　　在使用mysqlbinlog解析binlog之後一些都會真相大白。

　　因此可以基於binlog做到類似於oracle的閃回功能，其實都是依賴於binlog中的日誌記錄。

什麼時候產生：

　　事務提交的時候，一次性將事務中的sql語句（一個事物可能對應多個sql語句）按照一定的格式記錄到binlog中。

　　這裡與redo log很明顯的差異就是redo log並不一定是在事務提交的時候重新整理到磁碟，redo log是在事務開始之後就開始逐步寫入磁碟。

　　因此對於事務的提交，即便是較大的事務，提交（commit）都是很快的，但是在開啟了bin_log的情況下，對於較大事務的提交，可能會變得比較慢一些。

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

什麼時候釋放：

　　binlog的預設是保持時間由引數expire_logs_days配置，也就是說對於非活動的日誌檔案，在生成時間超過expire_logs_days配置的天數之後，會被自動刪除。

對應的物理檔案：

　　配置檔案的路徑為log_bin_basename，binlog日誌檔案按照指定大小，當日志檔案達到指定的最大的大小之後，進行滾動更新，生成新的日誌檔案。

　　對於每個binlog日誌檔案，通過一個統一的index檔案來組織。

其他：

　　二進位制日誌的作用之一是還原資料庫的，這與redo log很類似，很多人混淆過，但是兩者有本質的不同

　　作用不同：redo log是保證事務的永續性的，是事務層面的，binlog作為還原的功能，是資料庫層面的（當然也可以精確到事務層面的），雖然都有還原的意思，但是其保護資料的層次是不一樣的。

　　內容不同：redo log是物理日誌，是資料頁面的修改之後的物理記錄，binlog是邏輯日誌，可以簡單認為記錄的就是sql語句

　　另外，兩者日誌產生的時間，可以釋放的時間，在可釋放的情況下清理機制，都是完全不同的。

　　恢復資料時候的效率，基於物理日誌的redo log恢復資料的效率要高於語句邏輯日誌的binlog

　　關於事務提交時，redo log和binlog的寫入順序，為了保證主從複製時候的主從一致（當然也包括使用binlog進行基於時間點還原的情況），是要嚴格一致的，MySQL通過兩階段提交過程來完成事務的一致性的，也即redo log和binlog的一致性的，理論上是先寫redo log，再寫binlog，兩個日誌都提交成功（刷入磁碟），事務才算真正的完成。

四、錯誤日誌
　　錯誤日誌記錄著mysqld啟動和停止,以及伺服器在執行過程中發生的錯誤的相關資訊。在預設情況下，系統記錄錯誤日誌的功能是關閉的，錯誤資訊被輸出到標準錯誤輸出。
　　指定日誌路徑兩種方法:
　　　　編輯my.cnf 寫入 log-error=[path]
　　　　通過命令引數錯誤日誌 mysqld_safe –user=mysql –log-error=[path] &

顯示錯誤日誌的命令（如下圖所示）

五、普通查詢日誌 general query log

　　記錄了伺服器接收到的每一個查詢或是命令，無論這些查詢或是命令是否正確甚至是否包含語法錯誤，general log 都會將其記錄下來，記錄的格式為 {Time ，Id ，Command，Argument }。也正因為mysql伺服器需要不斷地記錄日誌，開啟General log會產生不小的系統開銷。因此，Mysql預設是把General log關閉的。

檢視日誌的存放方式：show variables like ‘log_output’;

　　如果設定mysql> set global log_output=’table’ 的話，則日誌結果會記錄到名為gengera_log的表中，這表的預設引擎都是CSV
　　如果設定表資料到檔案set global log_output=file;
　　設定general log的日誌檔案路徑：
　　　　set global general_log_file=’/tmp/general.log’;
　　　　開啟general log： set global general_log=on;
　　　　關閉general log： set global general_log=off;