Skip to content

Instantly share code, notes, and snippets.

@DavidGoodwin
Created November 5, 2017 20:37
Show Gist options
  • Save DavidGoodwin/fd6861267fcfc1612ad700cac588e70c to your computer and use it in GitHub Desktop.
Save DavidGoodwin/fd6861267fcfc1612ad700cac588e70c to your computer and use it in GitHub Desktop.
MySQL backup script (bash)
#!/bin/bash
# Simple backup script for MySQL/MariaDB on a Debian/Ubuntu based server.
# You can run this as often as you like (hourly, daily, weekly). It contains locking to stop multiple executions at once.
# Dependencies:
# apt-get install lockfile-progs
## Begin config.
# where to backup to
DEST=/var/backups/mysql
# some history
LOG=/var/log/backup-mysql.log
# keep the last X days of backups.
MAX_DAYS_AGE=10
## End config.
[ ! -d $DEST ] && mkdir -p $DEST
PATH=/sbin:/usr/sbin:/usr/local/sbin:/bin:/usr/bin:/usr/local/bin
export PATH
# abort on any non-zero return code (error); you may not want this.
set -e
LOCKFILE=/tmp/cron.mysql.backup
lockfile-create $LOCKFILE
if [ $? -ne 0 ]; then
echo "Fail..... can't get lockfile $LOCKFILE"
exit 1
fi
lockfile-touch $LOCKFILE &
LOCKFILEPID="$!"
trap "{ kill $LOCKFILEPID ; lockfile-remove $LOCKFILE ; }" SIGQUIT SIGTERM SIGINT EXIT
# See: http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/MySQL.Procedural.Importing.NonRDSRepl.html
# See: https://dev.mysql.com/doc/refman/5.5/en/mysqldump.html#option_mysqldump_master-data
function backup() {
database=$1
if [ $database = "mysql" ]; then
events="--events --ignore-table=mysql.event "
fi
echo "$(date) - mysql backup ($database) started " | tee -a $LOG
START=$(date +%s)
mysqldump \
--defaults-extra-file=/etc/mysql/debian.cnf \
--single-transaction $events \
--quick \
--lock-tables=false "${database}" | gzip > $DEST/${database}-$( date +"%F_%H-%M" ).sql.gz
END=$(date +%s)
echo "$(date) - mybackup backup for $database took $(( $END - $START )) " | tee -a $LOG
}
# Backup a single specific DB.
if [ "x$1" != "x" ]; then
backup $1
exit 0
fi
# assume database names cannot contain spaces...
for database in $(mysql --defaults-extra-file=/etc/mysql/debian.cnf -BNe "show databases")
do
if [ "${database}" = "performance_schema" ]; then
continue
fi
if [ "${database}" = "information_schema" ]; then
continue
fi
backup "${database}"
done
# remove old files
find $DEST -type f -mtime +${MAX_DAYS_AGE} -delete
# ideally setup a passive service in nagios so you get alerted if this hasn't run fully in whatever timeperiod.
# echo "hostname\tservicedesc\t0\tTextOutput\n" | /usr/sbin/send_nsca -H nagios.server
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment