This is a simple script that dumps a mysql database, moves it to the web directory and tar gzips it. The script then copies it to a remote server, deletes the original file and emails you to tell you it has finished.

Note - you’ll need to set up key based authentication between your servers. Note - don’t enter a passphrase. Other than that - look here http://www.debianadmin.com/ssh-your-debian-servers-without-password.html

If the script is of any use to you feel free to modify use and do as you wish with it.

 
#!/bin/bash

###THIS SCRIPT IS PROVIDED FREE OF CHARGE BUT WITHOUT A WARRANTY OF ANY KIND CHRISTOPHERROWSON@GMAIL.COM###

###SETUP THE SCRIPT PARAMETERS###

DATABASENAME=funkydatabase
DATABASEUSER=bob
DATABASEPASSWORD=securepass
WEBDIRECTORY=/home/website/
SCPSERVERLOCATION=bob@server.com:/home/bob/backups/$DATABASENAME-`date '+%d-%B-%Y'`.tgz
EMAILADDRESS=emailaddress@email.com
 
###OK FINISHED - YOU DON'T NEED TO CHANGE ANYTHING BEYOND HERE###
 
mysqldump -u $DATABASEUSER --password=$DATABASEPASSWORD $DATABASENAME > $DATABASENAME-`date '+%d-%B-%Y'`.sql
mv $DATABASENAME-`date '+%d-%B-%Y'`.sql $WEBDIRECTORY
tar czf /tmp/$DATABASENAME-`date '+%d-%B-%Y'`.tgz $WEBDIRECTORY
scp /tmp/$DATABASENAME-`date '+%d-%B-%Y'`.tgz $SCPSERVERLOCATION
rm /tmp/$DATABASENAME-`date '+%d-%B-%Y'`.tgz
rm /$WEBDIRECTORY/$DATABASENAME-`date '+%d-%B-%Y'`.sql
/bin/mail -s "$DATABASENAME-`date '+%d-%B-%Y'`.tgz created" "$EMAILADDRESS" <<EOF
Backup has completed
`date`
EOF