如何解決使用 mysqldump 將資料匯入到 Amazon RDS for MySQL 資料庫執行個體時發生的 1227 和定義器錯誤?

2 分的閱讀內容
0

當我嘗試使用 mysqldump 將資料匯入到 Amazon Relational Database Service (Amazon RDS) for MySQL 資料庫執行個體時,我收到類似下列其中一項的錯誤訊息:

Error: 1227 SQLSTATE: 42000 (ER_SPECIFIC_ACCESS_DENIED_ERROR) Access denied; you need (at least one of) the %s privilege(s) for this operation.
Definer error: example: /*!50003 CREATE*/ /*!50017 DEFINER=`root`@`localhost`*/ /*!50003 TRIGGER customer_update AFTER UPDATE ON `customer` FOR EACH ROW

簡短描述

當資料庫已啟用二進位日誌且 mysqldump 檔案包含已儲存物件 (例如觸發程序、檢視、函數或事件) 時,就會發生 1227 錯誤。如需詳細資訊,請參閱二進位日誌

當 MySQL 嘗試在資料庫使用者下建立物件,但目的地資料庫中不存在該資料庫使用者時,會觸發定義器錯誤。當 MySQL 嘗試為 localhost 建立使用者 (Amazon RDS 不允許的動作) 時,您可能會收到類似的錯誤。這是因為 Amazon RDS 沒有超級使用者權限。

解決方法

解決錯誤 1227

1.    在您為資料庫執行個體建立的自訂資料庫參數群組中,將 log_bin_trust_function_creators 參數設定true

2.    一些命令通常存在於 MySQL 傾印檔案中,例如 RDS 中不允許 "SET @@SESSION.SQL_LOG_BIN= 0;"。在針對 RDS 執行個體執行檔案之前,應先從傾印檔案中刪除這些行或對它們進行註解。

解決定義器錯誤

定義器錯誤可透過以下幾種方式解決:

  • 移除定義器行
  • 重新命名定義器使用者
  • 不使用定義器選項而建立或重新建立傾印檔案

移除定義器行

/*!50017 DEFINER=`root`@`localhost`*/

該行現在顯示的輸出類似如下:

/*!50003 CREATE*/ /*!50003 TRIGGER customer_update AFTER UPDATE ON `customer` FOR EACH ROW

重新命名定義器使用者

root 重新命名為 masteruser,並將 localhost 重新命名為 %host

/*!50003 CREATE*/ /*!50017 DEFINER=`masteruser`@`%`*/ /*!50003 TRIGGER customer_update AFTER UPDATE ON `customer` FOR EACH ROW

**注意:**您可以將 % 用作所有主機的萬用字元。

不使用定義器選項而建立或重新建立傾印檔案。

MySQL 傾印公用程式不提供移除定義器的選項。有些 MySQL 用戶端會在建立邏輯備份時提供略過定義器的選項,但此選項預設不提供。檢閱您偏好的 MySQL 用戶端的文件,以查看略過定義器的選項是否可用。MySQL 命令行用戶端無法排除定義器。不過,用戶端可與第三方工具搭配使用以移除定義器或尋找並取代使用者名稱和主機

下列範例示範如何在 Linux、MacOS 或 Windows Subsystem for Linux (WSL) 中移除定義器:

移除:

sed -i -e 's/DEFINER=`root`@`localhost`//g' dump.sql

尋找並取代:

sed -i -e 's/DEFINER=`root`@`localhost`/DEFINER=`masteruser`@`%`/g' dump.sql

**注意:**使用 Amazon RDS 主要使用者的名稱取代 masteruser 的值。


相關資訊

Amazon RDS 上的 MySQL

使用複寫從 MySQL 資料庫執行個體匯出資料