Database backup and restore

POLICY-3000 - Getting issue details... STATUS

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.

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

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
LOG_DIR=/tmp
BACKUP_WORKDIR=/tmp
BACKUP_ARCHIVEDIR=/tmp
MARIADB_BACKUP_EXE_DIR="/usr/bin"
DB_USER=backup
DB_PWD=******
HOST=host.docker.internal
PORT=3355

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

cd /

echo "Backup Started"

# call backup utility
{
"$MARIADB_BACKUP_EXE_DIR"/mysqldump --all-databases --host=$HOST --port=$PORT \
                                    --user=$DB_USER --password=$DB_PWD \
                                    --log-error=/tmp/$ERRFILE  > /tmp/$DMPFILE
}
echo >&2 "Backup Complete"

# 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
   exit 1
else
   rm $BACKUP_WORKDIR/$DMPFILE \
      $BACKUP_WORKDIR/$ERRFILE >/dev/null 2>/dev/null
   removeOldBackups
   exit 0
fi

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 the following 3 files:

backupPersistentVolume.yaml

apiVersion: v1
kind: PersistentVolume
metadata:
  name: local-pv
spec:
  capacity:
    storage: 50Mi
  volumeMode: Filesystem
  accessModes:
    - ReadWriteMany
  persistentVolumeReclaimPolicy: Retain
  storageClassName: local-storage
  local:
    path: /run/desktop/mnt/host/c/mariadb/backup
  nodeAffinity:
    required:
      nodeSelectorTerms:
        - matchExpressions:
            - key: kubernetes.io/hostname
              operator: In
              values:
                - docker-desktop

kubectl create -f backupPersistentVolume.yaml

backupPersistentClaim.yaml

apiVersion: v1
kind: PersistentVolumeClaim
metadata:
  name: backup-claim
spec:
  storageClassName: local-storage
  accessModes:
    - ReadWriteMany
  resources:
    requests:
      storage: 50Mi

kubectl create -f backupPersistentClaim.yaml

backupCronjob.yaml

apiVersion: batch/v1beta1
kind: CronJob
metadata:
  name: batch-every-10-minutes
spec:
  schedule: "*/10 * * * *"
  concurrencyPolicy: Forbid
  jobTemplate:
    spec:
      template:
        spec:
          volumes:
            - name: backup-volume
              persistentVolumeClaim:
                claimName: backup-claim
          containers:
          - name: mysqldump-backup
            image: ktimoney/mysqldump_backup
            volumeMounts:
            - mountPath: "/tmp"
              name: backup-volume
          restartPolicy: Never
      backoffLimit: 3

kubectl create -f backupCronjob.yaml

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

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.dumpIt 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