Creating a Asynchronous replica from an existing PXC

MySQL & MariaDB Percona XtraDB Cluster 5.x
1

Our current setup is Mysql Server 5.5 as MASTER → asynchronous replication → 3 Node PXC. We are using the PXC as read only replicas right now. The PXC is receiving all updates via asynchronous replication from the existing MASTER.

I need to create a new Asynchronous slave in our office, which we use for reporting but I want it’s Master to be one of the cluster members and not the existing master as that master will be shutdown once we migrate writes to the PXC.

I haven’t seen any documentation on this setup or how to create a asynchronous replica from an existing PXC. Of note, I have an existing replica from the old master already in the office. I would love to essentially use a change master statement if possible and not recreate it from scratch as it is quite large.

Any idea how to get this done?

Many thanks.

KB

2

The PXC node you want to master from obviously needs log-bin, log-slave-updates and everything else you’d normally do to setup a master.

Making a slave of it is just like making a slave of any other node: clone the master, build the slave. If you backup a cluster node, be sure it do it in a way that doesn’t invoke flow control on the cluster: [url]http://www.mysqlperformanceblog.com/2013/10/08/taking-backups-percona-xtradb-cluster-without-stalls-flow-control/[/url]

If you are migrating an existing slave of MASTER to become a slave of a PXC node without re-clone, you’d need to:

a) stop writes on MASTER, wait for PXC to catch up
b) CHANGE MASTER on slave to new PXC node’s current master binlog position
c) restart writes on MASTER

If you can’t stop writes on the MASTER, then you’ll probably have to reclone your slave.

3

how to configure Mysql Server 5.5 as MASTER asynchronous replication with 3 Node of PXC.

4

Pretty much the same way, except you setup one one in the cluster as a slave to your master with CHANGE MASTER TO. The slave in question needs log-slave-updates to replicate changes it sees from the master to the rest of the cluster.

5

Jay, do you happen to know if there there an existing write up on how to accomplish this with PXC? I have one stack set by Marco, but looking to set up a second set of stacks as well.

6

No jay i don’t want like this i need suppose i have one existing mysql server that working fine now i configure the PXC and its also working fine …but i need to configure the this that is… i need from EXISTING Mysql server data----to------PXC…thats means Existing mysql server is working in front end and pxc will work on back end…it means all entries come in existing mysql and its will diliver to pxc…is is possiable…

7

can you please reply for above mention problem/…

8

Fine, you just backup the existing mysql server and restore that on your first cluster node (the node you bootstrap). After the cluster is setup, one node in the cluster must replicate from the existing master (standard async mysql replication) as I mentioned above.

This is very similar to any HOWTO you may find on the internet about setting up a mysql slave. I cannot write you a full set of steps here.

9

when i am putting the the master entries in my.cnf file of pxc node the service cannot be up…here it is my.cnf file
[mysqld]

skip-name-resolve

datadir=/var/lib/mysql
user=mysql

Path to Galera library

wsrep_provider=/usr/lib64/libgalera_smm.so

Cluster connection URL contains IPs of node#1, node#2 and node#3

wsrep_cluster_address=gcomm://192.168.3.248,192.168.8.23,192.168.8.22

In order for Galera to work correctly binlog format should be ROW

binlog_format=ROW

MyISAM storage engine has only experimental support

default_storage_engine=InnoDB

This is a recommended tuning variable for performance

innodb_locks_unsafe_for_binlog=1

This changes how InnoDB autoincrement locks are managed and is a requirement for Galera

innodb_autoinc_lock_mode=2

Node #3 address

wsrep_node_address=192.168.8.23

Cluster name

wsrep_cluster_name=my_centos_cluster

SST method

wsrep_sst_method=xtrabackup

#Authentication for SST method
wsrep_sst_auth=“sstuser:s3cret”

max_allowed_packet=64

server-id = 2

master-host=192.168.3.215
master-connect-retry=60
master-user=sk
master-password=centos
relay-log = /var/lib/mysql/mysql-relay-bin
relay-log-index = /var/lib/mysql/mysql-relay-bin.index
log-error = /var/lib/mysql/mysql.err
master-info-file = /var/lib/mysql/mysql-master.info
relay-log-info-file = /var/lib/mysql/mysql-relay-log.info
log-bin = mysql-bin

10

hi,
can we change the data location DIR…if yes how?

please reply