Skip to content

Instantly share code, notes, and snippets.

@wmcmurray
Created October 9, 2021 15:15
Show Gist options
  • Save wmcmurray/cb1ad25f2d281cbe361d6c69f520a3a1 to your computer and use it in GitHub Desktop.
Save wmcmurray/cb1ad25f2d281cbe361d6c69f520a3a1 to your computer and use it in GitHub Desktop.
A simple bash script to create Daily, Monthly and Yearly MySQL database backups
#!/usr/bin/env bash
# This script creates daily + monthly + yearly mysql database backups locally (on the same server)
# It DOESN'T notify you by email if an error happens, and DOESN'T store backups on a cloud service !
# It DOES save a log file with the history of errors/success tho :)
# --- Edit those settings !
mysqlDbUser='root'
mysqlDbName='database_name_here'
backupDirectory='/path/to/backups/directory'
# ---
dirDaily=$backupDirectory/daily
dirMonthly=$backupDirectory/monthly
dirYearly=$backupDirectory/yearly
filenameDaily=backup-$(date +%Y-%m-%d)
filenameMonthly=backup-$(date +%Y-%m)
filenameYearly=backup-$(date +%Y)
sqlfile=$dirDaily/$filenameDaily.sql
zipfile=$dirDaily/$filenameDaily.zip
logfile=$backupDirectory/history.log
# Make sure directories exists
mkdir -p $dirDaily
mkdir -p $dirMonthly
mkdir -p $dirYearly
# Create daily backup
mysqldump -u $mysqlDbUser $mysqlDbName > $sqlfile
errorDuringBackup=""
if [ $? == 0 ]; then
# Compress daily backup
zip -q -j $zipfile $sqlfile
if [ $? == 0 ]; then
# Update monthly/yearly backups
cp $zipfile $dirMonthly/$filenameMonthly.zip
cp $zipfile $dirYearly/$filenameYearly.zip
else
errorDuringBackup='failed to compress backup'
fi
# Remove non-compresed file
rm $sqlfile
else
errorDuringBackup='mysqldump returned non-zero code'
fi
# keep 1 backup per day, for the last 30 days
find $dirDaily -type f -name "*\.zip" -mtime +30 -delete
# keep 1 backup per month, for the last 12 months (360 = 30 x 12)
find $dirMonthly -type f -name "*\.zip" -mtime +360 -delete
# keep 1 backup per year, for the last 10 years (3600 = 30 x 12 x 10)
find $dirYearly -type f -name "*\.zip" -mtime +3600 -delete
# Output result
finalOutput=''
if ! [ -z "$errorDuringBackup" ] ; then
finalOutput="backup error : $errorDuringBackup"
else
finalOutput="backup success"
fi
echo "$(date +%Y-%m-%d_%H:%M:%S) - $finalOutput" >> $logfile
echo $finalOutput
@wmcmurray
Copy link
Author

More details and a cronjob example can be found in the associated blog post :
https://wimantis.ninja/bash-script-to-create-daily-monthly-and-yearly-mysql-database-backups/

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment