MySQL 服務(wù)器中的 6 種日志類型

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


文章目錄

        錯(cuò)誤日志
        通用查詢?nèi)罩?br>        慢查詢?nèi)罩?br>        二進(jìn)制日志
        中繼日志
        DDL 日志

大家好！我是只談技術(shù)不剪發(fā)的 Tony 老師。今天我們來(lái)談?wù)?MySQL 服務(wù)器中各種日志文件的作用以及相關(guān)配置選項(xiàng)。

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


默認(rèn)情況下，服務(wù)器的日志文件都位于數(shù)據(jù)目錄（datadir）中。
錯(cuò)誤日志

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

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

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



如果查詢結(jié)果為 stderr，表示標(biāo)準(zhǔn)錯(cuò)誤輸出，也就是終端或控制臺(tái)窗口。

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

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

    ??MySQL 錯(cuò)誤日志是日常監(jiān)控和服務(wù)器出現(xiàn)故障時(shí)首先應(yīng)該查看的日志文件。

通用查詢?nèi)罩?br>
MySQL 通用查詢?nèi)罩荆╣eneral query log）記錄了 mysqld 執(zhí)行的各種操作，當(dāng)客戶端連接或斷開連接、每次提交 SQL 語(yǔ)句時(shí)都會(huì)記錄相關(guān)的信息。當(dāng)我們懷疑客戶端存在問題或者想要查看客戶端發(fā)送給服務(wù)器的確切請(qǐng)求時(shí)，通用查詢?nèi)罩緦?huì)非常有用。

默認(rèn)情況下，通用查詢?nèi)罩緵]有啟用；我們可以通過系統(tǒng)變量 general_log 查看和控制通用查詢?nèi)罩镜脑O(shè)置：

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)



查詢結(jié)果中的 general_log_file 是通用查詢?nèi)罩镜奈募?，默認(rèn)為數(shù)據(jù)目錄下的 host_name.log。

通用查詢?nèi)罩竞吐樵內(nèi)罩炯瓤梢詫懭肴罩疚募?，也可以寫入系統(tǒng)數(shù)據(jù)庫(kù)中的日志表中；通過系統(tǒng)變量 log_output 可以查看和設(shè)置：

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）中，也可以設(shè)置為 TABLE, FILE；NONE 表示禁用查詢?nèi)罩竞吐樵內(nèi)罩尽?br>
另外，我們還可以通過系統(tǒng)變量 sql_log_off 設(shè)置當(dāng)前會(huì)話的通用查詢?nèi)罩緺顟B(tài)，當(dāng)前前提是已經(jīng)啟用了系統(tǒng)的通用查詢?nèi)罩竟δ埽?br>
mysql> show variables like 'sql_log_off';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| sql_log_off   | OFF   |
+---------------+-------+
1 row in set (0.01 sec)



OFF 表示啟用日志功能；如果想要關(guān)閉當(dāng)前會(huì)話的通用查詢?nèi)罩荆梢詫⑵湓O(shè)置為 ON。

    ??MySQL 通用查詢?nèi)罩竞吐樵內(nèi)罩局С诌\(yùn)行時(shí)的動(dòng)態(tài)設(shè)置，包括啟用、禁用、修改日志文件名和輸出目標(biāo)等，具體可以參考官方文檔。

慢查詢?nèi)罩?br>
MySQL 慢查詢?nèi)罩荆╯low query log）記錄了執(zhí)行時(shí)間超過指定閾值的 SQL 語(yǔ)句，可以用于監(jiān)控需要執(zhí)行優(yōu)化的查詢語(yǔ)句。如果啟用了慢查詢?nèi)罩?，任何?zhí)行時(shí)間超過 long_query_time 并且執(zhí)行次數(shù)達(dá)到 min_examined_row_limit 次的查詢語(yǔ)句都會(huì)被記錄到慢查詢?nèi)罩局校?br>
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)



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

慢查詢?nèi)罩镜膯⒂煤徒糜上到y(tǒng)變量 slow_query_log 進(jìn)行控制，默認(rèn)禁用該功能：

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)


查詢結(jié)果中的 slow_query_log_file 是慢查詢?nèi)罩镜奈募?，默認(rèn)為數(shù)據(jù)目錄下的 host_name-slow.log。

與通用查詢?nèi)罩绢愃?，慢查詢?nèi)罩炯瓤梢詫懭肴罩疚募?，也可以寫入系統(tǒng)數(shù)據(jù)庫(kù)中的日志表（mysql.slow_log）中；通過系統(tǒng)變量 log_output 進(jìn)行設(shè)置，參考上文中的通用查詢?nèi)罩窘榻B。

默認(rèn)情況下，管理語(yǔ)句不會(huì)被記錄到慢查詢?nèi)罩荆?ALTER TABLE、ANALYZE TABLE、CHECK TABLE、CREATE INDEX、DROP INDEX、OPTIMIZE TABLE 以及 REPAIR TABLE。如果想要記錄這些操作，可以將 log_slow_admin_statements 設(shè)置為 ON：

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



另外，不使用索引進(jìn)行查找的查詢也不會(huì)被記錄到慢查詢?nèi)罩?。如果想要記錄這些查詢，可以將 log_queries_not_using_indexes 設(shè)置為 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)


與此相關(guān)的另一個(gè)系統(tǒng)變量是 log_throttle_queries_not_using_indexes；如果啟用了 log_queries_not_using_indexes，每分鐘內(nèi)執(zhí)行次數(shù)超過 log_throttle_queries_not_using_indexes 的查詢才會(huì)在慢查詢?nèi)罩局杏涗浺粭l匯總信息；該參數(shù)的默認(rèn)值為 0。

    ??MySQL 通用查詢?nèi)罩竞吐樵內(nèi)罩局С诌\(yùn)行時(shí)的動(dòng)態(tài)設(shè)置，包括啟用、禁用、修改日志文件名和輸出目標(biāo)等，具體可以參考官方文檔。

