In my case I have two MySQL servers. One is master and second one is slave.
When slave is not available, my application can read and write from/to master.
When master is not available, assume there is a tool mysqlfailover which successfully nominates a new master (from slave).
It sounds good, but application has master hostname inside the configuration. So it fails to find it.
What are available options to solve this problem? How application can be switched to a new master?
I know that I can switch hostname to point to the new IP, use Kubernetes (StatefulSet) + DB in a container, but anything else as free option on bare metal?
If speaking broadly, how this problem is solved using HA/failover tools?
Thank you
Best Answer
We had faced a similar kind of problem. A Multi-AZ MySQL-RDS provides an automatic failover feature. Everytime a failover occurred, although the URL of the new master would remain the same but still JDBC was not able to connect to the new master.
By default, once a connection to database is made some setups will never refresh DNS entries until the JVM is restarted. This can be solved by setting the networkaddress.cache.ttl parameter of JVM. Setting this value ensures that the DNS entries are refreshed after some interval. In case you are not using Java, try to find an analogous parameter for your connection.
In other words, first ensure that the URL of the new master remains the same(you need to talk to your operations team to understand how it can be done). Once that is done, post failover discovering the new master becomes the responsibility of the application itself which can be achieved by refreshing the underlying DNS entries periodically.