萬盛學電腦網

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

MySQL 備份還原實戰:邏輯/物理/增量/PITR 多層防護體系構建

備份策略分層模型

層級方式RPORTO優點缺點適用
邏輯全量mysqldump備份間隔慢 (SQL重放)跨版本、可編輯、攜帶方便大庫極慢、鎖表(非單事務)中小庫、遷移、開發環境
物理全量XtraBackup備份間隔快 (文件拷貝+應用Redo)無鎖、速度快、支持增量版本/參數強綁定、文件大生產大庫標配
增量備份XtraBackup --incremental / Binlog分鐘級中等備份窗口小、空間省恢復鏈長、依賴全量基準核心業務、高頻變更
PITR 時間點恢復全量備份 + Binlog 重放秒級視Binlog量誤操作秒級回滾需完整Binlog鏈、恢復慢關鍵業務、合規要求

方案一:mysqldump 邏輯備份 (Windows/Linux 通用)

Windows 命令行


REM 進入 bin 目錄
cd "C:\Program Files\MySQL\MySQL Server 8.0\bin"

REM 備份單庫
mysqldump -u root -p --single-transaction --routines --triggers --events voice > voice_%DATE:~0,4%%DATE:~5,2%%DATE:~7,2%.sql

REM 備份單表
mysqldump -u root -p --single-transaction voice users > voice_users.sql

REM 只導結構
mysqldump -u root -p -d --add-drop-table voice > voice_structure.sql

Linux 生產環境標準化腳本


#!/bin/bash
# /opt/scripts/backup_mysql.sh
BACKUP_DIR="/backup/mysql/$(date +%F)"
mkdir -p "$BACKUP_DIR"

# 並行壓縮備份 (需安裝 pigz)
mysqldump -u backup_user -p'StrongPass' \
  --single-transaction --routines --triggers --events \
  --master-data=2 --hex-blob --default-character-set=utf8mb4 \
  mydb | pigz -p 4 > "$BACKUP_DIR/mydb_$(date +%H%M).sql.gz"

# 保留 7 天
find /backup/mysql -type f -mtime +7 -name "*.gz" -delete

還原操作


# 1. 進入 MySQL 創建空庫
mysql -u root -p -e "CREATE DATABASE voice CHARSET utf8mb4;"

# 2. 導入 (解壓管道直導,無需落地解壓)
gunzip -c voice_20240115.sql.gz | mysql -u root -p voice

# 3. 或客戶端
mysql -u root -p voice
SOURCE /backup/mysql/2024-01-15/voice_1430.sql;

方案二:Percona XtraBackup 物理熱備 (生產首選)

安裝


# CentOS/RHEL
yum install -y percona-xtrabackup-80

# Ubuntu
apt install -y percona-xtrabackup-80

全量備份


# 備份
xtrabackup --backup --user=backup_user --password='StrongPass' \
  --target-dir=/backup/mysql/full_20240115 --parallel=4 --compress --compress-threads=4

# 準備 (應用 Redo Log,使數據文件一致)
xtrabackup --prepare --target-dir=/backup/mysql/full_20240115 --use-memory=4G

增量備份


# 基於上一次全量/增量備份
xtrabackup --backup --user=backup_user --password='StrongPass' \
  --target-dir=/backup/mysql/inc_20240115_1200 \
  --incremental-basedir=/backup/mysql/full_20240115 \
  --parallel=4 --compress

# 準備增量鏈 (依次應用)
xtrabackup --prepare --apply-log-only --target-dir=/backup/mysql/full_20240115
xtrabackup --prepare --apply-log-only --target-dir=/backup/mysql/full_20240115 \
  --incremental-dir=/backup/mysql/inc_20240115_1200
# 最後一次不加 --apply-log-only
xtrabackup --prepare --target-dir=/backup/mysql/full_20240115 \
  --incremental-dir=/backup/mysql/inc_20240115_1800

恢復


# 1. 停止 MySQL
systemctl stop mysqld

# 2. 清空數據目錄 (謹慎!)
rm -rf /var/lib/mysql/*

# 3. 拷回 (xtrabackup --copy-back 或 --move-back)
xtrabackup --copy-back --target-dir=/backup/mysql/full_20240115 --datadir=/var/lib/mysql

# 4. 修正權限
chown -R mysql:mysql /var/lib/mysql

# 5. 啟動
systemctl start mysqld

方案三:PITR (時間點恢復) —— 誤操作終極盾牌

  1. 確保 log_bin=ON、binlog_format=ROW、expire_logs_days 足夠長
  2. 找到誤操作前的最近全量備份
  3. 恢復全量備份
  4. 重放 Binlog 到誤操作前一秒:
    mysqlbinlog --stop-datetime="2024-01-15 14:30:00" /var/lib/mysql/mysql-bin.000123 | mysql -u root -p
  5. 或按位置:mysqlbinlog --stop-position=123456 /var/lib/mysql/mysql-bin.000123 | mysql -u root -p

定時備份調度

  • Linux: crontab -e → 0 2 * * * /opt/scripts/backup_mysql.sh >> /var/log/backup.log 2>&1
  • Windows: 任務計劃程序 → 創建基本任務 → 每天 02:00 → 啟動程序 cmd.exe → 參數 /c "C:\scripts\backup.bat"
  • 企業級: 使用備份平台 等,統一調度、去重、異地、合規審計

備份驗證:不可跳過的關鍵環節

  1. 定期 (月度) 隨機抽取備份在測試環境恢復
  2. 校驗核心表行數、關鍵業務指標 (金額總和、用戶數)
  3. 記錄恢復耗時,驗證 RTO 是否達標
copyright © 萬盛學電腦網 all rights reserved