Hi,
I’m new to XtraDB cluster (or any Galera flavor). I’m trying to figure out a backup strategy for a cluster.
My backup schedule would look like this:
- Weekly Full backup with xtrabackup
- Daily differential backup with xtrabackup
- binary log backup every 15 minutes.
Assuming I have a cluster composed of nodes A, B and C, and users may do writes on nodes A and B, on which node should I take the backups?
I was thinking about taking backups on node C. Would I need to to set log_slave_update=1 to have all transactions in the binary log?
If node C goes down and I need to move backup job to node A or B, do I need to start a whole new set of full/diff/binlog backup or full backup from node C can be used a base for differential backup on node A/B? I feel like we can’t mix and match backups and binary logs from different nodes…
Thank you!
Hey @eprevost,
Best practice is to take backups from your writer node. Since you are in a cluster, you can take them from C as well. log_slave_updates=1 should be set, yes, so that Galera-based transactions are re-written to C’s binary log. I believe this is already the default in 8.0.
If C goes down, it would be far easier to start a whole new set of backups from A or B. In theory, you only need 1 set of binlogs because GTIDs help mitigate any filename/position offset issues.
Thank you!
So, as long as log_slave_updates is enabled on all nodes, all transactions will be in binary logs of all nodes with the same GTID, but binary log file names may differ. But could the be in different order? Example: node C has GTID G1 followed by G2 in its binlog, could node A has have G2 then G1 instead?
No, because GTIDs have a sequence to them (the part after the :). They can’t be out of order.
1 Like