Post on 09-Apr-2017
Connecting to MySQL Connector/J - High Availability
Ivan Ma ivanxma@yahoo.com 2015-10-10
February 2015
Safe Harbor Statement
The following is intended to outline our general product direction. It is intended for information purposes only, and may not be incorporated into any contract. It is not a commitment to deliver any material, code, or functionality, and should not be relied upon in making purchasing decisions. The development, release, and timing of any features or functionality described for Oracle’s products remains at the sole discretion of Oracle.
February 2015 Copyright 2015, oracle and/or its affiliates. All rights reserved 2
Agenda
• Introduction
• Connecting to MySQL using Connector/J
– Building “select “HelloWorld “ App
– Creating Tomcat JSP page to connect to MySQL
– Using Connection Pool in Tomcat
– High Availability access to MySQL HA configuration • MySQL Replication
• MySQL Fabric
• MySQL Cluster
• Questions
February 2015 Copyright 2015, oracle and/or its affiliates. All rights reserved 3
Connecting to MySQL
• Native Client (mysql, other tools)
• Building Applications
– MySQL Connector
February 2015 Copyright 2015, oracle and/or its affiliates. All rights reserved 4
http://dev.mysql.com/downloads/connector/j/
JDBC – Type4
• Connector/J
– The package (jar file) – Latest as of 2015-10-10 • mysql-connector-java-5.1.36-bin.jar
• Commercial Download
• mysql-connector-java-commercial-5.1.36-bin.jar
– Driver • com.mysql.jdbc.Driver
• com.mysql.jdbc.ReplicationDriver
• com.mysql.fabric.jdbc.FabricMySQLDriver
February 2015 Copyright 2015, oracle and/or its affiliates. All rights reserved 5
App
Connector
Browser / Client
Connector
App Server (e.g. Tomcat)
Browser / Client
Connector
Connection Pool
App Server (e.g. Tomcat)
useConfigs
• $ unzip mysql-connector-java-<version>-bin.jar '*/configs/*
February 2015 Copyright 2015, oracle and/or its affiliates. All rights reserved 6
Building the java - select “helloworld” app
• Pre-requisite :
– A running MySQL database (e.g. localhost:3306)
February 2015 Copyright 2015, oracle and/or its affiliates. All rights reserved 7
java.util.Properties pp = new java.util.Properties();
pp.put("user", “root”);
pp.put("password", “”);
try {
Class.forName("com.mysql.jdbc.Driver");
conn = java.sql.DriverManager.getConnection( "jdbc:mysql://127.0.0.1:3306/mysql", pp);
stmt = conn.createStatement();
rs = stmt.executeQuery("select \"hello world\"");
while (rs.next()) {
System.out.println(rs.getString(1));
}
} catch
Compile and Run Hello World
• Compile
export JAVA_HOME=/usr/jdk/jdk_latest
$JAVA_HOME/bin/javac -cp .:./mysql-connector-java-5.1.33-bin.jar HelloWorld.java
• Run export JAVA_HOME=/usr/jdk/jdk_latest
$JAVA_HOME/bin/java -cp .:./mysql-connector-java-5.1.36-bin.jar \
-Dcom.mysql.demo.username=root \
-Dcom.mysql.demo.password= \
HelloWorld
February 2015 Copyright 2015, oracle and/or its affiliates. All rights reserved 8
Tomcat Running JSP / Servlet connecting to MySQL
Tomcat
• Copy the Connector/J jar into tomcat lib
Simple Access (jsp) <%@ page import="java.sql.DriverManager,java.sql.Statement,java.sql.ResultSet" %>
//…
// This is needed to use Connector/J. It basically creates a new instance of the Connector/J jdbc driver.
Class.forName("com.mysql.jdbc.Driver").newInstance();
java.util.Properties pp = new java.util.Properties();
pp.put("user", “root");
pp.put("password",”");
java.sql.Connection conn = DriverManager.getConnection("jdbc:mysql://“localhost:3306/mysql",pp);
Statement sqlStatement = conn.createStatement();
ResultSet sqlResult = sqlStatement.executeQuery(“select user,host , @@port from user”);
while(sqlResult.next()) { // looping the resultset
out.println("<td>" + sqlResult.getString(“user”) + "</td><td> " + sqlResult.getString(“host”) + "</td><td> " + sqlResult.getString(“@@port”) + "</td>");
}
sqlResult.close(); sqlStatement.close(); conn.close();
Using Connection Pool
• Tomcat –
– Server based context.xml
– App based context.xml
context.xml <?xml version="1.0" encoding="UTF-8"?>
<Context>
<Resource
auth="Container"
description="DB Connection"
name="jdbc/fabric01"
type="javax.sql.DataSource"
driverClassName="com.mysql.jdbc.Driver"
maxIdle="2"
maxWait="5000"
validationQuery="/* ping */ select 1"
username="myuser"
password="mypass“
url="jdbc:mysql://192.168.56.21:3306/mysql"
maxActive="4"/>
</Context>
Connect using datasource javax.naming.Context ctx = new javax.naming.InitialContext();
javax.sql.DataSource ds = (javax.sql.DataSource) ctx.lookup("java:/comp/env/jdbc/mysql01");
java.sql.Connection conn = ds.getConnection();
The ping statement • https://dev.mysql.com/doc/connector-j/en/connector-j-usagenotes-j2ee-concepts-
connection-pooling.html
• specify a validation query in your connection pool that starts with /* ping */. Note that the syntax must be exactly as specified. This will cause the driver send a ping to the server and return a dummy
• validationQuery="/* ping */ select 1”
High Availability – MySQL How Client / Web Applications accessing Master and Slave Multi-Master Sharded Data in MySQL Fabric
MySQL Replication
MySQL Fabric
DRBD
Windows/Solaris/Clusterware Clustering or Oracle VM
MySQL Cluster
MySQL HA Solutions
Getting Higher & Higher Availability Getting Higher & Higher Availability
Copyright 2015, Oracle and/or its affiliates. All rights reserved 17
Master and Slave Replication
• Connector /J
– Driver : com.mysql.jdbc.ReplicationDriver
– URL : jdbc:mysql:replication://master,slave1,slave2/db?param…
Copyright 2015, oracle and/or its affiliates. All rights reserved 18
Master :3306
Binary Logs
Relay Log
Relay Log
Slave 1 :3326
Slave2 :3336
High Availability Slave Access Properties in Connector/J
• For Slave(s), 1 x Master to Many Slaves
– Providing Slave Redundancy for Read Application
– pp.put("roundRobinLoadBlance","true");
– pp.put("loadBalanceBlacklistTimeout", "30000");
– pp.put("connectTimeout", "50");
• Demo
Copyright 2015, oracle and/or its affiliates. All rights reserved 19
java.util.Properties pp = new java.util.Properties();
Copyright 2015, oracle and/or its affiliates. All rights reserved 20
High Availability – Multi-Master Access Properties in Connector/J
• For READ-Only Applications, all DB servers are considered to be readable.
• For Multi-Master
– pp.put("roundRobinLoadBlance","true");
– pp.put("loadBalanceBlacklistTimeout", "30000");
– pp.put("connectTimeout", "50");
– pp.put("autoReconnect", "true");
– ppput("failOverReadOnly", "true"); // can set to false for dual Master config
– pp.put("allowMasterDownConnections", "true");
• Demo
Copyright 2015, oracle and/or its affiliates. All rights reserved 21
java.util.Properties pp = new java.util.Properties();
MySQL Fabric
• The MySQL Sharding and High Availability Framework
• Data distributed into multiple servers
• Connector/J
– Driver : com.mysql.fabric.jdbc.FabricMySQLDriver
– url : jdbc:mysql:fabric://192.168.56.21:32274/mysql?fabricUsername=admin&fabricPassword=admin&fabricReportError=true
• Fabric Server has additional Username and Password – On URL : specify fabricUsername and fabricPassword
– Alternative : provide connectionProperties=fabricUsername=admin;fabricPassword=admin
Copyright 2015, oracle and/or its affiliates. All rights reserved 22
Sample context.xml for Resource on Fabric • <Resource name="jdbc/fabricPool01"
• auth="Container” type="javax.sql.DataSource"
• factory="org.apache.tomcat.jdbc.pool.DataSourceFactory"
• testWhileIdle="true” testOnBorrow="true"
• testOnReturn="false“ validationQuery="/* ping */"
• validationInterval="30000” timeBetweenEvictionRunsMillis="30000"
• maxActive="100” minIdle="10” maxWait="10000"
• initialSize="10” removeAbandonedTimeout="120"
• removeAbandoned="true” logAbandoned="true"
• minEvictableIdleTimeMillis="30000” jmxEnabled="true"
• jdbcInterceptors="org.apache.tomcat.jdbc.pool.interceptor.ConnectionState;
• org.apache.tomcat.jdbc.pool.interceptor.StatementFinalizer"
• username="fabric” password="secret“
• driverClassName="com.mysql.fabric.jdbc.FabricMySQLDriver“
• connectionProperties="fabricUsername=admin;fabricPassword=admin;fabricReportError=true"
• url="jdbc:mysql:fabric://192.168.56.21:32274/mysql"/>
Copyright 2015, oracle and/or its affiliates. All rights reserved 23
Sample Code to access Fabric • ctx = new InitialContext();
• DataSource ds = (DataSource) ctx.lookup("java:/comp/env/jdbc/fabricPool01");
• try{
• //get a java.sql.Connection from the pool
• conn = ds.getConnection( );
• JDBC4FabricMySQLConnection fabricConn = conn.unwrap(JDBC4FabricMySQLConnection.class);
• fabricConn.setServerGroupName("fabric_test1_shard1");
• stmt = conn.createStatement( );
• //execute a SQL statement,generating a ResultSet
• rs = stmt.executeQuery(sql);
• ….
Copyright 2015, oracle and/or its affiliates. All rights reserved 24
fabric_test1_Shard1
fabric_test1_Shard2
fabric_test1_global
Sample Code to access Fabric • ctx = new InitialContext();
• DataSource ds = (DataSource) ctx.lookup("java:/comp/env/jdbc/fabricPool01");
• try{
• //get a java.sql.Connection from the pool
• conn = ds.getConnection( );
• JDBC4FabricMySQLConnection fabricConn = conn.unwrap(JDBC4FabricMySQLConnection.class);
• fabricConn.setServerGroupName("fabric_test1_shard1");
• stmt = conn.createStatement( );
• //execute a SQL statement,generating a ResultSet
• rs = stmt.executeQuery(sql);
• ….
Copyright 2015, oracle and/or its affiliates. All rights reserved 25
fabric_test1_Shard1
fabric_test1_Shard2
fabric_test1_global
Sample Code to access Fabric • ctx = new InitialContext();
• DataSource ds = (DataSource) ctx.lookup("java:/comp/env/jdbc/fabricPool01");
• try{
• //get a java.sql.Connection from the pool
• conn = ds.getConnection( );
• JDBC4FabricMySQLConnection fabricConn = conn.unwrap(JDBC4FabricMySQLConnection.class);
• fabricConn.setServerGroupName("fabric_test1_shard1");
• stmt = conn.createStatement( );
• //execute a SQL statement,generating a ResultSet
• rs = stmt.executeQuery(sql);
• ….
• fabricConn.setServerGroupName("fabric_test1_shard2");
• rs = stmt.executeQuery(sql);
Copyright 2015, oracle and/or its affiliates. All rights reserved 26
fabric_test1_Shard1
fabric_test1_Shard2
fabric_test1_global
Access Data vis setShardKey in MySQL Fabric • PreparedStatement ps = connection.prepareStatement(
"INSERT INTO employees.allemployees VALUES (?,?,?,? )");
• for (int i = 1; i <= 1000; ++i) {
• Integer myid = new Integer(i);
• // choose the shard that handles the data we interested in
• connection.setShardKey(myid.toString());
• // perform insert in standard fashion
• ps.setInt(1, myid);
• ps.setString(2, firstNames[new java.util.Random().nextInt(12)]);
• ps.setString(3, lastNames[new java.util.Random().nextInt(12)]);
• ………..
• ps.executeUpdate();
• }
February 2015 Copyright 2015, oracle and/or its affiliates. All rights reserved 27
Connecting to MySQL Cluster
February 2015 Copyright 2015, oracle and/or its affiliates. All rights reserved 28
NoSQL Access to MySQL Cluster data
ClusterJ
MySQL
JDBC
Apps
JPA
JNI
Python Ruby
ClusterJPA
Apps Apps Apps Apps Apps
Node.js
JS
Apps
mod-ndb
Apache
Apps
ndb-eng
Memcached
Apps Apps
NDB API (C++)
MySQL Cluster Data Nodes
Apps
PHP PERL
Apps
Java
NoSQL Access to MySQL Cluster data
ClusterJ
MySQL
JDBC
Apps
JPA
JNI
Python Ruby
ClusterJPA
Apps Apps Apps Apps Apps
Node.js
JS
Apps
mod-ndb
Apache
Apps
ndb-eng
Memcached
Apps Apps
NDB API (C++)
MySQL Cluster Data Nodes
Apps
PHP PERL
Apps
Java Connector/J
• There are few key points in using the Connector/J with Load Balancing on MySQL Cluster - Dynamic JMX with Connector/J loadBalanceEnableJMX=true&loadBalanceConnectionGroup=<group>
• - Timeout value in the blacklist with Connector/J loadBalanceBlacklistTimeout
http://mysqlhk.blogspot.hk/2014/11/connectorj-load-balancing-for-mysql.html
Connector/J – Load Balancing for MySQL Cluster
Connector/J and Monitoring Commercial Product
• Using MySQL Enterprise Monitor
Tomcat Pool Configuration
• Copy c-java-mysql-enterprise-plugin-<version>jar and commons-logging-1.1.1.jar
– From <MySQL Monitor Home>/apache-tomcat\webapps\ROOT\WEB-INF\lib
– To Apache Tomcat lib
• Edit context.xml
<Context> <Resource …..
connectionProperties="statementInterceptors=com.mysql.etools.jdbc.StatementPerformanceCounters;serviceManagerUrl=http://localhost:18080/;serviceManagerUser=agent;serviceManagerPassword=agent“
…
/>
<Context>
Notes Connections to MySQL take a long time to authenticate (Doc ID 1360836.1)
• IP to Name Resolution
– Client IP is to be resolved to name on Database Server
– if it takes long time, the connection will take long time to complete
– Resolution • Server to start with skip-name-resolve
• Put the client IP/name to your hosts file
• Fix the DNS
• useConfigs settings
• Do not forget granting access on MySQL Server
• Retry Master (loadBalanceBlacklistTimeout )
Resources
• http://dev.mysql.com/downloads/connector/j/
• https://dev.mysql.com/doc/connector-j/en/index.html
• http://downloads.mysql.com/docs/connector-j-en.pdf
• http://mysqlhk.blogspot.hk/2014/11/connectorj-load-balancing-for-mysql.html
• http://dev.mysql.com/doc/mysql-monitor/3.0/en/mem-qanal-using-cj.html
February 2015 Copyright 2015, oracle and/or its affiliates. All rights reserved 35
Connecting to MySQL Connector/J - High Availability
Questions?
February 2015