MySQLのDBからAWSのRDSへデータをインポートする #AWS

旧DBからdumpファイルを取得してRDSにインポートしようとしたら結構手間取った。

mysqldumpの実行

まずは旧環境にログインしてdumpファイルの出力

$ mysqldump -uuser_name -p -hhost_name --quick --single-transaction database_name > dump.sql

出力されたファイルをデータ移行対象のサーバーに移す。

インポート先のデータベースを作成する

インポート先となるデータベースは先に作成しておく必要があるため作成する。

mysql> CREATE DATABASE database_name;

インポート実行ユーザーに対して作成したデータベースへの権限を付与する。
今回は全権限を付与する。

mysql> GRANT ALL PRIVELEGES ON 'database_name'.* TO 'user_name'@`host_name`;

権限が付与されていることを確認。

mysql> SHOW GRANTS FOR `user_name`@`host_name`;

いざデータのインポート

$ mysql -uuser_name -p -hhost_name database_name < dump.sql

ハマった

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

Super権限を持つユーザーでなければ操作が実行できないらしい。
しかもRDSではSuper権限が付与されたユーザはAWS側が管理用に確保しているrdsadminというユーザーのみ。
なのでインポートを実行するユーザーにSuper権限を付与することもできない。

原因

エラーコードを調べてみるとAWS公式からの回答があった。

このエラーが発生するには、宛先のデータベースでバイナリログが有効になっていて、mysqldump ファイルにオブジェクト (トリガー、ビュー、関数、またはイベント) が含まれている場合です。
create ステートメントに、"NO SQL"、"READS SQL DATA"、あるいは "DETERMINISTIC" などのキーワードを含まないものがある場合、MySQL はこれらのオブジェクトを作成することができず、インポートはエラー 1227 で失敗します。
この状況を緩和するには、RDS カスタムパラメーターグループの大域 log_bin_trust_function_creators システム変数に 1 を設定します。詳細については、「DB パラメーターグループのパラメーターの変更」を参照してください。

つまりDBパラメーターグループのlog_bin_trust_function_creatorsシステム変数を1に設定すれば解決できるっぽい。

補足

バイナリログもDBパラメーターグループも正直なんのこっちゃって感じだったので調べてみた。

バイナリログとは

テーブル作成操作やテーブルデータへの変更などのデータベース変更を記述する「イベント」が格納されるもの。
レプリケーションやデータリカバリに利用される。

DBパラメータグループとは

1つ以上のDBインスタンスに適用され、DBエンジンの設定を管理するエンジン設定値のコンテナーとして機能するもの。
DBインスタンス作成時にユーザー定義のパラメータグループが指定されなければデフォルトのパラメータグループが作成される。
このデフォルトのパラメータグループは変更不能。
動的なパラメータは変更されると即座に変更内容が反映される。
静的なパラメータは変更後、DBインスタンスが手動で再起動されて初めて変更が反映される。

詳細はそれぞれ下記リンクを参照

MySQL :: MySQL 5.6 リファレンスマニュアル :: 5.2.4 バイナリログ
 DB パラメータグループを使用する - Amazon Relational Database Service

対応

DBパラメータグループのパラメータを変更

AWSのRDSコンソールからパラメータを変更。
反映させるためにDBインスタンスの再起動が必要な場合もあるが、今回は再起動の必要なく変更が反映された。
コンソール上で反映が完了したことが確認できたので、念のためmysqlからも確認する。

mysql> SHOW VARIABLES LIKE '%log_bin%';
+---------------------------------+--------+
| Variable_name                   | Value  |
+---------------------------------|--------+
| log_bin_trust_function_creators | ON     |
+---------------------------------|--------+

よし。これでうまくいくはず。

ハマった(再び)

$ mysql -uuser_name -p -hhost_name database_name < dump.sql
ERROR 1227 (42000) Access denied; you need (at least one of) the SUPER privilege(s) for this operation

いや、なんでや。。。

どうやらdumpファイルの中にtriggerが含まれているとうまくいかないらしい。

triggerを除いたdumpファイルを作成

triggerを確認する。

mysql> SHOW TRIGGERS;

triggerを除くように--skip-triggersオプションを追加。

$ mysqldump -uuser_name -p -hhost_name --quick --single-transaction --skip-triggers database_name > dump.sql

追記(2019.4.6)

ーーー追記開始ーーー

@clfsoft さんからご指摘をいただいた。

triggerに限らず、DEFINERが定義されているものであれば全てviewに影響してしまうので、~~インポートしたければ--skip-definerオプションをつけてDEFINERを取り除く必要があるらしい。~~

今回はたまたまtriggerだけが定義されていたが、プロシージャが定義されていればここでまたハマっていたと思う。

~~つまりコマンドは下記のようになる~~

$ mysqldump -uuser_name -p -hhost_name --quick --single-transaction --skip-definer database_name > dump.sql

ーーー追記終了ーーー

さらに追記(2019.4.10)

指摘いただいた内容自体を勘違いしていた。
--skip-definerオプションはmysqlpump用のオプションであり、mysqldump用ではない。よって上記コマンドは誤り