We had a mysql multi master with 3 nodes and unfortunately the replication broke. We have some set of tables which has to be in sync with other database servers, so i was looking into Percona-Toolkit for trying out the sync between these 3 servers. I could able to sync the records in the tables between 2 db servers in bidirectional way.
Is there any way to sync bidirectionally with 3 servers using pt-table-sync ? Or any steps involved in syncing three db servers?
Thanks,
Swaroop.
Best Answer
first of all, please have a look at this recent response about multi-master replication, where I remark how likely is this setup to create problems and some alternatives. This was probably a direct or indirect cause of your desync.
Bidirectional sync is supported in pt-table-sync, but not in a replication environment, and only between 2 servers. You must unlink your slaves from the replication process in order to apply it, separately for each pair. This will likely create downtime, at least for your writes. The reason for this is because it is implemented by checking rows that do not exist on the other node, and they are added. It does not handle deletes and complex situations.
Bidirectionally syncing 3 servers automatically is technically impossible, because if the 3 of them have the same row with different data, which one is right? You can still use the tool to help you define your own rules (e.g. tableX in server A is the reference one), or detect differences and apply the changes conditionally (pt-table-sync can log the changes before being applied).
The normal procedure for pt-table-sync is to use it in a single-direction replication, as you usually only have one master "reference" database (again, a problem with multi-master replication), and more reliable sync process is available. It also allows syncing multiple nodes at the same time. I recommend you to explore the options on my first link to avoid these kind of issues in the first place.