ChillNet - The Power of Simplicity - Development, Hosting, Bulk SMS and Fax2email made easy!

Please note:

You might need root access to the server.

Create a new file in /etc/cron.daily called mysql-backup.cron

vi /etc/cron.daily/mysql-backup.cron

Add the following in that file:

#!/bin/sh
date=`date -I`
mysqldump -uroot -pYOUR_ROOT_MYSQL_PASSWORD --opt --all-databases | gzip -c > /WHERE/YOU/WOULD/LIKE/TO/STORE/YOUR/BACKUPS/$date.sql.gz

The first line contains the Shebang.

The second line creates a variable containing today's date. Example:  YYYY-MM-DD

The 3rd line will use the mysqldump utility, log in as root, then dump all your databases and pipe the output to gzip where you'd like to store your file.

If you want to backup your databases individually, then you might want to consider the following:

#!/bin/sh
date=`date -I`
for customerdb in `mysql -h YOUR_HOST -uUSERNAME -pPASSWORD -e "show databases" -B -N` 

do

mysqldump -h YOUR_HOST -uUSERNAME -pPASSWORD $customerdb | gzip -c > /home/backups/mysql/$customerdb-$date.sql.gz 

done;

You might want to consider to add the following to your cron as well:

tmpwatch 672 /WHERE/YOU/WOULD/LIKE/TO/STORE/YOUR/BACKUPS/

This will ensure that you're disk space won't be used up eventually.  All files older than 673 hours (± 1 Month) would be removed after you've made your backups.