Your Xtrabackup cheat sheet

Posted in: MySQL, Open Source, Technical Track

My goal in this post is to present you with examples of useful operations using xtrabackup, using options which in some cases are not very well documented.

Xtrabackup works by copying your data files on the fly, which results in data that is internally inconsistent as the files are usually being changed. However it can perform crash recovery (–apply-logs option) on the files to make them consistent after. This is possible because while it is copying data files, it is also recording any modifications that happen, by inspecting the redo logs. You can check the official docs for more details on how this works.

Please note the examples I give usually assume you are restoring your data to /var/lib/mysql/ so be careful.

Installation

Easiest way is to install Percona repo and get the latest package from there e.g.:

yum -y install https://www.percona.com/downloads/percona-release/redhat/0.1-3/percona-release-0.1-3.noarch.rpm
yum -y install percona-xtrabackup

Backup user

This creates a user with the minimum required privileges:

CREATE USER 'bkpuser'@'localhost' IDENTIFIED BY 's3cret'; 
GRANT RELOAD, LOCK TABLES, REPLICATION CLIENT ON *.* \
TO 'bkpuser'@'localhost';

Streaming backups

Keep in mind nc command sends the output through the network without any encryption. Also, it is usually a good idea to compress the stream to save space.

Option 1: tar

To have nc listen on a designated port (e.g. 9876) on the destination host run:

nc -l 9876 | tar -ivxf -

On the origin:

innobackupex --stream=tar /tmp | nc desthost 9876
Option 2: xbstream

Using xbstream is similar, in this case I am adding parallel gzip (aka pigz) to the mix:

nc -l 9876 | xbstream -x -C /var/lib/mysql/
innobackupex --stream=xbstream /tmp | pigz -c --fast | nc dest 9876

Parallelising

With this option xtrabackup will read files in parallel which is much faster. This option does not work with the tar method, so you will need to use xbstream:

innobackupex --parallel=16 \
--stream=xbstream \
/tmp > backup.xbs

Compression & encryption

Xtrabackup has built-in options to compress (using quicklz algorithm) and encrypt your backups. Compression needs to happen before encryption to get a good ratio, so pigz is not useful here.

First generate a key to be used for encryption, and store somewhere safe:

echo -n $(openssl rand -base64 24) > /data/key

In this case I am using both compression and encryption but you can choose to remove any:

innobackupex --compress \
--compress-threads=8 \
--encrypt=AES256 \
--encrypt-key-file=/data/key \
--encrypt-threads=8 \
--parallel=8 \
--stream=xbstream \
/tmp > backup.xbs.qp

Decompression & decryption

Oddly, the qpress package is needed for decompression but not for compression. You can get it from the Percona repo.

Decompression needs to happen before the apply logs phase, and is done as follows:

xbstream -x < /backups/backup.xbs.qp -C /var/lib/mysql 
innobackupex \
--decompress \
--decrypt=AES256 \
--encrypt-key-file=/tmp/key \
--parallel=8 \
--use-memory=8G \
/var/lib/mysql

After that is done you can remove any leftover xbcrypt and qpress files, and apply logs normally:

find /var/lib/mysql \( -name "*.xbcrypt" -or -name "*.qp" \) -delete

innobackupex --apply-logs \
--use-memory=8G \
/var/lib/mysql

Incremental backups

It is best to have tracking of changed pages to my.cnf to allow incremental backups to  complete faster. Also if the backups will not be stored locally, we need to pass the --extra-lsndir option pointing to a local path. This way we can keep a copy of the checkpoints file available, which is needed for incremental to work.

Here's how to take an incremental backup (and compress it):

innobackupex --incremental \
--stream=xbstream \
--extra-lsndir=/backup/lsn_dir \
--incremental-basedir=/backup/lsn_dir | 
pigz -c > /backup/inc/inc1.xbs.gz

The restore steps would be:

  1. Decompress the full backup
    unpigz -c full.xbs.gz | xbstream -x -C /var/lib/mysql/
  2. Decompress all the incrementals you want to apply
    unpigz -c inc1.xbs.gz | xbstream -x -C /backup/inc/1/
  3. Apply logs to the base backup, using redo only option
    innobackupex --apply-log \
    --redo-only /var/lib/mysql/ \
    --use-memory=24G
  4. Apply any incrementals using redo only option e.g.
    innobackupex --apply-log \
    --redo-only /var/lib/mysql/ \
    --use-memory=24G \
    --incremental-dir=/backup/inc/1
    innobackupex --apply-log \
    --redo-only /var/lib/mysql/ \
    --use-memory=24G \
    --incremental-dir=/backup/inc/2
  5. After the last incremental has been applied, run apply logs one last time without the redo only option
    innobackupex --apply-log \
    --use-memory=24G \
    /var/lib/mysql
  6. Profit!

Do you have any questions? Let me know in the comments section!

email

Interested in working with Ivan? Schedule a tech call.

Lead Database Consultant

1 Comment. Leave new

Arnoldas Brazys
December 10, 2020 6:53 am

Thanks for info! I am trying out streaming on the fly straight to aws S3 with xbcloud as “aws s3 cp -” times out or gets cancelled for waiting on bigger database.

streaming dump compressing and encrypting on the fly:
xtrabackup –encrypt=AES256 –encrypt-key-file=/tmp/key –backup –compress –stream=xbstream –extra-lsndir=/tmp –target-dir=/tmp | xbcloud put –parallel=8 –storage=s3 –s3-bucket=mybucket test-xbcloud

How do I reverse and restore it on the fly?

Reply

Leave a Reply

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