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://
[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 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.
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 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 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 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("roundRobinLoadBalan
ce", "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("j
dbc:mysql:replication://master
,slave1,slave2,slave3/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().execute
Update("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().execute
Query("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://master,slave1,slave2,slave3/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=[master|slave];
for example:
jdbc:mysql:replication://addre
ss=(type=master)
(host=master1host),address=(ty
pe=master)
(host=master2host),address=(ty
pe=slave)
(host=slave1host)/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:

 getMasterHosts(): Returns a
collection of strings representing
the hosts configured as source
hosts
 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:

 getConnectionCountWithHostA
sSlave(String host): Returns
the number of
ReplicationConnection objects that
have the given host configured as a
possible replica
 getConnectionCountWithHostA
sMaster(String host): Returns
the number of
ReplicationConnection objects that
have the given host configured as a
possible source host
 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.cj.jdbc.ha.ReplicationConnectionGroupManager provides access to the
replication connection groups, together with some utility methods.
 getConnectionGroup(String
groupName): Returns
the ReplicationConnectionGro
up 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
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(Strin
g group);
public abstract int
getActiveSlaveHostCount(String
group);
public abstract int
getSlavePromotionCount(String
group);
public abstract long
getTotalLogicalConnectionCount
(String group);
public abstract long
getActiveLogicalConnectionCoun
t(String group);

Configuring Source/Replica Replication with DNS SRV

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

 PREV   HOME   UP   NEXT 
 
   © 2020, Oracle Corporation and/or its affiliates