MySQL Database Backup – Part 3 – Remote Backup using RSYNC

This is the final article (I think…) on MySQL database backup. In my previous article I discussed how to craft a very efficient script for automated LOCAL backup of your MySQL databases. But that isn’t the whole story. What happens if your server burns to the ground… or more likely the hard drive gives up the ghost, corrupting everything and effectively leaving you up the proverbial creek without a paddle? Today I want to finish our script by adding a couple of extra components and giving you a paddle in the form of “offsite” backups.

Offsite backups are part of a larger security topic known as disaster recovery. The previous script we wrote is great if some malicious person hacks your site and fills your pages with spam… (yep, this has happened to me! I had a whole site filled with download links to illegal copies of bollywood movies!) If that happens, restore yesterday’s backup, close up your security holes, and move on. However, today’s script is for when that world-ending (or at least server-ending…) disaster strikes and you need to rebuild somewhere else.

Now, DR can get expensive because it often means doubling your production resources. Which I have actually done for myself… I have a fully-loaded, ready-to-go secondary server that is available at all times and on a good day I can be back up and running on my secondary box in about 30 minutes from the time I start hitting keys on my keyboard.

However, you might not be as particular as me or have the funds to keep a second box running all the time. Luckily, you can easily adapt what I will be walking through today and use it to push backups to a drive at home. Your data is what is important and not easily replaceable irreplaceable. It might take you a day or two to secure a new server if your current one is plunged into the great beyond, but data can be lost forever. However if you have solid database backups, in most scenarios you CAN rebuild!

I can’t impress upon you enough how bloody vital off-server backups are if you have any amount of user or self-created content.

Home internet bandwidth is relatively cheap, consumer storage is really cheap, there is no reason to not pursue this.

So, without further pretense, let’s dive in!

As mentioned earlier, today’s script is a modified version of the script found here. As such, I am not going to reinvent the wheel; go read that article and work on getting local backups working first.

What we are adding to our script are some commands that will initiate a root RSYNC connection to a remote target server and then sync all of our local backups to our remote location.

Before we finally get to the actual scripting bit, you need to setup remote root SSH login without a password. I just finished an article on that very topic a few hours ago. Go read it, set it up, and then come back.

Okay, here we go!

Here are the new bits:

Let me quickly walk through what we are doing… The first three lines are all variables and you will need to modify their contents.

The first line is the port that SSH listens to for connections on your remote server. The default is 22. I use non-default ports for SSH (to avoid getting bombarded by malicious scripts/bots) so my script sets the port.

The next variable is the IP address of the backup server. In my example I am using a fictitious address, change it to match the public IP address of your remote backup target server.

The third variable sets the login account on the remote server. I do use the “root” account as it allows me to keep my file permissions intact. If you followed my tutorial on setting up SSH connections to root without a password, then you can leave this be. If you are having to use a different account, then you will need to set that name here.

The fourth line of the script is where the magic happens. This is the RSYNC command in all its glory.

Here is the basic RSYNC syntax:

It is VITALLY important that you pay attention to which flags you set with RSYNC and that you test carefully as you could inadvertently wipe out all of the data on your source server. I will talk about the “-n” flag which tells RSYNC to do a “dry run” which is perfect for testing.

Here are short descriptions of the flags I am using:
-a – archive — syncs recursively and preserves symbolic links, ownership, permissions, mod times, groups, etc.
-v – verbose output – tells you exactly what is going on
-P – show a progress bar
-e – specify the remote shell to use
–delete – deletes files and directories in the destination that are not present in the source
-n – do a dry run

I am going to talk a little more in-depth about these flags and their uses:

The -a flag is just plain critical for most backup operations. It is a combination flag in that it encompasses a bunch of other flags that are commonly used for “archive” purposes (i.e. backup/storage). Some of the critical stuff it keeps intact are timestamps and file permissions. It also tells RSYNC to go all the way down through sub-directories and get everything, not just the top level stuff.

The -e flag allows us to specify the remote shell (in this case, ssh) to use and pass a few flags for that shell along… Specific to this script, we need to specify which port to make an SSH connection on if we aren’t using standard port 22. Syntactically it is important to keep the stuff shown immediately after the -e flag (i.e. “ssh -p $SSHPORT”) immediately after the -e flag and not put any other flags in between.

–delete – this flag can be dangerous if you aren’t careful because it can delete files at the destination. This one is critical though because it makes this a true “sync” operation rather than just a straight copy/paste. If you are only keeping 30 days worth of backups on your source server and deleting anything older than that then you probably only want to keep about 30 days worth on your offsite target server. So when rsync runs it will delete anything on the destination server that isn’t present on the source server. I once mixed up my source and destination servers and started deleting backup files on my source server because I wasn’t being careful! Hence, we need to talk about…

Flags you should set for testing but need to remove from your final script:
The -v flag tells RSYNC to tell you exactly what it is doing as it does it. You can drop this in your final script but it is excellent for testing.

The -P flag shows a progress bar, once again, great for when you manually run your script and want an idea of what it is doing and how long things are taking.

Finally, the -n flag tells RSYNC to do a “dry run” – combine with -v this means that RSYNC is going to output to the commandline everything it WOULD copy and delete based on the command and flags but it doesn’t actually make any changes, hence it is a “dry run.” Obviously you don’t want this in your final script but you should definitely use it to make sure your RSYNC command is exactly correct. Make sure you remove this from your final script, otherwise your remote backup won’t actually backup anything!

Okay, so that is flags. As for the rest of the RSYNC command, I am keeping my backup files stored in the “/backup/database/” folder, so that is my source. The -a flag will make sure RSYNC also re-curses into any sub directories.

Finally, the destination is remote so I specify my ssh username at my remote server’s ip address then a “:” followed with the directory I want everything to sync into.

That is pretty much the whole of it. So, for the full, final script:

There you have it! Properly configure and test, then drop that into a cron job and never worry about database backups again!

RSYNC:
https://www.digitalocean.com/community/tutorials/how-to-use-rsync-to-sync-local-and-remote-directories-on-a-vps
http://unix.stackexchange.com/questions/92123/rsync-all-files-of-remote-machine-over-ssh-without-root-user