From LUGOP

Here is a script that takes a complete a backup of your MySQL table structure and then copies to a remote system. This can be done without stopping MySQL. It also assumes that you have SSH public key authentication setup between some account on the two systems. I use this daily and run it from /etc/cron.daily as root.

Note: This assumes that you can access MySQL as root without a password. This is likely a bad assumption. You can create a user with select and Lock Table privileges that can do this, and even have it have a password.

Code

#!/bin/bash


#$Id: mysql_backup,v 1.1.1.1 2006/02/06 04:06:07 stahnma Exp $
#Assumes a decent $PATH variable is set.



backup_dir=/var/backup
date=`date -I`
filename=$backup_dir/databasebackup-$date.sql.bz2
remote_host="rack"
remote_user="root"
remote_www_dir="/var/backup/www"
remote_database_dir="/var/backup/databases"
local_dir="/var/www/*"

[ -d "$backup_dir" ] || mkdir -p $backup_dir

mysqldump --opt --all-databases | bzip2 -c > $filename

scp $filename $remote_user@$remote_host:$remote_database_dir

rsync -av --delete -e ssh $local_dir $remote_user@$remote_host:$remote_www_dir