Discussion

A new feature available starting with Connector/J 3.1.5 is a ‘replication-aware‘ connection, which is basically a wrapper of two connections, one to a master, and another that is connected to one of a list of read-only slaves. To create a replication-aware connection, use the 'com.mysql.jdbc.ReplicationDriver' class instead of the normal 'com.mysql.jdbc.Driver' class when configuring your data sources.

By signaling that your application wants a transaction to be read-only by calling Connection.setReadOnly(true), a ‘replication-aware‘ connection will use one of the slave connections, which are load-balanced per-vm using a round-robin scheme (a given connection is ‘sticky‘ to a slave unless that slave is removed from service).

If your application has a write transaction, or if it has a read transaction that is ‘time-sensitive‘ (remember, replication in MySQL is asynchronous), you just set the connection to be not read-only, by calling Connection.setReadOnly(false).

The driver takes care of propagating the current state of autocommit, isolation level, and catalog between all of the connections that it uses.

Here‘s a quick example of how you might use it:

import java.sql.Connection;
import java.sql.ResultSet;
import java.util.Properties;

import com.mysql.jdbc.ReplicationDriver;

public class ReplicationDriverDemo {

	public static void main(String[] args) throws Exception {
		ReplicationDriver driver = new ReplicationDriver();

		Properties props = new Properties();
		props.put("autoReconnect", "true");
		props.put("roundRobinLoadBalance", "true");
		props.put("user", "foo");
		props.put("password", "bar");

		//
		// Looks like a normal MySQL JDBC url, with a comma-separated list
		// of hosts, the first being the 'master', the rest being any number
		// of slaves that the driver will load balance against
		//

		Connection conn =
			driver.connect("jdbc:mysql://master,slave1,slave2,slave3/test",props);

		// Do something on the master

		conn.setReadOnly(false);
		conn.setAutoCommit(false);
		conn.createStatement().executeUpdate("UPDATE some_table ....");
		conn.commit();

		// Now, do a query from a slave, the driver automatically picks one
		// from the list

		conn.setReadOnly(true);

		ResultSet rs = conn.createStatement().executeQuery(
			"SELECT a,b,c FROM some_other_table");
	}
}