8.3 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 URL for MySQL connection, but a specialized scheme:

jdbc:mysql:replication://[master host][:port],[slave host 1][:port][,[slave 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 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.

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 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 3.1.7 and higher includes a variant of the driver that will 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. For Connector/J 5.1.38 and later, 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 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 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 =
        driver.connect("jdbc:mysql:replication://master,slave1,slave2,slave3/test",
            props);

    //
    // Perform read/write work on the master
    // 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 slave, 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-Master Replication Topographies

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

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 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://address=(type=master)(host=master1host),address=(type=master)(host=master2host),address=(type=slave)(host=slave1host)/database

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 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-master) topographies is also supported. 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

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);