Daniel Green's Homepage

Speeding up and compressing MySQL backups using xtrabackup

I love Percona and its MySQL tools.  They have really enabled me to pretend I'm a DBA for much longer than I probably should have.

One of the main Percona tools we use is called "xtrabackup", an online / hot backup tool for MySQL.  This excellent tool, by default, produces backups that are equal in size to the on-disk MySQL files, which makes a lot of sense.  It also runs fairly slowly to avoid overtaxing a critical system like a database server, which also makes sense.

But what if we want to save disk space (and probably network bandwidth, too), and have server resource overhead to spare?  We can compress and parallelize the backup!

xtrabackup --defaults-file=/etc/my.cnf --backup --compress --compress-threads=8 --parallel=8 --stream=xbstream --binlog-info=ON -u user -p | split -d --bytes=50GB - /path/to/backups/20201221.xbstream

This will create (DB_SIZE/50gb) *.xbstream files, which is convenient because we will need multiple files if we want to increase transfer throughput when copying our backup files to S3.  And the same performance enhancement applies when restoring this backup to an RDS instance.

In my experience, the xbstream compression is good for at least a 50% savings in total backup filesize, and doesn't really seem to impact backup performance, especially when combined with the "--parallel" option.

MySQL Percona xtrabackup