MySQL Connector/J 5.1 Developer Guide
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).
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).
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”.
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
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.
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);