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.