Chapter 9 Multi-Host Connections

Table of Contents

9.1 Configuring Server Failover
9.2 Configuring Client-Side Failover when using the X Protocol
9.3 Configuring Load Balancing with Connector/J
9.4 Configuring Master/Slave Replication with Connector/J
9.5 Advanced Load-balancing and Failover Configuration

The following sections discuss a number of topics that involve multi-host connections, namely, server load-balancing, failover, and replication.

Developers should know the following things about multi-host connections that are managed through Connector/J:

9.1 Configuring Server Failover

MySQL Connector/J supports server failover. A failover happens when connection-related errors occur for an underlying, active connection. The connection errors are, by default, propagated to the client, which has to handle them by, for example, recreating the working objects (Statement, ResultSet, etc.) and restarting the processes. Sometimes, the driver might eventually fall back to the original host automatically before the client application continues to run, in which case the host switch is transparent and the client application will not even notice it.

A connection using failover support works just like a standard connection: the client does not experience any disruptions in the failover process. This means the client can rely on the same connection instance even if two successive statements might be executed on two different physical hosts. However, this does not mean the client does not have to deal with the exception that triggered the server switch.

The failover is configured at the initial setup stage of the server connection by the connection URL (see explanations for its format here):

jdbc:mysql://[primary host][:port],[secondary host 1][:port][,[secondary host 2][:port]]...[/[database]]»

The host list in the connection URL comprises of two types of hosts, the primary and the secondary. When starting a new connection, the driver always tries to connect to the primary host first and, if required, fails over to the secondary hosts on the list sequentially when communication problems are experienced. Even if the initial connection to the primary host fails and the driver gets connected to a secondary host, the primary host never loses its special status: for example, it can be configured with an access mode distinct from those of the secondary hosts, and it can be put on a higher priority when a host is to be picked during a failover process.

The failover support is configured by the following connection properties (their functions are explained in the paragraphs below):

  • failOverReadOnly

  • secondsBeforeRetryMaster

  • queriesBeforeRetryMaster

  • retriesAllDown

  • autoReconnect

  • autoReconnectForPools

Configuring Connection Access Mode

As with any standard connection, the initial connection to the primary host is in read/write mode. However, if the driver fails to establish the initial connection to the primary host and it automatically switches to the next host on the list, the access mode now depends on the value of the property failOverReadOnly, which is true by default. The same happens if the driver is initially connected to the primary host and, because of some connection failure, it fails over to a secondary host. Every time the connection falls back to the primary host, its access mode will be read/write, irrespective of whether or not the primary host has been connected to before. The connection access mode can be changed any time at runtime by calling the method Connection.setReadOnly(boolean), which partially overrides the property failOverReadOnly. When failOverReadOnly=false and the access mode is explicitly set to either true or false, it becomes the mode for every connection after a host switch, no matter what host type are being connected to; but, if failOverReadOnly=true, changing the access mode to read/write is only possible if the driver is connecting to the primary host; however, even if the access mode cannot be changed for the current connection, the driver remembers the client's last intention and, when falling back to the primary host, that is the mode that will be used. For an illustration, see the following successions of events with a two-host connection.

  • Sequence A, with failOverReadOnly=true:

    1. Connects to primary host in read/write mode

    2. Sets Connection.setReadOnly(true); primary host now in read-only mode

    3. Failover event; connects to secondary host in read-only mode

    4. Sets Connection.setReadOnly(false); secondary host remains in read-only mode

    5. Falls back to primary host; connection now in read/write mode

  • Sequence B, with failOverReadOnly=false

    1. Connects to primary host in read/write mode

    2. Sets Connection.setReadOnly(true); primary host now in read-only mode

    3. Failover event; connects to secondary host in read-only mode

    4. Set Connection.setReadOnly(false); connection to secondary host switches to read/write mode

    5. Falls back to primary host; connection now in read/write mode

The difference between the two scenarios is in step 4: the access mode for the secondary host in sequence A does not change at that step, but the driver remembers and uses the set mode when falling back to the primary host, which would be read-only otherwise; but in sequence B, the access mode for the secondary host changes immediately.

Configuring Fallback to Primary Host

