1. 症状
以下のように、 Access denied; you need (at least one of) the SUPER privilege(s) for this operation というメッセージが出て、データのインポートができない。
$ mysql -u user_name -p -h <host> -P <ポート番号> <データベース名> < dump.sql
Enter password:
ERROR 1227 (42000) at line <行番号>: Access denied; you need (at least one of) the SUPER privilege(s) for this operation
SUPER privilege(s)という権限が必要らしい。
この問題への対処法を以下に記載します。
2. 動作環境
# mysqldumpのバージョン確認
$ mysqldump --version
mysqldump Ver 8.0.33-0ubuntu0.22.04.2 for Linux on x86_64 ((Ubuntu))
# mysqlclientのバージョン確認
$ mysql --version
mysql Ver 8.0.33-0ubuntu0.22.04.2 for Linux on x86_64 ((Ubuntu))
# RDS内のMySQL serverのバージョン確認
$ mysql -u root -p -h 127.0.0.1 -P 12345
Enter password:
...
mysql> select version();
+------------+
| version() |
+------------+
| 5.7.12-log |
+------------+
1 row in set (0.00 sec)
3. 対処法について
ネット上で調べたところ、解決策が書かれている記事がいくつかありましたが、ヒットした記事だけでは私の問題の解決には至りませんでした。
ただ、エラー原因が私とは異なる場合には、それらの記事の方法が役立つかもしれませんので、それらの方法も簡単に記載します。
- SUPER権限を付与 (❌)
- dumpファイルを修正する +α (🔺不採用)
- DEFINERを除去する (❌)
- dumpファイルのエクスポートオプションを工夫する (⭕️これで解決)
3-1. SUPER権限を付与
私は、この方法ではうまくいきませんでした。
というのも、この記事の方法では、私の使っているmysqlのユーザにSUPER権限を付与できなかったためです。
RDSだからか、もしくは私に権限が不足していたからなのかなと思います。
うまくいく方もいらっしゃると思いますので、試してみる価値はあると思います。
3-2. dumpファイルを修正する +α
この方法は、私は採用しませんでした。
というのは、私のdumpファイルはGB単位のファイルだったので、
- エディターで開くだけでかなりの時間がかかる
- 修正が必要な該当行があまりにも多数にわたって、修正漏れが発生する
といった恐れがあったためです。
更に、過去に他のメンバーが、
- そのRDSからエクスポートしたデータのインポートに成功したことがある
- その際、dumpファイルを直接編集したりはしていない
という前情報もあったので、この方法は採用しませんでした。
3-3. DEFINERを除去する
この方法は試しましたが、権限エラーは結局消えませんでした。
うまくいく方もいらっしゃるかもしれないので、dumpファイル作成時のコマンドを記載しておきます。
$ mysqldump -u user_name -p -h host_name --quick --single-transaction database_name | sed -e 's/DEFINER[ ]*=[ ]*[^*]*\*/\*/' > dump.sql
| sed -e 's/DEFINER[ ]*=[ ]*[^*]*\*/\*/' の部分が肝なようです。
3-4. dumpファイルのエクスポートオプションを工夫する
mysqldump 実行時のいくつかのオプションのオンオフを試行錯誤したところ、 以下のオプション設定で冒頭のエラーを回避できました。
$ mysqldump -u user_name -p -h 127.0.0.1 -P 12345 --quick --set-gtid-purged=OFF --skip-column-statistics db_name | sed -e 's/DEFINER[ ]*=[ ]*[^*]*\*/\*/' > sample.sql
ポイントは --set-gtid-purged=OFF というオプションです。
それ以外のオプションは、本件エラーとは恐らく関係ありません。
このオプションの意味が知りたい方はこのリンク先(4.5.4 mysqldump — データベースバックアッププログラム)を確認してみてください。
※| sed -e 's/DEFINER[ ]*=[ ]*[^*]*\*/\*/'は、無くても問題ないかもしれませんが、この有無で1時間以上待たされるのは嫌だったので、付けたまま実行しました。
4. 補足
ところで、以下のエラー文言内の<行番号>の位置に何が書いてあったかが気になりますね。
ERROR 1227 (42000) at line <行番号>: Access denied; you need (at least one of) the SUPER privilege(s) for this operation
dumpファイルをlessコマンド1で開くと、該当行には次のように書かれていました。
SET @MYSQLDUMP_TEMP_LOG_BIN = @@SESSION.SQL_LOG_BIN;
これが原因であり、先ほどの --set-gtid-purged=OFF オプションによって、こうした行が全て取り除かれたようです。
5. 参考記事
5-1. MySQLバージョン確認方法
5-2. エクスポート失敗に関する記事
最初はここからつまづいて、この記事に助けられました。
mysqldump (client)のバージョンが8.0で、RDSのMySQL側が5.7の方はこの問題が発生するんじゃないかなと思います。
5-3. --set-gtid-purged について
--set-gtid-purgedというオプションの意味は以下のリンク先に書いてあります。
-
lessコマンドは、ファイルを開く際に全体を読み込まずに、先頭のごく一部だけを読み込むので、大規模なファイルも待ち時間なしで開くことができます。 ↩