I have a primary-primary MySQL server setup that is connected to a load balancer. At one point, a failure occurred to where the replication wasn't happening and data was being written to one server and not the other. We have since fixed the replication issue. However, both servers are missing data that the other server has due to the load balance switching.

Is there a way to sync the missing data between both devices?

  • 1
    You've essentially entered a split brain scenario and this will not be a simple reconciliation. It might be wise to retire one of the nodes from writes and introduce a new replica in it's place. This way you can take your time to reconcile the data. Effort to take missing rows from the read only node could be instigated based on use from the application. If the records are not initially found on the primaries another lookup could be performed on the read-only host and a lazy migration of the data performed on demand. Just an idea. Good luck.
    – eroomydna
    Jul 27 '16 at 23:37
  • 1
    Alas, you have found yet another reason not to use multi-master and write to both master.
    – Rick James
    Jul 29 '16 at 19:48

This may be your best bet: https://www.percona.com/doc/percona-toolkit/2.1/pt-table-sync.html

However, if you have inserted the same unique key (or AUTO_INCREMENT value) on both masters, there will be a mess.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.