# Cloud SQL > [name=翁維甫] > [time=Thur, Feb 9, 2020 4:00 PM] --- # Agenda * SQL Server 建立 * Proxy 設定 * Connecting from Docker image * Connecting from K8S * 備份還原 --- ## SQL Server 建立 --- ### 建立執行個體  --- ### 選擇資料庫種類  --- ### 幫資料庫取名+設定密碼  --- ### 選擇區域及分區  --- ### 設定資料庫版本  --- ### 設定連線(IP及Proxy)  --- ### 選擇機器類型及儲存空間  --- ### 備份、復原、可用性  --- ### 設定旗標  --- ### 維護時間  --- ### 標籤  --- ## Proxy 設定 --- ### Cloud SQL 連線示意  --- ### 優勢 * 安全連結 * 簡化連結方式 * 在高可用狀態下進行容錯移轉時，會自動連接新的機器 --- ### A .下載 Gloud SQL Proxy Linux 64-bit ```linux= wget https://dl.google.com/cloudsql/cloud_sql_proxy.linux.amd64 -O cloud_sql_proxy ``` macOS 64-bit ```linux= curl -o cloud_sql_proxy https://dl.google.com/cloudsql/cloud_sql_proxy.darwin.amd64 ``` --- ### B. 給 Gloud SQL Proxy 權限 ```linux chmod +x cloud_sql_proxy ``` --- ### C. 建立服務帳戶 #### 前往 GCP 的IAM與管理的服務帳戶，建立服務帳戶  --- #### 設定服務帳戶設定資料，輸入服務帳戶名稱  --- #### 設定服務帳戶角色為 Cloud SQL 管理員  --- #### 建立金鑰  --- #### 選擇服務帳戶金鑰類型為 JSON 檔案，點選建立按鈕後即可下載金鑰檔案  --- #### 下載金鑰並丟進環境機器  --- ### D. 建立 Cloud SQL Proxy Linux 64-bit ```linux= ./cloud_sql_proxy -instances=<INSTANCE_CONNECTION_NAME>=tcp:3306 \ -credential_file=<PATH_TO_KEY_FILE> & ``` macOS 64-bit ```linux= ./cloud_sql_proxy -dir=/<cloud_sql_proxy路徑> -instances=<INSTANCE_CONNECTION_NAME>=tcp:3306 \ -credential_file=<PATH_TO_KEY_FILE> & ``` --- #### <INSTANCE_CONNECTION_NAME> 連線名稱  --- #### <PATH_TO_KEY_FILE> 金鑰所在資料夾路徑 --- #### 驗證一下3306是否LISTEN Linux 64-bit ```linux= netstat -tunlp ```  macOS 64-bit ```linux= lsof -i:3306 ```  --- ### E. 使用 Cloud SQL Proxy 連線到資料庫 Linux 64-bit ```linux= mysql -u root -h 127.0.0.1 -p ``` #### 輸入密碼即可使用  --- macOS 64-bit https://sequelpro.com/download#auto-start  --- ## Connecting from Docker Image --- ### Start the Proxy Docker Image ```docker= docker run -d \ -v <PATH_TO_KEY_FILE>:/config \ -p 127.0.0.1:3306:3306 \ gcr.io/cloudsql-docker/gce-proxy:1.16 /cloud_sql_proxy \ -instances=<INSTANCE_CONNECTION_NAME>=tcp:0.0.0.0:3306 -credential_file=/config ``` --- ## Connecting from K8S --- ### 把下載的金鑰檔案匯入至 Kubernetes ```docker= kubectl create secret generic <YOUR-SA-SECRET> \ --from-file=service_account.json=~/key.json ``` --- ### 也可使用 yaml ```yaml= apiVersion: v1 kind: Secret metadata: name: cloudsql-instance-credentials type: Opaque stringData: credentials.json: |- { "type": "service_account", "project_id": "xsg-wz3", "private_key_id": "88bc39acebbf80ba90ac93d96ab05b0d8861306a", "private_key": "-----BEGIN PRIVATE KEY-----\nMIIEvgIBADANBgkqhkiG9w0BAQEFAASCBKgwggSkAgEAAoIBAQC2OGz7P/PV6kUE\nOuwvn2mCPYCFb4ajYjRY5SjyksNaYHhjbb1mlOSPDlnPuYEf/V7Saxk+btWU1OTw\nMWfD/K2HcfakJiRB0EC45bh54oUDMVMXwn83B7lrOEkwdl1gYCVtxioc7dWeYLzv\nn50A0eErwdFgxC174BWjxcJKD6Rh6GyCa3mp6QjNZdyyqpd/VtjKXM0we4+XhbVe\nSDBd8x6lKr0cYmV3jUEnDwV+gKSKEJ3VnkcVI98jOn6HjmB3rI0HjXzpemnRMtwo\niZimYwdbemEvj8uQTRjbMB65nSMIpnFbRevPmKwp2fMT86zjPo72R4IifuBe0LmJ\nN33qejGPAgMBAAECggEAInvyL+gt6n0vu3m1KdwGuRWPhXESL0hN9MbFZYadvrRE\nBDkH32ptCJ+kInoONDIcaEDJYbXS6JwTiVsoMoBWjKvmC7GGKghRv0wGCdby6iRC\n/9uYfJ8TGXXo7+luiWcYnjoizR+7TgIrr0NrEHUnokLHL4NgwjEplzk9KAvmxXpH\nY0gYG5DC/9obiHwcNmrDXvnoCqHfoacGUtOLM1dDh6LLAh+sDEJSzKw7b4mnzn4+\n0lI756lmMOjX3sP2s9ntZsgTk1JLl5hR0pXTg3x7uIZwg8fxcIQw4qc6BQqaXBV7\n15iyrgBC1ok2zKeajPZfJY5Zl/30FyQvtdYMMaXw/QKBgQDnGWiKB4y032fAr59b\nBMAejtyXbfYGzop+EGBbzw/joOJS6dkPd7gkKiv3kHHeKve3ZkwMPqYR8RAz56c2\nLmNIEHTTgwV9PRardWcLrd59rASK7vEoXqQh4k/duQ/CdY7my+YtCHYK5qMQnZKZ\neQEs/ZqigC0kC09ds8q/qF3wvQKBgQDJ2sEKvi1WV6GMFzJmAgu8QBPQPH20uArK\nSBkO/CF/LOXPTx7bFWesZs45tDL42zXk7Trr5gR6DtZhS+hqJJ1i9m0LijryAmg5\nGkO/6qhIpTq4ZAJxRkm6rDaHnAcujBkPVOWwdZAyGCVX2bnxAU1zEEnlmkIL+lSa\nDjaR/VTuOwKBgArhyWhmfputwWbzXh+dE6bct0pyJRmg9CiHgOIR1/+x/bZFyLDP\nxF3/ll5r8NCzWULLkF0rPgquPUMUb20/tfEYxUeIbeO90ql0N8v2/fkQ9o3MPpLW\nEgh+baBVLIbNnlMD2uzX7ycIQTlxEgwkBIgn0PZB7t45oh0ZcDyiIvpJAoGBAKJ/\nh351gRwMKdIUbo8uQHJJB2pQ+EW/D3iut/WXRgQSieLtaAKo5bAlhwlvJsWt+2IT\nFKcJojr8rNrg9/ZTCZy8kyaCHlfrVin6GeZrKxRoZ7A/WrQECKDNuIxNMH7pa9cq\n2knHKtEZHR7u+UCqcsYZq9O57kekO+1VWdsHHRM3AoGBAJUcDFptSwZrv+o2neDp\nfOTGlicIVNdwRimgMi/AR61Z0HoLCybm49wT67ktJ8xfZtuZh0C4aaydQxBcgIDk\nLaoRl/xvuht0UKHWHSDnMZjq3VzANgkhoRSJYu/gPlbHNttU07ughKrrGJIApC91\npxvoq9RuMjmwymBzRnbp3Z8u\n-----END PRIVATE KEY-----\n", "client_email": "cloudsql@xsg-wz3.iam.gserviceaccount.com", "client_id": "112237234783353638185", "auth_uri": "https://accounts.google.com/o/oauth2/auth", "token_uri": "https://oauth2.googleapis.com/token", "auth_provider_x509_cert_url": "https://www.googleapis.com/oauth2/v1/certs", "client_x509_cert_url": "https://www.googleapis.com/robot/v1/metadata/x509/cloudsql%40xsg-wz3.iam.gserviceaccount.com" } ``` 建立物件 ```yaml= kubectl apply -f <FILENAME> ``` 檢查是否正常 ```docker= kubectl get secret ``` --- ### 建立 Cloud SQL 的 Deployment ```yaml= apiVersion: extensions/v1beta1 kind: Deployment metadata: name: cloudsql-proxy-pod spec: replicas: 1 template: metadata: labels: component: cloudsql-proxy spec: containers: - name: cloudsql-proxy image: gcr.io/cloudsql-docker/gce-proxy:1.16 command: - /cloud_sql_proxy # <INSTANCE_CONNECTION_NAME>：改成自己的 - -instances=<INSTANCE_CONNECTION_NAME>=tcp:0.0.0.0:3306 - -credential_file=/secrets/cloudsql/credentials.json volumeMounts: - name: cloudsql-instance-credentials mountPath: /secrets/cloudsql readOnly: true volumes: - name: cloudsql-instance-credentials secret: secretName: cloudsql-instance-credentials ``` --- ### 建立 Cloud SQL 的 Service ```yaml= apiVersion: v1 kind: Service metadata: name: cloudsql-proxy-service spec: ports: - protocol: TCP port: 3306 targetPort: 3306 selector: component: cloudsql-proxy ``` --- 建立以上物件 ```docker= kubectl apply -f <FILENAME> ``` 檢查 Pod、Service ```docker= kubectl get all ``` --- 連線到資料庫 ```linux= mysql -u root -p -h <cloudsql-proxy-service> ``` --- ## 備份還原 --- ### 建立本機副本  --- ### 複製執行個體目前的狀態  --- ### 從過去時間點複製資料  --- #### 二進位紀錄檔名稱、復原位置 取得 BINARY_LOG_FILE ```sql= SHOW BINARY LOGS; ``` 查看內容，是否有重要的 position ```sql= SHOW BINLOG EVENTS IN 'BINARY_LOG_FILE' LIMIT 100; ```