備份指令大全
單一資料庫備份
# 純 SQL
mysqldump -u root -p db_name > /backup/db_name_$(date +%F).sql
# 壓縮備份 (推薦)
mysqldump -u root -p db_name | gzip > /backup/db_name_$(date +%F).sql.gz
全庫備份 (含 mysql 系統庫)
mysqldump --all-databases -u root -p | gzip > /backup/all_dbs_$(date +%F).sql.gz
指定表備份
mysqldump -u root -p db_name table1 table2 > /backup/tables.sql
只備份結構 / 只備份資料
# 結構
mysqldump --no-data -u root -p db_name > schema.sql
# 資料
mysqldump --no-create-info -u root -p db_name > data.sql
還原/匯入方式比較
| 方式 | 指令 | 優點 | 缺點 |
|---|
| 1. 重導向 | mysql -u root -p db_name < backup.sql | 簡單、單行 | 錯誤時難中斷、大檔案記憶體壓力 |
| 2. source 命令 (推薦) | mysql -u root -p db_name -e "source /backup/backup.sql" | 可見進度、支援多語句、易中斷 | 需進入 mysql 互動模式 |
| 3. 管線 + pv 監控 | pv backup.sql | mysql -u root -p db_name | 顯示進度條、速率、ETA | 需安裝 pv |
| 4. 壓縮檔直匯 | zcat backup.sql.gz | mysql -u root -p db_name | 省磁碟、單指令 | 無法隨機存取、解壓失敗難處理 |
實戰建議
- 自動化：寫入
/etc/cron.daily/mysql-backup，保留 7/30 天輪轉。
- 驗證：定期在測試環境
source 還原，檢查 CHECKSUM TABLE。
- 權限：備份布號僅需
SELECT, LOCK TABLES, SHOW VIEW, EVENT, TRIGGER。
- 一致性：InnoDB 加
--single-transaction --master-data=2 獲得一致性快照 (不鎖表)。
- 異地：備份檔同步至異地存儲 (S3/NAS/異地機房)。
常用參數速查
--single-transaction # InnoDB 一致性備份 (不鎖表)
--master-data=2 # 記錄 Binlog 位置 (CHANGE MASTER 註解)
--routines # 包含存儲過程/函數
--triggers # 包含觸發器
--events # 包含事件
--hex-blob # 二進位欄位用十六進位 (避免編碼問題)
--quick # 大表串流輸出
--lock-tables=false # 避免鎖表 (需配合 single-transaction)
