7. Attaching a database

Numerous applications are stateful in some way and want to save data persistently, be it in a database, as files on a filesystem or in an object store. In this lab, we are going to create a MariaDB database and configure our application to store its data in it.

Task 7.1: Instantiate a MariaDB database

We are going to use an OpenShift template to create the database. This can be done by either using the Web Console or the CLI. Both are going to be explained in this lab, so pick the one you are more comfortable with.

Instantiate a template using the Web Console

Make sure you are in OpenShift’s Developer view (upper left dropdown) and have selected the correct Project:

Now click +Add, choose Database, MariaDB (Ephemeral) and then Instantiate Template. A form opens. Check that the first field corresponds to the correct Project and set the MariaDB Database Name field to acend_exampledb and leave the remaining fields as they are. Finally, click Create at the end of the form.

Instantiate a template using the CLI

We are going to instantiate the MariaDB Template from the openshift Project. Before we can do that, we need to know what parameters the Template expects. Let’s find out:

oc process --parameters openshift//mariadb-ephemeral

NAME                    DESCRIPTION                                                               GENERATOR           VALUE
MEMORY_LIMIT            Maximum amount of memory the container can use.                                               512Mi
NAMESPACE               The OpenShift Namespace where the ImageStream resides.                                        openshift
DATABASE_SERVICE_NAME   The name of the OpenShift Service exposed for the database.                                   mariadb
MYSQL_USER              Username for MariaDB user that will be used for accessing the database.   expression          user[A-Z0-9]{3}
MYSQL_PASSWORD          Password for the MariaDB connection user.                                 expression          [a-zA-Z0-9]{16}
MYSQL_ROOT_PASSWORD     Password for the MariaDB root user.                                       expression          [a-zA-Z0-9]{16}
MYSQL_DATABASE          Name of the MariaDB database accessed.                                                        sampledb
MARIADB_VERSION         Version of MariaDB image to be used (10.2 or latest).                                         10.2

As you might already see, each of the parameters has a default value (“VALUE” column). Also, the parameters MYSQL_USER, MYSQL_PASSWORD and MYSQL_ROOT_PASSWORD are going to be generated (“GENERATOR” is set to expression and “VALUE” contains a regular expression). This means we don’t necessarily have to overwrite any of them so let’s simply use those defaults:

oc process openshift//mariadb-ephemeral -pMYSQL_DATABASE=acend_exampledb  | oc apply --namespace=<namespace> -f -

The output should be:

secret/mariadb created
service/mariadb created
deploymentconfig.apps.openshift.io/mariadb created

Task 7.2: Inspection

What just happened is that you instantiated an OpenShift Template that creates multiple resources using the (default) values as parameters. Let’s have a look at the resources that have just been created by looking at the Template’s definition:

oc get templates -n openshift mariadb-ephemeral -o yaml

The Template’s content reveals a Secret, a Service and a DeploymentConfig.

The Secret contains the database name, user, password, and the root password. However, these values will neither be shown with oc get nor with oc describe:

oc get secret mariadb --output yaml --namespace <namespace>

apiVersion: v1
data:
  database-name: YWNlbmQtZXhhbXBsZS1kYg==
  database-password: bXlzcWxwYXNzd29yZA==
  database-root-password: bXlzcWxyb290cGFzc3dvcmQ=
  database-user: YWNlbmRfdXNlcg==
kind: Secret
metadata:
  ...
type: Opaque

The reason is that all the values in the .data section are base64 encoded. Even though we cannot see the true values, they can easily be decoded:

echo "YWNlbmQtZXhhbXBsZS1kYg==" | base64 -d

The interesting thing about Secrets is that they can be reused, e.g., in different Deployments. We could extract all the plaintext values from the Secret and put them as environment variables into the Deployments, but it’s way easier to instead simply refer to its values inside the Deployment (as in this lab) like this:

...
spec:
  template:
    spec:
      containers:
      - name: mariadb
        env:
        - name: MYSQL_USER
          valueFrom:
            secretKeyRef:
              key: database-user
              name: mariadb
        - name: MYSQL_PASSWORD
          valueFrom:
            secretKeyRef:
              key: database-password
              name: mariadb
        - name: MYSQL_ROOT_PASSWORD
          valueFrom:
            secretKeyRef:
              key: database-root-password
              name: mariadb
        - name: MYSQL_DATABASE
          valueFrom:
            secretKeyRef:
              key: database-name
              name: mariadb
...

Above lines are an excerpt of the MariaDB Deployment. Most parts have been cut out to focus on the relevant lines: The references to the mariadb Secret. As you can see, instead of directly defining environment variables you can refer to a specific key inside a Secret. We are going to make further use of this concept for our Python application.

Task 7.3: Attach the database to the application

By default, our example-web-app application uses an SQLite memory database.

However, this can be changed by defining the following environment variable to use the newly created MariaDB database:

#MYSQL_URI=mysql://<user>:<password>@<host>/<database>
MYSQL_URI=mysql://acend_user:mysqlpassword@mariadb/acend_exampledb

The connection string our example-web-app application uses to connect to our new MariaDB, is a concatenated string from the values of the mariadb Secret.

For the actual MariaDB host, you can either use the MariaDB Service’s ClusterIP or DNS name as the address. All Services and Pods can be resolved by DNS using their name.

The following commands set the environment variables for the deployment configuration of the example-web-app application:

oc set env --from=secret/mariadb --prefix=MYSQL_ deploy/example-web-app --namespace <namespace>

and

oc set env deploy/example-web-app MYSQL_URI='mysql://$(MYSQL_DATABASE_USER):$(MYSQL_DATABASE_PASSWORD)@mariadb/$(MYSQL_DATABASE_NAME)' --namespace <namespace>

