I want to backup my databases without downtime... Well, there are couple approaches to this. One of them is to add a standby slave and use LVM snapshot approach. The second approach is to use percona xtrabackup. My problem is that I have some huge databases here, so taking local backups is out of the question.
But just to remain written this is how to do local hot backups.
1. Take the backup
[codesyntax lang="bash"]
time innobackupex --user=user --password=password --no-timestamp --rsync --slave-info --safe-slave-backup /path/mysql
[/codesyntax]
2. At this point the data is not ready to be restored. There might be uncommitted transactions to be undone or transactions in the logs to be replayed. Doing those pending operations will make the data files consistent and it is the purpose of the prepare stage. Once this has been done, the data is ready to be used.
[codesyntax lang="bash"]
time innobackupex --apply-log --use-memory=12G /path/mysql
[/codesyntax]
Since in my case having storing the backup locally (not even temporary) is not an option I need to transport the whole thing to the storage server.
The lame solution is to use NFS... but c'mon... grrrr... no!
The right solution is to stream the data directly to the storage server. To achieve this I am using netcat. Also to gain more performance I am compressing the data on the fly.
1. On the target machine
[codesyntax lang="bash"]
nc -l -p 9999 | qpress -dio | xbstream -x -C /path/to/mysql
[/codesyntax]
2. On the source machine
[codesyntax lang="bash"]
time innobackupex --user=user --password=pass --no-timestamp --slave-info --parallel=$((`nproc`-2)) --safe-slave-backup --stream=xbstream /data/db/mysql | qpress -io something | nc destination 9999
[/codesyntax]
3. On the target machine
Apply the transaction log in order to make the backup consistent.
[codesyntax lang="bash"]
time innobackupex --apply-log --use-memory=120G /path/to/mysql
[/codesyntax]
Note: --use-memory option is used to speed up the process by using more memory (the default is 100MB which is a quite small value).