概要

○環境　移行前、移行後
・PHP5.2 -> 5.3
・Mysql5.2 -> 5.5(RDS)
・Postgres9.1 -> 9.5

DB（Mysql)AWS移行、３パターン

・sql形式でのdump&リストア
・tsv形式でのdump&リストア
・レプリケーション
の３つを紹介しています。
今回のデータが特別だったたけで、通常は３つとも成功すると思います。

sql形式でのdump&リストア

RDSへのデータ移行には、AWSのマイグレーションサービスを使えれば便利だが、これはMysql5.5からしかサポートしていない。
今回はMysq5.2からの移行なのでMysqldumpで移行を行う。

今回のDBサイズは70GBあるので、圧縮状態でdumpを行い、RDSに対しリストアします。
ただし、以下のように全てのデータを一つのファイルでdumpすると失敗します。

データのdump+圧縮>リストア

mysqldump -u USERNAME -p DBNAME | gzip > /home/user/dumpfilename.gz
zcat /home/user/dumpfilename.gz | mysql -u USERNAME -p DBNAME -h xxxxxx.rds.amazonaws.com

error内容

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

viewやtriggerは作成ユーザーの情報を持っており、リストア時にそのユーザーでないとエラーが出ます。
Super権限を持つユーザーでリストアを行えば解決できるのですが、
RDSではSuper権限が付与されたユーザはAWS側が管理用に確保しているrdsadminというユーザのみになっており、自身で作成したユーザにはSuper権限が付与できません。
そのためdumpファイル側に工夫を施し、権限が無くてもリストアできるかたちにする必要があります。

ダンプファイルを工夫

ダンプファイルを、テーブルやView、Trigger、データの３つのファイルにわけてダンプをし、
ViewとTriggerのSqlファイルから作成者情報を削除します。

データのみダンプ

mysqldump -u user -p db_name --no-create-info --skip-triggers --skip-dump-date | gzip > onry_data.gz

・テーブルとビューのみダンプ

テーブルとビューのみダンプ

mysqldump -u user -p db_name --no-data --skip-triggers --skip-dump-date > only_schema.sql
sed -i -e '/^\/\*!50013 DEFINER=/d' only_schema.sql

・トリガーとストアドのみダンプ

トリガーとストアドのみダンプ

mysqldump -u user -p db_name --no-create-info --no-data --routines --skip-dump-date > only_trigger.sql
sed -i -E 's/\/\*\!500.. DEFINER=`.*`@`.*`\*\/ //g' only_trigger.sql

これによりテーブル、ビュー、トリガーの作成には成功したが、データのインポートに失敗します。

データimportエラー

ERROR 2013 (HY000) at line 8304: Lost connection to MySQL server during query

line 8304 で何を実行しようとしているか確認をしたいですが、大容量ファイルのため通常のエディタでは開けません。
大容量ファイルを開けるEmEditorを使用して中身を見てみます。
するとデータが文字化けしていたことが原因だとわかりました。

TSV形式でのインポート

文字化けしているデータは複数レコード存在した。
これを逐一修正するのは時間がかかるので、TSV形式でdump -> import を実施。
dumpで権限エラーが出る場合、rootで行えるならばスムーズに実施できる。

TSV形式でdump

mysqldump -u {USER} -p --fields-terminated-by="\t" {DB名} {TABLE名} --tab=/tmp(保存ディレクトリ)

Import。対象RDSへアクセスし以下を実行

mysql> load data local infile '/home/xxxxx.tsv' into table {TABLE名};

レプリケーション

上記でデータの移行には成功したが、
実際には以下のように、稼働中のDBとレプリケーションすることで対応した。

オンプレ稼働中DB <-レプリケーション-> EC2上のDB <-レプリケーション-> 移行後のRDS

中間に１つEC2を挟んでいるのは、稼働中のDBと、移行後のRDSでは直接レプリケーションができなかったためです。

postgresでのデータ移行

postgresではひっかかるところは無く、すんなり移行が出来た。

１．postgresqlに実行中プロセスが無いことを確認（10分）

readonlyに変更（やらなくてもいいかも）

alter database DB名 set default_transaction_read_only = on;

２．dump（10分）

pg_dump database名 > backup_file名

３．データ移動（5分）

リストア作業をするサーバーへ移動

４．解凍（5分）

gzip -d xxx.sql.gz

５．RDSへリモート接続でリストア　（15分）

# マスターログイン
psql -h ホスト名 -U ユーザー名 -d DB名

# DB 削除　（一度でもデータインポートした実績がある場合はDBごと削除すると楽）	
drop database DB名;

# 再度DB作成
create database DB名 encoding 'UTF8';

# postgres一旦ログアウト　
Ctrl + d

# データロード
psql -U postgres -h ホスト名 DB名 < XXXXXXXXXXXXX.sql