MySQL 日誌完整指南：啟用、查看、二進制日誌恢復實戰

MySQL 日誌完整指南：啟用、查看、二進制日誌恢復實戰

MySQL 日誌是資料庫運維、故障排查、資料恢復的核心工具。本文系統梳理錯誤日誌、查詢日誌、慢查詢日誌、二進制日誌的啟用配置、查看方式，並詳細講解如何利用 mysqlbinlog 進行基於時間點與位置的精準資料恢復。

一、主要日誌類型與啟用參數

二、配置檔啟用範例 (my.cnf / my.ini)

[mysqld]
log-bin = /var/log/mysql/binlog
binlog_format = ROW
server-id = 1
expire_logs_days = 7
max_binlog_size = 100M

slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 1
log_queries_not_using_indexes = 1

log-error = /var/log/mysql/error.log

修改後需重啟 MySQL 服務生效。

三、常用查看與管理命令

-- 查看二進制日誌狀態
SHOW VARIABLES LIKE 'log_bin';
SHOW MASTER STATUS;

-- 列出所有 binlog 檔案
SHOW BINARY LOGS;

-- 查看當前 binlog 事件
SHOW BINLOG EVENTS;

-- 使用 mysqlbinlog 查看檔案內容
mysqlbinlog /var/log/mysql/binlog.000001
mysqlbinlog /var/log/mysql/binlog.000001 | tail -20

-- 導出為 SQL 檔案便於分析
mysqlbinlog /var/log/mysql/binlog.000001 > /tmp/binlog.sql

四、基於二進制日誌的資料恢復實戰

1. 指定時間點恢復

-- 恢復到 2024-01-15 10:00:00 之前
mysqlbinlog --stop-datetime="2024-01-15 10:00:00" /var/log/mysql/binlog.000001 | mysql -u root -p

-- 恢復 10:01:00 之後的操作
mysqlbinlog --start-datetime="2024-01-15 10:01:00" /var/log/mysql/binlog.000001 | mysql -u root -p

2. 指定位置恢復（更精準）

-- 先導出特定時間區間檢視 position
mysqlbinlog --start-datetime="2024-01-15 09:55:00" --stop-datetime="2024-01-15 10:05:00" \
  /var/log/mysql/binlog.000001 > /tmp/review.sql

-- 確認錯誤 SQL 的 position 後，分段恢復
mysqlbinlog --stop-position="368312" /var/log/mysql/binlog.000001 | mysql -u root -p
mysqlbinlog --start-position="368315" /var/log/mysql/binlog.000001 | mysql -u root -p

3. 透過導出腳本恢復

mysqlbinlog /var/log/mysql/binlog.000001 > /tmp/full.sql
mysql -u root -p -e "source /tmp/full.sql"

五、進階管理操作

-- 產生新的 binlog 檔案（舊檔編號+1）
FLUSH LOGS;

-- 刪除所有 binlog 並重新編號（謹慎使用！）
RESET MASTER;

-- 快速備份資料庫
mysqldump -u root -p --single-transaction --quick db_name > backup.sql

六、避坑指南

• binlog_format 建議 ROW：避免 STATEMENT 格式下的非確定性函數導致主從不一致。
• expire_logs_days：必須設定自動清理，防止磁碟爆滿。
• 恢復前備份：任何恢復操作前，先備份現有資料與當前 binlog。
• GTID 模式：MySQL 5.6+ 建議啟用 GTID，簡化故障轉移與恢復邏輯。