MySQL 數據備份與恢復完整策略：冷備、邏輯備、增量備、時間點恢復

MySQL 備份恢復體系：策略制定、多模式實施、演練驗證

備份是最後防線，恢復才是目的。建立「定期備份、開啟 Binlog、定期演練」三位一體體系。

一、備份策略三原則

1. RPO/RTO 量化：業務可接受數據丟失量（RPO）與恢復時間（RTO）
2. 多層次備份：冷備（文件級）+ 邏輯備（SQL級）+ 增量備
3. 定期演練：月度恢復演練，驗證備份可用性與流程熟練度

二、冷備份（物理文件級）

# 備份步驟
systemctl stop mysqld
tar -zcf /backup/mysql_cold_$(date +%F).tar.gz /var/lib/mysql
systemctl start mysqld
# 備份啟動後產生的 binlog
cp /var/lib/mysql/mysql-bin.* /backup/binlog/

# 恢復步驟
systemctl stop mysqld
rm -rf /var/lib/mysql/*
tar -zxf /backup/mysql_cold_2024-01-15.tar.gz -C /
systemctl start mysqld
# 重放 binlog
mysqlbinlog /backup/binlog/mysql-bin.000001 | mysql -u root -p

優點：速度快、文件級一致性。缺點：需停服務、跨版本/架構遷移受限。

三、邏輯備份

# 夜間低峰備份（-F 刷新 binlog 便於增量）
mysqldump -u root -p --single-transaction --routines --triggers --events -F db_name > db_name_$(date +%F).sql

# 壓縮
gzip db_name_$(date +%F).sql

# 恢復
mysql -u root -p db_name < db_name_2024-01-15.sql
# 重放備份後的 binlog
mysqlbinlog /var/lib/mysql/mysql-bin.000002 | mysql -u root -p

四、單表備份與恢復

-- 導出（方法1：SELECT INTO OUTFILE）
SELECT * INTO OUTFILE '/tmp/order_tab.csv' 
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
FROM order_tab;

-- 導出（方法2：mysqldump -T）
mysqldump -u root -p -T /tmp db_name order_tab --fields-terminated-by=','

-- 導入（方法1：LOAD DATA）
LOAD DATA [LOCAL] INFILE '/tmp/order_tab.csv' INTO TABLE order_tab
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n';

-- 導入（方法2：mysqlimport）
mysqlimport -u root -p [--local] db_name order_tab.txt --fields-terminated-by=','

-- 跨平台注意：Windows \r\n vs Linux \n，指定 LINES TERMINATED BY

五、增量備份與時間點恢復 (PITR)

前提：log_bin=ON，binlog_format=ROW（推薦）

1. 基於時間點恢復

# 假設 10:00 發生誤操作，恢復到 09:59:59
mysqlbinlog --stop-datetime="2024-01-15 09:59:59" /var/lib/mysql/mysql-bin.000001 | mysql -u root -p

# 跳過故障時段，繼續重放後續
mysqlbinlog --start-datetime="2024-01-15 10:01:00" /var/lib/mysql/mysql-bin.000001 | mysql -u root -p

2. 基於位置恢復（更精確）

# 1. 導出故障前後 binlog 片段分析
mysqlbinlog --start-datetime="2024-01-15 09:55:00" --stop-datetime="2024-01-15 10:05:00" \
    /var/lib/mysql/mysql-bin.000001 > /tmp/restore.sql

# 2. 編輯文件，定位錯誤語句前後 position（如 368312, 368315）

# 3. 恢復到錯誤前
mysqlbinlog --stop-position="368312" /var/lib/mysql/mysql-bin.000001 | mysql -u root -p

# 4. 從錯誤後繼續
mysqlbinlog --start-position="368315" /var/lib/mysql/mysql-bin.000001 | mysql -u root -p

六、MyISAM 表修復

# 損壞癥狀：查詢中斷、.MYI 丟失、記錄文件損壞

# 方法1：myisamchk（需停庫）
myisamchk -r /var/lib/mysql/db/tbl_name      # 標準修復
myisamchk -o /var/lib/mysql/db/tbl_name      # 強力修復（慢）

# 方法2：SQL 語句（在線）
CHECK TABLE tbl_name [QUICK|FAST|MEDIUM|EXTENDED|CHANGED];
REPAIR [LOCAL | NO_WRITE_TO_BINLOG] TABLE tbl_name [QUICK] [EXTENDED] [USE_FRM];

七、企業級備份工具推薦

備份未經恢復演練 = 沒備份。建立備份清單：庫表清單、備份頻率、保留周期、異地存儲、恢復步驟文檔、責任人、演練記錄。