use rsync with stream

[Michael Kefeder]

Hello xtrabackup team

I am using your awesome tool to backup innodb Servers that host many databases.
Problem is the locking of tables while you are copying the .frm, .MYD and more files - which is a problem when using the --stream option.
I am using the --stream option, because it makes the most sense (backup directly to a separate machine in a separate building). Now you have implemented the --rsync option, which is a smart way to minimize locking time. On my Testserver i have 58 databases which end up to have 8526 files that need to be copied during the lock.
Lock on that test-machine is held for 1m20s using normal --stream method, apparently copying 8526 files takes that long. As a proof of concept I adopted your rsync trick for --stream and now locking takes 0m1s. Here is the command line used with my patch applied:

backupserver$ ssh USER@LIVESRV "/usr/bin/innobackupex --rsync --stream=tar /tmp/mysql_backup" | gzip > /tmp/backup.tar.gz

I basically use the same idea, but instead of streaming the files right away I just run the second rsync (locally) into /tmp/mysql_backup/, then unlocking happens, then i use the /tmp/xtrabackup_rsyncfiles_pass2 file list to stream the files afterwards. This is still a consistent backup, which can be streamed but it uses much less locking time. And in consecutive runs even a little less total backup-time since /tmp/mysql_backup/ already holds those files that normally don't change much. (maybe some method to purge that folder from time to time is in order too)

Since I've found some TODO comments in your code where rsync will be used in the future, I understand that using my current trick with --rsync and --stream might make code too complicated. I am just presenting a working implementation of my idea. I guess for a final version of this a new option called --rsync-before-stream, --stream-frm-after-unlock or something similar would be easier to enable/disable this snappier method of streamed-backups. I also know that my using of the /tmp/xtrabackup_rsyncfiles_pass2 list is not the most elegant solution but it works ;)

I hope you will accept my patch and please let me know if I can help to nicely integrate this feature into your future releases.

best regards
 Mike

[Michael Kefeder (michael-kefeder)]

forgot to mention, patch is against percona-xtrabackup-2.2.3

[Alexey Kopytov (akopytov)]

Michael,

Thanks for the contribution! We actually have https://blueprints.launchpad.net/percona-xtrabackup/+spec/rsync-with-stream-option. We currently plan to implement it in a future 2.2 release.

Keeping this bug open to trace the contributed patch.

[Shahriyar Rzayev (rzayev-sehriyar)]

Percona now uses JIRA for bug reports so this bug report is migrated to: https://jira.percona.com/browse/PXB-1010