I have a node in Percoran Xtradb cluster that I want to change to asynchronous replication due to slowness reasons. I have binary logs enabled on the node that will act as master. I want to know what configuraton changes I will need to make into slave node to shift it to asynchronous mode. I have large database and thats why I want to use xtradb sst method to do initial sync with galera cluster and when this slave node as all the data, we can shift it to asynchronous mode. Mainly the idea is to use SST for initial data sync and then switch to asynchronous mode as SST give smooth initial data sync. Some of the steps that I have in my mind already are the following.
1- Use SST to create slave node.
2- Disable xtradb galera replication on slave node.
3- Make sure master node has binary logs enabled and note down binray logs positon.
4- Use binary logs position in slave node after disabling galera synchronous replication to point it master node binary logs.
Please let me know how I will achieve it. Correct me if you feel there is some issue in this idea.
Async replication and Galera(PXC) replication are different replication method.
What you need to do is re provision the replica using PXB (Percona Xtrabackup, same product used for SST) with the procedure described here: How to setup a replica for replication in 6 simple steps with Percona XtraBackup - Percona XtraBackup
Do note that you won’t be converting the PXC node to async replication, but rather taking the PXC node out of the cluster and setting up an async replica from scratch.
If the replica does not need to be a PXC cluster then you should use Percona Server binaries rather than PXC binaries (same version) and comment all wsrep variables from the config file.
Thanks for reply.
Can you please explain a bit why I cant use exiting PXC node data for slave. It will be quite easy to have the data already and then configure it as slave instead of creating new node from scratch. I am trying to avoid large data restoration if it is possible.
Also, if i remove the wsrep configurations from slave node, wont it work ? I thought that wsrep is kind of extra module that is loaded to achieve synchrnous replication and if it is not loaded it will just act as normal mysql percona mysql node. Please correct me if I am wrong.