Backupscript for mysql

[osnet]

i wrote a small DB backup-script, maybe one finds it useful. i am triggering it via cronjob on the dockerhost. kopano.io mentioned that backups usind mysqldump and rsync are prefered over bricklevel, i am using this aswell : )

#!/bin/bash

BACKUPPATH=./db-backup/
DATETIME=$(date '+%d-%m-%Y_%H:%M:%S')

DBUSER=kopano
DBNAME=kopano
DBPASS=secret

CONTAINER=test_db_1
#files to keep in backup dir, MAX remaining plus the newest one
MAX=5

while test $# -gt 0
do
    case "$1" in
        --backup) 
            echo "Backing up Database ..."
        docker exec "$CONTAINER" /usr/bin/mysqldump -u "$DBUSER" --password="$DBPASS" --single-transaction --routines "$DBNAME" > "$BACKUPPATH"backup-"$DATETIME".sql
            ;;

        --backup-cron)
            echo "Rotating ..."
        cd $BACKUPPATH
        ls -t | sed -e '1,'"$MAX"'d' | xargs -d '\n' rm 
            echo "Backing up Database ..."
            docker exec "$CONTAINER" /usr/bin/mysqldump -u "$DBUSER" --password="$DBPASS" --single-transaction --routines "$DBNAME" > ../"$BACKUPPATH"backup-"$DATETIME".sql
            ;;

        --restore)
            if [ -n "$2" ]
            then

               read -p "Restoring Backup:$2 - Are you sure?[Y/n] " -n 1 -r
        echo 
        if [[ $REPLY =~ ^[YJ]$ ]]
        then
            # do dangerous stuff
           echo "restoring ..."
           chmod 0777 "$BACKUPPATH$2"
                   docker exec "$CONTAINER" /usr/bin/mysql -u "$DBUSER" --password="$DBPASS" "$DBNAME" < "$BACKUPPATH$2"
        else
           echo "aborted by user!"
                fi
        else
        echo "missing Backupfile!" 
            exit 0

            fi
            ;;
        --help) 
            echo "bad option $1"
            ;;
        --*) echo "bad option $1"
            ;;
    esac
    shift
done

exit 0

feel free to adapt

[mlan]

Hi @osnet

Many thanks for sharing your work here. I bet plenty of people will find it very valuable.

Since we are talking about backing up the SQL database here, I tough I could mention the method I am using. The docker hosts I use run on ZFS and have the docker-zfs-plugin installed. This allows me to use zrep to snapshot the volume holding the database and send the diff to a remote standby host every 15 minutes.

[mlan]

Hi @osnet

The accessibility of this information would certainly benefit from it being consolidated on the wiki page.