Backup and Restore MariaDB Galera Deployments on Kubernetes

Introduction

MariaDB Galera Cluster makes it easy to create a high-availability database cluster with synchronous replication while still retaining all the familiar MariaDB clients and tools. VMware Tanzu Application Catalog (Tanzu Application Catalog) offers a MariaDB Galera Helm chart that makes it quick and easy to deploy such a cluster on Kubernetes. This Helm chart is compliant with current best practices and can also be easily upgraded to ensure that you always have the latest fixes and security updates.

Once you have a MariaDB Galera Cluster deployed, you need to start thinking about ongoing maintenance and disaster recovery, and begin putting a data backup/restore strategy in place. This backup/restore strategy is needed for many operational scenarios, including disaster recovery planning, off-site data analysis or application load testing.

This guide walks you through two different approaches you can follow when backing up and restoring MariaDB Galera Cluster deployments on Kubernetes:

Back up the data from the source deployment and restore it in a new deployment using MariaDB's built-in backup/restore tools.
Back up the persistent volumes from the source deployment and attach them to a new deployment using Velero, a Kubernetes backup/restore tool.

Assumptions and prerequisites

This guide makes the following assumptions:

You have two separate Kubernetes clusters - a source cluster and a destination cluster - with kubectl and Helm v3 installed. This guide uses Google Kubernetes Engine (GKE) clusters but you can also use any other Kubernetes provider. Learn how to install kubectl and Helm v3.x.
You have configured Helm to use the Tanzu Application Catalog chart repository following the instructions for Tanzu Application Catalog or the instructions for VMware Tanzu Application Catalog for Tanzu Advanced.

You have previously deployed the Tanzu Application Catalog MariaDB Galera Helm chart on the source cluster and added some data to it. Example command sequences to perform these tasks are shown below, where the PASSWORD and REPL-PASSWORD placeholders refer to the database administrator and replication user passwords respectively. Replace the REPOSITORY and REGISTRY placeholders with references to your Tanzu Application Catalog chart repository and container registry respectively.

helm install galera REPOSITORY/mariadb-galera \
  --namespace default \
  --set rootUser.password=PASSWORD \
  --set galera.mariabackup.password=REPL-PASSWORD
kubectl run galera-mariadb-galera-client --rm --tty -i --restart='Never' --namespace default --image REGISTRY/mariadb-galera:10.4.12-debian-10-r78 --command -- mysql -h galera-mariadb-galera -P 3306 -uroot -pPASSWORD
CREATE DATABASE mydb;
USE mydb;
CREATE TABLE accounts (name VARCHAR(255) NOT NULL, total INT NOT NULL);
INSERT INTO accounts VALUES ('user1', '647'), ('user2', '573');
exit

Method 1: Backup and restore data using MariaDB's built-in tools

The method described below involves using MariaDB's mysqldump tool to create a point-in-time backup of the data in the source cluster, and then using the mysql tool to restore this data on the destination cluster.

Step 1: Backup data with mysqldump

The first step is to back up the data in the MariaDB Galera source cluster. Follow these steps:

Obtain the MariaDB Galera Cluster's administrator password:

export PASSWORD=$(kubectl get secret --namespace default galera-mariadb-galera -o jsonpath="{.data.mariadb-root-password}" | base64 --decode)

Forward the MariaDB Galera Cluster service port and place the process in the background:
```
kubectl port-forward --namespace default svc/galera-mariadb-galera 3306:3306 &
```
Create a directory for the backup files and make it the current working directory:
```
mkdir mybackup
cd mybackup
```
Back up the contents of all the databases to the current directory using the mysqldump tool. If this tool is not installed on your system, use Tanzu Application Catalog's MariaDB Galera Docker image to perform the backup, as shown below. Replace the PASSWORD placeholder with the database administrator password and the REGISTRY placeholder with a reference to your Tanzu Application Catalog container registry.
```
docker run --rm --name mysqldump -v $(pwd):/app --net="host" REGISTRY/mariadb-galera:latest mysqldump -h 127.0.0.1 -u root -pPASSWORD -A > backup.sql
```
Here, the --net parameter lets the Docker container use the host's network stack and thereby gain access to the forwarded port. The mysqldump command connects to the forwarded MariaDB Galera service and creates an SQL backup file in the /app directory, which is mapped to the current directory (mybackup/) on the Docker host. Finally, the --rm parameter deletes the container after the mysqldump command completes execution.
Stop the service port forwarding by terminating the corresponding background process.

