MySQL 利用 Binlog 實現增量備份與實例還原完整指南

什麼是增量備份？

增量備份僅針對新增或變更的數據進行備份。假設資料庫總量 10 GB，每日新增 10 MB，若每天完整備份會造成極大的伺服器負擔；採用增量備份僅需處理每日新增的 10 MB，大幅降低 I/O 與儲存壓力。

啟用 Binlog

編輯 my.cnf，在 [mysqld] 區段加入：

log-bin=/var/lib/mysql/mysql-bin.log

若未指定路徑，預設會在 datadir 下產生 mysql-bin.000001、mysql-bin.000002 等檔案，每次 MySQL 重啟都會產生新的 binlog 檔案。mysql-bin.index 則記錄所有 binlog 檔案的索引。

檢視 Binlog 內容

使用 mysqlbinlog 將二進制日志轉為可讀 SQL：

mysqlbinlog /var/lib/mysql/mysql-bin.000002 > /tmp/add.sql

輸出範例：

# at 106
#100929 21:29:35 server id 1 end_log_pos 134 Intvar SET INSERT_ID=16
# at 134
#100929 21:29:35 server id 1 end_log_pos 237 Query thread_id=1 exec_time=0 error_code=0
use test
SET TIMESTAMP=1285766975
insert into aa(name) values('cccccccccc')

每筆事件包含位置（# at 106）、時間戳、Server ID、事件類型（Intvar、Query、Xid 等）與實際 SQL。

增量備份與還原策略

1. 增量備份

定期複製 binlog 檔案至備份目錄：

cp /var/lib/mysql/mysql-bin* /data/mysql_newbak/

2. 增量還原常用方式

• 按時間區間還原：
  mysqlbinlog --start-date="2010-09-29 18:00:00" --stop-date="2010-09-29 23:00:00" /var/lib/mysql/mysql-bin.000002 | mysql -u root -p
• 按位置還原：
  mysqlbinlog --start-position=370 --stop-position=440 /var/lib/mysql/mysql-bin.000002 | mysql -u root -p
  位置數值來自 mysqlbinlog 輸出的 # at 370 與 end_log_pos 440。
• 指定資料庫：
  mysqlbinlog -d test /var/lib/mysql/mysql-bin.000002（小寫 -d，勿與 mysqldump 的 -D 混淆）
• 指定主機/連接埠/Server ID：
  -h 192.168.1.102、-P 3306（大寫 P）、--server-id=1
• 組合使用：
  mysqlbinlog --start-date="2010-09-29 18:00:00" -d test -h 127.0.0.1 /var/lib/mysql/mysql-bin.000002 | mysql -u root -p

後續維護：避免 Binlog 佔滿磁碟

執行完整備份時搭配 --flush-logs 切換新 binlog，舊檔案即可安全刪除：

mysqldump --flush-logs -u root myblog > /tmp/myblog.sql
mysqldump --flush-logs -u root --all-databases > /tmp/alldatabase.sql

若設定 binlog-do-db=test1，則僅記錄 test1 資料庫的變更，進一步減少日誌體積。