MySQL 備份恢復全攻略：邏輯/物理、全量/增量、時間點/位置恢復

一、邏輯備份與恢復

mysqldump 常用場景

# 全庫備份
mysqldump -uroot -p --all-databases > all.sql

# 單庫備份
mysqldump -uroot -p product > product.sql

# 單表/多表備份
mysqldump -uroot -p product code list > code_list.sql

# 僅結構
mysqldump -uroot -p -d --add-drop-table db > schema.sql

關鍵參數：-l 鎖表、-F 切換 binlog、--single-transaction（InnoDB 熱備不鎖表）。

完全恢復流程

# 1. 恢復全量備份
mysql -uroot -p product < product.sql

# 2. 重放備份後的 binlog
mysqlbinlog localhost-bin.000012 | mysql -uroot -p product

基於時間點恢復（PITR）

# 恢復到 08:59:59
mysqlbinlog --stop-datetime="2023-09-17 08:59:59" /var/log/mysql/bin.123456 | mysql -uroot -p

# 跳過故障點繼續
mysqlbinlog --start-datetime="2023-09-17 09:01:00" /var/log/mysql/bin.123456 | mysql -uroot -p

基於位置恢復（更精確）

# 1. 導出故障前後區間 SQL
mysqlbinlog --start-datetime="08:55:00" --stop-datetime="09:05:00" bin.123456 > /tmp/restore.sql
# 2. 編輯找到錯誤語句前後 position（如 198765 / 198869）
# 3. 分段重放
mysqlbinlog --stop-position="198765" bin.123456 | mysql -uroot -p
mysqlbinlog --start-position="198769" bin.123456 | mysql -uroot -p

二、物理備份與恢復

三、生產環境建議

• 主從架構下，**從庫做備份**，主庫零影響
• 全量備份週期視數據量而定（TB 級建議物理備份 + XtraBackup）
• binlog 保留策略：expire_logs_days=7 或按磁盤容量清理
• 定期演練「全量 + binlog」恢復、PITR 恢復，記錄 RTO/RPO
• 備份文件**異地多副本**（對象存儲 + 異地機房），定期校驗校驗碼