解決 mysqldump 錯誤 1142：SELECT, LOCK TABLES 權限被拒絕

mysqldump 權限錯誤 1142 解決方案

在使用 AutoMySQLBackup 進行資料庫備份時，常會遇到以下錯誤：

mysqldump: Got error: 1142: SELECT, LOCK TABLES command denied to user 'root'@'localhost' for table 'accounts' when using LOCK TABLES

這個錯誤發生的原因是備份用戶（此處為 root@localhost）缺乏 LOCK TABLES 權限，導致 mysqldump 在嘗試鎖定表以確保資料一致性時失敗。錯誤日誌完整輸出如下：

[root@DB-Server ~]# /usr/bin/automysqlbackup /etc/automysqlbackup/myserver.conf
Parsed config file "/etc/automysqlbackup/automysqlbackup.conf"
# Checking for permissions to write to folders:
base folder /u03 ... exists ... ok.
backup folder /u03/mysqlbackup/ ... exists ... writable? yes. Proceeding.
...
Backup Start Time Sat Jul 11 22:12:31 CST 2015
...
Daily Backup of Database ( mysql )
...
Daily Backup of Database ( performance_schema )
...
Daily Backup of Database ( test )
...
###### WARNING ######
Errors reported during AutoMySQLBackup execution.. Backup failed
Error log below..
Warning: Using a password on the command line interface can be insecure.
mysqldump: Got error: 1142: SELECT, LOCK TABLES command denied to user 'root'@'localhost' for table 'accounts' when using LOCK TABLES

解決方法

只需修改 AutoMySQLBackup 的配置檔，將參數 CONFIG_mysql_dump_single_transaction 設為 yes 即可。此參數會啟用 --single-transaction 選項，使 mysqldump 在單一事務中讀取資料，無需鎖定表，自然也就繞過了 LOCK TABLES 權限檢查。

操作步驟

1. 編輯配置檔：vi /etc/automysqlbackup/myserver.conf（或 automysqlbackup.conf）
2. 找到 CONFIG_mysql_dump_single_transaction 參數
3. 將其值改為 yes
4. 儲存並重新執行備份腳本

原理說明

--single-transaction 選項會在匯出前發送 BEGIN 陳述式，利用 MVCC（多版本並發控制）確保資料一致性，適用於 InnoDB 等支援事務的儲存引擎。此法不需 LOCK TABLES 權限，且不會阻塞寫入操作，是線上熱備份的推薦做法。

替代方案

網路上也常見建議加入 --skip-lock-tables 參數，但這會在備份期間允許資料變更，可能導致備份資料不一致，生產環境不建議使用。

驗證修復

修改配置後再次執行備份，錯誤訊息應消失，備份流程正常完成：

Backup End Time Sat Jul 11 22:15:42 CST 2015
Total disk space used for backup storage...
Size - Location
443k /u03/mysqlbackup/