This week I needed to backup a Percona MySQL server.
One solution for this, is to stop the MySQL server, create a
mysqldump, and transfer it to your backup location.
However, depending on your tables and data size, this might not be the best solution. Especially if the database you want to backup is a live database with active users.
The solution for me was to use xtrabackup (innobackupex) from Percona to stream the database in tar format over SSH to another server:
innobackupex --stream=tar . | ssh firstname.lastname@example.org "cat - > /mnt/backup/backup.tar"
Once this is done, the other server needs to unpack the tar and prepare the backup:
xtrabackup --prepare --target-dir=/var/lib/mysql
At the end of this command, you should see an OK message.
If all went well, you can now do:
chown -R mysql:mysql /var/lib/mysql
and restart the MySQL server. The binlog position will be included in the output of the
xtrabackup --prepare so you can easily set up master/slave syncing.
Finally, I created a cronjob on the MySQL Slave server which will take a daily backup with
xtrabackup and upload to a 3rd party secure storage.