Manage MySQL in Openshift Using KubeDB

Overview

The databases that KubeDB support are MongoDB, Elasticsearch, MySQL, MariaDB, PostgreSQL, Memcached and Redis. You can find the guides to all the supported databases here . In this tutorial we will deploy MySQL database. We will cover the following steps:

Install KubeDB
Deploy Standalone Database
Install Stash
Backup Using Stash
Recover Using Stash

Install KubeDB

We will follow the following steps to install KubeDB.

Step 1: Get Cluster ID

We need the cluster ID to get the KubeDB License. To get cluster ID we can run the following command:

$ oc get ns kube-system -o=jsonpath='{.metadata.uid}'
08b1259c-5d51-4948-a2de-e2af8e6835a4

Step 2: Get License

Go to Appscode License Server to get the license.txt file. For this tutorial we will use KubeDB Enterprise Edition.

The License Server

Step 3: Install KubeDB

We will use helm to install KubeDB. Please install helm here if it is not already installed. Now, let’s install KubeDB.

$ helm repo add appscode https://charts.appscode.com/stable/
$ helm repo update

$ helm search repo appscode/kubedb
NAME                      	CHART VERSION	APP VERSION	DESCRIPTION                                       
appscode/kubedb           	v2021.06.23  	v2021.06.23	KubeDB by AppsCode - Production ready databases...
appscode/kubedb-autoscaler	v0.4.0       	v0.4.0     	KubeDB Autoscaler by AppsCode - Autoscale KubeD...
appscode/kubedb-catalog   	v2021.06.23  	v2021.06.23	KubeDB Catalog by AppsCode - Catalog for databa...
appscode/kubedb-community 	v0.19.0      	v0.19.0    	KubeDB Community by AppsCode - Community featur...
appscode/kubedb-crds      	v2021.06.23  	v2021.06.23	KubeDB Custom Resource Definitions                
appscode/kubedb-enterprise	v0.6.0       	v0.6.0     	KubeDB Enterprise by AppsCode - Enterprise feat...


# Install KubeDB Enterprise operator chart
$ helm install kubedb appscode/kubedb \
    --version v2021.06.23 \
    --namespace kube-system \
    --set-file global.license=/path/to/the/license.txt \
    --set kubedb-enterprise.enabled=true \
    --set kubedb-autoscaler.enabled=true

Let’s verify the installation:

$ watch oc get pods --all-namespaces -l "app.kubernetes.io/instance=kubedb"

NAMESPACE     NAME                                        READY   STATUS    RESTARTS   AGE
kube-system   kubedb-kubedb-autoscaler-569f66dbbc-qqmmb   1/1     Running   0          3m28s
kube-system   kubedb-kubedb-community-b6469fb9c-4hwbh     1/1     Running   0          3m28s
kube-system   kubedb-kubedb-enterprise-b658c95fc-kwqt6    1/1     Running   0          3m28s

We can see the CRD Groups that have been registered by the operator by running the following command:

$ oc get crd -l app.kubernetes.io/name=kubedb
NAME                                              CREATED AT
elasticsearchautoscalers.autoscaling.kubedb.com   2021-04-21T04:05:40Z
elasticsearches.kubedb.com                        2021-04-21T04:05:37Z
elasticsearchopsrequests.ops.kubedb.com           2021-04-21T04:05:37Z
elasticsearchversions.catalog.kubedb.com          2021-04-21T04:02:43Z
etcds.kubedb.com                                  2021-04-21T04:05:38Z
etcdversions.catalog.kubedb.com                   2021-04-21T04:02:44Z
mariadbs.kubedb.com                               2021-04-21T04:05:38Z
mariadbversions.catalog.kubedb.com                2021-04-21T04:02:44Z
memcacheds.kubedb.com                             2021-04-21T04:05:38Z
memcachedversions.catalog.kubedb.com              2021-04-21T04:02:45Z
mongodbautoscalers.autoscaling.kubedb.com         2021-04-21T04:05:37Z
mongodbopsrequests.ops.kubedb.com                 2021-04-21T04:05:40Z
mongodbs.kubedb.com                               2021-04-21T04:05:38Z
mongodbversions.catalog.kubedb.com                2021-04-21T04:02:46Z
mysqlopsrequests.ops.kubedb.com                   2021-04-21T04:05:48Z
mysqls.kubedb.com                                 2021-04-21T04:05:38Z
mysqlversions.catalog.kubedb.com                  2021-04-21T04:02:46Z
perconaxtradbs.kubedb.com                         2021-04-21T04:05:38Z
perconaxtradbversions.catalog.kubedb.com          2021-04-21T04:02:47Z
pgbouncers.kubedb.com                             2021-04-21T04:05:39Z
pgbouncerversions.catalog.kubedb.com              2021-04-21T04:02:47Z
postgreses.kubedb.com                             2021-04-21T04:05:39Z
postgresversions.catalog.kubedb.com               2021-04-21T04:02:48Z
proxysqls.kubedb.com                              2021-04-21T04:05:39Z
proxysqlversions.catalog.kubedb.com               2021-04-21T04:02:49Z
redises.kubedb.com                                2021-04-21T04:05:39Z
redisopsrequests.ops.kubedb.com                   2021-04-21T04:05:54Z
redisversions.catalog.kubedb.com                  2021-04-21T04:02:49Z

