Documentation Home
MySQL Connector/J 5.1 Developer Guide
Related Documentation Download this Manual
PDF (US Ltr) - 506.7Kb
PDF (A4) - 508.8Kb
HTML Download (TGZ) - 122.4Kb
HTML Download (Zip) - 141.9Kb


MySQL Connector/J 5.1 Developer Guide  /  Multi-Host Connections  /  Configuring Source/Replica Replication with Connector/J

8.3 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 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 allowMasterDownConnections=true to allow Connection objects to be created even though no source hosts are reachable. Such Connection objects report they are read-only, and isMasterConnection() 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.

For Connector/J 5.1.38 and later, users may specify the property allowSlavesDownConnections=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 readFromMasterWhenNoSlaves 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 3.1.7 and higher includes a variant of the driver that will automatically send queries to a read/write source, 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. For Connector/J 5.1.38 and later, after calling Connection.setReadOnly(true), if you want to allow connection to a source when no replicas are available, set the property readFromMasterWhenNoSlaves 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 readFromMasterWhenNoSlaves=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 com.mysql.jdbc.ReplicationDriver class when configuring your application server's connection pool or when creating an instance of a JDBC driver for your standalone application. Because it accepts the same URL format as the standard MySQL JDBC driver, ReplicationDriver does not currently work with java.sql.DriverManager-based connection creation unless it is the only MySQL JDBC driver registered with the DriverManager .

Here is a short example of how ReplicationDriver might be used in a standalone application:

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

import com.mysql.jdbc.ReplicationDriver;

public class ReplicationDriverDemo {

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

    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 =
        driver.connect("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

Since Connector/J 5.1.27, multi-source replication topographies are supported.

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. Supporting deployments with an arbitrary number of sources and replicas requires the URL syntax for specifying the hosts and the properties for specific hosts (which is discussed here) and the use of the property type=[master|slave]; for example:

jdbc:mysql:replication://address=(type=master)(host=source1host),address=(type=master)(host=source2host),address=(type=slave)(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 8.2, “Configuring Load Balancing with Connector/J”.

Live Reconfiguration of Replication Topography

Since Connector/J 5.1.28, live management of replication host (single or multi-source) topographies is also supported. 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:

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

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

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

  • promoteSlaveToMaster(String host): Removes the host from the pool of potential replicas 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

  • removeSlaveHost(String host, boolean closeGently): Removes the host (host name match must be exact) from the list of configured replicas; 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 sources

Some useful management metrics include:

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

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

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

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

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

  • 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.jdbc.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 replicationEnableJMX=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.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);