Connecting to a mysql cluster

Without a load balancer (ELB ) , you can connect to the mysql cluster through a datasource configured in your servlet container or in your spring config , basically you will need a apache commons datasource driver or something similar which support auto health check of the connections in your pool.

At a minimum , you need the following in your datasource config

<property name=”validationQuery”> <value>/* ping */</value> </property>

<property name=”testOnBorrow”> <value>true</value></property>

If your app does not use a datasource , you can use a connection pool configured in your servlet container with a comma separated mysql hosts. I am using Apache roller weblogger in which I did not find how to configure a datasource directly in its configuration. So I have the datasource definition defined at conf/context.xml file of my tomcat which does load balancing.

<Resource name=”jdbc/rollerDB” auth=”Container” type=”javax.sql.DataSource”

validationQuery=”/* ping */” testOnBorrow=”true” testOnReturn=”true”

maxActive=”100″ maxIdle=”30″ maxWait=”10000″

username=”xxx” password=”xxx” driverClassName=”com.mysql.jdbc.Driver”

url=”jdbc:mysql://clusternode1, clusternode2:3306/dbshcema?autoReconnect=true&amp;loadBalanceBlacklistTimeout=50&amp;autoCommit=true”/>

of course, if you have load balancer fronting your mysql cluster , you will not need the datasource to do any testing of corrupt connections though it is recommended.

One issue you need to know before using an elastic load balancer is “host blocked connection error”

Warning: mysql_connect(): Host ‘xxxx’ is blocked because of many connection errors. Unblock with ‘mysqladmin flush-hosts’

The ELB health check keeps on pinging mysql port on instances behind it to make sure they are alive. The default connection setting in mysql is 10 , soon after 10 pings are reached , none of your clients will be able to access the DB since the mysql cluster blocks the elb IP so in turn blocks access from all clients

You need to disable connection error check by using

max_connect_errors =999999999 in my.cnf

More explained here


Post a Comment

Required fields are marked *


%d bloggers like this: