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.

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=policy
DB_PWD=*******
HOST=host.docker.internal
PORT=3355

# cleanup old backups
removeOldBackups()
{
for i in `ls $BACKUP_ARCHIVEDIR/$FILENAME*.tar.gz | grep -v $ARCHIVE`
do
        rm $i >/dev/null 2>/dev/null
done
}

cd /

echo "Backup Started"

# call backup utility
{
"$MARIADB_BACKUP_EXE_DIR"/mysqldump --all-databases --add-drop-database --compact --routines --host=$HOST --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
   exit 1
else
   rm $BACKUP_WORKDIR/$DMPFILE $BACKUP_WORKDIR/$ERRFILE
   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

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, 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