Introduction

Google Cloud SQL Proxy provides secure access to Cloud SQL Second Generation instances without having to whitelist IP addresses or configure SSL. Cloud SQL Proxy provides several important benefits:

  • 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.

The proxy does not provide a new connectivity path; it relies on existing IP connectivity. For example, you cannot use the proxy to connect with an instance using Private IP unless the proxy is using a VPC network that has been configured for private services access.

How the Cloud SQL Proxy works

The Cloud SQL Proxy works by having a local client, called the proxy, running in the local environment. Your application communicates with the proxy with the standard database protocol used by your database. The proxy uses a secure tunnel to communicate with its companion process running on the server.

The following diagram shows how the proxy connects to Cloud SQL:

What Problem does Cloud SQL Proxy Solve?

Typically, developers do not setup SSL for Cloud SQL connections. This is very insecure as the database credentials are transmitted in the clear over port 3306. The proxy solves this problem by encrypting connections and provides automatic authorization without needing to hard-code the Cloud SQL Server IP address as only 127.0.0.1 is required for access.

Cloud SQL Proxy also solves the problem of whitelisting IP addresses. If you have autoscaling setup, you will not know what the IP addresses are for new instances. With the proxy, you just specify 127.0.0.1 for the Cloud SQL server in your application.

Prerequisites

  • The Cloud SQL Admin API must be enabled.
  • The Compute Engine service account has been assigned one of the following roles:
    • Project Owner
    • Project Editor
    • Cloud SQL Client
    • Cloud SQL Editor
    • Cloud SQL Admin
  • The Compute Engine instance must have a public IPv4 address. I will cover private IP in a separate article.
  • This article supports Debian 9 and Ubuntu 16/18. Other Linux operating systems have similar steps.

Download the Cloud SQL Proxy

In your home directory, create a working directory:

mkdir proxy
cd proxy

Download the proxy:

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

Make the proxy executable:

chmod +x cloud_sql_proxy

Configure as a Service (TCP)

The simplest method is to configure the proxy to use TCP. The other method shown below uses Unix Sockets.

Create a new file cloud-sql-proxy.service with the following contents. Replace INSTANCE_CONNECTION_NAME with the Cloud SQL instance connection name.

The instance connection name will look similar to this: myprojectid:us-central1:wordpress-sql. The format is PROJECT_ID : REGION : INSTANCE.

You will find the Instance connection name in the “Instance details” page of the Google Cloud Console under “Connect to this instance”.

[Unit]
Description=Connecting MySQL Client from Compute Engine using the Cloud SQL Proxy
Documentation=https://cloud.google.com/sql/docs/mysql/connect-compute-engine
Requires=networking.service
After=networking.service

[Service]
WorkingDirectory=/usr/local/bin
ExecStart=/usr/local/bin/cloud_sql_proxy -instances=INSTANCE_CONNECTION_NAME=tcp:3306
Restart=always
StandardOutput=journal
User=root

[Install]
WantedBy=multi-user.target

Copy this file to /etc/systemd/system/cloud-sql-proxy.service

sudo cp cloud-sql-proxy.service /etc/systemd/system/cloud-sql-proxy.service

Enable the Cloud SQL Proxy to autostart when the Compute Engine starts:

sudo systemctl enable cloud-sql-proxy.service

Reboot your Compute Engine instance and verify that the service is running after restart:

sudo systemctl status cloud-sql-proxy.service

Configure as a Service (Unix Sockets)

Create a new file cloud-sql-proxy.service with the following contents. Replace INSTANCE_CONNECTION_NAME with the Cloud SQL instance connection name.

The instance connection name will look similar to this: myprojectid:us-central1:wordpress-sql. The format is PROJECT_ID : REGION : INSTANCE.

You will find the Instance connection name in the “Instance details” page of the Google Cloud Console under “Connect to this instance”.

[Unit]
Description=Connecting MySQL Client from Compute Engine using the Cloud SQL Proxy
Documentation=https://cloud.google.com/sql/docs/mysql/connect-compute-engine
Requires=networking.service
After=networking.service

[Service]
WorkingDirectory=/usr/local/bin
ExecStart=/usr/local/bin/cloud_sql_proxy -dir=/cloudsql -instances=INSTANCE_CONNECTION_NAME
Restart=always
StandardOutput=journal
User=root

[Install]
WantedBy=multi-user.target

Copy this file to /etc/systemd/system/cloud-sql-proxy.service

sudo cp cloud-sql-proxy.service /etc/systemd/system/cloud-sql-proxy.service

Create the working directory for the proxy:

sudo mkdir /cloudsql

Enable the Cloud SQL Proxy to autostart when the Compute Engine starts:

