リモートにあるMySQLの大量データをCSVで出力してデータ移行

大量データを移行する必要があって調べていたら
AWSさんには良い記事がありました。

docs.aws.amazon.com

ただ困ったことに、オロゴンリージョンのRDSからから東京リージョンのRDSへ
1Tぐらいのデータを移さないといけなかったので
・SSHでDBに入れない
・データが超でかい
の状況でデータ移行をする方法を模索しました

MySQLからCSVでデータを取得してくる

最終的にはコマンドでテーブル毎にデータを流し込むことになるので
データをCSVで取得してきます。
「--tab」 オプションを使う方法だとDBサーバーのローカルにファイルが作られるので色々具合が悪いです。

qiita.com

queryを分割してしまうと「--single-transaction」オプションが使えず
レプリケーションの設定とかはできないので注意が必要です。
今回はテストデータ移行で特に同期は必要ないのでサクッと取得します。

SELECTしたらsedでCSVに変換

MySQLには標準でCSV出力の機構がないので
取得データをsedで整形してリダイレクトで吐き出す事になるのですが
日本語データとかブログ記事データとか含まれているのでちょいちょい面倒くさい感じになりました。

参考にしたのは以下

qiita.com

qiita.com

もろもろ検討して結果的に以下のshell fileを作って実行しました。

#!/bin/bash
TABLES=(tableName1 tableName2 tableName3)
for item in ${TABLES[@]}; do
echo $item
MYSQL_PWD=password mysql my_db -u db_user -h hostname -N -B -e "select * from ${item};" | sed -e 's/\"/\\\"/g' -e 's/\t/","/g' -e 's/^/\"/' -e 's/$/\"/' > ${item}.txt &
done;

・書き出し部分はテーブルによって重たいので、バックグラウンド実行にして並列取得
・passwordを毎回聞かれると辛いのでコマンド実行時に環境変数に設定する（非推奨）

という感じで取得しました。

圧縮して送信

このあたりは余裕ですね。

tar zcvf dump.tar.gz data

こんな感じで良いと思います。
あとはscpなりsftpなりでざっくり送ります。
個人的にはsftpが好きなので今回はsftpでこんな感じです。

sftp -oIdentityFile=~/.ssh/ssh_key.ppk tanaka@hostname

ファイルが取得できました。

解凍して挿入

挿入もカチカチのままの方がtar.gzを展開してcsvの取り込みを行います。

DBのテーブルデータは既に作られている前提です。

展開はこんな感じでサクッといきます。

tar zxvf dump.tar.gz

展開されたら挿入も並列実行でガリガリやっていきましょう。
AWSのスクリプトを参考にしつつ、極度の面倒くさがりなので、こちらのshell作りました。

#!/bin/bash
TABLES=(tableName1 tableName2 tableName3)
for item in ${TABLES[@]}; do
echo $item
MYSQL_PWD=password mysql -u db_user -h hostname me_db -e'LOAD DATA LOCAL INFILE \'${item}.txt\' INTO TABLE ${item} FIELDS TERMINATED BY \',\' ENCLOSED BY \'"\' LINES TERMINATED BY \'0x0d0a\';' >> ~/import.log &
done;

あとはshellが勝手にやってくれるので放置でOKです。
進行状況はプロセス監視になるので
以下のコマンドで様子を見る感じになります。

ps aux | grep mysql

やばそうならkillしてテーブルDROPして再実行かなと・・・。

まとめ

テラバイト級のデータの扱いはミドルウェアに任せる部分が多いので
いざ平文で触ろうとするとなかなか辛いところが多いですね。
どう作業しても時間はかかるので
ゆったりした気持ちで進めたいものです。
（今度本番データの移行があるからビビってる）