mysqldump 備份與恢復實戰：從基礎命令到壓縮、遠程傳輸、自動化腳本

mysqldump 是 MySQL 官方邏輯備份工具，功能強大、跨版本兼容、無第三方依賴。本文從單庫備份到生產級自動化腳本，構建完整備份體系。

基礎備份與恢復

# 1. 單庫備份 (含結構+數據)
mysqldump -u root -p db_name > db_name_$(date +%F).sql

# 2. 壓縮備份 (節省 70%+ 空間)
mysqldump -u root -p db_name | gzip > db_name_$(date +%F).sql.gz

# 3. 恢復 (需先建庫)
mysql -u root -p -e "CREATE DATABASE db_name;"
mysql -u root -p db_name < db_name_2024-01-15.sql

# 4. 從壓縮文件直接恢復
gzip -dc db_name_2024-01-15.sql.gz | mysql -u root -p db_name

常用參數速查表

生產級備份腳本模板

#!/bin/bash
# backup_mysql.sh - 每日全量 + Binlog 增量
set -euo pipefail

# ==== 配置區 ====
DB_USER="backup_user"
DB_PASS="*****"
DB_HOST="127.0.0.1"
BACKUP_DIR="/backup/mysql"
RETENTION_DAYS=7
LOG_FILE="${BACKUP_DIR}/backup.log"
MY_CNF="/etc/my.cnf"  # 含 [client] password 配置，避免明文

# ==== 函數 ====
log() { echo "[$(date '+%F %T')] $*" | tee -a "${LOG_FILE}"; }

# ==== 主流程 ====
DATE=$(date +%F)
FULL_FILE="${BACKUP_DIR}/full_${DATE}.sql.gz"
BINLOG_DIR="${BACKUP_DIR}/binlog"

mkdir -p "${BACKUP_DIR}" "${BINLOG_DIR}"

log "=== 開始全量備份 ==="
mysqldump --defaults-file="${MY_CNF}" \
  --host="${DB_HOST}" --user="${DB_USER}" \
  --single-transaction --master-data=2 \
  --routines --triggers --events --hex-blob --quick \
  --all-databases | gzip > "${FULL_FILE}"

if [[ ${PIPESTATUS[0]} -eq 0 ]]; then
  log "全量備份成功: ${FULL_FILE} ($(du -h ${FULL_FILE} | cut -f1))"
else
  log "全量備份失敗！" && exit 1
fi

# 刪除舊備份
find "${BACKUP_DIR}" -name "full_*.sql.gz" -mtime +${RETENTION_DAYS} -delete
log "清理 ${RETENTION_DAYS} 天前備份完成"

# Binlog 增量 (需開啟 log-bin)
log "=== 刷新 Binlog 並備份增量 ==="
mysql --defaults-file="${MY_CNF}" -h"${DB_HOST}" -u"${DB_USER}" -e "FLUSH LOGS;"
# 複製除最新外的所有 binlog 到備份目錄 (略，參考 90785 腳本)

log "=== 備份任務結束 ==="

遠程異地備份 (SSH + 管道)

# 推送到備份服務器 (免密鍵登錄)
mysqldump -u root -p --single-transaction --quick db_name | \
  gzip | ssh backup_user@backup_server "cat > /backup/mysql/db_name_$(date +%F).sql.gz"

# 或拉取 (備份服務器主動連接)
ssh db_server "mysqldump -u root -p --single-transaction --quick db_name | gzip" \
  > /backup/mysql/db_name_$(date +%F).sql.gz

驗證備份可用性 (必做！)

1. 校驗和：sha256sum backup.sql.gz 記錄，恢復前核對。
2. 定期演練恢復：每月在測試環境 mysql < backup.sql，驗證數據完整、應用啟動正常。
3. 監控備份大小/耗時：異常增大/縮小 → 告警排查（如誤刪表、Binlog 爆增）。

總結

1. **單庫/小表**：mysqldump --single-transaction | gzip 簡單可靠。
2. **生產核心庫**：必加 --master-data=2 --routines --triggers --events --hex-blob，配合 Binlog 增量實現 RPO=分鐘級。
3. **超大庫 (TB 級)**：邏輯備份過慢，改用 Percona XtraBackup 物理熱備份 或 文件系統快照 (LVM/ZFS/EBS)。
4. **備份未驗證 = 無備份**，納入定期演練計劃。

1. 上一篇文章： MySQL SHOW 命令完全手冊：18 大用法分類、進階技巧與避坑指南
2. 下一篇文章： MySQL 數據恢復全策略：從 myisamchk 修復到備份+Binlog 完整還原

MySQL 資料庫相關知識

MySQL 查看與修改安裝時字符集配置：SHOW VARIABLES、my.cnf 修改與重啟驗證

確認 MySQL 當前字符集配置、修改預設字符集並生效的標準

MySQL 利用 FEDERATED 引擎實現遠程表連接完整指南

前言 MySQL 的 FEDERATED 儲存引擎提供了一種

MySQL 5.0 新特性：觸發器與游標實戰——資料加密、彙總計算與存儲過程游標應用

觸發器核心概念 MySQL 5.0.2+ 引入觸發器：命名資

一致性哈希算法深度解析：原理、虛擬節點與分佈式緩存架構實戰

傳統取模算法的災難性缺陷 分佈式緩存集群N台節點，路由算法h