Mysql – Sync of Master Master replication when Primary node comes up MySQL 5.7 Enterprise

lockingMySQLmysql-5.7mysqld-safereplication

I have a Master (say Node A) – Master (Say Node B) asynchronous replication setup with ROW based logging method.

Node A has been setup as Write/Read node and Node B has been setup as Read Node Only.

From JBOSS , i am doing switch over that , when A node goes down , all the Write operation will get forward to B node and whenever Node A comes up , JBOSS will forward all the Writes to A node.

Now suppose Node A remains down for long time , then all the Writes will go to B node . When A node comes up , it will remain un-sync for a while till it get complete sync with B . While it is getting sync , write operation is also happening on Node A which might result in locking issues as well.

So my question is :

Is there any method in MySQL that when Node A comes up , it will not be accessible by outer application ( like JBOSS , i have mentioned) till it gets complete sync with Node B .
Can we control it via MySQL_Safe mode so that other application can perceive Node A as still down or is there any other method

Best Answer

Do not write to the formerly-down side until replication is "caught up".

In your scenario, the formerly-down Master will eventually catchup. Wait. Only if it can't catch up, or you find corruption of some kind, should you go to the effort of copying the data over and rebuilding. If you want to seriously worry about that, have a Slave hanging off each Master for such recovery purposes.

SET GLOBAL read_only = ON whenever a server is not the writable master. In fact, have that as the default in my.cnf on both servers. That way, if there is, say, a brief power failure that causes the writable master to reatart, you won't be able to write to it until you get in there to see what the situation really is.

"Split brain" is the worst thing that can happen. This is when the network is dead, but both Masters are alive and each thinks that the other Master is dead (because of the network). Both happily take writes that will cause duplicate key problems later. Avoid that at all cost.

In general Master-Master, even when writing to only one Master at any time, is the least desirable HA solution. See "Group Replication" and "Galera" for better solutions. (But not "Fabric", it seems to be dying.)

Related Question