As already mentioned, the primary host is special in the failover arrangement when it comes to the host's access mode. Additionally, the driver tries to fall back to the primary host as soon as possible by default, even if no communication exception occurs. Two properties, secondsBeforeRetryMaster and queriesBeforeRetryMaster, determine when the driver is ready to retry a reconnection to the primary host (the Master in the property names stands for the primary host of our connection URL, which is not necessarily a master host in a replication setup):

  • secondsBeforeRetryMaster determines how much time the driver waits before trying to fall back to the primary host

  • queriesBeforeRetryMaster determines the number of queries that are executed before the driver tries to fall back to the primary host. Note that for the driver, each call to a Statement.execute*() method increments the query execution counter; therefore, when calls are made to Statement.executeBatch() or if allowMultiQueries or rewriteBatchStatements are enabled, the driver may not have an accurate count of the actual number of queries executed on the server. Also, the driver calls the Statement.execute*() methods internally in several occasions. All these mean you can only use queriesBeforeRetryMaster only as a coarse specification for when to fall back to the primary host.

In general, an attempt to fallback to the primary host is made when at least one of the conditions specified by the two properties is met, and the attempt always takes place at transaction boundaries. However, if auto-commit is turned off, the check happens only when the method Connection.commit() or Connection.rollback() is called. The automatic fallback to the primary host can be turned off by setting simultaneously secondsBeforeRetryMaster and queriesBeforeRetryMaster to 0. Setting only one of the properties to 0 only disables one part of the check.

Configuring Reconnection Attempts

When establishing a new connection or when a failover event occurs, the driver tries to connect successively to the next candidate on the host list. When the end of the list has been reached, it restarts all over again from the beginning of the list; however, the primary host is skipped over, if (a) NOT all the secondary hosts have already been tested at least once, AND (b) the fallback conditions defined by secondsBeforeRetryMaster and queriesBeforeRetryMaster are not yet fulfilled. Each run-through of the whole host list, (which is not necessarily completed at the end of the host list) counts as a single connection attempt. The driver tries as many connection attempts as specified by the value of the property retriesAllDown.

Seamless Reconnection

Although not recommended, you can make the driver perform failovers without invalidating the active Statement or ResultSet instances by setting either the parameter autoReconnect or autoReconnectForPools to true. This allows the client to continue using the same object instances after a failover event, without taking any exceptional measures. This, however, may lead to unexpected results: for example, if the driver is connected to the primary host with read/write access mode and it fails-over to a secondary host in real-only mode, further attempts to issue data-changing queries will result in errors, and the client will not be aware of that. This limitation is particularly relevant when using data streaming: after the failover, the ResultSet looks to be alright, but the underlying connection may have changed already, and no backing cursor is available anymore.

9.2 Configuring Client-Side Failover when using the X Protocol

When using the X Protocol, Connector/J supports a client-side failover feature for establishing a Session. If multiple hosts are specified in the connection URL, when Connector/J fails to connect to a listed host, it tries to connect to another one. This is a sample X DevAPI URL for configuring client-side failover:


An alternate format can also be used, with which the priority for connection can be set explicitly for each individual host:


With the client-side failover configured, when there is a failure to establish a connection, Connector/J keeps attempting to connect to a host on the host list in the order of the set priorities for the hosts, which are specified by any numbers between 0 to 100, with a larger number indicating a higher priority for connection. Priorities should either be set for all or no hosts. When no priorities are specified, the priorities for connection are established according to the order the hosts appear in the list, with a host appearing earlier in the list receiving a higher priority.

Notice that this feature only allows for a failover when Connector/J is trying to establish a connection, but not during operations after a connection has already been made.

9.3 Configuring Load Balancing with Connector/J

Connector/J has long provided an effective means to distribute read/write load across multiple MySQL server instances for Cluster or master-master replication deployments. You can dynamically configure load-balanced connections, with no service outage. In-process transactions are not lost, and no application exceptions are generated if any application is trying to use that particular server instance.

The load balancing is configured at the initial setup stage of the server connection by the following connection URL, which has a similar format as the general JDBC URL for MySQL connection, but a specialized scheme:

