{{parent page="MySQL"}}

===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.

%%(java;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();
}
}
}
%%