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, 3. Deploying a container image and 4. Exposing a service before you continue with this lab.

Task 7.1: Instantiate a MariaDB database

We are first going to create a so-called Secret in which we store sensitive data. The secret will be used to access the database and also to create the initial database. The oc create secret command helps us create the secret like so:

oc create secret generic mariadb \
  --from-literal=database-name=acend_exampledb \
  --from-literal=database-password=mysqlpassword \
  --from-literal=database-root-password=mysqlrootpassword \
  --from-literal=database-user=acend_user \
  --namespace <namespace> \
  --dry-run=client -o yaml > secret_mariadb.yaml

Above command has not yet created any resources on our cluster as we used the --dry-run=client parameter and redirected the output into the file secret_mariadb.yaml.

The reason we haven’t actually created the Secret yet but instead put the resource definition in a file has to do with the way things work at Baloise. The file will help you later. But for now, create the Secret by applying the file’s content:

oc apply -f secret_mariadb.yaml

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.

At Baloise, secrets are managed by HashiCorp Vault and integrated into OpenShift by use of the External Secrets Operator .

We are now going to create a Deployment and a Service. As a first example, we use a database without persistent storage. Only use an ephemeral database for testing purposes as a restart of the Pod leads to data loss. We are going to look at how to persist this data in a persistent volume later on.

In our case we want to create a Deployment and Service for our MariaDB database. Save this snippet as mariadb.yaml:

apiVersion: v1
kind: Service
metadata:
  name: mariadb
  labels:
    template: mariadb-ephemeral-template
spec:
  ports:
  - name: mariadb
    port: 3306
    protocol: TCP
    targetPort: 3306
  selector:
    app: mariadb
  type: ClusterIP
---
apiVersion: apps/v1
kind: Deployment
metadata:
  name: mariadb
  labels:
    app: mariadb
spec:
  selector:
    matchLabels:
      app: mariadb
  strategy:
    type: Recreate
  template:
    metadata:
      labels:
        app: mariadb
    spec:
      containers:
        - image: REGISTRY-URL/acend/mariadb-105:1
          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
          livenessProbe:
            tcpSocket:
              port: 3306
          ports:
            - containerPort: 3306
              name: mariadb
          resources:
            limits:
              cpu: 500m
              memory: 512Mi
            requests:
              cpu: 50m
              memory: 128Mi
          volumeMounts:
            - mountPath: /var/lib/mysql/data
              name: mariadb-data
      volumes:
        - emptyDir: {}
          name: mariadb-data

Apply it with:

oc apply -f mariadb.yaml --namespace <namespace>

As soon as the container image has been pulled, you will see a new Pod using oc get pods.

The environment variables defined in the deployment configure the MariaDB Pod and how our frontend will be able to access it.

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:

Warning

Depending on the shell you use, the following set env command works but inserts too many apostrophes! Check the deployment’s environment variable afterwards or directly edit it as described further down below.

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: REGISTRY-URL/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)

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)