At the end of this step, the backup directory should contain a file with the data from your running MariaDB Galera Cluster deployment.

Step 2: Restore data with mysql

The next step is to create an empty MariaDB Galera Cluster deployment on the destination cluster and restore the data into it. You can also use the procedure shown below with a new MariaDB Galera Cluster deployment in a separate namespace in the same cluster.

Create a new MariaDB Galera Cluster deployment. Replace the PASSWORD, REPL-PASSWORD and REPOSITORY placeholders with the database administrator password, replication user password and a reference to your Tanzu Application Catalog chart repository.
```
helm install galera-new REPOSITORY/mariadb-galera \
    --set rootUser.password=PASSWORD \
    --set galera.mariabackup.password=REPL-PASSWORD
```
NOTE: If using Tanzu Application Catalog for Tanzu Advanced, install the chart following the steps described in the VMware Tanzu Application Catalog for Tanzu Advanced documentation instead.
Forward the MariaDB Galera Cluster service port for the new deployment and place the process in the background:
```
kubectl port-forward --namespace default svc/galera-new-mariadb-galera 3306:3306 &
```
Create and start a Tanzu Application Catalog MariaDB Galera container image. Mount the directory containing the backup file as a volume in this container and use the mysql client tool in the container image to import the backup into the new cluster, as shown below. Replace the PASSWORD placeholder with the database administrator password and the REGISTRY placeholder with a reference to your Tanzu Application Catalog container registry.
```
cd mybackup
docker create -t --rm --name galera-client -v $(pwd):/app --net="host" REGISTRY/mariadb-galera:latest bash
docker start galera-client
docker exec -i galera-client mysql -h 127.0.0.1 -uroot -pPASSWORD < backup.sql
```
Here, the -v parameter mounts the current directory (containing the backup file) to the container's /app path. Then, the mysql client tool is used to connect to the new MariaDB Galera Cluster service and restore the data from the original deployment. The --rm parameter destroys the container on exit.
Stop the service port forwarding by terminating the background process.
Connect to the new deployment and confirm that your data has been successfully restored. Replace the PASSWORD placeholder with the database administrator password and the REGISTRY placeholder with a reference to your Tanzu Application Catalog container registry.
```
kubectl run galera-new-mariadb-galera-client --rm --tty -i --restart='Never' --namespace default --image REGISTRY/mariadb-galera:10.4.12-debian-10-r78 --command -- mysql -h galera-new-mariadb-galera -P 3306 -uroot -pPASSWORD -e "SELECT * FROM mydb.accounts"
```
Here is an example of what you should see:

Method 2: Back up and restore persistent data volumes

This method involves copying the persistent data volume for the primary MariaDB Galera Cluster node and reusing it in a new deployment with Velero, an open source Kubernetes backup/restore tool. This method is only suitable when:

The cloud provider is supported by Velero.
Both clusters are on the same cloud provider, because Velero does not support the migration of persistent volumes across cloud providers.
The restored deployment on the destination cluster will have the same name, namespace and credentials as the original deployment on the source cluster.

NOTE
This approach requires scaling the cluster down to a single node to perform the backup.

NOTE
For persistent volume migration across cloud providers with Velero, you have the option of using Velero's Restic integration. This integration is currently beta quality and is not covered in this guide.

Step 1: Install Velero on the source cluster

Velero is an open source tool that makes it easy to backup and restore Kubernetes resources. It can be used to back up an entire cluster or specific resources such as persistent volumes.

Modify your context to reflect the source cluster (if not already done).
Follow the Velero plugin setup instructions for your cloud provider. For example, if you are using Google Cloud Platform (as this guide does), follow the GCP plugin setup instructions to create a service account and storage bucket and obtain a credentials file.
Then, install Velero on the source cluster by executing the command below, remembering to replace the BUCKET-NAME placeholder with the name of your storage bucket and the SECRET-FILENAME placeholder with the path to your credentials file:
```
velero install --provider gcp --plugins velero/velero-plugin-for-gcp:v1.1.0 --bucket BUCKET-NAME --secret-file SECRET-FILENAME
```
You should see output similar to the screenshot below as Velero is installed:
Confirm that the Velero deployment is successful by checking for a running pod using the command below:
```
kubectl get pods -n velero
```

