Mysql 日誌管理

低吟不作語發表於2022-05-03
MySql

概述

日誌檔案記錄 MySQL 資料庫執行期間發生的變化，當資料庫遭到意外的損害時，可以通過日誌檔案查詢出錯原因，並進件資料恢復

MySQL 日誌檔案可以分成以下幾類：

  • 二進位制日誌：記錄所有更改資料的語句，可以用於主從複製
  • 錯誤日誌：記錄 MySQL 服務出現的問題
  • 查詢日誌：記錄建立的客戶端連線和執行的語句
  • 慢查詢語句：記錄所有執行時間超過 long_query_time 的所有查詢或不適用索引的查詢
  • 中繼日誌：記錄複製時從伺服器從主伺服器收到的資料該表
  • 資料定義語句日誌：記錄資料定義語句執行的後設資料操作

操作二進位制日誌

1. 啟動二進位制日誌

預設情況下，二進位制檔案是關閉的，可以通過以下 SQL 語句來查詢二進位制開關

SHOW VARIABLES LIKE 'log_bin%';

修改 my.cnf 或 my.ini 檔案，以 Windows 系統為例，開啟 MySQL 目錄下的 my.ini 檔案，將 log-bin 加入 [mysqld] 組

[mysqld]
log-bin
server-id=201811
expire_logs_days=10
max_binding_size=100M

重新啟動 MySQL 服務，再執行上述的 SQL 語句，看到 log_bin 變數的值為 ON，表明二進位制日誌已經開啟

如果想改變日誌檔案的目錄和名稱，可以對 my.ini 中的 log_bin 引數修改如下：

[mysqld]
log-bin="d:\mysql\logs"

2. 檢視二進位制日誌

當 MySQL 建立二進位制日誌檔案時，先建立一個 binlog.000001 檔案，以後 MySQL 服務重新啟動一次，以 .000001 為字尾的檔案就會增加一個，字尾名按 1 遞增。如果日誌長度超過了 max_binlog_size 的上限，就會建立一個新的日誌檔案

SHOW BINARY LOGS;

二進位制日誌檔案不能直接檢視，可以使用 mysqlbinlog 命令檢視

mysqlbinlog [日誌檔案路徑]

3. 使用二進位制日誌恢復資料庫

如果 MySQL 伺服器啟用了二進位制日誌，在資料庫出現意外丟失資料時，可以使用 MySQLbinlog 工具從指定的時間點開始恢復資料

mysqlbinlog [option] filename|mysql -uroot -p;
  • option 是一些可選的選項：
    • --start-date：指定恢復資料庫的起始時間點
    • --stop-date：指定恢復資料庫的結束時間點
    • --start-position：指定恢復資料庫的起始位置
    • --stop-position：指定恢復資料庫的結束位置
  • filename 是日誌檔名

4. 暫停二進位制日誌

在配置檔案設定了 log-bin 選項以後，MySQL 服務會一直開啟二進位制日誌功能，刪除該選項就可以停止二進位制日誌功能

MySQL 提供暫時停止二進位制日誌功能的語句，如果使用者不希望自己執行的某些 SQL 語句記錄在二進位制日誌，可以使用 SET 語句來暫停二進位制日誌功能，0 表示停止，1 表示恢復

SET SQL_LOG_BIN={0|1}

5. 刪除二進位制日誌

MySQL 的二進位制檔案可以配置自動刪除，同時 MySQL 也提供手動刪除二進位制檔案的方法

使用 PURGE MASTER LOGS 語句刪除指定日誌檔案

# 刪除建立時間比指定日誌檔案早的日誌檔案,以字尾為判斷標準
PURGE {MASTER|BINARY} LOGS TO 'log_name'
# 刪除指定時間前的日誌檔案
PURGE {MASTER|BINARY} LOGS BEFORE 'date'

使用 RESET MASTER 語句刪除所有二進位制日誌檔案

RESET MASTER

執行完該語句，所有二進位制日誌將被刪除，MySQL 會重新建立二進位制檔案，新的日誌副檔名將重新從 000001 開始


操作錯誤日誌

1. 啟動錯誤日誌

在 MySQL 資料庫中，錯誤日誌是預設開啟的，並且無法被禁止。錯誤日誌檔案的名稱預設為 hostname.err，其中 hostname 表示 MySQL 伺服器的主機名

2. 檢視錯誤日誌

SHOW VARIABLES LIKE 'log_err%';

3. 刪除錯誤日誌

MySQL 的錯誤日誌是以文字形式儲存的，可以直接刪除


通用查詢日誌

1. 啟動通用查詢日誌

MySQL 預設沒有開啟通用查詢日誌，可以通過修改 my.cnf 或 my.ini 配置檔案來設定

[mysqld]
general_log=ON
general_log_file=[path[filename]]

重啟 MySQL，在 MySQL 的 data 目錄下生成新的通用查詢日誌

從 5.1.6 以後開始，MySQL 支援全域性級別的動態修改，不需要重啟

SET GLOBAL general_log=on;
SET GLOBAL general_log=off;
SET GLOBAL general_log_file='path/filename';

2. 檢視通用查詢日誌

SHOW VARIABLES LIKE 'general_log%';

3. 停止通用查詢日誌

MySQL 停止通用查詢日誌功能有兩種方式，一種是在配置檔案把 general_log 值設定為 off，再重啟 MySQL，另一種是使用 SET 語句設定，即動態修改

4. 刪除通用查詢日誌

通用查詢日誌可以手動刪除，也可以使用命令刪除

mysqladmin -uroot -p flush-logs

此種方式新的通用查詢日誌會直接覆蓋舊日誌


慢查詢日誌

1. 啟動慢查詢日誌

MySQL 慢查詢日誌預設是關閉的，可以通過修改 my.cnf 或 my.ini 配置檔案來設定

[mysqld]
long_query_time=n
slow_query_log=ON
slow_query_log_file=[path[filename]]
  • long_query_time 設定慢查詢的閾值，超出此設定值的 SQL 都會被記錄，預設為 10 秒

MySQL 也支援 SET 語句開啟

set global slow_query_log=ON;
set global long_query_time=2;
set session long_query_time=2;

2. 檢視慢查詢日誌

檢視慢查詢日誌所在目錄

SHOW VARIABLES LIKE '%slow_query_log_file%';

檢視慢查詢日誌的超時時長

SHOW VARIABLES LIKE '%long_query_time%';

3. 停止慢查詢日誌

可以在配置檔案把 slow_query_log 的值設為 OFF，重啟 MySQL，也可以使用 SET 語句

4. 刪除慢查詢日誌

慢查詢日誌可以手動刪除，也可以使用命令刪除

mysqladmin -uroot -p flush-logs

此種方式新的慢查詢日誌會直接覆蓋舊日誌


相關文章