核心備份命令速查
| 場景 | 命令範例 | 說明 |
|---|
| 完整備份單一資料庫 | mysqldump db_name > backup.sql | 含結構與資料 |
| 指定帳號密碼備份 | mysqldump -u root -p db_name > backup.sql | 提示輸入密碼（較安全） |
| 備份所有資料庫 | mysqldump -A -u root -p > all_dbs.sql | -A = --all-databases |
| 僅備份結構（無資料） | mysqldump -d db_name -u root -p > schema.sql | -d = --no-data |
| 僅備份資料（無結構） | mysqldump -t db_name -u root -p > data.sql | -t = --no-create-info |
| 備份指定資料表 | mysqldump -u root -p db_name tbl1 tbl2 > tables.sql | 多表以空格分隔 |
| 含 DROP TABLE 陳述式 | mysqldump --add-drop-table db_name > backup.sql | 還原前自動刪除舊表 |
| 壓縮備份（管道） | mysqldump -u root -p db_name | gzip > backup.sql.gz | 節省磁碟空間 |
還原操作
# 還原單一資料庫（需先建立資料庫）
mysql -u root -p db_name < backup.sql
# 還原所有資料庫
mysql -u root -p < all_dbs.sql
# 直接在 MySQL 客戶端執行
mysql> SOURCE /path/to/backup.sql;
進階參數與最佳實踐
一致性備份關鍵參數
# 單交易一致性快照（InnoDB 推薦）
mysqldump --single-transaction --master-data=2 -u root -p db_name > backup.sql
# 參數解釋：
# --single-transaction: 在 REPEATABLE READ 隔離級別下開啟交易，獲得一致性視圖（僅適用 InnoDB）
# --master-data=2: 在輸出中以註釋形式記錄 Binlog 位點（CHANGE MASTER TO），用於從庫搭建
# --routines: 匯出存儲過程/函數
# --triggers: 匯出觸發器（預設開啟）
# --events: 匯出事件排程器任務
# --hex-blob: 二進制欄位以十六進制輸出，避免編碼問題
大數據量優化技巧
- 分表並行匯出：配合
xargs -P 或 GNU Parallel 多線程加速
- 避免鎖表：InnoDB 必加
--single-transaction；MyISAM 只能 --lock-tables（會阻塞寫入）
- 分塊匯入：大 SQL 檔拆分為多個小檔，減少單次交易體積與回滾風險
--quick（預設開啟）：逐行讀取而非緩衝整個結果集，降低記憶體佔用
結構變更常用 ALTER 語句
| 操作 | 語法範例 |
|---|
| 重命名欄位 | ALTER TABLE tbl CHANGE old_col new_col INT; |
| 修改欄位型別（保留名稱） | ALTER TABLE tbl CHANGE col col BIGINT NOT NULL; |
| 重命名資料表 | ALTER TABLE old_tbl RENAME new_tbl; |
| 新增欄位 | ALTER TABLE tbl ADD col_name VARCHAR(50); |
| 刪除欄位 | ALTER TABLE tbl DROP col_name; |
常見問題排查
| 現象 | 原因 | 對策 |
|---|
還原報錯 Table already exists | 目標庫已有同名表 | 備份加 --add-drop-table，或先 DROP DATABASE |
| 還原資料截斷/型別不符 | 目標表結構與備份不一致 | 確保目標表結構相容，或僅導入資料 -t |
| 備份耗時極長、佔用大量記憶體 | 大表無 --quick、緩衝過大 | 確認 --quick 生效，調整 --max-allowed-packet |
| GTID 複寫環境還原後主從報錯 | 備份含 GTID 資訊衝突 | 使用 --set-gtid-purged=OFF 或匯入前 RESET MASTER |
自動化備份腳本範例
#!/bin/bash
# /usr/local/bin/mysql_backup.sh
BACKUP_DIR="/backup/mysql"
DATE=$(date +%F_%H-%M)
DB_USER="backup_user"
DB_PASS="xxxxxx"
DBS="db1 db2 db3" # 或留空代表所有
mkdir -p $BACKUP_DIR
for DB in $DBS; do
mysqldump --single-transaction --master-data=2 \
--routines --triggers --events --hex-blob \
-u$DB_USER -p$DB_PASS $DB | gzip > $BACKUP_DIR/${DB}_${DATE}.sql.gz
# 保留最近 7 天
find $BACKUP_DIR -name "${DB}_*.sql.gz" -mtime +7 -delete
done
加入 crontab -e：0 2 * * * /usr/local/bin/mysql_backup.sh 每天凌晨 2 點執行。
