GCP(Google Cloud Platform)のCompute EngineのVMインスタンス(仮想マシン)のMySQLにデータをインポート

9月になっちゃいましたね....久々に部屋を掃除しようと朝から頑張っていたら、どうも1日では終わらんと見切りをつけて現実逃避をしてる今日この頃です。

というわけで、今回は、GCPVMインスタンス(OSがCentOS7の仮想マシン)にインストールしていたMySQLに、ローカル環境のMySQLからデータをエクスポートし、インポートしていきたいと思います。

エクスポートするデータベースの確認

まずは、ローカル環境(Windows 10 Home)側のMySQLにログインします。

f:id:ts0818:20170901230329j:plain

エクスポートするデータベースを確認。

f:id:ts0818:20170901230326j:plain

そうしたら、一旦、MySQLからログアウトします。

mysqldumpでエクスポート

厄介なのは、WindowsPowerShellなどでmysqldumpを行うと文字コードの問題で文字化けが起こることがあるようです。

MySQL - windows上でmysqldumpしたファイルがリストアできない(43154)|teratail

 

今回は、コマンドプロンプトで実行するので問題ないようです、たぶん。

で、ダンプした(mysqldumpすること、つまりエクスポート)ファイルを特に編集することがないならば、「default-character-set オプション設定」で「binary」(バイナリ)を指定してあげると良いようです。

その際は、インポート側でも同じように 「default-character-set オプション設定」で「binary」を指定してあげます。

mysqldump --default-character-set=[文字コード] -u [ユーザー名] -p [データベース名] > [出力ファイル名]

f:id:ts0818:20170902131814j:plain

実際に、「C:¥Users¥ユーザー名¥local_fs6.sql」ファイルが作成されました。 

f:id:ts0818:20170902133847j:plain

 

WinSCPGCPにエクスポートしたファイルをアップ

WinSCPを使って、GCPVMインスタンスにアップしていきます。 

GCPVMインスタンスが起動していることを確認し、

f:id:ts0818:20170902132909j:plain

WinSCPを起動し、前回作成した、GCPVMインスタンスへのセッション(接続)を選択し、「ログイン」をクリック。

f:id:ts0818:20170902133416j:plain

 「秘密鍵パスフレーズ」を聞かれるので入力し、「OK」をクリック。

f:id:ts0818:20170902133135j:plain

GCPVMインスタンスに接続されました。 

f:id:ts0818:20170902133842j:plain

とりあえず、「/<ルート>」をクリックして、「/home/ユーザー名」のフォルダに移動します。移動したら、さきほど作成したエクスポートファイルを「ドラッグ&ドロップ」します。

f:id:ts0818:20170902150905j:plain

そしたら、次はGCPVMインスタンスssh接続していきます。

 

GCPssh接続

ちょっと、今回は、Msys2のbashで新しい SSH 認証鍵ペア(新しい SSH 認証鍵ファイルとそれに対応する公開鍵)を生成し、GCPと接続してみたいと思います。(Msys2とか用意されてない方は、GCPの「gcloud」でssh接続できます。)

「ConEmu」を起動し、「{Bash::Msys2-64}」を選択します。

f:id:ts0818:20170902153612j:plain

ssh秘密鍵、公開鍵を作成します。「passphrase」は適当なものを入力。

ssh-keygen -t rsa -f ~/.ssh/[KEY_FILE_NAME] -C [USERNAME]

ssh-keygen ツールで認証鍵ペアを生成できます。Google ユーザー名を使ってコメントを追加するには、-C フラグを指定します。

  • [USERNAME] は、この SSH 認証鍵のユーザーです。
  • [KEY_FILE_NAME] は、認証鍵ファイルに使用する名前です。たとえば、値が my-ssh-key であれば、my-ssh-key という名前の秘密鍵ファイルと my-ssh-key.pub という名前の公開鍵ファイルが生成されます。

SSH 認証鍵の追加と削除  |  Compute Engine ドキュメント  |  Google Cloud Platform

「-Cフラグ」の説明がいまいちよく分からんです。 

f:id:ts0818:20170902153708j:plain

とりあえず、Msys2を使ってsshの鍵を生成すると、Msys2がインストールされてる場所にもよるのですが、「C:¥msys64¥home¥Toshinobu¥.ssh」 フォルダにできてます。

f:id:ts0818:20170902155149j:plain

f:id:ts0818:20170902161107j:plain

 

公開鍵(ここでは、「gcp-ssh-key.pub」) の内容を編集する必要があり、

[USERNAME]:ssh-rsa [KEY_VALUE] [USERNAME]

公開 SSH 認証鍵を設定する場合は、認証鍵にユーザー名の接頭辞を付ける必要があります。公開鍵ファイルを編集して、ユーザー名の接頭辞を含めます。次の例の場合、認証鍵には、認証鍵コメントのユーザー名に加えて、ユーザー名接頭辞も含まれます。

  • [USERNAME] は、この SSH 認証鍵のユーザーです。
  • [KEY_VALUE] は、生成される SSH 認証鍵の値です。

