Using the CloudSQL Proxy to talk to MySQL from your GKE Rails application

In my first article, I described the steps to deploy a Rails application to Google Container Engine (GKE) and how to use a GCE TLS Ingress to do TLS termination.

The demo app in this article uses Kubernetes Secrets and the Google CloudSQL Proxy to talk to a CloudSQL (MySQL Second Generation) instance from a Rails application hosted in GKE.

All the code in this example is in Github

I have taken the “Cats” example from the GCP documentation and changed it to Dogs :) Here, I would like to see all the dogs in my “dogs” table as follows:

Prerequisites:

1. A GCP Project has been created
2. A MySQL (CloudSQL) instance (db) has been created
3. A database called dogs has been created and there is a table dogs which will have a list of dogs and their ages.

Choices, choices: There are many ways of connecting to a CloudSQL database from a Rails application:

Option I: You can whitelist your IP address or allow all IPs (not recommended) via setting the network IP to 0.0.0.0/0 in the database Access Control tab settings. This will help you during your local development where you need to talk to the CloudSQL database. You would also need to change your database.yml to reflect the actual connection params:

database.yml

development:
adapter: mysql2
pool: 5
timeout: 5000
username: your user name
password: your password
database: dogs
socket: /cloudsql/gcedemo-173901:us-central1:db
host: your database host IP adress

Option II: You can use a local instance of a CloudSQL Proxy as described in the GCP Page here . This allows you to work in your local environment but connect to the remote database without compromising database security.

Option III: You can whitelist the IP addresses of your instances themselves. The “External IPs” in your instance group would be whitelisted to enable access. Note: this solution will not work if you scale up/down the number of instances in your instance group.

Option IV: You can deploy a CloudSQL Proxy along with your Rails app into Kubernetes, so you have a safe, secure way of communicating between your application containers and your CloudSQL instance. This article shows you how to do that.

Using a CloudSQL Proxy to access CloudSQL from a Rails application in GKE

In the demo app (gkedemo) the following changes have been made to accommodate the above interaction:

1. Create a service account, and download the JSON credentials file (as described here)

2. Create a user name and password for the user that the CloudSQL Proxy will use to access the database. In this case, the user name is dogs_admin and the password is password

3. gcloud_deployment.sh: Create the secrets that will be used by your application (in web-deployment-cloudsqlproxy.yml) to talk to CloudSQL. There are 2 secrets:

cloudsql-instance-credentials : used by your application to talk to the CloudSQL instance. It uses the credentials file created earlier for the service account in step 1.

cloudsql-db-credentials: the user name and password that is needed to connect to the database.

4. web-deployment-cloudsqlproxy.yml — This adds a Docker image for the cloud sql proxy and defines the volumes where the secrets will be present. Ensure that you replace [YOUR_INSTANCE] with the appropriate instance name

You will also need to make sure that your application container (called web in my case) refers to the following environment variables to connect to the CloudSQL instance: DB_HOST, DB_PASSWORD, DB_USER. These are names I used for this example.

5. database.yml — For the “development” group (I run this application in development mode), you can add the following entries:

Once the above are done, you can run ./gcloud_deployment.sh to deploy the web application and the cloudsql proxy. Note: if this is your first time running this application, you can uncomment the section “For FIRST TIME DEPLOYMENT….” and comment out the section “# For SUBSEQUENT DEPLOYMENTS — -”

After you have run gcloud_deployment.sh successfully, you can run “kubectl get pods”. You should be able to see the pods running the web and cloudsql-proxy containers as below: (The 2/2 in the READY column)

To check if the proxy is running properly, you can view it’s logs by running the command “kubectl logs <podname> -c cloudsql-proxy. You should see a similar message as below:

Resources:

1. How to deploy a Rails application to GKE: https://medium.com/@nithinmallya4/deploying-a-rails-application-to-google-container-engine-with-kubernetes-b08b2de353fc
2. Google’s official documentation on how to connect to CloudSQL using the CloudSQL Proxy from GKE: https://cloud.google.com/sql/docs/mysql/connect-container-engine
3. Kubernetes Documentation: https://kubernetes.io/docs/home/
4. Blue/Green Deployments: https://medium.com/@nithinmallya4/blue-green-deployments-for-a-rails-app-in-google-container-engine-gke-49ddcc1b002