Database backup and restore

POLICY-3000: Create an ability to recover if policy database gets corruptedClosed

Recover from corruption of policy database. [Possibly a bug in mariadb: MDEV-23119]

High Level Design

A Kubernetes cron will run a docker container once every 24 hours.

The docker container will call a shell script to do the backup.

The backups will be stored remotely.

We may want to use configMaps and secrets for some of the variable specified in the shell script.

The example below works on windows 10 running docker desktop with kubernetes enabled.

mariadb is installed on ubuntu using port 3355 instead of the default.

/etc/mysql/my.cnf
[mysqld]
port=3355

Dockerfile

FROM mariadb:latest

RUN apt update
COPY mysqldump_backup.sh .

ENTRYPOINT [ "/bin/sh" ]
CMD [ "./mysqldump_backup.sh" ]

Shell script

db_backup.sh

#!/bin/sh

FILENAME=$(basename $0 .sh)
BACKUP_TS=$(date +%F-%T)
ARCHIVE=${FILENAME}_${BACKUP_TS}.tar.gz
ERRFILE=${FILENAME}_${BACKUP_TS}.err
DMPFILE=${FILENAME}_${BACKUP_TS}.dmp
BACKUP_WORKDIR=/tmp
BACKUP_ARCHIVEDIR=/tmp
MARIADB_BACKUP_EXE_DIR="/usr/bin"

# cleanup old backups
removeOldBackups()
{
# remove backups older than -mmin for minutes, -mtime for days
echo "Removing old backups ..."
for i in `find $BACKUP_ARCHIVEDIR/*.tar.gz $BACKUP_ARCHIVEDIR/*.dmp $BACKUP_ARCHIVEDIR/*.err \
          2>/dev/null -type f -${BACKUP_RETENTION_INTERVAL} +${BACKUP_RETENTION_PERIOD} -ls | grep -o '\S\+$'`
do
        echo "Removing $i"
        rm $i >/dev/null 2>/dev/null
done
}

cd /

echo "Backup Started"

# call backup utility
{
"$MARIADB_BACKUP_EXE_DIR"/mysqldump --all-databases --host=$DB_HOST --port=$DB_PORT \
                                    --user=$DB_USER --password=$DB_PWD \
                                    --log-error=/tmp/$ERRFILE  > /tmp/$DMPFILE
}

# create a tar archive from the backup directory
tar cvzf $BACKUP_ARCHIVEDIR/$ARCHIVE $BACKUP_WORKDIR/$DMPFILE \
         $BACKUP_WORKDIR/$ERRFILE >/dev/null 2>/dev/null

# check for errors
if [ -s "$BACKUP_WORKDIR/$ERRFILE" ]
then
   rm $BACKUP_ARCHIVEDIR/$ARCHIVE >/dev/null 2>/dev/null
   cat $BACKUP_WORKDIR/$ERRFILE | xargs echo -e
   echo "Backup Completed With Errors"
   exit 1
else
   rm $BACKUP_WORKDIR/$DMPFILE \
      $BACKUP_WORKDIR/$ERRFILE >/dev/null 2>/dev/null
   removeOldBackups
   echo "Backup Completed Successfully"
   exit 0
fi

* Old backups will only be removed if the backup succeeds

docker build . -t ktimoney/mysqldump_backup

docker push ktimoney/mysqldump_backup

To run create directory c:\mariadb\backup

docker run -v /mnt/c/mariadb/backup:/tmp ktimoney/mysqldump_backup

Your backup will be available in the tar.gz file in the c:\mariadb\backup directory once docker completes

The kubernetes cron can be created using helm.

The helm directory will look like this:

ls mariadb-backup/
Chart.yaml charts templates values.yaml

ls mariadb-backup/templates/
backupCronjob.yaml backupPersistentVolume.yaml
backupConfigMap.yaml backupPersistentClaim.yaml backupSecret.yaml

Chart.yaml

apiVersion: v2
name: mariadb-backup
description: A Helm chart for Kubernetes

# A chart can be either an 'application' or a 'library' chart.
#
# Application charts are a collection of templates that can be packaged into versioned archives
# to be deployed.
#
# Library charts provide useful utilities or functions for the chart developer. They're included as
# a dependency of application charts to inject those utilities and functions into the rendering
# pipeline. Library charts do not define any templates and therefore cannot be deployed.
type: application

# This is the chart version. This version number should be incremented each time you make changes
# to the chart and its templates, including the app version.
# Versions are expected to follow Semantic Versioning (https://semver.org/)
version: 0.1.0

# This is the version number of the application being deployed. This version number should be
# incremented each time you make changes to the application. Versions are not expected to
# follow Semantic Versioning. They should reflect the version the application is using.
# It is recommended to use it with quotes.
appVersion: "1.16.0"

echo -n '<string>' | base64 to convert your db_user and db_pwd to base64 encoding

values.yaml

backupPersistentVolume.yaml

backupPersistentClaim.yaml

backupConfigMap.yaml

backupSecret.yaml

backupCronjob.yaml

Run helm install mariadb-backup-t1 ./mariadb-backup to install

Run helm list to get the name and chart

Run helm upgrade mariadb-backup-t1 mariadb-backup to update

Run helm uninstall mariadb-backup-t1 to remove

* backoffLimit sets the number of times the cron will retry the job before it stops

This will back up the local copy of mariadb every 10 minutes to the c:\mariadb\backup folder.

You can restore your database with a command like the following:

sudo mysql -u root -p < ../../mariadb/tmp/mysqldump_backup_2021-05-06-12_40_07.dmp

Links

Backing Up and Restoring Databases

Openstack MariaDB database backup and restore

mariabackup is an open source tool for doing back ups on MariaDB.

Incremental Backup and Restore with Mariabackup

Back up databases using Kubernetes CronJobs

Replication as a Backup Solution

Testing

Backup Testing

Notes

We may need to create a user specifically for doing backups:

create user backup identified by '******';
GRANT RELOAD, PROCESS, SELECT, LOCK TABLES, REPLICATION CLIENT, REPLICATION SLAVE ON *.* TO 'backup'@'%';

mariabackup isn't designed to work remotely. When testing it got stuck in a loop when trying to read the logs.

mysqldump: This can be used to backup mariadb running in a docker container :

docker exec <mariadb_container_name> mysqldump [--user <db username>] [--password= <db password>] <db name> > /<backup path>/db.dump
It can also be run inside a container:

mysqldump --all-databases --add-drop-database --compact --routines --host=<hostname> --port=<port> --user=<db user> --password=<db password> --log-error=/tmp/<filename>.err > /tmp/<dmp file name>.dmp