SSH 認証鍵の追加と削除  |  Compute Engine ドキュメント  |  Google Cloud Platform

公開鍵のファイルを開いて(本当はコマンド上でやったほうが良いと思われますが)、

f:id:ts0818:20170902160800j:plain

 先頭に「USERNAME:」 を付けて保存しましたら、駄目でした。

f:id:ts0818:20170902161213j:plain

これ、前も不備あったけど、GCPのマニュアル不備が多い気がします。なので、ここは、

ssh-rsa [KEY_VALUE] [USERNAME]

つまり、作成される公開鍵をそのまま使えば良さそうです。(末尾の「USERNAME」がない場合は、半角スペース空けて足せば大丈夫かと。)

そしたら、「アクセスを秘密鍵に制限することで、自分だけが読み取ることができ、誰も書き込めないようにすることができます」ようなので、

chmod 400 ~/.ssh/[KEY_FILE_NAME]

ここで、[KEY_FILE_NAME] は秘密鍵ファイルの名前です。

f:id:ts0818:20170902161103j:plain

としたんですが、chmodコマンドが効かないという...Msys2のbash駄目じゃん...。

⇩  ちなみに、git bashでも同じ問題があるようです

git bash(windows)でchmodが効かない件 | CheapArchitec

まぁ、ローカル環境のほうはあんまり権限関係ないのかな?

 

新しくsshの鍵を作らず、前に作ったことがあるし、それを使う場合もGCPでは、公開鍵の形式を「ssh-rsa [KEY_VALUE] [USERNAME]」という形にしてしまえば良いようです、と思ったけどなんか怪しいかもです。(ちょっと最近、自分の中でのGoogleの信用度が落ちてます)。

 

SSH 認証鍵の追加(公開鍵をGCPに登録)

公開 SSH 鍵(公開鍵)をGCPに登録していきます。GCPのダッシュボードで「メタデータ」を選択し、「SSH 認証鍵」を選択します。

f:id:ts0818:20170902163919j:plain

「編集」をクリック。

f:id:ts0818:20170902164209j:plain

「+項目を追加」をクリックします。 

f:id:ts0818:20170902164331j:plain

Msys2で、 

cat ~/.ssh/[公開鍵ファイル]

f:id:ts0818:20170902165554j:plain

「公開鍵」の内容が表示されるので、コピーし、GCP側で「SSH 認証鍵」で新しく追加されたテキストエリアに貼り付けて「保存」をクリック。

f:id:ts0818:20170902170648j:plain

新しい「SSH 認証鍵」が登録されました。

f:id:ts0818:20170902170644j:plain

 

SSH接続

Msys2からGCPssh接続します。

ssh -i ~/.ssh/my-ssh-key [USERNAME]@[IP_ADDRESS]

でいけるようです。[USERNAME]は、ここでは、「gcp-ssh」に、「IP_ADDRESS」はVMインスタンスの「外部 IP」でOKです。

接続できました!

f:id:ts0818:20170902190737j:plain

どうやら、ssh接続の際に接続先のGCPVMインスタンスに「ユーザー名」が存在しない場合、その「ユーザー名」を作成してくれるようです。

よって、新たなユーザー「gcp-ssh」がVMインスタンスに追加されたようです。

 

MySQLのデータをインポート

ようやく本題のMySQLのデータをインポートしていきたいと思います。

まずは、GCPVMインスタンスMySQLにデータベースを作成します。まずは、ログイン(MySQLに接続)します。

mysql -u root -p

f:id:ts0818:20170902192324j:plain

ローカル環境のMySQLのデータベース名と同じものを作成。

CREATE DATABASE [データベース名]

f:id:ts0818:20170902192534j:plain

作成できましたら、一旦、MySQLからログアウト(MySQLからの切断)します。

quit

f:id:ts0818:20170902192739j:plain

では、エクスポートしたファイルでデータをインポートします。 

エクスポートしたファイルを配置した(WinSCPでアップロードした)場所まで移動しておきます。

f:id:ts0818:20170902193602j:plain

では、いざ、尋常にインポート! 

 

 f:id:ts0818:20170902194409j:plain

 はい、エラー。ここでも権限ですかね?

どうやら、これは、「ユーザ名」と「パスワード」を使わないと駄目だったようです。

mysql -u[ユーザー名] -p --default-cahracter-set=[文字コード] [データベース名] < [ファイル名]

f:id:ts0818:20170902195202j:plain

MySQLにログインして確認してみます。

mysql -u root -p
USE [データベース名]
SHOW TABLES;

f:id:ts0818:20170902195656j:plain

テーブルがインポートされました! 

f:id:ts0818:20170902200504j:plain

データも入ってます。では、MySQLからログアウトし、Nginx、Tomcatを起動しブラウザでアクセスしてみます。

quit
nginx
systemctl start tomcat

f:id:ts0818:20170902202109j:plain

ブラウザからアクセスし、「ユーザー名」「パスワード」を入力するも、入れず! 

f:id:ts0818:20170902202105j:plain

淡い期待を込めて、MySQLサーバ起動してない?

f:id:ts0818:20170902202906j:plain

