{"id":3768,"date":"2020-10-01T13:14:18","date_gmt":"2020-10-01T04:14:18","guid":{"rendered":"https:\/\/okamurax.com\/?p=3768"},"modified":"2021-12-31T04:47:29","modified_gmt":"2021-12-30T19:47:29","slug":"gcp-cloud-sql-%e4%bd%bf%e3%81%84%e6%96%b9","status":"publish","type":"post","link":"https:\/\/appbay.org\/?p=3768","title":{"rendered":"GCP Cloud SQL(MySQL) \u4f7f\u3044\u65b9"},"content":{"rendered":"

\u5148\u65e5VPS(Indigo)\u306eSQLite\u2192MySQL\u306e\u5b9f\u9a13\u3092\u884c\u3063\u3066\u307f\u305f\u304c\u3001GCP\u3067\u3082\u3084\u3063\u3066\u307f\u308b\u3053\u3068\u306b\u3057\u305f\u3002<\/p>\n

\u521d\u671f\u8a2d\u5b9a<\/h2>\n

Cloud SQL \u7121\u6599\u30c8\u30e9\u30a4\u30a2\u30eb\u3092\u5b9f\u884c\u3059\u308b\u3002
\n(\u65e2\u306b\u30af\u30ec\u30b8\u30c3\u30c8\u7b49\u306e\u60c5\u5831\u306f\u767b\u9332\u6e08\u307f\u306a\u306e\u3067\u305d\u306e\u307e\u307e\u5b8c\u4e86)<\/p>\n

\u30b3\u30f3\u30bd\u30fc\u30eb\u306b\u30ed\u30b0\u30a4\u30f3\uff1e\u5de6\u5074\u30e1\u30cb\u30e5\u30fc\u306eSQL\u3092\u9078\u629e\u3002
\n\u30d7\u30ed\u30b8\u30a7\u30af\u30c8(\u3059\u3067\u306bMyFirstProject\u304c\u5b58\u5728\u3057\u3066\u3044\u308b)\u3092\u30af\u30ea\u30c3\u30af\u3002
\n\u30a4\u30f3\u30b9\u30bf\u30f3\u30b9\u3092\u4f5c\u6210\u3092\u30af\u30ea\u30c3\u30af\u3002
\nMySQL\u3092\u30af\u30ea\u30c3\u30af\u3002<\/p>\n

\u30a4\u30f3\u30b9\u30bf\u30f3\u30b9ID\u3001root\u30d1\u30b9\u30ef\u30fc\u30c9\u3092\u5165\u529b\u3059\u308b\u3002
\n\u7121\u6599\u30c8\u30e9\u30a4\u30a2\u30eb\u3060\u304c\u3001\u5b9f\u969b\u904b\u7528\u3092\u59cb\u3081\u308c\u3070\u5b89\u304f\u5229\u7528\u3057\u305f\u3044\u306e\u3067\u3001
\nasia-northeast1(\u6771\u4eac)\/db-f1-micro
\nSSD\/10GB
\n\u81ea\u52d5\u30d0\u30c3\u30af\u30a2\u30c3\u30d7\u3057\u306a\u3044
\n\u3092\u9078\u629e\u3059\u308b\u3002\u30a4\u30f3\u30b9\u30bf\u30f3\u30b9\u751f\u6210\u306b\u306f\u5c11\u3057\u6642\u9593\u304c\u304b\u304b\u308b\u3002<\/p>\n

\u30fb\u30a4\u30f3\u30b9\u30bf\u30f3\u30b9\u4f5c\u6210\u5f8c<\/p>\n

\u30b3\u30f3\u30bd\u30fc\u30eb\u306b\u30ed\u30b0\u30a4\u30f3\uff1e\u5de6\u5074\u30e1\u30cb\u30e5\u30fc\u306eSQL\u3092\u9078\u629e\u3002
\n\u4f5c\u6210\u3057\u305f\u30a4\u30f3\u30b9\u30bf\u30f3\u30b9\u3092\u9078\u629e\u3002<\/p>\n

\u5de6\u5074\u30e1\u30cb\u30e5\u30fc\u306e\u63a5\u7d9a\u3092\u9078\u629e\u3002
\nSSL\u63a5\u7d9a\u306e\u307f\u8a31\u53ef\u3092\u30af\u30ea\u30c3\u30af\u3002
\n\u30af\u30e9\u30a4\u30a2\u30f3\u30c8\u8a3c\u660e\u66f8\u3092\u4f5c\u6210\u3092\u30af\u30ea\u30c3\u30af\u3057\u3001\u4e00\u610f\u306eID\u3092\u8ffd\u52a0\u3057\u4f5c\u6210\u3002<\/p>\n

\u30fb\u30b5\u30fc\u30d0\u30fc\u8a3c\u660e\u66f8\uff1aserver-ca.pem
\n\u30fb\u30af\u30e9\u30a4\u30a2\u30f3\u30c8\u306e\u516c\u958b\u9375\u8a3c\u660e\u66f8\uff1aclient-cert.pem
\n\u30fb\u30af\u30e9\u30a4\u30a2\u30f3\u30c8\u79d8\u5bc6\u9375\uff1aclient-key.pem
\n\u3068\u3044\u3046\uff13\u3064\u306e\u30d5\u30a1\u30a4\u30eb\u3092\u30c0\u30a6\u30f3\u30ed\u30fc\u30c9\u3059\u308b\u3002<\/p>\n

\u540c\u3058\u304f\u5de6\u5074\u30e1\u30cb\u30e5\u30fc\u306e\u63a5\u7d9a\u304b\u3089\u3001\u30cd\u30c3\u30c8\u30ef\u30fc\u30af\u3092\u8ffd\u52a0\u3092\u30af\u30ea\u30c3\u30af\u3002
\n\u30cd\u30c3\u30c8\u30ef\u30fc\u30af\u306e\u9805\u76ee\uff1a0.0.0.0\/0
\n\u3092\u8ffd\u52a0\u3057\u5b8c\u4e86\uff1e\u4fdd\u5b58\u3092\u5b9f\u884c\u3002<\/p>\n

\u5de6\u5074\u30e1\u30cb\u30e5\u30fc\u306e\u6982\u8981\u3092\u9078\u629e\u3001\u30d1\u30d6\u30ea\u30c3\u30afIP\u30a2\u30c9\u30ec\u30b9\u3092\u78ba\u8a8d\u3002<\/p>\n

MySQL Wokbench\u304b\u3089\u63a5\u7d9a<\/h2>\n

Standard TCP\/IP\u3092\u9078\u629e\u3002<\/p>\n

\u30fbParameters\u30bf\u30d6
\nHostname\uff1a\u30d1\u30d6\u30ea\u30c3\u30afIP\u30a2\u30c9\u30ec\u30b9
\nPort\uff1a3306
\nUsername\uff1aDB\u30e6\u30fc\u30b6\u30fc\u540d(root\u3001app_user\u7b49)
\n\u30d1\u30b9\u30ef\u30fc\u30c9\uff1aDB\u30d1\u30b9\u30ef\u30fc\u30c9<\/p>\n

\u30fbSSL\u30bf\u30d6
\nUse SSL\uff1aRequire and Verify CA
\nSSL Key File, SSL CERT File, SSL CA File
\n\u305d\u308c\u305e\u308c\u3001\u30c0\u30a6\u30f3\u30ed\u30fc\u30c9\u3057\u305f\u30d5\u30a1\u30a4\u30eb\u3092\u9078\u629e\u3002
\n\u203b\u767b\u9332\u5f8c\u30d5\u30a1\u30a4\u30eb\u306e\u30d1\u30b9\u5909\u66f4(\u79fb\u52d5)\u3067\u304d\u306a\u3044\u306e\u3067\u6ce8\u610f\u3002<\/p>\n

\u3068\u308a\u3042\u3048\u305a\u3053\u308c\u3067\u3064\u306a\u304c\u308b\u3002<\/p>\n

C#(MySqlConnection)\u304b\u3089\u63a5\u7d9a<\/h2>\n

.Net\u3067\u306fpem\u304c\u4f7f\u3048\u306a\u3044\u305f\u3081\u3001
\n(https:\/\/dev.mysql.com\/doc\/connector-net\/en\/connector-net-tutorials-ssl-pfx.html)
\nopenssl\u306e\u3042\u308b\u74b0\u5883\u4e0a\u3067(\u4eca\u56de\u306fVPS\u3067)\u5b9f\u884c<\/p>\n

openssl pkcs12 -export -inkey client-key.pem -in client-cert.pem -out client.pfx<\/span>
\npfx\u30d5\u30a1\u30a4\u30eb\u3092\u4f5c\u6210\u3059\u308b\u3002(client\u90e8\u306f\u4efb\u610f\u306e\u540d\u524d)<\/p>\n

pfx\u30d1\u30b9\u30ef\u30fc\u30c9\u3092\u805e\u304b\u308c\u308b\u304c\u3001\u7a7a\u767d\u3082\u53ef\u80fd\u3067\u7a7a\u767d\u306e\u5834\u5408\u3001\u63a5\u7d9a\u6587\u5b57\u5217\u306eCertificatePassword\u306f\u4e0d\u8981\u3068\u306a\u308b\u3002<\/p>\n

\u30fb\u30ef\u30f3\u30e9\u30a4\u30ca\u30fc\u3067\u8a2d\u5b9a\u3059\u308b\u5834\u5408
\npass=$(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;<\/span><\/p>\n

\u30fb\u63a5\u7d9a\u6587\u5b57\u5217<\/p>\n

\"server=IP\u30a2\u30c9\u30ec\u30b9;port=3306;database=DB\u540d;user=MySQL\u30e6\u30fc\u30b6\u30fc\u540d;password=MySQL\u30d1\u30b9\u30ef\u30fc\u30c9;SslMode=Required;CertificateFile=test.pfx;CertificatePassword=pfx\u30d1\u30b9\u30ef\u30fc\u30c9\";<\/pre>\n
        public Form1()\r\n        {\r\n            InitializeComponent();\r\n\r\n            string connectionString = \"server=xxx.xxx.xxx.xxx;port=3306;database=xxx;user=xxx;password=xxx;\" +\r\n                \"SslMode=Required;CertificateFile=xxx.pfx;CertificatePassword=xxx\";\r\n\r\n            using (MySqlConnection con = new MySqlConnection(connectionString))\r\n            {\r\n                con.Open();\r\n                MySqlCommand cmd = new MySqlCommand(\"select * from \u8a2d\u8a08\u4e00\u89a7 limit 1\", con);\r\n                using (MySqlDataReader reader = cmd.ExecuteReader())\r\n                {\r\n                    DataTable dt = new DataTable();\r\n                    dt.Load(reader);\r\n                    \r\n                    \/\/ \u30ab\u30e9\u30e0\u540d\u3092\u6307\u5b9a\u3057\u3066\u30a2\u30af\u30bb\u30b9\r\n                    MessageBox.Show(dt.Rows[0].Field<string>(\"\u6848\u4ef6\u540d\"));\r\n                    \r\n                    \/\/ \u5168\u3066\u306e\u30ab\u30e9\u30e0\u3092\u53d6\u5f97\r\n                    MessageBox.Show(string.Join(\",\", dt.Rows[0].ItemArray));\r\n\r\n                }\r\n            }\r\n        }<\/pre>\n
MySQL\u306eConnector\u306fNuGet\u3067\u3082\u30a4\u30f3\u30b9\u30c8\u30fc\u30e9\u30fc\u3067\u3082\u3044\u3051\u308b\u3002\u30a4\u30f3\u30b9\u30c8\u30fc\u30e9\u306a\u3089\u53c2\u7167\u8a2d\u5b9a\u304b\u3089\u767b\u9332\u3002<\/p>\n
VBA\u304b\u3089\u306e\u63a5\u7d9a<\/h2>\n
MySQL\u306eODBC\u30c9\u30e9\u30a4\u30d0\u3092\u30a4\u30f3\u30b9\u30c8\u30fc\u30eb\u3059\u308b\u300232\/64bit\u306fOS\u3067\u306f\u306a\u304fOffice\u306b\u5408\u308f\u305b\u308b ‘(\u30d5\u30a1\u30a4\u30eb\uff1e\u30a2\u30ab\u30a6\u30f3\u30c8\uff1eExcel\u30d0\u30fc\u30b8\u30e7\u30f3\u60c5\u5831)<\/p>\n
https:\/\/dev.mysql.com\/downloads\/connector\/odbc\/
\nmysql-connector-odbc-xxx-winx64(32).msi<\/p>\n
Sub test()\r\n\r\n'Microsoft ActiveX Data Objects x.x Library\u53c2\u7167\u8a2d\u5b9a\r\n\r\nSet cn = CreateObject(\"ADODB.Connection\")\r\nSet rs = CreateObject(\"ADODB.Recordset\")\r\n\r\ncn.Open \"Driver={MySQL ODBC 8.0 Unicode Driver}; Server=xxx.xxx.xxx.xxx; Database=xxx; Port=3306; Uid=xxx; Pwd=xxx; \" & _\r\n\"Sslmode=Required; sslcert=\" & ThisWorkbook.Path & \"\\client-cert.pem; sslkey=\" & ThisWorkbook.Path & \"\\client-key.pem; \"\r\n\r\nrs.Open \"select * from \u8a2d\u8a08\u4e00\u89a7 where \u8868\u793a\u72b6\u614b = '\u8868\u793a'\", cn\r\n\r\nSet w = ActiveSheet\r\n\r\n'\u898b\u51fa\r\n\r\ni = 0\r\nFor c = 1 To rs.Fields.Count - 1\r\n  w.Cells(1, c).Value = rs.Fields(i).Name\r\n  i = i + 1\r\nNext c\r\n\r\n'\u672c\u4f53\r\n\r\nr = 2\r\nDo Until rs.EOF\r\n\r\n  i = 0\r\n  For c = 1 To rs.Fields.Count - 1\r\n    w.Cells(r, c).Value = rs.Fields(i)\r\n    i = i + 1\r\n  Next c\r\n  \r\n  rs.MoveNext\r\n  r = r + 1\r\nLoop\r\n\r\nIf rs.State = 1 Then rs.Close\r\nSet rs = Nothing\r\n \r\nIf cn.State = 1 Then cn.Close\r\nSet cn = Nothing\r\n\r\nEnd Sub\r\n\r\n<\/pre>\n
MySQL\u306b\u30a4\u30f3\u30dd\u30fc\u30c8<\/h2>\n
\u30fb\u30c7\u30fc\u30bf\u30d9\u30fc\u30b9\u4f5c\u6210<\/p>\n
\u30b3\u30f3\u30bd\u30fc\u30eb\uff1eSQL\uff1e\u30a4\u30f3\u30b9\u30bf\u30f3\u30b9\u9078\u629e\uff1e\u30c7\u30fc\u30bf\u30d9\u30fc\u30b9\uff1e\u30c7\u30fc\u30bf\u30d9\u30fc\u30b9\u306e\u4f5c\u6210\u3092\u30af\u30ea\u30c3\u30af\u3002<\/p>\n
\u6587\u5b57\u30bb\u30c3\u30c8\uff1autf8
\n\u7167\u5408\uff1a\u30c7\u30d5\u30a9\u30eb\u30c8\u306e\u7167\u5408<\/p>\n
\u30fb\u30d5\u30a1\u30a4\u30eb\u30a2\u30c3\u30d7\u30ed\u30fc\u30c9(Storage)<\/p>\n
\u30b3\u30f3\u30bd\u30fc\u30eb\uff1eStorage\uff1e\u30d6\u30e9\u30a6\u30b6\uff1e\u30d0\u30b1\u30c3\u30c8\u3092\u4f5c\u6210\u3092\u30af\u30ea\u30c3\u30af\u3002<\/p>\n
\u30d0\u30b1\u30c3\u30c8\u306b\u4e00\u610f\u306e\u540d\u524d\u3092\u3064\u3051\u308b\u3002<\/p>\n
\u30ed\u30b1\u30fc\u30b7\u30e7\u30f3\u30bf\u30a4\u30d7\uff1aRegion
\n\u30ed\u30b1\u30fc\u30b7\u30e7\u30f3\uff1aasia-northeast1(\u6771\u4eac)
\n\u30b9\u30c8\u30ec\u30fc\u30b8\u30af\u30e9\u30b9\uff1aStandard
\n\u30a2\u30af\u30bb\u30b9\u5236\u5fa1\uff1a\u5747\u4e00<\/p>\n
\u30b3\u30f3\u30bd\u30fc\u30eb\uff1eStorage\uff1e\u30d6\u30e9\u30a6\u30b6\uff1e\u30d0\u30b1\u30c3\u30c8\u3092\u9078\u629e\uff1e\u30d5\u30a1\u30a4\u30eb\u3092\u30a2\u30c3\u30d7\u30ed\u30fc\u30c9\u3092\u30af\u30ea\u30c3\u30af\u3002<\/p>\n
\u30fb\u30a4\u30f3\u30dd\u30fc\u30c8<\/p>\n
\u30b3\u30f3\u30bd\u30fc\u30eb\uff1eSQL\uff1e\u30a4\u30f3\u30b9\u30bf\u30f3\u30b9\u9078\u629e\uff1e\u6982\u8981\uff1e\u30a4\u30f3\u30dd\u30fc\u30c8\u3092\u30af\u30ea\u30c3\u30af\u3002<\/p>\n
\u30bd\u30fc\u30b9\u306b\u30d5\u30a1\u30a4\u30eb\u3092\u6307\u5b9a\uff1a\u30a2\u30c3\u30d7\u30ed\u30fc\u30c9\u3057\u305f\u30d5\u30a1\u30a4\u30eb\u9078\u629e
\n\u30d5\u30a1\u30a4\u30eb\u5f62\u5f0f\uff1aSQL
\n\u30a4\u30f3\u30dd\u30fc\u30c8\u5148\uff1a\u4f5c\u6210\u3057\u305f\u30c7\u30fc\u30bf\u30d9\u30fc\u30b9\u540d<\/p>\n
VPS\u306eMySQL\u3078\u306fSQLite\u3067\u4f5c\u6210\u3057\u305f\u30d5\u30a1\u30a4\u30eb\u3092\u76f4\u63a5\u30a4\u30f3\u30dd\u30fc\u30c8\u3067\u304d\u305f\u304c\u3001GCP\u3067\u306f\u3067\u304d\u306a\u304b\u3063\u305f\u305f\u3081\u3001\u3068\u308a\u3042\u3048\u305a\u4eca\u56de\u306fSQLite\uff1eMySQL(VPS)\uff1eMySQL(GCP)\u3068\u3044\u3046\u6d41\u308c\u306b\u306a\u3063\u305f\u3002<\/p>\n","protected":false},"excerpt":{"rendered":"
\u5148\u65e5VPS(Indigo)\u306eSQLite\u2192MySQL\u306e\u5b9f\u9a13\u3092\u884c\u3063\u3066\u307f\u305f\u304c\u3001GCP\u3067\u3082\u3084\u3063\u3066\u307f\u308b\u3053\u3068\u306b\u3057\u305f\u3002 \u521d\u671f\u8a2d\u5b9a Cloud SQL \u7121\u6599\u30c8\u30e9\u30a4\u30a2\u30eb\u3092\u5b9f\u884c\u3059\u308b\u3002 (\u65e2\u306b\u30af\u30ec\u30b8\u30c3\u30c8\u7b49\u306e\u60c5\u5831\u306f\u767b\u9332\u6e08\u307f\u306a\u306e\u3067\u305d\u306e\u307e\u307e\u5b8c\u4e86) … <\/p>\n
“GCP Cloud SQL(MySQL) \u4f7f\u3044\u65b9” \u306e<\/span>\u7d9a\u304d\u3092\u8aad\u3080<\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1],"tags":[],"class_list":["post-3768","post","type-post","status-publish","format-standard","hentry","category-1"],"_links":{"self":[{"href":"https:\/\/appbay.org\/index.php?rest_route=\/wp\/v2\/posts\/3768","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/appbay.org\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/appbay.org\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/appbay.org\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/appbay.org\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=3768"}],"version-history":[{"count":22,"href":"https:\/\/appbay.org\/index.php?rest_route=\/wp\/v2\/posts\/3768\/revisions"}],"predecessor-version":[{"id":4711,"href":"https:\/\/appbay.org\/index.php?rest_route=\/wp\/v2\/posts\/3768\/revisions\/4711"}],"wp:attachment":[{"href":"https:\/\/appbay.org\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=3768"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/appbay.org\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=3768"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/appbay.org\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=3768"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}