【 1. 日志的基本原理 】

• 任何一種數據庫，都會擁有各種各樣的 日志 ，用來 記錄數據庫的運行情況、日常操作和錯誤等信息，分析這些日志，可以幫助我們了解 MySQL 數據庫的運行情況、日常操作、錯誤信息和哪些地方需要進行優化。
  例如，當用戶 root 登錄到 MySQL 服務器后，就會在日志文件里記錄該用戶的登錄事件、執行操作等信息。當 MySQL 服務器運行時出錯，出錯信息就會被記錄到日志文件里。
• MySQL 也有不同類型的日志文件，各自存儲了不同類型的日志。
  MySQL中4 種日志：
  • 在 MySQL 所支持的日志文件里，除了二進制日志文件外，其它日志文件都是文本文件。
  • 默認情況下 MySQL 只會啟動錯誤日志文件，而 其它日志則需要手動啟動。

日志類型	作用
二進制日志	該日志文件會以 二進制的形式記錄數據庫的各種操作，但不記錄查詢語句。
錯誤日志	該日志文件會記錄 MySQL 服務器的啟動、關閉和運行錯誤 等信息。
通用查詢日志	該日志記錄 MySQL 服務器的啟動和關閉信息、客戶端的連接信息、更新、查詢數據記錄的 SQL 語句 等。
慢查詢日志	記錄執行事件超過指定時間的操作，通過工具分析慢查詢日志可以定位 MySQL 服務器性能瓶頸所在。

• 日志的缺點
  • 啟動日志后，雖然可以對 MySQL 服務器性能進行維護，但是會 降低 MySQL 的執行速度。
    例如，一個查詢操作比較頻繁的 MySQL 中，記錄通用查詢日志和慢查詢日志要花費很多的時間。
  • 日志文件還會 占用大量的硬盤空間。對于用戶量非常大、操作非常頻繁的數據庫，日志文件需要的存儲空間甚至比數據庫文件需要的存儲空間還要大。

【 2. 錯誤日志 Error Log 】

• 錯誤日志（Error Log） 是 MySQL 中最常用的一種日志，主要記錄 MySQL 服務器啟動和停止過程中的信息、服務器在運行過程中發生的故障和異常情況 等。
• 錯誤日志中記錄的并非全是錯誤信息 ，例如 MySQL 如何啟動 InnoDB 的表空間文件、如何初始化自己的存儲引擎等，這些也記錄在錯誤日志文件中。

2.1 啟動和設置錯誤日志

• 在 MySQL 數據庫中，默認開啟錯誤日志功能 。一般情況下，錯誤日志存儲在 MySQL 數據庫的data 數據文件夾下，通常名稱為 hostname.err，其中 hostname 表示 MySQL 服務器的主機名。
• 在 MySQL 配置文件中（在Linux上通常是 my.cnf ，在Windows上是 my.ini ，可以用記事本打開），錯誤日志所記錄的信息可以通過 log-error 和 log-warnings 來定義：
  • log-err 定義 是否啟用錯誤日志功能和錯誤日志的存儲位置。
  • log-warnings 定義是否將警告信息也記錄到錯誤日志中。
• 將 log_error 選項加入到 MySQL 配置文件的 [mysqld] 組中，基本語法：
  • dir 參數指定錯誤日志的存儲路徑；
  • filename 參數指定錯誤日志的文件名；
  • 省略參數時文件名默認為主機名即 hostname.err，存放在 Data 目錄中。
  • 重啟 MySQL 服務后，參數開始生效，可以在指定路徑下看到 filename.err 的文件 。

log-error=dir/{filename}

2.2 查看錯誤日志

• 應用背景
  MySQL 服務器發生異常時，管理員可以 在錯誤日志中找到發生異常的時間、原因，然后根據這些信息來解決異常。
• MySQL 中，SHOW 命令可以 查看錯誤日志文件所在的目錄及文件名信息。
  例如下面的結果顯示我的錯誤日志文件在MySQL安裝路徑的DATA下。

SHOW VARIABLES LIKE 'log_error';

• 錯誤日志以文本文件的形式存儲，直接通過記事本查看：
  

2.3 刪除錯誤日志

