I have a three node cluster. Should we be performing XtraDB backups independently on each/all the nodes or designate a specific node ?
Currently all transactions/connections are routed to ONLY node1. I am thinking of offloading the the XtraDB backup to node2 OR node3. But I can see that the binlog files on node1 are bigger in sizes which is as expected. Binlog for the other nodes are minimal. Do you see any issues/gotchas when performing point in time recovery using node2 or node3 full backups and binlog files?
Ok after testing further- looks like the answer in my case is that a point in time recovery will NOT be valid when backup is performed on node2 or node3 because the transactions performed on node1 are not recorded in the binlog files (node2 and node3). So in order to support point in time recovery I need to take the backup on node1 because node1 is where all transactions are performed.
Now how would you handle point in time restore in a typical cluster setup where all transactions are spread out to all 3 nodes?
I have the same set-up on EC2. Our writes are only on node1 and I only take xtrabackup on node3. I do a full and incremental backups plus I also backup binlogs. I tried doing a xtrabackup and did a PITR and it seems to work fine. My test is I created a table on node1, inserted some rows and did a PITR. I saw the created rows and tables in my restored db. What type of testing did you do?
My test was similar to yours. I perform the DML only on Node1. The binlogs on Node2 and Node3 did not capture the DMLs performed on Node1.
In fact I could not find any DMLs in the Node2 and 3 binlogs.