MySQL 資料庫備份與恢復實戰：mysqldump 與 SELECT INTO OUTFILE

MySQL 資料庫備份與恢復實戰：mysqldump 與 SELECT INTO OUTFILE

備份是資料庫最後一道防線。本文實戰演示 mysqldump 邏輯備份（庫/表/結構/數據分離）與 SELECT ... INTO OUTFILE 文本導出兩大體系，含恢復命令與自動化腳本思路。

一、mysqldump 邏輯備份

1. 基礎備份命令

2. 結構與數據分離

# 僅導出結構（無數據）
mysqldump -u root -p -d db t1 > struct.sql

# 僅導出數據（無建表語句）
mysqldump -u root -p -t db t1 > data.sql

3. 壓縮與自動化腳本

#!/bin/bash
BakDir=/backup/mysql
Date=$(date +%Y%m%d)
mysqldump -u root -p'password' db_name --single-transaction | gzip > $BakDir/db_$Date.sql.gz
# 保留 7 天
find $BakDir -name "*.sql.gz" -mtime +7 -delete

二、SELECT INTO OUTFILE / LOAD DATA INFILE

適用於大量數據高速導出導入（比 SQL 文本快 5-10 倍），但需 FILE 權限且文件落在 Server 端。

1. 導出

-- 導出全表數據（制表符分隔、換行符結尾）
SELECT * FROM db.t INTO OUTFILE '/tmp/t.txt' 
FIELDS TERMINATED BY '\t' OPTIONALLY ENCLOSED BY '"' 
LINES TERMINATED BY '\n';

-- 導出指定列
SELECT id, name FROM db.t INTO OUTFILE '/tmp/t.txt';

2. 導入

LOAD DATA INFILE '/tmp/t.txt' INTO TABLE db.t
FIELDS TERMINATED BY '\t' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
(id, name);  -- 指定列順序

三、恢復操作

1. 命令行恢復

# 恢復單表
mysql -u root -p db < backup.sql

# 恢復全庫（含 CREATE DATABASE）
mysql -u root -p < full_backup.sql

2. 客戶端內部恢復

mysql> USE db;
mysql> SOURCE /path/backup.sql;
-- 或
mysql> \. /path/backup.sql

四、最佳實踐清單

• 備份策略：每日全量 + Binlog 增量（PITR）
• 驗證機制：每周隨機抽取 1 個備份文件在測試環境全量恢復
• 權限最小化：備份賬號僅需 SELECT, LOCK TABLES, SHOW VIEW, EVENT, TRIGGER, RELOAD, FILE
• 異地存儲：備份文件同步至 OSS/S3/異地 FTP
• 加密傳輸：生產備份文件加密存儲（GPG/AES）

「備份不驗證，等於沒備份。」建立「備份→驗證→恢復演練」閉環，才是真正的數據安全。