MySQL 服務器中的 6 種日志類型

作者: 不剪發(fā)的Tony老師
畢業(yè)于北京航空航天大學，十多年數據庫管理與開發(fā)經驗，目前在一家全球性的金融公司從事數據庫架構設計。CSDN學院簽約講師以及GitChat專欄作者。csdn上的博客收藏于以下地址：https://tonydong.blog.csdn.net


文章目錄

        錯誤日志
        通用查詢日志
        慢查詢日志
        二進制日志
        中繼日志
        DDL 日志

大家好！我是只談技術不剪發(fā)的 Tony 老師。今天我們來談談 MySQL 服務器中各種日志文件的作用以及相關配置選項。

MySQL 提供了一套完整的服務器日志體系，包含了多種日志類型，可以幫助我們查找、解決系統(tǒng)問題和優(yōu)化數據庫的性能。


默認情況下，服務器的日志文件都位于數據目錄（datadir）中。
錯誤日志

MySQL 錯誤日志（error log）記錄了 mysqld 啟動和停止的相關信息，同時還記錄了服務器在啟動、停止以及運行期間發(fā)生的診斷消息，例如錯誤、警告和通知等。例如，當 mysqld 發(fā)現某個表需要執(zhí)行自動檢查或修復時，它會向錯誤日志中寫入一條消息。

默認安裝時，錯誤日志通常在配置文件（my.cnf 或者 my.ini）中 mysqld 部分的 log-error 配置項進行設置；利用 MySQL 系統(tǒng)變量 log_error 可以查看錯誤日志的文件名：

mysql> show variables like 'log_error';
+---------------+---------------------+
| Variable_name | Value               |
+---------------+---------------------+
| log_error     | /var/log/mysqld.log |
+---------------+---------------------+
1 row in set (0.01 sec)



如果查詢結果為 stderr，表示標準錯誤輸出，也就是終端或控制臺窗口。

從 MySQL 8.0 開始，錯誤日志采用了 MySQL 組件體系結構，由執(zhí)行日志過濾和寫入的多個組件構成，系統(tǒng)變量 log_error_services 配置了用于生成錯誤日志的組件。當 log_error 系統(tǒng)變量指定了默認的錯誤日志文件之后，日志寫入器組件可能基于該值設置自己的輸出文件，或者使用單獨的輸出文件：

    如果 log_error 設置為 stderr，默認的錯誤日志將會輸出到控制臺；日志寫入器組件 log_sink_internal、log_sink_json 以及 log_sink_test 都會輸出到控制臺，但是 log_sink_syseventlog 的輸出目標為系統(tǒng)日志文件，而不是 log_error。
    如果 log_error 設置為系統(tǒng)文件，默認的錯誤日志將會輸出到該文件；日志寫入器組件 log_sink_internal 和 log_sink_test 都會輸出到該文件；log_error_services 中配置的多個 log_sink_json 將會使用文件名加上帶編號的 .NN.json 文件：file_name.00.json、file_name.01.json 等；log_sink_syseventlog 的輸出目標為系統(tǒng)日志文件，而不是 log_error。

    ??MySQL 錯誤日志是日常監(jiān)控和服務器出現故障時首先應該查看的日志文件。

通用查詢日志

MySQL 通用查詢日志（general query log）記錄了 mysqld 執(zhí)行的各種操作，當客戶端連接或斷開連接、每次提交 SQL 語句時都會記錄相關的信息。當我們懷疑客戶端存在問題或者想要查看客戶端發(fā)送給服務器的確切請求時，通用查詢日志將會非常有用。

默認情況下，通用查詢日志沒有啟用；我們可以通過系統(tǒng)變量 general_log 查看和控制通用查詢日志的設置：

mysql> show variables like 'general_log%';
+------------------+----------------------------+
| Variable_name    | Value                      |
+------------------+----------------------------+
| general_log      | OFF                        |
| general_log_file | /var/lib/mysql/sqlhost.log |
+------------------+----------------------------+
2 rows in set (0.01 sec)



查詢結果中的 general_log_file 是通用查詢日志的文件名，默認為數據目錄下的 host_name.log。

通用查詢日志和慢查詢日志既可以寫入日志文件，也可以寫入系統(tǒng)數據庫中的日志表中；通過系統(tǒng)變量 log_output 可以查看和設置：

mysql> show variables like 'log_output';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_output    | FILE  |
+---------------+-------+
1 row in set (0.01 sec)



