Backing up MySQL via SSH.

Tech Notes

(mt) Set up authorized keys (to enable automatic login)

Copy your public key from the machine you want to backup to. sh-3.2$ cat ~/.ssh/id_rsa.pub Enable SSH and then SSH into (mt). -bash-3.2$ cd -bash-3.2$ whoami [username] -bash-3.2$ su Password:  [root@domain domain.com]# mkdir .ssh [root@domain domain.com]# chown [username] .ssh/ [root@domain domain.com]# exit -bash-3.2$ vi .ssh/authorized_keys2 [paste in copied keys making sure you remove pasted in linebreaks] -bash-3.2$ chmod 600 .ssh/authorized_keys2  Test that you can login without a password.

Create a script to run a mysqldump via SSH.

#!/bin/bash DATE=`eval date +%Y%m%d` SOURCE=[domain.com] REMOTE_USER=[ssh-username] SQL_USER=[sql-username] SQL_PASS=[sql-password] REMOTE_CMD=/usr/bin/mysqldump DATE=`eval date +%Y%m%d` DATABASE=[db-name]   ssh -l $REMOTE_USER $SOURCE "$REMOTE_CMD --quick --user=$SQL_USER --password=$SQL_PASS $DATABASE | gzip -c" > remote/$SOURCE-dump-$DATE.sql.gz Run the script then gunzip the result to check its all there. Create a cron.daily script to run the script every day.