Script to backup MySQL

backup_mysql.sh

# Configuration.
date=`date +%Y-%m-%d`
bk_dest='/var/archives/mysql'
log_file=$bk_dest/bk_mysql-${date}.log
mysql_cmd='/usr/bin/mysql'
mysqldump_cmd='/usr/bin/mysqldump'
dbuser=root
dbpass=`cat /etc/.mysqlpasswd`


databases=(`echo 'show databases;' | $mysql_cmd -u ${dbuser} --password=${dbpass} | grep -v ^Database$`)

for d in "${databases[@]}"; do
      if [[ $d != 'tmp' && $d != 'test' ]]
      then
            echo "DATABASE ${d}" >> $log_file
            path="${bk_dest}/${date}"
            mkdir -p ${path}
            ${mysqldump_cmd} --user=${dbuser} --password=${dbpass} --opt --databases ${d} | bzip2 -c > ${path}/${d}.sql.bz2
       fi
done

# delete old dumps (retain 5 days)
find ${bk_dest} -mtime +5 -exec rm {} \;

echo "" >> $log_file
echo Disk Space Report: >> $log_file
echo -------------------------------------- >> $log_file
du -h --max-depth=1 $bk_dest >> $log_file