Simple script that backs up all MySQL user databases separately, with dated filenames

backup-mysql.sh

#!/bin/bash
# Dumps all databases to separate backup files named with today's date

BACKUP_TO="$HOME/backup/db"
MYSQLDUMP_OPTIONS="--compact --add-drop-table --skip-set-charset"

# ---
mkdir -p $BACKUP_TO
DATE=`date +'%Y-%m-%d'`
mysql -e 'show databases' | ruby -e "
  to_skip = ['database','information_schema']
  STDIN.read.each do |db|          
        db.chop!                       
        if !to_skip.include?(db.downcase)     
                puts %Q(backing up #{db})  
                cmd = %Q(mysqldump $MYSQLDUMP_OPTIONS #{db}) 
                file = %Q($BACKUP_TO/#{db}_$DATE.sql)      
                system %Q(#{cmd} > #{file})          
        end 
  end
"

# remove old backups
/usr/bin/find $BACKUP_TO -mtime +90 -type f -exec /bin/rm '{}' \;

# remove zero-length backups
/usr/bin/find $BACKUP_TO -size 0 -type f -exec /bin/rm '{}' \;