What is Cloud SQL?

Cloud SQL is a fully-managed database service that makes it easy to set up, maintain, and administer your relational PostgreSQL and MySQL databases in the cloud. Cloud SQL offers high performance, vertical scalability, and convenience. Hosted on Google Cloud Platform, Cloud SQL provides a database infrastructure for applications running anywhere.

Connection Options

Currently there are 3 ways of connecting to Cloud SQL:

Requirements

If you are connecting via terminal, you will need the database clients (mysql, psql) to be able to connect to the database. If you are using an application to connect to your Cloud SQL instance, please make sure to have the appropriate dependencies installed and configured.

Other Notes

Once you decide which way you will access your instance, you can go directly to the "Connecting to a Cloud SQL Instance" and see details for connecting to MySQL and PostgreSQL.

Private networking enables connectivity to your Cloud SQL instances from your GCP VPC. VPCs provide private communication between compute resources you create, and now enable private communication to Cloud SQL.

Using private networking with Cloud SQL has several additional advantages over using public IP addresses:

Update Cloud SQL Instance with Private IP

  1. Navigate to the Cloud SQL instance page in the Google Cloud Platform.
  2. Click the instance name to open its Overview page.
  3. Select the Connections tab.
  4. Select the Private IP checkbox.
  5. Select the network where the resources you want to connect from are located.
  6. If no allocated IP range exists for this network:
  1. To let Cloud SQL allocate the range for you and create the private connection, click Allocate and connect. You are done; proceed with choosing instance settings and creating the instance.
  2. Otherwise, allocate an IP range manually and return to this task.
  1. If one or more allocated IP ranges exist for your network, and you haven't yet selected the range you want to use to connect to the Cloud SQL service, select the range and click Connect.

Finding an Instance's Private IP

On your terminal or Google Cloud Shell using the gcloud command, use:

gcloud sql instances list

A list similar to the following will be displayed:

NAME                DATABASE_VERSION  LOCATION       TIER              ADDRESS         STATUS
public-and-private  MYSQL_5_7         us-central1-f  db-n1-standard-1   35.224.148.226  RUNNABLE
psql-codelab        POSTGRES_9_6      us-central1-f  db-custom-4-15360  35.226.53.72    RUNNABLE
only-private        MYSQL_5_7         us-central1-f  db-n1-standard-1   10.176.0.7      RUNNABLE
only-public         MYSQL_5_7         us-central1-f  db-n1-standard-1   35.192.179.200  RUNNABLE

Take note of the ADDRESS field for the instance you wish to connect.

You can also get this information on the GUI in the Connect to this instance section on your instance details accessible on the Google Cloud Console.

The Cloud SQL Proxy is a secure way to connect to your Cloud SQL instance, no matter your location. This makes the Proxy an ideal way to connect to Cloud SQL from application platforms that do not support VPC and from outside of Google Cloud. The proxy will listen on a local port on your machine, and will act as an application endpoint for connections to your Cloud SQL instance. In this section, we will setup a connection to your database with the Cloud SQL Proxy, and verify that your app can connect to it successfully.

Install the Proxy

Download the Cloud SQL Proxy accordingly to your Operating System. In the following example we are using Linux 64-bit and make it executable with the following commands:

wget https://dl.google.com/cloudsql/cloud_sql_proxy.linux.amd64 -O cloud_sql_proxy
chmod +x cloud_sql_proxy

The first command downloads the cloud_sql_proxy binary and the second one gives permission to it be executed.

Create Proxy Service Accounts

In order to connect to your Cloud SQL instance, the Proxy needs to be provided with some kind of credentials. The best way to do this is by creating a service account and granting it permission to connect to your Cloud SQL instance. Service accounts are an easy and secure way to authenticate your applications.

A service account is a special type of Google account that belongs to your project. Instead of using an individual user's credential, a service account can be used to authenticate your application instead. Service accounts should have only required permissions - this limits the damage caused if an account is compromised. You can create multiple private keys per service account, which can be used for authentication when connecting to Google Cloud. You can find more information about service accounts in the documentation here.

Creating Service Account for use on the Proxy

Create a new service account with the following command, replacing [SERVICE_ACCOUNT_NAME] with a name of your choosing (e.g. "my-proxy-user"):

gcloud iam service-accounts create proxy-user --display-name "[SERVICE_ACCOUNT_NAME]"

Retrieve the email of the service account, which will be used in the following steps:

