おけらのブログ++

駆け出しWebエンジニアの奮闘記

ローカルDBでデータを作成し、AWSのDBにインポートする

database

やりたいこと

最近仕事でLaravelを使った開発をやっています。
AWSサーバーにあるDevelop環境にテストデータを追加したかったのですが、諸事情でいきなりAWSサーバーでSeederを実行するのはリスクがありローカルで作成したレコードをdumpしてAWSサーバーDBにインポートするという作業をしました。

f:id:Okerra:20190422225734p:plain

おおまかな流れ

  • ローカルDBのレコードをdumpする
  • AWS側のテーブルのidの連番になるようにdumpファイルを修正
  • AWSサーバーにdumpしたsqlファイルを移動する
  • AWSサーバーにdumpファイル をAWSサーバーのDBにインポートする

mysqldumpコマンドでレコードをdumpする

レコードをdumpするためにmysqldumpコマンドを使います。 説明は省きますがデータベース単位やテーブル単位など様々な用法があるようなので、それは下記参照ください。 qiita.com

今回はレコード単位でさらにテーブルの中のid=XXX以上のレコードをdumpしたかったためmysqldumpにWHEREオプションを合わせて実行します。

最終的なコマンド

$ mysqldump -u USERNAME --no-create-info DB_NAME TABLE_NAME --where="id >= 100" > dump.sql

--no-create-info
今回の場合AWS側のDBは既にあるテーブルにレコードを追加したかったため、このオプションをつけました。これをつけなければCREATE TABLEクエリが発行されるため既に登録されているレコードも全て初期化されてしまいます。

--where
『--where(-w)』オプションを指定することでいつものSQL文と同じように条件を絞ることができます

これでレコードのdumpファイルの完了です。

AWS側のidに連番となるようにdumpファイルを修正する

AWS側のテーブルには既にレコードが登録されており、それの最終idから連番となるように追加をしたいです。そのためにdumpファイルをちょっとだけ修正します。 登録済みのレコードそのままに追加するのではなく、テーブルごと置き換える場合は不要です。

修正前

INSERT INTO `TABLE_NAME` * VALUES (100,AAA,SATO,sato@test.com),(101,AAB,SUZUKI,suzuki@test.com),(102,AAC,YAMADA,yamada@test.com).........


修正後

INSERT INTO `TABLE_NAME` (user_id, name, email) VALUES (AAA,SATO,sato@test.com),(AAB,SUZUKI,suzuki@test.com),(AAC,YAMADA,yamada@test.com).........

ポイント

  • *の部分にauto incrementのid以外の全カラムを書く
  • 正規表現を使ってVALUESのid部分の値を削除する

こうすることで空となったid部分は、挿入先のテーブルの連番となるように追加されます。

ローカルサーバーのファイルをAWSサーバーに移動する

dumpファイル ができたら、もうあとは余裕だ！と思っていいたら思わぬところで躓きました。

dumpファイル をどうやってAWSサーバーに持っていくのか

最初はコピペしてみたもののサイズが大きすぎてvimが途中で止まってしまいました。そのためちゃんと調べたところscpコマンドというSSH先のサーバーにファイル転送するコマンドがありました。

scp コマンド
scpコマンドとはローカルサーバーからリモートサーバーにファイルを転送したり、その逆でリモートサーバーからローカルサーバーにファイルを転送したりできます。

今回は鍵認証を使ってローカルからリモートにコピーします。 鍵は既に~/.ssh/key.pemというファイルがあったのでこれを -i オプションを使って転送します。

# フォーマット
$ scp -i 秘密鍵ファイル　ファイル名　送信先のアドレス：ファイルを保存するディレクトリ

# 例
$ scp -i ~/.ssh/key.pem /{ローカルファイルのパス}/dump.sql ec2-user@XXX.amazonaws.com:/{保存するディレクトリパス}

参考 qiita.com docs.aws.amazon.com

dumpファイル をAWSサーバーのDBにインポートする

最後にAWS側のDBにmysqlコマンドを使用してdumpしたファイルをインポートさせます。

$ mysql -h XXX.amazonaws.com -u username -p DB_NAME < dump.sql


以上でローカルでテストデータを作成し、AWSのDBにインポートする方法でした。正直たかがテストデータを作成するだけでかなり時間がかかってしまいました。

おまけ

- mysqlテーブルのauto incrementの値を確認する

mysql> show table status like 'TABLE_NAME';

- mysqlテーブルのauto incrementの値を更新する

mysql> ALTER TABLE TABLE_NAME AUTO_INCREMENT = 1000;

- 組み込みエンジニアから念願のWebエンジニアに転職しました