0

I have 3 nodes 'master master' cluster and using PXC 5.6

But I am only using a single node for both read and write.

Before using pxc, i was using standalone Mariadb 5.5 and I was taking backup using xtrabackup.

My question is, how should I take backup of PXC? Should i take full and incremental backup for the node which is NOT used for read and write without any additional precautions just like i was taking for mariadb 5.5?

For e.g: xtrabackup --backup --target-dir=/data/backup

Is there any officially recommended way to safely take backups of pxc?

I want to take backup so that if there is database corruption, I can start my cluster from scratch using that backup.

1

Here's another option:

mysql> SET GLOBAL wsrep_desync=1;
shell# <execute backup>
mysql> SET GLOBAL wsrep_desync=0;

Just make sure to take the backup from the non-write node and no traffic is going through the node that's running XtraBackup. Test first on non-production systems.

https://www.percona.com/doc/percona-xtradb-cluster/5.6/wsrep-system-index.html#wsrep_desync

0

One way to take a backup of PXC or Galera:

  1. Remove a node from the cluster. (not the one you are writing to)
  2. Take the backup on it.
  3. Put the node back into the cluster. (It will automatically resync.)

5.5 is rather out of date; recommend you plan on moving forward.

  • thanks Rick. I have already upgraded to 5.6 .. If I do not remove the node before taking a backup then what kind of problems I might face or any risk? Also, can you tell me how to safely take node out of the cluster? – Ted Khi Dec 21 '18 at 6:24
  • Some people also recommend delayed slave technique in master-master scenario. Do you think it is helpful? – Ted Khi Dec 21 '18 at 6:31
  • You can hang a delayed Slave off any of the PXC nodes. It protects against one thing: a Delete or Drop that should have been done (either accidental or malicious). And it helps only if you catch it before the delay runs out. – Rick James Dec 21 '18 at 6:43
  • Can you please also tell me how to safely take node out of cluster for backup without affecting other nodes ? Should it be wsrep_desync ON? And what are the cons of not removing the node before backup? – Ted Khi Dec 21 '18 at 6:50

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.