雖然圖形化工具 (phpMyAdmin、DBeaver、Navicat) 方便直觀，但命令列模式在自動化腳本、無形介面伺服器、大數據量匯出、權限受限環境中不可替代。本文系統整理 MySQL 客戶端匯出資料的核心命令、進階技巧與自動化備份方案。

一、MySQL 客戶端連線參數速查

mysql -h host_ip -P port -u username -p password db_name -A

# 參數詳解
-h  --host        伺服器 IP (本機可省略)
-P  --port        端口 (預設 3306)
-u  --user        用戶名
-p  --password    密碼 (緊跟 -p 無空格，或留空交互輸入)
db_name           直接進入指定資料庫
-A  --no-auto-rehash  禁用自動補全，大幅加快啟動速度 (生產必加)

二、核心匯出場景與命令

1. 直接執行 SQL 檔並匯出結果 (Tab 分隔、無表頭)

# Shell 管道模式：SQL 檔 -> mysql -> 標準輸出重導向
cat query.sql | mysql -h host -P port -u user -pPass db_name > /tmp/data.tsv

# 結果格式範例 (Tab 分隔)
id      name    email
1       Alice   alice@example.com
2       Bob     bob@example.com

優點： 適合後續 LOAD DATA INFILE 導入、Excel 開啟、ETL 流程對接。
缺點： 無表頭、NULL 顯示為 \N、無法自定義分隔符。

2. 交互模式下保存輸出 (tee 命令)

mysql> tee /tmp/output.txt
Logging to file '/tmp/output.txt'
mysql> SELECT * FROM users WHERE created_at > '2024-01-01';
mysql> notee
# /tmp/output.txt 現包含完整交互輸出 (含表頭、網格框)

適用： 臨時調試、小量資料人工審核。

3. 專業匯出工具：mysqldump (結構+資料/純資料/條件導出)

# 完整備份 (結構+資料+觸發器+存儲過程)
mysqldump -h host -u user -pPass --single-transaction --routines --triggers --events db_name > full_backup.sql

# 僅導出資料 (無建表語句)
mysqldump -h host -u user -pPass --no-create-info --single-transaction db_name > data_only.sql

# 指定表、條件導出 (Where 子句)
mysqldump -h host -u user -pPass --single-transaction --where="created_at > '2024-01-01'" db_name orders > recent_orders.sql

# 壓縮輸出 (管道 gzip)
mysqldump -h host -u user -pPass --single-transaction db_name | gzip > db_name_$(date +%F).sql.gz

金鑰參數： --single-transaction (InnoDB 一致性備份不鎖表)、--quick (大表流式讀取不緩存)、--set-gtid-purged=OFF (避免 GTID 污染目標庫)。

4. SELECT ... INTO OUTFILE (伺服器端檔案、需 FILE 權限)

SELECT id, name, email 
FROM users 
WHERE status = 1 
INTO OUTFILE '/var/lib/mysql-files/users_export.csv'
FIELDS TERMINATED BY ',' ENCLOSED BY '"' ESCAPED BY '\\'
LINES TERMINATED BY '\n';

前置條件：

• MySQL 啟用 secure_file_priv 目錄 (查看：SHOW VARIABLES LIKE 'secure_file_priv';)
• 執行用戶擁有 FILE 權限
• 目標檔案**不能存在** (安全限制)

三、自動化備份腳本實戰

方案 A：邏輯備份腳本 (每日全量 + 增量 Binlog)

#!/bin/bash
# /opt/scripts/mysql_daily_backup.sh
set -euo pipefail

BACKUP_DIR="/backup/mysql/$(date +%F)"
mkdir -p "$BACKUP_DIR"

# 1. 全量邏輯備份 (壓縮)
mysqldump -h127.0.0.1 -uroot -p"${MYSQL_ROOT_PASSWORD}" \
  --single-transaction --quick --routines --triggers --events \
  --set-gtid-purged=OFF --all-databases | gzip > "${BACKUP_DIR}/full_$(date +%H%M).sql.gz"

# 2. 刪除 7 天前備份
find /backup/mysql -type f -mtime +7 -name '*.gz' -delete

# 3. 備份 Binlog (增量恢復關鍵)
cp /var/lib/mysql/mysql-bin.[0-9]* "${BACKUP_DIR}/" 2>/dev/null || true

加入 crontab -e：

0 2 * * * /opt/scripts/mysql_daily_backup.sh >> /var/log/mysql_backup.log 2>&1

方案 B：物理備份 (Percona XtraBackup) - 不鎖表、秒級恢復

# 安裝
yum install -y percona-xtrabackup-80  # MySQL 8.0 版本對應

# 完整備份 (不停庫、不鎖表)
xtrabackup --backup --target-dir=/backup/xtrabackup/full_$(date +%F) --user=root --password="${MYSQL_ROOT_PASSWORD}"

# 準備階段 (必須，應用 Redo Log 使資料一致)
xtrabackup --prepare --target-dir=/backup/xtrabackup/full_$(date +%F)

# 恢復：停庫 -> 清空 datadir -> copy-back -> 改權限 -> 啟動
systemctl stop mysqld
rm -rf /var/lib/mysql/*
xtrabackup --copy-back --target-dir=/backup/xtrabackup/full_2024-01-15
chown -R mysql:mysql /var/lib/mysql
systemctl start mysqld

四、Windows 環境下的 XCOPY 物理備份 (冷備份)

適用於 MySQL 服務可停機的 Windows 伺服器：

:: db_back.bat
@echo off
net stop mysql
xcopy "E:\AppServ\MySQL\data\mydb\*.*" "D:\db_backup\%date:~0,4%-%date:~5,2%-%date:~8,2%\" /E /Y /H
net start mysql

警告： 冷備份需停止服務，**業務停機時間 = 複製耗時**。僅適用於非核心、資料量小、可接受停機的場景。生產核心庫請用 XtraBackup 熱備份。

五、匯出常見問題排查

問題	原因	解決
mysqldump: Got error: 1044 Access denied	缺 PROCESS/LOCK TABLES/SELECT 權限	GRANT SELECT, PROCESS, LOCK TABLES, SHOW VIEW, EVENT, TRIGGER ON *.* TO 'backup_user'@'%';
SELECT INTO OUTFILE 報錯 Errcode: 13	secure_file_priv 限制或目錄權限	檢查 secure_file_priv 目錄，確保 mysql 用戶可寫入
匯出中文亂碼	字元集不匹配	mysqldump --default-character-set=utf8mb4 ...
大表匯出 OOM / 耗時過長	緩存過大 / 無 --quick	加 --quick --single-transaction，分表並行導出

總結

命令列匯出是 DBA 與自動化運維的核心技能：

• **日常開發/小量導出**：cat sql | mysql > file 或 tee
• **定時備份/遷移/審計**：mysqldump --single-transaction + 壓縮 + 定時任務
• **大表/核心庫/秒級恢復**：**Percona XtraBackup 物理熱備份**
• **Windows 簡單冷備份**：XCOPY 批次腳本 (接受停機)

根據 RTO/RPO 需求選型，建立「備份即代碼」的自動化體系，讓資料安全可控。