Create and populate a database in MariaDB

In the last lesson, you created a back-end RESTful service. In this lesson, you'll switch out the hard-coded quotes for quotes stored in a MariaDB database.

In order to get full benefit from taking this lesson, you need to:

• Create a Persistent Volume Claim (PVC)
• Create a Secret
• Create and populate a database

In this lesson, you will:

• Swap out the hard-coded results for a microservice that reads from a database

---

Create a PVC to support MariaDB running in Kubernetes

In your quotemysql directory, you'll find the file mysqlvolume.yaml. It's 5 GB in size, using the host file system. This is where we will direct the MariaDB application to place the data files it needs.

apiVersion: v1

kind: PersistentVolumeClaim

metadata:

  name: mysqlvolume

spec:

  resources:

    requests:

      storage: 5Gi

  volumeMode: Filesystem

  accessModes:

    - ReadWriteOnce

Run the following command to create the PVC:

kubectl create -f mysqlvolume.yaml

Figure 11 shows an example.

Create a Secret to be used with the database

Navigate to the quotemysql directory on your local PC, where you'll find the file mysql-secret.yaml:

apiVersion: v1

kind: Secret

metadata:

  name: mysqlpassword

type: Opaque

data:

  password: YWRtaW4=

Run the following command to create the Secret object:

kubectl create -f mysql-secret.yaml

Create a MariaDB database, quotesdb, running in Kubernetes

In your quotemysql directory, you'll find the file mysql-deployment.yaml. Notice the password name (mysqlpassword, the Secret we created), the persistentVolumeClaim (mysqlvolume, which we created), and the volumeMounts information.

These entries should look familiar. We just created them.

apiVersion: apps/v1

kind: Deployment

metadata:

  name: mysql

  labels:

    sandbox: learn-kubernetes

    learn-kubernetes: quotemysql

spec:

  selector:

    matchLabels:

      app: mysql

      tier: database

  template:

    metadata:

      labels:

        app: mysql

        tier: database

    spec:

      containers:

      - name: mariadb

        env:

        - name: MYSQL_ROOT_PASSWORD

          valueFrom:

            secretKeyRef:

              name: mysqlpassword

              key: password

        image: mariadb

        resources:

          limits:

            memory: "512Mi"

            cpu: "500m"

        ports:

        - containerPort: 3306

        volumeMounts:

        - name: mysqlvolume

          mountPath: /var/lib/mysql

      volumes:

       - name: mysqlvolume

         persistentVolumeClaim:

           claimName: mysqlvolume

We now have all the pieces to spin up a MariaDB database in our Kubernetes cluster:

• Persistent Volume Claim (PVC)
• Secret
• Deployment

Run the following command to create the MariaDB database instance:

kubectl create -f mysql-deployment.yaml

Create and populate the database

The following instructions pertain to a PowerShell-based command-line experience. For instructions on how to use Bash Shell, scroll down further in this activity.

PowerShell:

We need the name of the pod running MariaDB. The pod name begins with mysql. Run the following command to get the pod name into an environment variable:

$a = (kubectl get pods | select-string 'mysql') -match 'mysql([^\s]+)'; $podname = $matches[0]

This command puts the pod name into a variable ($podname) to be used in the remaining command. In this particulate example, the pod name is mysql-65c8cd6dc6-fs2zj. Optionally, you can use the PowerShell command $podname to see the value. Here’s an example of these commands:

$a = (kubectl get pods | select-string 'mysql') -match 'mysql([^\s]+)'; $podname = $matches[0]

$podname

mysql-749cfb6d5f-qxqwk

Run the following three commands to copy the database creation commands into the pod and execute the script:

kubectl cp ./create_database_quotesdb.sql ${podname}:/tmp/create_database_quotesdb.sql

kubectl cp ./create_database.sh ${podname}:/tmp/create_database.sh

kubectl exec deploy/mysql -- /bin/bash ./tmp/create_database.sh

Run the following three commands to copy the table creation commands into the pod and execute the script:

kubectl cp ./create_table_quotes.sql ${podname}:/tmp/create_table_quotes.sql

kubectl cp ./create_tables.sh ${podname}:/tmp/create_tables.sh

kubectl exec deploy/mysql -- /bin/bash ./tmp/create_tables.sh

Run the following four commands to copy data into the pod and populate the database:

kubectl cp ./populate_table_quotes_POWERSHELL.sql ${podname}:/tmp/populate_table_quotes_POWERSHELL.sql

kubectl cp ./quotes.csv ${podname}:/tmp/quotes.csv

kubectl cp ./populate_tables_POWERSHELL.sh ${podname}:/tmp/populate_tables_POWERSHELL.sh

kubectl exec deploy/mysql -- /bin/bash ./tmp/populate_tables_POWERSHELL.sh

Run the following three commands to query the table to prove that the database is ready:

kubectl cp ./query_table_quotes.sql ${podname}:/tmp/query_table_quotes.sql

kubectl cp ./query_table_quotes.sh ${podname}:/tmp/query_table_quotes.sh

