One of my recent tasks was to set-up a new automatic backup script, which dumps out the MySQL database on the remote host at a regular time, at a later time, it is RSYNC’d from the backup server through a remote firewall. I must say that I was a little surprised, to discover that the finished script and the configuration that goes along with it, was actually quite simple and easily repeatable. I was able to replicate the process for three sites very quickly and will easily be able to scale it to many more when necessary.

SSH Tunneling and SSH Keys

In order to perform a process on a remote firewalled host, you need to first set up keys, to allow the trusted backup server to gain access to the intermediate host. You must also set up a key which allows the intermediate host to gain access to the firewalled host.

First, let’s generate a public key on the backup server, if we don’t already have one. Be sure to use an empty pass phrase since this is an unattended script.

[backup@lexx log]# ssh-keygen -t dsa
Generating public/private dsa key pair.
Enter file in which to save the key (/backup/.ssh/id_dsa): 
Enter passphrase (empty for no passphrase): 
Enter same passphrase again: 
Your identification has been saved in /backup/.ssh/id_dsa.
Your public key has been saved in /backup/.ssh/id_dsa.pub.
The key fingerprint is:
3d:48:9c:0f:46:dc:da:c3:a6:19:82:63:b1:18:91:62 backup@lexx

The default key will, by default, be located in ~/.ssh/id_dsa.pub Copy the contents of this file to the clipboard, you will need this to get the remote server to trust the backup server.

Logon to the remote external server via ssh. On this server we will configure it to trust the backup server.

[backup@lexx ~]# ssh user@remotehost.com
user@remotehost.com's password: 
Last login: Thu Jul 14 22:57:58 2011 from 69.73.94.214
[user@remotehost ~]# ls -al .ssh
total 28
drwx------  2 user user 4096 2011-07-14 22:05 .
drwxr-x--- 12 user user 4096 2011-07-14 21:54 ..
-rw-------  1 user user 3024 2011-07-14 21:57 authorized_keys2
-rw-------  1 user user  668 2010-10-27 23:52 id_dsa
-rw-r--r--  1 user user  605 2010-10-27 23:52 id_dsa.pub
-rw-r--r--  1 user user 5169 2010-10-21 13:01 known_hosts

If the authorized_keys2 or simmilarly named file does not yet exist, create it and open the file in your text editor of choice. Then paste the key you copied from the id_dsa.pub file on the backup server.

To make the remote server recognize the newly added key run the following:

[user@remotehost ~]# ssh-agent sh -c 'ssh-add < /dev/null && bash'

Now we can make sure that the key works as intended by running the following command, which will ssh into the server and execute the uptime command:

[backup@lexx ~]$ ssh user@remotehost.com uptime
 23:57:17 up 47 days,  4:11,  1 user,  load average: 0.54, 0.14, 0.04

Since we got the output of the uptime command without a login prompt, it means the key was created successfully.

Now we repeat the ssh key process, this time between the remotehost server and the firewalled server.

[user@remotehost ~]# ssh-keygen -t dsa
Generating public/private dsa key pair.
Enter file in which to save the key (/user/.ssh/id_dsa): 
Enter passphrase (empty for no passphrase): 
Enter same passphrase again: 
Your identification has been saved in /user/.ssh/id_dsa.
Your public key has been saved in /user/.ssh/id_dsa.pub.
The key fingerprint is:
3d:48:9c:0f:46:dd:df:c3:a6:19:82:63:b1:18:91:62 user@remotehost

Copy the information from the .ssh/id_dsa.pub of the remote external server to the firewalled server, then add to the authorized_keys file and run:

[user@firewalledserver ~]# ssh-agent sh -c 'ssh-add < /dev/null && bash'

Now you should be able to pass the rsync command all the way through the remote firewall, to the firewalled server from the backup server.

This can be tested by the following command, which tunnels through the firewall and executes the uptime command on the internal server:

[backup@lexx ~]$ ssh user@remotehost.com ssh user@firewalledserver uptime
 23:52:17 up 41 days,  4:12,  1 user,  load average: 0.50, 0.13, 0.03

RSYNC The Data From the Backup Server, Through The Firewall

Now that we've got all of our keys set-up, most of the work has been done. I'm assuming you have a cron job on the internal server which dumps the mysql database at a specific time. You should set-up your rsync command late enough, so that the cron job has had enough time to dump the database.

Here is the rsync command which puts you through the firewall to download the remote mysql database dump. The -z flag allows you to do this with compression, which can significantly speed up the process.

[backup@lexx ~]$ rsync -avz -e "ssh user@remotehost.com ssh" user@firewalledserver:/home/user/rsync-backup/mysqldump.sql /home/backup/

This will create a hidden file that will be named something like .mysqldump.sql.NvD8D, which stores the data until the sync is complete. After the sync is complete you will see a file named mysqldump.sql in /home/backup/ folder.

Just set up the necessary cron scripts to make sure everything happens at the right time, possibly put some logging in there so you can see what has happened and you're done!

Here's an example of what I did on the backup server, to call the backup script. It appends the output of both STDOUT and STDERR to the /var/log/remote_backuplog file each time it is run. It also runs the script as the backup user so the files it generates have the correct permissions for the backup user to access.

01 6 * * * backup /home/backup/run_backups.sh >> /var/log/remote_backuplog 2>&1

Here is what my rsync script run_backups.sh looks like.

#!/bin/bash

echo "running backups"
# print the date into the logfile
date

# backup server 1
echo "backing up server1"
ssh user@externalserver1 ssh user@internalserver1 ls -l /home/user/rsync-backup/mysqldump.sql
/usr/bin/rsync -avz -e "ssh user@externalserver1 ssh" user@internalserver1:/home/user/rsync-backup/mysqldump.sql /home/backup/server1/

# backup server 2
echo "backing up server2"
ssh user@externalserver2 ssh user@internalserver2 ls -l /home/user/rsync-backup/mysqldump.sql
/usr/bin/rsync -avz -e "ssh user@externalserver2 ssh" user@internalserver2:/home/user/rsync-backup/mysqldump.sql /home/backup/server2/

# backup server 3
echo "backing up server3"
ssh user@externalserver3 ssh user@internalserver3 ls -l /home/user/rsync-backup/mysqldump.sql
/usr/bin/rsync -avz -e "ssh user@externalserver3 ssh" user@internalserver3:/home/user/rsync-backup/mysqldump.sql /home/backup/server3/