兩大備份流派

方式	優點	缺點
SELECT ... INTO OUTFILE / LOAD DATA INFILE	純資料、格式可控、跨表匯入靈活	無結構定義、需手工建表、權限要求高（FILE 權限）
mysqldump	含結構與資料、標準工具、支援遠端、壓縮、過濾	檔案較大、還原較慢

mysqldump 核心場景速查

# 1. 備份整庫
mysqldump -u user -p db_name > db.sql

# 2. 備份單表
mysqldump -u user -p db_name table_name > table.sql

# 3. 僅備份結構（不含資料）
mysqldump -u user -p -d --add-drop-table db_name > schema.sql

# 4. 多庫備份
mysqldump -u user -p --databases db1 db2 > multi.sql

# 5. 全例備份（含 mysql 系統庫）
mysqldump -u root -p --all-databases > full.sql

# 6. 遠端備份
mysqldump -h 192.168.1.100 -u user -p db_name | gzip > db.sql.gz

# 7. 只備份特定條件資料
mysqldump -u user -p db_name table_name --where="status=1 AND created_at>'2024-01-01'" > filtered.sql

還原操作

# 方式一：mysql 客戶端 source（適合中小檔案）
mysql -u root -p
CREATE DATABASE db_name;
USE db_name;
SET NAMES utf8mb4;
SOURCE /path/to/db.sql;

# 方式二：管道導入（適合大檔案、自動化）
mysql -u root -p db_name < db.sql

# 方式三：壓縮檔直接導入
gunzip -c db.sql.gz | mysql -u root -p db_name

關鍵參數詳解（節選高頻）

參數	說明	建議
--single-transaction	InnoDB 熱備份（一致性快照，不鎖表）	InnoDB 必加
--master-data=2	輸出 Binlog 位置（註釋形式），用於 PITR	主庫備份必加
--flush-logs	備份前切換 Binlog，配合增量備份	增量備份策略必加
--routines --triggers --events	包含存儲過程/觸發器/事件	預設不含，生產必加
--set-gtid-purged=OFF	避免 GTID 資訊干擾異構還原	跨版本/架構還原時加
--compress	客戶端-伺服器傳輸壓縮	遠端備份建議開啟
--hex-blob	二進位欄位以十六進位導出	含 Blob/Binary 必加

增量備份策略（配合 Binlog）

1. 週日全量：mysqldump --single-transaction --flush-logs --master-data=2 --all-databases > full_sun.sql
2. 每日增量：mysqladmin flush-logs 產生新 Binlog，備份前一個 Binlog 檔案
3. 恢復：先導入全量，再依序 mysqlbinlog binlog.xxx | mysql 重放增量

自動化腳本骨架

#!/bin/bash
BACKUP_DIR="/backup/mysql/$(date +%F)"
mkdir -p "$BACKUP_DIR"
mysqldump --single-transaction --master-data=2 --routines --triggers --events \
  -u backup_user -p"$PASS" --all-databases | gzip > "$BACKUP_DIR/full_$(date +%H%M).sql.gz"
find /backup/mysql -type f -mtime +7 -name '*.gz' -delete  # 保留 7 天

配合 crontab 定時執行，構建「全量+增量+保留策略」的完整備份體系。