MySQL 備份恢復進階：結合 Binlog 確保數據零丟失

MySQL 備份恢復進階：結合 Binlog 確保數據零丟失

前文介紹熱拷貝，本文聚焦數據一致性保障。假設凌晨做全量備份，白天發生故障，凌晨至故障點的增量數據若無 Binlog 將永久丟失。InnoDB 支持事務與 Binlog，通過全量備份 + Binlog 增量恢復可實現時間點恢復（PITR），最大程度減少 RPO。

架構示意圖

實戰模擬：基於時間點的恢復

步驟 1：驗證當前數據

mysql> use larrydb;
mysql> select * from class;
+------+--------+
| cid | cname |
+------+--------+
| 1 | linux | | 2 | oracle |
+------+--------+
mysql> select * from stu;
+------+---------+------+
| sid | sname | cid |
+------+---------+------+
| 1 | larry01 | 1 |
| 2 | larry02 | 2 |
+------+---------+------+

步驟 2：全量備份（mysqldump）

[root@serv01 databackup]# mysqldump -uroot -p123456 --database larrydb > larrydb.sql

步驟 3：重置 Binlog（備份後舊日志無用）

mysql> show binary logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000001 | 27320 | ...
| mysql-bin.000018 | 13085 |
+------------------+-----------+
18 rows in set (0.00 sec)
mysql> reset master;
Query OK, 0 rows affected (0.01 sec)
mysql> show binary logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000001 | 107 |
+------------------+-----------+

步驟 4：模擬業務寫入（產生增量 Binlog）

mysql> insert into class values(3,'Devel');
mysql> update class set cname="dab" where cid=2;
mysql> delete from stu where cid=2;
mysql> update stu set sname="larry007" where sid=1;
mysql> select * from class;
+------+-------+
| cid | cname |
+------+-------+
| 1 | linux | | 2 | dab | | 3 | Devel |
+------+-------+
mysql> select * from stu;
+------+----------+------+
| sid | sname | cid |
+------+----------+------+
| 1 | larry007 | 1 |
+------+----------+------+
[root@serv01 data]# date
Tue Sep 10 19:38:24 CST 2013

步驟 5：模擬災難（刪除庫文件 & DROP DATABASE）

[root@serv01 data]# rm -rf /usr/local/mysql/data/larrydb/
mysql> drop database larrydb;

步驟 6：恢復全量備份

[root@serv01 databackup]# mysql -uroot -p123456 < larrydb.sql
mysql> use larrydb;
mysql> select * from class;
+------+--------+
| cid | cname |
+------+--------+
| 1 | linux | | 2 | oracle |
+------+--------+
mysql> select * from stu;
+------+---------+------+
| sid | sname | cid |
+------+---------+------+
| 1 | larry01 | 1 |
| 2 | larry02 | 2 |
+------+---------+------+

步驟 7：根據 Binlog 精確恢復增量數據

使用 mysqlbinlog --stop-datetime 指定恢復截止時間（故障點 +1 秒）：

[root@serv01 data]# mysqlbinlog --stop-datetime "2013-09-10 19:37:45" mysql-bin.000001 | mysql -uroot -p123456
mysql> select * from stu;
+------+---------+------+
| sid | sname | cid |
+------+---------+------+
| 1 | larry01 | 1 |
+------+---------+------+
mysql> select * from class;
+------+-------+
| cid | cname |
+------+-------+
| 1 | linux | | 2 | dab | | 3 | Devel |
+------+-------+

# 再恢復 1 秒，包含最後一次 COMMIT
[root@serv01 data]# mysqlbinlog --stop-datetime "2013-09-10 19:37:46" mysql-bin.000001 | mysql -uroot -p123456
mysql> select * from stu;
+------+----------+------+
| sid | sname | cid |
+------+----------+------+
| 1 | larry007 | 1 |
+------+----------+------+
mysql> select * from class;
+------+-------+
| cid | cname |
+------+-------+
| 1 | linux | | 2 | dab | | 3 | Devel | | 3 | Devel |  <-- 重複插入，需注意去重
+------+-------+

關鍵規律： 恢復時間點應為「事故發生時間 + 1 秒」，確保包含該事務的 COMMIT。生產環境建議結合 --start-position / --stop-position 精確控制。

最佳實踐總結

1. 定期全量備份 + 啟用 Binlog（Row 格式最佳）。
2. 備份成功後 RESET MASTER 或 PURGE BINARY LOGS 清理舊日志。
3. 災難恢復：先恢復全量備份，再按時間點/位置回放 Binlog。
4. 恢復操作必須在測試庫驗證無誤後，再應用到生產庫。