The first command inserts the values from the Secret, the second finally uses these values to put them in the environment variable MYSQL_URI which the application considers.

You can also do the changes by directly editing your local deployment_example-web-app.yaml file. Find the section which defines the containers. You should find it under:

...
spec:
...
 template:
 ...
  spec:
    containers:
    - image: ...
...

The dash before image: defines the beginning of a new container definition. The following specifications should be inserted into this container definition:

        env:
          - name: MYSQL_DATABASE_NAME
            valueFrom:
              secretKeyRef:
                key: database-name
                name: mariadb
          - name: MYSQL_DATABASE_PASSWORD
            valueFrom:
              secretKeyRef:
                key: database-password
                name: mariadb
          - name: MYSQL_DATABASE_ROOT_PASSWORD
            valueFrom:
              secretKeyRef:
                key: database-root-password
                name: mariadb
          - name: MYSQL_DATABASE_USER
            valueFrom:
              secretKeyRef:
                key: database-user
                name: mariadb
          - name: MYSQL_URI
            value: mysql://$(MYSQL_DATABASE_USER):$(MYSQL_DATABASE_PASSWORD)@mariadb/$(MYSQL_DATABASE_NAME)

Your file should now look like this:

      ...
      containers:
      - image: quay.io/acend/example-web-python:latest
        imagePullPolicy: Always
        name: example-web-app
        ...
        env:
        - name: MYSQL_DATABASE_NAME
          valueFrom:
            secretKeyRef:
              key: database-name
              name: mariadb
        - name: MYSQL_DATABASE_PASSWORD
          valueFrom:
            secretKeyRef:
              key: database-password
              name: mariadb
        - name: MYSQL_DATABASE_ROOT_PASSWORD
          valueFrom:
            secretKeyRef:
              key: database-root-password
              name: mariadb
        - name: MYSQL_DATABASE_USER
          valueFrom:
            secretKeyRef:
              key: database-user
              name: mariadb
        - name: MYSQL_URI
          value: mysql://$(MYSQL_DATABASE_USER):$(MYSQL_DATABASE_PASSWORD)@mariadb/$(MYSQL_DATABASE_NAME)

Then use:

oc apply -f deployment_example-web-app.yaml --namespace <namespace>

to apply the changes.

The environment can also be checked with the set env command and the --list parameter:

oc set env deploy/example-web-app --list --namespace <namespace>

This will show the environment as follows:

# deployments/example-web-app, container example-web-app
# MYSQL_DATABASE_PASSWORD from secret mariadb, key database-password
# MYSQL_DATABASE_ROOT_PASSWORD from secret mariadb, key database-root-password
# MYSQL_DATABASE_USER from secret mariadb, key database-user
# MYSQL_DATABASE_NAME from secret mariadb, key database-name
MYSQL_URI=mysql://$(MYSQL_DATABASE_USER):$(MYSQL_DATABASE_PASSWORD)@mariadb/$(MYSQL_DATABASE_NAME)

In order to find out if the change worked we can either look at the container’s logs (oc logs <pod>) or we could register some “Hellos” in the application, delete the Pod, wait for the new Pod to be started and check if they are still there.

Task 7.4: Manual database connection

As described in 6. Troubleshooting we can log into a Pod with oc rsh <pod>.

Show all Pods:

oc get pods --namespace <namespace>

Which gives you an output similar to this:

NAME                                  READY   STATUS      RESTARTS   AGE
example-web-app-574544fd68-qfkcm      1/1     Running     0          2m20s
mariadb-f845ccdb7-hf2x5               1/1     Running     0          31m
mariadb-1-deploy                      0/1     Completed   0          11m

Log into the MariaDB Pod:

oc rsh --namespace <namespace> <mariadb-pod-name>

You are now able to connect to the database and display the data. Login with:

mysql -u$MYSQL_USER -p$MYSQL_PASSWORD -h$MARIADB_SERVICE_HOST $MYSQL_DATABASE

Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 52810
Server version: 10.2.22-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [acend_exampledb]>

Show all tables with:

show tables;

Show any entered “Hellos” with:

select * from hello;

Task 7.5: Import a database dump

Our task is now to import this dump.sql into the MariaDB database running as a Pod. Use the mysql command line utility to do this. Make sure the database is empty beforehand. You could also delete and recreate the database.

Solution

This is how you copy the database dump into the MariaDB Pod.

Download the dump.sql or get it with curl:

curl -O https://raw.githubusercontent.com/acend/kubernetes-basics-training/main/content/en/docs/attaching-a-database/dump.sql

Copy the dump into the MariaDB Pod:

oc cp ./dump.sql <podname>:/tmp/ --namespace <namespace>

This is how you log into the MariaDB Pod:

oc rsh --namespace <namespace> <podname>

This command shows how to drop the whole database:

mysql -u$MYSQL_USER -p$MYSQL_PASSWORD -h$MARIADB_SERVICE_HOST $MYSQL_DATABASE

drop database `acend_exampledb`;
create database `acend_exampledb`;
exit

Import a dump:

mysql -u$MYSQL_USER -p$MYSQL_PASSWORD -h$MARIADB_SERVICE_HOST $MYSQL_DATABASE < /tmp/dump.sql

Check your app to see the imported “Hellos”.

oc get route --namespace <namespace>

oc rsh --namespace <namespace> <podname>

mysqldump --user=$MYSQL_USER --password=$MYSQL_PASSWORD -h$MARIADB_SERVICE_HOST $MYSQL_DATABASE > /tmp/dump.sql

oc cp <podname>:/tmp/dump.sql /tmp/dump.sql