Select Page

Backing Up MySQL Galera Cluster

Author: Wesley Lifford | | August 16, 2016

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.

How to Solve the Oracle Error ORA-12154: TNS:could not resolve the connect identifier specified

The “ORA-12154: TNS:could not resolve the connect identifier specified” Oracle error is a commonly seen message for database administrators.

Vijay Muthu | February 4, 2021

Best RAID For SQL Server | RAID 0, RAID 1, RAID 5, RAID 10

Which RAID should you use with SQL Server? Learn the differences between RAID 0, RAID 1, RAID 5, and RAID 10, along with best practices.

Eric Russo | June 8, 2015

Scripting Out SQL Server Logins, Server Role Assignments, and Server Permissions

This blog reviews how you can generate scripts for SQL server logins, role assignments, and server permissions for a smooth migration.

JP Chen | October 1, 2015

Subscribe to Our Blog

Never miss a post! Stay up to date with the latest database, application and analytics tips and news. Delivered in a handy bi-weekly update straight to your inbox. You can unsubscribe at any time.

Work with Us

Let’s have a conversation about what you need to succeed and how we can help get you there.

CONTACT US

Work for Us

Where do you want to take your career? Explore exciting opportunities to join our team.

EXPLORE JOBS