前言

數據備份是資安核心，MySQL 提供邏輯備份（mysqldump）與物理備份（xtrabackup、快照）多種手段。本文聚焦邏輯備份的標準化操作流程，涵蓋全量備份、單庫/單表備份、壓縮、恢復驗證與自動化腳本模板。

核心工具：mysqldump

基本語法

mysqldump [options] -u user -p db_name [tbl_name ...] > dump.sql
mysqldump [options] -u user -p --databases db1 db2 ... > dump.sql
mysqldump [options] -u user -p --all-databases > dump.sql

常用參數速查

參數	說明
-d, --no-data	僅導出結構（DDL）
-t, --no-create-info	僅導出數據（DML）
--single-transaction	InnoDB 熱備份必加，保證一致性視圖，不鎖表
--master-data=2	輸出 Binlog 位置（註釋形式），便於主從搭建
--routines --triggers --events	導出存儲過程、觸發器、事件
--hex-blob	二進制列用十六進制導出，避免編碼問題
--quick	大表流式讀取，不緩存整個結果集
--compress	客戶端與服務端傳輸壓縮

備份實戰範例

1. 全量備份（含結構、數據、常規對象、Binlog 位置）

mysqldump -u root -p \
  --single-transaction \
  --master-data=2 \
  --routines --triggers --events \
  --hex-blob \
  --quick \
  --databases db1 db2 > /backup/full_$(date +%F).sql

2. 僅備份表結構（用於審核、遷移 Schema）

mysqldump -u root -p -d --databases db1 > /backup/schema_db1_$(date +%F).sql

3. 單表備份並壓縮

mysqldump -u root -p db1 large_table | gzip > /backup/db1_large_table_$(date +%F).sql.gz

4. 遠程備份到備份服務器

mysqldump -h db_host -u root -p db1 | ssh backup_user@backup_host "cat > /backup/db1_$(date +%F).sql"

恢復操作

方法一：進入 MySQL 客戶端執行

mysql -u root -p
mysql> CREATE DATABASE IF NOT EXISTS db1 CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
mysql> USE db1;
mysql> SOURCE /backup/full_2024-01-15.sql;

方法二：管道直接導入（非交互，適合腳本）

mysql -u root -p db1 < /backup/full_2024-01-15.sql
# 壓縮文件
zcat /backup/db1_large_table_2024-01-15.sql.gz | mysql -u root -p db1

方法三：並行恢復大表（需 mydumper/myloader 或手動分表）

mysqldump 導出為單文件，恢復為單線程。大數據量建議使用 mydumper 並行導出、myloader 並行導入，效率提升 5-10 倍。

驗證備份可用性（必做）

1. 校驗文件完整性：tail -5 backup.sql 應包含 -- Dump completed on ...。
2. 校驗表數量：grep -c "CREATE TABLE" backup.sql 對比源庫 SHOW TABLES。
3. 定期演練恢復：每季度在測試環境全量恢復並跑業務驗證腳本。
4. 校驗和：sha256sum backup.sql > backup.sha256，異地存儲校驗文件。

自動化備份腳本模板（每日全量 + Binlog 增量）

#!/bin/bash
# /usr/local/bin/mysql_backup.sh
set -euo pipefail

BACKUP_DIR="/backup/mysql"
RETENTION_DAYS=7
MYSQL_USER="backup_user"
MYSQL_PASS="******"
MYSQL_HOST="127.0.0.1"
DATABASES="db1 db2"

mkdir -p "$BACKUP_DIR"
DATE=$(date +%F_%H-%M)

# 全量備份
mysqldump -h "$MYSQL_HOST" -u "$MYSQL_USER" -p"$MYSQL_PASS" \
  --single-transaction --master-data=2 --routines --triggers --events \
  --hex-blob --quick --databases $DATABASES \
  | gzip > "$BACKUP_DIR/full_${DATE}.sql.gz"

# 刪除舊備份
find "$BACKUP_DIR" -name "full_*.sql.gz" -mtime +$RETENTION_DAYS -delete

# 記錄日誌
echo "$(date) Full backup completed: full_${DATE}.sql.gz" >> /var/log/mysql_backup.log

加入 crontab：0 2 * * * /usr/local/bin/mysql_backup.sh（每天凌晨 2 點執行）。

phpMyAdmin 圖形化備份（僅限小數據量）

• 登錄 phpMyAdmin → 選擇數據庫 → 「匯出」→ 選擇「快速」或「自定義」（勾選結構與數據、添加 DROP TABLE、壓縮 gzip）。
• 導出文件默認保存在配置的 SaveDir，可通過 HTTP/FTP 下載。
• 警告：超過 50MB 或執行時間超過 PHP max_execution_time 會失敗，生產庫請用命令行。

總結

規範的 MySQL 備份體系 = 標準化 mysqldump 參數 + 自動化腳本 + 異地存儲 + 定期恢復演練。邏輯備份雖慢但通用性強、可跨版本遷移；核心業務建議配合 xtrabackup 物理熱備份實現 RPO=0、RTO 分鐘級目標。