Asynchronous Replication Scenario ( Binary Logging Method) :
Primary Node <-----> Secondary Node
Write is enabled on Primary node and Read is enabled on both node.
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.
Which is the best possible method for auto switching ? Also , during switch over , can we have a loss for some events ?