MySQL 數據導出備份全方案：邏輯備份、物理導出、自動化實戰

備份是數據庫生存底線。本文系統整理 MySQL 導出工具 mysqldump、SQL 導出語句、CSV 格式導出、跨主機遷移、Windows/Linux 自動化備份腳本。

方法一：SELECT ... INTO OUTFILE (服務端導出)

-- 制表符分隔、換行結尾 (默認)
SELECT * FROM tutorials_tbl INTO OUTFILE '/tmp/tutorials.txt';

-- CSV 格式：字段雙引號包裹、逗號分隔、CRLF 換行
SELECT * FROM passwd 
INTO OUTFILE '/tmp/tutorials.csv'
FIELDS TERMINATED BY ',' ENCLOSED BY '"' ESCAPED BY '"'
LINES TERMINATED BY '\r\n';

限制與權限

• 需 FILE 權限：GRANT FILE ON *.* TO 'user'@'host';
• 文件在服務器端生成，客戶端無法直接獲取
• 目標文件不能存在，防止覆蓋
• 輸出文件屬主為 mysql 用戶，權限 644

方法二：mysqldump 邏輯備份 (標準工具)

導出表數據 (無建表語句)

mysqldump -u root -p --no-create-info --tab=/tmp TUTORIALS tutorials_tbl
# 生成：tutorials_tbl.txt (數據)、tutorials_tbl.sql (建表)

導出表結構+數據 (SQL 格式)

# 單表
mysqldump -u root -p TUTORIALS tutorials_tbl > dump.sql

# 多表
mysqldump -u root -p TUTORIALS t1 t2 t3 > dump.sql

# 整庫
mysqldump -u root -p TUTORIALS > database_dump.sql

# 所有庫
mysqldump -u root -p --all-databases > all_databases.sql

常用優化參數

生產環境標準命令模板

mysqldump -h localhost -u backup_user -p \
  --single-transaction --master-data=2 \
  --routines --triggers --events \
  --hex-blob --quick --compress \
  --set-gtid-purged=OFF \
  TUTORIALS > /backup/TUTORIALS_$(date +%F_%H%M).sql

方法三：CSV 格式導出 (業務交換)

# mysqldump 導出 CSV
mysqldump -u root -p -t -T /export_dir db_name \
  --fields-terminated-by=',' \
  --fields-enclosed-by='"' \
  --fields-escaped-by='"' \
  --lines-terminated-by='\r\n'

# 命令行導出 CSV (含表頭)
mysql -h host -u user -p -D db -e "SELECT * FROM t" \
  | sed 's/\t/","/g;s/^/"/;s/$/"/;s/\n//g' > export.csv

方法四：跨主機直接遷移 (無中間文件)

# 源主機導出 | 管道傳輸 | 目標主機導入
mysqldump -h source_host -u user -p db_name \
  --single-transaction --quick --compress \
  | mysql -h target_host -u user -p db_name

Windows 自動化備份腳本

@echo off
REM backup_mysql.bat
set MYSQL_BIN="C:\wamp\mysql\bin\mysqldump.exe"
set DB_NAME=bugdb
set USER=root
set PASS=123456
set BACKUP_DIR=F:\backup\website
set DATE_STR=%date:~0,4%%date:~5,2%%date:~8,2%

REM 清理 7 天前備份
forfiles /p %BACKUP_DIR% /m bugdb_*.sql /d -7 /c "cmd /c del @path"

REM 執行備份
%MYSQL_BIN% %DB_NAME% -u%USER% -p%PASS% > %BACKUP_DIR%\bugdb_%DATE_STR%.sql

REM 壓縮 (需安裝 7zip)
"C:\Program Files\7-Zip\7z.exe" a -tzip %BACKUP_DIR%\bugdb_%DATE_STR%.zip %BACKUP_DIR%\bugdb_%DATE_STR%.sql
del %BACKUP_DIR%\bugdb_%DATE_STR%.sql

Linux 自動化備份腳本 (crontab)

#!/bin/bash
# /scripts/backup_mysql.sh
DB_NAME="bugdb"
USER="backup_user"
PASS="strong_password"
BACKUP_DIR="/backup/mysql"
DATE=$(date +%F_%H%M)
RETENTION_DAYS=7

mkdir -p $BACKUP_DIR

mysqldump -h localhost -u$USER -p$PASS \
  --single-transaction --master-data=2 \
  --routines --triggers --events \
  --hex-blob --quick \
  $DB_NAME | gzip > $BACKUP_DIR/${DB_NAME}_${DATE}.sql.gz

# 清理舊備份
find $BACKUP_DIR -name "${DB_NAME}_*.sql.gz" -mtime +$RETENTION_DAYS -delete

# 上傳異地 (可選)
# aws s3 cp $BACKUP_DIR/${DB_NAME}_${DATE}.sql.gz s3://my-bucket/mysql-backups/

# crontab -e
0 2 * * * /scripts/backup_mysql.sh >> /var/log/mysql_backup.log 2>&1

還原/恢復命令

# SQL 文件還原
mysql -u root -p db_name < dump.sql

# 壓縮文件還原
gunzip < dump.sql.gz | mysql -u root -p db_name

# 指定字符集還原
mysql -u root -p --default-character-set=utf8mb4 db_name < dump.sql

備份策略建議 (3-2-1 原則)

• 3 份副本：生產 + 本地備份 + 異地備份
• 2 種介質：磁盤 + 對象存儲/磁帶
• 1 份離線/不可變備份：防勒索軟體
• 頻率：核心庫每日全量 + Binlog 實時增量；非核心庫每周全量
• 定期演練恢復：每季度至少 1 次全量恢復演練