RDSにスナップショットのdumpファイルをインポート #MySQL

とあるシステムのステージング環境のDBをいじっていた、ある日—。

show tables;
>> Empty set (0.00 sec)

スナップショット復元

AWS RDS コンソールから「スナップショット」>「アクション」>「復元」
立ち上がったスナップショットDBのインスタンスを選択し、変更を行う
- 「認証情報管理」>「AWS Secrets Manager で管理」を選択し、保存
- 必要に応じて正しいセキュリティグループを当てる（もともとのRDSに当ててるやつと同じにするとか）
Secrets ManagerからスナップショットDBのユーザー名とパスワードを確認

スナップショットDBの中身をエクスポート

Secrets Managerから確認できる、スナップショットDBのユーザー名とパスワードを使って、DBの中身をエクスポートします。

指定するDB名ですが、RDSのコンソールでは「-」みたいに表示されてDB名が分からないかもしれませんが、おそらくスナップショットを取った時点でDB名も引き継いでいるはずなのでそれと同じものになると思います。
もしそれすら忘れてしまったら、MySQLにログインしてデータベース一覧を確認しましょう。

# ログイン
mysql -h スナップショットのエンドポイント -u ユーザー名 -p

SHOW DATABASES;

エクスポートを実行します。

mysqldump -h スナップショットのエンドポイント -u ユーザー名 -p DB名 > dump.sql --set-gtid-purged=OFF

dump.sqlというファイルが生成されます。この中にデータを復元させるためのSQLコマンドがたくさん入ってます。実際に覗いてみると分かりやすいので見てみてください。

別のRDSにインポート

私はcdkでRDSを定義していたため、cdkでRDSを再生成しました。
とにかく、インポート先のDBを用意します。

そしてそこに、先ほどのdump.sqlを読み込ませてデータをインポートします。

そして本来なら以下の方法でインポートできるはず。

# よく見るやり方
mysql -h インポート先のDBエンドポイント -u ユーザー名 -p DB名 < dump.sql

しかし、私の場合は以下の権限エラーが起こりました。

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

このエラーに関しては調べたらたくさん記事が出てきて、
例えばdumpファイルからDEFINERを消すとか、
ファイルの冒頭にあるSET3行をコメントアウトするとか、
mysqldump実行時に--set-gtid-purged=OFFを入れる（今回は上記ですでに入れている）
などをすれば解消されるとのことでしたが、解消されず。

よくわかりませんが、外から実行できないなら一旦中に入って直接実行させてみようということで、インポート先のDBにログインしちゃいます。
（root権限があるユーザーである前提）

# mysqlログイン
mysql -h インポート先のDBエンドポイント -u ユーザー名 -p

データベースを作成（なければ）

CREATE DATABASE `DB名`;

データベースを指定

USE DB名;

そしてSOURCEコマンドでdumpファイルの中身を実行させます。

SOURCE /home/ec2-user/dump.sql;

これだとうまくいきました。

言うまでも無いですが上記コマンドのdumpファイルのパスはpwdとかで確認すれば良いです。（もちろんmysqlからログアウトした状態で）

pwd dump.sql
> /home/ec2-user

なぜmysqlにログインしてからSOURCEコマンドじゃないとできないのか

claudeに聞いてみた

mysql -h <エンドポイント> -u <ユーザー名> -p <DB名> < dump.sql
この方法では、MySQLクライアントにdump.sqlファイルの内容が直接渡されます。このとき、ファイル内のSQLコマンドが順次実行されますが、SQLコマンドの中に権限不足のものがあると、エラーが発生してしまいます。
SOURCE /home/ec2-user/dump.sql;
この方法では、MySQLクライアントにログイン後、SOURCEコマンドを使ってdump.sqlファイルを読み込んでいます。この場合、ファイル内のSQLコマンドはMySQLサーバー側で実行されるため、クライアント側の権限とは別に、MySQLユーザーアカウントの権限が適用されます。

つまり、1の方法ではMySQLクライアントプロセスの権限でSQLコマンドが実行されるのに対し、2の方法ではMySQLサーバープロセスの権限でSQLコマンドが実行されるためです。

エラーメッセージから、使用しているMySQLユーザーアカウントにSUPERまたはSYSTEM_VARIABLES_ADMIN権限がないということがわかります。SOURCEコマンドを使った2の方法ではこの権限は必要ない ため、エラーが発生しませんでした。

一般的に、SQLスクリプトをインポートする際は、SOURCEコマンドを使う方が安全です。ただし、本番環境ではスーパーユーザー権限を持つユーザーを使用するのは避けるべきです。代わりに、必要最小限の権限を持つアプリケーション専用のユーザーを作成し、そのユーザーでデータベースにアクセスすることをお勧めします。

だそうです。