MySQL Clustering on Amazon EC2 using HA-JDBC

What a great bit of software. HA-JDBC sits on top of your JDBC drivers for easy integration into your project and gives you database clustering capability with built-in recovery. I had a fully clustered Amazon EC2-based DB cluster in matter of hours.

To get HA-JDBC working on EC2
The standard setup instructions are easy to follow and will pretty much get you there on EC2. But here are a few notes that you need to bear in mind for Amazon's infrastructure.

You'll need to have your web server running in the same security group as your database server. If you don't, then you'll need to open up some ports using the security groups to allow the boxes to see each other.

HA-JDBC uses a multicast UDP stack by default. This is not allowed on EC2 so you'll have to switch the tcp stack in the ha-jdbc-your_env_name.xml config.

Once you've defined tcp as your stack, you'll need to also let jGroups know the list of servers in the cluster, as well as the ip to bind to. This is done by using the following java arguments:

-Djgroups.tcpping.initial_hosts=xxx.xxx.xxx.xxx[7800],xxx.xxx.xxx.xxx[7800]
-Djgroups.bind_addr=(your amazon internal ip address)

Defining all the nodes upfront is not ideal if you want to dynamically scale up by adding nodes, but since you don't get multicast in EC2, the only other option is to create a GossipRouter. This is only required to be there when the nodes start up, so although it seems you’re introducing another point of failure in your system, it's not a critical one and you don't have to make it redundant, as long as it's started when nodes are added/removed.

You'll also need to modify your MySQL installation to bind on the host ip address and not 127.0.0.1. Make sure that all the clusters have access to all the databases by adding a wildcard host to the username that is used in your cluster definition. Here's the SQL for MySQL to do this:

create user "some_user"@"%" identified by "a_password";
grant all on the_database.* to "some_user"@"%";

Finally, in your datasource, you just specify the following:

jdriverClass = "net.sf.hajdbc.sql.Driver"
jdbcUrl = "jdbc:ha-jdbc:your_env_name"