backup-mysql.sh

#!/bin/bash -e

PATH=/usr/bin:/usr/sbin:/bin:/sbin

date=`date +"%Y-%m-%d"`
hostname=`hostname -f`

dump=/usr/bin/mysqldump
dump_options="--ignore-table=mysql.event --single-transaction --quick"
show=/usr/bin/mysqlshow
show_options=""

# /var/lib/backups can be e.g. NFS mounted
backup_dir=/var/lib/backups/mysql/${hostname}/
dir_name=backup-${date}
dir_name_full=${backup_dir}/${dir_name}
file_name=${dir_name}.tar

rm -fr ${dir_name_full}
mkdir -p ${dir_name_full}

for database in `${show} ${show_options} | awk '{if (NF == 3) { print $2 }}' | egrep -v "^Databases$|^information_schema$|^performance_schema$"` ; do
    ${dump} ${dump_options} ${database} | xz >> ${dir_name_full}/${database}.sql.xz
done

cd ${backup_dir}
rm -f ${file_name}
tar cf ${file_name} ${dir_name}
rm -rf ${dir_name}

# Optionally delete all backups after 90 days
#find /var/lib/backups/${hostname}/mysql -ctime +90 -type f -print | xargs rm 2>/dev/null