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:
|
1 2 |
mkdir proxy cd proxy |
Download the proxy:
|
1 |
wget https://dl.google.com/cloudsql/cloud_sql_proxy.linux.amd64 -O cloud_sql_proxy |
Make the proxy executable:
|
1 |
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”.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
[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
|
1 |
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:
|
1 |
sudo systemctl enable cloud-sql-proxy.service |
Reboot your Compute Engine instance and verify that the service is running after restart:
|
1 |
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”.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
[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
|
1 |
sudo cp cloud-sql-proxy.service /etc/systemd/system/cloud-sql-proxy.service |
Create the working directory for the proxy:
|
1 |
sudo mkdir /cloudsql |
Enable the Cloud SQL Proxy to autostart when the Compute Engine starts:
|
1 |
sudo systemctl enable cloud-sql-proxy.service |
Reboot your Compute Engine instance and verify that the service is running after restart:
|
1 |
sudo systemctl status cloud-sql-proxy.service |
Test Cloud SQL Proxy
Install the MySQL Client:
|
1 |
sudo apt install mysql-client |
TCP Configuration:
Connect to the Cloud SQL Instance. You will need your MySQL database user and password.
|
1 2 3 |
mysql -u <USERNAME> -p --host 127.0.0.1 SHOW DATABASES; QUIT |
Unix Sockets Configuration:
|
1 2 3 |
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:
|
1 |
define('DB_HOST', '123.123.123.99'); |
Change to:
|
1 |
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:
|
1 |
define('DB_HOST', '123.123.123.99'); |
Change to:
|
1 |
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.
|
1 2 |
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:
|
1 |
ExecStart=/usr/local/bin/cloud_sql_proxy -instances=myprojectid:us-central1:mysqlinstance1=tcp:3306,myprojectid:us-central1:mysqlinstance2=tcp:3307 |
|
1 2 |
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:
|
1 |
ExecStart=/usr/local/bin/cloud_sql_proxy -verbose=true -dir=/cloudsql -instances=myprojectid:us-central1:mysqlinstance1,myprojectid:us-central1:mysqlinstance2 |
|
1 2 |
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.
|
1 |
ExecStart=/usr/local/bin/cloud_sql_proxy -verbose=true -instances=INSTANCE_CONNECTION_NAME=tcp:3306 |
Unix Sockets:
|
1 |
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:
|
1 |
sudo systemctl status cloud-sql-proxy.service |
The normal running status will be like this:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
● 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:
|
1 2 3 |
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:
|
1 |
sudo systemctl status cloud-sql-proxy.service |
This error message shows that the wrong Project ID is configured:
|
1 2 3 4 |
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:
|
1 2 3 4 |
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
|
1 2 3 |
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.
I design software for enterprise-class systems and data centers. My background is 30+ years in storage (SCSI, FC, iSCSI, disk arrays, imaging) virtualization. 20+ years in identity, security, and forensics.
For the past 14+ years, I have been working in the cloud (AWS, Azure, Google, Alibaba, IBM, Oracle) designing hybrid and multi-cloud software solutions. I am an MVP/GDE with several.
December 9, 2020 at 8:43 PM
Great article, thank you. Only one that shows systemd with Unix sockets.