MySQL 資料庫備份與還原：mysqldump 與多種實戰方法

資料庫匯出匯入是遷移、備份、同步核心操作。本文系統整理命令列、phpMyAdmin、原始檔案複製等多種方案。

方法一：mysqldump 命令列工具 (推薦)

匯出整個資料庫

# 基本語法
mysqldump -u username -p database_name > backup.sql

# 實例：匯出 db_name 為 test_db.sql
mysqldump -u root -p db_name > test_db.sql

# 遠端主機
mysqldump -h 192.168.0.156 -u username -p --skip-lock-tables db_name > database.sql

匯出單一資料表

mysqldump -u username -p database_name table_name > table_backup.sql
# 範例
mysqldump -u wcnc -p test_db users > test_users.sql

匯出多個資料庫

mysqldump -u root -p --databases db1 db2 db3 > multi_db.sql

僅匯出結構/僅匯出資料

# 僅結構
mysqldump -u root -p -d db_name > structure.sql

# 僅資料
mysqldump -u root -p -t db_name > data.sql

方法二：匯入資料庫

命令列匯入

# 方式 1：mysql 指令 (需先建立空資料庫)
mysql -u username -p database_name < backup.sql

# 方式 2：source 指令 (在 mysql client 內)
mysql -u root -p
CREATE DATABASE db_name;
USE db_name;
SOURCE /path/backup.sql;

大檔案匯入優化

• 壓縮傳輸：gzip -d backup.sql.gz | mysql -u root -p db_name
• 關閉自動提交：在 SQL 檔案開頭加 SET autocommit=0; 結尾加 COMMIT;
• 關閉外鍵檢查：SET FOREIGN_KEY_CHECKS=0; ... SET FOREIGN_KEY_CHECKS=1;
• 調大 max_allowed_packet (如 100M)

方法三：phpMyAdmin 圖形介面

匯出設定重點

• 選擇「結構」+「資料」
• **勿勾選**「DROP DATABASE」與「DROP TABLE」
• 勾選「另存為檔案」，大資料勾選「gzipped」

匯入注意事項

• PHP 上傳限制：修改 php.ini 的 upload_max_filesize、post_max_size
• 執行時間限制：調整 max_execution_time
• 大檔案建議命令列處理

方法四：原始檔案複製 (僅限 MyISAM/相同版本)

# 停止服務
systemctl stop mysqld

# 複製資料目錄 (通常 /var/lib/mysql/db_name)
cp -r /var/lib/mysql/db_name /backup/

# 啟動服務
systemctl start mysqld

> ⚠️ InnoDB 無法直接複製檔案，需配合 innodb_file_per_table 且相同版本/架構。

方法五：二進制日誌增量備份

# 啟用 binlog (my.cnf)
log-bin=mysql-bin
server-id=1

# 完整備份後，增量備份
mysqlbinlog mysql-bin.000001 > incremental.sql

遷移檢查清單

項目	檢查點
版本相容	目標版本 ≥ 來源版本，或使用 --compatible 參數
字元集	來源/目標均為 utf8mb4
權限重建	匯入後執行 FLUSH PRIVILEGES
觸發器/視圖	mysqldump 預設包含，確認 DEFINER 存在
自增值	確認 AUTO_INCREMENT 值正確