五大日志類型速覽
| 日志類型 | 核心作用 | 默認狀態 | 關鍵配置 |
|---|
| 錯誤日志 | 啟動/關閉、錯誤堆棧、事件調度器、從庫線程 | 強制開啟，不可關閉 | log_error, log_warnings |
| 查詢日志 | 所有客戶端操作（含增刪改查） | 關閉 | general_log, general_log_file, log_output |
| 慢查詢日志 | 超時 SQL 定位性能瓶頸 | 關閉 | slow_query_log, long_query_time, log_queries_not_using_indexes |
| 事務日志 | InnoDB Redo Log，WAL 機制保證持久性 | 強制開啟 | innodb_log_file_size, innodb_log_files_in_group, innodb_flush_log_at_trx_commit |
| 二進制日志 | 主從同步、時間點恢復、審計 | 關閉 | log_bin, binlog_format, sync_binlog, expire_logs_days |
錯誤日志：定位故障的第一現場
# 查看配置
SHOW VARIABLES LIKE 'log_error'; -- /var/log/mysql/error.log
# 實時尾部追蹤
tail -f /var/log/mysql/error.log
# 5.5.7+ 手動輪轉（舊版本用 mysqladmin flush-logs）
mv error.log error.log.old
mysqladmin flush-logs
二進制日志：核心操作與恢復實戰
查看與定位
SHOW MASTER LOGS; -- 列表
SHOW MASTER STATUS; -- 當前寫入位置
SHOW BINLOG EVENTS [IN 'file'] [FROM pos] [LIMIT n]; -- 解析事件
mysqlbinlog 命令行工具
# 查看文本
mysqlbinlog mysql-bin.000001 | head -50
# 導出 SQL
mysqlbinlog mysql-bin.000001 > /tmp/replay.sql
# 時間點恢復（生產慎用，需精確位置）
mysqlbinlog --start-datetime="2024-01-01 10:00:00" --stop-datetime="2024-01-01 10:30:00" mysql-bin.000001 | mysql -uroot -p
# 位置恢復（推薦，精確可控）
mysqlbinlog --start-position=12345 --stop-position=67890 mysql-bin.000001 | mysql -uroot -p
Binlog 格式選擇
| 格式 | 特點 | 適用場景 |
|---|
| STATEMENT | 記錄 SQL 文本，體積小 | 確定性函數、簡單更新 |
| ROW | 記錄行變更，體積大，精確 | 非確定性函數、複雜更新、主從一致性要求高 |
| MIXED (推薦) | 自動判斷，兼顧效能與正確性 | **生產默認選擇** |
關鍵持久化參數
sync_binlog=1：每次提交 fsync，**零資料丟失**，效能犧牲可接受（配合 RAID/BBU）。expire_logs_days=7：自動清理舊日誌，防磁盤爆滿。max_binlog_size=1G：單文件上限，超過自動切割。
清理與維護命令
# 按文件清理（保留指定文件及之後）
PURGE BINARY LOGS TO 'mysql-bin.000010';
# 按時間清理（保留指定時間及之後）
PURGE BINARY LOGS BEFORE '2024-01-01 00:00:00';
# 重置所有 Binlog（極度危險，僅初始化新實例使用）
RESET MASTER;
慢查詢日誌：性能調優的金礦
# 開啟並設置閾值
SET GLOBAL slow_query_log=1;
SET GLOBAL long_query_time=1; -- 1 秒，可微秒級
SET GLOBAL log_queries_not_using_indexes=ON; -- 記錄未用索引查詢
# 分析神器
pt-query-digest /var/log/mysql/slow.log --limit 20 --filter '$event->{fingerprint} !~ /^SET/'
# 輸出 Top 20 慢查詢報告，含執行次數、總耗時、平均耗時、掃描行數、索引使用情況
事務日志：InnoDB 核心參數
SHOW VARIABLES LIKE 'innodb_log_%';
-- innodb_log_file_size=512M (單文件)
-- innodb_log_files_in_group=2 (文件組數量)
-- innodb_log_group_home_dir=./ (目錄)
-- innodb_flush_log_at_trx_commit=1 (1=每提交刷盤, 2=每秒刷盤, 0=OS決定)
建議：innodb_log_file_size 設為 Buffer Pool 的 1/4 ~ 1/2（如 4GB BP → 1GB Log File），減少 Checkpoint 刷盤頻率。
日志輸出目標控制
SET GLOBAL log_output='FILE,TABLE'; -- 同時寫文件與 mysql.general_log/slow_log 表
SET GLOBAL log_output='NONE'; -- 開啟功能但不記錄（調試用）
總結：日志運維核心清單
- **錯誤日志**永久保留，接入日志平台（ELK/Loki）告警關鍵字。
- **慢查詢日誌**全量開啟，
long_query_time=1，定期 pt-query-digest 分析。
- **二進制日誌**生產強制開啟，
MIXED + sync_binlog=1 + expire_logs_days=7。
- **事務日誌**根據 BP 大小調整
innodb_log_file_size，innodb_flush_log_at_trx_commit=1。
- **查詢日誌**僅調試臨時開啟，**嚴禁生產長期開啟**（IO 暴增）。
- 建立 **Binlog 位置備份機制**（每日導出
SHOW MASTER STATUS），支撐精準時間點恢復。
