MySQL for Java Developers - Oracle

Document Sample
MySQL for Java Developers - Oracle Powered By Docstoc
 <Insert Picture Here>

MySQL for Java Developers
Pavan Venkatesh
Senior Sales Consultant

•   MySQL Overview
•   MySQL Connectors overview
•   Connector/J
•   Load Balancing and Failover examples
•   Connector MXJ
•   XML support
•   MySQL 5.5 and Enterprise tools
•   MySQL 5.6 DM

Overview of MySQL

• 12 million product installations

• Over 100 million copies downloaded worldwide

• Over 70,000 downloads each day

• Sun acquired MySQL in April 2008

• Oracle completed Sun acquisition in February 2010

• Majority of Oracle customers also use MySQL

The World’s Leading On-Line Database

Powering 9 of the top 10 most trafficked sites on the web*

Oracle’s Investment in MySQL
Rapid Innovation
Make MySQL a Better MySQL
• #1 Open Source Database for Web Applications
• Most Complete LAMP Stack
• Telecom & Embedded

Develop, Promote and Support MySQL
• Improve engineering, consulting and support
• Leverage 24x7, World-Class Oracle Support

MySQL Community Edition
• Source and binary releases
• GPL license

        Oracle + MySQL Customers

• Product     Integration
     - Oracle GoldenGate (Complete!)
     - Oracle Enterprise Linux + Oracle VM
     - Oracle Secure Backup
     - Oracle Audit Vault
     - Oracle Enterprise Manager

•   Support
     - Leverage 24x7, World-Class Oracle Support
     - MyOracle Support

MySQL Connectors

 Developed by MySQL   Developed by Community
• Connector/ODBC
                      • PHP drivers
• Connector/J
                      • Perl Driver
• Connector/Net
                      • Python driver
• Connector/MXJ
                      • Ruby driver
• Connector/C
                      • C++ wrapper for MySQL C API
• Connector/C++

       Pluggable Storage Engines Architecture
       MySQL Server
      Clients and Apps                                                   Native C API, JDBC, ODBC, .Net, PHP, Ruby, Python, Perl

Enterprise Management Services and
               Utilities                                                                         Connection Pool
         Backup & Recovery                                          Authentication – Thread Reuse – Connection Limits – Check Memory – Caches
                                                 SQL Interface                        Parser                       Optimizer                         Caches
                                          DDL, DML, Stored Procedures,       Query Translation, Object       Access Paths, Statistics       Global and Engine Specific
          Instance Manager
                                              Views, Triggers, Etc..                Privileges                                                 Caches and Buffers
         MySQL Workbench

                                                                   Pluggable Storage Engines
                                                              Memory, Index and Storage Management

          InnoDB      MyISAM    Cluster       Etc…                                                                              Partners Community     More..

                                                                  Filesystems, Files and Logs
                                                      Redo, Undo, Data, Index, Binary, Error, Query and Slow


        - 57% of MySQL Enterprise Customers Develop Using Java
        -73% of MySQL Community Customers Develop Using PHP

Connectors for Java
• MySQL Connector/J is a MySQL-developed JDBC connector for Java
  client applications
   – JDBC Type 4 driver

• Connector/J with J2EE and Other Java Frameworks
   – GlassFish
   – Jboss
   – Spring
   – Tomcat


• Connector/J 5.1 provides ease of development features

• Per connection client information
   – Categorized SQLExceptions
   – Standardized validity checks

• Connector/J 5.0 includes XA support

• Connector/J 3.0 does not support server-side prepared statements,and does not support any
  of the features in versions of MySQL later than 4.1

 Basics of Connector/J

• The JDBC URL format for MySQL Connector/J is as follows

  jdbc:mysql://[host][:port]/[database] » [?propertyName1][=propertyValue1]

  If the host name is not specified, it defaults to If the port is not
  specified, it defaults to 3306, the default port number for MySQL servers.

Basics of Connector/J
Unlike Under MySQL Client

USE database1;

SELECT COUNT(*) FROM table1; # selects from

USE database2;

SELECT COUNT(*) FROM table; # selects from

JDBC clients should never employ the USE database statement to specify the desired database,
they should always use the Connection.setCatalog() method instead.

