8.3 Master/Slave Replication with ReplicationConnection

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

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), this replication-aware connection will use one of the slave connections, which are load-balanced per-vm using a round-robin scheme (a given connection is sticky to a slave unless that slave is removed from service). 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", "bar");

    //
    // 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 investigating 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. They can be specified using the following host definition syntax:

address=(host=hostname)(port=3306)(type=[master|slave])

The definition described above assumes 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 for specifying different properties for specific hosts, which is just an expansion of the legacy URL syntax with the property type=[master|slave]; for example:

jdbc:mysql://address=(type=master)(host=master1host),address=(type=master)(host=master2host),address=(type=slave)(host=slave1host)/db

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.1, “Configuring Load Balancing with Connector/J”.

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.

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:

Some useful management metrics include:

ReplicationConnectionGroupManager

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

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