Configure Google Cloud SQL¶

When running LTD Keeper from the Google Container Engine, it makes sense to use Google’s Cloud SQL hosted database service. Separating Keeper’s state from the application grants us operational flexibility to scale and upgrade the application through Kubernetes.

Google Cloud SQL manages the MySQL server, along with backups and fail-over, allowing pods and the LTD Keeper application itself to be stateless and therefore scalable.

LTD Keeper uses a 2nd generation high-availability Cloud SQL instance, which can be created by following Google’s documentation at https://cloud.google.com/sql/docs/create-instance.

Enable the Cloud SQL API¶

First, the Cloud SQL API may need to be explicitly enabled. This can be done from the Google Cloud console’s API section.

Create a high-availabilty SQL instance¶

Create a master instance named ltd-sql-1 using the gcloud tool:

gcloud sql instances create ltd-sql-1 --tier=db-g1-small --activation-policy=ALWAYS

Then set the root password for this instance:

gcloud sql instances set-root-password ltd-sql-1 --password [PASSWORD]

Next, follow instructions at https://cloud.google.com/sql/docs/configure-ha to create a high-availability instance. A high-availability SQL instance creates a fail-over node that continuously replicates the master.

You can also enable automated backups from the instance’s page on the Google Cloud console.

Create a Service Account¶

Note

This step may not longer be necessary with Cloud SDK authentication and automatic service discovery.

To authenticate to the Cloud SQL instance, we need to create a Google Cloud Service Account.

Create a new Service Account from the IAM & Admin section of the Google Cloud console.

Name the Service Account sql-proxy-service and request a JSON credentials file. This JSON file will be used directly with the Cloud SQL proxy and also to build configuration secrets.

See Google’s documentation for complete steps on creating a Service Account.

Install and Run the Cloud SQL Proxy¶

LTD operators should install the Cloud SQL Proxy locally to access and administer the Cloud SQL instance.

Assuming that Go is installed:

go get github.com/GoogleCloudPlatform/cloudsql-proxy/cmd/cloud_sql_proxy

Create a convenient directory where a unix socket can be created:

mkdir cloudsql
sudo chmod 777

And run the proxy:

$GOPATH/bin/cloud_sql_proxy -dir=cloudsql

Note

Alternatively, a Service Account credential can be used:

$GOPATH/bin/cloud_sql_proxy -dir=cloudsql -instances=PROJECT:REGION:ltd-sql-1 --credential_file=service_account.json

Replace PROJECT and REGION with the Google Cloud project’s name and default region (specified previously in Setting up a Google Kubernetes Engine Cluster).

service_account.json is the path to the service account JSON credentials file that was downloaded previously.

See the github.com/GoogleCloudPlatform/cloudsql-proxy repository for further details.

Connect to the Cloud SQL Instance and Create a keeper Database¶

With the Cloud SQL Proxy running in one terminal session, run a mysql client in other.

mysql -u root -p -S ./cloudsql/PROJECT:REGION:ltd-sql-1

and enter the databases’ root password created previously.

While logged into the database, create a DB specifically for LTD Keeper:

mysql> create database keeper;
Query OK, 1 row affected (0.10 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| keeper             |
| mysql              |
| performance_schema |
+--------------------+
4 rows in set (0.08 sec)

You may now exit from the mysql terminal and close the proxy connection (control-C).

Next, create configuration secrets in Kubernetes.