sudo systemctl enable cloud-sql-proxy.service

Reboot your Compute Engine instance and verify that the service is running after restart:

sudo systemctl status cloud-sql-proxy.service

Test Cloud SQL Proxy

Install the MySQL Client:

sudo apt install mysql-client

TCP Configuration:

Connect to the Cloud SQL Instance. You will need your MySQL database user and password.

mysql -u <USERNAME> -p --host 127.0.0.1
SHOW DATABASES;
QUIT

Unix Sockets Configuration:

mysql -u root -p -S /cloudsql/myprojectid:us-central1:mysqlinstance
SHOW DATABASES;
QUIT

WordPress

WordPress normally stores the MySQL database parameters in wp-config.php. Make a backup copy of this file. Then edit the file to use the proxy. Typical locations for this file are:

  • /var/www/html
  • /opt/bitnami/apps/wordpress/htdocs

Step 1: Change the MySQL hostname

TCP Configuration:

Look for this line in wp-config.php:

define('DB_HOST', '123.123.123.99');

Change to:

define('DB_HOST', '127.0.0.1');

Unix Sockets Configuration:

Note: WordPress and Unix Sockets does not work. The following should work but does not.

FIXED: The problem was using 127.0.0.1 instead of localhost. PHP/MySQL Client uses 127.0.0.1 for TCP and localhost for Unix Sockets.

Look for this line in wp-config.php:

define('DB_HOST', '123.123.123.99');

Change to:

define('DB_HOST', 'localhost:/cloudsql/myprojectid:us-central1:mysqlinstance');

Reboot the server and verify that WordPress is functioning correctly.

Step 2: Remove the server’s IP address from the Cloud SQL Whitelist

Your goal should be to only use Cloud SQL Proxy for connections to Cloud SQL. Make a note of your WordPress server’s IP address and remove that IP address from Cloud SQL.

Log in to the Google Cloud Console. Go to Cloud SQL. Select your instance. Go to the “CONNECTIONS” tab. Under Authorized networks take a screenshot for backup. Remove your server’s IP address under Authorized networks. Make a note of any other addresses listed. Update those systems to use Cloud SQL Proxy. Then come back and remove them as well.

Reboot the server and verify that WordPress is functioning correctly.

Step 3: Create a new MySQL user

If you have been using Cloud SQL without SSL, now is the time to create a new database user. Delete the old user once you have switched to Cloud SQL Proxy.

Log in to the Google Cloud Console. Go to Cloud SQL. Select your instance. Go to the USERS tab. Click the “Create user account” button. Enter the user name and password.

Edit wp-config.php and change these lines to use the new MySQL user and password.

define('DB_USER', 'username');
define('DB_PASSWORD', 'password');

Reboot the server and verify that WordPress is functioning correctly.

Supporting Multiple Cloud SQL Instances

The proxy supports multiple Cloud SQL instances. For TCP, you must specify different port numbers for each instance. For Unix Sockets, you can specify different socket names to identity different Cloud SQL instances.

TCP:

ExecStart=/usr/local/bin/cloud_sql_proxy -instances=myprojectid:us-central1:mysqlinstance1=tcp:3306,myprojectid:us-central1:mysqlinstance2=tcp:3307
mysql -u <USERNAME> -p --host 127.0.0.1 --port=3306 # Connect to the first instance
mysql -u <USERNAME> -p --host 127.0.0.1 --port=3307 # Connect to the second instance

Unix Sockets:

ExecStart=/usr/local/bin/cloud_sql_proxy -verbose=true -dir=/cloudsql -instances=myprojectid:us-central1:mysqlinstance1,myprojectid:us-central1:mysqlinstance2
mysql -u root -p -S /cloudsql/myprojectid:us-central1:mysqlinstance1 # Connect to the first instance
mysql -u root -p -S /cloudsql/myprojectid:us-central1:mysqlinstance2 # Connect to the second instance

Debugging Problems

Enabling verbose output

TCP:

Add the command-line option -verbose=true to the file /etc/systemd/system/cloud-sql-proxy.service.

ExecStart=/usr/local/bin/cloud_sql_proxy -verbose=true -instances=INSTANCE_CONNECTION_NAME=tcp:3306

Unix Sockets:

ExecStart=/usr/local/bin/cloud_sql_proxy -verbose=true -dir=/cloudsql -instances=INSTANCE_CONNECTION_NAME

Once everything is working, change the flag to false.

Cloud SQL Proxy Status

The first step is to check the Cloud SQL Proxy status:

sudo systemctl status cloud-sql-proxy.service

The normal running status will be like this:

