Requirements: access to the remote machine (usually a server) via SSH.
I backup my databases to the local installation of MAMP (stands for Mac-Apache-MySQL-PHP), but you can specify any directory you wish.
First step: do some stuff, so you can login automatically (no password prompt) to the remote machine. I followed the steps at http://ubuntuforums.org/showthread.php?t=30709 and it worked flawlessly. In the first step exposed there, just type what the folk says, then “enter” all the time long (no need to enter data).
After you successfully copy the public key to the remote server, just copy-paste the following to your script editor, modify what you need to match your settings and hit “Run”:
--> this is the folder where the remote "mysql" dir will be copied
--> I choose MAMP's one, but you can choose any for backup purposes
set localDir to "User:Library:Application Support:appsolute:MAMP PRO:db:"
--> enter your server, the dir where your "mysql" dir is located (usually something as /var/lib)
--> and move it to whatever location is available via HTTP
--> if you don't have such service, you can use "scp" to copy the file to your machine
--> but then you must setup your machine to accept SSH connections
with timeout of 3600 seconds --> 1 hour
do shell script "ssh -l root server.com 'cd /var/lib; tar -zcf mysql.tar.gz mysql;mv mysql.tar.gz /var/www'"
end timeout
--> download tgz file and remove pre-existing "mysql" folder in local machine (not sure yours, mine yes)
do shell script "cd " & quoted form of POSIX path of localDir & "; curl -O http://www.server.com/mysql.tar.gz; rm -rf mysql;"
--> unpack
tell application "Finder" to open (localDir & "mysql.tar.gz")
--> remove tgz file from remote and local servers (I'll keep the uncompressed dir)
do shell script "ssh -l root qilania.com 'rm /var/www/mysql.tar.gz'; rm " & quoted form of POSIX path of (localDir & "mysql.tar.gz")
Cheers!
EDIT: See this post for a different option (supports other db engines, such as InnoDB).
Julio Sancho's Blog 