Data sync between 2 percona xtradb mysql clusters

1

Hi All
Good Afternoon

I have two Percona XtraDB MySQL clusters:

Cluster A:

  • Master: 172.10.10.05
  • Node: 172.10.10.06

Cluster B:

  • Master: 172.10.10.07
  • Node: 172.10.10.08

I am trying to set up bidirectional replication between these two clusters. The configurations I used are as follows:

1. Create Replication Users

On Cluster A:

CREATE USER 'replicator'@'%' IDENTIFIED BY 'password';
GRANT REPLICATION SLAVE ON *.* TO 'replicator'@'%';
FLUSH PRIVILEGES;

On Cluster B:

CREATE USER 'replicator'@'%' IDENTIFIED BY 'password';
GRANT REPLICATION SLAVE ON *.* TO 'replicator'@'%';
FLUSH PRIVILEGES;

2. Configure Cluster A to Replicate to Cluster B

On a node in Cluster A, add the following configuration to the my.cnf or my.cnf.d file:

[mysqld]
server-id=1
log-bin=mysql-bin
binlog-format=ROW
gtid-mode=ON
enforce-gtid-consistency=ON
log-slave-updates=ON

Restart MySQL on all nodes in Cluster A:

systemctl restart mysql

3. Configure Cluster B to Replicate to Cluster A

On a node in Cluster B, add the following configuration to the my.cnf or my.cnf.d file:

[mysqld]
server-id=2
log-bin=mysql-bin
binlog-format=ROW
gtid-mode=ON
enforce-gtid-consistency=ON
log-slave-updates=ON

Restart MySQL on all nodes in Cluster B:

systemctl restart mysql

4. Set Up Replication

On Cluster A, find the current binary log file and position:

SHOW MASTER STATUS;

On Cluster B, execute the following command, replacing MASTER_LOG_FILE and MASTER_LOG_POS with the values obtained from Cluster A:

CHANGE MASTER TO 
MASTER_HOST='172.10.10.07',
MASTER_USER='replicator',
MASTER_PASSWORD='password',
MASTER_AUTO_POSITION=1;

START SLAVE;

On Cluster B, find the current binary log file and position:

SHOW MASTER STATUS;

On Cluster A, execute the following command, replacing MASTER_LOG_FILE and MASTER_LOG_POS with the values obtained from Cluster B:

CHANGE MASTER TO 
MASTER_HOST='172.10.10.05',
MASTER_USER='replicator',
MASTER_PASSWORD='password',
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=157,
MASTER_AUTO_POSITION=1;

START SLAVE;

5. Verify Replication

On Cluster A:

SHOW SLAVE STATUS\G;

On Cluster B:

SHOW SLAVE STATUS\G;

However, when I start the slave, the cluster nodes are not communicating properly; they only synchronize between the masters, but the master nodes are not syncing with each other.

i want to sync cluster A to Cluster B and cluster B to Cluster A is it possible

2

Hi,

I would recommend to have a look on this script which can help you to manager replication between two PXC cluster, there is a detailed description how to setup if you follow these steps the replication should work. Mysql-tools/PXC at master · y-trudeau/Mysql-tools · GitHub

However be careful with active-active replication it might can cause you some headaches and replication can break between clusters causing drift in data on the clusters…

Thanks.

3

Thanks

but we need to setup cluster to cluster sync (DC ↔ DR )

Is it possible to do this type of setup PXC

4

You can either have one big cluster which contains all the nodes from DC and DR , but that could impact the performance or you can use traditional replication between the two clusters and you can try the script what I posted in my previous comment.

5

What I have done… take it as you desire (is not be best practice and it DOES have holes in it)

Make one larger cluster
Say Three in your primary location two in the other location
Make all writes to a single system in the primary location (with fail over order to the other two in the primary location behind say a load balancer or proxy)
modify the quarm weight so that the Primary location only has to talk to the DR location if one of the primarys is down and having issues.

Keep DR (secondary location) up all the time so that it gets followed by all the normal cluster rules

If the primary location goes down (all servers) it kicks to the DR location.
The DR is now a two node cluster (Not Safe I know)
If you have to recover the primary location (SST)
Point the donor to the secondary DR server.
Bring the rest of the Primary servers up just like this (after you get one of the Primary servers in sync you can use the LAN connection for the donor aspect)

Additionally I do Backups on the DR servers so that any potential for a slowdown during backups does not cause a issue to production. It also allows for a geographically diverse backup to happen

Another POTENTIAL option depending on how much data and whatnot that you have automate a backup solution where xbackup takes a dump and it gets restored every xx to the DR system… OR potentially two different clusters and leverage pt-table-sync/checksum All this depends on the requirements you have…

Please understand the various risks and items associated with the above… In my case I am willing to take those risks and understand the pitfalls.