Step 2: Deploy Standalone Database

Now we are going to Install MySQL with the help of KubeDB. At first, let’s create a Namespace in which we will deploy the database.

$ oc create ns demo
namespace/demo created

Now, before deploying the MySQL CRD let’s perform some checks to ensure that it will be deployed correctly.

Check 1: StorageClass Check

Let’s check the available storage classes:

$ oc get storageclass
NAME         PROVISIONER             RECLAIMPOLICY   VOLUMEBINDINGMODE      ALLOWVOLUMEEXPANSION
local-path   rancher.io/local-path   Delete          WaitForFirstConsumer   false

Here, we can see that I have a storageclass named local-path. If you do not have a storage class you can run the following command:

$ oc apply -f https://gist.githubusercontent.com/tamalsaha/58fb8cb07d60f34d77db048680f6102e/raw/a15e7181ea34e447011e91ebf23cd07f2877cbdb/local-path-storage.yaml

This will create the storage-class named local-path.

Check 2: Correct Permissions

We can ensure that the service account has correct permissions by running the following command:

$ oc adm policy add-scc-to-user privileged system:serviceaccount:local-path-storage:local-path-provisioner-service-account

OpenShift has Security Context Constraints for which the MySQL CRD is restricted to be deployed. The above command will give the required permissions.
Here is the yaml of the MySQL CRD we are going to use:

apiVersion: kubedb.com/v1alpha2
kind: MySQL
metadata:
  name: mysql-quickstart
  namespace: demo
spec:
  version: "8.0.23-v1"
  storageType: Durable
  storage:
    accessModes:
    - ReadWriteOnce
    resources:
      requests:
        storage: 1Gi
  terminationPolicy: WipeOut

Let’s save this yaml configuration into mysql.yaml. Then apply using the command oc apply -f mysql.yaml

In this object we can see in the spec.version field, the version of MySQL. You can list the supported versions by running oc get mysqlversions command.
Another field to notice is the spec.storagetype field. This can be Durable or Ephemeral depending on the requirements of the database to be persistent or not.
Lastly, the spec.terminationPolicy field is Wipeout means that the database will be deleted without restrictions. It can also be “Halt”, “Delete” and “DoNotTerminate”. Learn More about these HERE .

Deploy MySQL CRD

Once these are handled correctly and the MySQL CRD is deployed you will see that the following are created:

$ oc get all -n demo
NAME                     READY   STATUS    RESTARTS   AGE
pod/mysql-quickstart-0   1/1     Running   0          2m3s

NAME                            TYPE        CLUSTER-IP     EXTERNAL-IP   PORT(S)    AGE
service/mysql-quickstart        ClusterIP   10.217.5.152   <none>        3306/TCP   2m4s
service/mysql-quickstart-pods   ClusterIP   None           <none>        3306/TCP   2m4s

NAME                                READY   AGE
statefulset.apps/mysql-quickstart   1/1     2m6s

