MySQL backup script (bash)

mysql-backup.sh

#!/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