MySQL 數據庫備份恢復兩大方案：mysqldump 邏輯備份與 SELECT INTO OUTFILE/LOAD DATA 物理文件備份對比實戰

方案一：mysqldump 邏輯備份 (標準、通用、跨版本)

核心優勢

• 導出 **SQL 文本** (CREATE TABLE + INSERT)，可讀、可編輯、跨版本/引擎遷移。
• 支持 `--single-transaction` (InnoDB 一致性視圖)、`--master-data=1` (binlog 位置)、`--routines`/`--triggers`/`--events`。
• 壓縮管道：`mysqldump ... | gzip > backup.sql.gz`。

常用命令模板

# 全量備份 (含存儲過程/觸發器/事件)
mysqldump -uroot -p --single-transaction --master-data=2 --routines --triggers --events --all-databases | gzip > full_$(date +%F).sql.gz

# 單庫備份
mysqldump -uroot -p --single-transaction db_name | gzip > db_$(date +%F).sql.gz

# 只導結構
mysqldump -uroot -p --no-data db_name > schema.sql

# 只導數據 (無 CREATE TABLE)
mysqldump -uroot -p --no-create-info db_name > data.sql

恢復

# 解壓並導入
gunzip < backup.sql.gz | mysql -uroot -p
# 或指定庫
mysql -uroot -p db_name < backup.sql

方案二：SELECT INTO OUTFILE / LOAD DATA 文件備份 (高性能、大數據量)

原理

• **導出：** `SELECT * INTO OUTFILE '/path/tbl.txt' FROM tbl;` → 生成制表符分隔文本文件 (服務器端文件系統)。
• **導入：** `LOAD DATA INFILE '/path/tbl.txt' INTO TABLE tbl;` → 批量流式載入，**極快** (繞過 SQL 解析、索引逐行維護、事務日志)。

PHP 實現自動化備份腳本 (核心邏輯)

query("SHOW TABLES");
$backup_dir = '/backup/mysql/'.date('Y-m-d').'/';
mkdir($backup_dir, 0755, true);

while ($row = $tables->fetch_row()) {
    $table = $row[0];
    $file = $backup_dir . $table . '.txt';
    // 導出 (需 FILE 權限、secure_file_priv 允許目錄)
    $db->query("SELECT * INTO OUTFILE '$file' 
        FIELDS TERMINATED BY '\t' OPTIONALLY ENCLOSED BY '\"' 
        LINES TERMINATED BY '\n' FROM `$table`");
}

// 壓縮打包
exec("tar czf {$backup_dir}.tar.gz -C {$backup_dir} .");
?>

恢復腳本邏輯

query("TRUNCATE TABLE `$table`");
    // 高速載入
    $db->query("LOAD DATA INFILE '$file' INTO TABLE `$table` 
        FIELDS TERMINATED BY '\t' OPTIONALLY ENCLOSED BY '\"' 
        LINES TERMINATED BY '\n'");
}
?>

兩大方案深度對比

生產環境最佳組合策略

1. **每日全量：** `mysqldump --single-transaction --master-data=2` 上傳對象存儲 (S3/OSS)。
2. **增量備份：** 啟用 `binlog` + `mysqlbinlog --read-from-remote-server` 定期拉取增量。
3. **大表專項：** 核心大表 (日志/訂單/流水) 週度 `SELECT INTO OUTFILE` 物理備份，縮短 RTO。
4. **定期演練：** 季度隨機抽取備份在測試環境全量恢復驗證。