Database Backup Strategy

So how do you make backups of huge MySQL databases that are in your production environment without affecting your customers? The answer is with Percona’s Xtrabackup tool. It performs binary backups of heavily loaded MySQL servers amazingly fast. It even supports incremental backups so that you don’t have to backup your entire database every single time. However, even it requires a table lock at end of its procedure if you want the binary log position of the backup. Luckily, there’s a “–no-lock” option and a little trick you can use to get the binary log position when you use it.
Now that we’re using Xtrabackup to backup our live data and we know how to get the binary log position, we just have to automate the procedure. This is harder than you think, because for every incremental backup you need information on the last one you did so that it knows where to start the new one. If you store your backups as compressed data (which you should to save space), this information must be stored separately which means you have to parse it out yourself. Also, in order to restore a backup, you need a list of all the incremental backups so that you can restore them in order.
I spent a long time creating the perfect automation script for all this. For a full backup, the procedure is as such:

1. Run ‘innobackupex’ with –no-lock, –stream, and –compress to create a compressed backup.
2. Use ‘sed’ to parse the log sequence number from the output, which is used for incremental backups.
3. Save the LSN in a separate file so you can refer to it later.
4. Save the filename of the backup in it’s own file, so that you can easily keep track of the all the backups you’ve done in case you need to restore them in order.
5. Upload the final compressed backup and the files from step 3 and 4 to Amazon’s S3. To do this, it’s best to split the backup up into smaller files and upload them all in parallel.

For an incremental backup, the procedure is very similar:

1. Grab the LSN from the file that was created during the full backup
2. Run ‘innobackupex’ with the same options as before, but add –incremental and –incremental-lsn=<LSN>
3. Use ‘sed’ to parse the new log sequence number from the output.
4. Overwrite the LSN file with the new one.
5. Append the incremental backup’s filename to the backup list file.
6. Upload everything to S3.

Restoring the backups is perhaps the trickiest part:

1. Grab the list of all the backups that have happened from the backup list file.
2. Loop through them, and for each one:
1. Uncompress the backup
2. Run ‘innobackupex’ with –redo-only, –apply-log, and –incremental-dir=<full backup directory> on the uncompressed backup. If it’s the original full backup then leave out the –incremental-dir part.
3. Now that all the incremental backups have been applied to the full backup (now called the base), finish up the process by running ‘innobackupex’ with –apply-log on the base directory.
4. chown -R mysql:mysql <base directory>
5. Start MySQL on the base directory

We’ve been running this script regularly for weeks now, and it has been working great. We do one full backup per day and an incremental backup each hour. Also, since the backups contain the binary log position, we also have the ability to do point-in-time recovery by replaying the bin-logs. It’s important to note that creating these backups uses a lot of disk IOPS, so it’s wise to do them on a separate drive.