Using Cloudsqlproxy to initiate connection to mysql from the shell

The cloud sql proxy is a really useful way of managing connectivity to cloudSQL instances. The proxy provides secure access to your Cloud SQL Second Generation instances without having to whitelist IP addresses or configure SSL.

Accessing Cloud SQL instances using the Cloud SQL Proxy offers these advantages:

  • Secure connections: The proxy automatically encrypts traffic to and from the database using TLS 1.2 with a 128-bit AES cipher; SSL certificates are used to verify client and server identities.
  • Easier connection management: The proxy handles authentication with Cloud SQL, removing the need to provide static IP addresses.

There are a couple of pre-requisites before this will work:

  • Only Second generation instances can benifit from this feature.
  • Cloud SQL Admin API must be enabled
  • The instance must have  either a public IPv$ address or be configured to use a private IP.
  • The proxy needs to be given GCP authentication credentials, either an individual or a service account.

Full details of exactly what to do are here – https://cloud.google.com/sql/docs/mysql/sql-proxy 

Some notes on what I did on macOS sierra:

  1. Created a Service Account, gave it the Cloud SQL Client role and then generated a new private key (json key)
curl -o cloud_sql_proxy https://dl.google.com/cloudsql/cloud_sql_proxy.darwin.amd64

Unpack the dmg file, then give execute permissions on the proxy file.

chmod +x cloud_sql_proxy

Then start the proxy:

./cloud_sql_proxy -instances=project:region:instance=tcp:3306 \
-credential_file=/Users/dbamohsin/sslkeys/cloudsqlproxy-env.json

The proxu will now start listening on your local machine for connection attempts against port 3306:

2018/11/09 11:53:10 Listening on 127.0.0.1:3306 for project:region:instance
2018/11/09 11:53:10 Ready for new connections

Then in another terminal window, connect as normal to a mysql instance using the localhost IP:

mysqlsh -udbamohsin -p -h127.0.0.1

This should make a connection to the cloudSQL instance using the proxy and the connection will show in the proxy log:

2018/11/09 11:53:42 New connection for "project:region:instance"
Advertisements