一、基礎備份與還原指令

1. 一般大小資料庫

# 備份單一資料庫
mysqldump -u root -p dbname > dbname_$(date +%Y%m%d).sql

# 備份並壓縮（節省空間）
mysqldump -u root -p dbname | gzip > dbname_$(date +%Y%m%d).sql.gz

# 還原
mysql -u root -p dbname < dbname_20240115.sql

# 直接解壓還原
gzip -d < dbname_20240115.sql.gz | mysql -u root -p dbname

2. 多資料庫/全備份

# 所有資料庫
mysqldump -u root -p --all-databases > all_dbs_$(date +%Y%m%d).sql

# 指定多個資料庫
mysqldump -u root -p --databases db1 db2 > multi_dbs.sql

二、Crontab 定時自動備份腳本

建立備份腳本 /usr/local/bin/backup_mysql.sh

#!/bin/bash
BACKUP_DIR="/backup/mysql"
DATE=$(date +%Y%m%d_%H%M%S)
DB_USER="root"
DB_PASS="your_password"
DB_NAME="your_database"

mkdir -p "$BACKUP_DIR"

# 備份並壓縮
mysqldump -u"$DB_USER" -p"$DB_PASS" --single-transaction --routines --triggers "$DB_NAME" | \
gzip > "$BACKUP_DIR/${DB_NAME}_${DATE}.sql.gz"

# 保留最近 7 天備份
find "$BACKUP_DIR" -name "${DB_NAME}_*.sql.gz" -mtime +7 -delete

echo "[$(date)] Backup completed: ${DB_NAME}_${DATE}.sql.gz"

設定執行權限與排程

chmod +x /usr/local/bin/backup_mysql.sh

# 編輯 crontab（每天凌晨 3 點執行）
crontab -e
# 加入：
0 3 * * * /usr/local/bin/backup_mysql.sh >> /var/log/mysql_backup.log 2>&1

三、超大資料庫（GB 級）匯入利器：BigDump

phpMyAdmin 受限於 PHP 上傳大小與執行時間，無法處理超大 SQL。BigDump 採分批次執行方式，可突破限制。

操作步驟

1. 下載並設定：解壓取得 bigdump.php，修改資料庫連線參數（約第 38 行）：

   $db_server = 'localhost';
   $db_name = 'target_db';
   $db_username = 'root';
   $db_password = 'password';
   $db_connection_charset = 'utf8';  // 若資料為 UTF-8

2. 上傳檔案：將 bigdump.php 與 SQL 檔（或 .gz/.zip）上傳至同一目錄（建議 /tmp）。大檔案請用 FTP 上傳後再解壓。
3. 瀏覽器啟動：訪問 https://yourdomain.com/tmp/bigdump.php，介面會列出偵測到的 SQL 檔。
4. 開始匯入：點選「Start Import」，工具會自動分批執行，顯示進度條與已處理位元組數。
5. 完成確認：出現 Congratulations: End of file reached, assuming OK 即代表成功。
6. 清理現場：務必刪除 bigdump.php 與 SQL 備份檔，避免資安風險。

四、還原語法速查表

# 建立目標資料庫
mysqladmin -u root -p create target_db

# 還原
mysql -u root -p target_db < backup.sql

# 簡寫
mysql -u root -p dbname < backup.sql

五、生產環境建議

• 使用 --single-transaction 確保 InnoDB 一致性備份（不鎖表）
• 啟用 --routines --triggers 備份預存程序與觸發器
• 異地備份：搭配 rsync 或 scp 同步至遠端伺服器
• 定期演練還原流程，驗證備份可用性