MySQL 數據庫備份實戰（二）：mysqldump 進階參數與跨機遷移

mysqldump 進階用法：單表備份、--opt 優化、壓縮、跨機流式遷移、檢查點鎖定

本文深入 mysqldump 實戰技巧：分表備份、--add-drop-table 避免恢復衝突、--opt/--quick 加速、--lock-tables/--flush-logs 構建備份檢查點、--compress 跨網傳輸優化、--delayed 減輕目標庫壓力。

mysqldump 輸出結構

# MySQL Dump 6.0
# Host: localhost  Database: samp_db
# Table structure for table 'absence'
CREATE TABLE absence (
  student_id int(10) unsigned DEFAULT '0' NOT NULL,
  date date DEFAULT '0000-00-00' NOT NULL,
  PRIMARY KEY (student_id,date)
);
# Dumping data for table 'absence'
INSERT INTO absence VALUES (3,'1999-09-03');
INSERT INTO absence VALUES (5,'1999-09-03');
...

常用進階參數

實戰場景

1. 單表/多表分文件備份

# 僅備份核心業務表
mysqldump samp_db student score event absence > gradebook.sql
mysqldump samp_db member president > hist-league.sql

2. 跨機流式遷移（無中間文件）

# 目標機建庫
mysqladmin -h target_host -u root -p create samp_db

# 流式傳輸
mysqldump --opt samp_db | mysql -h target_host -u root -p samp_db

# 後續增量同步
mysqldump --opt --add-drop-table samp_db | mysql -h target_host -u root -p samp_db

3. 備份檢查點構建（生產環境推薦）

mysqldump --quick --single-transaction huge_table > huge_table.sql
# --single-transaction 替代 --lock-tables（InnoDB 適用），不阻塞寫入

⚠️ --opt 的代價

--opt 默認啟用 --lock-tables，備份期間全庫只讀。若業務極其敏感，可拆分為單表備份或使用 --single-transaction（需 InnoDB）。

壓縮傳輸範例