核心概念對應

操作	對應動作	核心命令
匯入 / 還原	將 SQL 檔案執行到資料庫	source (在 mysql shell 內)
匯出 / 備份	將資料庫輸出為 SQL 檔案	mysqldump (在系統 shell 內)

匯入 / 還原 (source)

# 1. 進入 mysql shell
mysql -u root -p

# 2. 建立/選擇目標資料庫
CREATE DATABASE IF NOT EXISTS target_db CHARACTER SET utf8mb4;
USE target_db;

# 3. 執行匯入
source /absolute/path/to/backup.sql;
-- 或相對路徑 (相對 mysql 啟動目錄)
source ./backup.sql;

優點：支援超大檔、直接由 Server 端解析執行、無 PHP 記憶體/上傳限制。

匯出 / 備份 (mysqldump)

基本語法

mysqldump -u root -p [options] db_name [table1 table2...] > /path/backup.sql

常用組合參數 (生產級)

mysqldump -u root -p \
  --single-transaction \      # InnoDB 熱備份，不鎖表
  --routines --triggers --events \  # 匯出存儲過程/觸發器/事件
  --master-data=2 \           # 輸出 CHANGE MASTER 註解 (建從庫用)
  --hex-blob \                # 二進制欄位用十六進制
  --quick \                   # 逐行讀取，不緩衝整張表 (大表必加)
  --compress \                # 壓縮傳輸 (遠程備份)
  db_name > backup_$(date +%F_%H%M).sql

只匯出結構 / 只匯出資料

# 只結構
mysqldump -u root -p --no-data db_name > schema.sql

# 只資料
mysqldump -u root -p --no-create-info db_name > data.sql

# 指定表
mysqldump -u root -p db_name table1 table2 > subset.sql

壓縮管道 (節省磁碟/網路)

mysqldump -u root -p db_name | gzip > db_$(date +%F).sql.gz
# 解壓還原
gunzip < db_2024-01-15.sql.gz | mysql -u root -p db_name

自動化備份腳本範例 (每日全量 + Binlog 增量)

#!/bin/bash
# /usr/local/bin/mysql_backup.sh
BACKUP_DIR="/backup/mysql"
DB_USER="backup_user"
DB_PASS="StrongPass"
DB_NAME="prod_db"
DATE=$(date +%F_%H%M)

# 全量備份 (每日 02:00)
mysqldump -u$DB_USER -p$DB_PASS --single-transaction --routines --triggers --events --master-data=2 --hex-blob --quick $DB_NAME | gzip > $BACKUP_DIR/full_$DATE.sql.gz

# 保留 30 天
find $BACKUP_DIR -name "full_*.sql.gz" -mtime +30 -delete

# Binlog 增量 (每小時) - 需另行腳本配合 mysqlbinlog

crontab -e → 0 2 * * * /usr/local/bin/mysql_backup.sh >/dev/null 2>&1

還原驗證 (必做)

1. 建立臨時資料庫：CREATE DATABASE verify_db;
2. 還原：gunzip < backup.sql.gz | mysql -u root -p verify_db
3. 抽樣核對：SELECT COUNT(*) FROM core_table;、CHECKSUM TABLE core_table;
4. 定期演練：每季至少一次全量還原演練