• 可以在 cmd 命令行 中使用 mysqladmin 命令來開啟新的錯誤日志，以 保證 MySQL 服務器上的硬盤空間。
  基本語法：
  • 執行該命令后，MySQL 服務器首先會 自動創建一個新的錯誤日志，然后 將舊的錯誤日志更名為 filename.err-old。

mysqladmin -uroot -p flush-logs

【 3. 二進制日志 Binary Log 】

• 二進制日志（Binary Log） 也可叫作 變更日志（Update Log） ，主要用于 記錄數據庫的變化情況，即 SQL 語句的 DDL 和 DML 語句，不包含數據記錄查詢操作。
• 應用背景
  如果 MySQL 數據庫意外停止，可以通過二進制日志文件來查看用戶執行了哪些操作，對數據庫服務器文件做了哪些修改，然后根據二進制日志文件中的記錄來恢復數據庫服務器。
• 默認情況下，二進制日志功能是關閉的。 可以通過以下命令查看二進制日志是否開啟，基本語法：

SHOW VARIABLES LIKE 'log_bin';

3.1 啟動和設置二進制日志

• 在 MySQL 中，可以通過 在配置文件的[mysqld]組中添加 log-bin 選項來開啟二進制日志。
  基本語法：
  • dir 參數指定二進制文件的存儲路徑；
  • filename 參數指定二進制文件的文件名，其形式為 filename.number，number 的形式為 000001、000002 等。
  • 如果沒有 dir 和 filename 參數，二進制日志將默認存儲在數據庫的data數據目錄下，默認的文件名為 hostname-bin.number，其中 hostname 表示主機名。
    比如我的是 PROMETHUS-BIN
  • 每次重啟 MySQL 服務后，都會生成一個新的二進制日志文件，這些日志文件的文件名中 filename 部分不會改變，number 會不斷遞增。

log-bin=dir/[filename]

3.2 查看二進制日志

• 查看二進制日志文件列表：

SHOW binary logs;

• 查看當前正在寫入的二進制日志文件：

SHOW master status;

• 查看二進制日志文件內容
  二進制日志使用二進制格式存儲，不能直接打開查看 。如果需要查看二進制日志，必須使用 mysqlbinlog 命令。
  • mysqlbinlog 命令只在當前文件夾下查找指定的二進制日志，因此需要在二進制日志所在的目錄下運行該命令，否則將會找不到指定的二進制日志文件。

mysqlbinlog filename.number

• 實例
  使用 mysqlbinlog 命令，來查看 指定目錄下的 mylog.000001 文件
  mysqlbinlog "F:\ProgramData\MySQL\MySQL Server 8.4\Data\PROMETHUS-bin.000001"
  
• 除了 filename.number 文件，MySQL 還會生成一個名為 filename.index 的文件，這個文件 存儲著所有二進制日志文件的列表，可以用記事本打開該文件。
  例如我的是PROMETHUS-bin.index：
  

3.3 刪除二進制文件

• 二進制日志中記錄著大量的信息，如果很長時間 不清理二進制日志，將會浪費很多的磁盤空間。

刪除所有二進制日志

• 登錄 MySQL 數據庫后，可以執行該語句來刪除所有二進制日志。刪除所有二進制日志后，MySQL 將會重新創建新的二進制日志，新二進制日志的編號從 000001 開始。

RESET MASTER;

刪除小于指定編號的二進制日志

• 每個二進制日志文件后面有一個 6 位數的編號，如 000001。使用 PURGE MASTER LOGS TO 語句，可以 刪除指定二進制日志的編號之前的日志，基本語法：
  • 該語句將刪除編號小于 filename.number 的所有二進制日志。

PURGE MASTER LOGS TO 'filename.number';

刪除創建時間之前的二進制日志

• 使用 PURGE MASTER LOGS TO 語句，可以 刪除指定時間之前創建的二進制日志，基本語法：
  • 其中，“hh”為 24 制的小時。該語句將刪除在指定時間之前創建的所有二進制日志。

PURGE MASTER LOGS TO 'yyyy-mm-dd hh:MM:ss';

3.4 暫時停止二進制日志