NAME                                                  TYPE               VERSION   AGE
appbinding.appcatalog.appscode.com/mysql-quickstart   kubedb.com/mysql   8.0.23    2m10s

NAME                                VERSION     STATUS   AGE
mysql.kubedb.com/mysql-quickstart   8.0.23-v1   Ready    2m13s

We have successfully deployed MySQL database in OpenShift. Now we can exec into the container to use the database.

Accessing Database Through CLI

To access the database through CLI we have to exec into the container:

$ oc exec -it -n demo mysql-quickstart-0 -- bash
root@mysql-quickstart-0:/#

Then to login into mysql:

root@mysql-quickstart-0:/# mysql -uroot -p${MYSQL_ROOT_PASSWORD}
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 25
Server version: 8.0.23 MySQL Community Server - GPL

Copyright (c) 2000, 2021, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

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

Now we have entered into the MySQL CLI and we can create and delete as we want. Let’s create a database called ’testdb’ and create a table called MyGuests:

mysql> create database testdb;
mysql> show databases;

+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| testdb             |
+--------------------+
5 rows in set (0.01 sec)
mysql> use testdb

Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

mysql> CREATE TABLE MyGuests (
     id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
     firstname VARCHAR(30) NOT NULL,
     lastname VARCHAR(30) NOT NULL,
     email VARCHAR(50),
     reg_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
     );
Query OK, 0 rows affected, 1 warning (0.02 sec)

mysql> show tables;
+------------------+
| Tables_in_testdb |
+------------------+
| MyGuests         |
+------------------+
1 row in set (0.02 sec)

This was just one example of database deployment. The other databases that KubeDB support are MongoDB, Elasticsearch, MariaDB, PostgreSQL, Memcached and Redis. The tutorials on how to deploy these into the cluster can be found HERE

Backup and Recover Database Using Stash

Here we are going to backup the database we deployed before using Stash.

Step 1: Install Stash

Here we will use the KubeDB license we obtained earlier.

$ helm install stash appscode/stash             \
  --version v2021.04.12                  \
  --namespace kube-system                       \
  --set features.enterprise=true                \
  --set-file global.license=/path/to/the/license.txt

Let’s verify the installation:

$ oc get pods --all-namespaces -l app.kubernetes.io/name=stash-enterprise --watch

Step 2: Prepare Backend

Stash supports various backends for storing data snapshots. It can be a cloud storage like GCS bucket, AWS S3, Azure Blob Storage etc. or a Kubernetes persistent volume like HostPath, PersistentVolumeClaim, NFS etc.

For this tutorial we are going to use gcs-bucket. You can find other setups here .

My GCS bucket

At first we need to create a secret so that we can access the gcs bucket. We can do that by the following code:

$ echo -n 'YOURPASSWORD' > RESTIC_PASSWORD
$ echo -n 'YOURPROJECTNAME' > GOOGLE_PROJECT_ID
$ cat /PATH/TO/JSONKEY.json > GOOGLE_SERVICE_ACCOUNT_JSON_KEY
$ oc create secret generic -n demo gcs-secret \
       --from-file=./RESTIC_PASSWORD \
       --from-file=./GOOGLE_PROJECT_ID \
       --from-file=./GOOGLE_SERVICE_ACCOUNT_JSON_KEY

Step 3: Create Repository

apiVersion: stash.appscode.com/v1alpha1
kind: Repository
metadata:
  name: gcs-repo
  namespace: demo
spec:
  backend:
    gcs:
      bucket: stash-shohag
      prefix: /demo/mysql/sample-mysql
    storageSecretName: gcs-secret

This repository CRD specifies the gcs-secret we created before and stores the name and path to the gcs-bucket. It also specifies the location in the bucket where we want to backup our database.

My bucket name is stash-shohag. Don’t forget to change spec.backend.gcs.bucket to your bucket name.

Step 4: Create BackupConfiguration

Now we need to create a BackupConfiguration file that specifies what to backup, where to backup and when to backup.

apiVersion: stash.appscode.com/v1beta1
kind: BackupConfiguration
metadata:
  name: sample-mysql-backup
  namespace: demo
