Thursday, October 14, 2010

HowTo: xtrabackup directly to target host, no additional space for archive file needed

xtrabackup is a great tool for taking backups/snapshots, etc. and sometimes we have large amounts of data to deal with and not enough storage to mess around with.

The xtrabackup docs contain steps for how to stream your backup over the network to another host, which is fine if the end result you want is a tar/gzip type archive, however, in some cases you may want to just get the files to the other host unextracted in order to create a new slave DB.

In this case you just want to get that snapshot into the new host as easily as possible -- in many cases I don't have enough storage to first put it into a tar or tar.gz and then extract.

To work around that, here is a way you can stream your backup over the network straight onto disk on the other side, while avoiding the need for an archive file as a stepping stone in the process.

Note: My bash-fu is probably not as advanced as some, so perhaps there is a more elegant way to make this fly, but it seems to work just fine for me.

ssh root@target-host "cd /data/target-dir; nc -l 9210 | tar xvif - " & sleep 1; \
innobackupex-1.5.1 --stream=tar /datadir/path --user=root --password=XXXXX\
   --slave-info | nc target-host 9210

Once you are done, remember you still need to --apply-log before the snapshot can be used.

Hopefully this will save someone else a few minutes.