I have two mysql servers in two different places. Both servers must be able to read and write. All tables are designed to based on unique id (UUID) primary key. I want to sync these two databases live using a replication method when networks are available. But both servers must be able to work individually when networks are not available.
Which replication method is suitable for this situation ? If someone has already tried this, please help me to resolve this problem.
I have tried MySQL group replication, but when network disconnected i can only work on one mysql server which is bootstrapped. Othe one doesn't allow writes.
Best Answer
Group replication is not a good match for scenarios where server diverge in time.
What you want is a Master to Master setup, where given two server M1 and M2, when the network is restored, M1 is a replication slave of M2 and M2 is a replication slave of M1.
A crucial details is that both servers must not do conflicting updates to the same data, so you should partition your data accordingly and you can also use auto increment settings to generate different ids on different machines.