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.

Warning

Please make sure you completed labs 2. First steps and 5. Scaling before you continue with this lab.

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

Warning

Do not execute these steps if you already have created a MariaDB database using the Web Console.

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

Note

There’s also the oc extract command which can be used to extract the content of Secrets and ConfigMaps into a local directory. Use oc extract --help to see how it works.

Note

By default, Secrets are not encrypted!

However, both OpenShift and Kubernetes (1.13 and later) offer the capability to encrypt data in etcd.

Another option would be the use of a secrets management solution like Vault by HashiCorp .

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:

#SPRING_DATASOURCE_URL=jdbc:mysql://<host>/<database>
SPRING_DATASOURCE_URL=jdbc:mysql://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.

Add the environment variables by directly editing the Deployment:

oc edit deployment example-web-app --namespace <namespace>

      ...
      containers:
      - image: quay.io/appuio/example-spring-boot:latest
        imagePullPolicy: Always
        name: example-web-app
        ...
        env:
        - name: SPRING_DATASOURCE_DATABASE_NAME
          valueFrom:
            secretKeyRef:
              key: database-name
              name: mariadb
        - name: SPRING_DATASOURCE_USERNAME
          valueFrom:
            secretKeyRef:
              key: database-user
              name: mariadb
        - name: SPRING_DATASOURCE_PASSWORD
          valueFrom:
            secretKeyRef:
              key: database-password
              name: mariadb
        - name: SPRING_DATASOURCE_DRIVER_CLASS_NAME
          value: com.mysql.cj.jdbc.Driver
        - name: SPRING_DATASOURCE_URL
          value: jdbc:mysql://mariadb/$(SPRING_DATASOURCE_DATABASE_NAME)?autoReconnect=true
        ...

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
# SPRING_DATASOURCE_DATABASE_NAME from secret mariadb, key database-name
# SPRING_DATASOURCE_USERNAME from secret mariadb, key database-user
# SPRING_DATASOURCE_PASSWORD from secret mariadb, key database-password
SPRING_DATASOURCE_DRIVER_CLASS_NAME=com.mysql.cj.jdbc.Driver
SPRING_DATASOURCE_URL=jdbc:mysql://mariadb/$(SPRING_DATASOURCE_DATABASE_NAME)?autoReconnect=true

Warning

Do not proceed with the lab before all example-web-app pods are restarted successfully.

The change of the deployment definition (environment change) triggers a new rollout and all example-web-app pods will be restarted. The application will not be connected to the database until all pods are restarted successfully.

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.

Note

This does not work if we delete the database Pod as its data is not yet persisted.

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:

Note

As mentioned in 6. Troubleshooting, remember to append the command with winpty if you’re using Git Bash on Windows.

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.

Note

You can also copy local files into a Pod using oc cp. Be aware that the tar binary has to be present inside the container and on your operating system in order for this to work! Install tar on UNIX systems with e.g. your package manager, on Windows there’s e.g. cwRsync . If you cannot install tar on your host, there’s also the possibility of logging into the Pod and using curl -O <url>.

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”.

Note

You can find your app URL by looking at your route:

oc get route --namespace <namespace>

Note

A database dump can be created as follows:

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

Last modified November 13, 2024: Merge 94e638b16354ec7cc741b072b384c57879f53f43 into cc6d879ac4794295fa476bd1a7c2944d9d6e363c (f2e52dc)