I have a new production 3-node Galera (MariaDB 10.2) cluster, and am looking for advice on backups. My current plan is twofold:

  1. Replicate one node to a non-clustered slave, and schedule mysqldumps on the slave.

  2. Do full VM backups of one Galera node with a pre-freeze / post-thaw script which stops and then restarts mysqld.

I am particularly interested for feedback on the second point (VM backup). Is this an acceptable strategy?

Thanks, Sera

  • State the goals of the backup. – Rick James Nov 9 '17 at 18:55
  • Are the 3 nodes in 3 separate datacenters? If not... Consider (3) Async replicate to another 3-node cluster. Then you could failover to it if the first cluster totally dies? – Rick James Nov 9 '17 at 18:58
  • Rick - the 3 nodes are in the same data center, but I'd like to extend the fault-tolerance to our second data center. I was considering either your suggestion async to another cluster, or even a single cluster with nodes in each data center. We have a Gb fiber p2p link between the data centers. I do realise regarding this last suggestion, that I would also need a node at a 3rd DC, which we don't have, to prevent split-brain. Could use an AWS instance for this node, but would not then have the Gb link. I'm currently investigating any options for a "non-data" node which can act as an aribiter? – sera4000 Nov 10 '17 at 16:05
  • 1
    Could use a garbd in AWS -- to minimize cost. How much of the Gb link are you really using? That sounds like overkill. – Rick James Nov 10 '17 at 16:08
  1. This would work, although you might want to consider a physical backup solution with percona-xtrabackup (innobackupex) or the new mariabackup. Depending on the load on your cluster, you can even do these physical backups directly on one of the cluster nodes. (Though in my experience, xtrabackup does lock (FLUSH TABLES WITH READ LOCK) for about 15-20 seconds during backup of our 30G database.)
  2. I don't have experience with this, but it sounds like it should work, obviously provided that when you restore from this backup you bootstrap the cluster from this node.
  • I think mariabackup is a fork of xtrabackup though, so might have the same lock issue as xtrabackup. Depending on your DB availability requirements, this may or may not be an issue. – dbdemon Nov 10 '17 at 15:40
  • ... but the lock issue shouldn't affect the cluster, if xtrabackup is run on an off-cluster slave node, correct? – sera4000 Nov 10 '17 at 15:54
  • Correct, then it's not a problem. – dbdemon Nov 10 '17 at 16:11

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.