ERROR 1227 (42000) at line 18: Access denied; you need (at least one of) the SUPER privilege(s) for this operation

AWS
language

EC2でRDSからデータをダンプしてから、RDSへリストアしようとしたらタイトル通りのエラーが出ました。

リストアしようとして実行したコマンドは以下のとおりです。

$ mysql -u USER_NAME -p -h HOST_NAME < dump.sql
Enter password: 
ERROR 1227 (42000) at line 18: Access denied; you need (at least one of) the SUPER privilege(s) for this operation

このエラーはダンプしたデータにダンプしたユーザーとリストアを実行したユーザーが異なるために起きるエラーです。

ダンプしたデータにDEFINERとしてダンプしたユーザーが記録されています。

この記事ではこのエラーの解決方法とエラーが出ないダンプの方法をご紹介します。

エラーの解決方法1: DEFINERを削除する

指定されたユーザでリストアしたら問題なくリストアできますが、DEFINERを削除してどのユーザーでもリストアできるようにします。

ダンプファイルにDEFINERが記載されているかは、以下のコマンドで確認できます。

$ cat ./dump.sql | grep "SQL SECURITY DEFINER"
/*!50013 DEFINER=`seeder`@`%` SQL SECURITY DEFINER */

vimで以下の記載を削除するとユーザーの指定がなくなるので、どのユーザーでもダンプデータをリストアできます。

/*!50013 DEFINER=`seeder`@`%` SQL SECURITY DEFINER */

以下のコマンドでもダンプデータ内のDEFINERを削除することができます。

	$ sed 's/\sDEFINER=`[^`]*`@`[^`]*`//g' -i ./dump.sql

エラーの解決方法2: DEFINERがない場合

DEFINERがない場合は、以下の3行を削除またはコメントアウトします。

SET @MYSQLDUMP_TEMP_LOG_BIN = @@SESSION.SQL_LOG_BIN;
SET @@SESSION.SQL_LOG_BIN= 0;

SET @@GLOBAL.GTID_PURGED='';

以下はコメントアウトしています。これら3行を削除やコメントアウトすることでユーザーに関係なくリストアできます。

-- SET @MYSQLDUMP_TEMP_LOG_BIN = @@SESSION.SQL_LOG_BIN;
-- SET @@SESSION.SQL_LOG_BIN= 0;

-- SET @@GLOBAL.GTID_PURGED='';

DEFINERが記載されないエクスポートの方法

ユーザー指定のないダンプファイルを作る場合はダンプするときに--set-gtid-purged=OFFを付ける必要があります。
ダンプファイル名には日時を入れています。

$ mysqldump -t DB_NAME -u USER_NAME -p  -h HOST_NAME --set-gtid-purged=OFF > dump_`date +%Y%m%d_%H%M%S`.sql

--set-gtid-purgedオプションについてはmysqlドキュメントに詳しく書かれています。

OFF
SET @@GLOBAL.gtid_purged is not added to the output, and SET @@SESSION.sql_log_bin=0 is not added to the output. For a server where GTIDs are not in use, use this option or AUTO. Only use this option for a server where GTIDs are in use if you are sure that the required GTID set is already present in gtid_purged on the target server and should not be changed, or if you plan to identify and add any missing GTIDs manually.

SET @@GLOBAL.gtid_purged は出力に追加されず、SET @@SESSION.sql_log_bin=0 は出力に追加されません。GTIDが使用されていないサーバーでは、このオプションまたはAUTOを使用してください。GTIDが使用されているサーバーでは、必要なGTIDセットがターゲットサーバーのgtid_purgedに既に存在し、変更すべきでないことが確実な場合、または不足しているGTIDを手動で特定し追加する予定の場合のみ、このオプションを使用します。

–set-gtid-purged=value

--set-gtid-purgedオプションを付けていないと以下のようにwarningが出ます。

$ mysqldump -t DB_NAME -u USER_NAME -p  -h HOST_NAME > dump.sql
Enter password: 
Warning: A partial dump from a server that has GTIDs will by default include the GTIDs of all transactions, even those that changed suppressed parts of the database. If you don't want to restore GTIDs, pass --set-gtid-purged=OFF. To make a complete dump, pass --all-databases --triggers --routines --events. 

また、データベースに絵文字や特殊文字がある場合は、–default-character-setオプションを付けないと文字化けした状態でダンプされます。

以下のように–default-character-setオプションで文字コードを指定すると絵文字や特殊文字もダンプされます。

$ mysqldump -t DB_NAME -u USER_NAME -p  -h HOST_NAME --set-gtid-purged=OFF --default-character-set=utf8mb4  > dump_`date +%Y%m%d_%H%M%S`.sql

参考

MySQL :: Developer Zone
Access denied; you need (at least one of) the SUPER privilege(s) for this operation
So I try to import sql file into rds (1G MEM, 1 CPU). The sql file is like 1.4G mysql -h xxxx.rds.amazonaws.com -u user -ppass --max-allowed-packet=33554432 db...
RDSでAccess Denied...the SUPER privilegeを解決するメモ | 7me
とてもエラーとあるオンプレのサーバで mysqldumpしたSQLファイルを、RDSにリストアしようとした時にエラーが出た 1ERROR 1227 (42000) at line 4058: Access denied; you need (at least one of) the SUPER privilege(s)...
[MySQL] ERROR 1227 (42000) at line 18: Access denied; you need (at least one of) the SUPER privilege(s) for this operation - Qiita
エラーメッセージと発生箇所 MySQL上でdumpファイルを読み込もうとした時に発生。 ERROR 1227 (42000) at line 18: Access denied; you need (at least one ...
mysqldumpしたデータを戻したら絵文字が文字化けしてたのでなんとかしたいっ!
mysqldump このブログのデータは旧サーバーでdumpして...
タイトルとURLをコピーしました