LNMP 環境下 MySQL 命令列匯出/匯入大型資料庫實戰指南

phpMyAdmin 等 GUI 工具處理大型資料庫（>100MB）極易超時、記憶體溢位。命令列 mysqldump + gzip + mysql 管線是運維標準動作，高效、可控、支援斷點續傳。

一、匯出（備份）

mysqldump -u用戶名 -p密碼 --add-drop-table --complete-insert 資料庫名 | gzip > db_backup.sql.gz

• --add-drop-table：匯入前自動 DROP TABLE IF EXISTS，避免主鍵衝突
• --complete-insert：完整 INSERT INTO table (col1, col2) VALUES (...)，相容性最佳
• gzip：即時壓縮，節省磁碟與網路傳輸
• 建議使用 root 或具備 LOCK TABLES, SELECT, SHOW VIEW, TRIGGER 權限的專用備份帳號

二、匯入（還原）

# 1. 解壓
gzip -d db_backup.sql.gz

# 2. 匯入（指定字符集防亂碼）
mysql -u用戶名 -p密碼 資料庫名 < db_backup.sql --default-character-set=utf8

三、常見錯誤與調優

錯誤：MySQL server has gone away

原因：封包過大、等待超時、連線中斷。修改 /etc/my.cnf：

[mysqld]
wait_timeout = 2880000
interactive_timeout = 2880000
max_allowed_packet = 100M  # 視備份大小調整，最大 1G

重啟生效

systemctl restart mysqld  # 或 service mysqld restart

四、進階技巧

• 只匯出結構：mysqldump -d -u... -p... db > schema.sql
• 只匯出資料：mysqldump -t -u... -p... db > data.sql
• 排除特定表：--ignore-table=db.log_table
• 單表匯出：mysqldump -u... -p... db table > table.sql
• 平行匯出/匯入：mydumper/myloader（多執行緒，大庫必備）

五、生產環境建議

1. 備份腳本加入 crontab 定時執行，保留 7/30/365 天多層級
2. 異地同步：rsync / aws s3 cp / ossutil 推送至對象儲存
3. 定期演練還原，驗證備份可用性（備份未驗證等於無備份）
4. 監控 mysqldump 退出碼與備份檔大小異常波動

命令列備份還原，是每個 DBA 的基本功，也是災難恢復的最後防線。