MySQL 備份與還原完整指南：mysqldump 參數、錯誤排查與導入最佳實踐

MySQL 備份與還原完整指南：mysqldump 參數、錯誤排查與導入最佳實踐

mysqldump 是 MySQL 官方邏輯備份工具，支援結構、資料、條件導出。本文系統整理常用參數組合、常見權限錯誤解法、導入注意事項，幫助 DBA 建立標準化備份流程。

一、常用備份命令模板

二、常見錯誤與解決

錯誤：Access denied ... when doing LOCK TABLES

mysqldump: Got error 1044: Access denied for user 'zndw'@'localhost' 
to database 'scilibrary' when doing LOCK TABLES

原因：備份用戶缺少 LOCK TABLES 權限。

方案 1（推薦）：授予權限

GRANT LOCK TABLES ON dbname.* TO 'user'@'host';

方案 2（快速繞過）：跳過鎖表

mysqldump -uroot -p dbname --skip-lock-tables > db.sql

• ⚠️ --skip-lock-tables 可能導致備份資料不一致（非事務表），InnoDB 配合 --single-transaction 更安全。

三、InnoDB 熱備份最佳參數組合

mysqldump -uroot -p \
  --single-transaction \
  --flush-logs \
  --master-data=2 \
  --routines --triggers --events \
  dbname > db_backup_$(date +%F_%H-%M).sql

• --single-transaction：啟動一致性快照，不鎖表（InnoDB 專用）。
• --flush-logs：切換二進制日誌，配合 --master-data=2 記錄備份時刻 Binlog 位置，支援時間點恢復（PITR）。
• --routines --triggers --events：包含存儲過程、觸發器、事件。

四、導入（還原）操作

# 進入 mysql 客戶端
mysql -uroot -p dbname < db_backup.sql

# 或在 mysql 客戶端內
SOURCE /path/to/db_backup.sql;

注意：

1. SOURCE 後不加分號。
2. 用戶需具備 INSERT、CREATE、DROP 等權限。
3. 大檔導入建議先 SET autocommit=0; SET unique_checks=0; SET foreign_key_checks=0; 結束後恢復，大幅提速。