2

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

2

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.

  • 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 '17 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 '18 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 '18 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 '18 at 20:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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