Backing Up MySQL Galera Cluster

MySQL Galera Cluster backups are very important — even with multiple copies of the dataset in the cluster. Deriving a backup strategy that doesn’t affect database performance or lock tables can be challenging. You require a backup strategy that enables backing up the Galera Cluster without affecting the applications, defines when to use a specific backup type (full, differential or incremental), and defines the exact backup method (mysqldump or Percona XtraBackup).

For more details on Galera clustering, see the recently released Datavail’s whitepaper, Why Choose Galera-Based Clustering Solution for MySQL, which illustrates the benefits, general guidelines and best practices of implementing the Galera Cluster.

This blog post discusses three main methods of backing up the Galera Cluster, as listed below:

1. Disconnecting a node for backup
2. Using SST script
3. Using Percona XtraBackup

Before you perform a backup of Galera Cluster, here are some of the best practices to follow:

• Ensure all the Galera Cluster nodes are updated consistently
• Isolate a reference node for backups
• Assign a Global Transaction ID (GTID) with the backup

Please note that GTID marks position/state in the transaction stream. Backups with known GTID are usually able to use IST when joining new nodes.

Disconnecting a node for backup

In this case, you have to isolate a node for backup from the cluster being backed up. After disconnecting the backup node from the cluster group, read the GTID ‘from’ status and assign to the backup.

Using SST script

Donor mode offers an isolated processing environment, therefore a special SST script is used to create a backup in the donor node. Garbd is used to trigger the donor node by running the wsrep_sst_backup.sh script. SST node provisioning is similar to a full data backup since a full copy of database sets is created while associating with the GTID database state.

Upon reaching a well-defined point where no changes are happening to the database, the SST backup script is run passing the GTID corresponding to the current database state. Given that replication across the nodes ensures the same data, running the SST backup script on one node backs up the data on all nodes within the same Galera Cluster.

Some of the benefits of backing up using an SST script are as follows:

• Node is desynchronized from the cluster to avoid affecting performance during the backup process
• The Galera Cluster is aware of the node carrying out the backup and cannot select the node as another node’s donor
• The node associates GTID with the backup
• The node initiates backup at well-defined points

Using Percona XtraBackup

Percona XtraBackup is Percona’s hot backup method, usable anytime due to its simplicity and efficiency. XtraBackup can back up databases comprising XtraDB, InnoDB and MyISAM tables. During the backup process, the database is not locked and has better restoration time in comparison to mysqldump.

Percona XtraBackup supports two backup types:

• Full Backup: Backs up the entire database
• Incremental Backup: Backs up data that has changed since the last backup

In summary, it’s highly recommended to do database backups with an associated GTID, otherwise they will be insufficient to recover a node to a well-defined state within the Galera Cluster. Also, make sure the backup operation does not affect/block the clustering process.

You can adopt a backup system of XtraBackup, where full backup is done once a week and incremental backups can be done daily. And, of course, don’t forget to back up your database before making any significant changes.