I’ve been using standard MySQL dumps as backup technique on my VPS for few years. It works fine and backups were usable few times when I needed them. But in other places I’m using xtrabackup. It’s faster when crating backups and a lot faster when restoring them - they’re binary so there is no need to reevaluate all SQL create tables/inserts/etc. Backups also include my.cnf config file so restoring on other machine should be easy.

After I switched from MariaDB to Percona I have Percona repos configured, so I will use latest version of xtrabackup.

apt-get install -y percona-xtrabackup

Prerequisities

xtrabackup requires configured user to be able to make backups. One way is to write user and password in plaintext in ~/.my.cnf. Another is using mysql_config_editor to generate ~/.mylogin.cnf file with encrypted credentials. To be honest I didn’t check what kind of security provides this encryption but it feels better than keeping password in plaintext.

I do not want to create new user for this task - I just used debian-sys-maint user. Check password for this user like this:

grep password /etc/mysql/debian.cnf

Now create encrypted file:

mysql_config_editor set --login-path=client --host=localhost --user=debian-sys-maint --password

Hit enter and copy/paste password. File .mylogin.cnf should be created with binary content. We may check this with:

# mysql_config_editor print
[client]
user = debian-sys-maint
password = *****
host = localhost

Looks OK.

Backuping

Now backup script. I placed it directly in cron.daily dir ex. /etc/cron.daily/zz-percona-backup with content:

#!/bin/bash
DATE=`date +%F-%H%M%S`
DIR=/backup/xtrabackup
DST=$DIR/${DATE}.tar.xz

# this will produce directories with compresses files
# mkdir -p $DST
# xtrabackup --backup --compress --target-dir=$DST

# this will produce tar.xz archives
xtrabackup --backup --stream=tar | xz -9 > $DST

# delete files older than 30 days
find $DIR -type f -mtime +30 -delete

I prefer to have single archive with backup because I’m transferring those files to my NAS (for security). But for local backups directories are more convenient and faster when restoring. Also tar archives have to be decompressed with -ioption external link .

Restoring

First time I saw it it scared me a little but after all worked fine and without problems…

service mysql stop
rm -rf /var/lib/mysql
mkdir /var/lib/mysql

Now prepare backup, if you used directory backups it’s easy:

xtrabackup --decompress --target-dir=/backup/xtrabackup/2016-03-14-214233
xtrabackup --prepare --target-dir=/backup/xtrabackup/2016-03-14-214233
xtrabackup --copy-back --target-dir=/backup/xtrabackup/2016-03-14-214233

But if you used tar archives it’s little more messy… You have to create temporary dir and extract archive there:

mkdir /tmp/restore
tar -xvif /backup/xtrabackup/2016-03-14-214233.tar.xz -C /tmp/restore
xtrabackup --prepare --target-dir=/tmp/restore
xtrabackup  --copy-back --target-dir=/tmp/restore

We have to fix ownership of restored files and db may be started:

chown -R mysql:mysql /var/lib/mysql
service mysql start

If your backup is huge you should reorder commands to shutdown database after backup decompression.

Source:
https://www.percona.com/doc/percona-xtrabackup/2.3/xtrabackup_bin/xtrabackup_binary.html external link
http://dev.mysql.com/doc/refman/5.7/en/mysql-config-editor.html external link
https://www.percona.com/doc/percona-xtrabackup/2.1/innobackupex/streaming_backups_innobackupex.html external link