Docstoc
EXCLUSIVE OFFER FOR DOCSTOC USERS
Try the all-new QuickBooks Online for FREE.  No credit card required.

MySQL for Java Developers - Oracle

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




MySQL for Java Developers
Pavan Venkatesh
Senior Sales Consultant
    AGENDA

•   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


                                           3
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



                                                      4
MySQL:
The World’s Leading On-Line Database




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

                                                             5
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


                                                 6
        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




                                                   7
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++



                                                      8
       Pluggable Storage Engines Architecture
       MySQL Server
                                                                                              Connectors
      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
               Security
             Replication
               Cluster
                                                 SQL Interface                        Parser                       Optimizer                         Caches
             Partitioning
                                          DDL, DML, Stored Procedures,       Query Translation, Object       Access Paths, Statistics       Global and Engine Specific
          Instance Manager
                                              Views, Triggers, Etc..                Privileges                                                 Caches and Buffers
        Information_Schema
         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




                                                                                                                                                                         9
Languages




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


                                                                 10
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




                                                                   11
Connector/J




• 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


                                                                                              12
 Basics of Connector/J

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


  jdbc:mysql://[host][:port]/[database] » [?propertyName1][=propertyValue1]
  [&propertyName2][=propertyValue2]...


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




                                                                                    13
Basics of Connector/J
Unlike Under MySQL Client

USE database1;

SELECT COUNT(*) FROM table1; # selects from
database1.table1

USE database2;

SELECT COUNT(*) FROM table; # selects from
database2.table

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




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




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




                                                                                                             16
Load Balancing/Failover Use Cases
• Directly (jdbc:mysql:loadbalance:// host-1,host-2,...host
  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://)




                                                              17
                MySQL Cluster Architecture
                                                               Clients




                             MySQL Cluster Application Nodes




                                                                         MySQL Cluster
MySQL Cluster                                                            Mgmt
Mgmt




                               MySQL Cluster Data Nodes




                                                                                         18
   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.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 a,b FROM alt_table");




                                                                                                             19
            MySQL Replication
  Clients
            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



                                                                                             20
 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
  milliseconds)

jdbc:mysql:loadbalance://host-1,host-2,...host-n/database?loadBalanceBlacklistTimeout=5000

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




                                                                                             21
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



                                                                          22
Internals of Connector/MXJ




                             23
MySQL- XML

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




                                                                                   24
     ExtractValue()
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           |
  LIMIT 2
                                                                 +-----------------------------------------+
" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
                                                                 1 row in set (0.00 sec)
 <row>
     <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)
</resultset>




                                                                                                                                 25
     UpdateXML()
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;
 @new_xml:=UpdateXML('<book><chapter/></book>',
                                                                         +--------------------------------------+
   ->    '//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)




                                                                                                                        26
     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
• SIGNAL/RESIGNAL
• More Partitioning Options
• New PERFORMANCE_SCHEMA



                                                     27
MySQL 5.5 Scales on multi core
SysBench Read Write
                                                         GA




                                 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.




                                                                      28
 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
                                         GA
            MySQL Enterprise Monitor 2.2
                       GA
              MySQL Cluster 7.1
                    GA
            MySQL Cluster Manager 1.0
                     GA
                                                              A Better MySQL

Q1 CY2010          Q2 CY2010               Q3 CY2010              Q4 CY2010




                                                                                        30
New Packaging and Pricing




                            31
     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


                                                           Database

                                                                                                                           32
    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!



                                                                                                33
                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




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



                                                              35
High Performance Backups
                                                    GA




     Backups are up to 3.5x Faster than mysqldump

                                                         36
High Performance Restore
                                                  GA




     Restore is up to 16x Faster than mysqldump

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


                                                           38
   MySQL 5.6 – A Better MySQL
                                                     DM
                                                   Download!
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
                   dev.mysql.com/downloads/mysql

                                                               39
   MySQL 5.6:
                                                                                                                 DM
   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
      mysqld
                                                                    • 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


                                                                                                                           40
Useful Links

Connector J
http://dev.mysql.com/downloads/connector/j/

MySQL Replication Whitepaper
http://www.mysql.com/why-mysql/white-papers/mysql-wp-replication.php


MySQL On-Demand Webinars
http://www.mysql.com/news-and-events/on-demand-webinars/

MySQL Enterprise Whitepaper
http://www.mysql.com/why-mysql/white-papers/mysql_wp_enterprise_ready.php




                                                                            41
Need to contact ????

                    Chandra Balani
             MySQL Country Sales Manager
                 Mobile: +91.9000302255
                 Office: +914066581726
            Email: Chandra.Balani@oracle.com


                       Ronen Baram
             MySQL Senior Sales Consultant
                Mobile: +61 400 77 35 44
            Email: Ronen.Baram@oracle.com



                                               42
43
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



                                                                  45
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
  applications.
• Easy to embed a full blown MySQL server (mysqld) into your
  Java applications



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




                                                                                         47

				
DOCUMENT INFO