0

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

Improve this question

1 Answer 1

Reset to default
0

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.)

Improve this answer
5
  • If I set read only at Node A what will happen to all the write and update statements once this node comes up ? Will it get fail or will it get routed to Node B ? Also as you said to set it as default in both cnf. If I will do this then either of node will not accept any write operations..isnt ?
    – simplifiedDB
    Apr 10, 2017 at 19:22
  • Writes will fail. The non-writable master should also be read_only. (That is, until you get in and manually turn it off.)
    – Rick James
    Apr 11, 2017 at 5:23
  • hmm..this doesnt seems to solve this issue . Ok let me give you one example . If i need to perform same thing in oracle , i can stop listeners to listen from outer world.In this way , JBOSS will consider this node as still down but in reality it is up and is syncing with Slave. So is there anything similar in MySQL ?
    – simplifiedDB
    Apr 11, 2017 at 9:40
  • I advise people to avoid dual-Master. It is not as wonderful as one might think.
    – Rick James
    Apr 11, 2017 at 17:48
  • hmmm, still but this is not solution .
    – simplifiedDB
    Apr 13, 2017 at 5:19

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.