#!/usr/local/bin/bash
# monthly backup each mysql db into a different file, rather than one big file, for freebsd
# as with --all-databases - will make restores easier

USER="root"
PASSWORD="put_your_password_here"
OUTPUTDIRFINAL="/backup"
myfilename=`date +%Y_%m`
MYSQLDUMP="/usr/local/bin/mysqldump"
MYSQL="/usr/local/bin/mysql"

# clean up any old backups
OUTPUTDIR=$OUTPUTDIRFINAL"/mysql"
rm -rf "$OUTPUTDIR" > /dev/null 2>&1
mkdir "$OUTPUTDIR"
cd "$OUTPUTDIR"

# get a list of databases
databases=`$MYSQL --user=$USER --password=$PASSWORD -e "SHOW DATABASES;" | tr -d "| " | grep -v Database`

# dump each database in turn
for db in $databases; do
	echo $db
	nice $MYSQLDUMP --force --opt --user=$USER --password=$PASSWORD --databases $db > "$OUTPUTDIR/$db.sql"
done

cd ..
nice /usr/local/bin/rar a -r -s -m4 "$myfilename""_mysql.rar" "mysql"
rm -r "$OUTPUTDIR" > /dev/null 2>&1

# log events (optional)
echo $myfilename >/tmp/cron_monthly.log