Posted at

AWS移行 - mysql, postgres(RDS)リストア


概要

○環境 移行前、移行後

・PHP5.2 -> 5.3

・Mysql5.2 -> 5.5(RDS)

・Postgres9.1 -> 9.5


DB(Mysql)AWS移行、3パターン

・sql形式でのdump&リストア

・tsv形式でのdump&リストア

・レプリケーション

の3つを紹介しています。

今回のデータが特別だったたけで、通常は3つとも成功すると思います。


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、データの3つのファイルにわけてダンプをし、

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

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


postgresでのデータ移行

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


1.postgresqlに実行中プロセスが無いことを確認(10分)

readonlyに変更(やらなくてもいいかも)

alter database DB set default_transaction_read_only = on;


2.dump(10分)

pg_dump database > backup_file


3.データ移動(5分)

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


4.解凍(5分)

gzip -d xxx.sql.gz


5.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