資料備份是 DBA 核心職責。MySQL 提供邏輯備份工具 mysqldump 與檔案級物理備份兩大路徑。本文詳細解析 mysqldump 關鍵參數、常用備份/還原指令、以及檔案級複製的適用場景與風險。
mysqldump [OPTIONS] database [tables] > backup.sql
# 不指定 tables 則備份整庫
| 參數 | 說明 |
|---|---|
--opt | 組合參數:--quick --add-drop-table --add-locks --extended-insert --lock-tables,產出最快匯入效能的 SQL (預設開啟) |
--single-transaction | InnoDB 專用:啟動一致性快照,不鎖表,適合線上熱備份 |
--master-data=1 | 在輸出檔寫入 CHANGE MASTER TO 陳述式,記錄 Binlog 位置,利於從庫搭建 |
--quick / -q | 不緩衝查詢結果,直接串流輸出,防大表 OOM |
--extended-insert / -e | 多行 INSERT 語法,縮小檔案、加快匯入 |
--add-drop-table | 每個 CREATE TABLE 前加 DROP TABLE,還原時自動重建 |
--lock-tables / -l | 備份前鎖定所有表 (MyISAM 必須);InnoDB 用 --single-transaction 替代 |
--no-data / -d | 僅匯出結構 (Schema),不含資料 |
--no-create-info / -t | 僅匯出資料,不含 CREATE TABLE |
--where / -w | 只備份符合條件的列,如 -w "id>1000" |
--compress / -C | 客戶端/伺服器間壓縮傳輸 |
--host / -h, --port / -P, --user / -u, --password / -p | 連線參數 |
# 1. 完整線上熱備份 (InnoDB),含 Binlog 位置,壓縮輸出
mysqldump --single-transaction --master-data=1 --quick --extended-insert --compress db_name | gzip > db_$(date +%F).sql.gz
# 2. 僅結構備份 (Schema 版控)
mysqldump --no-data --routines --triggers --events db_name > schema.sql
# 3. 分表匯出 (每表一個 .sql + .txt),需在伺服器本機執行
mysqldump --tab=/backup/dir --fields-terminated-by=',' --fields-enclosed-by='"' db_name
# 建立目標資料庫
mysqladmin create target_db
# 還原 (若備份含 CREATE DATABASE 則無需建庫)
mysql target_db < backup.sql
# 或壓縮檔直接管道
zcat backup.sql.gz | mysql target_db
直接複製 datadir 下資料庫目錄 (如 C:\mysql\data\TestA) 到另一台同版本 MySQL 的 datadir,重啟或 FLUSH TABLES 後即可識別。
FLUSH TABLES WITH READ LOCK 確保一致性;不支援跨版本升級;非官方支援,生產環境慎用。xtrabackup 實體增量)。Percona XtraBackup 實體熱備份,支援流式傳輸、壓縮、並行、部分備份,企業級首選。備份無價,唯有經過驗證的還原流程才算真正的備份。