kubectl exec deploy/mysql -- /bin/bash ./tmp/query_table_quotes.sh

Bash 

We need the name of the pod running MariaDB. The pod name begins with mysql. Run the following command to get the name of the pod running the MariaDB instance into an environment variable:

export PODNAME=$(a=$(kubectl get pods | grep 'mysql' | awk '{print $1}') && set – $a && echo $1)

This puts the pod name into a variable (PODNAME) to be used in the remaining command. In this particular example, the pod name is mysql-65c8cd6dc6-fs2zj.

Run the following three commands to copy the database creation commands into the pod and execute the script:   

kubectl cp ./create_database_quotesdb.sql $PODNAME:/tmp/create_database_quotesdb.sql

kubectl cp ./create_database.sh $PODNAME:/tmp/create_database.sh

kubectl exec deploy/mysql -- /bin/bash ./tmp/create_database.sh

Run the following three commands to copy the table creation commands into the pod and execute the script:

kubectl cp ./create_table_quotes.sql $PODNAME:/tmp/create_table_quotes.sql

kubectl cp ./create_tables.sh $PODNAME:/tmp/create_tables.sh

kubectl exec deploy/mysql -- /bin/bash ./tmp/create_tables.sh

Run the following four commands to populate the database table:

kubectl cp ./populate_table_quotes_BASH.sql $PODNAME:/tmp/populate_table_quotes_BASH.sql

kubectl cp ./quotes.csv $PODNAME:/tmp/quotes.csv

kubectl cp ./populate_tables_BASH.sh $PODNAME:/tmp/populate_tables_BASH.sh

kubectl exec deploy/mysql -- /bin/bash ./tmp/populate_tables_BASH.sh

Run the following three commands to query the database to prove our work:

kubectl cp ./query_table_quotes.sql $PODNAME:/tmp/query_table_quotes.sql

kubectl cp ./query_table_quotes.sh $PODNAME:/tmp/query_table_quotes.sh

kubectl exec deploy/mysql -- /bin/bash ./tmp/query_table_quotes.sh

Update the back-end program and observe the results

Before we switch to version 2, we need to do some housekeeping. While version 1 of our quotes service has values hard-coded into the code, version 2 reads from the database service mysql. The name of this service is not hard-coded into the source code. Instead, it reads the name of the service from the environment variable DB_SERVICE_NAME.

Here's the code snippet where that happens:

    try:

        conn = mariadb.connect(

            user="root",

            password="admin",

            host=os.environ['DB_SERVICE_NAME'],

            database="quotesdb",

            port=3306)

The following command will create that environment variable in our deployment. When we update to version 2, it will be available to the Python code:

kubectl set env deployment/quotes DB_SERVICE_NAME=mysql

At this point, we have a front-end application (quotesweb) talking to the back-end application (quotes). We also have a database, running in service mysql.

What we need to do is update our back-end app to use our database. Kubernetes will do this on-the-fly by doing what's called a rolling update. We already have a version 2 image in an image registry, so all we need to do is change the image in our deployment of quotes to point to version 2. Kubernetes will pull the image, spin up a pod running version 2, and then switch the routing to version 2.

Run the following command to switch to version 2:

kubectl set image deploy quotes quotes=quay.io/donschenck/quotes:v2

After a few seconds—seconds, not minutes—you may need to refresh your browser and re-enter the endpoint URL. At that point, you will notice that there are several more quotes being randomly accessed.

Destroy the MariaDB pod to observe Kubernetes' self healing

Because we use a PVC for our database, instead of an ephemeral database, our data remains intact when a pod falls over. You can prove this by deleting the pod running your MariaDB database. Kubernetes will replace the pod immediately and MariaDB will restart with no operator intervention. Go ahead, give it a try.

Run the following command to delete the running MariaDB pod:

PowerShell

kubectl delete pod ${podname}

Bash

kubectl delete pod $PODNAME

---

Move it, remove it, improve it

Now that you know how to create an application using Kubernetes, here are some other ideas to try.

Move

You can download all of the YAML files associated with this application and use them to move it to another OpenShift instance by using the Export Application button in the upper right corner of the OpenShift dashboard.

Remove

You can remove parts of all of this activity by using one of the following commands:

• To remove only the quotes function:

  kubectl delete pod $PODNAME

• To remove only the quotesweb application:

  kubectl delete all -l learn-kubernetes=quotesweb

• To remove only the MariaDB objects:

  kubectl delete all -l learn-kubernetes=quotemysql

• To remove all of the objects associated with this activity:

  kubectl delete all -l sandbox=learn-kubernetes

Improve

Some ideas to improve or alter this activity:

• Write your own back-end function in a different language.
• Use a different database engine or a database outside of Kubernetes.
• Write the front end in another language.
• Use a server-based web engine that reads the URL from an environment variable that doesn’t need to be entered on the screen.

Guess what? You know Kubernetes.

Well...now you know Kubernetes. Granted, it's just a start, but you're on your way. Congratulations. We have a virtual ton of information available at developers.redhat.com. I suggest you start at KubernetesByExample.com. See you there.