jdbc:mysql:loadbalance://[host1][:port],[host2][:port][,[host3][:port]]...[/[database]] »

There are two configuration properties associated with this functionality:

  • loadBalanceConnectionGroup – This provides the ability to group connections from different sources. This allows you to manage these JDBC sources within a single class loader in any combination you choose. If they use the same configuration, and you want to manage them as a logical single group, give them the same name. This is the key property for management: if you do not define a name (string) for loadBalanceConnectionGroup, you cannot manage the connections. All load-balanced connections sharing the same loadBalanceConnectionGroup value, regardless of how the application creates them, will be managed together.

  • ha.enableJMX – The ability to manage the connections is exposed when you define a loadBalanceConnectionGroup; but if you want to manage this externally, enable JMX by setting this property to true. This enables a JMX implementation, which exposes the management and monitoring operations of a connection group. Further, start your application with the JVM flag. You can then perform connect and perform operations using a JMX client such as jconsole.

Once a connection has been made using the correct connection properties, a number of monitoring properties are available:

  • Current active host count.

  • Current active physical connection count.

  • Current active logical connection count.

  • Total logical connections created.

  • Total transaction count.

The following management operations can also be performed:

  • Add host.

  • Remove host.

The JMX interface, com.mysql.cj.jdbc.jmx.LoadBalanceConnectionGroupManagerMBean, has the following methods:

  • int getActiveHostCount(String group);

  • int getTotalHostCount(String group);

  • long getTotalLogicalConnectionCount(String group);

  • long getActiveLogicalConnectionCount(String group);

  • long getActivePhysicalConnectionCount(String group);

  • long getTotalPhysicalConnectionCount(String group);

  • long getTotalTransactionCount(String group);

  • void removeHost(String group, String host) throws SQLException;

  • void stopNewConnectionsToHost(String group, String host) throws SQLException;

  • void addHost(String group, String host, boolean forExisting);

  • String getActiveHostsList(String group);

  • String getRegisteredConnectionGroups();

The getRegisteredConnectionGroups() method returns the names of all connection groups defined in that class loader.

You can test this setup with the following code:

public class Test {

    private static String URL = "jdbc:mysql:loadbalance://" +
        "localhost:3306,localhost:3310/test?" +

