MySQL 原生命令實戰：資料庫備份與還原完整指南

場景：500MB 大庫遷移與亂碼排查

1. 進入客戶端：mysql -u root -p12345
2. 建庫指定編碼：CREATE DATABASE demo DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
3. 切換資料庫：USE demo;
4. 匯入：SOURCE file.sql;（檔案置於 bin 目錄或給絕對路徑）

亂碼解決：重建庫前執行 SET NAMES utf8; 或在連接時指定 --default-character-set=utf8。

mysqldump 核心語法

# 匯出整庫
mysqldump -u root -p -h IP dbname > file.sql

# 匯出單表
mysqldump -u root -p dbname tblname > file.sql

# 僅匯出結構 (無資料)
mysqldump -u root -p -d --add-drop-table dbname > structure.sql

# 匯出帶條件資料
mysqldump -u root -p dbname tblname -t -w "id=101" > data.sql
  -t  不含 CREATE TABLE
  -d  不含資料
  -w  WHERE 條件

匯入方式三選一

# 方式 1：SOURCE (進入 mysql 後)
USE dbname;
SOURCE file.sql;

# 方式 2：mysqldump 管線
mysqldump -u root -p dbname < file.sql

# 方式 3：mysql 管線
mysql -u root -p -D dbname < file.sql

進階：SELECT ... INTO OUTFILE / LOAD DATA INFILE

適合大量資料高效導出入，繞過 SQL 解析層。

-- 導出
SELECT * FROM student WHERE studentno=10101 INTO OUTFILE '/tmp/stu.txt';

-- 導入
LOAD DATA INFILE '/tmp/stu.txt' INTO TABLE student;

• 需 FILE 權限，檔案路徑在 Server 端，受 secure_file_priv 限制。
• 字符集、欄位分隔符、行結束符可透過 CHARACTER SET、FIELDS TERMINATED BY、LINES TERMINATED BY 控制。

XML 格式匯出入

# 導出 XML
mysqldump --xml -u root -p dbname [tblname] > file.xml
mysql -X -u root -p -e "USE dbname; SELECT * FROM tblname" > file.xml

# 導入 XML (需建立 BLOB 欄位儲存)
CREATE TABLE xmlt (id INT, doc BLOB);
INSERT INTO xmlt VALUES (1, LOAD_FILE('/home/a.xml'));

常用參數速查表

結語

熟練掌握 mysqldump/mysql/SOURCE/LOAD DATA 組合拳，能應對 90% 以上備份遷移場景。大庫建議加 --single-transaction --quick --compress，並配合 pv 監控進度。