Connector/J example
 import java.sql.Connection;
 import java.sql.DriverManager;
 import java.sql.SQLException;
 import java.sql.Statement;
 import java.sql.ResultSet;
 Connection conn = null;
 Statement stmt = null;
 ResultSet rs = null;
 try {
    Conn = DriverManager.getConnection("jdbc:mysql://localhost/test?" +"user=root&password=mysql");
        stmt = conn.createStatement();
        rs = stmt.executeQuery("SELECT foo FROM bar");
 if (stmt.execute("SELECT foo FROM bar")) {
         rs = stmt.getResultSet();
 } catch (SQLException ex) {
    System.out.println("SQLException: " + ex.getMessage());
    System.out.println("SQLState: " + ex.getSQLState());
    System.out.println("VendorError: " + ex.getErrorCode()); }

Fail Over Support
• MySQL Connector/J has fail-over support. This enables the driver to fail over to any number of slave
  hosts and still perform read-only queries. Fail over only happens when the connection is in an
  autoCommit(true) state, because fail-over can not happen reliably when a transaction is in progress.

• The fail-over functionality has the following behavior:

   – If the URL property autoReconnect is false: Failover only happens at connection initialization,
      and failback occurs when the driver determines that the first host has become available again.

   – If the URL property autoReconnect is true: Failover happens when the driver determines that the
      connection has failed (before every query), and falls back to the first host when it determines that
      the host has become available again

• In either case, whenever you are connected to a "failed-over" server, the connection will be set to
  read-only state, so queries that would modify data will have exceptions thrown.

Load Balancing/Failover Use Cases
• Directly (jdbc:mysql:loadbalance:// host-1,host-2,
  n/ database):
  – Clustered (NDB) deployment where both read and write
    operations are distributed across all hosts.

• Indirectly:
  – Replication deployments where read-only load can be
    distributed to slaves (jdbc:mysql:replication://)

                MySQL Cluster Architecture

                             MySQL Cluster Application Nodes

                                                                         MySQL Cluster
MySQL Cluster                                                            Mgmt

                               MySQL Cluster Data Nodes

   Read-only load distribution

props.put("autoReconnect", "true"); // We want this for failover on the slaves
props.put("roundRobinLoadBalance", "true"); // We want to load balance between the slaves
props.put("user", "foo");
props.put("password", "bar");

Connection conn=driver.connect("jdbc:mysql:replication://master,slave1,slave2,slave3/test", props);

conn.setReadOnly(false); // Perform read/write work on the master by setting the read-only flag to "false"
conn.createStatement().executeUpdate("UPDATE some_table ....");
// Now, do a query from a slave, the driver automatically picks one from the list
ResultSet rs =conn.createStatement().executeQuery("SELECT a,b FROM alt_table");

            MySQL Replication
            Read Scalability

                                                                MySQL Replication

                                             Slaves                                 Master

• Used by leading web properties for scale-out
• Reads are directed to slaves, writes to master
• Delivers higher performance & scale with efficient resource utilization

 Dont Forget !

• The "loadBalanceBlacklistTimeout" adds the needed feature that failed
  connections in a connection pool are put aside for the specified time, and
  only working connections are utilized.

• Set “loadBalanceBlacklistTimeout” to your best case outage window (in


                  (setting it to zero means hosts won’t be automatically blacklisted)

Connector MXJ

• Connector/MXJ is a Java utility package for deploying and managing a
  MySQL database.
• Not an embedded version of MySQL
• Not a version of MySQL written as part of a Java class
• Works through the use of an embedded, compiled binary of mysqld
• The same as deploying a standard MySQL installation

 It is the Connector/MXJ wrapper, support classes and tools that enable
 Connector/MXJ to appear as a MySQL instance

Internals of Connector/MXJ


• You can retrieve data from MySQL in XML format, and store data obtained
 from an XML source into the database.
• Better XML handling through Xpath functions
  – ExtractValue() allows you to use an XPath expression on a fragment of XML in
    order to return the content of one or more elements
  – UpdateXML() makes it possible to replace an existing XML fragment with a new
    one, using XPath to specify the fragment to be replaced.

mysql> SELECT @xml\G                                             mysql> SELECT ExtractValue(@xml, '//row[2]/field[1]');
*************************** 1. row ***************************   +-----------------------------------------+
@xml: <?xml version="1.0"?>                                      | ExtractValue(@xml, '//row[2]/field[1]') |
<resultset statement="SELECT name,country FROM cities            | Seoul           |
" xmlns:xsi="">
                                                                 1 row in set (0.00 sec)
     <field name="name">Mumbai (Bombay)</field>                  Another way-
     <field name="country">India</field>
 </row>                                                          mysql> SELECT ExtractValue(@xml, '//field[@name="name"][2]');

 <row>                                                           | ExtractValue(@xml, '//field[@name="name"][2]') |
     <field name="name">Seoul</field>                            +------------------------------------------------+
     <field name="country">South Korea</field>                   | Seoul       |
 </row>                                                          +------------------------------------------------+
                                                                 1 row in set (0.00 sec)

Consider the XML fragment <book><chapter/></book>. Now suppose you wish to change this to
<book><part><chapter/></part></book>. This shows how you can do so using UpdateXML(), saving the result into a user
variable @new_xml:

mysql> SELECT                                                            mysql> SELECT @new_xml;
   ->    '//chapter',
                                                                         | @new_xml                                 |
   ->    '<part><chapter/></part>')
   -> AS uxml;                                                           +--------------------------------------+
+--------------------------------------------------------------------+   | <book><part><chapter/></part></book> |
| uxml                                                      |            +--------------------------------------+
+--------------------------------------------------------------------+   1 row in set (0.00 sec)
| <book><part><chapter/></part></book> |
1 row in set (0.00 sec)

     MySQL 5.5 – Highest Quality Release Ever
InnoDB becomes default storage engine
• ACID Transactions, FKs, Crash Recovery        GA

Improved Performance
• Scales upto 32 cores
• Improved Recovery time
• + 360% over 5.1 on Linux
• + 1500% over 5.1 on Windows
Improved Availability
• Semi-synchronous Replication
• Replication Heartbeat
Improved Usability
• More Partitioning Options

MySQL 5.5 Scales on multi core
SysBench Read Write

                                 AMD Opteron 7160 (Magny-Cours) @2100 MHz
                                                64 GB memory
                                          2 x Intel X25E SSD drives
                                    OS is Oracle Enterprise Linux with the
                                               Enterprise Kernel
                                      4 sockets with a total of 48 cores.

 MySQL 5.5 SysBench Benchmarks
 Windows                                      GA

                                      MySQL 5.5.6
                                      (New InnoDB)

                                      MySQL 5.1.50
                                      (InnoDB Plug-in)

                                      MySQL 5.1.50
                                      (InnoDB built-in)

                                      Intel x86_64
                                      4 CPU x 2 Cores/CPU
1561% performance gain                3.166 GHz, 8GB RAM
for MySQL 5.5 over 5.1.50; at scale   Windows Server 2008
                    MySQL Product Releases
                     Continuous Innovation
                                                       • MySQL Database 5.5
                                                       • MySQL Enterprise Backup 3.5
                                                       • MySQL Enterprise Monitor 2.3
                                                         MySQL Cluster Manager 1.1
                                                                   All GA now!
                                  MySQL Workbench 5.2
            MySQL Enterprise Monitor 2.2
              MySQL Cluster 7.1
            MySQL Cluster Manager 1.0
                                                              A Better MySQL

Q1 CY2010          Q2 CY2010               Q3 CY2010              Q4 CY2010

New Packaging and Pricing

     MySQL Enterprise Edition
Comprehensive offering of MySQL Database, Management tools, and Oracle
Lifetime Support services

   Management                                                                                                    Support
                                                           MySQL Enterprise Backup

                                MySQL Enterprise Monitor

                                                                                     Oracle Lifetime Support
               Performance Monitoring/ Alerts

                                                                                                     Service packs

             Query Analyzer

                                                                                                               Hot fixes
        MySQL Workbench


    MySQL Enterprise Monitor

• Single,consolidated view into entire MySQL
application development environment

• Auto-discovery of MySQL servers, replication topologies

• Automated, customizable rules-based monitoring,
tuning, SNMP/SMTP alerts

• Query Analyzer for query monitoring, analysis, tuning,
source code tracing

• Application Tuning during Dev/QA/Roll out

• Reduces risk of problems after apps are deployed

                                                            A Virtual MySQL Tuning Assistant!

                Monitoring Queries with Connector/J, NET
                      Application Server
                                                                                                                         MySQL Database

                                                               (Sql statements and result sets)
                            3. Connector/J, NET                                                                   3306

                              4. Plugin for Connector/J, NET

                                                                                                                                 2. MySQL Agent

                      (SQL performance data: statements,
                    examples, EXPLAINs, aggregated stats)
                                                                                          (MySQL & OS
                                                                   18080                 monitoring data)

                                                                                         1. MySQL Enterprise Monitor
                                                                                        (Service Manager, Dashboard)

List of components to download & configure:
1.   MySQL Enterprise Monitor & Query Analyzer (2.2)
2.   MySQL Agent (2.2)
3.   Connector/J v5.1 or newer/ Connector/NET v6.2 or newer
4.   New! MySQL Enterprise Plugin for Connector/J, NET

       MySQL Enterprise Backup

 Formerly “InnoDB Hot Backup”
 InnoDB Hot Backup is rebranded as MySQL Enterprise Backup
• Online, non-locking backup & recovery
    - Tables, Indexes
     - Server, database
•   Incremental backup
•   Point-in-time recovery
•   Compressed backups
•   Also provides backup & recovery for MyISAM
•   Cross-Platform (Windows, Linux, Unix)

High Performance Backups

     Backups are up to 3.5x Faster than mysqldump

High Performance Restore

     Restore is up to 16x Faster than mysqldump

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.

   MySQL 5.6 – A Better MySQL
Better InnoDB

    - Persistent Optimizer Stats
    - New INFORMATION_SCHEMA tables:

Better Replication
    - Crash-Safe Slaves
    - Multi-threaded Slaves
    - Replication Checksums
    - Time-Delayed Replication
    - Remote Binlog Backups

   MySQL 5.6:
   NotOnlySQL: Memcached API                                                                                  Download!

                                                                    • Fast, simple access to InnoDB
                               Application                             – Accessed via Memcached API
   SQL                                                                 – Use existing Memcached clients
                                                 NoSQL (Memcached
(MySQL Client)                                    Protocol)            – Bypasses SQL transformations
                                                                    • NotOnlySQL access
           MySQL                     Memcached
           Server                    plugin                            – Memcached for key-value operations
                                                                       – SQL for rich queries, JOINs, foreign keys, etc.
                 InnoDB Storage Engine
                                                                    • Implementation
                                                                       – Memcached daemon plug-in to mysqld
                                                                       – Memcached protocol mapped to the native
                                                                         InnoDB API
                                                                       – Shared process space for ultra-low latency
                                                                       – Additional implementations in future DMs

Useful Links

Connector J

MySQL Replication Whitepaper

MySQL On-Demand Webinars

MySQL Enterprise Whitepaper

Need to contact ????

                    Chandra Balani
             MySQL Country Sales Manager
                 Mobile: +91.9000302255
                 Office: +914066581726

                       Ronen Baram
             MySQL Senior Sales Consultant
                Mobile: +61 400 77 35 44

  Java Technology
• Java technology is both a programming language and a platform
• Open source
• The Java platform is ubiquitous:
   – More than 6.5 million developers
   – In every major industry segment
   – In wide range of devices, computers, and networks
• Java is
   – Simple
   – Architecture neutral
   – Object oriented and Portable
   – Distributed and High performance
   – Multithreaded and Robust
   – Dynamic & Secure

Why Java & MySQL

• Open source and community driven
• Ease of use
• Ubiquity
• MySQL’s high performance combined with powerful Java
  Servlet technology creates secure, robust, reliable, online
• Easy to embed a full blown MySQL server (mysqld) into your
  Java applications

Java - XML

• Java revolutionized the programming world by providing a platform-
  independent programming language.

• XML takes the revolution a step further with a platform-independent
  language for interchanging data.

  Java and XML share many features that are ideal for building web-based enterprise
   applications, such as platform-independence, extensibility, reusability, and global
        language (Unicode) support, and both are based on industry standards.