quick simple mariadb backup

mymariabackup.sh

#!/bin/bash
#
# 2025-10-05: Paul Reece <paulreece42@gmail.com>, initial wite
#
# Take a backup of MariaDB
#
# Note: I wrote this in under a couple hours, without using AI... keep an eye on it
# make sure it runs, use proper monitoring, etc
#
# Requires zstd, or "zstandard" compression tool
# apt-get install zstd
# yum install zstd
# etc
#
# Verify it, make sure it's "sane"
#
# This isn't anything fancy or impressive, I just end up re-writing some version
# of this script every 6 months or so, because I encounter some edge-case where
# we can't use a more elegent/polished solution
#
# Any (working) backup is better than no backup!!!
#
# pushover-say is a script I wrote myself idk just write an alerting
# script
#
# Recommend setting this up with some sort of dead man's switch
# This can be as simple as echoing a timestamp to a static HTML
# file on a local webserver and checking it with an external service
#
# prometheus textfile editor on node-exporter that reports seconds since last
# success
#
# Nagios freshness checks with NSCA is an old school method, still works great
#
# etc
#

NOW=$(date -Isecond)
LOGFILE=/var/log/mybackup.log
MINFILES=100
MINSIZE=1229960 # bytes
MINORIGSIZE=3549856 # bytes


# from the flock manpage, if you aren't using this, you aren't even living
[ "${FLOCKER}" != "$0" ] && exec env FLOCKER="$0" flock -en "$0" "$0" "$@" || :

echo "${NOW} - backup starting" | tee -a $LOGFILE

