MySQL 備份核心工具 mysqldump 功能強大但參數繁多。本文整理從單表到全庫、從本地到遠程、從文本到壓縮、從手動到自動化的完整備份腳本庫，並深度解析關鍵參數，助你構建企業級備份體系。

一、mysqldump 核心參數速查表

參數	作用	建議
--single-transaction	InnoDB 熱備份，啟動一致性快照，不鎖表	✅ 生產必加
--routines	備份預存程序/函數	✅
--triggers	備份觸發器	✅
--events	備份定時任務	✅
--quick / -q	流式讀取，不緩存整個結果集到記憶體	✅ 大表必加
--hex-blob	二進制欄位用十六進制導出，避免字元集問題	✅
--add-drop-table	建表前加 DROP TABLE，便於覆蓋還原	按需
--no-data / -d	只備份結構（Schema）	遷移結構用
--no-create-info / -t	只備份資料，不含建表語句	資料遷移用
--databases / -B	多庫備份，輸出包含 CREATE DATABASE/USE	多庫備份
--all-databases / -A	備份所有資料庫	全例備份
--master-data=2	輸出 CHANGE MASTER TO 註釋（從庫同步座標）	主從搭建
--flush-logs	備份前切換 Binlog，配合增量備份	增量備份基點

二、常用備份命令大全

1. 單庫完整備份（生產標準）

mysqldump -h localhost -u root -p \
  --single-transaction --routines --triggers --events \
  --quick --hex-blob --add-drop-table \
  my_database > backup_$(date +%F_%H-%M-%S).sql

2. 單庫壓縮備份（節省 70-90% 空間）

mysqldump -u root -p --single-transaction --quick my_database | gzip > backup_$(date +%F).sql.gz

# 解壓還原
gunzip < backup.sql.gz | mysql -u root -p my_database

3. 指定表備份

mysqldump -u root -p my_database users orders products > backup_tables.sql

4. 多庫備份

mysqldump -u root -p --databases db1 db2 db3 > multi_db_backup.sql

5. 僅備份結構（無資料）

mysqldump -u root -p --no-data --databases db1 db2 > schema_only.sql

6. 全例備份（含 mysql 系統庫）

mysqldump -u root -p --all-databases --single-transaction --routines --triggers --events > full_instance_backup.sql

7. 遠程備份到另一台 MySQL（無中間檔案）

mysqldump -h source_host -u root -p --single-transaction my_database | \
  mysql -h target_host -u root -p -C my_database

8. Windows Bat 定時備份腳本

@echo off
set MYSQL_BIN="C:\Program Files\MySQL\MySQL Server 8.0\bin\mysqldump.exe"
set DB_USER=root
set DB_PASS=your_password
set DB_NAME=my_database
set BACKUP_DIR=D:\MySQL_Backup

set YMD=%date:~0,4%%date:~5,2%%date:~8,2%
set HMS=%time:~0,2%%time:~3,2%%time:~6,2%
set HMS=%HMS: =0%

mkdir "%BACKUP_DIR%\%YMD%" 2>nul

%MYSQL_BIN% -u%DB_USER% -p%DB_PASS% --single-transaction --routines --triggers --events --quick --hex-blob %DB_NAME% > "%BACKUP_DIR%\%YMD%\%DB_NAME%_%YMD%_%HMS%.sql"

if %errorlevel% equ 0 (
  echo [%date% %time%] Backup OK
) else (
  echo [%date% %time%] Backup FAILED
  exit /b 1
)

REM 保留 7 天
forfiles /p "%BACKUP_DIR%" /s /m *.* /d -7 /c "cmd /c del @path" 2>nul

9. Linux Shell + Cron 定時備份

#!/bin/bash
# /usr/local/bin/mysql_daily_backup.sh
# Cron: 0 2 * * * /usr/local/bin/mysql_daily_backup.sh >> /var/log/mysql_backup.log 2>&1

set -euo pipefail

DB_USER="root"
DB_PASS="your_password"
DB_NAME="my_database"
BACKUP_DIR="/backup/mysql"
DATE=$(date +%F_%H-%M-%S)
FILE="${DB_NAME}_${DATE}.sql.gz"

mkdir -p "${BACKUP_DIR}/$(date +%F)"

mysqldump -u${DB_USER} -p${DB_PASS} \
  --single-transaction --routines --triggers --events --quick --hex-blob \
  ${DB_NAME} | gzip > "${BACKUP_DIR}/$(date +%F)/${FILE}"

if [[ ${PIPESTATUS[0]} -eq 0 ]]; then
  echo "[$(date)] OK: ${FILE}"
else
  echo "[$(date)] FAILED" >&2
  exit 1
fi

find "${BACKUP_DIR}" -type f -name "*.sql.gz" -mtime +7 -delete

三、還原操作指南

備份類型	還原命令
純 SQL 檔	mysql -u root -p db_name < backup.sql
壓縮 SQL (.gz)	gunzip < backup.sql.gz | mysql -u root -p db_name
多庫備份 (含 CREATE DATABASE)	mysql -u root -p < multi_db_backup.sql
全例備份	mysql -u root -p < full_backup.sql
只還原某表 (從全量備份中提取)	sed -n '/^CREATE TABLE `table_name`/,/^CREATE TABLE/p' full_backup.sql | mysql -u root -p db_name

四、進階備份策略

1. 邏輯 + 物理結合： 每日 mysqldump 全量 + 每小時 Binlog 增量（或 xtrabackup 物理增量）。
2. 異地多副本： 本地 + 異地機房 + 雲端對象存儲（S3/OSS/GCS），至少 3 副本。
3. 加密傳輸與靜態加密： mysqldump 管道傳輸用 SSH/SSL，落盤用 OpenSSL/GnuPG 加密。
4. 備份驗證自動化： 定期在測試環境恢復、跑 CHECKSUM TABLE、核心業務查詢驗證。

五、總結

mysqldump 參數組合決定備份質量：**生產環境必加 --single-transaction --routines --triggers --events --quick --hex-blob**。配合壓縮、加密、異地存儲、定時任務、自動驗證，構建「可恢復、可審計、可擴展」的備份體系。記住：**未經驗證的備份 = 沒有備份。**