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

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

参考

https://dev.mysql.com/

https://stackoverflow.com/questions/44015692/access-denied-you-need-at-least-one-of-the-super-privileges-for-this-operat#comment116941195_59454713

https://7me.nobiki.com/2020/10/14/rds-mysql-error-1227-42000-access-denied-the-super-privilege-for-this-operation/

https://qiita.com/jesus_isao/items/16da16885e0c3a2aec4c

https://blog.mitsuto.com/mysqldump-utf8mb4

よかったらシェアしてね!
  • URLをコピーしました!
  • URLをコピーしました!

コメント

コメントする


The reCAPTCHA verification period has expired. Please reload the page.

目次