MySQL 日誌體系完整解析：類型、配置、查看與二進位日誌時間點恢復實戰

MySQL 七大日誌類型總覽

日誌配置最佳實踐

[mysqld]
# 錯誤日誌（必開）
log-error=/var/log/mysql/mysqld_err.log

# 慢查詢日誌（必開）
log-slow-queries=/var/log/mysql/mysqld_slow.log
long_query_time=1
log-queries-not-using-indexes

# 二進位日誌（主庫必開，從庫建議開）
log-bin=/var/log/mysql/mysqld_bin.bin
binlog_format=ROW
max_binlog_size=512M
expire_logs_days=7
sync_binlog=1

# 通用查詢日誌（僅除錯時暫時開啟）
# log=/var/log/mysql/mysqld_common.log

日誌查看工具與技巧

1. 文字日誌直接查看

tail -f /var/log/mysql/mysqld_err.log
less /var/log/mysql/mysqld_slow.log
grep "ERROR" /var/log/mysql/mysqld_err.log

2. 二進位日誌查看：mysqlbinlog

# 確認是否啟用
SHOW VARIABLES LIKE 'log_bin';

# 當前寫入檔案與位置
SHOW MASTER STATUS;

# 查看指定檔案（輸出為文字）
mysqlbinlog /var/log/mysql/mysql-bin.000040

# 導出文字檔分析
mysqlbinlog /var/log/mysql/mysql-bin.000040 > /tmp/binlog_000040.txt

# 指定時間範圍
mysqlbinlog --start-datetime='2024-01-01 00:00:00' \
            --stop-datetime='2024-01-31 23:59:59' \
            /var/log/mysql/mysql-bin.000040 > /tmp/jan2024.sql

實戰：二進位日誌時間點恢復 (PITR) 完整流程

場景模擬

-- 建立測試表與資料
CREATE TABLE test(id INT AUTO_INCREMENT PRIMARY KEY, val INT, data VARCHAR(20));
INSERT INTO test(val,data) VALUES(10,'liang'),(20,'jia'),(30,'hui');
FLUSH LOGS;  -- 產生 binlog.000001
INSERT INTO test(val,data) VALUES(40,'aaa'),(50,'bbb'),(60,'ccc');
DELETE FROM test WHERE id BETWEEN 4 AND 5;  -- 誤刪！
INSERT INTO test(val,data) VALUES(70,'ddd');
FLUSH LOGS;  -- 產生 binlog.000002
INSERT INTO test(val,data) VALUES(80,'dddd'),(90,'eeee');
DROP TABLE test;  -- 災難性誤操作！
FLUSH LOGS;  -- 產生 binlog.000003

恢復策略

1. 全量恢復第一個日誌：binlog.000001 完整重放
2. 第二個日誌跳過 DELETE：分析位置，--stop-position 停在 DELETE 前，--start-position 從 DELETE 後繼續
3. 第三個日誌跳過 DROP TABLE：同理跳過 DROP 位置

具體操作

# 1. 分析第二個日誌，找到 DELETE 位置
mysqlbinlog /var/log/mysql/mysql-bin.000002 > /tmp/000002.txt
# 搜尋 "delete from test" 找到位置：起始 875，結束 1008

# 2. 恢復流程
# 完整重放第一個日誌
mysqlbinlog /var/log/mysql/mysql-bin.000001 | mysql -uroot -p

# 重放第二個日誌：DELETE 前
mysqlbinlog --stop-position=875 /var/log/mysql/mysql-bin.000002 | mysql -uroot -p

# 重放第二個日誌：DELETE 後
mysqlbinlog --start-position=1008 /var/log/mysql/mysql-bin.000002 | mysql -uroot -p

# 重放第三個日誌：DROP TABLE 前（假設位置 574）
mysqlbinlog --stop-position=574 /var/log/mysql/mysql-bin.000003 | mysql -uroot -p

驗證結果

SELECT * FROM test;
+----+------+-------+
| id | val  | data  |
+----+------+-------+
|  1 |   10 | liang |
|  2 |   20 | jia   |
|  3 |   30 | hui   |
|  4 |   40 | aaa   |
|  5 |   50 | bbb   |
|  6 |   60 | ccc   |
|  7 |   70 | ddd   |
|  8 |   80 | dddd  |
|  9 |   90 | eeee  |
+----+------+-------+

所有資料完美恢復，誤刪與誤 DROP 被成功跳過。

關鍵參數對照

生產環境 PITR 標準作業程序 (SOP)

1. 確認最後一次**全量備份**時間與位置（SHOW MASTER STATUS）
2. 從全量備份恢復基礎資料
3. 套用全量備份後至目標時間點的**所有二進位日誌**
4. 使用 --stop-datetime 精確停在事故發生前
5. 驗證關鍵業務資料完整性
6. 切換流量、通知相關方