Cloudn PaaS v2(CloudFoundry)の Service(PostgreSQL & MySQL）に接続

Coundry v2 Service(PostgreSQL & MySQL）等に接続

Cloud Foundry上のServiceについて

Cloud Foundryではcli(cfコマンド）から

cf create-service postgresql default pg-instance
          or 
cf create-service mysql default mysql-instance

といったコマンドを使ってPostgreSQLやMySQLとアプリケーションを簡単に紐付ける事が可能となります。

Cloud Foundry v1の頃に提供されていたcli(vmcコマンド）では、vmc tunnel(通称：caldecott)を実行する事でcreate-serviceしたサービスに接続しローカル環境から
『psqlコマンド』や『mysql』を使ってデータベースの操作をする事が可能でした。

Cloud Foundry v2になってからはvmc tunnelは廃止されてしまいpsqlなどのコマンドを使ってdbの操作が出来なくなってしまい、私自身不便さを感じていました。

※vmc tunnelについての参考URL: http://blog.udcp.net/2011/11/22/vmc-tunnel/

今回はGo言語で実装されたTCP tunnel over HTTPアプリケーションであるchiselをベースに
Cloud Foundry v2向けに実装した以下のコードを使い

Cloud Foundry v2をベースにサービス提供を行っている
『Cloudn PaaS』上のpostgresqlやmysqlに接続する方法を紹介したいと思います。

chiselの準備

gitコマンドを使ってソースを取得

$ git clone https://github.com/morikat/chisel
$ cd chisel
$ ls
Godeps  Procfile  README.md  bin  chisel-bin  client  example  main.go  manifest.yml  server  share  test

Cloudn PaaSにCFコマンドからログイン

$ cf api https://api.paas.jp-e1.cloudn-service.com
$ cf login
ユーザIDとパスワードを入力

※ユーザIDとパスワードはCloudn Portalサイトにログインした状態で
APIアクセスキー・秘密鍵管理にアクセスすることで確認可能です

サービス作成

ここでは
mysql用に『chisel-mysql-db』
postgresql用に『chisel-pg-db』
というサービス名でサービスの作成を行います

mysqlの場合

$ cf create-service mysql default chisel-mysql-db
Creating service chisel-mysql-db in org clnxxxxxxxxx / space default as Dxxxxxxxxxxxxxxxx...
OK

※この例の場合chisel-mysql-dbがデータベースインスタンス名

postgresqlの場合

$ cf create-service postgresql default chisel-pg-db   Creating service chisel-pg-db in org clnxxxxxxxxxxxx / space default as D0xxxxxxxxxxxxxxxxx...
OK

※この例の場合chisel-pg-dbがデータベースインスタンス名

chiselをCloudn PaaS上にpush（デプロイ）

ここでは
mysql用に『chisel-mysql』
postgresql用に『chisel-pg』
というアプリ名でpushします

mysql用のアプリpush

$ cf push chisel-mysql --no-start
Using manifest file /home/morika-t/golang/chisel_gopath/src/github.com/morikat/chisel/manifest.yml

Creating app chisel-mysql in org clnXXXXXX / space default as DXXXXXXX...
OK

Creating route chisel-mysql.paas.jp-e1.cloudn-service.com...
OK

Binding chisel-mysql.paas.jp-e1.cloudn-service.com to chisel-mysql...
OK

Uploading chisel-mysql...
Uploading app files from: /home/morika-t/golang/chisel_gopath/src/github.com/morikat/chisel
Uploading 500.6K, 130 files
Done uploading
OK

※push時に--no-startオプションを指定しているのは先ほど作成したサービスと紐付けを行ってからアプリを起動させる為です

postgresql用のアプリpush

$ cf push chisel-pg --no-start
Using manifest file /home/morika-t/golang/chisel_gopath/src/github.com/morikat/chisel/manifest.yml

Creating app chisel-pg in org clnxxxxxxxxxxx / space default as DXXXXXXXX...
OK

Using route chisel-pg.paas.jp-e1.cloudn-service.com
Binding chisel-pg.paas.jp-e1.cloudn-service.com to chisel-pg...
OK

Uploading chisel-pg...
Uploading app files from: /home/morika-t/golang/chisel_gopath/src/github.com/morikat/chisel
Uploading 500.6K, 130 files
Done uploading
OK

※push時に--no-startオプションを指定しているのは先ほど作成したサービスと紐付けを行ってからアプリを起動させる為です

アプリとサービスの紐付けとアプリ起動

mysqlの紐付け

$ cf bind-service chisel-mysql chisel-mysql-db
Binding service chisel-mysql-db to app chisel-mysql in org clnXXXXXX / space default as DXXXXXXXXXX...
OK
TIP: Use 'cf restage' to ensure your env variable changes take effect

$ cf start chisel-mysql
Starting app chisel-mysql in org clnxxxxxxxxxx / space default as Dxxxxxxxxxxx...
-----> Downloaded app package (188K)
Cloning into '/tmp/buildpacks/go-buildpack'...
Submodule 'compile-extensions' (https://github.com/cloudfoundry-incubator/compile-extensions.git) registered for path '$
ompile-extensions'
Cloning into 'compile-extensions'...
Submodule path 'compile-extensions': checked out '9f02e38433ca08c2e82cfec8844db9dd26acb336'
-------> Buildpack version 1.2.0
-----> Installing go1.4... done
-----> Running: godep go install -tags cloudfoundry ./...
-----> Uploading droplet (4.2M)

0 of 1 instances running, 1 down
0 of 1 instances running, 1 down
0 of 1 instances running, 1 starting
1 of 1 instances running


App started


OK

App chisel-mysql was started using this command ``

Showing health and status for app chisel-mysql in org clnxxxxxxxxxxxxx / space default as Dxxxxxxxxxxxxxx...
OK

requested state: started
instances: 1/1
usage: 64M x 1 instances
urls: chisel-mysql.paas.jp-e1.cloudn-service.com
package uploaded: unknown

     state     since                    cpu    memory        disk          details
#0   running   2015-04-04 12:01:10 PM   0.0%   1.7M of 64M   14.9M of 2G

postgresqlの紐付け

$ cf bind-service chisel-pg chisel-pg-db
Binding service chisel-pg-db to app chisel-pg in org XXXXXX / space default as D0XXXXXXXXXXXXX...
OK
TIP: Use 'cf restage' to ensure your env variable changes take effect

$ cf start chisel-pg
Starting app chisel-pg in org cln100050646 / space default as D0P3SCC9GGBFUJVNCK63...
-----> Downloaded app package (188K)
Cloning into '/tmp/buildpacks/go-buildpack'...
Submodule 'compile-extensions' (https://github.com/cloudfoundry-incubator/compile-extensions.git) registered for path 'c
ompile-extensions'
Cloning into 'compile-extensions'...
Submodule path 'compile-extensions': checked out '9f02e38433ca08c2e82cfec8844db9dd26acb336'
-------> Buildpack version 1.2.0
-----> Installing go1.4... done
-----> Running: godep go install -tags cloudfoundry ./...
-----> Uploading droplet (4.2M)

0 of 1 instances running, 1 down
0 of 1 instances running, 1 down
0 of 1 instances running, 1 starting
0 of 1 instances running, 1 starting
1 of 1 instances running

App started


OK

App chisel-pg was started using this command ``

Showing health and status for app chisel-pg in org clnXXXXXXXX / space default as D0XXXXXXXXXXX...
OK

requested state: started
instances: 1/1
usage: 64M x 1 instances
urls: chisel-pg.paas.jp-e1.cloudn-service.com
package uploaded: unknown

     state     since                    cpu    memory        disk          details
#0   running   2015-04-04 12:15:56 PM   0.0%   1.8M of 64M   14.9M of 2G

mysqlやpostgresqlに接続

ここまでで事前準備が整っている為、
後は

./bin/caldecott.sh アプリ名

と入力する事でchisel clientを経由してmysqlコマンドやpsqlコマンドの利用が可能となります。
※内部的にjqコマンドとmysqlコマンドおよびpsqlコマンドを利用する為、事前にインストールが必要です。

mysqlの場合

$ ./bin/caldecott.sh chisel-mysql
2015/04/04 12:31:22 client: Connecting to ws://chisel-mysql.paas.jp-e1.cloudn-service.com:80
2015/04/04 12:31:22 client: 153.149.5.25:3307#1: Enabled
2015/04/04 12:31:22 client: Fingerprint 3e:ab:a8:10:a4:f9:ba:03:2d:35:f9:97:c4:df:b6:1e
2015/04/04 12:31:22 client: Sending configurating
2015/04/04 12:31:22 client: Connected (Latency 8.596974ms)
2015/04/04 12:31:25 client: 153.149.5.25:3307#1: conn#1: Open
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 13027236
Server version: 5.5.41 Source distribution

Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> help

For information about MySQL products and services, visit:
   http://www.mysql.com/
For developer information, including the MySQL Reference Manual, visit:
   http://dev.mysql.com/
To buy MySQL Enterprise support, training, or other products, visit:
   https://shop.mysql.com/

List of all MySQL commands:
Note that all text commands must be first on line and end with ';'
?         (\?) Synonym for `help'.
clear     (\c) Clear the current input statement.
connect   (\r) Reconnect to the server. Optional arguments are db and host.
delimiter (\d) Set statement delimiter.
edit      (\e) Edit command with $EDITOR.
ego       (\G) Send command to mysql server, display result vertically.
exit      (\q) Exit mysql. Same as quit.
go        (\g) Send command to mysql server.
help      (\h) Display this help.
nopager   (\n) Disable pager, print to stdout.
notee     (\t) Don't write into outfile.
pager     (\P) Set PAGER [to_pager]. Print the query results via PAGER.
print     (\p) Print current command.
prompt    (\R) Change your mysql prompt.
quit      (\q) Quit mysql.
rehash    (\#) Rebuild completion hash.
source    (\.) Execute an SQL script file. Takes a file name as an argument.
status    (\s) Get status information from the server.
system    (\!) Execute a system shell command.
tee       (\T) Set outfile [to_outfile]. Append everything into given outfile.
use       (\u) Use another database. Takes database name as argument.
charset   (\C) Switch to another charset. Might be needed for processing binlog with multi-byte charsets.
warnings  (\W) Show warnings after every statement.
nowarning (\w) Don't show warnings after every statement.

For server side help, type 'help contents'
mysql> \q
2015/04/04 12:31:32 client: 153.149.5.25:3307#1: conn#1: Close (sent 204 received 0)
Bye

postgresqlの場合

$ ./bin/caldecott.sh chisel-pg
2015/04/04 12:38:10 client: Connecting to ws://chisel-pg.paas.jp-e1.cloudn-service.com:80
2015/04/04 12:38:10 client: 153.149.13.35:5434#1: Enabled
2015/04/04 12:38:10 client: Fingerprint 23:45:69:cd:00:1c:e5:fb:60:21:d8:f6:f2:c1:a4:76
2015/04/04 12:38:10 client: Sending configurating
2015/04/04 12:38:10 client: Connected (Latency 9.179384ms)
2015/04/04 12:38:13 client: 153.149.13.35:5434#1: conn#1: Open
psql (9.1.15, server 9.2.4)
WARNING: psql version 9.1, server version 9.2.
         Some psql features might not work.
Type "help" for help.

dce0aa91400ff4148be336655b716f0ea=> help
You are using psql, the command-line interface to PostgreSQL.
Type:  \copyright for distribution terms
       \h for help with SQL commands
       \? for help with psql commands
       \g or terminate with semicolon to execute query
       \q to quit
dce0aa91400ff4148be336655b716f0ea=> \q
2015/04/04 12:38:18 client: 153.149.13.35:5434#1: conn#1: Close (sent 188 received 0)