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

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

Tags:

VirMach 便宜 VPS

QNews

QNews