對(duì)于慢查詢?nèi)罩镜姆治?，推薦使用 mysqldumpslow 或者 pt-query-digest 工具。
二進(jìn)制日志

MySQL 二進(jìn)制日志（binary log）存儲(chǔ)了描述數(shù)據(jù)庫(kù)變更的事件信息，例如創(chuàng)建表或者修改數(shù)據(jù)；即使該語(yǔ)句實(shí)際上可能并沒有修改任何數(shù)據(jù)，例如沒有匹配到任何數(shù)據(jù)行的 DELETE 語(yǔ)句。不過它不會(huì)記錄查詢語(yǔ)句，例如 SELECT 或者 SHOW，因?yàn)樗鼈儾粫?huì)修改數(shù)據(jù)。如果想要記錄所有的語(yǔ)句（例如為了找出存在問題的查詢）可以使用上文中的通用查詢?nèi)罩尽?br>
二進(jìn)制日志同時(shí)還包含了更新語(yǔ)句所消耗的時(shí)間。二進(jìn)制日志為我們提供了兩個(gè)重要的功能：

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

啟用二進(jìn)制日志會(huì)給性能稍微帶來(lái)一些影響，但是它對(duì)復(fù)制的支持和數(shù)據(jù)的還原功能帶來(lái)的優(yōu)勢(shì)更加明顯。二進(jìn)制日志不會(huì)受到系統(tǒng)故障的影響，因?yàn)樗挥涗浐突刈x完整的事件和事務(wù)。

接下來(lái)我們介紹與二進(jìn)制相關(guān)的幾個(gè)主要系統(tǒng)變量，完整的參數(shù)可以參考官方文檔：

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 以及之前的版本中，默認(rèn)沒有啟用二進(jìn)制日志；如果想要啟用，可以指定 --log-bin 選項(xiàng)。

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

    ??如果想要禁用二進(jìn)制日志，可以指定 --skip-log-bin 或者 --disable-log-bin 啟動(dòng)選項(xiàng)。

系統(tǒng)變量 log_bin_basename 記錄了二進(jìn)制日志文件的路徑和文件名，可以使用 --log-bin 選項(xiàng)進(jìn)行設(shè)置。對(duì)于 MySQL 8.0，如果沒有指定 --log-bin，默認(rèn)的文件名為 binlog。對(duì)于 MySQL 5.7，默認(rèn)的文件名為 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)建二進(jìn)制日志文件時(shí)增加了一個(gè)數(shù)字?jǐn)U展名。每次創(chuàng)建一個(gè)新的日志文件時(shí)，擴(kuò)展名中的數(shù)字編號(hào)都會(huì)加 1。服務(wù)器會(huì)在以下情況創(chuàng)建一個(gè)新的日志文件：

    服務(wù)器進(jìn)程啟動(dòng)或者重啟時(shí)；
    服務(wù)器刷新日志；
    當(dāng)前日志文件的大小到達(dá) max_binlog_size 參數(shù)的限制。

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

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

日志文件中的事件格式取決于二進(jìn)制日志的格式，可以通過 binlog_format 查看和設(shè)置 ：

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

關(guān)于二進(jìn)制日志格式的詳細(xì)介紹，可以參考 MySQL 官方文檔。

MySQL 提供了一個(gè)實(shí)用工具 mysqlbinlog ，可以用于讀取二進(jìn)制日志文件中的內(nèi)容。mysqlbinlog 可以通過重新執(zhí)行日志文件中的語(yǔ)句實(shí)現(xiàn)恢復(fù)操作：

shell> mysqlbinlog log_file | mysql -h server_name



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

對(duì)于 InnoDB 存儲(chǔ)引擎，為了實(shí)現(xiàn)事務(wù)的持久性和故障恢復(fù)等功能，還使用了另外兩個(gè)事務(wù)日志：重做日志（redo log）和回滾日志（undo log）。

    ??MySQL 8.0.20 提供了二進(jìn)制日志事務(wù)壓縮功能，可以節(jié)省日志的存儲(chǔ)空間，包括從節(jié)點(diǎn)中繼日志以及日志備份的存儲(chǔ)空間；而且可以節(jié)省主從節(jié)點(diǎn)之間發(fā)送日志時(shí)的網(wǎng)絡(luò)帶寬。

中繼日志

MySQL 中繼日志（relay log）只存在于主從復(fù)制結(jié)構(gòu)中的從節(jié)點(diǎn)上，用于保存主節(jié)點(diǎn)傳輸過來(lái)的數(shù)據(jù)變更事件，然后將這些事件應(yīng)用于從節(jié)點(diǎn)。

中繼日志與二進(jìn)制日志類似，也是由一組帶編號(hào)的文件組成；同時(shí)還包含了一個(gè)索引文件，記錄了所有已經(jīng)使用過的中繼日志文件。中繼日志文件默認(rèn)存放在數(shù)據(jù)目錄中。

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 分別對(duì)應(yīng)為中繼日志的文件名和索引文件名。

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

    ??關(guān)于主從復(fù)制中的中繼日志信息，可以參考官方文檔。

DDL 日志

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

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

對(duì)于 MySQL 5.7 以及之前的版本，元數(shù)據(jù)操作的記錄日志位于數(shù)據(jù)目錄下的 ddl_log.log 文件。該文件是一個(gè)二進(jìn)制文件，不支持手動(dòng)修改，也沒有相關(guān)的配置選項(xiàng)和變量。

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

如果覺得文章對(duì)你有用，請(qǐng)不要白嫖！歡迎關(guān)注??、評(píng)論??、點(diǎn)贊??！