MySQL Connector/J 5.1 Developer Guide
Connector/J has long provided an effective means to distribute read/write load across multiple MySQL server instances for Cluster or source-source replication deployments. Starting with Connector/J 5.1.3, you can now dynamically configure load-balanced connections, with no service outage. In-process transactions are not lost, and no application exceptions are generated if any application is trying to use that particular server instance.
The load balancing is configured at the initial setup stage of the server connection by the following connection URL, which has a similar format as the general URL for MySQL connection, but a specialized scheme:
jdbc:mysql:loadbalance://[host
1][:port
],[host
2][:port
][,[host
3][:port
]]...[/[database
]] » [?propertyName1
=propertyValue1
[&propertyName2
=propertyValue2
]...]
There are two configuration properties associated with this functionality:
loadBalanceConnectionGroup
– This
provides the ability to group connections from different
sources. This allows you to manage these JDBC sources within
a single class loader in any combination you choose. If they
use the same configuration, and you want to manage them as a
logical single group, give them the same name. This is the
key property for management: if you do not define a name
(string) for loadBalanceConnectionGroup
,
you cannot manage the connections. All load-balanced
connections sharing the same
loadBalanceConnectionGroup
value,
regardless of how the application creates them, will be
managed together.
loadBalanceEnableJMX
– The ability to
manage the connections is exposed when you define a
loadBalanceConnectionGroup
; but if you
want to manage this externally, enable JMX by setting this
property to true
. This enables a JMX
implementation, which exposes the management and monitoring
operations of a connection group. Further, start your
application with the
-Dcom.sun.management.jmxremote
JVM flag.
You can then perform connect and perform operations using a
JMX client such as jconsole
.
Once a connection has been made using the correct connection properties, a number of monitoring properties are available:
Current active host count.
Current active physical connection count.
Current active logical connection count.
Total logical connections created.
Total transaction count.
The following management operations can also be performed:
Add host.
Remove host.
The JMX interface,
com.mysql.jdbc.jmx.LoadBalanceConnectionGroupManagerMBean
,
has the following methods:
int getActiveHostCount(String group);
int getTotalHostCount(String group);
long getTotalLogicalConnectionCount(String
group);
long getActiveLogicalConnectionCount(String
group);
long getActivePhysicalConnectionCount(String
group);
long getTotalPhysicalConnectionCount(String
group);
long getTotalTransactionCount(String
group);
void removeHost(String group, String host) throws
SQLException;
void stopNewConnectionsToHost(String group, String
host) throws SQLException;
void addHost(String group, String host, boolean
forExisting);
String getActiveHostsList(String group);
String getRegisteredConnectionGroups();
The getRegisteredConnectionGroups()
method
returns the names of all connection groups defined in that class
loader.
You can test this setup with the following code:
public class Test { private static String URL = "jdbc:mysql:loadbalance://" + "localhost:3306,localhost:3310/test?" + "loadBalanceConnectionGroup=first&loadBalanceEnableJMX=true"; public static void main(String[] args) throws Exception { new Thread(new Repeater()).start(); new Thread(new Repeater()).start(); new Thread(new Repeater()).start(); } static Connection getNewConnection() throws SQLException, ClassNotFoundException { Class.forName("com.mysql.jdbc.Driver"); return DriverManager.getConnection(URL, "root", ""); } static void executeSimpleTransaction(Connection c, int conn, int trans){ try { c.setAutoCommit(false); Statement s = c.createStatement(); s.executeQuery("SELECT SLEEP(1) /* Connection: " + conn + ", transaction: " + trans + " */"); c.commit(); } catch (SQLException e) { e.printStackTrace(); } } public static class Repeater implements Runnable { public void run() { for(int i=0; i < 100; i++){ try { Connection c = getNewConnection(); for(int j=0; j < 10; j++){ executeSimpleTransaction(c, i, j); Thread.sleep(Math.round(100 * Math.random())); } c.close(); Thread.sleep(100); } catch (Exception e) { e.printStackTrace(); } } } } }
After compiling, the application can be started with the
-Dcom.sun.management.jmxremote
flag, to
enable remote management. jconsole
can then
be started. The Test
main class will be
listed by jconsole
. Select this and click
. You can then navigate to the
com.mysql.jdbc.jmx.LoadBalanceConnectionGroupManager
bean. At this point, you can click on various operations and
examine the returned result.
If you now had an additional instance of MySQL running on port
3309, you could ensure that Connector/J starts using it by using
the addHost()
, which is exposed in
jconsole
. Note that these operations can be
performed dynamically without having to stop the application
running.
For further information on the combination of load balancing and failover, see Section 8.4, “Advanced Load-balancing and Failover Configuration”.