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
time innobackupex --user=user --password=password --no-timestamp --rsync --slave-info --safe-slave-backup /path/mysql
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.
time innobackupex --apply-log --use-memory=12G /path/mysql
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
nc -l -p 9999 | qpress -dio | xbstream -x -C /path/to/mysql
2. On the source machine
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
3. On the target machine
Apply the transaction log in order to make the backup consistent.
time innobackupex --apply-log --use-memory=120G /path/to/mysql
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).