FILE 表示輸出到日志文件，TABLE 表示輸出到日志表（分別為 mysql.general_log 和 mysql.slow_log）中，也可以設置為 TABLE, FILE；NONE 表示禁用查詢日志和慢查詢日志。

另外，我們還可以通過系統(tǒng)變量 sql_log_off 設置當前會話的通用查詢日志狀態(tài)，當前前提是已經啟用了系統(tǒng)的通用查詢日志功能：

mysql> show variables like 'sql_log_off';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| sql_log_off   | OFF   |
+---------------+-------+
1 row in set (0.01 sec)



OFF 表示啟用日志功能；如果想要關閉當前會話的通用查詢日志，可以將其設置為 ON。

    ??MySQL 通用查詢日志和慢查詢日志支持運行時的動態(tài)設置，包括啟用、禁用、修改日志文件名和輸出目標等，具體可以參考官方文檔。

慢查詢日志

MySQL 慢查詢日志（slow query log）記錄了執(zhí)行時間超過指定閾值的 SQL 語句，可以用于監(jiān)控需要執(zhí)行優(yōu)化的查詢語句。如果啟用了慢查詢日志，任何執(zhí)行時間超過 long_query_time 并且執(zhí)行次數達到 min_examined_row_limit 次的查詢語句都會被記錄到慢查詢日志中：

mysql> show variables like 'long_query_time';
+-----------------+-----------+
| Variable_name   | Value     |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+
1 row in set (0.00 sec)

mysql> show variables like 'min_examined_row_limit';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| min_examined_row_limit | 0     |
+------------------------+-------+
1 row in set (0.01 sec)



默認情況下，執(zhí)行時間超過 10 秒的任何查詢都會被看作慢查詢。

慢查詢日志的啟用和禁用由系統(tǒng)變量 slow_query_log 進行控制，默認禁用該功能：

mysql> show variables like 'slow_query_log%';
+---------------------+---------------------------------+
| Variable_name       | Value                           |
+---------------------+---------------------------------+
| slow_query_log      | OFF                             |
| slow_query_log_file | /var/lib/mysql/sqlhost-slow.log |
+---------------------+---------------------------------+
2 rows in set (0.01 sec)


查詢結果中的 slow_query_log_file 是慢查詢日志的文件名，默認為數據目錄下的 host_name-slow.log。

與通用查詢日志類似，慢查詢日志既可以寫入日志文件，也可以寫入系統(tǒng)數據庫中的日志表（mysql.slow_log）中；通過系統(tǒng)變量 log_output 進行設置，參考上文中的通用查詢日志介紹。

默認情況下，管理語句不會被記錄到慢查詢日志，包括 ALTER TABLE、ANALYZE TABLE、CHECK TABLE、CREATE INDEX、DROP INDEX、OPTIMIZE TABLE 以及 REPAIR TABLE。如果想要記錄這些操作，可以將 log_slow_admin_statements 設置為 ON：

mysql> show variables like 'log_slow_admin_statements';
+---------------------------+-------+
| Variable_name             | Value |
+---------------------------+-------+
| log_slow_admin_statements | OFF   |
+---------------------------+-------+
1 row in set (0.00 sec)



另外，不使用索引進行查找的查詢也不會被記錄到慢查詢日志。如果想要記錄這些查詢，可以將 log_queries_not_using_indexes 設置為 ON：

mysql> show variables like 'log_queries_not_using_indexes';
+-------------------------------+-------+
| Variable_name                 | Value |
+-------------------------------+-------+
| log_queries_not_using_indexes | OFF   |
+-------------------------------+-------+
1 row in set (0.00 sec)


與此相關的另一個系統(tǒng)變量是 log_throttle_queries_not_using_indexes；如果啟用了 log_queries_not_using_indexes，每分鐘內執(zhí)行次數超過 log_throttle_queries_not_using_indexes 的查詢才會在慢查詢日志中記錄一條匯總信息；該參數的默認值為 0。

    ??MySQL 通用查詢日志和慢查詢日志支持運行時的動態(tài)設置，包括啟用、禁用、修改日志文件名和輸出目標等，具體可以參考官方文檔。

對于慢查詢日志的分析，推薦使用 mysqldumpslow 或者 pt-query-digest 工具。
二進制日志

MySQL 二進制日志（binary log）存儲了描述數據庫變更的事件信息，例如創(chuàng)建表或者修改數據；即使該語句實際上可能并沒有修改任何數據，例如沒有匹配到任何數據行的 DELETE 語句。不過它不會記錄查詢語句，例如 SELECT 或者 SHOW，因為它們不會修改數據。如果想要記錄所有的語句（例如為了找出存在問題的查詢）可以使用上文中的通用查詢日志。

