1

I tried to set up Master <-> Master replication on production and some people they tell me that is not a good option.

I want to know why is not a good option and what is the problem that may find? :)

Mysql version: 5.7.23

mysql config:

auto-increment-increment = 2
# 1 Node
auto-increment-offset = 1
# 2 Node
auto-increment-offset = 2
2

The autoincrement% settings you mentioned take care of AUTO_INCREMENT values, but do not handle other UNIQUE KEYs. Keep in mind that if a dup key error occurs, replication is stopped until a human intervenes.

Depending on how the code (router, proxy, etc) picks which Master to write to, you may have the "critical read" problem. This is where, say, someone posts something, then goes to another web page and expects to see the results of it. But the read goes to another machine and the results have not [yet] arrived because of replication delay.

Automatic failover is a big challenge. It is probably best left to be manual. If the network dies, you could have the "split brain" problem. In this case it is unsafe to failover because machine can't see the other. If they both blindly thing the other is dead, and take control, you could get data conflicts that are 'impossible' to fix.

Some of the issues are avoided by designating one of the Master as active and having the other be readonly.

After all, every write to a Master is also performed on every Slave (including the other Master in a dual-Master setup). That is, there is essentially no "write scaling" by having dual-Master. There is some "read scaling".

What is your goal of picking M-M? Scaling? HA? Disaster protection? Facilitate certain schema changes? Other? Are the two Masters located next to each other? On the same power source?

One of my points is that M-M provides limited benefits. And you may have to do other things to really achieve them. (Separate data centers, careful routing of queries to servers, etc.)

A number of the issues are lessened if you go to one of the "cluster solutions" (Galera or Group Replication). They require 3 servers, preferably in 3 geographic locations. But they handle split brain, automatic failover, and some other issues.

| improve this answer | |
  • For the original question poster, check also this other recent question one someone suffering issues with a M-M replication, and my answers: dba.stackexchange.com/questions/223337/… – jynus Nov 28 '18 at 17:15
  • Thanks, the two Server is for 2 project different that they need to write and read from the db. Galera i don't know if is a good idea to be on 3 geographic locations because it takes more time to approve be all three and then insert in the db. Correct me if i make i mistake about that :P :) – pioupiou Nov 30 '18 at 8:11
  • @cdemet - It is a choice you have to make -- enhanced disaster protection via 3 locations versus faster. And... How much faster depends on a lot of things. Some systems don't see enough slowdown to worry about. – Rick James Nov 30 '18 at 22:06

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.