MySql Multi Master Active Active Mutli Write

Question

I am establishing Multi Master Replication [All mater can write to their database], Initially I started with two server. After establishing connection and sync data on both servers. There are some questions which are bothering me like

1. If there is connection lost between two server and at that time both updates the same row or create a row with same primary. There will be collision between them and sync will break.
2. Can we implement same things for Multiple Master configuration?
3. Is there any monitoring and conflict resolution tools which can I use?

Answer 1

1 & 2) You should look at [auto_increment_increment] and auto_increment_offset variables. This prevents the two masters from creating the same Primary Key (assuming you are using Auto Increment Values).

3) Not Natively, for this you would need to look at moving to one of the Galera Cluster variants.

As it stands I wouldn't recommend an Active Active environment because you will almost certainly run into problems when the network drops out / replication stops on one or both sides.

Answer 2

Something as simple as a UNIQUE key (not just AUTO_INCREMENT) can cause Active-Active to break. If two clients independently, but simultaneously, access different Masters to insert the same unique key, then... The INSERTs could both succeed, only to get to the other Master and fail there.

For that reason (and others), it is unwise to have both Masters "active".

Galera (PXC, etc) solves the problem, but you must test for errors even after COMMIT.