spec:
  schedule: "*/5 * * * *"
  repository:
    name: gcs-repo
  target:
    ref:
      apiVersion: appcatalog.appscode.com/v1alpha1
      kind: AppBinding
      name: mysql-quickstart
  runtimeSettings:
    container:
      securityContext:
        runAsUser: 1000610000
        runAsGroup: 1000610000
  retentionPolicy:
    name: keep-last-5
    keepLast: 5
    prune: true

Notice that the BackupConfiguration contains spec.runtimeSettings.container.securitycontext field. The user and group security context need to be changed in OpenShift to the values within 1000610000 - 1000619999.
This BackupConfiguration creates a cronjob that backs up the specified database (spec.target) every 5 minutes.
spec.repository contaiins the secret we created before called gcs-secret.
spec.target.ref contains the reference to the appbinding that we want to backup.

So, after 5 minutes we can see the following status:

$ oc get backupsession -n demo
NAME                             INVOKER-TYPE          INVOKER-NAME          PHASE       AGE
sample-mysql-backup-1624861209   BackupConfiguration   sample-mysql-backup   Succeeded   103s

$ oc get repository -n demo
NAME       INTEGRITY   SIZE        SNAPSHOT-COUNT   LAST-SUCCESSFUL-BACKUP   AGE
gcs-repo   true        3.670 MiB   1                2m18s                    15m

$ oc get backupconfiguration -n demo
NAME                  TASK   SCHEDULE      PAUSED   AGE
sample-mysql-backup          */5 * * * *            16m

Now if we check our GCS bucket we can see that the backup has been successful.

gcsSuccess

If you reached here CONGRATULATIONS!! 🎊 🥳 🎊 The backup has been successful. If you didn’t, it’s okay. You can reach out to us through EMAIL .

Recover

Let’s think of a scenario in which the database has been accidentally deleted or there was an error in the database causing it to crash. In such a case, we have to pause the BackupConfiguration so that the failed/damaged database does not get backed up into the cloud:

oc patch backupconfiguration -n demo sample-mysql-backup --type="merge" --patch='{"spec": {"paused": true}}'

At first let’s simulate accidental database deletion.

$ oc exec -it -n demo mysql-quickstart-0 -- bash
root@mysql-quickstart-0:/# mysql -uroot -p${MYSQL_ROOT_PASSWORD}
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 252
Server version: 8.0.23 MySQL Community Server - GPL

Copyright (c) 2000, 2021, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

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

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| testdb             |
+--------------------+
5 rows in set (0.01 sec)

mysql> drop database testdb;
Query OK, 1 row affected (0.03 sec)

Step 1: Create a RestoreSession

apiVersion: stash.appscode.com/v1beta1
kind: RestoreSession
metadata:
  name: sample-mysql-restore
  namespace: demo
spec:
  repository:
    name: gcs-repo
  target:
    ref:
      apiVersion: appcatalog.appscode.com/v1alpha1
      kind: AppBinding
      name: mysql-quickstart
  runtimeSettings:
    container:
      securityContext:
        runAsUser: 1000610000
        runAsGroup: 1000610000
  rules:
    - snapshots: [latest]

Notice that the securityContext field is the same as we mentioned earlier in the BackupConfiguration. This RestoreSession specifies where the data will be restored. Once this is applied, a RestoreSession will be created. Once it has succeeded, the database has been successfully recovered as you can see below:

$ oc get restoresession -n demo
NAME                   REPOSITORY   PHASE       AGE
sample-mysql-restore   gcs-repo     Succeeded   41s

Now let’s check whether the database has been correctly restored:

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| testdb             |
+--------------------+
5 rows in set (0.02 sec)

mysql> use testdb;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;
+------------------+
| Tables_in_testdb |
+------------------+
| MyGuests         |
+------------------+
1 row in set (0.01 sec)

The recovery has been successful. If you faced any difficulties in the recovery process you can reach out to us through EMAIL .

Support

To speak with us, please leave a message on our website .

To receive product announcements, follow us on Twitter .

If you have found a bug with KubeDB or want to request for new features, please file an issue .

KubeDB

Stash

KubeVault

Voyager

ConfigSyncer

Guard

RESOURCES

RECENT NEWS/BLOG