Streaming MySQL backup

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 user@x.x.x.x "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.

By jochen

Developer at TestingBot and Tinder

Leave a Reply

Your email address will not be published. Required fields are marked *