[AWS / RDS] RDSのmysqlにdumpファイルをimport / restoreしようとしたら権限エラーが出た時の対処

# 1. 症状

以下のように、 `Access denied; you need (at least one of) the SUPER privilege(s) for this operation` というメッセージが出て、データのインポートができない。

```bash
$ 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. 動作環境

```bash:bash
# 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. 対処法について

ネット上で調べたところ、解決策が書かれている記事がいくつかありましたが、ヒットした記事だけでは私の問題の解決には至りませんでした。

ただ、エラー原因が私とは異なる場合には、それらの記事の方法が役立つかもしれませんので、それらの方法も簡単に記載します。

1. SUPER権限を付与 (❌)
1. dumpファイルを修正する +α (🔺不採用)
1. DEFINERを除去する (❌)
1. dumpファイルのエクスポートオプションを工夫する (⭕️これで解決)


## 3-1. SUPER権限を付与

私は、この方法ではうまくいきませんでした。

http://cannano.com/lab/database/mysql/490/

というのも、この記事の方法では、私の使っているmysqlのユーザにSUPER権限を付与できなかったためです。

RDSだからか、もしくは私に権限が不足していたからなのかなと思います。
うまくいく方もいらっしゃると思いますので、試してみる価値はあると思います。

## 3-2. dumpファイルを修正する +α

この方法は、私は採用しませんでした。

https://yoneyore.hatenablog.com/entry/2020/07/15/205611

というのは、私のdumpファイルはGB単位のファイルだったので、

- エディターで開くだけでかなりの時間がかかる
- 修正が必要な該当行があまりにも多数にわたって、修正漏れが発生する

といった恐れがあったためです。

更に、過去に他のメンバーが、

- そのRDSからエクスポートしたデータのインポートに成功したことがある
- その際、dumpファイルを直接編集したりはしていない

という前情報もあったので、この方法は採用しませんでした。


## 3-3. DEFINERを除去する

この方法は試しましたが、権限エラーは結局消えませんでした。

https://qiita.com/hikey/items/ab5fca33849d3e3929cb

うまくいく方もいらっしゃるかもしれないので、dumpファイル作成時のコマンドを記載しておきます。

> ```bash
> $ mysqldump -u user_name -p -h host_name --quick --single-transaction database_name　| sed -e 's/DEFINER[ ]*=[ ]*[^*]*\*/\*/' > dump.sql
> ```
> https://qiita.com/hikey/items/ab5fca33849d3e3929cb#%E3%81%95%E3%82%89%E3%81%AB%E8%BF%BD%E8%A8%982019410

`　| sed -e 's/DEFINER[ ]*=[ ]*[^*]*\*/\*/'` の部分が肝なようです。


## 3-4. dumpファイルのエクスポートオプションを工夫する

`mysqldump` 実行時のいくつかのオプションのオンオフを試行錯誤したところ、 以下のオプション設定で冒頭のエラーを回避できました。

```bash
$ 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 — データベースバックアッププログラム)](https://dev.mysql.com/doc/refman/8.0/ja/mysqldump.html#option_mysqldump_set-gtid-purged)を確認してみてください。

※`| sed -e 's/DEFINER[ ]*=[ ]*[^*]*\*/\*/'`は、無くても問題ないかもしれませんが、この有無で1時間以上待たされるのは嫌だったので、付けたまま実行しました。


# 4. 補足

ところで、以下のエラー文言内の`<行番号>`の位置に何が書いてあったかが気になりますね。

```bash
ERROR 1227 (42000) at line <行番号>: Access denied; you need (at least one of) the SUPER privilege(s) for this operation
```

dumpファイルを`less`コマンド[^1]で開くと、該当行には次のように書かれていました。

[^1]: `less`コマンドは、ファイルを開く際に全体を読み込まずに、先頭のごく一部だけを読み込むので、大規模なファイルも待ち時間なしで開くことができます。

```sql
SET @MYSQLDUMP_TEMP_LOG_BIN = @@SESSION.SQL_LOG_BIN;
```

これが原因であり、先ほどの `--set-gtid-purged=OFF` オプションによって、こうした行が全て取り除かれたようです。



# 5. 参考記事

## 5-1. MySQLバージョン確認方法

https://qiita.com/rokumura7/items/b270acb9550efddd5fe5


## 5-2. エクスポート失敗に関する記事

最初はここからつまづいて、この記事に助けられました。

https://qiita.com/JINr/items/eed1ba183438a0f6fe23

mysqldump (client)のバージョンが8.0で、RDSのMySQL側が5.7の方はこの問題が発生するんじゃないかなと思います。

## 5-3. `--set-gtid-purged` について

`--set-gtid-purged`というオプションの意味は以下のリンク先に書いてあります。

https://dev.mysql.com/doc/refman/8.0/ja/mysqldump.html#option_mysqldump_set-gtid-purged