二進制日志同時還包含了更新語句所消耗的時間。二進制日志為我們提供了兩個重要的功能：

    用于復制。主節(jié)點上的二進制日志存儲了數據的變更記錄并發(fā)送給從節(jié)點，然后在從節(jié)點上回放日志實現相同的操作。
    用于數據恢復。通過備份文件執(zhí)行還原之后，利用二進制日志可以將數據庫恢復到指定時間點或者最新狀態(tài)。

啟用二進制日志會給性能稍微帶來一些影響，但是它對復制的支持和數據的還原功能帶來的優(yōu)勢更加明顯。二進制日志不會受到系統(tǒng)故障的影響，因為它只記錄和回讀完整的事件和事務。

接下來我們介紹與二進制相關的幾個主要系統(tǒng)變量，完整的參數可以參考官方文檔：

mysql> show variables like '%log_bin%';
+---------------------------------+-----------------------------+
| Variable_name                   | Value                       |
+---------------------------------+-----------------------------+
| log_bin                         | ON                          |
| log_bin_basename                | /var/lib/mysql/binlog       |
| log_bin_index                   | /var/lib/mysql/binlog.index |
| log_bin_trust_function_creators | OFF                         |
| log_bin_use_v1_row_events       | OFF                         |
| sql_log_bin                     | ON                          |
+---------------------------------+-----------------------------+
6 rows in set (0.01 sec)



在 MySQL 5.7 以及之前的版本中，默認沒有啟用二進制日志；如果想要啟用，可以指定 --log-bin 選項。

從 MySQL 8.0 開始，無論是否指定 --log-bin 選項，默認開啟二進制日志（系統(tǒng)變量 log_bin 設置為 ON）。唯一的例外是手動執(zhí)行帶 --initialize 或者 --initialize-insecure 選項的 mysqld 命令初始化數據目錄，此時默認不會啟用二進制日志；當然也可以通過 --log-bin 選項啟用二進制日志。

    ??如果想要禁用二進制日志，可以指定 --skip-log-bin 或者 --disable-log-bin 啟動選項。

系統(tǒng)變量 log_bin_basename 記錄了二進制日志文件的路徑和文件名，可以使用 --log-bin 選項進行設置。對于 MySQL 8.0，如果沒有指定 --log-bin，默認的文件名為 binlog。對于 MySQL 5.7，默認的文件名為 host_name-bin。

[root@sqlhost ~]# ls /var/lib/mysql/binlog.* -1
/var/lib/mysql/binlog.000059
/var/lib/mysql/binlog.000060
/var/lib/mysql/binlog.000061
/var/lib/mysql/binlog.000062
/var/lib/mysql/binlog.000063
/var/lib/mysql/binlog.000064
/var/lib/mysql/binlog.000065
/var/lib/mysql/binlog.000066
/var/lib/mysql/binlog.000067
/var/lib/mysql/binlog.index



從文件系統(tǒng)可以看出，MySQL 在創(chuàng)建二進制日志文件時增加了一個數字擴展名。每次創(chuàng)建一個新的日志文件時，擴展名中的數字編號都會加 1。服務器會在以下情況創(chuàng)建一個新的日志文件：

    服務器進程啟動或者重啟時；
    服務器刷新日志；
    當前日志文件的大小到達 max_binlog_size 參數的限制。

為了追蹤已經使用的二進制日志文件，MySQL 還創(chuàng)建了一個二進制日志索引文件。默認的索引文件和二進制日志文件名相同，后綴為“.index”。索引文件名可以通過 --log-bin-index 選項進行設置，但是千萬不要在服務器運行時修改索引文件的內容。

如果客戶端擁有設置 RESTRICTED SESSION 系統(tǒng)變量的權限，可以通過 SET sql_log_bin=OFF 語句禁用當前會話的二進制日志功能。系統(tǒng)變量 sql_log_bin 顯示了當前的設置，默認為 ON。

日志文件中的事件格式取決于二進制日志的格式，可以通過 binlog_format 查看和設置 ：

    基于語句的日志格式（statement-based logging）。以 SQL 語句為基礎記錄數據庫的變更，MySQL 中的復制功能最初就是基于語句從主節(jié)點到從節(jié)點的傳播。這種方式需要記錄的日志量少，但是對于不確定性的函數（例如 now()）無法保證主從節(jié)點的一致性。通過 --binlog-format=STATEMENT 啟動選項可以設置為該模式。
    基于語句的日志格式（row-based logging），默認設置。日志中記錄了每一行數據庫的修改信息；如果一個 UPDATE 語句修改了 100 條記錄，需要記錄 100 條修改信息，所以可能產生大量的日志。通過 --binlog-format=ROW 啟動選項可以設置為該模式。
    混合日志格式（mixed logging）。默認使用基于語句的日志格式，對于不確定性的語句切換為基于行的日志格式。通過 --binlog-format=MIXED 啟動選項可以設置為該模式。

