MySQL 資料備份與還原三大核心方法深度解析

前言

資料安全的核心在於「備份可用、還原可靠」。本文實測 MySQL 4.0.18 環境，詳細演示 BACKUP/RESTORE TABLE、SELECT INTO OUTFILE / LOAD DATA INFILE、mysqldump 三種主流備份還原方案的完整操作流程與輸出解讀。

環境準備

C:\mysql\bin> mysql -u root mysql
mysql> CREATE DATABASE test;
mysql> USE test;
mysql> CREATE TABLE test (
    id INT,
    name VARCHAR(40),
    age TINYINT
);
mysql> INSERT INTO test VALUES (5101,'chifeng',23),(5102,'phpchina',18),(5103,'admin',40);

方法一：BACKUP TABLE / RESTORE TABLE（僅限 MyISAM）

備份

mysql> BACKUP TABLE test TO 'c:/';
+-----------+--------+----------+----------+
| Table     | Op     | Msg_type | Msg_text |
+-----------+--------+----------+----------+
| test.test | backup | status   | OK       |
+-----------+--------+----------+----------+

產生 test.frm（結構）與 test.MYD（資料），不含索引檔。

還原

mysql> DROP TABLE test;
mysql> RESTORE TABLE test FROM 'c:/';
+-----------+---------+----------+----------+
| Table     | Op      | Msg_type | Msg_text |
+-----------+---------+----------+----------+
| test.test | restore | status   | OK       |
+-----------+---------+----------+----------+

驗證 SELECT * FROM test; 資料完整回覆。

方法二：SELECT ... INTO OUTFILE / LOAD DATA INFILE

匯出純文字資料

mysql> SELECT * FROM test INTO OUTFILE 'f:/mysql_copy/test.dat';
Query OK, 3 rows affected (0.00 sec)

還原資料

mysql> LOAD DATA INFILE 'f:/mysql_copy/test.dat' INTO TABLE test;
Query OK, 3 rows affected (0.08 sec)
Records: 3  Deleted: 0  Skipped: 0  Warnings: 0

適用於跨平台、跨版本的文字資料遷移，支援欄位分隔符自訂。

方法三：mysqldump 工具（最通用、推薦）

備份

C:\mysql\bin> mysqldump -u root -p test > test.sql

產生包含 CREATE TABLE、INSERT 等完整 SQL 腳本，支援單庫、多庫、全例備份。

還原

C:\mysql\bin> mysql -u root -p test < test.sql

或進入 mysql 客戶端執行 SOURCE test.sql;。

三種方案對比與選型建議

最佳實踐建議

1. 日常自動化備份： 使用 mysqldump --single-transaction --routines --triggers --events 搭配 crontab 定時任務。
2. 大資料表高效遷移： 結合 SELECT INTO OUTFILE + LOAD DATA INFILE 並行處理，速度遠快於 SQL 重放。
3. 災難演練： 定期在測試環境執行完整還原演練，驗證備份檔案完整性與 RTO/RPO 指標。