Multi-master support in MySQL Connector/Java

MySQL Connector/Java has long had support for replication-aware deployment, allowing a single logical Connection object to effectively “pool” connections to both a master and (potentially multiple) slaves.  This allowed scale-out of read load by distribution of read traffic to slaves, while routing write load to the master.  The JDBC specification provides a nice hook to know what’s read-only traffic – Connection.setReadOnly().  When a boolean value of true is passed, a ReplicationConnection will route further commands a selected slave instance, while values of false trigger routing to the master.  This is sufficient for many simple replication topographies, but not all – most notably, it has been difficult to handle multi-master deplyment.  MySQL Connector/Java 5.1.27 aims to solve that, and a number of related problems.

Allowing multiple masters

As noted above, ReplicationConnection provided support for replication-aware topographies, and it could be initialized using a JDBC URL as follows:

jdbc:mysql:replication://master:3306,slave1:3306,slave2:3306/db

Technically, a multi-master replication topography is the same – from a connector’s point of view – as a load-balanced connection, which MySQL Connector/Java also supports. If you can distribute both read and write load across all hosts, a load-balanced deployment is an ideal solution.  But when you have multiple masters (typically for HA, rather than scale-out) and replication spokes from each master to scale read traffic, you could not get native support in MySQL Connector/Java previously.

Just like ReplicationConnection uses a load-balanced connection internally for managing slave connections, MySQL Connector/Java 5.1.27 now uses a load-balanced connection internally for management of the master connections.  That means that ReplicationConnection, when configured to use multiple masters, exposes the same options to balance load across master hosts.  The default behavior described here applies, as does the ability to enable this when auto-commit is turned on – and you can always write your own load-balancing host-picking extension.

URL syntax

The URL syntax for ReplicationConnection definition described above only works when we can assume that the first (and only the first) host is the master.  Supporting deployments with an arbitrary number of masters and slaves requires a different URL syntax that allows for properties which apply to specific hosts.  Fortunately, this URL syntax already exists in Connector/Java as part of IPv6 support.  Here’s what the manual says about this syntax:

IPv6 Connections

For IPv6 connections, use this alternative syntax to specify hosts in the URL, address=(key=value). Supported keys are:

  • (protocol=tcp), or (protocol=pipe) for named pipes on Windows.
  • (path=path_to_pipe) for named pipes.
  • (host=hostname) for TCP connections.
  • (port=port_number) for TCP connections.

For example:

jdbc:mysql://address=(protocol=tcp)(host=localhost)(port=3306)(user=test)/db

Any other parameters are treated as host-specific properties that follow the conventions of the JDBC URL properties. This now allows per-host overrides of any configuration property for multi-host connections (that is, when using failover, load balancing, or replication). Limit the overrides to user, password, network timeouts and statement and metadata cache sizes; the results of other per-host overrides are not defined.

Added to the list of properties as of version 5.1.27 is (type=[master|slave]).  So defining a multi-master replication-aware JDBC connection URL might result in the following:

jdbc:mysql://address=(type=master)(host=master1host),address=(type=master)(host=master2host),address=(type=slave)(host=slave1host)/db

For single-master replication topographies, the legacy URL syntax remains fully supported.

Flexible handling of no masters

The legacy behavior of ReplicationConnection was problematic to some, as new Connection objects could not be created when the master was unavailable.  Interestingly, a primary purpose of ReplicationConnection was to support HA deployments and automatic failover to slaves when the master was unavailable; the behavior which insisted upon an available master connection at startup time was inconsistent with that objective.

Starting in MySQL Connector/Java 5.1.27, users may specify the property, allowMasterDownConnections=true.  This allows Connection objects to be created even though no master hosts are reachable.  Such Connection objects will report they are read-only, and isMasterConnection() will return false.  The Connection will test for available master hosts when Connection.setReadOnly(false) is called, throwing a SQLException if it cannot establish a connection to a master, or switching to a master connection if the host is available.

Fixing autoReconnect

A common complaint in dealing with load-balancing connections in Connector/Java is that exception handling can be fairly heavy-handed.  When a new connection is selected as part of rebalance operations, Connector/Java can be instructed to check the validity of the newly-selected connection.  If it fails, the load-balanced Connection closes up shop, and no further operations are allowed.  With MySQL Connector 5.1.27, setting either autoReconnect or autoReconnectForPools properties to true will cause load-balanced connections to attempt to reconnect on the next usage after an Exception is thrown.  The same caveats that apply to auto-reconnect in general apply here as well.

