XtraBackup is a command line tool by Percona that creates backups of MySQL databases. Unlike mysqldump, XtraBackup copies the entire MySQL data directory. When you want to restore the database from a backup, you simply replace the entire MySQL data directory with the backup.

One great feature of XtraBackup is the ability to stream your backup off-site. By streaming directly to something like AWS S3, you get to skip the step of writing the files to a directory on the server before ultimately sending them elsewhere – no need to worry about running out of disk space if you have a large database.

The newest version of XtraBackup, version 8.0, also allows you to compress the stream so you end up with a smaller file.

Here's an example of a script that will stream the entire backup as a compressed file to an S3 bucket.

#!/bin/bashDATE=`date +%Y%m%d_%H%M%S`xtrabackup --defaults-extra-file=/etc/mysql/conf.d/my.cnf --backup --stream=xbstream --compress | aws s3 cp - s3://$MYSQL_BACKUP_S3_BUCKET/$DATE-dbname.xbstream

The --defaults-extra-file argument specifies the location of my.cnf. By adding your username and password inside of your my.cnf you can avoid passing them as arguments.

[client]
user=root
password=root

The output of XtraBackup is piped to aws-cli which streams it to an S3 bucket. I'm using an environment variable on the server to set the bucket name, but you could just hard-code it in the script.

When the backup is complete, you end up with a single file in your S3 bucket and nothing extra on the server. The .xbstream file extension is used to indicate that this is the xbstream format and not .sql.

Restoring the Database

Restoring from a backup involves a few more steps than the backup itself. I'm not going to go through every step in detail, but here's an overview so you know what's involved.

1. Download the backup from the S3 bucket.
2. Unpack the backup using xbstream -x < dbname.xbstream
3. Prepare the files with xtrabackup --prepare
4. Shut down the MySQL service.
5. Delete the MySQL data directory. (Check /var/lib/mysql)
6. Use xtrabackup --copy-back` to replace the data directory.
7. Ensure that the files have the owner and group "mysql" using chown.
8. Restart MySQL.

If you are limited on disk space, you could also prepare the files on another server or your own local instance of MySQL before uploading them to the server. Do this after shutting down the MySQL service and deleting the data directory to avoid using double the space.

Check out Percona's article on The Backup Cycle - Full Backups