Backing up MySQL Remotely with Rsync and SSH
Backing up your data is one of those annoying hassles that far too many of us take far too lightly. In the free time you find between midnight and 12 AM you should ask yourself - what is my nightmare IT scenario (at work or at home). Once you answer this question you should take steps to mitigate risk and alleviate damage that might occur. My nightmare scenario is losing my MySQL tables with years of data, articles, and other detritus in them to a crash or corruption. Fortunately, it is fairly easy to back up this data. MySQL backups are highly portable, and it is fairly straightforward to back up and restore MySQL databases.
The first thing to consider is where you want to back up your databases. Do you want to move them to a different place on the host, or to another host entirely? Personally I feel slightly safer moving the databases to an entirely different machine. This is sort of a pain to set up, but it protects me if the database server suffers some massive catastrophe.
An Eye Towards Security
For a scripted solution, the kind we can leave unattended, you want to think very carefully about security. You're going to need a MySQL account that can access the database and back it up. The problem is that the password to this account will be stored in plain text on the server. This could be potentially disastrous if you decide to take a shortcut and just use the root account (since it already has full access to all the databases). Instead, create an account that only has privileges that are needed to back up the data on the database. For instance:
mysql> use mysql; mysql> grant select, lock tables on *.* -> to 'backup'@'localhost' -> identified by 'backup';
This will create a new account called 'backup' with the same password. This account will only be allowed to access the database from the localhost server, and will only be allowed to select records and lock tables (the necessary privileges to perform a mysqldump).
If you're really careful you could also create a unix account for the backup process so you could keep careful tabs on that account, limiting it's privileges to simply execute backup scripts. For our purposes though we'll just stick to the MySQL account. Once you've created this account you're ready to script the backup.
On to the Meat
Backing up the database is fairly simply. We're going to make use of the mysqldump command. This dumps a SQL file with all the necessary SQL to recreate the databases. We want to dump all the databases, and we want to make sure that we use the --quick flag. This option allows the MySQL server to dump data directly to file rather than storing it all in RAM first (making the backup faster and less taxing on the host). The command is:
mysqldump -u backup -pbackup -A -q > /home/backup/backup.sql
This will dump all the database data to a file for easy recovery. This file will be rather large and unwieldy though so we'll want to zip it up to make it smaller. Also, in our resultant script we're going to want to make sure to delete any previous versions before we get started. The final shell script will look like:
#!/bin/bash rm -rf /home/backup/backup.sql.gz mysqldump -u backup -pbackup -A -q > /home/backup/backup.sql gzip /home/backup/backup.sql
This will dump the database and compress it so it is much smaller and more manageable. The next step is to move this file over to another server. We'd like to avoid any extra work in this process however, and there's a wonderful tool that will allow us to maximize our efforts, rsync.
Using Rsync to Back up the Backup
Rsync is a venerable unix utility in the same family as all the rtools (rlogin, rsh, etc.) that have been deemed 'insecure' by the standing security establishment. Luckily security work arounds have been devised since rsync is far too useful to discard. Using SSH we can successfully rsync over the network, encrypting traffic for security. The rsync command we'll use is fairly simple:
rsync -ave ssh /home/backup.sql.gz user@target:/home/backup.sql.gz
Where 'user' is the remote username and 'target' is the backup server. The only trick in this step is that we want to script the behavior. At this point rsync will trigger an ssh login, which requires a password. We could include this password as an argument to our rsync command, but once again, having a password in the script can be dangerous.
SSH Client Keys to the Rescue
Luckily we can create client keys in SSH so that we don't have to list passwords in our shell script. This means that specific accounts from the database host will be able to log into the backup server without a password. We'll want to explicitly limit where clients can initiate such a connection and what they can do with it, but if we're careful we can craft a login from the database host that will only allow the rsync backup. The first thing we'll need to do is create the keys on the database server (not the backup server). To do this type:
$ ssh-keygen -t rsa
The key to getting this to work is to just hit Enter when asked for a pass phrase. This will allow holders of the private key to log in without authentication (the key is the login token rather than a password). Make sure to save the key in an easily identifiable place then fill out the rest of the required data. For purposes of this example we'll assume that the keys go into ~/.ssh/ on our database server. Next we need to copy the public key to the backup server. You can do this a lot of ways, personally I use:
$sftp backupserver justin@backup's password: sftp>put .ssh/id_rsa.pub
Next log into the backup server and move the key into the home directory on that machine like so:
$ mv id_rsa.pub .ssh/authorized_keys
You'll have to make sure the backup server allows public key authentication (PKA). Check the sshd_config file and look for the lines:
#PubkeyAuthentication yes #AuthorizedKeysFile .ssh/authorized_keys
Make sure they're un commented (remove the hash symbol) and the name of the file is correct. If those lines aren't in your config file go ahead and add them.
Now log out of the backup server and test the configuration. You should be able log in without supplying a password (this will be a little scary at first). You're going to have to be careful about guarding the private key on the database server because it grants access to the backup server now.
In order to preserve a modicum of security it would be nice to limit where the public key could be used from. In fact, this can be done by editing the authorized_keys file on the backup server. You add the following to the beginning of the authorized_keys file:
from="backup.domain.tld"
and then a space. Make sure not to add any line breaks in the file. This will limit access using public key authentication to specific domain name. You could also use an IP address if you wanted to be more specific.
The Final Script
Now that you can log into the backup server without a password you can complete the above backup script by adding lines that finish the rsync. The final script should look like:
#!/bin/bash rm -rf /home/backup.sql.gz mysqldump -u backup -pbackup -A -q > /home/backup.sql gzip /home/backup.sql rsync -ave ssh /home/backup.sql.gz user@target:/home/backup.sql.gz
And you're done. The only thing left to do is schedule the script to run in cron and you can safely sit back and smile knowing you've actually got a backup system in place!
Troubleshooting
There are several things that can go wrong with a PKA setup. The permissions on the files in question are most often the culprit. If the target host keeps prompting you for your passphrase then you can be sure something isn't set up properly. For clues as to what is going wrong check the auth.log.
[justin@lyon ~]$ sudo tail /var/log/auth.log lyon sshd[6468]: Authentication refused: bad ownership or modes for file /home/justin/.ssh/authorized_keys
As you can see something isn't quite right. Actually you need to set the permissions on the authorized_keys files quite carefully:
[justin@lyon ~]$ chmod 0644 ~/.ssh/authorized_keys [justin@lyon ~]$ ls -lah ~/.ssh/authorized_keys -rw-r--r-- 1 justin justin 409 Jul 17 13:13 /home/justin/.ssh/authorized_keys
You might also run into problems with your id_rsa (private key) file permissions. Fixing these so they can only be read or written by the owner will clear these problems up:
[justin@avignon ~]$ ssh lyon Welcome to Lyon.MadIrish.net Unauthorized access prohibited! @@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@ @ WARNING: UNPROTECTED PRIVATE KEY FILE! @ @@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@ Permissions 0770 for '/home/justin/.ssh/id_rsa' are too open. It is recommended that your private key files are NOT accessible by others. This private key will be ignored. bad permissions: ignore key: /home/justin/.ssh/id_rsa [justin@avignon ~]$ chmod 0600 ~/.ssh/id* [justin@avignon ~]$ ls -lah ~/.ssh -rw------- 1 justin justin 1.7K 2007-07-17 13:00 id_rsa -rw------- 1 justin justin 409 2007-07-17 13:00 id_rsa.pub [justin@avignon ~]$ ssh lyon Connected to lyon. [justin@lyon ~]$