Asynchronous Replication Scenario ( Binary Logging Method) :

                         Primary Node <-----> Secondary Node

Write is enabled on Primary node and Read is enabled on both node.

Problem :

     How to do automatic switching once Primary node went down i.e. all write events will point to Secondary Node ? Can it be done by Shell scripting 

Solution i tried :

   1. Automatic switching via JBOSS . but we suspect that during switch some records may get loss

Solutions i haven't tried yet :

   1. DBVIP : - I haven't tried this as per https://dba.stackexchange.com/questions/24549/how-do-i-switch-master-with-slave-and-vice-versa-on-mysql mentioned by Rolando. I haven't tried this because i don't have DBVIP.

   2. MySQLFailOver utilities : Because i am not using GTID replication

   3. I haven't use any 3rd party tool like HA proxy because of additional overhead.  

Question :

Which is the best possible method for auto switching ?
Also , during switch over , can we have a loss for some events ?
  • And the question is...? – Rick James Apr 18 '17 at 16:03
  • Apologize . I have added the required part – Ankit Kapoor Apr 19 '17 at 4:12
  • MySQLFailOver is quite new, so I can't comment on it. I like Galera-based HA. In my opinion, Master-Master is at the bottom of any list of HA solutions. – Rick James Apr 19 '17 at 4:16
  • You must use some 3rd party tool because there is nothing completely builtin, at least not all the way back to the client's connection. – Rick James Apr 19 '17 at 4:17
  • Proxies take a tiny percentage of overhead. If you are that close to running out of steam, you have bigger problems. – Rick James Apr 19 '17 at 4:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.