mysqldump 導入報錯「LOCK TABLES 權限不足」解決方案

mysqldump 導入報錯「LOCK TABLES 權限不足」解決方案

使用共享主管/雲數據庫 (RDS/CloudSQL) 導入 mysqldump 導出文件時，常遇到 ERROR 1142 (42000): LOCK TABLES command denied to user。根因是導出文件含 LOCK TABLES/UNLOCK TABLES 語句，而目標賬號缺乏 LOCK TABLES 全局權限。本文提供三種解決方案。

現象重現

$ mysql -h rds-endpoint -u app_user -p db_name < backup.sql
ERROR 1142 (42000) at line 25: LOCK TABLES command denied to user 'app_user'@'%' for table 'tianqi'

打開 backup.sql 見：

LOCK TABLES `tianqi` WRITE;
/*!40000 ALTER TABLE `tianqi` DISABLE KEYS */;
INSERT INTO `tianqi` VALUES ...;
/*!40000 ALTER TABLE `tianqi` ENABLE KEYS */;
UNLOCK TABLES;

方案一：導出時跳過 LOCK TABLES (推薦，源端可控)

mysqldump -u root -p --single-transaction --skip-lock-tables db_name > backup.sql

• --single-transaction：InnoDB 可重複讀隔離級下一致性備份，無需鎖表。
• --skip-lock-tables：不輸出 LOCK TABLES/UNLOCK TABLES。
• 適用場景：全 InnoDB 表、源端有權限加參數。

方案二：導入前清理文件 (源端不可控/已有文件)

# 方法 1：sed 刪除兩行
sed -i '/^LOCK TABLES/d; /^UNLOCK TABLES/d' backup.sql

# 方法 2：Python 處理大文件 (內存友好)
python3 -c "
import sys
with open('backup.sql', 'r') as f_in, open('backup_clean.sql', 'w') as f_out:
    for line in f_in:
        if not (line.startswith('LOCK TABLES') or line.startswith('UNLOCK TABLES')):
            f_out.write(line)
"

# 方法 3：vim 手動編輯 (小文件)
vim backup.sql
/LOCK TABLES
dd
/UNLOCK TABLES
dd
:wq

方案三：授予目標賬號 LOCK TABLES 權限 (僅自建 MySQL/有超級權限)

GRANT LOCK TABLES ON db_name.* TO 'app_user'@'%';
FLUSH PRIVILEGES;

⚠️ 雲托管數據庫通常禁止授予 LOCK TABLES 全局權限，方案三僅適用自建實例。

避坑檢查清單

1. 導出加 --skip-lock-tables (配合 --single-transaction)。
2. 導出加 --no-autocommit 減少事務提交開銷。
3. 導出加 --set-gtid-purged=OFF 避免 GTID 污染目標實例。
4. 導入前檢查文件頭部 SET @MYSQLDUMP_TEMP_LOG_BIN 等變量是否兼容目標版本。
5. 大文件導入用 SOURCE 而非管道，避免連接斷開無感知。

核心原則：備份導出端控制輸出格式，避免產生目標端無權限執行的語句。養成 mysqldump --single-transaction --skip-lock-tables --routines --triggers --events 的肌肉記憶，可消除 90% 導入報錯。