Help us understand the problem. What is going on with this article?

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
ryokwkm
エンジニアをやめて俳優として生きていこうとしているダメ人間 ちょっと中二っぽいのが特徴
http://ryokwkm.hatenablog.com/
Why not register and get more from Qiita?
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
Comments
No comments
Sign up for free and join this conversation.
If you already have a Qiita account
Why do not you register as a user and use Qiita more conveniently?
You need to log in to use this function. Qiita can be used more conveniently after logging in.
You seem to be reading articles frequently this month. Qiita can be used more conveniently after logging in.
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
ユーザーは見つかりませんでした