Using XtraBackup to backup a remote machine

I love Percona's XtraBackup utility.  It's basically a GPL answer to Oracle's proprietary (and expensive) MySQL Enterprise Backup.  Percona claims that it has even more features that Enterprise Backup.  I do not have access to Oracle's product, though, so I cannot evaluate that claim.

I have my backups set up in a particular way, for convenience and security.  I have a backup machine that has ssh access to the backup user on other machines.  It has terrabytes of spare disk space, and runs a script that uses rsync and other utilities to take backups of my other servers.

Since my backup server is where all backup scripts are run, I have previously used mysqldump to take a backup of one of my slave servers.  This worked well, but was not ideal.  About once a week, mysqldump dies for no apparent reason during the dump, so I end up without a good backup for that day.  It's also slow, and locks the entire database while running.

I've been wanting to switch to XtraBackup for a while.  I've used it when setting up new slaves, and not only is it faster, it does not lock up the entire database while it runs in order to get a consistent backup.  The only problem is that it doesn't fit into my current backup strategy; it is designed to run on the same host as mysqld.

Today, I figured out how to get it working in streaming mode, controlled by my backup server.  It uses little to no disk space on the MySQL server, and streams the backups over to my backup server.  It required a little sudo magic, but beyond that it fit right into my existing backup procedure.

In order to do this, Percona's XtraBackup will need to be installed on both servers.  I had the Percona repository added to my server already, so I just ran:

# yum install percona-xtrabackup

The sudo trick was necessary because I am using ssh to log into my MySQL server as the backup user, which cannot successfully run XtraBackup.  I created the following file as /usr/local/bin/backup-mysql.sh:

#!/bin/bash

/usr/bin/innobackupex --slave-info --stream=xbstream /data/tmp

Then I needed to make sudo allow the backup user to run this script as root without a password.  I added the following line to my /etc/sudoers:

backup ALL=(root) NOPASSWD: /usr/local/bin/backup-mysql.sh

Alternatively, I could have just had the sudoers file contain /usr/bin/innobackupex, and then called that directly.  By keeping it one step removed, I've made it so that only a specific innobackupex command can be run, which lowers the security risk involved if any bugs were found in innobackupex in the future.

On my backup server, I added this to my backup script:

/usr/bin/ssh \
    backup@mysqlserver \
    "/usr/bin/sudo /usr/local/bin/backup-mysql.sh" \
    | xbstream -x -C /backup/daily/0/mysql

/usr/bin/innobackupex \
    --host=mysqlserver \
    --user=root \
    --password=rootpassword \
    --apply-log \
    /backup/daily/0/mysql

The username and password is used to detect the version of MySQL being used, so it can use the correct XtraBackup binary.  I could also have just told it which binary to use, but this is more forward-compatible.