• 問題背景
  在配置文件中設置了 log_bin 選項之后，MySQL 服務器將會一直開啟二進制日志功能；刪除該選項后就可以停止二進制日志功能，如果需要再次啟動這個功能，需要重新添加 log_bin 選項；由于這樣比較麻煩，所以 MySQL 提供了暫時停止二進制日志功能的語句。如果用戶不希望自己執行的某些 SQL 語句記錄在二進制日志中，可以在執行這些 SQL 語句之前暫停二進制日志功能。
• 使用 SET 語句來暫停/開啟二進制日志功能，基本語法：
  • 0 表示暫停二進制日志功能，1 表示開啟二進制功能。

SET SQL_LOG_BIN=0/1;

3.5 二進制日志的其他設置

• my.ini 中的 [mysqld] 組下面有幾個設置參數是關于二進制日志的，這里以expire_logs_day 和 max_binlog_size 為例講述。
• expire_logs_day 定義了 MySQL 清除過期日志的時間、二進制日志自動刪除的天數。默認值為 0，表示“沒有自動刪除”，當 MySQL 啟動或刷新二進制日志時可能刪除。

expire_logs_days = 10

• max_binlog_size 定義了 單個文件的大小限制，如果二進制日志寫入的內容大小超出給定值，日志就會發生滾動（關閉當前文件，重新打開一個新的日志文件）。不能將該變量設置為大于 1GB 或小于 4096B（字節），其默認值是 1GB。

max_binlog_size = 1?00M

3.6 使用二進制日志還原數據庫

• 二進制日志中記錄了用戶對數據庫更改的所有操作，如 INSERT 語句、UPDATE 語句、CREATE 語句等。如果數據庫因為操作不當或其它原因丟失了數據，可以通過二進制日志來查看在一定時間段內用戶的操作，結合數據庫備份來還原數據庫。
• 數據庫恢復的流程
  數據庫遭到意外損壞時，應該 先使用最近的備份文件來還原數據庫；另外備份之后，數據庫可能進行了一些更新，這時可以 使用二進制日志來還原。
• 還原數據庫的基本語法：
  • 先使用 mysqlbinlog 命令來讀取 filename.number 中的內容，再使用 mysql 命令將這些內容還原到數據庫中。
  • 使用 mysqlbinlog 命令進行還原操作時，必須是編號（number）小的先還原。例如，mylog.000001 必須在 mylog.000002 之前還原。

mysqlbinlog filename.number | mysql -u root -p

【 4. 通用查詢日志 General Query Log 】

• 通用查詢日志（General Query Log） 用來 記錄用戶的所有操作，包括啟動和關閉 MySQL 服務、更新語句和查詢語句等。
• 默認情況下，通用查詢日志功能是關閉的，可以通過以下命令查看通用查詢日志是否開啟：
  結果中的 general_log_file 變量指定了通用查詢日志文件所在的位置。

SHOW VARIABLES LIKE '%general%';

4.1 啟動和設置通用查詢日志

• 在 MySQL 中，可以通過 在配置文件的[mysqld]組中添加 log 選項來開啟通用查詢日志，格式如下：
  • dir 參數指定通用查詢日志的存儲路徑；
  • filename 參數指定日志的文件名。如果不指定存儲路徑，通用查詢日志將默認存儲到 MySQL 數據庫的數據文件夾下。如果不指定文件名，默認文件名為 hostname.log，其中 hostname 表示主機名。

log=dir/filename

4.2 查看通用查詢日志

• 如果希望了解用戶最近的操作，可以查看通用查詢日志。通用查詢日志以文本文件的形式存儲，可以 直接使用記事本打開通用查詢日志。

4.3 停止通用查詢日志

• 將 MySQL 配置文件中的相關配置注釋掉，然后重啟服務器，來停止通用查詢日志，如下所示：

#log=dir\filename

• 設置 MySQL 的環境變量 general_log 為關閉狀態 也可以停止該日志，示例如下：

SET GLOBAL general_log=off;

4.4 刪除通用查詢日志

