mysqldump を使用して Amazon RDS for MySQL DB インスタンスにデータをインポートする際に発生する 1227 エラーと definer エラーを解決する方法を教えてください。

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

簡単な説明

1227 エラーはデータベースでバイナリログが有効になっていて、保存されたオブジェクト (トリガー、ビュー、関数、またはイベント) が mysqldump ファイルに含まれている場合に発生します。詳細については、「バイナリログ」を参照してください。

MySQL が、送信先のデータベースに存在しないデータベースユーザーの下にオブジェクトを作成しようとすると、definer エラーが発生します。MySQL が localhost でユーザーを作成しようとした場合も、同様のエラーが表示されることがあります。このアクションは Amazon RDS では許可されていません。Amazon RDS にはスーパーユーザー許可がないためです。

解決方法

エラー 1227 を解決する

1.    DB インスタンス用に作成したカスタム DB パラメータグループにおいて、log_bin_trust_function_creators パラメータを true に設定します。

2.    「SET @@SESSION.SQL_LOG_BIN= 0;」のようないくつかのコマンドは、通常 MySQL ダンプファイルに存在し、RDS では許可されません。これらの行は、ファイルを RDS インスタンスに対して実行する前に、ダンプファイルから削除するか、ダンプファイルにコメントする必要があります。

definer エラーを解決する

definer エラーの対処にはいくつか方法があります。

• definer 行を削除する
• definer ユーザーの名前を変更する
• definer のオプションなしでダンプファイルを作成または再作成する

definer 行を削除する

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

この行には、次のような出力が表示されています。

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

definer ユーザーの名前を変更する

root を masteruser に、localhost を %host に変更します。

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

注: % は、すべてのホストのワイルドカードとして使用できます。

definer のオプションなしでダンプファイルを作成または再作成します。

MySQL のダンプユーティリティには DEFINER を削除するオプションはありません。論理バックアップを作成するときに definer を無視するオプションを提供する MySQL クライアントもありますが、このオプションはデフォルトでは起こりません。使用している MySQL クライアントのドキュメントを調べて、DEFINER を無視するオプションがあるかどうかを確認してください。MySQL コマンドラインクライアントは definer を除外できません。ただし、クライアントがサードパーティーツールを使用して DEFINER を削除したり、ユーザー名とホストを検索し置き換えることは可能です

次の例に、Linux、macOS、あるいは Windows Subsystem for Linux (WSL) で DEFINER を削除する方法を示します。

削除:

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

検索と置換:

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

注: masteruser の値を Amazon RDS マスターユーザーの名前に置き換えます。

---

関連情報

Amazon RDS での MySQL

レプリケーションを使用した MySQL DB インスタンスからのデータのエクスポート