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-gtid-purged=
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を手動で特定し追加する予定の場合のみ、このオプションを使用します。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
コメント