Page Comparison

...

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

Code Block

language	javayml
title	Dockerfile

FROM mariadb:latest

RUN apkapt update
COPY dbmysqldump_backup.sh .

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

Shell script

Code Block

language	javatext
title	db_backup.sh

#!/bin/sh

FILENAME=$(basename $0 .sh)
BACKUP_TS=$(date +%F-%T)
ARCHIVE=${FILENAME}_${BACKUP_TS}.tar.gz
LOGERRFILE=${FILENAME}.log
LOG_DIR=/mnt/c/mariadb/logs_${BACKUP_TS}.err
DMPFILE=${FILENAME}_${BACKUP_TS}.dmp
BACKUP_WORKDIR=/mnt/c/mariadb/backuptmp
BACKUP_ARCHIVEDIR=/mnt/c/mariadb/backup_archivestmp
MARIADB_BACKUP_EXE_DIR="/usr/bin"
HOST=localhost
PORT=3355
DB_USER=p####
DB_PWD=******
OK_MSG="completed OK!"

cd /

# if the backup directory doesn't exist, create it
if [ ! -d $BACKUP_WORKDIR ]; then
         mkdir -p $BACKUP_WORKDIR
fi

# if the archive directory doesn't exist, create it
if [ ! -d $BACKUP_ARCHIVEDIR ]; then
         mkdir -p $BACKUP_ARCHIVEDIR
fi

# if the log directory doesn't exist, create it
if [ ! -d $LOG_DIR ]; then
         mkdir -p $LOG_DIR
fi

echo "Backup Started"
echo "Backing up to $BACKUP_WORKDIR"

# check if directory is empty, if not delete it's contents
if [ "$(ls -A $BACKUP_WORKDIR)" ]; then
      rm -rf $BACKUP_WORKDIR/*
fi

# redirect output to log file
{
 "$MARIADB_BACKUP_EXE_DIR"/mariabackup --backup --target-dir=$BACKUP_WORKDIR --user=$DB_USER --password=$DB_PWD --host=$HOST --port=$
PORT
} >$LOG_DIR/$LOG 2>&1
echo >&2 "Backup Complete"

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

# remove he backup directory
 rm -rf $BACKUP_WORKDIR/* >/dev/null 2>/dev/null

# check the log for the "completed OK!" message
grep -q "$OK_MSG" $LOG_DIR/$LOG
if [ $? -eq 0 ]; then

# 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

Code Block

language	yml
title	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

Code Block

language	yml
title	values.yaml

# Default values for mariadb-backup.
# This is a YAML-formatted file.
# Declare variables to be passed into your templates.
image:
  tag: "ktimoney/mysqldump_backup"
backoffLimit: 4
schedule: "*/5 * * * *"
volumes:
  name: backup-pv
  storageClassName:
    path: /run/desktop/mnt/host/c/mariadb/backup
    accessModes: ReadWriteOnce
  capacity:
    storage: 50Mi
  persistentVolumeReclaimPolicy: Retain
  nodeAffinity:
    nodeSelectorTerms: docker-desktop
data:
  backup_retention_interval: mmin # mmin for minutes, mtime for days
  backup_retention_period: "120"
  db_host: host.docker.internal
  db_port: "3306"
  db_user: YmFja3Vw
  db_pwd: YmFja3Vw

Code Block

language	yml
title	backupPersistentVolume.yaml

apiVersion: v1
kind: PersistentVolume
metadata:
  name: {{ .Values.volumes.name }}
spec:
  capacity:
    storage: {{ .Values.volumes.capacity.storage }}
  volumeMode: Filesystem
  accessModes:
    - {{ .Values.volumes.storageClassName.accessModes }}
  persistentVolumeReclaimPolicy: Retain
  storageClassName: local-storage
  local:
    path: {{ .Values.volumes.storageClassName.path }}
  nodeAffinity:
    required:
      nodeSelectorTerms:
        - matchExpressions:
            - key: kubernetes.io/hostname
              operator: In
              values:
                - {{ .Values.volumes.nodeAffinity.nodeSelectorTerms }}

Code Block

language	yml
title	backupPersistentClaim.yaml

apiVersion: v1
kind: PersistentVolumeClaim
metadata:
  name: backup-claim
spec:
  storageClassName: local-storage
  accessModes:
    - {{ .Values.volumes.storageClassName.accessModes }}
  resources:
    requests:
      storage: {{ .Values.volumes.capacity.storage }}

Code Block

language	yml
title	backupConfigMap.yaml

apiVersion: v1
kind: ConfigMap
metadata:
  name: backup-config
  namespace: default
data:
  backup_retention_interval: {{ .Values.data.backup_retention_interval }}
  backup_retention_period: {{ .Values.data.backup_retention_period | quote }}
  db_host: {{ .Values.data.db_host }}
  db_port: {{ .Values.data.db_port | quote }}

Code Block

language	yml
title	backupSecret.yaml

apiVersion: v1
kind: Secret
metadata:
  name: backup-secret
type: Opaque
data:
  db_user: {{ .Values.data.db_user }}
  db_pwd: {{ .Values.data.db_pwd }}

Code Block

language	yml
title	backupCronjob.yaml

apiVersion: batch/v1beta1
kind: CronJob
metadata:
  name: backup-cron
spec:
  schedule: {{ .Values.schedule | quote }}
  concurrencyPolicy: Forbid
  jobTemplate:
    spec:
      template:
        spec:
          volumes:
            - name: backup-volume
              persistentVolumeClaim:
                claimName: backup-claim
          containers:
          - name: mysqldump-backup
            image: {{ .Values.image.tag }}
            volumeMounts:
            - mountPath: /tmp
              name: backup-volume
            env:
            - name: DB_HOST
              valueFrom:
                configMapKeyRef:
                  name: backup-config
                  key: db_host
            - name: DB_PORT
              valueFrom:
                configMapKeyRef:
                  name: backup-config
                  key: db_port
            - name: BACKUP_RETENTION_INTERVAL
              valueFrom:
                configMapKeyRef:
                  name: backup-config
                  key: backup_retention_interval
            - name: BACKUP_RETENTION_PERIOD
              valueFrom:
                configMapKeyRef:
                  name: backup-config
                  key: backup_retention_period
            - name: DB_USER
              valueFrom:
                secretKeyRef:
                  name: backup-secret
                  key: db_user
            - name: DB_PWD
              valueFrom:
                secretKeyRef:
                  name: backup-secret
                  key: db_pwd
        exit 0 elserestartPolicy: Never
      backoffLimit: exit 1
fi{{ .Values.backoffLimit }}

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

...

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 redo logs.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

Versions Compared

Old Version 14

New Version Current

Key

Dockerfile

Shell script

Links

Testing

Notes