萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MySQL 教學

MySQL 備份與還原深度實戰:mysqldump、XtraBackup、binlog、策略、驗證

構建企業級 MySQL 數據保護體系的完整參考實施指南

涵蓋邏輯備份、物理熱備、增量日誌、異構遷移、分級策略、恢復演練七大模塊,針對 InnoDB/MyISAM 差異給出生產級參數與流程,可直接作為運維手冊章節。

一、mysqldump 邏輯備份:參數精簡版

# 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

二、Percona XtraBackup 物理熱備:標準化流程

# 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

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

  1. 啟用:log-bin=binlog, binlog_format=ROW, binlog_row_image=FULL, expire_logs_days=7
  2. 切換:FLUSH LOGS; 或 FLUSH BINARY LOGS; 產生新文件
  3. 備份:定時 rsync/scp binlog.* 到異地
  4. PITR 恢復:
    # 全量恢復到 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

四、SELECT INTO OUTFILE / LOAD DATA 異構遷移

-- 導出(需 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');

五、分級備份策略矩陣(按業務價值)

級別全量增量工具鏈RPORTO異地
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每日 mysqldumpmysqldump< 24 h< 4 h同區備份桶
P3 測試/開發按需無mysqldump --no-data無無本地

六、MyISAM 專項:表級鎖備份

# 必須鎖表
mysqldump -u root -p --lock-tables --flush-logs mydb > myisam_backup.sql
# 或 FLUSH TABLES WITH READ LOCK + cp .MYD/.MYI/.frm 文件

七、恢復演練標準化檢查清單(月度必做)

  1. 在隔離測試環境執行全量 + 增量恢復
  2. 核對核心表行數:SELECT COUNT(*) FROM core_table;
  3. 核對關鍵指標:SELECT SUM(amount) FROM orders WHERE date = '...';
  4. 校驗檢查和:CHECKSUM TABLE core_table;
  5. 驗證自增 ID 連續性、觸發器/存儲過程/視圖完整性
  6. 記錄耗時,對比 RTO 目標,產出演練報告

八、常見失敗模式與對策

失敗模式根因對策
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 精準。
策略分級有矩陣,工具鏈條標準化。
備份不演練等於零,月度演練出報告。
失敗模式全收錄,對策預案先落地。

copyright © 萬盛學電腦網 all rights reserved