Hi All,
I hope your well.
I have a question in regards to xtrabackup and the requires of tables with primary keys, I’m pretty sure I read up that primary keys are required for xtrabackup to function but I can’t remember where that is why I have decided to ask here.
I was looking at xtra dbcluster, but that looks like I would have to build a new cluster all together and then migrate data over which wouldn’t be suitable, but I have been advised that xtrabackup it’s self is a great tool for Galera clusters.
Any advice would be awesome, as I need to look at getting this installed asap. Our cluster is monitored through the cluster control free version.
Kindest Regards,
Jammy 
1 Like
Sorry for the bump, I was just wondering if anyone has any advice on this? We have 1 table in our galera cluster with out primary keys, so just need advice as to whether or not xtrabackup requires them.
1 Like
Hi James you might have been reading around PKs in general.
First of all, for PXC to function correctly, you need PKs in place. [URL]Redirecting
Although you reference galera, PXC has that as its foundation.
There’s a relevant discussion here I think: [URL]https://www.percona.com/forums/questions-discussions/percona-xtradb-cluster/52307-primary-key-on-a-galera-cluster[/URL]
It’s a bit old but the SeveralNines blog seems to have some good content related to this [URL]https://severalnines.com/blog/simple-backup-management-galera-cluster-using-s9sbackup[/URL]
Overall, I think we’d advise use of a primary key even if it’s not functional.
If I missed the point let me know and we can take another run at it. Thanks!
1 Like
Ah thank you so much, yeah, I have been trying to get abetter understanding of this for a while now.
I am still quite new to DBs but would love to get more involved with the DBA side of things, that is why I have picked this up. Now, I am able to implement this key I believe with the following command;
alter table oc_collres_accesscache add column id int(10) unsigned primary KEY AUTO_INCREMENT;
My last question if you don’t mind is, do both primary keys on each node need to be the exact same? Or will the above command suffice enough?
Kind Regards,
James
1 Like
Hello @james.shaw ,
primary keys are required for xtrabackup to function
That is not true. xtrabackup does not care about PKs.
Percona XtraDB Cluster (Galera) requires PKs to function most efficiently. You can “upgrade” your existing MySQL to PXC without migrating to a brand new cluster. Simply stop existing mysql, install PXC packages, and then bootstrap the cluster with this original node. You now have a cluster of 1. Install the packages on another mysql host, delete the data directory, start mysql. This new node will copy all the data from node 1 automatically. Now you have a cluster of 2. Repeat for node 3. All done!
1 Like