1

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

2 Answers 2

1

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.

1
  • 1
    BTW you only need that when your Java application installs a SecurityManager. Otherwise the default is typically below a minute. If you use a Failover VIP DNS Cache also won’t be an issue
    – eckes
    Apr 3, 2019 at 4:12
1

You can have multiple host:port entries with comma separated in the URL.

For more details check the connector/J documentation https://dev.mysql.com/doc/connector-j/8.0/en/connector-j-config-failover.html or if you prefer a free client https://mariadb.com/kb/en/library/failover-and-high-availability-with-mariadb-connector-j/

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.