MySQL 日志體系深度解析 (一) : 錯誤、查詢、慢查詢、二進制、中繼日志全景

MySQL 日志體系深度解析 (一) : 錯誤、查詢、慢查詢、二進制、中繼日志全景

MySQL 多種日志各司其職，構成了故障診斷、性能調優、數據恢復、主從復制的基石。本文系統梳理五大核心日志類型的配置、查看、分析、切割實戰。

一、日志總覽與進程級查看

# 查看 mysqld 進程打開的日志文件 (排除數據/索引/臨時文件)
lsof -nc mysqld | grep -vE '(\.so|\.frm|\.MY[ID]|\.ibd|ib_logfile|ibdata|TCP)'

# 典型輸出:
# mysqld  26379 mysql  1w   REG  8,2  10234  /data/logs/mysql/mysql_error.log      # 錯誤日志
# mysqld  26379 mysql  3u   REG  8,2    132  /data/logs/mysql/binarylog/mysql_bin.index # Binlog 索引
# mysqld  26379 mysql 11w   REG  8,2   2618  /data/logs/mysql/slow_query.log        # 慢查詢日志
# mysqld  26379 mysql 13w   REG  8,2   8059  /data/logs/mysql/general_log.log       # 通用查詢日志
# mysqld  26379 mysql 15w   REG  8,2    425  /data/logs/mysql/binarylog/mysql_bin.000003 # Binlog 文件

二、錯誤日志 - 核心診斷入口

記錄內容

• 服務啟動/關閉過程信息
• 運行期錯誤、警告、筆記
• 事件調度器執行信息
• 從庫啟動復制進程信息

配置

[mysqld_safe]
log_error=/data/logs/mysql/mysql_error.log

[mysqld]
log_error=/data/logs/mysql/mysql_error.log
log_warnings=2          # 記錄更多警告 (如連接中斷、不安全語句)
# syslog 支持 (系統日志集中化)
# skip-syslog           # 禁用 syslog，改用文件

動態修改：不支持，需重啟。

三、通用查詢日志 - 全量 SQL 審計 (慎用)

特點

• 記錄所有連接、斷開、收到的 SQL 文本 (含 SELECT)
• 極大增加磁盤 I/O，生產環境默認關閉
• 僅調試、審計短時開啟

配置

[mysqld]
general_log=ON
general_log_file=/data/logs/mysql/general_log.log
log_output=FILE         # 或 TABLE (寫入 mysql.general_log 表), NONE

# 動態開關 (無需重啟)
SET GLOBAL general_log = 'ON';
SET GLOBAL general_log = 'OFF';

TABLE 模式遠程審計

SET GLOBAL log_output = 'TABLE';
SET GLOBAL general_log = 'ON';
-- 遠程查詢 mysql.general_log 表，無需登錄服務器
SELECT * FROM mysql.general_log ORDER BY event_time DESC LIMIT 100;

表結構：event_time, user_host, thread_id, server_id, command_type, argument (CSV 引擎)。

四、慢查詢日志 - 性能調優利器 (強烈建議常開)

判定標準

執行時間 (含等待 CPU/IO) > long_query_time (默認 10s，建議 1-2s)。

配置

[mysqld]
slow_query_log=ON
slow_query_log_file=/data/logs/mysql/slow_query.log
long_query_time=1
log_queries_not_using_indexes=ON    # 記錄未用索引的查詢 (即使很快)
log_slow_admin_statements=ON        # 記錄管理命令 (ANALYZE, OPTIMIZE 等)
log_slow_slave_statements=ON        # 從庫記錄慢復制 SQL
log_output=FILE
min_examined_row_limit=100          # 忽略掃描行數 < 100 的查詢

動態開關

SET GLOBAL slow_query_log = 'ON';
SET GLOBAL slow_query_log = 'OFF';

分析工具鏈

• pt-query-digest (Percona Toolkit)：行業標準，聚合相似 SQL、按總耗時/平均耗時/掃描行排序、生成優化建議。
• mysqldumpslow：官方腳本，簡單聚合。
• mysqlsla、myprofi、mysql-explain-slow-log 等。

pt-query-digest 輸出解讀示例

# Overall: 776 total, 11 UNIQUE, 0.00 QPS
# Attribute    total     min     max     avg     95%  stddev  median
# EXEC TIME   5657s      2s     33s      7s     23s      6s      5s
# LOCK TIME     33s       0     19s    43ms    98us   715ms    38us
# ROWS sent  323.38k     0  107.36k   427      1    6.35k      0
# Query SIZE 217.95k    38    562     288    421    81.8    285
# 重點關注: EXEC TIME 總耗時高、ROWS examined 大、Query SIZE 大的 Top SQL

五、二進制日志 - 恢復與復制的核心

作用