This allows load-balanced connection behavior to be consistent with the legacy behavior when a replication master goes offline, but it also provides tangible benefits to users of load-balanced connections who want a more forgiving experience when servers go offline.

 

9 thoughts on “Multi-master support in MySQL Connector/Java

    1. Hi Neal,

      Sorry that this wasn’t clear – you absolutely can use it with IPv4 addresses. The important point is that we leverage the IPv6-style address definition to define whether each host is a master or a slave. The following would work just fine, for example:

      jdbc:mysql://address=(protocol=tcp)(host=192.168.1.1)(port=3306)(type=master),address=(protocol=tcp)(host=192.168.1.2)(port=3306)(type=slave)/db

      I hope that helps clarify.

      Todd

  1. Hello Todd,

    A couple questions with regards to new multi-master functionality.

    1. How to construct url for a multi-master setup ?

    The docs

    http://dev.mysql.com/doc/connector-j/en/connector-j-master-slave-replication-connection.html

    describe multi-master setup on the “Replication Connection” page, which implies using

    master.hibernate.connection.driver_class= com.mysql.jdbc.ReplicationDriver

    and

    url syntax “jdbc:mysql:replication ….”

    however using this Driver and Syntax with 2 masters yields the error

    “Must specify at least one slave host to connect to for master/slave replication load-balancing functionality”

    so what is the url/driver to use to get multi-master and have JMX exposure of status information ?

    2. multi-master does not appear to be loadbalanced

    The docs imply multi-master is load-balanced, but using the url

    master.hibernate.connection.url=jdbc:mysql//address=(protocol=tcp)(user=sa)(password=moonwalk)(host=host1)(port=3306)(type=master),address=(protocol=tcp)(host=host2)(port=3306)(type=master)/db?profileSQL=false&autoReconnect=true&loadBalanceEnableJMX=true

    only keeps connections to host1, it appears to try and initially connect to host2, but none of the c3p0 connections are physically to the 2nd host. Is this the correct behavior?

    Cheers,

    -Trevor

    1. Hi Trevor,

      If you are doing a straight multi-master (all servers can handle both read and write operations), you can simply use the load-balanced option (jdbc:mysql:loadbalance://host1,host2/db). The key to using the replication or loadbalancing-aware features is to use the correct prefix. The example URL cited in your second point is actually doing neither – that’s actually the failover support (where the connection is pegged to host1 until it becomes unavailable, then uses host2 until recovery). I think there’s some confusion of these terms in the documentation, and I’ll work with that team to sort that out.

      I thought about the Exception you cite in your first point when I added multi-master support to the replication driver. I decided not to relax that existing check in case it’s needed for backwards compatibility. I’m making some changes for the next release which may prompt removal, all the same.

      Let me know if you run into any more issues!

      Todd

  2. I found that in order to get “straight multi-master” to work I had to use a connection string like this:

    “jdbc:mysql://address=(protocol=tcp)(host=127.0.0.1)(port=10160)(type=master), “+
    “address=(protocol=tcp)(host=127.0.0.1)(port=56222)(type=master)/test” +
    “?failOverReadOnly=false&autoReconnect=true”

    The “failOverReadOnly=false” part is pretty critical, since otherwise you don’t get to write to your other master after failing over.

    One thing I haven’t been able to figure out is how to get the connection to fail back to the original master after it’s become available again. Perhaps that’s not possible at all.

  3. In chapter “Flexible handling of no masters” it is written that
    The Connection will test for available master hosts when Connection.setReadOnly(true) is called, throwing a SQLException if it cannot establish a connection to a master.
    Should be
    … when Connection.setReadOnly(false) is called…, right?

  4. Hi Todd,
    I used the multi-master url to connect to my dbs.
    but the application was blocked when getting the connection.
    can you figure it out for me?

    Thx,
    Darren

    1. Hi Darren,

      Can you explain what happens when the application is “blocked”? Is an Exception raised? If so, please provide the error message and any stack trace.

Leave a Reply

Your email address will not be published. Required fields are marked *

Time limit is exhausted. Please reload CAPTCHA.