GCP Cloud SQL(MySQL) 使い方

先日VPS(Indigo)のSQLite→MySQLの実験を行ってみたが、GCPでもやってみることにした。

初期設定

Cloud SQL 無料トライアルを実行する。
(既にクレジット等の情報は登録済みなのでそのまま完了)

コンソールにログイン＞左側メニューのSQLを選択。
プロジェクト(すでにMyFirstProjectが存在している)をクリック。
インスタンスを作成をクリック。
MySQLをクリック。

インスタンスID、rootパスワードを入力する。
無料トライアルだが、実際運用を始めれば安く利用したいので、
asia-northeast1(東京)/db-f1-micro
SSD/10GB
自動バックアップしない
を選択する。インスタンス生成には少し時間がかかる。

・インスタンス作成後

コンソールにログイン＞左側メニューのSQLを選択。
作成したインスタンスを選択。

左側メニューの接続を選択。
SSL接続のみ許可をクリック。
クライアント証明書を作成をクリックし、一意のIDを追加し作成。

・サーバー証明書：server-ca.pem
・クライアントの公開鍵証明書：client-cert.pem
・クライアント秘密鍵：client-key.pem
という３つのファイルをダウンロードする。

同じく左側メニューの接続から、ネットワークを追加をクリック。
ネットワークの項目：0.0.0.0/0
を追加し完了＞保存を実行。

左側メニューの概要を選択、パブリックIPアドレスを確認。

MySQL Wokbenchから接続

Standard TCP/IPを選択。

・Parametersタブ
Hostname：パブリックIPアドレス
Port：3306
Username：DBユーザー名(root、app_user等)
パスワード：DBパスワード

・SSLタブ
Use SSL：Require and Verify CA
SSL Key File, SSL CERT File, SSL CA File
それぞれ、ダウンロードしたファイルを選択。
※登録後ファイルのパス変更(移動)できないので注意。

とりあえずこれでつながる。

C#(MySqlConnection)から接続

