恢復策略決策樹

故障類型	恢復方式
表輕微損壞	myisamchk -r / REPAIR TABLE 修復
表丟失/無法修復	備份文件 + 二進制日志重放
整庫損壞/誤刪	全量備份 + 增量日志重放
誤執行 DROP/DELETE	全量備份 + 日志截取至誤操作前

一、全量備份最佳實踐

# 邏輯備份（跨版本遷移首選）
mysqldump -u root -p --single-transaction --routines --triggers --all-databases > full_$(date +%F).sql

# 物理備份（大數據庫、恢復快）
# 1. 關閉服務或加全局讀鎖
mysqladmin -u root -p flush-tables-with-read-lock
# 2. 拷貝 datadir（tar/cp/rsync）
tar czf /backup/datadir_$(date +%F).tar.gz /var/lib/mysql
# 3. 釋放鎖
mysqladmin -u root -p unlock-tables

二、增量備份：二進制日志

1. my.cnf 啟用：log-bin=mysql-bin、binlog_format=ROW、expire_logs_days=7
2. 定期 FLUSH LOGS 切換新日志文件
3. 備份所有 mysql-bin.* 文件

三、恢復操作手冊

1. 恢復整個數據庫

# 停止服務
systemctl stop mysqld
# 清空/備份舊 datadir
mv /var/lib/mysql /var/lib/mysql.bak
# 還原物理備份
tar xzf /backup/datadir_2024-01-01.tar.gz -C /
# 重啟服務
systemctl start mysqld
# 重放二進制日志（指定起始位置）
mysqlbinlog --start-position=1234 /var/lib/mysql/mysql-bin.000001 | mysql -u root -p
# 或按文件順序全部重放
ls -1 /var/lib/mysql/mysql-bin.* | xargs cat | mysql -u root -p

2. 恢復單表（邏輯備份）

# 從全量 SQL 中提取單表
sed -n '/^CREATE TABLE `target_table`/,/^UNLOCK TABLES;/p' full_backup.sql > table.sql
# 導入
mysql -u root -p target_db < table.sql
# 重放該表相關的 binlog（需 mysqlbinlog 過濾）
mysqlbinlog --database=target_db --table=target_table mysql-bin.* | mysql -u root -p target_db

3. 針對誤操作的精準恢復

# 1. 找到誤操作的 binlog 位置
mysqlbinlog --base64-output=decode-rows -v mysql-bin.000001 | grep -n -B5 -A5 "DROP TABLE"
# 2. 重放至誤操作前一個 position
mysqlbinlog --stop-position=XXXX mysql-bin.000001 | mysql -u root -p

四、自動化腳本框架

#!/bin/bash
# daily_full.sh
mysqldump ... > /backup/full_$(date +%F).sql
find /backup -name "full_*.sql" -mtime +7 -delete

# hourly_incremental.sh
mysqladmin flush-logs
cp /var/lib/mysql/mysql-bin.* /backup/binlog_$(date +%F_%H)/

五、驗證與演練

• 每月至少一次「火演」：在測試環境全量恢復並校驗數據完整性
• 監控備份文件大小、耗時、binlog 磁盤佔用
• 異地備份：rsync/rclone 推送至異地存儲