GCP CloudSQL Proxy

GCP CloudSQL Proxy

Cloud SQL 인증 프록시 정보
https://cloud.google.com/sql/docs/mysql/sql-proxy?authuser=1 

Cloud SQL 인증 프록시 다운로드 및 설치
https://cloud.google.com/sql/docs/mysql/sql-proxy?authuser=1#install 


승인된 네트워크나 ssl 없이 cloud sdk 인증으로 cloudsql 로 연결되는 프록시 실행
-> 연결하려면 Cloud SQL 클라이언트 역할(roles/cloudsql.client) 필요
-> cloud-sql-proxy 를 사용하더라도 VPC 외부에서 접근하려면 cloudsql 인스턴스에 퍼블릭 아이피가 필요함


# 설치

curl -o cloud-sql-proxy https://storage.googleapis.com/cloud-sql-connectors/cloud-sql-proxy/v2.0.0/cloud-sql-proxy.darwin.arm64

chmod +x cloud-sql-proxy

mv cloud-sql-proxy /usr/local/bin/

mkdir ~/.cloud-sql-proxy

## centos

curl -o cloud-sql-proxy https://storage.googleapis.com/cloud-sql-connectors/cloud-sql-proxy/v2.0.0/cloud-sql-proxy.linux.amd64

# 실행

## tcp 모드로 실행

cloud-sql-proxy premisan-test:asia-northeast3:premisan-test-mysql

## private ip 만 가진 cloudsql 인스턴스로 접속
-> cloudsql 인스턴스와 동일한 vpc 에서만 엑세스 가능

cloud-sql-proxy premisan-test:asia-northeast3:premisan-test-mysql --private-ip

## unix 소켓으로 실행

cloud-sql-proxy premisan-test:asia-northeast3:premisan-test-mysql -u /Users/premisan/.cloud-sql-proxy

## 중요 옵션들

Flags:
  -a, --address string                       (*) Address to bind Cloud SQL instance listeners. (default "127.0.0.1")
  -i, --auto-iam-authn                       (*) Enables Automatic IAM Authentication for all instances
  -p, --port int                             (*) Initial port for listeners. Subsequent listeners increment from this value.
      --private-ip                           (*) Connect to the private ip address for all instances
  -u, --unix-socket string                   (*) Enables Unix sockets for all listeners with the provided directory.

# 접속

## cloud-sql-proxy 를 tcp 모드로 실행한 경우

 

mysql -u root -p -h 127.0.0.1

## cloud-sql-proxy 를 unix 소켓으로 실행한 경우

mysql -uroot -p --socket=/Users/premisan/.cloud-sql-proxy/premisan-test:asia-northeast3:premisan-test-db

## 출력

Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 266
Server version: 5.7.36-google-log (Google)

Copyright (c) 2000, 2021, Oracle and/or its affiliates.

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> status
--------------
mysql  Ver 14.14 Distrib 5.7.34, for osx10.17 (x86_64) using  EditLine wrapper

Connection id:		266
Current database:
Current user:		root@cloudsqlproxy~123.123.123.123
SSL:			Not in use
Current pager:		less
Using outfile:		''
Using delimiter:	;
Server version:		5.7.36-google-log (Google)
Protocol version:	10
Connection:		Localhost via UNIX socket
Server characterset:	utf8
Db     characterset:	utf8
Client characterset:	utf8
Conn.  characterset:	utf8
UNIX socket:		/Users/premisan/.cloud-sql-proxy/premisan-test:asia-northeast3:premisan-test-db
Uptime:			40 min 35 sec

Threads: 6  Questions: 5407  Slow queries: 0  Opens: 202  Flush tables: 1  Open tables: 192  Queries per second avg: 2.220
--------------

mysql> show processlist;
+-----+------+----------------------------+------+---------+------+----------+------------------+
| Id  | User | Host                       | db   | Command | Time | State    | Info             |
+-----+------+----------------------------+------+---------+------+----------+------------------+
|  12 | root | localhost                  | NULL | Sleep   |   16 |          | NULL             |
|  20 | root | localhost                  | NULL | Sleep   |   10 |          | NULL             |
| 252 | root | localhost                  | NULL | Sleep   |    0 |          | NULL             |
| 266 | root | cloudsqlproxy~123.123.123.123 | NULL | Query   |    0 | starting | show processlist |
| 273 | root | localhost                  | NULL | Sleep   |   10 |          | NULL             |
| 286 | root | localhost                  | NULL | Sleep   |   10 |          | NULL             |
+-----+------+----------------------------+------+---------+------+----------+------------------+
6 rows in set (0.01 sec)

# cloudsql proxy sidecar 컨테이너
-> rollout(or deployment, statefulset)에 service account 부여 후 gcp iam 에서 워크로드 아이덴티티 설정
-> cloudsql proxy 를 sidecar 컨테이너로 실행하고, 앱 컨테이너에서는 127.0.0.1 로 db 접속

https://cloud.google.com/sql/docs/mysql/connect-kubernetes-engine?authuser=1#run_the_as_a_sidecar 

apiVersion: argoproj.io/v1alpha1
kind: Rollout
metadata:
  name: sample
spec:
  progressDeadlineSeconds: 300
  replicas: 1
  revisionHistoryLimit: 3
  selector:
    matchLabels:
      app: sample
  strategy:
    canary:
      maxSurge: 1
      maxUnavailable: 0
  template:
    metadata:
      labels:
        app: sample
    spec:
      terminationGracePeriodSeconds: 30
      serviceAccount: sa-sample
      containers:
...
      - name: cloud-sql-proxy
        image: gcr.io/cloudsql-docker/gce-proxy:1.28.0
        command:
          - "/cloud-sql-proxy"
          - "-log_debug_stdout"
          - "premisan-test:asia-northeast3:premisan-test-mysql=tcp:3306"
        securityContext:
          runAsNonRoot: true
        resources:
          requests:
            memory: "2Gi"
            cpu:    "1"
...