Connect to Google Cloud SQL with Cloud SQL Auth proxy and UNIX Sockets
Google Cloud SQL provides multiple ways for a developer to connect to the cloud-hosted, managed database externally for development or testing purposes.
The Cloud SQL Proxy allows simple, secure connectivity to Google Cloud SQL. It is a long-Google Cloud SQL Auth proxy docs
running process that opens local sockets (either TCP or Unix sockets) according to the
parameters passed to it. A local application connects to a Cloud SQL instance by using the
- Multiple Approaches to Connect Externally to Google Cloud SQL
- Installing Google Cloud SQL Auth proxy
- Configuring Connection String for proxy
- What Are UNIX Sockets?
- Gotchas with Cloud SQL Auth proxy and UNIX Sockets
- Challenge with recent Apple macOS versions and Security settings
- Configuring Shell Scripts to Start/Stop Cloud SQL Auth proxy locally
- Connecting Doctrine to Cloud SQL via proxy
Multiple Approaches to Connect Externally to Google Cloud SQL
One approach is to create an Authorized Network by providing your IP address. Google Cloud allows your computer to access your database directly once the IP address is assigned. While the easiest to do, this approach can get cumbersome with multiple people or even home Internet connections that often change their public IP addresses.
A second approach is to utilize the Google Cloud SQL Auth proxy. The proxy eliminates the need to create and manage Authorized Networks. It does require some initial setup and configuration. But once complete, the proxy can be started and stopped with a simple shell script on your development machine.
How the Cloud SQL Auth proxy connects to Cloud SQL (diagram from Google)
One benefit of using Google Cloud SQL Auth proxy is that it manages the proxy. Keep your binaries up-to-date, and you only have to worry about your client applications and local connection.
Installing Google Cloud SQL Auth proxy
You can install the proxy on Linux, macOS, Windows, and via Docker. Each platform's command line is provided on the official documentation. In addition to local installation, make sure you enable the Cloud SQL Client role within IAM and the necessary account(s).
Configuring Connection String for proxy
There are two ways to connect via the proxy - TCP or UNIX Sockets. For either method, replace project_name, cloud_region (i.e. us-east1), and database_name with your specific values.
Proxy via TCP ./cloud_sql_proxy -instances=project_name:cloud_region:database_name=tcp:3306 Proxy via UNIX Sockets ./cloud_sql_proxy -instances=project_name:cloud_region:database_name -dir=/cloudsql
What Are UNIX Sockets?
A UNIX socket is an inter-process communication mechanism that allows bidirectional data exchange between processes running on the same machine. IP sockets (especially TCP/IP sockets) are a mechanism allowing communication between processes over the network.
In some cases, you can use TCP/IP sockets to talk with processes running on the same computer (by using the loopback interface). UNIX domain sockets know that they’re executing on the same system, so they can avoid some checks and operations (like routing); which makes them faster and lighter than IP sockets.ServerFault
Gotchas with Cloud SQL Auth proxy and UNIX Sockets
First, if using a Windows-based operating system, UNIX Sockets cannot be used. Linux and Mac support them.
Second, if using UNIX Sockets, there is a limit on length of the path (i.e. "dir=/cloudsql"). This is the reason that if you use UNIX Sockets, you need to keep path short.
Challenge with recent Apple macOS versions and Security settings
During the initial configuration of Cloud SQL Auth proxy and UNIX Sockets on my Mac, I ran into an issue when creating the root directory cloudsql. Since macOS Catalina's release a few years ago, Apple has made the system volume (the root) read-only.
The solution is to create a /etc/synthetic.conf to define a symbolic link such as /cloudsql on the root level. Follow the steps on this blog to create a root-level directory.
Configuring Shell Scripts to Start/Stop Cloud SQL Auth proxy locally
To make things simple, I'd recommend you create a shell script that can be fired easily. Include the "&" at the end of the line. Each new line can contain other database connection strings, if needed. (Don't forget to change the values of project_name, cloud_region, and database_name)
./cloud_sql_proxy -instances=project_name:cloud_region:database_name -dir=/cloudsql &
Connecting Doctrine to Cloud SQL via proxy
#.env file DATABASE_NAME=database_name DATABASE_HOST=localhost DATABASE_USER=admin DATABASE_PASSWORD=password DATABASE_SOCKET=/cloudsql/project_name:cloud_region:database_server DATABASE_URL="mysql://%env(DATABASE_USER)%:%env(DATABASE_PASSWORD)%@%env(DATABASE_HOST)%/%env(DATABASE_NAME)%?unix_socket=%env(DATABASE_SOCKET)%"
If you use Jetbrain's DataGrip, there is documentation on how to connect via UNIX Sockets.
Using Google Cloud SQL Auth proxy removes a lot of the common challenges associated when connecting from outside of the cloud environment to managed databases.
Setting it up once is all it takes, then for each new project, region, database server, it is the same approach.
RELATED POST TO READ
How to Deploy WordPress with SSL on Google Cloud for Free
In a few hours, anyone, even those not well-versed in cloud computing or shell scripting, can deploy WordPress running SSL for free in Google Cloud.
RELATED POST TO READ
How To Redirect to HTTPS for WordPress with NGINX and SSL Certified by Bitnami and Automattic
How to redirect HTTP (unsecured) traffic to HTTPS when using NGINX.