More than 3 years have passed since last update.

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

Last updated at 2020-11-12Posted at 2018-06-08

旧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用ではない。よって上記コマンドは誤り

ーーー追記開始ーーー

正しいコマンドは以下のようになる。dumpファイルを作成してからDEFINERを取り除くという処理を行なっているらしい。

$ mysqldump -uuser_name -p -hhost_name --quick --single-transaction database_name　| sed -e 's/DEFINER[ ]*=[ ]*[^*]*\*/\*/' > dump.sql

ーーー追記終了ーーー

インポート実行

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

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

出典

You get articles that match your needs
You can efficiently read back useful information
You can use dark theme

What you can do with signing up