動いてらっしゃいました、疑ってすみませんでした。となると、JDBCドライバとかの問題としか思えないんですが。 

と思いきや、「/opt/tomcat/logs/ctalina.out」ファイルで、Eclipseでいうところの「コンソール」に出力されるものが確認できるようです。そこで、MySQLのユーザーで「owner」の文字が!

f:id:ts0818:20170902203839j:plain

GCP側のMySQLで、「owner」 ユーザーを作っていなかったし、JDBCドライバの接続情報のユーザーで「owner」を使っていたという...むちゃくちゃ怖いですね。

MySQLにログインして確認。

f:id:ts0818:20170902204758j:plain

やっぱり、「owner」ユーザーいないですね、だってGCP側のMySQLで作った覚えないし。ローカル環境の「mysql.user」テーブルのデータを持ってきたほうが良いのか?いや、rootのパスワード違っちゃってるし。

作りますか。そして、エラー。 

f:id:ts0818:20170902205531j:plain

どうやら、「パスワード」は、 

validate_password_policyの初期値はMEDIUMで、パスワード設定に必要な条件は『MEDIUM ポリシーは、パスワードが最低 1 つの数値文字を含み、1 つの小文字および大文字を含み、1 つの特殊文字 (英数字以外) を含む必要があるという条件を追加します。』

mysql5.7でパスワードを変更する - Qiita

が効いてるようです。Javaの講義では、「validate_password_policy」の値が変更されていてたみたい。

非常にマズイ、なぜなら、「DAO(DB接続系)クラス」のDB接続情報のパスワードも変更せねばならないからです。アップしたJavaファイルの一部だけ変更とかできるのか?

と、今回は、「SET GLOBAL validate_password_policy=LOW;」でパスワードのポリシーを変更することに。(実際の現場でやったらぶっ飛ばされそうな気がしますが)

ところが、しばらく放置してたらMySQLにつながらなくなったっす!

f:id:ts0818:20170902225423j:plain

いつのまにかMySQLが落ちてる!

f:id:ts0818:20170902225426j:plain

なんでなんだよ~!しばらく放置してただけでしょう、なんで落ちてんの?意味不明、怖~い。 いや、何もしてないのに勝手に不具合とかって、どんだけ~!?

どうすればいいんですか、先生!うちの子は助かるんでしょうか? 

手は尽くしましたが....いや~~~~~~っ!

って気持ちにもなりますわいな...。

ログを確認。

 tail -200 mysqld.log

InnoDB: mmap(137428992 bytes) failed; errno 12』 で検索したら、

AmazonAWSで、MySQL がよく落ちる? そんな時はSWAP領域をチェック! - Qiita

⇧  AWSAmazon Web Service)ですが、気になる記事が。

free

f:id:ts0818:20170903000602j:plain

Swap領域が無いのが問題だったようです。Swap領域を作成していきます。ddコマンドで指定サイズの空のファイルを作成します。サイズは1024MB。

dd if=/dev/zero of=/swapfile bs=1M count=1024

f:id:ts0818:20170903002207j:plain

mkswapで、スワップ領域を作成します。

mkswap /swapfile

f:id:ts0818:20170903002204j:plain

swaponコマンドで、スワップ領域を有効にします。

swapon /swapfile

f:id:ts0818:20170903002157j:plain

『free』コマンドで確認すると、スワップ領域ができてます!

再起動した時にマウントされないので、 /etc/fstab に以下の1行を追加します。

vi /etc/fstab

f:id:ts0818:20170903002154j:plain

MySQLを起動して、ステータスを確認。

f:id:ts0818:20170903003008j:plain

動き出しました!ログインして、「ユーザー」を追加していきます。

mysql -u root -p
SET GLOBAL validate_password_policy=LOW;

f:id:ts0818:20170903004051j:plain

文字の種類を緩めていけると思いきや、

GRANT ALL PRIVILEGES ON fs6project_db.* TO owner IDENTIFIED BY 'owner1';

f:id:ts0818:20170903004048j:plain

エラー。文字数を6文字以内ならokに。再度、ユーザーを作成。

SET GLOBAL validate_password_length=6;
GRANT ALL PRIVILEGES ON fs6project_db.* TO owner IDENTIFIED BY 'owner1';

f:id:ts0818:20170903004045j:plain

行けました!Nginx、Tomcatを再起動して、

f:id:ts0818:20170903004859j:plain

 ブラウザで「http://外部 IP/プロジェクト名/サーブレット名」にアクセス。データベースに登録された「ユーザー名」「パスワード」を入力で「ログイン」をクリック。

f:id:ts0818:20170902202105j:plain

「管理画面」にログインできました!無事、データベースとの連携も行えました。

f:id:ts0818:20170903005327j:plain

まだ、ところどころ不具合はありますが、なんとかローカル環境のEclipseで作成した「動的Webプロジェクト」を本番環境にデプロイできました。

とりあえず、このへんで。今回もハマりにハマりました。スワップとか調べてみないとですね。

  

MySQLのmysqldumpの文字コード

【MySQL】mysqldumpで移したDBの内容が文字化けするんですけど…. | ハックノート