MySQL
AWS
RDS
mysqldump

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

旧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_finction_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 VALIABLES 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

インポート実行

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

今度こそうまくいった。
dumpとって移すだけだしすぐ済むだろとか思ってたら結構大変だったので備忘録として。

出典