1. 數據恢復 (PITR)：全量備份 + Binlog 重放至任意時間點。
2. 主從復制：主庫 Binlog → 從庫 Relay Log → SQL 線程重放。

記錄內容

• 所有 DDL (CREATE/ALTER/DROP) 和 DML (INSERT/UPDATE/DELETE/REPLACE)
• 不記錄 SELECT、SHOW 等只讀語句

三種格式

生產強烈推薦 ROW (或 MIXED 兼容舊應用)。

關鍵配置

[mysqld]
log_bin=/data/logs/mysql/binarylog/mysql_bin
binlog_format=ROW
max_binlog_size=1G
expire_logs_days=7
binlog_cache_size=4M
max_binlog_cache_size=512M
sync_binlog=1                    # 每次提交刷盤，配合 innodb_flush_log_at_trx_commit=1 保證零丟失
binlog_row_image=FULL            # 全列圖像，便於異構復制

查看與分析

# 列出 Binlog 文件
SHOW BINARY LOGS;
SHOW MASTER STATUS;

# 查看事件
SHOW BINLOG EVENTS [IN 'mysql_bin.000005'] [FROM 120] [LIMIT 10];

# mysqlbinlog 導出分析
mysqlbinlog --no-defaults --start-position=120 --stop-position=332 mysql_bin.000005 | gzip > extra.sql.gz
mysqlbinlog --start-datetime="2024-01-15 10:00:00" mysql_bin.000005 > extra.sql
mysqlbinlog --database=test --set-charset=utf8mb4 mysql_bin.000005 mysql_bin.000006 > test.sql

# 遠程讀取 (需複製用戶 REPLICATION SLAVE)
mysqlbinlog -urepl -p -hmaster_ip -P3306 --read-from-remote-server mysql_bin.000005 > remote.sql

清理策略

# 自動: expire_logs_days=7
# 手動 (謹慎，確認從庫已同步)
RESET MASTER;                              # 危險：刪除所有 Binlog，重置索引
PURGE MASTER LOGS TO 'mysql_bin.000010';   # 刪除指定文件之前
PURGE MASTER LOGS BEFORE '2024-01-01';     # 刪除指定時間前
PURGE MASTER LOGS BEFORE CURRENT_DATE - INTERVAL 3 DAY;

六、中繼日志 - 從庫復制緩衝區

特點

• 格式與 Binlog 完全相同，可用 mysqlbinlog 解析。
• 由 I/O 線程從主庫拉取 Binlog 寫入，SQL 線程讀取重放。
• 自動清理：SQL 線程處理完的 Relay Log 自動刪除。

關鍵配置

[mysqld]
relay_log=/data/logs/mysql/relaylog/mysql_relay
relay_log_index=/data/logs/mysql/relaylog/mysql_relay.index
max_relay_log_size=1G       # 0 時跟隨 max_binlog_size

狀態文件 (備份必備)

備份從庫時必備份這兩個文件 + Relay Log**，恢復後用 CHANGE MASTER TO MASTER_LOG_FILE=..., MASTER_LOG_POS=... 接續復制。

七、日志切割 - 防止磁盤寫滿 (logrotate)

/etc/logrotate.d/mysql-server

/var/log/mysql.log /var/log/mysql/mysql.log /var/log/mysql/mysql-slow.log {
    daily
    rotate 7
    missingok
    create 640 mysql adm
    compress
    sharedscripts
    postrotate
        test -x /usr/bin/mysqladmin || exit 0
        MYADMIN="/usr/bin/mysqladmin --defaults-file=/etc/mysql/debian.cnf"
        if [ -z "`$MYADMIN ping 2>/dev/null`" ]; then
            if killall -q -s0 -umysql mysqld; then exit 1; fi
        else
            $MYADMIN flush-logs
        fi
    endscript
}
# 執行: logrotate -f /etc/logrotate.d/mysql-server (強制執行)

關鍵：sharedscripts + flush-logs 確保多日志文件只觸發一次 FLUSH LOGS，避免 Binlog 虛增。

1. 上一篇文章： MySQL 游標使用詳解：聲明、遍歷、處理器與實戰案例
2. 下一篇文章： MySQL 實戰課程推廣：高可用架構、讀寫分離、分庫分表中間件與 Galera Cluster 全棧教學

MySQL 綜合教學

Windows Server 2012 R2 安裝 MySQL 5.7 ZIP 版完整配置教程

Windows Server 2012 R2 環境下部署 M

Linux 下 MySQL 編碼修改完整指南

查看當前字符集 SHOW VARIABLES LIKE %c

MySQL 5.6.36 Windows x64 手動安裝教程（含 VC++ 依賴與服務管理）

環境準備 OS：Windows 7/10/Server

Windows / WAMP 環境下 MySQL 密碼修改圖文教程

針對 WAMP 一鍵安裝包與原生 MySQL 在 Windo