gcloud iam service-accounts list

Next, grant your service account the Cloud SQL Client role. Replace [PROJECT_ID] with your project ID and [SERVICE_ACCOUNT_EMAIL] with the email associated with the service account created in the previous step. This will allow the service account to connect the proxy on your behalf:

gcloud projects add-iam-policy-binding [PROJECT_ID] --member \
serviceAccount:[SERVICE_ACCOUNT_EMAIL] --role roles/cloudsql.client

Finally, create a file called key.json that will be used to authenticate with your service account:

gcloud iam service-accounts keys create key.json --iam-account [SERVICE_ACCOUNT_EMAIL]

Start Cloud SQL Proxy

In order to start the Cloud SQL Proxy, you will need the [INSTANCE_CONNECTION_NAME] of the Cloud SQL instance you want to connect to, and the [PORT] you want the proxy to listen on. The default [PORT] is 3306 for MySQL, and 5432 for PostgreSQL.

The [INSTANCE_CONNECTION_NAME] is in the format "<PROJECT_ID>:<REGION>:<INSTANCE_ID>" and can be found on the "Instance details" page, or with the following command in a terminal:

gcloud sql instances describe [INSTANCE_NAME]| grep connectionName

One you have your [INSTANCE_CONNECTION_NAME], replace it and [PORT] in one of the following commands:

# General
./cloud_sql_proxy -instances=[INSTANCE_CONNECTION_NAME]=tcp:[PORT] -credential_file=key.json &

# MySQL
./cloud_sql_proxy -instances=[INSTANCE_CONNECTION_NAME]=tcp:3306 -credential_file=key.json &

# PostgreSQL
./cloud_sql_proxy -instances=[INSTANCE_CONNECTION_NAME]=tcp:5432 -credential_file=key.json &

The proxy will run in the background until the process is killed. To kill the proxy on a Linux environment run:

killall cloud_sql_proxy

For more information on how to connect to your MySQL or PostgreSQL client, jump to their respective option in the menu on the left.

On your terminal or Google Cloud Shell using the gcloud command, use:

gcloud sql instances list

A list similar to the following will be displayed:

NAME                DATABASE_VERSION  LOCATION       TIER              ADDRESS         STATUS
public-and-private  MYSQL_5_7         us-central1-f  db-n1-standard-1   35.224.148.226  RUNNABLE
psql-codelab        POSTGRES_9_6      us-central1-f  db-custom-4-15360  35.226.53.72    RUNNABLE
only-private        MYSQL_5_7         us-central1-f  db-n1-standard-1   10.176.0.7      RUNNABLE
only-public         MYSQL_5_7         us-central1-f  db-n1-standard-1   35.192.179.200  RUNNABLE

Take note of the ADDRESS field for the instance you wish to connect. For this step it is necessary for the IP address of the machine connecting to the Cloud SQL instance to be whitelisted. You can check this tutorial to learn how to Authorize Networks on your Cloud SQL instance.

You can also get this information on the GUI in the Connect to this instance section on your instance details accessible on the Google Cloud Console.

For more information on how to connect to your MySQL or PostgreSQL client, jump to their respective option in the menu on the left.

Connecting to a MySQL Instance

Using the mysql client installed on your machine/server, you can connect using the following:

mysql -h [IP_ADDRESS] -u [MYSQL_USERNAME] -p

Replace [IP_ADDRESS] and [MYSQL_USERNAME] with the information pertinent to your instance. [IP_ADDRESS] could be in this case:

Example:

mysql -h 35.123.045.089 -u root -p

You will be prompted to enter the password of your [MYSQL_USERNAME].

For more information on how to connect to a MySQL instance using SSL or a different port access this link.

Connecting to a PostgreSQL instance

You will need the psql client installed on your machine/server, the following command will allow you to connect to your database:

psql -h [IP_ADDRESS] -U [POSTGRES_USERNAME] -W [POSTGRES_SCHEMA]

Replace [IP_ADDRESS], [POSTGRES_USERNAME], and [POSTGRES_SCHEMA] with the information pertinent to your instance. [IP_ADDRESS] could be in this case:

Example:

psql -h 10.176.1.3 -U postgres -W store

You will be prompted to enter the password of your [POSTGRES_USERNAME].

For more information on how to connect to a PostgreSQL instance using SSL or a different port access this link.

For more information on how to connect from other operating systems and other services like Google App Engine and Cloud Functions please checkout the Cloud SQL documentation.