MySQL on GCP

In today’s article we’re going to set up a MySQL database which is going to be use by our pods’s API.

Do you remember how we did that with minikube ?

With Minikube we create a seperate StatefulSets deployment which contain a volume that was linked to our StatefulSet deployment. However as describe in previous articles, creating a database in a container is not what should be done in production environment as database need performance, storage which can grow at anytime. Thus it’s recommended to use a seperate Database environment and this is what we’re going to do today.

GCP has a service name Storage where we can create a relational database like MySQL or PostgreSQL

Creating our MySQL database¶

Let’s create our database by naming it db with the password which is bobbaroot.

Note: We use the same password that we gave with Minikube. However you should not do that in real production environment. This is intended for demonstration purposes.

Now that you have create your database we will connect to the database. Run this command in your terminal, and enter your password when ask.

gcloud sql connect db --user=root

Now we need to create a database. Run this command

create database thebestbobba;

All right let’s create our table. Run these commands

use thebestbobba;

# then create the table
CREATE TABLE bobba (
    ID int NOT NULL AUTO_INCREMENT,
    name varchar(255),
    price decimal,
    shop varchar(255),
    flavor varchar(255),
    calory decimal,
    PRIMARY KEY (ID)
);

Now you have a database with a table great isn’t it ?

Connecting our database with Cloud SQL Proxy¶

There are 2 ways of connecting your database with GCP. The first one is by using a private IP which is the most straightforward way.

However Google is proposing an other way of connecting your database. This one is named CloudSQL Proxy and it’s made to be use with Kubernetes in mind. Therefore we’re going to take a look at how to implement the CloudSQL Proxy.

Creating a new user¶

Firstly, we’re going to create a new user that’s going to be used by the CloudSQL Proxy. In order to create a user click on your database then click on the users tab. Create a new user with the following credentials

username: alison
password: bobba

Now let’s make a connection test in order to check that our user has been added.

gcloud sql connect db --user=alison

Now let’s activate the cloud sql admin api by going into the API section of the GCP dashboard.

On the searchbar type: cloud sql
Click on the Cloud SQL Admin SQL
Click on the Enable button
Create a service account by following this article service account
Get the instance connection name by running the command gcloud sql instances describe db
The name should be equal to the connectionName property within your yaml

Create the credentials¶

We’re going to need 2 types of secrets:

cloudsql-instance-credentials: Used by application to talk to the CloudSQL instance. It uses the credentials files generated when you create the service account.
cloudsql-db-credentials: The new username & password added to the database earlier.

Let’s create the secret for the cloudsql-instance-credentials by running the command

kubectl create secret generic cloudsql-instance-credentials --from-file=credentials.json=<path_to_generate_credentials_file.json>

# If everything goes as expected the shell should print
# secret "cloudsql-instance-credentials" created

Let’s create the secret for the cloudsql-db-credentials by running this command

kubectl create secret generic cloudsql-db-credentials --from-literal=username=alison --from-literal=password=bobba

# If everything goes as expected the shell should print
# secret "cloudsql-db-credentials" created

Update the configuration file¶

Now that we have create our credentials let’s take a look at how the configuration is going to be. The api.yml deployment file already contain the configuration for configuration for cloudsql-proxy.

First we’re adding an other container to the pod definition of our api.yml

spec:
    containers:
    #... the bobba-api container
    # Add the cloudsql-proxy below
        - name: cloudsql-proxy
        image: gcr.io/cloudsql-docker/gce-proxy:1.11
        # Command that's going to be executed when the pod will start
        command: ["/cloud_sql_proxy",
                    "-instances=kubernetes-demo-232217:us-central1:db=tcp:3306",
                    "-credential_file=/secrets/cloudsql/credentials.json"]
        securityContext:
            runAsUser: 2  # non-root user
            allowPrivilegeEscalation: false
        # Reference the volume inside the container
        volumeMounts:
            - name: cloudsql-instance-credentials
            mountPath: /secrets/cloudsql
            readOnly: true
    # Link the volumes where our credentials are stored
    volumes:
    - name: cloudsql-instance-credentials
        secret:
            secretName: cloudsql-instance-credentials

Futhermore we’re updating the bobba-api container definition by providing env variables based on the cloudsql-db-credentials secret we create earlier. (This will override the original environment variables set to the Dockerfile.release)

containers:
- name: bobba-api
  image: gcr.io/kubernetes-demo-232217/sesame_api:v1
  imagePullPolicy: IfNotPresent
  # Override the environment file that we'd define on the Dockerfile
  env:
    - name: MYSQL_HOST
        value: 127.0.0.1
    - name: MYSQL_USER
        valueFrom:
        secretKeyRef:
            name: cloudsql-db-credentials
            key: username
    - name: MYSQL_PASSWORD
        valueFrom:
        secretKeyRef:
            name: cloudsql-db-credentials
            key: password    
  ports:
    - containerPort: 8000
  args:
    - sh
    - start.sh

Creating our MySQL database¶

Connecting our database with Cloud SQL Proxy¶

Creating a new user¶

Create the credentials¶

Update the configuration file¶

Now let’s deploy our API