Connecting From GCE / GAE (Java) to Google Cloud SQL

Started using Google Cloud SQL couple of days back. While it was easy to connect to the database from app engine application, it became pain for a few hours last evening to figure out how to connect the app on the compute engine instances. Now that I am clearer on how to connect to Cloud SQL databases from both these environments, I wanted to leave a small write-up here.

1) Connecting from App Engine (GAE) to Cloud SQL:

A) Authorization between App Engine and DB instances

  • If your GAE app and the DB instances are under the same project, there is no need to worry about setting up the authorization between the two. It happens under the hoods, using the default service account of the app engine app.
  • If your GAE app is under a different project than the DB instance’s, then you need to find out the default service account for app engine project and add it as a member with Editor role in the project that owns the DB instance. You need to use “IAM & Admin” section on the Google Cloud Platform project for both these actions.

B) Establishing JDBC Connection

  • Before you can make JDBC connections, you need to create a user at the database-level and grant the appropriate permissions. Say, we create a user with details test_user/test_password.
  • Find out the Cloud SQL DB instance’s connection name. You will find it under “Instance connection name” entry, if you go to the db instance’s details page on the Google Cloud Dashboard. It takes the form “<project id>:<time zone>:<instance id>”
  • The JDBC driver to use is –
"com.mysql.jdbc.GoogleDriver"

Nothing to be installed for it. On, App Engine, it’s available by default.

  • The connection string to use is –
"jdbc:google:mysql://<project id>:<time zone>:<instance id>/<db_name>?user=test_user&password=test_password"

The GoogleDriver takes care of understanding this URL and establishing the connection.

2 a) Connecting from GCE (Compute Engine) to Cloud SQL – Using Google Cloud Proxy

A) Authorization between GCE and DB instances

  • If your GCE instance has a static IP address, the communication between the GCE instance and DB instance needs to be pre-authorized by IP address. It can be done by going to the Cloud SQL instance’s details page on the Cloud Dashboard, opening “Access Control -> Authorisation” section and add an entry there to whitelist the GCE instance’s IP address.
  • If your GCE instace doesn’t have a static IP address (perhaps, it’s part of an auto-scalabale instance-pool that sits behind a load-balancer), then you cannot do the pre-authorization by adding the IP address. Your only option is to use Google Cloud SQL Proxy.

Using the proxy takes care of the following:

  –  authorization using the GCE instance’s default service account

  –  opens a tunnel between the localhost’s 3306 port to the DB instance identified by “<project id>:<time zone>:<instance id>”. Remember that the DB could be in the same project, or in another project that your GCE service account has Editor access to.

B) Establishing JDBC Connection

  • The Stock Mysql JDBC driver needs to be used now –
"com.mysql.jdbc.Driver"
  • The connection string to use is –
"jdbc:mysql://127.0.0.1:3306/<db_name>?user=test_user&password=test_password"

Behind the scenes, Cloud SQL proxy connects localhost’s 3306 port safely to the Cloud SQL instance.

C) Setting up the Cloud SQL Proxy

The relevant steps for downloading/starting the proxy are reproduced below from https://cloud.google.com/sql/docs/compute-engine-access

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

mv cloud_sql_proxy.linux.amd64 cloud_sql_proxy

chmod +x cloud_sql_proxy

./cloud_sql_proxy -instances=<project id>:<time zone>:<instance id>=tcp:3306

If Your GCE instance is part of an instance-pool that grows/shrinks based on the scale, then you need to automate the above steps in the instance’s start-up script.

Also, if you don’t want to use 3306 locally. You can choose another free port and use the same in starting the proxy and in the JDBC Connection URL.

EDIT (23-Sep-2016): It seems that there is one more way to make GCE to Google Cloud SQL connections, using “cloud-sql-mysql-socket-factory”. Added the next section 2 b) to cover its details.

2 b) Connecting from GCE (Compute Engine) to Cloud SQL – Using “cloud-sql-mysql-socket-factory”

A) Authorization between GCE and DB instances

Remains same as when you use Google Cloud SQL Proxy. It’s handled by this custom socket factory library behind the scenes.

B) Establishing JDBC Connection

  • The Stock Mysql JDBC driver needs to be used –
"com.mysql.jdbc.Driver"
  • The connection string to use is –
"jdbc:mysql://google/<db_name>?cloudSqlInstance=<project id>:<time zone>:<instance id>&socketFactory=com.google.cloud.sql.mysql.SocketFactory&user=test_user&password=test_password"

IMO, here are some potential pros and cons of using this library vs the Google Cloud SQL Proxy:

Pros: No complication of downloading / installing / starting the Google Cloud Sql Proxy in the instance start-up scripts.

Cons: Relatively new library. Born just recently in May-2016. Not sure how battle-ready we should assume it to be, and whether it’ll be constantly performant on scale.

That’s all. Hope it helps someone. Cheers.