Step 2: Back up the MariaDB Galera Cluster deployment on the source cluster

Next, back up the main persistent volume using Velero.

Scale down the cluster to only a single node:
```
kubectl scale statefulset --replicas=1 galera-mariadb-galera
```
NOTE: In a multi-node cluster, Galera's grastate.dat file typically has the safe_to_bootstrap value set to 0. When restoring node PVCs and attempting to bootstrap a new Galera cluster with any one of them, Galera will fail to bootstrap due to this value. One approach to this is to examine each PVC to identify the one with the highest transaction seqno and manually set the safe_to_bootstrap value to 1 for that PVC. This is tedious when dealing with a large number of PVCs. The alternative approach discussed in this guide involves scaling the cluster down to one node (this sets the safe_to_bootstrap value to 1 on that node) and then using the corresponding PVC to bootstrap the new cluster without needing to manually inspect/edit Galera's grastate.dat files.
Obtain the name of the running pod. Make a note of the node number which is suffixed to the name. For example, if the running pod is galera-mariadb-galera-0, the node number is 0.
```
kubectl get pods | grep galera
```

Create a backup of the persistent volumes on the source cluster:

velero backup create galera-backup --include-resources=pvc,pv --selector app.kubernetes.io/instance=galera

Execute the command below to view the contents of the backup and confirm that it contains all the required resources:
```
velero backup describe galera-backup  --details
```

To avoid the backup data being overwritten, switch the bucket to read-only access:

kubectl patch backupstoragelocation default -n velero --type merge --patch '{"spec":{"accessMode":"ReadOnly"}}'

If required, scale the cluster back to its original size:

kubectl scale statefulset --replicas=3 galera-mariadb-galera

Step 3: Restore the MariaDB Galera Cluster deployment on the destination cluster

You can now restore the persistent volumes and integrate them with a new MariaDB Galera Cluster deployment on the destination cluster.

Modify your context to reflect the destination cluster.
Install Velero on the destination cluster as described in Step 1. Remember to use the same values for the BUCKET-NAME and SECRET-FILENAME placeholders as you did originally, so that Velero is able to access the previously-saved backups.
```
velero install --provider gcp --plugins velero/velero-plugin-for-gcp:v1.1.0 --bucket BUCKET-NAME --secret-file SECRET-FILENAME
```
Confirm that the Velero deployment is successful by checking for a running pod using the command below:
```
kubectl get pods -n velero
```
Restore the persistent volumes in the same namespace as the source cluster using Velero.
```
velero restore create --from-backup galera-backup
```
Confirm that the persistent volumes have been restored:
```
kubectl get pvc --namespace default
```
Delete all the restored persistent volumes except the volume with number corresponding to the running node number seen in Step 2. For example, if the node number noted in Step 2 is 0, delete all the persistent volumes except the volume named data-galera-mariadb-galera-0.
```
kubectl delete pvc --namespace default PVC-NAME-1 PVC-NAME-2
```
Create a new MariaDB Galera Cluster deployment. Use the same name and namespace as the original deployment. Replace the PASSWORD and REPL-PASSWORD placeholders with the same database administrator and replication user passwords used in the original deployment and the REPOSITORY placeholder with a reference to your Tanzu Application Catalog chart repository.
```
helm install galera REPOSITORY/mariadb-galera \
  --namespace default \
  --set rootUser.password=PASSWORD \
  --set galera.mariabackup.password=REPL-PASSWORD
```
NOTE: It is important to create the new deployment on the destination cluster using the same namespace, deployment name and credentials as the original deployment on the source cluster.

This will create a new deployment that bootstraps from the original node volume (and hence the original data).
Connect to the new deployment and confirm that your data has been successfully restored. Replace the PASSWORD placeholder with the database administrator password and the REGISTRY placeholder with a reference to your Tanzu Application Catalog container registry.
```
kubectl run galera-mariadb-galera-client --rm --tty -i --restart='Never' --namespace default --image REGISTRY/mariadb-galera:10.4.12-debian-10-r78 --command -- mysql -h galera-mariadb-galera -P 3306 -uroot -pPASSWORD -e "SELECT * FROM mydb.accounts"
```
Here is an example of what you should see:

Useful links

MariaDB Galera Helm chart
MariaDB client applications mysqldump and mysql
Velero documentation