KfWiki: MySQL JDBC fail-over

HomePage » Database » MySQL » MysqlJDBCFailover

MySQL JDBC fail-over

MySQL J/connector supports failover settings. You can specify more than 1 server to connect to in the JDBC connection string.

Testbed

My test bed is 2 instances of MySQL running on the same machine, listening to different ports.

MySQL version: 5.0.45
MySQL J/connector version: 5.0.7

Installing / starting mysql

Install mysql with the install shield. Configure mysql to use c:\mysql as base, and c:\mysql\data as data storage. The install ends with a database creation wizard. Just create a database with all default settings. Do not install MySQL as Windows service, although that will not affect the outcome of this test.

Now, copy c:\mysql\data to c:\mysql\data1 and c:\mysql\data2. Then start mysql with the followings:

mysqld-nt.exe -P3306 --datadir=c:\mysql\data1
mysqld-nt.exe -P3307 --datadir=c:\mysql\data2

Programming JDBC with failover option

The following piece of code is *supposed* to query the slave instance when the master is shutdown. But it failed during my test. When the master is shut down, it simply waits forever for port 3306 to be available! This could be a bug of the driver.

MysqlFailoverTest.java

package test.db;

import java.sql.*;

import java.util.Properties;

import com.mysql.jdbc.ReplicationDriver;

public class MysqlFailoverTest {

  public static void main(String[] args)  {

    ReplicationDriver driver = null;

    try {

        driver = new ReplicationDriver();

    } catch (SQLException e) {

        e.printStackTrace();

    }

    System.out.println("Replication driver loaded.");

    Properties props = new Properties();

    props.put("user", "root");

    props.put("password", "mysql");

    props.put("autoReconnect", "true");

    props.put("maxReconnects", "2");

    props.put("initialTimeout", "2");

    props.put("queriesBeforeRetryMaster", "3");

    props.put("secondsBeforeRetryMaster", "3");

    // We want to load balance between the slaves

    props.put("roundRobinLoadBalance", "true");

    props.put("failOverReadOnly", "false");

    // If enabled, when master is turned back on, exceptions will be thrown

    // props.put("connectTimeout", "3");

    // props.put("socketTimeout", "10");

    // 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;

    System.out.println("Trying to connect to MySQL...");

    try {

        conn = driver.connect("jdbc:mysql://localhost:3306,localhost:3307/test", props);

        System.out.println("MySQL connected.");

        //

        // Perform read/write work on the master

        // by setting the read-only flag to "false"

        //

        conn.setReadOnly(false);

        conn.setAutoCommit(false);

        ResultSet rs  = conn.createStatement().executeQuery("SELECT * from mytable;");

        while (rs.next()) {

            System.out.println(rs.getString(1));

        }

        rs.close();

        conn.close();

        System.out.println("Connection closed.");

    } catch (SQLException e) {

        e.printStackTrace();

    }

  }

}