MySQL Connector/J provides loadbalancing and failover support. While testing failover I wanted to know what was happening under the hood. Specifically I wanted to know when a host was blacklisted and which host.
If you set
loadBalanceStrategy to
random in your connection properties, which has been the recommended strategy over round-robin, you can modify the RandomBalanceStrategy.java file to log when a host is blacklisted.
Using guidance found on Todd Farmer's blog (
http://mysqlblog.fivefarmers.com/) and a bit of twiddling it wasn't difficult.
In my setup, I have jboss-as-7.1.1. running on my local windows machine with my test app I developed using one of the JBoss quickstarts.
The connector (connector-java-5.1.21) is deployed and I have the source on C:\mysql-connector-java-5.1.21.
In RandomBalanceStrategy.java, right after the class declaration, I added the second line shown below.
public class RandomBalanceStrategy implements BalanceStrategy {
private static Logger logger = Logger.getLogger("cyfd.connectorj.plugins");
Next, I added the second line shown below.
proxy.addToGlobalBlacklist( hostPortSpec );
logger.log(Level.SEVERE, "RandomBalanceStrategy-"+"Blacklisted host: " +
hostPortSpec + " att: " + attempts + " ret:" + numRetries );
Save the file and compile it: (from windows cmd prompt at C:\mysql-connector-java-5.1.21\src\com\mysql\jdbc>; and, this is all one line)
javac -verbose -classpath "C:\jboss-as-7.1.1.Final\modules\com\mysql\main\
mysql-connector-java-5.1.24-bin.jar" -sourcepath . RandomBalanceStrategy.java
With jboss shutdown, update the jar file with the new class (again, this is all one line but executed from under the src folder which is C:\mysql-connector-java-5.1.21\src on my machine):
jar uf C:\jboss-as-7.1.1.Final\modules\com\mysql\main\mysql-connector-java-
5.1.24-bin.jar com/mysql/jdbc/RandomBalanceStrategy.class
Start jboss and when a host is blacklisted, you'll get a message similar to this.
08:25:11,005 SEVERE [cyfd.connectorj.plugins] (http--127.0.0.1-8080-80) RandomBalanceStrategy Blacklisted host: mysqlA:3306 att: 0 ret:100
So far the results of failover and load balance testing work perfectly. As far as I can tell, a web client will get an error page--sometimes--but they can refresh the page and all transactions happen on the other server.
More testing to do....