涵蓋邏輯備份、物理熱備、增量日誌、異構遷移、分級策略、恢復演練七大模塊,針對 InnoDB/MyISAM 差異給出生產級參數與流程,可直接作為運維手冊章節。
# InnoDB 生產標準(無鎖一致性快照 + binlog 位點)
mysqldump -u backup -p --single-transaction --master-data=2 \
--routines --triggers --events --hex-blob --quick \
--default-character-set=utf8mb4 --databases db1 db2 > backup_$(date +%F_%H%M).sql
# 只導結構(審計/遷移)
mysqldump -u root -p --no-data --routines --triggers --events db1 > schema.sql
# 只導數據(重建表後導入)
mysqldump -u root -p --no-create-info --extended-insert=FALSE db1 > data.sql
# 1. 全量備份
xtrabackup --backup --target-dir=/backup/full_$(date +%F) \
--user=backup --password=xxx --parallel=4 --compress --compress-threads=2
# 2. 增量備份(基於最近一次全量/增量)
xtrabackup --backup --target-dir=/backup/inc_$(date +%F) \
--incremental-basedir=/backup/full_2024-01-15 --parallel=4 --compress
# 3. 準備階段(必須,回放 redo log 使數據一致)
xtrabackup --prepare --target-dir=/backup/full_2024-01-15
xtrabackup --prepare --target-dir=/backup/full_2024-01-15 \
--incremental-dir=/backup/inc_2024-01-16
# 4. 恢復(需停庫、datadir 為空)
xtrabackup --copy-back --target-dir=/backup/full_2024-01-15
chown -R mysql:mysql /var/lib/mysql
systemctl start mysqld
log-bin=binlog, binlog_format=ROW, binlog_row_image=FULL, expire_logs_days=7FLUSH LOGS; 或 FLUSH BINARY LOGS; 產生新文件# 全量恢復到 2024-01-15 02:00
mysqlbinlog --stop-datetime="2024-01-15 02:00:00" /backup/binlog.* | mysql -u root -p
# 或指定位點
mysqlbinlog --start-position=12345 --stop-position=67890 /backup/binlog.000001 | mysql -u root -p
-- 導出(需 FILE 權限、secure_file_priv 目錄)
SELECT * FROM t INTO OUTFILE '/var/lib/mysql-files/t.tsv'
CHARACTER SET utf8mb4 FIELDS TERMINATED BY '\t' LINES TERMINATED BY '\n';
-- 導入
LOAD DATA INFILE '/var/lib/mysql-files/t.tsv' INTO TABLE t
CHARACTER SET utf8mb4 FIELDS TERMINATED BY '\t' LINES TERMINATED BY '\n'
IGNORE 1 LINES (col1, col2, @dt) SET created_at = STR_TO_DATE(@dt, '%Y-%m-%d %H:%i:%s');
| 級別 | 全量 | 增量 | 工具鏈 | RPO | RTO | 異地 |
|---|---|---|---|---|---|---|
| P0 核心交易 | 每日 02:00 | 實時 binlog + 每小時 XtraBackup 增量 | XtraBackup + binlog | < 1 min | < 30 min | 跨 AZ/跨雲 |
| P1 重要業務 | 每日 03:00 | 每 6 小時 XtraBackup 增量 | mysqldump + binlog | < 1 h | < 2 h | 跨 AZ |
| P2 一般業務 | 每周日 04:00 | 每日 mysqldump | mysqldump | < 24 h | < 4 h | 同區備份桶 |
| P3 測試/開發 | 按需 | 無 | mysqldump --no-data | 無 | 無 | 本地 |
# 必須鎖表
mysqldump -u root -p --lock-tables --flush-logs mydb > myisam_backup.sql
# 或 FLUSH TABLES WITH READ LOCK + cp .MYD/.MYI/.frm 文件
SELECT COUNT(*) FROM core_table;SELECT SUM(amount) FROM orders WHERE date = '...';CHECKSUM TABLE core_table;| 失敗模式 | 根因 | 對策 |
|---|---|---|
| mysqldump 卡死 OOM | 大表無 --quick 緩存全表 | 加 --quick、分庫分表導出 |
| XtraBackup prepare 失敗 | 增量基點目錄錯誤/權限 | 腳本化 basedir/incremental-dir、權限預檢 |
| binlog PITR 時間點偏移 | 時區不一致(UTC vs LOCAL) | 統一 UTC,工具參數顯式指定時區 |
| LOAD DATA 導入極慢 | 未關 unique_checks/foreign_key_checks | 會話級關閉檢查、批次提交 |
| 備份文件損壞/丟失 | 單點存儲、無校驗 | 多副本異地、定期 SHA256 校驗 |
邏輯備份 mysqldump 通用,物理熱備 XtraBackup 极速,增量靠 binlog 精準。
策略分級有矩陣,工具鏈條標準化。
備份不演練等於零,月度演練出報告。
失敗模式全收錄,對策預案先落地。