MySql Multi Master Active Active Mutli Write

multi-masterMySQL

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?

Best Answer

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.

Related Question