MySQL 學習筆記（十六）：數據庫維護——備份、還原、導出、導入、遷移全攻略

MySQL 學習筆記（十六）：數據庫維護——備份、還原、導出、導入、遷移全攻略

系統整理 MySQL 數據維護核心操作：文件複製備份、mysqldump 邏輯備份、SELECT INTO OUTFILE 導出、LOAD DATA INFILE 導入、同版本/跨版本/異構遷移策略。

一、數據備份

1. 文件複製（僅限 MyISAM）

• 停止服務 → FLUSH TABLES → 複製 datadir 文件。
• InnoDB 不適用（需配合 innodb_fast_shutdown=0 且仍有風險）。

2. mysqldump 邏輯備份（通用）

# 備份單庫（指定表）
mysqldump -u root -p dbname table1 table2 > backup.sql
# 備份單庫（全表）
mysqldump -u root -p dbname > backup.sql
# 備份多庫
mysqldump -u root -p --databases db1 db2 > backup.sql
# 備份所有庫
mysqldump -u root -p --all-databases > all.sql

輸出包含 CREATE TABLE + INSERT，可直接 SOURCE 還原。

二、數據還原

1. 文件複製還原（MyISAM、同主版本）

• 主版本號一致（如 5.5.x → 5.5.y）才保證文件格式兼容。
• 僅限 MyISAM 表。

2. mysqldump 還原

# 指定庫還原
mysql -u root -p dbname < backup.sql
# 不指定庫，還原文件中所有庫
mysql -u root -p < all.sql

三、數據導出（文本文件）

1. SELECT ... INTO OUTFILE

SELECT col1, col2 FROM tbl
INTO OUTFILE '/path/file.txt'
FIELDS TERMINATED BY ',' ENCLOSED BY '"' ESCAPED BY '\\'
LINES TERMINATED BY '\n';

選項：FIELDS TERMINATED BY、ENCLOSED BY、OPTIONALLY ENCLOSED BY、ESCAPED BY、LINES STARTING BY、LINES TERMINATED BY。

2. mysqldump -T 導出

mysqldump -u root -p -T /export/dir dbname tblname
--fields-terminated-by=',' --fields-enclosed-by='"' --lines-terminated-by='\n'

四、數據導入

1. LOAD DATA INFILE

LOAD DATA [LOCAL] INFILE '/path/file.txt'
INTO TABLE tbl
FIELDS TERMINATED BY ',' ENCLOSED BY '"' ESCAPED BY '\\'
LINES TERMINATED BY '\n'
IGNORE n LINES
(col1, col2, ...)
SET col3 = expr;

LOCAL：從客戶端讀取文件。IGNORE n LINES 跳過標題。(col_list) 指定列映射。SET 列轉換表達式。

2. mysqlimport

mysqlimport -u root -p [--local] dbname /path/file.txt
--fields-terminated-by=',' --lines-terminated-by='\n' --ignore-lines=n

按文件名匹配表名（去掉擴展名）。

五、數據庫遷移

1. 同版本 MySQL 間遷移

mysqldump -h host1 -u root -p --all-databases | mysql -h host2 -u root -p

管道流式傳輸，無中間文件。

2. 不同版本 MySQL 遷移

• 低版本 → 高版本：兼容性好，直接 mysqldump + mysql 還原。
• MyISAM 表：可用文件複製或 mysqlhotcopy。
• InnoDB 表：必須用 mysqldump 邏輯備份還原。

3. 異構遷移

• MySQL → SQL Server：MyODBC 驅動連接導入。
• MySQL → Oracle：mysqldump 導出 SQL → 手動調整 CREATE TABLE 語法（類型、自增、引號等）→ Oracle 導入。

六、避坑提示

• INTO OUTFILE 目標路徑需 MySQL 進程寫權限，文件不可存在。
• 導入導出參數（分隔符、轉義、換行）必須**完全一致**。
• 大數據量遷移建議分表並行、關閉索引/外鍵檢查、批量提交。
• 跨版本遷移前必須在測試環境全量驗證。