• 問題背景
  由于通用查詢日志會記錄用戶的所有操作，如果數據庫的使用非常頻繁，通用查詢日志將會占用非常大的磁盤空間，對系統性能影響較大。所以在一般情況下，數據管理員可以刪除很長時間之前的通用查詢日志或關閉此日志，以保證 MySQL 服務器上的硬盤空間。
• 在 MySQL 中，可以使用 mysqladmin 命令來開啟新的通用查詢日志，新的通用查詢日志會直接覆蓋舊的查詢日志，不需要再手動刪除了。
  基本語法：
  • 如果希望備份舊的通用查詢日志，必須先將舊的日志文件拷貝出來或者改名。然后，再執行 mysqladmin 命令。

mysqladmin -uroot -p flush-logs

• 除了上述方法之外，還可以 手動刪除通用查詢日志，刪除之后需要重新啟動 MySQL 服務，重啟之后就會生成新的通用查詢日志。
  如果希望備份舊的日志文件，可以將舊的日志文件改名，然后重啟 MySQL 服務。

【 5. 慢查詢日志 Slow Query Log 】

• 慢查詢日志（Slow Query Log） 用來 記錄在 MySQL 中執行時間超過指定時間的查詢語句。通過慢查詢日志，可以查找出哪些查詢語句的執行效率低，以便進行優化。 通俗的說，MySQL 慢查詢日志是排查問題的 SQL 語句，以及檢查當前 MySQL 性能的一個重要功能。如果不是調優需要，一般不建議啟動該參數，因為開啟慢查詢日志會或多或少帶來一定的性能影響。
• 默認情況下，慢查詢日志功能是關閉的。 可以通過以下命令查看是否開啟慢查詢日志功能。
  • slow_query_log：慢查詢開啟狀態
  • slow_query_log_file：慢查詢日志存放的位置（一般設置為 MySQL 的數據存放目錄）
  • long_query_time：查詢超過多少秒才記錄

SHOW VARIABLES LIKE 'slow_query%';

5.1 啟動和設置慢查詢日志

• 可以通過 log-slow-queries 選項開啟慢查詢日志。通過 long_query_time 選項來 設置時間值，時間以秒為單位，如果查詢時間超過了這個時間值，這個查詢語句將被記錄到慢查詢日志 。將 log_slow_queries 選項和 long_query_time 選項加入到配置文件的 [mysqld] 組中。格式如下：
  • dir 參數指定慢查詢日志的存儲路徑，如果不指定存儲路徑，慢查詢日志將默認存儲到 MySQL 數據庫的數據文件夾下。
  • filename 參數指定日志的文件名，生成日志文件的完整名稱為 filename-slow.log。 如果不指定文件名，默認文件名為 hostname-slow.log，hostname 是 MySQL 服務器的主機名。
  • n 參數是設定的時間值，該值的單位是秒。如果不設置 long_query_time 選項，默認時間為 10 秒。

log-slow-queries=dir\filename
long_query_time=n

• 還可以通過以下命令啟動慢查詢日志、設置指定時間：

SET GLOBAL slow_query_log=ON/OFF;
SET GLOBAL long_query_time=n;

5.2 查看慢查詢日志

• 如果想查看哪些查詢語句的執行效率低，可以從慢查詢日志中獲得信息。和錯誤日志、查詢日志一樣，慢查詢日志也是以文本文件的形式存儲的，可以 直接通過記事本打開查看。

5.3 刪除慢查詢日志

• 慢查詢日志的刪除方法與通用日志的刪除方法是一樣的，可以使用 mysqladmin 命令來刪除。mysqladmin 命令的語法如下：
  • 執行該命令后，命令行會提示輸入密碼；輸入正確密碼后，將執行刪除操作。新的慢查詢日志會直接覆蓋舊的查詢日志，不需要再手動刪除。
  • 通用查詢日志和慢查詢日志都是使用這個命令，使用時一定要注意，一旦執行這個命令，通用查詢日志和慢查詢日志都只存在新的日志文件中。
    如果需要備份舊的慢查詢日志文件，必須先將舊的日志改名，然后重啟 MySQL 服務或執行 mysqladmin 命令。

mysqladmin -uroot -p flush-logs

• 數據庫管理員也可以 手動刪除慢查詢日志，刪除之后需要重新啟動 MySQL 服務。