Preamble

As we have already seen putting MySQL replication in action is quite easy and it became even much more easy with MySQL 5.6 and the new GTID feature.

As DBA we also have few cool tools like MHA, MySQL utilities, MMM and Percona Replication Manager (PRM) that can help you to recover situation in case of failure of servers. For example to promote a slave as the new master.

From DBA standpoint all is quite easy to set up but I wanted this time to go one step further and understand what happens at application level. If you look at official MySQL documentation in Chapter 22. Connectors and APIs you will see a long list of available connector and there is surely one handling MySQL replication for your favorite development language (non exhaustively):

• Connect/J for Java
• Mysqlnd for PHP
• Connector/Net for .Net
• .

Even if I prefer and know better PHP I have chosen to test Connector/J in Java because I believe this is the most used connector and Java is a standard for applications development where I currently work.

This blog post has been written using Oracle Linux Server release 6.5, MySQL Community Server (GPL) 5.6.15, Java “1.7.0_51” and Connector/J 5.1.28.

My master server is called server1.domain.com and has 192.168.56.101 as non-routable IP address. My slave server is called server2.domain.com and has 192.168.56.102 as non-routable IP address.

Testing

To test it I wanted to use a kind of real life scenario with a Java application, problem is that I don’t know Java at all so had to do a lot of technical awareness. And find a graphical Java editor, after few tests I decided to use a free one: Eclipse.

The first Java code I have written is this one:

package jdbcdemo1;
 
import com.mysql.jdbc.Driver;
 
import java.sql.ResultSet;
import java.util.Properties;
import java.sql.Connection;
//import java.sql.Statement;
import java.sql.SQLException;
//import java.sql.DriverManager;
 
public class jdbcdemo1 {
  public static void main(String[] args) throws Exception {
    Driver driver = new Driver();
    Properties props = new Properties();
    ResultSet rs;
    String variable_value;
    Connection conn = null;
    String JDBC_URL = "jdbc:mysql:replication://address=(protocol=tcp)(host=192.168.56.101)(port=3316)(type=master),"
    			+ "address=(protocol=tcp)(host=192.168.56.102)(port=3316)(type=slave)";
 
    props.put("replicationConnectionGroup", "rep_group");
    props.put("replicationEnableJMX", "true");
 
    props.put("user", "yjaquier");
    props.put("password", "secure_password");
 
    System.out.println("\n------------ MySQL Connector/J and MySQL Replication Testing ------------\n");
 
    try {
    	Class.forName("com.mysql.jdbc.ReplicationDriver");
    }
    catch (ClassNotFoundException e) {
    	System.out.println("Where is your MySQL JDBC Driver?");
	e.printStackTrace();
	return;
    }
 
    System.out.println("MySQL Connector/J driver loaded");
 
    System.out.println("Trying connection...");
 
    try {
		conn = driver.connect(JDBC_URL, props);
	}
	catch (SQLException e) {
		System.out.println("Connection Failed! Check output console");
		//e.printStackTrace();
		System.out.println("Error cause: "+e.getCause());
		System.out.println("Error message: "+e.getMessage());
		return;
	}
 
    System.out.println("Connection established...");
 
    for(int i=1; i <= 1000; i++) {
    	System.out.println("\nQuery "+i+": ");
    	// Fake Read Write SQL statement (just to see where it goes)
    	System.out.println("Read Write query...");
    	try {
    		conn.setReadOnly(false);
    	}
    	catch (SQLException e) {
    		System.out.println("Connection read write property set has has failed...");
    	}
 
    	try {
    		rs = conn.createStatement().executeQuery("SELECT variable_value FROM information_schema.global_variables where variable_name='hostname'");
    		while (rs.next()) {
    			variable_value = rs.getString("variable_value");
    			System.out.println("variable_value : " + variable_value);
    		}
	    }
    	catch (SQLException e) {
    		System.out.println("Read/write query has failed...");
    	}
 
    	// Read Only statement
    	System.out.println("Read Only query...");
    	try {
    		conn.setReadOnly(true);	
    	}
    	catch (SQLException e) {
    		System.out.println("Connection read only property set has has failed...");
    	}
 
    	try {
    		rs = conn.createStatement().executeQuery("SELECT variable_value FROM information_schema.global_variables where variable_name='hostname'");
    		while (rs.next()) {
    			variable_value = rs.getString("variable_value");
    			System.out.println("variable_value : " + variable_value);
    		}
    		}
    	catch (SQLException e) {
    		System.out.println("Read only query has failed...");
    	}
 
    	Thread.sleep(2000);
    }
  }
}

With a live example it gives:

Notes:

address=(host=hostname)(port=3306)(type=[master|slave])

setReadOnly(true | false)

When master and slave servers are up and running all is working fine (as expected I would say). Testing disaster scenarios is a bit more complex.

For example most favorable scenario is when the slave server is down, then in this case the connection cannot be made as Connector/J does not accept slave server to be down and does not redirect read-only queries to master server.

There is an allowMasterDownConnections parameter but no allowSlaveDownConnections… And even so if master server is down the connection is working but the conn.setReadOnly(true); statement is throwing an error even in a try catch statement:

Exception in thread "main" java.lang.NullPointerException
	at com.mysql.jdbc.ReplicationConnection.swapConnections(ReplicationConnection.java:783)
	at com.mysql.jdbc.ReplicationConnection.swapConnections(ReplicationConnection.java:765)
	at com.mysql.jdbc.ReplicationConnection.switchToMasterConnection(ReplicationConnection.java:751)
	at com.mysql.jdbc.ReplicationConnection.setReadOnly(ReplicationConnection.java:712)
	at jdbcdemo1.jdbcdemo1.main(jdbcdemo1.java:77)

In official documentation they suggest to use “Load Balancing JDBC Driver for MySQL (mysql-lbpool)” to manage check and failure of systems but the project seems to be dead and there is no simple jar file to download to include in a Java project…

I thought I was doing something wrong (again not a Java expert) so had long discussion around this with a SkySQL support engineer and we reached same conclusion: even if Connector/J is a not so young product the health checks capability is not handled at all and we have to use another product like HAProxy, for example, that I plan to test soon… The split of read-write and read-only queries is by the way working very well till all servers are up and running.

Cluster live reconfiguration

With Connector/J 5.1.28 Oracle implemented live reconfiguration of replication topography, I expected it was answer to issues I had wit my disaster scenario. Even if I learnt a new thing using JConsole (that is a nice Java monitoring tool) it is not working like I expected. It anyways supports live reconfiguration of your application if you plan to change your cluster topology (adding/removing nodes mainly).

To enable it there is only two properties to set in your Java code:

    props.put("replicationConnectionGroup", "rep_group");
    props.put("replicationEnableJMX", "true");

Then I searched where I can download JConsole to realize that the executable (jconsole.exe) was already in my JDK 1.7 under the usual direactory C:\Program Files\Java\jdk1.7.0_51\bin.

Connect to your running Java application (process should not be too difficult to find in available list) and you initially get something like:

If I go to MBeans tab and call getSlaveHostsList method supplying the ReplicationConnectionGroup I have set in my Java code (rep_group):

I get below expected result:

References

• 9.4 Configuring Source/Replica Replication with Connector/J
• 5.1. Driver/Datasource Class Names, URL Syntax and Configuration Properties for Connector/J
• Live reconfiguration of replication topography in Connector/Java

About Post Author

Yannick Jaquier

administrator

Find more about me on social media.

See author's posts