3

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

Improve this question

1 Answer 1

Reset to default
3

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.

Improve this answer
5
  • Thank you Rick for a comment. Why do I need to take Slave out of replication when I want to take backup (I'm using Xtrabackup)? If I'm using transaction based replication (GTID) with gtid_strict_mode=ON and Slave in read_only mode do I need to perform consistency checks?
    – Marko Sutic
    Nov 27, 2017 at 17:28
  • @MarkoSutic - It may be safe. However, since it is normally simple, fast, etc, to remove a node and put it back in, I approached the answer that way. (Maybe someone else can verify the need or lack of need for taking it out of the cluster.) Hopefully IST will be used when the node goes back into the cluster.
    – Rick James
    Jan 2, 2018 at 17:05
  • I understand for a Plan A why you want to take node out of the cluster. In my comment I was referring on a Plan B. When I have additional Slave added to one of the nodes and I'm taking backups on the Slave, why do I need to take Slave out of replication when taking backups? If I'm using Xtrabackup for taking backup I think that there is no need for stopping replication. Or am I wrong? Thanks
    – Marko Sutic
    Jan 3, 2018 at 20:40
  • Hanging a slave off one of the cluster's nodes -- Good. Probably do not need to stop replication.
    – Rick James
    Jan 3, 2018 at 20:55
  • I don't think changing the wsrep_on variable is the right thing to do github.com/codership/documentation/issues/122
    – Luke Cousins
    Nov 1, 2022 at 22:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.