I have recently started working on a project involving the Percona XtraDB cluster in order to integrate it into our production environment.
Now… I have a few issues while testing backup options for the XtraDB cluster.
While restoring a single database created by xtrabackup to a percona cluster with two working databases, recreating the table structure and importing the .ibd and .cfg files then discarding and importing table space for the table.
I noticed that the two nodes that were not bootstrapped were not replicating the data in the table, only the table structure was replicated.
I went over the documentation and notice this:
When running Percona XtraDB Cluster in cluster mode, avoid ALTER TABLE ... IMPORT/EXPORT workloads. It can lead to node inconsistency if not executed in sync on all nodes.
I would like to ask if the only option is just to perform the action on all the nodes?
And the second question I have for you lovely people is…
How will I go about restoring a backup created by xtrabackup/innobackupex to a running cluster?
it seems as if this is not possible as you need to completely clear your data dir and by that destroy the cluster workflow.
what I did was stop node1, node2 node3 clear out their datadir upload the backup and then let them sync with SST while starting the MySQL service on the nodes one at a time.
I would like to know if that is the best practice for a restoration of a full backup created by xtrabackup/innobackupex to a live cluster?
Importing an entire file will only affect the data for the node being restored. PXC only replicate “sql syntax commands” executed on the server, so IMPORT/EXPORT is a limitation for PXC and considered an unsafe operation.
Regarding “doing the same on every node in the topology” that would be unsafe also. After restoring one node (but before restoring the others) if there is activity on the restored table and the other nodes realize it, they will self-detect the data inconsistency and self-shutdown.
Also doing IMPORT/EXPORT is a “data dictionary” operation. Considering that on newer MySQL versions the data dictionary has been reworked and works in a different way means that even though IMPORT/EXPORT might work for certain PXC versions, there is no guarantee that will work on every version nor that I will continue to work in the future. It might even succeed but fail silently.
So if you need to recover a single table using Xtrabackup is to work on one node then make the others SST.
The other possible approach is that you take a logical backup from that specific schema/table and restore it on 1 PXC node that will replicate in real time to the others. Note that a logical backup will take longer compared to a physical backup but is a safe path and with minimal downtime.
Last, remember that PXC aims for data consistency at the cost of performance.
Hi MastaMike. Just to add to what Carlos said above, regarding this question of yours, you can restore the backup to a node, but you must have the grstate.dat file so make sure you include --galera-info in your backup, otherwise that node will SST on startup anyways. Also, make sure the other nodes all have a gcache size which covers the delta between when you took that backup and “right now” or, again, it will SST anyways.