● cloud-sql-proxy.service - Connecting MySQL Client from Compute Engine using the Cloud SQL Proxy
   Loaded: loaded (/etc/systemd/system/cloud-sql-proxy.service; enabled; vendor preset: enabled)
   Active: active (running) since Sun 2019-07-28 21:12:53 UTC; 1h 39min ago
     Docs: https://cloud.google.com/sql/docs/mysql/connect-compute-engine
 Main PID: 633 (cloud_sql_proxy)
    Tasks: 8 (limit: 4915)
   CGroup: /system.slice/cloud-sql-proxy.service
           └─633 /usr/local/bin/cloud_sql_proxy -dir=/var/run/cloud-sql-proxy -instances=myprojectid:us-central1:mysqlinstance=tcp:3306

Jul 28 21:12:53 instance-1 systemd[1]: Started Connecting MySQL Client from Compute Engine using the Cloud SQL Proxy.
Jul 28 21:12:55 instance-1 cloud_sql_proxy[633]: 2019/07/28 21:12:55 Rlimits for file descriptors set to {&{8500 8500}}
Jul 28 21:13:01 instance-1 cloud_sql_proxy[633]: 2019/07/28 21:13:01 Listening on 127.0.0.1:3306 for myprojectid:us-central1:mysqlinstance
Jul 28 21:13:01 instance-1 cloud_sql_proxy[633]: 2019/07/28 21:13:01 Ready for new connections

Look for two items. On line 3: “running” and on line 9 “Ready for new connections. If there are errors, review to determine a probable cause.

Wrong Cloud SQL Instance Connection Name

If you have the wrong instance connection name configured in cloud-sql-proxy.service, you will see an error like this:

mysql -u <USERNAME> -p --host 127.0.0.1

ERROR 2013 (HY000): Lost connection to MySQL server at 'reading initial communication packet', system error: 0 "Internal error/check (Not system error)"

Check the Cloud SQL Proxy status:

sudo systemctl status cloud-sql-proxy.service

This error message shows that the wrong Project ID is configured:

Jul 28 23:10:28 instance-1 cloud_sql_proxy[634]: 2019/07/28 23:10:28 Ready for new connections
Jul 28 23:10:38 instance-1 cloud_sql_proxy[634]: 2019/07/28 23:10:38 New connection for "xmyprojectid:us-central1:mysqlinstance"
Jul 28 23:10:38 instance-1 cloud_sql_proxy[634]: 2019/07/28 23:10:38 couldn't connect to "xmyprojectid:us-central1:mysqlinstance": googleapi: Error 400: Project specified in the request is invalid., error
InvalidProject

This error messages shows that the wrong instance name is configured or you do not have permissions to access the Cloud SQL instance:

Jul 28 23:13:54 instance-1 cloud_sql_proxy[644]: 2019/07/28 23:13:54 Ready for new connections
Jul 28 23:14:11 instance-1 cloud_sql_proxy[644]: 2019/07/28 23:14:11 New connection for "myprojectid:us-central1:xmysqlinstance"
Jul 28 23:14:11 instance-1 cloud_sql_proxy[644]: 2019/07/28 23:14:11 couldn't connect to "myprojectid:us-central1:xmysqlinstance": ensure that the account has access to "wordpress-nfs:us-central1:xjhanley " (and make sure there's no typo in that name). Error during createEphemeral for myprojectid:us-central1:xmysqlinstance: googleapi: Error 403: The client is not authorized to make this request., notAuthor
ized

The Compute Engine Service Account does not have Cloud SQL Permissions

When creating a Compute Engine instance, you need to enable one of the following under the instance “Identity and API access” screen in Compute Engine:

  • Set access for each API: Cloud SQL: Enabled
  • Allow full access to all Cloud APIs

I generated the following error by setting Cloud SQL: None

Jul 28 23:13:54 instance-1 cloud_sql_proxy[644]: 2019/07/28 23:13:54 Ready for new connections<
Jul 28 23:14:11 instance-1 cloud_sql_proxy[644]: 2019/07/28 23:14:11 New connection for "myprojectid:us-central1:mysqlinstance"
Jul 28 23:14:11 instance-1 cloud_sql_proxy[644]: 2019/07/28 23:14:11 couldn't connect to "myprojectid:us-central1:mysqlinstance": ensure that the account has access to "wordpress-nfs:us-central1:jhanley " (and make sure there's no typo in that name). Error during createEphemeral for myprojectid:us-central1:mysqlinstance: googleapi: Error 403: The client is not authorized to make this request., notAuthorized

Credits

I write free articles about technology. Recently, I learned about Pexels.com which provides free images. The image in this article is courtesy of Dids at Pexels.