20151010 my sq-landjavav2a

Connecting to MySQL Connector/J - High Availability

Ivan Ma [email protected] 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


Connecting to MySQL

• Native Client (mysql, other tools)

• Building Applications

– MySQL Connector


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


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


Building the java - select “helloworld” app

• Pre-requisite :

– A running MySQL database (e.g. localhost:3306)


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


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();


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”

https://dev.mysql.com/doc/connector-j/en/connector-j-usagenotes-j2ee-concepts-connection-pooling.html















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



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



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


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


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

• ….


fabric_test1_Shard1

fabric_test1_Shard2

fabric_test1_global



• try{








• ….


fabric_test1_Shard1

fabric_test1_Shard2

fabric_test1_global



• try{








• ….




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();

• }


Connecting to MySQL Cluster


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

http://dev.mysql.com/doc/connector-j/en/connector-j-reference-configuration-properties.html


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


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://dev.mysql.com/doc/mysql-monitor/3.0/en/mem-qanal-using-cj.html









Connecting to MySQL Connector/J - High Availability

Questions?

February 2015

20151010 my sq-landjavav2a

Technology

Transcript of 20151010 my sq-landjavav2a