How to perform backup on quite active 500G+ MariaDB Galera Cluster?

Question

what would be your preferred method to perform backup on quite active 500G+ MariaDB Galera Cluster?

1. Using mariabackup/innobackupex (fork of Percona Xtrabackup) with throttle. You must think about FLUSH TABLES WITH READ LOCK which can cause problems to whole cluster if lock lasts to long. When using Percona XtraDB Cluster this isn't big problem because you have LOCK TABLES FOR BACKUP.

2. Put node in Desync state, perform backup, and turn off Desync mode after backup finishes. Here potential problem could be if backup takes too long, and it will take some time for a node to catch up.

3. Create Slave and perform backups on Slave. You have to monitor that slave is in sync with master. Maybe perform occasional consistency checks?

4. Use mysqldump. I think taking backup of 500G+ database using mysqldump would take too long.

Interested to hear your opinions.

Regards, Marko

Answer 1

Plan A: Using just the nodes of the cluster:

1. take a node out of the cluster: SET GLOBAL wsrep_desync = ON; SET GLOBAL wsrep_on = OFF; (See wsrep_desync and wsrep_on.)
2. back it up via Xtrabackup (or whatever).
3. add it back to cluster: SET GLOBAL wsrep_on = ON; SET GLOBAL wsrep_desync = OFF;
4. eventually IST will get that node in sync with the others.

Cons: You lose a node for some time -- steps 2 and 4 are time consuming.
Need gcache to be big enough to allow for IST instead of SST.

Plan B: Permanently add a slave to one of the nodes. Take that Slave out of replication when you want to take a backup; then put it back into replication.

Con: Requires another server.
Pros: Not the Cons of Plan A.