    public static void main(String[] args) throws Exception {
        new Thread(new Repeater()).start();
        new Thread(new Repeater()).start();
        new Thread(new Repeater()).start();

    static Connection getNewConnection() throws SQLException, ClassNotFoundException {
        return DriverManager.getConnection(URL, "root", "");

    static void executeSimpleTransaction(Connection c, int conn, int trans){
        try {
            Statement s = c.createStatement();
            s.executeQuery("SELECT SLEEP(1) /* Connection: " + conn + ", transaction: " + trans + " */");
        } catch (SQLException e) {

    public static class Repeater implements Runnable {
        public void run() {
            for(int i=0; i < 100; i++){
                try {
                    Connection c = getNewConnection();
                    for(int j=0; j < 10; j++){
                        executeSimpleTransaction(c, i, j);
                        Thread.sleep(Math.round(100 * Math.random()));
                } catch (Exception e) {

After compiling, the application can be started with the flag, to enable remote management. jconsole can then be started. The Test main class will be listed by jconsole. Select this and click Connect. You can then navigate to the com.mysql.cj.jdbc.jmx.LoadBalanceConnectionGroupManager bean. At this point, you can click on various operations and examine the returned result.

If you now had an additional instance of MySQL running on port 3309, you could ensure that Connector/J starts using it by using the addHost(), which is exposed in jconsole. Note that these operations can be performed dynamically without having to stop the application running.

For further information on the combination of load balancing and failover, see Section 9.5, “Advanced Load-balancing and Failover Configuration”.

9.4 Configuring Master/Slave Replication with Connector/J

This section describe a number of features of Connector/J's support for replication-aware deployments.

The replication is configured at the initial setup stage of the server connection by the connection URL, which has a similar format as the general JDBC URL for MySQL connection, but a specialized scheme:

jdbc:mysql:replication://[master host][:port],[slave host 1][:port][,[slave host 2][:port]]...[/[database]] »

Users may specify the property allowMasterDownConnections=true to allow Connection objects to be created even though no master hosts are reachable. Such Connection objects report they are read-only, and isMasterConnection() returns false for them. The Connection tests for available master hosts when Connection.setReadOnly(false) is called, throwing an SQLException if it cannot establish a connection to a master, or switching to a master connection if the host is available.

Users may specify the property allowSlavesDownConnections=true to allow Connection objects to be created even though no slave hosts are reachable. A Connection then, at runtime, tests for available slave hosts when Connection.setReadOnly(true) is called (see explanation for the method below), throwing an SQLException if it cannot establish a connection to a slave, unless the property readFromMasterWhenNoSlaves is set to be true (see below for a description of the property).

Scaling out Read Load by Distributing Read Traffic to Slaves

Connector/J supports replication-aware connections. It can automatically send queries to a read/write master, or a failover or round-robin loadbalanced set of slaves based on the state of Connection.getReadOnly().

An application signals that it wants a transaction to be read-only by calling Connection.setReadOnly(true). The replication-aware connection will use one of the slave connections, which are load-balanced per slave host using a round-robin scheme. A given connection is sticky to a slave until a transaction boundary command (a commit or rollback) is issued, or until the slave is removed from service. After calling Connection.setReadOnly(true), if you want to allow connection to a master when no slaves are available, set the property readFromMasterWhenNoSlaves to true. Notice that the master host will be used in read-only state in those cases, as if it is a slave host. Also notice that setting readFromMasterWhenNoSlaves=true might result in an extra load for the master host in a transparent manner.

If you have a write transaction, or if you have a read that is time-sensitive (remember, replication in MySQL is asynchronous), set the connection to be not read-only, by calling Connection.setReadOnly(false) and the driver will ensure that further calls are sent to the master MySQL server. The driver takes care of propagating the current state of autocommit, isolation level, and catalog between all of the connections that it uses to accomplish this load balancing functionality.

To enable this functionality, use the specialized replication scheme ( jdbc:mysql:replication://) when connecting to the server.

Here is a short example of how a replication-aware connection might be used in a standalone application:

import java.sql.Connection;
import java.sql.ResultSet;
import java.util.Properties;
import java.sql.DriverManager;

public class ReplicationDemo {

  public static void main(String[] args) throws Exception {
    Properties props = new Properties();

    // We want this for failover on the slaves
    props.put("autoReconnect", "true");

    // We want to load balance between the slaves
    props.put("roundRobinLoadBalance", "true");

    props.put("user", "foo");
    props.put("password", "password");

    // Looks like a normal MySQL JDBC url, with a
    // comma-separated list of hosts, the first
    // being the 'master', the rest being any number
    // of slaves that the driver will load balance against

    Connection conn =

    // Perform read/write work on the master
    // by setting the read-only flag to "false"

    conn.createStatement().executeUpdate("UPDATE some_table ....");

    // Now, do a query from a slave, the driver automatically picks one
    // from the list


    ResultSet rs =
      conn.createStatement().executeQuery("SELECT a,b FROM alt_table");


Consider using the Load Balancing JDBC Pool (lbpool) tool, which provides a wrapper around the standard JDBC driver and enables you to use DB connection pools that includes checks for system failures and uneven load distribution. For more information, see Load Balancing JDBC Driver for MySQL (mysql-lbpool).

Support for Multiple-Master Replication Topographies

Connector/J supports multi-master replication topographies.

The connection URL for replication discussed earlier (i.e., in the format of jdbc:mysql:replication://master,slave1,slave2,slave3/test) assumes that the first (and only the first) host is the master. Supporting deployments with an arbitrary number of masters and slaves requires the "address-equals" URL syntax for multiple host connection discussed in Section 6.2, “Connection URL Syntax”, with the property type=[master|slave]; for example:


Connector/J uses a load-balanced connection internally for management of the master connections, which means that ReplicationConnection, when configured to use multiple masters, exposes the same options to balance load across master hosts as described in Section 9.3, “Configuring Load Balancing with Connector/J”.

Live Reconfiguration of Replication Topography

Connector/J also supports live management of replication host (single or multi-master) topographies. This enables users to promote slaves for Java applications without requiring an application restart.

The replication hosts are most effectively managed in the context of a replication connection group. A ReplicationConnectionGroup class represents a logical grouping of connections which can be managed together. There may be one or more such replication connection groups in a given Java class loader (there can be an application with two different JDBC resources needing to be managed independently). This key class exposes host management methods for replication connections, and ReplicationConnection objects register themselves with the appropriate ReplicationConnectionGroup if a value for the new replicationConnectionGroup property is specified. The ReplicationConnectionGroup object tracks these connections until they are closed, and it is used to manipulate the hosts associated with these connections.

Some important methods related to host management include:

  • getMasterHosts(): Returns a collection of strings representing the hosts configured as masters

  • getSlaveHosts(): Returns a collection of strings representing the hosts configured as slaves

  • addSlaveHost(String host): Adds new host to pool of possible slave hosts for selection at start of new read-only workload

  • promoteSlaveToMaster(String host): Removes the host from the pool of potential slaves for future read-only processes (existing read-only process is allowed to continue to completion) and adds the host to the pool of potential master hosts

  • removeSlaveHost(String host, boolean closeGently): Removes the host (host name match must be exact) from the list of configured slaves; if closeGently is false, existing connections which have this host as currently active will be closed hardly (application should expect exceptions)

  • removeMasterHost(String host, boolean closeGently): Same as removeSlaveHost(), but removes the host from the list of configured masters

Some useful management metrics include:

  • getConnectionCountWithHostAsSlave(String host): Returns the number of ReplicationConnection objects that have the given host configured as a possible slave

  • getConnectionCountWithHostAsMaster(String host): Returns the number of ReplicationConnection objects that have the given host configured as a possible master

  • getNumberOfSlavesAdded(): Returns the number of times a slave host has been dynamically added to the group pool

  • getNumberOfSlavesRemoved(): Returns the number of times a slave host has been dynamically removed from the group pool

  • getNumberOfSlavePromotions(): Returns the number of times a slave host has been promoted to a master

  • getTotalConnectionCount(): Returns the number of ReplicationConnection objects which have been registered with this group

  • getActiveConnectionCount(): Returns the number of ReplicationConnection objects currently being managed by this group


com.mysql.cj.jdbc.ha.ReplicationConnectionGroupManager provides access to the replication connection groups, together with some utility methods.

  • getConnectionGroup(String groupName): Returns the ReplicationConnectionGroup object matching the groupName provided

The other methods in ReplicationConnectionGroupManager mirror those of ReplicationConnectionGroup, except that the first argument is a String group name. These methods will operate on all matching ReplicationConnectionGroups, which are helpful for removing a server from service and have it decommissioned across all possible ReplicationConnectionGroups.

These methods might be useful for in-JVM management of replication hosts if an application triggers topography changes. For managing host configurations from outside the JVM, JMX can be used.

Using JMX for Managing Replication Hosts

When Connector/J is started with ha.enableJMX=true and a value set for the property replicationConnectionGroup, a JMX MBean will be registered, allowing manipulation of replication hosts by a JMX client. The MBean interface is defined in com.mysql.cj.jdbc.jmx.ReplicationGroupManagerMBean, and leverages the ReplicationConnectionGroupManager static methods:

 public abstract void addSlaveHost(String groupFilter, String host) throws SQLException;
 public abstract void removeSlaveHost(String groupFilter, String host) throws SQLException;
 public abstract void promoteSlaveToMaster(String groupFilter, String host) throws SQLException;
 public abstract void removeMasterHost(String groupFilter, String host) throws SQLException;
 public abstract String getMasterHostsList(String group);
 public abstract String getSlaveHostsList(String group);
 public abstract String getRegisteredConnectionGroups();
 public abstract int getActiveMasterHostCount(String group);
 public abstract int getActiveSlaveHostCount(String group);
 public abstract int getSlavePromotionCount(String group);
 public abstract long getTotalLogicalConnectionCount(String group);
 public abstract long getActiveLogicalConnectionCount(String group);

9.5 Advanced Load-balancing and Failover Configuration

Connector/J provides a useful load-balancing implementation for MySQL Cluster or multi-master deployments, as explained in Section 9.3, “Configuring Load Balancing with Connector/J” and Support for Multiple-Master Replication Topographies. This same implementation is used for balancing load between read-only slaves for replication-aware connections.

When trying to balance workload between multiple servers, the driver has to determine when it is safe to swap servers, doing so in the middle of a transaction, for example, could cause problems. It is important not to lose state information. For this reason, Connector/J will only try to pick a new server when one of the following happens:

  1. At transaction boundaries (transactions are explicitly committed or rolled back).

  2. A communication exception (SQL State starting with "08") is encountered.

  3. When a SQLException matches conditions defined by user, using the extension points defined by the loadBalanceSQLStateFailover, loadBalanceSQLExceptionSubclassFailover or loadBalanceExceptionChecker properties.

The third condition revolves around three properties, which allow you to control which SQLExceptions trigger failover:

  • loadBalanceExceptionChecker - The loadBalanceExceptionChecker property is really the key. This takes a fully-qualified class name which implements the new com.mysql.cj.jdbc.ha.LoadBalanceExceptionChecker interface. This interface is very simple, and you only need to implement the following method:

    public boolean shouldExceptionTriggerFailover(SQLException ex)

    A SQLException is passed in, and a boolean returned. A value of true triggers a failover, false does not.

    You can use this to implement your own custom logic. An example where this might be useful is when dealing with transient errors when using MySQL Cluster, where certain buffers may become overloaded. The following code snippet illustrates this:

    public class NdbLoadBalanceExceptionChecker
     extends StandardLoadBalanceExceptionChecker {
     public boolean shouldExceptionTriggerFailover(SQLException ex) {
      return super.shouldExceptionTriggerFailover(ex)
        ||  checkNdbException(ex);
     private boolean checkNdbException(SQLException ex){
     // Have to parse the message since most NDB errors
     // are mapped to the same DEMC.
      return (ex.getMessage().startsWith("Lock wait timeout exceeded") ||
      (ex.getMessage().startsWith("Got temporary error")
      && ex.getMessage().endsWith("from NDB")));

    The code above extends com.mysql.cj.jdbc.ha.StandardLoadBalanceExceptionChecker, which is the default implementation. There are a few convenient shortcuts built into this, for those who want to have some level of control using properties, without writing Java code. This default implementation uses the two remaining properties: loadBalanceSQLStateFailover and loadBalanceSQLExceptionSubclassFailover.

  • loadBalanceSQLStateFailover - allows you to define a comma-delimited list of SQLState code prefixes, against which a SQLException is compared. If the prefix matches, failover is triggered. So, for example, the following would trigger a failover if a given SQLException starts with "00", or is "12345":

  • loadBalanceSQLExceptionSubclassFailover - can be used in conjunction with loadBalanceSQLStateFailover or on its own. If you want certain subclasses of SQLException to trigger failover, simply provide a comma-delimited list of fully-qualified class or interface names to check against. For example, if you want all SQLTransientConnectionExceptions to trigger failover, you would specify:


While the three failover conditions enumerated earlier suit most situations, if autocommit is enabled, Connector/J never re-balances, and continues using the same physical connection. This can be problematic, particularly when load-balancing is being used to distribute read-only load across multiple slaves. However, Connector/J can be configured to re-balance after a certain number of statements are executed, when autocommit is enabled. This functionality is dependent upon the following properties:

  • loadBalanceAutoCommitStatementThreshold – defines the number of matching statements which will trigger the driver to potentially swap physical server connections. The default value, 0, retains the behavior that connections with autocommit enabled are never balanced.

  • loadBalanceAutoCommitStatementRegex – the regular expression against which statements must match. The default value, blank, matches all statements. So, for example, using the following properties will cause Connector/J to re-balance after every third statement that contains the string test:


    loadBalanceAutoCommitStatementRegex can prove useful in a number of situations. Your application may use temporary tables, server-side session state variables, or connection state, where letting the driver arbitrarily swap physical connections before processing is complete could cause data loss or other problems. This allows you to identify a trigger statement that is only executed when it is safe to swap physical connections.