mysqldump：最通用的邏輯備份工具

常用導出組合

需求	命令範例
僅結構	mysqldump -uroot -p -d dbname > dbname_schema.sql
僅數據	mysqldump -uroot -p -t dbname > dbname_data.sql
結構+數據	mysqldump -uroot -p dbname > dbname_full.sql
指定表	mysqldump -uroot -p dbname tbl1 tbl2 > tables.sql
單一表結構/數據/全量	加 -d/-t 或無參數

進階參數（生產必備）

mysqldump -uroot -p \
  --single-transaction \      -- InnoDB 一致性備份，不加鎖
  --master-data=2 \           -- 記錄 Binlog 位點（從庫建立必備）
  --routines --triggers \     -- 包含存儲過程/觸發器
  --events \                  -- 包含事件調度器
  --hex-blob \                -- 二進制欄位十六進制導出
  --set-gtid-purged=OFF \     -- 避免 GTID 污染目標庫
  --default-character-set=utf8mb4 \
  dbname > dbname_full.sql

還原

# 建庫並指定字元集
CREATE DATABASE dbname DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

# 方式一：客戶端管道導入
mysql -uroot -p dbname < dbname_full.sql

# 方式二：源命令（需先 USE dbname）
SOURCE /path/dbname_full.sql;

大數據量高效導出導入：SELECT INTO OUTFILE / LOAD DATA

繞過 SQL 層，直接操作文本文件，速度比 mysqldump 快 5~10 倍。

導出（需 FILE 權限、secure_file_priv 目錄可寫）

SELECT * INTO OUTFILE '/tmp/export.txt'
  FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
  LINES TERMINATED BY '\n'
  FROM `orders`
  WHERE `created_at` < '2023-01-01';

導入（同樣需權限與目錄）

LOAD DATA INFILE '/tmp/export.txt'
  INTO TABLE `orders`
  FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
  LINES TERMINATED BY '\n'
  (`order_id`, `user_id`, `amount`, `created_at`);

跨機器導入：LOAD DATA LOCAL INFILE

# 客戶端啟用 local-infile
mysql --local-infile=1 -uroot -p -h remote_host dbname

# SQL 中指定 LOCAL
LOAD DATA LOCAL INFILE '/local/path/export.txt' INTO TABLE `orders` ...

• PHP 需在 mysqli_real_connect 設定 MYSQLI_CLIENT_LOCAL_FILES 標誌。
• 報錯 The used command is not allowed with this MySQL version 通常是客戶端庫未編譯支援或伺服器禁用 local_infile。

表結構查看輔助命令

DESC `wp_users`;
SHOW CREATE TABLE `wp_users`\G;

避坑清單

• 字元集一致性：導出導入全鏈路指定 utf8mb4，避免亂碼。
• 外鍵約束：導入前 SET FOREIGN_KEY_CHECKS=0;，導入後恢復。
• 自增 ID 衝突：合併數據時需重新分配 ID 或使用 REPLACE INTO。
• 權限問題：secure_file_priv 限制導出目錄，檢查 SHOW VARIABLES LIKE 'secure_file_priv';。

總結

• 中小庫（< 50GB）：mysqldump --single-transaction 最省心。
• 大表（> 100GB）：SELECT INTO OUTFILE + LOAD DATA 並行分片導入。
• 跨版本/跨引擎遷移：優先用邏輯備份，物理備份（xtrabackup）需版本、配置高度一致。