# Data sync between 2 percona xtradb mysql clusters **URL:** \ **Category:** Uncategorized\ **Created:** [July 25, 2024, 10:02am UTC](https://forums.percona.com/t/data-sync-between-2-percona-xtradb-mysql-clusters/31804 "2024-07-25T10:02:14Z")\ **Posts on this page:** 5\ **Page:** 1
**Author:** ![sarath](https://avatars.discourse-cdn.com/v4/letter/s/958977/32.png) [@sarath](https://forums.percona.com/u/sarath)\ **Post date:** [July 25, 2024, 10:02am UTC](https://forums.percona.com/t/data-sync-between-2-percona-xtradb-mysql-clusters/31804/1 "2024-07-25T10:02:14Z")
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: ```auto CREATE USER 'replicator'@'%' IDENTIFIED BY 'password'; GRANT REPLICATION SLAVE ON *.* TO 'replicator'@'%'; FLUSH PRIVILEGES; ``` On Cluster B: ```auto 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: ```auto [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: ```auto 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: ```auto [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: ```auto systemctl restart mysql ``` **4. Set Up Replication** On Cluster A, find the current binary log file and position: ```auto 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: ```auto 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: ```auto 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: ```auto 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: ```auto SHOW SLAVE STATUS\G; ``` On Cluster B: ```auto 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 ---
**Author:** ![Tibor\_Korocz\_Percona](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/tibor_korocz_percona/32/17251_2.png) [@Tibor\_Korocz\_Percona](https://forums.percona.com/u/Tibor_Korocz_Percona)\ **Post date:** [July 25, 2024, 10:35am UTC](https://forums.percona.com/t/data-sync-between-2-percona-xtradb-mysql-clusters/31804/2 "2024-07-25T10:35:07Z")
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](https://github.com/y-trudeau/Mysql-tools/tree/master/PXC) 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. ---
**Author:** ![sarath](https://avatars.discourse-cdn.com/v4/letter/s/958977/32.png) [@sarath](https://forums.percona.com/u/sarath)\ **Post date:** [August 5, 2024, 10:45am UTC](https://forums.percona.com/t/data-sync-between-2-percona-xtradb-mysql-clusters/31804/3 "2024-08-05T10:45:39Z")
Thanks but we need to setup cluster to cluster sync (DC ↔ DR ) Is it possible to do this type of setup PXC ---
**Author:** ![Tibor\_Korocz\_Percona](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/tibor_korocz_percona/32/17251_2.png) [@Tibor\_Korocz\_Percona](https://forums.percona.com/u/Tibor_Korocz_Percona)\ **Post date:** [August 12, 2024, 10:07am UTC](https://forums.percona.com/t/data-sync-between-2-percona-xtradb-mysql-clusters/31804/4 "2024-08-12T10:07:34Z")
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. ---
**Author:** ![meyerder](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/meyerder/32/16994_2.png) [@meyerder](https://forums.percona.com/u/meyerder)\ **Post date:** [August 12, 2024, 5:02pm UTC](https://forums.percona.com/t/data-sync-between-2-percona-xtradb-mysql-clusters/31804/5 "2024-08-12T17:02:45Z")
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.