MySQL 資料庫管理：使用 mysqldump 進行匯出與匯入完整教學│MySQL & MariaDBの世界

1 MySQL 資料庫管理中的 mysqldump 匯出與匯入

MySQL 資料庫管理中的 mysqldump 匯出與匯入

1. 簡介

MySQL 資料庫在網頁應用程式與資料庫管理系統中被廣泛使用。妥善管理資料庫並定期進行備份，是預防意外故障或資料遺失的關鍵步驟。特別是 mysqldump 指令，是 MySQL 資料庫進行匯出與後續匯入的重要工具之一。

本文將詳細說明如何使用 mysqldump 來備份（匯出）MySQL 資料庫，以及如何透過備份檔案將資料復原（匯入）至資料庫。內容適合資料庫管理員與工程師，並會介紹常見錯誤與效能最佳化的方法。

2. mysqldump 指令基礎

mysqldump 是一個功能強大的命令列工具，用於備份 MySQL 資料庫。它能將資料表結構與資料以文字檔案的形式匯出。以下將介紹基本用法與常見選項。

2.1 mysqldump 的基本用法

最基本的指令如下：

mysqldump -u [使用者名稱] -p [資料庫名稱] > [輸出檔案名稱]

此指令會將指定資料庫的所有資料表結構與資料匯出至檔案。

範例：

mysqldump -u root -p mydatabase > backup.sql

其中 -u 指定 MySQL 使用者，-p 會提示輸入密碼。mydatabase 為欲備份的資料庫名稱，backup.sql 為匯出檔案名稱。

2.2 常用選項說明

–single-transaction：使用交易方式避免匯出時鎖表，InnoDB 資料表可確保資料一致性。
–skip-lock-tables：避免在匯出過程中鎖表，允許其他使用者同時存取資料庫。
–no-data：僅匯出資料表結構，不包含資料，適合只需備份結構的情境。

2.3 匯出檔案的結構

mysqldump 會輸出包含 SQL 語法的檔案，例如：

DROP TABLE IF EXISTS `table_name`;
CREATE TABLE `table_name` (
  `id` int(11) NOT NULL,
  `name` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `table_name` (`id`, `name`) VALUES (1, 'John'), (2, 'Doe');

該檔案在復原時會先刪除資料表，再重新建立並插入資料。

3. 使用 mysqldump 匯入資料的步驟

接下來介紹如何將匯出的備份檔案匯入資料庫，主要使用 mysql 指令完成。

3.1 基本匯入指令

基本語法如下：

mysql -u [使用者名稱] -p [資料庫名稱] < [輸入檔案名稱]

範例：

mysql -u root -p mydatabase < backup.sql

此指令會將 backup.sql 中的內容匯入至指定的資料庫 mydatabase。

3.2 匯入時的注意事項

確認資料庫是否存在：若匯入目標資料庫不存在，將會發生錯誤。需要先建立資料庫，可使用以下指令：

CREATE DATABASE mydatabase;

大量資料匯入：當匯入大量資料時，可能會造成伺服器效能下降。可在匯入前後停用索引或使用批次處理來提升效率。

4. 錯誤處理與疑難排解

在匯入過程中常會遇到錯誤，但只要正確處理，大多可以解決。以下整理常見錯誤與解決方法。

4.1 常見錯誤範例

ERROR 1064 (語法錯誤)

原因：不同 MySQL 版本的相容性問題，或 SQL 檔案語法錯誤。例如，新版 MySQL 中已廢止的語法仍被使用時。
對策：檢查錯誤訊息，修正有問題的 SQL 語句。跨版本移轉時，需搭配適當選項。

ERROR 1049 (找不到資料庫)

原因：指定的資料庫不存在或名稱錯誤。
對策：確認資料庫是否存在，若沒有，請先建立：
CREATE DATABASE 資料庫名稱;

ERROR 1146 (找不到資料表)

原因：SQL 檔案中參考的資料表不存在，通常是匯入過程未正確建立。
對策：確認 SQL 檔案內是否有正確的 CREATE TABLE 語句，必要時可手動建立。

4.2 避免錯誤的最佳實踐

保持匯出與匯入環境一致：不同版本或設定差異可能導致語法錯誤或資料型態不一致。
測試備份檔：在正式匯入前，可先於本地建立測試資料庫進行測試匯入。

4.3 疑難排解步驟

解決匯入錯誤的步驟：

檢查錯誤訊息：MySQL 命令列或錯誤日誌通常會顯示錯誤行與內容。
檢查匯出檔案：手動查看 SQL 檔案是否有缺少的 CREATE TABLE 或 INSERT 語句。
調整匯出選項：例如使用 --compatible 提升跨版本相容性。

5. 匯入時的效能最佳化

在匯入大量資料時，可能會對資料庫效能造成影響。本章將介紹一些提升匯入效率的方法。

5.1 停用與重建索引

若在匯入過程中啟用索引，會降低資料插入速度。因此可在匯入前先停用索引，待匯入完成後再重新啟用，能顯著縮短時間。

停用索引範例：

ALTER TABLE 資料表名稱 DISABLE KEYS;

匯入完成後，重新啟用索引：

ALTER TABLE 資料表名稱 ENABLE KEYS;

5.2 使用批次處理

當資料量過大時，可分批匯入以降低伺服器負載。例如，不是一次匯入數百萬筆，而是每次匯入 10 萬筆資料。

5.3 使用資料壓縮

壓縮資料不僅能節省磁碟空間，也能縮短傳輸時間。可使用 gzip 等工具進行壓縮，並在匯入時解壓。

範例如下：

gunzip < backup.sql.gz | mysql -u root -p mydatabase

6. 總結

在 MySQL 資料庫管理中，利用 mysqldump 進行匯出與匯入是一種非常實用的方法。本文從 mysqldump 的基本用法、常見錯誤處理到效能最佳化，進行了完整的解說。

對於大型資料庫，適當地管理索引與批次匯入尤為重要。同時，定期備份並測試匯入，可以大幅降低資料遺失風險。

遵循這些最佳實踐，能讓資料庫匯入作業更順暢、更高效。

MySQL 資料庫管理：使用 mysqldump 進行匯出與匯入完整教學