Backup Percona XtraDB Cluster or Galera Cluster with TwinDB

To backup my Percona XtraDB Cluster, which is running galera3 for write-set replication, I use the tool TwinDB backup. This backup tool uses Percona XtraBackup to create, store and restore full and incremental backups, compress backups and encrypt backups.

I use HA-Proxy to load balance the Percona XtraDB cluster nodes and to automatically fail-over if a node enters the desynced state or is shut down. You can read everything about installing a Percona XtraDB Cluster with HA-Proxy Load Balancer in my previous blog post. Why I choose for TwinDB is because it uses the global 'wsrep_desync' variable to move the node into Donor/Desynced mode and disables flow control. When a node enters the desynced state, the node is removed from the rotation and I can backup the server without any interruption in my production environment.

The installation of TwinDB is easy:

$ curl -s https://packagecloud.io/install/repositories/twindb/main/script.rpm.sh | sudo bash
$ yum install twindb-backup

If you have an older installation of Percona XtraBackup, you have to remove that package first:

$ yum remove percona-xtrabackup

In my case, we're still using MySQL 5.6, so I have to use an older version of TwinDB:

$ yum install twindb-backup-2.12.0

The latest MySQL backup is stored locally, so we have to create a directory for that:

$ mkdir -p /var/backups/mysql

The configuration is stored in /etc/twindb/twindb-backup.cfg. All options are described in the TwinDB documentation located at readthedocs.io. I'm using the following configuration:

# NOTE: don't quote option values
# What to backup
[source]
backup_dirs=/etc
backup_mysql=yes

# Destination
[destination]
# backup destination can be ssh or s3
backup_destination=ssh
keep_local_path=/var/backups/mysql

[ssh]
# SSH destination settings
backup_host=127.0.0.1
backup_dir=/tmp/backup
ssh_user=root
ssh_key=/root/.ssh/id_rsa

[mysql]
# MySQL
mysql_defaults_file=/etc/twindb/my.cnf
full_backup=daily

[retention]
# Remote retention policy
hourly_copies=24
daily_copies=7
weekly_copies=4
monthly_copies=3
yearly_copies=0

[retention_local]
# Local retention policy
hourly_copies=1
daily_copies=1
weekly_copies=0
monthly_copies=0
yearly_copies=0

[intervals]
# Run intervals
run_hourly=yes
run_daily=yes
run_weekly=yes
run_monthly=yes
run_yearly=no

The `/etc/twindb/my.cnf` file doesn't exist by default. You have to create it and store the MySQL credentials for the backup user in there:

[client]
user="<user>"
password="<password">

If you don't have a backup user yet, create one:

GRANT ALL ON *.* TO 'xtrabackup'@'127.0.0.1' IDENTIFIED BY '<password>';

Change the /etc/cron.d/twindb-backup to change the email address of the monitor e-mail. Add MAILTO=<email address> to the cron file. You can also change when the cronjobs are executed if you want.

Check if the twindb-backup is configured correctly:

$ twindb-backup status

When everything is configured correctly you will receive the status of the twindb backup:

{
    "daily": {},
    "hourly": {},
    "monthly": {},
    "weekly": {},
    "yearly": {}
}

Run twindb-backup backup hourly to start the hourly backup.

$ twindb-backup backup hourly

Happy backuping!