您现在的位置：程式碼之家 >> 數據庫知識 > >> MySQL 資料庫 >> MySQL 資料庫相關知識

MySQL 日誌實戰：通用查詢日誌、慢查詢日誌與二進制日誌管理全攻略

編輯：MySQL 資料庫相關知識

前言

MySQL 三大核心日誌——通用查詢日誌、慢查詢日誌、二進制日誌——分別服務於 SQL 審計、效能調優、主從複寫與時間點恢復（PITR）。本文詳細說明各日誌的啟用參數、輸出格式、分析工具、輪轉策略與生產環境最佳實踐。

一、通用查詢日誌

1.1 啟用方式

# my.cnf [mysqld]
general_log=ON
general_log_file=/var/log/mysql/general.log
log_output=FILE  # 或 TABLE (寫入 mysql.general_log 表)

動態開啟（無需重啟）：

SET GLOBAL general_log=ON;
SET GLOBAL general_log_file='/var/log/mysql/general.log';

1.2 適用場景與風險

✅ 審計所有 SQL、排查應用異常行為、安全合規。
⚠️ **極大影響寫入效能**（每條 SQL 同步寫磁碟），**嚴禁長期在生產核心庫開啟**。
建議：僅在排查時段短暫開啟，或配合 log_output=TABLE 定期 TRUNCATE mysql.general_log。

二、慢查詢日誌

2.1 核心參數

# my.cnf [mysqld]
slow_query_log=ON
slow_query_log_file=/var/log/mysql/slow.log
long_query_time=1          # 超過 1 秒記錄（可設浮點數 0.1）
log_queries_not_using_indexes=ON  # 未用索引也記錄
log_slow_admin_statements=ON      # 記錄管理指令
log_slow_slave_statements=ON      # 從庫複寫慢 SQL
min_examined_row_limit=100        # 忽略掃描行數 < 100 的查詢

2.2 分析工具

# pt-query-digest (Percona Toolkit) - 標準分析
pt-query-digest /var/log/mysql/slow.log --limit 20 --filter '$event->{Query_time} > 2'

# mysqldumpslow - 內建簡易統計
mysqldumpslow -s t -t 10 /var/log/mysql/slow.log  # 按總耗時排序 Top10

# 自動化：配合 crontab 每日產生報表
0 1 * * * pt-query-digest /var/log/mysql/slow.log --output=html --report-format=html > /var/www/slow_report_$(date +\%F).html

2.3 慢查詢優化流程

取得 Top SQL → EXPLAIN 分析執行計畫。
檢查 type（ALL/index/range/ref/eq_ref/const）、key、rows、Extra（Using index/Using where/Using temporary/Using filesort）。
優化手段：加索引、改寫 SQL、調整 join_buffer_size、分區表、Summary Table。
驗證：FLUSH SLOW LOGS 後再次觀察。

三、二進制日誌

3.1 核心參數

# my.cnf [mysqld]
log_bin=/var/log/mysql/mysql-bin
binlog_format=ROW          # ROW/STATEMENT/MIXED，推薦 ROW
binlog_expire_logs_seconds=604800  # 7 天自動清理 (MySQL 8.0+)
max_binlog_size=1G
binlog_checksum=CRC32
sync_binlog=1              # 每次提交 fsync，最安全
gtid_mode=ON
enforce_gtid_consistency=ON

3.2 常用管理指令

-- 查看目前 binlog
SHOW MASTER STATUS;
SHOW BINARY LOGS;

-- 刷新產生新 binlog
FLUSH LOGS;  -- 或 mysqladmin flush-logs

-- 刪除指定 binlog 之前的檔案 (慎用)
PURGE BINARY LOGS TO 'mysql-bin.000123';
PURGE BINARY LOGS BEFORE '2024-05-01 00:00:00';

-- 解析 binlog 內容
mysqlbinlog --base64-output=DECODE-ROWS -v /var/log/mysql/mysql-bin.000123 > /tmp/binlog_000123.sql

3.3 時間點恢復（PITR）實戰

# 1. 完整備份時間點：2024-05-20 02:00 (xtrabackup)
# 2. 事故發生：2024-05-20 14:30 誤執行 DROP TABLE
# 3. 恢復步驟
# 3.1 還原完整備份到臨時實例
# 3.2 套用 binlog 從 02:00 到 14:29
mysqlbinlog --start-datetime="2024-05-20 02:00:00" \
            --stop-datetime="2024-05-20 14:29:00" \
            /var/log/mysql/mysql-bin.000123 \
            | mysql -uroot -p -h127.0.0.1 -P3307

四、日誌輪轉與磁碼空間管理

4.1 logrotate 設定

# /etc/logrotate.d/mysql
/var/log/mysql/*.log {
    daily
    missingok
    rotate 14
    compress
    delaycompress
    notifempty
    create 640 mysql mysql
    sharedscripts
    postrotate
        test -x /usr/bin/mysqladmin && /usr/bin/mysqladmin flush-logs >/dev/null 2>&1 || true
    endscript
}

4.2 二進制日誌自動清理

MySQL 5.7：expire_logs_days=7
MySQL 8.0：binlog_expire_logs_seconds=604800（優先級更高）
主從環境**禁止手動 rm binlog**，必須用 PURGE BINARY LOGS 確保從庫已複寫。

五、常見問題速查表

現象	原因	處理
slow.log 暴增佔滿磁碟	`long_query_time` 設太小或全表掃描多	調大閾值、優化 Top SQL、啟用 logrotate
binlog 未自動清理	從庫複寫延遲導致主庫不敢刪除	檢查 `Seconds_Behind_Master`、補足從庫、調大 `expire_logs_days`
`mysqlbinlog` 解析報錯	版本不匹配或 binlog 損壞	使用目標 MySQL 版本自帶的 mysqlbinlog；檢查 `binlog_checksum`
通用日誌寫入阻塞	磁碟 IOPS 耗盡	立即關閉 `general_log`、改用 `audit_plugin` (Percona/MariaDB)

結語： 日誌是 MySQL 可觀測性的基石。生產環境建議：慢查詢日誌永久開啟、二進制日誌 ROW 格式 + GTID 永久開啟、通用查詢日誌按需短時開啟。配合 pt-query-digest、mysqlbinlog、logrotate 自動化，讓日誌從「佔空間的負擔」變成「解決問題的利器」。