Backup all MySQL databases to gzip individually with mysqldump

I was always kind of disappointed with the mysqldump cron jobs I saw published because they always threw everything into a single backup file. Alternately I could call out each db individually, but that meant if I added another mysql database I had to update my script. When I started looking for shell scripts that I could run as a cron job (as opposed to say commercial tools, stored procedures, etc.) I kept coming up empty. So I came up with the following that backups all databases individually and then deletes any backups older than 30 days:

#/bin/bash

MYSQL_DATA=/data/mysql/data
MYSQL_BACKUP=/backup/mysql
DATE=`date '+%F'`
TEMPFILE=/tmp/MYSQL_BACKUP.tmp

touch $TEMPFILE
ls -l $MYSQL_DATA -I mysql | grep ^d | awk '{print $9}' >> $TEMPFILE
DIR_LIST=( `cat "$TEMPFILE" `)

for i in "${DIR_LIST[@]}"
do
        mysqldump -u <mysql_backup_user> -p<password> $i | gzip > /backup/$i.$DATE.sql.gz
done

mysqldump --events -u <mysql_backup_user> -p<password> mysql | gzip > /backup/mysql.$DATE.sql.gz

rm -rf $TEMPFILE
find $MYSQL_BACKUP/*.gz -type f -mtime +30 -exec rm {} \;

Copyright © All Rights Reserved · Green Hope Theme by Sivan & schiy · Proudly powered by WordPress