Apache Tomcat/7, CentOS, AWS RDS Aurora 5.6.10a

AWS provides a cluster CNAME that at any time points to the writer instance of the cluster. e.g. dbname-cluster.cluster-id.us-west-2.rds.amazonaws.com This name is used in the resource url value, and does not change. Once failover completes, the CNAME record then points to the newly promoted writer instance where SQL traffic will continue to work.

The problem as configured is the connection between the application and MySQL instance does not change, so after failover creates problems as the database then is in read-only mode. This requires Tomcat to be restarted. Disabling Java DNS cache by setting networkaddress.cache.ttl=0 in $JAVA_HOME/jre/lib/security/java.security does overcome this issue, as the connector will try again after the failure occurs, and by then connect to the writer instance. A downside is the loss of DNS cache for the application. I'm looking for a method to periodically test the state of the database, and if determined to be read-only, create a new connection to the same URL.

I've read about MySQL, JDBC High Availability and Clustering, but in the context of listing primary and secondary MySQL servers. In my case the server URL never changes, so the method failing over to another server url does not fit. My hopes are to find a method to test the connection, and if found to be read-only, create new connections to the same server url.

  • 1
    I think MariaDB Connector/J is the best solution, see mariadb.com/kb/en/mariadb/… – Federico Sierra Aug 26 '16 at 20:40
  • You could also implement an external dns cache on the local server with a defined timeout (like nscd for example). It's not an elegant solution but it's a quick fix and you can quickly set your preference between how fast failover occurs and how long the entries are cached in one setting. – yoonix Aug 27 '16 at 8:45
  • Thank you Federico Sierra and yoonix. I've not responded as I need to setup a test instance to update the connector, but looking over MariaDB this appears to be a very good move to implement. As there is still some effort to build the test instances, there will still be a delay for my results. The replies are extremely helpful. – slacker Sep 23 '16 at 18:04

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.