Mysql backup daily

Mysql Backup Daily

** This is need to be modified

#!/bin/sh

TIME_1=`date +%s`

cd /backup/mysql

DBS="$(mysql --user=youruser --password=yourpass -Bse 'show databases')"

for db in ${DBS[@]}
do
echo ${db}-$(date +%m-%d-%y).sql.bz2 is being saved in /backup/mysql
# remember to add the options you need with your backups here.
mysqldump --user=youruser --password=yourpass $db --single-transaction -R | bzip2 -c > ${db}-$(date +%m-%d-%y).sql.bz2
done

TIME_2=`date +%s`

elapsed_time=$(( ( $TIME_2 - $TIME_1 ) / 60 ))

## just a sanity check to make sure i am not running a dump for 4 hours

echo "This mysql dump ran for a total of $elapsed_time minutes." > mysql_dump_runtime.txt

# delete old databases. I have it setup on a daily cron so
# anything older than 60 minutes is fine

for del in $(find /backup/mysql -name '*.sql.bz2' -mmin +60)
do
echo This directory is more than one day old and it is being removed: $del
rm $del
done