.Netではpemが使えないため、
(https://dev.mysql.com/doc/connector-net/en/connector-net-tutorials-ssl-pfx.html)
opensslのある環境上で(今回はVPSで)実行

openssl pkcs12 -export -inkey client-key.pem -in client-cert.pem -out client.pfx
pfxファイルを作成する。(client部は任意の名前)

pfxパスワードを聞かれるが、空白も可能で空白の場合、接続文字列のCertificatePasswordは不要となる。

・ワンライナーで設定する場合
pass=$(head /dev/urandom | tr -dc a-km-np-z1-9 | head -c 13);openssl pkcs12 -export -inkey client-key.pem -in client-cert.pem -out client.pfx -passout pass:${pass};echo $pass;

・接続文字列

"server=IPアドレス;port=3306;database=DB名;user=MySQLユーザー名;password=MySQLパスワード;SslMode=Required;CertificateFile=test.pfx;CertificatePassword=pfxパスワード";

1	"server=IPアドレス;port=3306;database=DB名;user=MySQLユーザー名;password=MySQLパスワード;SslMode=Required;CertificateFile=test.pfx;CertificatePassword=pfxパスワード";

        public Form1()
        {
            InitializeComponent();

            string connectionString = "server=xxx.xxx.xxx.xxx;port=3306;database=xxx;user=xxx;password=xxx;" +
                "SslMode=Required;CertificateFile=xxx.pfx;CertificatePassword=xxx";

            using (MySqlConnection con = new MySqlConnection(connectionString))
            {
                con.Open();
                MySqlCommand cmd = new MySqlCommand("select * from 設計一覧 limit 1", con);
                using (MySqlDataReader reader = cmd.ExecuteReader())
                {
                    DataTable dt = new DataTable();
                    dt.Load(reader);
                    
                    // カラム名を指定してアクセス
                    MessageBox.Show(dt.Rows[0].Field<string>("案件名"));
                    
                    // 全てのカラムを取得
                    MessageBox.Show(string.Join(",", dt.Rows[0].ItemArray));

                }
            }
        }

public Form1()

{

InitializeComponent();

string connectionString = "server=xxx.xxx.xxx.xxx;port=3306;database=xxx;user=xxx;password=xxx;" +

"SslMode=Required;CertificateFile=xxx.pfx;CertificatePassword=xxx";

using (MySqlConnection con = new MySqlConnection(connectionString))

{

con.Open();

MySqlCommand cmd = new MySqlCommand("select * from 設計一覧 limit 1", con);

using (MySqlDataReader reader = cmd.ExecuteReader())

{

DataTable dt = new DataTable();

dt.Load(reader);

// カラム名を指定してアクセス

MessageBox.Show(dt.Rows[0].Field<string>("案件名"));

// 全てのカラムを取得

MessageBox.Show(string.Join(",", dt.Rows[0].ItemArray));

}

MySQLのConnectorはNuGetでもインストーラーでもいける。インストーラなら参照設定から登録。

VBAからの接続

MySQLのODBCドライバをインストールする。32/64bitはOSではなくOfficeに合わせる ‘(ファイル＞アカウント＞Excelバージョン情報)

https://dev.mysql.com/downloads/connector/odbc/
mysql-connector-odbc-xxx-winx64(32).msi

Sub test()

'Microsoft ActiveX Data Objects x.x Library参照設定

Set cn = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")

cn.Open "Driver={MySQL ODBC 8.0 Unicode Driver}; Server=xxx.xxx.xxx.xxx; Database=xxx; Port=3306; Uid=xxx; Pwd=xxx; " & _
"Sslmode=Required; sslcert=" & ThisWorkbook.Path & "\client-cert.pem; sslkey=" & ThisWorkbook.Path & "\client-key.pem; "

rs.Open "select * from 設計一覧 where 表示状態 = '表示'", cn

Set w = ActiveSheet

'見出

i = 0
For c = 1 To rs.Fields.Count - 1
  w.Cells(1, c).Value = rs.Fields(i).Name
  i = i + 1
Next c

'本体

r = 2
Do Until rs.EOF

  i = 0
  For c = 1 To rs.Fields.Count - 1
    w.Cells(r, c).Value = rs.Fields(i)
    i = i + 1
  Next c
  
  rs.MoveNext
  r = r + 1
Loop

If rs.State = 1 Then rs.Close
Set rs = Nothing
 
If cn.State = 1 Then cn.Close
Set cn = Nothing

End Sub

Sub test()

'Microsoft ActiveX Data Objects x.x Library参照設定

Set cn = CreateObject("ADODB.Connection")

Set rs = CreateObject("ADODB.Recordset")

cn.Open "Driver={MySQL ODBC 8.0 Unicode Driver}; Server=xxx.xxx.xxx.xxx; Database=xxx; Port=3306; Uid=xxx; Pwd=xxx; " & _

"Sslmode=Required; sslcert=" & ThisWorkbook.Path & "\client-cert.pem; sslkey=" & ThisWorkbook.Path & "\client-key.pem; "

rs.Open "select * from 設計一覧 where 表示状態 = '表示'", cn

Set w = ActiveSheet

'見出

i = 0

For c = 1 To rs.Fields.Count - 1

w.Cells(1, c).Value = rs.Fields(i).Name

i = i + 1

Next c

'本体

r = 2

Do Until rs.EOF

i = 0

For c = 1 To rs.Fields.Count - 1

w.Cells(r, c).Value = rs.Fields(i)

i = i + 1

Next c

rs.MoveNext

r = r + 1

Loop

If rs.State = 1 Then rs.Close

Set rs = Nothing

If cn.State = 1 Then cn.Close

Set cn = Nothing

End Sub

MySQLにインポート

・データベース作成

コンソール＞SQL＞インスタンス選択＞データベース＞データベースの作成をクリック。

文字セット：utf8
照合：デフォルトの照合

・ファイルアップロード(Storage)

コンソール＞Storage＞ブラウザ＞バケットを作成をクリック。

バケットに一意の名前をつける。

ロケーションタイプ：Region
ロケーション：asia-northeast1(東京)
ストレージクラス：Standard
アクセス制御：均一

コンソール＞Storage＞ブラウザ＞バケットを選択＞ファイルをアップロードをクリック。

・インポート

コンソール＞SQL＞インスタンス選択＞概要＞インポートをクリック。

ソースにファイルを指定：アップロードしたファイル選択
ファイル形式：SQL
インポート先：作成したデータベース名

VPSのMySQLへはSQLiteで作成したファイルを直接インポートできたが、GCPではできなかったため、とりあえず今回はSQLite＞MySQL(VPS)＞MySQL(GCP)という流れになった。