do_mysqldump_backup () {
    # Not actually a dir in case of mysqldump but just copy and pasting code lol
    # I really like taking both logical and hotcopy backups whenever possible
    DIRPREFIX=$1
    mydir=/backups/${DIRPREFIX}/${NOW}
    # check to make sure mydir is at least X numbers long
    # this might SEEM like paranoia, but check the last line of this
    # function... rm -rf
    if [ ${#mydir} -lt 30 ]; then
        /usr/bin/pushover_say alerts-db "${HOSTNAME} MySQL backup failed, dir name too short"
        echo "$(date -Isec) - ${HOSTNAME} MySQL backup failed, dir name too short" | tee -a $LOGFILE
        exit 2
    fi
    mysqldump -A --master-data=2 --single-transaction | zstd > ${mydir}.sql.zst
    retval=$?
    mySQLdumpSize=$(stat -c "%s" ${mydir}.sql.zst)
    if [[ ${retval} -ne 0 || ${mySQLdumpSize} -lt ${MINSIZE} ]]; then
        /usr/bin/pushover_say alerts-db "${HOSTNAME} MySQL backup failed on tar verify"
        echo "`date -Isec` - ${HOSTNAME} MySQL backup failed on SQLdump verify" | tee -a $LOGFILE
        echo -n "backup ${mydir}.sql.zst FAILED VERIFY, ${retval} return code on mysqldump, " | tee -a $LOGFILE
        echo "${mySQLdumpSize} compressed (min ${MINSIZE})" | tee -a $LOGFILE
        exit 2
    fi

    echo -n "$(date -Isec) - backup ${mydir}.sql.zst taken OK  " | tee -a $LOGFILE
    echo "${mySQLdumpSize} compressed (min ${MINSIZE})" | tee -a $LOGFILE
}

do_mymaria_hotcopy_backup () {
    # take a backup to the specified dir
    DIRPREFIX=$1
    mydir=/backups/${DIRPREFIX}/${NOW}
    mkdir -p ${mydir}
    # check to make sure mydir is at least X numbers long
    # this might SEEM like paranoia, but check the last line of this
    # function... rm -rf
    if [ ${#mydir} -lt 30 ]; then
        /usr/bin/pushover_say alerts-db "${HOSTNAME} MySQL backup failed, dir name too short"
        echo "$(date -Isec) - ${HOSTNAME} MySQL backup failed, dir name too short" | tee -a $LOGFILE
        exit 2
    fi

    mariadb-backup --defaults-extra-file=/etc/my.client-creds-backup.cnf --backup --target-dir=${mydir}

    mysize=$(du -bxs $mydir | awk '{print $1}')
    if [ $mysize -lt $MINORIGSIZE ] ; then
        /usr/bin/pushover_say alerts-db "${HOSTNAME} MySQL backup failed on mariabackup"
        echo "$(date -Isec) - ${HOSTNAME} MySQL backup failed on mariabackup, nonzero error code" | tee -a $LOGFILE
        exit 2
        # this exit is important, if we don't CRASH here, it will go on to clean
        # up the backups, potentially deleting everything recent and leaving
        # you with only BROKEN backups D:
    fi

    tar --zstd -cf ${mydir}.tar.zst ${mydir}
    # zstd or "zstandard" is incredible, life-changing compression, if you haven't used it yet
    if [ $? -ne 0 ]; then
        /usr/bin/pushover_say alerts-db "${HOSTNAME} MySQL backup failed on tar"
        echo "$(date -I sec) - ${HOSTNAME} MySQL backup failed on tar, nonzero exit code on tar" | tee -a $LOGFILE
        exit 2
    fi
    myTarSize=$(stat -c "%s" ${mydir}.tar.zst)
    myTarFileCount=$(tar -tvf ${mydir}.tar.zst  | wc -l)
    retval=$?
    # tar can sometimes bomb out on list files like this, that means we've got
    # issues for sure, so we check retval = 0, size > about 3MB, and more than 100 files
    if [[ ${retval} -ne 0 || ${myTarSize} -lt ${MINSIZE} || ${myTarFileCount} -lt ${MINFILES} ]]; then
        /usr/bin/pushover_say alerts-db "${HOSTNAME} MySQL backup failed on tar verify"
        echo "`date -Isec` - ${HOSTNAME} MySQL backup failed on tar verify" | tee -a $LOGFILE
        echo -n "backup ${mydir}.tar.zst FAILED VERIFY, ${retval} return code on tar tvf, " | tee -a $LOGFILE
        echo -n  "${mysize} bytes original (min ${MINORIGSIZE}), ${myTarSize} compressed (min ${MINSIZE}), " | tee -a $LOGFILE
        echo  "${myTarFileCount} files in archive (min ${MINFILES})" | tee -a $LOGFILE
        exit 2
    fi
    # if we haven't died yet, delete the folder
    rm -rf $mydir
    echo -n "$(date -Isec) - backup ${mydir}.tar.zst taken OK, ${mysize} bytes original (min ${MINORIGSIZE}), " | tee -a $LOGFILE
    echo "${myTarSize} compressed (min ${MINSIZE}), ${myTarFileCount} files in archive (min ${MINFILES})" | tee -a $LOGFILE

}

# useful bash-fu for Linux backups I came up with a decade ago
# and have been using ever since, some quick retention
#
# keep daily backups for 2 weeks
# keep weekly backups for 3 months
# keep monthly backups for 2 years
# keep yearly backups for 7 years
#
# I'm going to index off the first of the {week,month,year}
# these things are usually done for 98% for legal retention
# purposes. But if you wanted to get clever, it might be more
# useful to index off the LAST day of the period...
#
# https://stackoverflow.com/questions/12381501/how-to-use-bash-to-get-the-last-day-of-each-month-for-the-current-year-without-u
# Some stack overflow to get ya started on that
#
# for m in {1..12}; do
#  date -d "$m/1 + 1 month - 1 day" "+%b - %d days";
# done

DOY=`date +%j` # day of year
DOM=`date +%d` # day of month

if [ $DOY -eq 1 ]; then
    # first day of year
    do_mymaria_hotcopy_backup yearly
    do_mysqldump_backup yearly
    find /backups/yearly/ -mindepth 1 -maxdepth 1 -mtime +2555 -delete
elif [ $DOM -eq 1 ]; then
    # first day of month
    do_mymaria_hotcopy_backup monthly
    do_mysqldump_backup monthly
    find /backups/yearly/ -mindepth 1 -maxdepth 1 -mtime +730 -delete
elif [ $((DOM % 7)) -eq 0 ]; then
    # day of month modulus 7 (divide 7 and take remainder)
    # equals 0. So every 7th day
    do_mymaria_hotcopy_backup weekly
    do_mysqldump_backup weekly
    find /backups/weekly/ -mindepth 1 -maxdepth 1 -mtime +90 -delete
else
    # just a normal boring everday :D
    do_mymaria_hotcopy_backup daily
    do_mysqldump_backup daily
    find /backups/daily/ -mindepth 1 -maxdepth 1 -mtime +14 -delete
fi

#
# https://choosealicense.com/licenses/mit/
#
# MIT License
#
# Copyright (c) 2025 Paul Reece
#
# Permission is hereby granted, free of charge, to any person obtaining a copy
# of this software and associated documentation files (the "Software"), to deal
# in the Software without restriction, including without limitation the rights
# to use, copy, modify, merge, publish, distribute, sublicense, and/or sell
# copies of the Software, and to permit persons to whom the Software is
# furnished to do so, subject to the following conditions:
#
# The above copyright notice and this permission notice shall be included in all
# copies or substantial portions of the Software.
#
# THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
# IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
# FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
# AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
# LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
# OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE
# SOFTWARE.