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をコピーしました!

エンジニアを目指す方必見!おすすめプログラミングスクール

最短でエンジニアになるには、いかに効率よく学習するかが重要です。モチベーションを維持しながら最短でエンジニアを目指すならプログラミングスクールを利用するのもおすすめです。

目次
閉じる