Simple MySQL backup script

September 19th, 2020 at 01:02

I made a simple bash backup script for the newer MySQL database servers at Cogneato. We moved to Ubuntu servers for our newer sites, which doesn’t have holland in its default repos like CentOS. Ubuntu’s automysqlbackup seemed less flexible and transparent. Wanting something with normal sql dumps, instead of what I use for my own server, I decided to make a quick shell script.

The script runs on a cron, and basically:

Removes previous latest backup (because of hard links)
Queries for list of databases
Loops through databases and dumps to a gzipped file in the latest backup directory
Does a hard link copy with date named folder for hourly, daily, weekly, monthly, and yearly periods, if applicable

We do the backup directly on the server, and use snapshots to back them up remotely. We have created a backup database user with only SELECT privileges on all tables to use for the dumps, something like:

CREATE USER 'backup'@'127.0.0.1' IDENTIFIED BY 'p455w0rd';
GRANT SELECT ON *.* to 'backup'@'127.0.0.1';
FLUSH PRIVILEGES;

We are using the user dotfile .my.cnf to store the credentials, which can look like:

[clientbackup]
user=backup
password=p455w0rd

with a chmod 600 to limit prying eyes.

If running as root, we can save our script to /root/bin/db-backup and then set up our cron job with crontab -e, to run every, say, 8 hours:

5 */8 * * * test -x /root/bin/db-backup && /root/bin/db-backup

We might create our backup folder in /var/backups/db:

mkdir /var/backups/db && chmod 750 /var/backups/db

Our backup script looks something like:

#!/bin/bash
backupPath=/var/backups/db
keepHourly=3
keepDaily=6
keepWeekly=3
keepMonthly=4
keepYearly=5

#--prep folder
latestDate=`date +%y%m%d-%H%M`
latestPath=${backupPath}/_latest
mkdir -p ${latestPath}

#--remove previous latest so we don't overwrite hard links
rm -rf ${latestPath}/*

#--grab db names
dbNames=`echo 'SHOW DATABASES' | mysql --defaults group-suffix=backup`
readarray -t dbs <<<"$dbNames"
for db in "${dbNames[@]}"
do
    case "$db" in
        Database|information_schema|mysql|performance|_schema|sys)
            #--skip internal db's
        ;;
        *)
            #--dump and gzip
            mysqldump --defaults-group-suffix=backup --add-drop-table --allow-keywords \
                --create-options --extended-insert --hex-blob --lock-tables=false --no-create-db \
                --no-tablespaces --quick --set-charset --skip-add-locks --skip-comments \
                --skip-opt ${db} | tee /dev/stdout | gzip -c > ${latestPath}/${db}.sql.gz
        ;;
    esac
done

#--rotate
function rotate {
    path="${backupPath}${rotateSubPath}"
    mkdir -p ${path}
    cd ${path}
    #--hard link copy of latest into place
    cp -al ${latestPath} ${latestDate}
    #--remove old backups by count
    ls -t . | sed -e 1,"${rotateKeep}"d | xargs -d '\n' rm -rf > /dev/null 2>&1
}
if (( $keepHourly > 0 )); then
    rotateSubPath="hourly"
    rotateKeep="${keepHourly}"
    rotate
fi
if [ `find ${backupPath}/hourly -maxdepth 1 -type d -name "$(date +'%y%m%d-')*" | wc -l` -eq 1 ]; then
    if (( $keepDaily > 0 )); then
        rotateSubPath="daily"
        rotateKeep="${keepDaily}"
        rotate
    fi
    if (( $keepWeekly > 0 )) && [ `date +%u` -eq 1 ]; then
        rotateSubPath="weekly"
        rotateKeep="${keepWeekly}"
        rotate
    fi
    if (( $keepMonthly > 0 )) && [ `date +%d` -eq 1 ]; then
        rotateSubPath="monthly"
        rotateKeep="${keepMonthly}"
        rotate
    fi
    if (( $keepYearly > 0 )) && [ `date +%j` -eq 1 ]; then
        rotateSubPath="yearly"
        rotateKeep="${keepYearly}"
        rotate
    fi
fi

That seems to be working for us.

Related Posts