Saturday, September 26, 2015

allowMasterDownConnections = true works but long delay to connect to slave

cat ReplicationDriverDemo.java
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();
    // We want this for failover on the slaves
    props.put("autoReconnect", "false");
    props.put("connectTimeout", "5");
    props.put("socketTimeout", "5");

    // We want to load balance between the slaves
    props.put("roundRobinLoadBalance", "false");

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

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


    // 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:replication://localhost:5001,localhost:5002/appdb", props);
        //driver.connect("jdbc:mysql:replication://address=(type=master)(protocol=tcp)(host=localhost)(port=5001),address=(type=master)(protocol=tcp)(host=localhost)(port=5002)/appdb", props);

    //
    // Perform read/write work on the master
    // by setting the read-only flag to "false"
    //

    // 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 'ReplicationDriverDemo'+current_timestamp()");
        conn.close();


  }
}


--------------------------------------

[mysql@localhost ~]$ time /usr/java/jdk1.8.0_60/bin/java  ReplicationDriverDemo
real    0m31.454s
user    0m1.626s
sys     0m0.113s

--------------------------------------

150926 17:29:06    70 Connect   root@localhost on appdb
                   70 Query     /* mysql-connector-java-5.1.36 ( Revision: 4fc1f969f740409a4e03750316df2c0e429f3dc8 ) */SELECT @@session.auto_increment_increment, @@character_set_client, @@character_set_connection, @@character_set_results, @@character_set_server, @@init_connect, @@interactive_timeout, @@license, @@lower_case_table_names, @@max_allowed_packet, @@net_buffer_length, @@net_write_timeout, @@query_cache_size, @@query_cache_type, @@sql_mode, @@system_time_zone, @@time_zone, @@tx_isolation, @@wait_timeout
                   70 Query     SET NAMES latin1
                   70 Query     SET character_set_results = NULL
                   70 Query     SET sql_mode='NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES'
                   70 Query     set session transaction read only
                   70 Query     SELECT 'ReplicationDriverDemo'+current_timestamp()
                   70 Quit


=================================
[mysql@localhost ~]$  /usr/java/jdk1.8.0_60/bin/java ReplicationDriverDemo
2015-09-26 23:36:58.284: Create connection
2015-09-26 23:37:29.571: Set Readonly
2015-09-26 23:37:29.571: Execute Query

2015-09-26 23:37:29.602: Close Connection
=================================
import java.sql.Timestamp;
...
System.out.println(new Timestamp(System.currentTimeMillis())+": Create connection");
    Connection conn =
        driver.connect("jdbc:mysql:replication://localhost:5001,localhost:5002/appdb", props);
...