AWS移行 - mysql, postgres（RDS）リストア

#概要
○環境　移行前、移行後
・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