關于二進制日志格式的詳細介紹，可以參考 MySQL 官方文檔。

MySQL 提供了一個實用工具 mysqlbinlog ，可以用于讀取二進制日志文件中的內容。mysqlbinlog 可以通過重新執(zhí)行日志文件中的語句實現恢復操作：

shell> mysqlbinlog log_file | mysql -h server_name



默認情況下，每次提交事務都會將二進制日志同步到磁盤；該行為由系統(tǒng)參數 sync_binlog 控制，默認值為 1；此時不會丟失已經提交的事務，但是會對性能產生一定的影響。如果禁用 sync_binlog（設置為 0），操作系統(tǒng)或者服務器故障可能導致日志中丟失一些已經提交的語句；將 sync_binlog 設置為 N 意味著 N 次事務提交同步一次磁盤，這是一種性能和數據丟失風險的折衷。

對于 InnoDB 存儲引擎，為了實現事務的持久性和故障恢復等功能，還使用了另外兩個事務日志：重做日志（redo log）和回滾日志（undo log）。

    ??MySQL 8.0.20 提供了二進制日志事務壓縮功能，可以節(jié)省日志的存儲空間，包括從節(jié)點中繼日志以及日志備份的存儲空間；而且可以節(jié)省主從節(jié)點之間發(fā)送日志時的網絡帶寬。

中繼日志

MySQL 中繼日志（relay log）只存在于主從復制結構中的從節(jié)點上，用于保存主節(jié)點傳輸過來的數據變更事件，然后將這些事件應用于從節(jié)點。

中繼日志與二進制日志類似，也是由一組帶編號的文件組成；同時還包含了一個索引文件，記錄了所有已經使用過的中繼日志文件。中繼日志文件默認存放在數據目錄中。

mysql> show variables like 'relay_log%';
+---------------------------+----------------------------------------+
| Variable_name             | Value                                  |
+---------------------------+----------------------------------------+
| relay_log                 | sqlhost-relay-bin                      |
| relay_log_basename        | /var/lib/mysql/sqlhost-relay-bin       |
| relay_log_index           | /var/lib/mysql/sqlhost-relay-bin.index |
| relay_log_info_file       | relay-log.info                         |
| relay_log_info_repository | TABLE                                  |
| relay_log_purge           | ON                                     |
| relay_log_recovery        | OFF                                    |
| relay_log_space_limit     | 0                                      |
+---------------------------+----------------------------------------+
8 rows in set (0.01 sec)



其中，relay_log 和 relay_log_index 分別對應為中繼日志的文件名和索引文件名。

中繼日志文件的格式和二進制日志文件的格式相同，因此也可以使用 mysqlbinlog 工具進行讀取。

    ??關于主從復制中的中繼日志信息，可以參考官方文檔。

DDL 日志

MySQL DDL 日志，也稱為元數據日志（metadata log）記錄了數據定義語言執(zhí)行的元數據操作，例如 DROP TABLE 或者 ALTER TABLE。

如果元數據操作的過程中發(fā)生故障，MySQL 使用該日志進行恢復。當我們執(zhí)行 DROP TABLE t1, t2 命令時，需要確保 t1 和 t2 一起被刪除 ，而且每個表都被完整地刪除。另一個示例是 ALTER TABLE t3 DROP PARTITION p2 語句，必須確保分區(qū)被完整刪除并且分區(qū)的定義從t3 的分區(qū)列表中刪除。

對于 MySQL 5.7 以及之前的版本，元數據操作的記錄日志位于數據目錄下的 ddl_log.log 文件。該文件是一個二進制文件，不支持手動修改，也沒有相關的配置選項和變量。

對于 MySQL 8.0，DDL 日志存儲在 mysql.innodb_ddl_log 數據字典中。這是一個隱藏的數據字典表，不支持直接查詢；可以通過設置系統(tǒng)變量 innodb_print_ddl_logs （默認為 OFF）將 DDL 日志打印到標準錯誤輸出 strderr。

如果覺得文章對你有用，請不要白嫖！歡迎關注??、評論??、點贊??！