9.4 Configuring Source/Replica 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://[source host][:port],[replica host 1][:port][,[replica host 2][:port]]...[/[database]] »
[?propertyName1=propertyValue1[&propertyName2=propertyValue2]...]

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

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

Scaling out Read Load by Distributing Read Traffic to Replicas

Connector/J supports replication-aware connections. It can automatically send queries to a read/write source host, or a failover or round-robin loadbalanced set of replicas 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 replica connections, which are load-balanced per replica host using a round-robin scheme. A given connection is sticky to a replica until a transaction boundary command (a commit or rollback) is issued, or until the replica is removed from service. After calling Connection.setReadOnly(true), if you want to allow connection to a source when no replicas are available, set the property readFromSourceWhenNoReplicas to “true.” Notice that the source host will be used in read-only state in those cases, as if it is a replica host. Also notice that setting readFromSourceWhenNoReplicas=true might result in an extra load for the source 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 source 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 replicas
    props.put("autoReconnect", "true");

    // We want to load balance between the replicas
    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 'source', the rest being any number
    // of replicas that the driver will load balance against
    //

    Connection conn =
        DriverManager.getConnection("jdbc:mysql:replication://source,replica1,replica2,replica3/test",
            props);

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

    conn.setReadOnly(false);
    conn.setAutoCommit(false);
    conn.createStatement().executeUpdate("UPDATE some_table ....");
    conn.commit();

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

    conn.setReadOnly(true);

    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-Source Replication Topographies

Connector/J supports multi-source replication topographies.

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

jdbc:mysql:replication://address=(type=source)(host=source1host),address=(type=source)(host=source2host),address=(type=replica)(host=replica1host)/database

Connector/J uses a load-balanced connection internally for management of the source connections, which means that ReplicationConnection, when configured to use multiple sources, exposes the same options to balance load across source 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-source) topographies. This enables users to promote replicas 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:

• getSourceHosts(): Returns a collection of strings representing the hosts configured as source hosts

• getReplicaHosts(): Returns a collection of strings representing the hosts configured as replica hosts

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

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

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

• removeSourceHost(String host, boolean closeGently): Same as removeReplicaHost(), but removes the host from the list of configured source hosts

Some useful management metrics include:

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

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

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

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

• getNumberOfReplicaPromotions(): Returns the number of times a replica host has been promoted to be a source host

• 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

ReplicationConnectionGroupManager

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 addReplicaHost(String groupFilter, String host) throws SQLException;
 public abstract void removeReplicaHost(String groupFilter, String host) throws SQLException;
 public abstract void promoteReplicaToSource(String groupFilter, String host) throws SQLException;
 public abstract void removeSourceHost(String groupFilter, String host) throws SQLException;
 public abstract String getSourceHostsList(String group);
 public abstract String getReplicaHostsList(String group);
 public abstract String getRegisteredConnectionGroups();
 public abstract int getActiveSourceHostCount(String group);
 public abstract int getActiveReplicaHostCount(String group);
 public abstract int getReplicaPromotionCount(String group);
 public abstract long getTotalLogicalConnectionCount(String group);
 public abstract long getActiveLogicalConnectionCount(String group);

Configuring Source/Replica Replication with DNS SRV

See Section 6.14, “Support for DNS SRV Records” for details.