Docstoc

Mysql

Document Sample
Mysql Powered By Docstoc
					         Mysql
The open source database
        solution.

      George Bongiovano
             What is it?
• Open source Relational Database
  Management System (RDBMS)
• Free under GNU Public License (GPL)
• Pronounced "my-ess-queue-ell” OR “my
  see quel”
               History
• 14 years old
• Named from SQL the IBM query
  language
• “My” daughter of Finnish designer
  Micheal “Monty” Widenius.
               Types
• Community Server
• Enterprise server
Architecture
                Storage engines
• MyISAM
  – the default storage engine. (index, table definition, data)

• InnoDB
  – maximum performance when processing large data volumes
  – Originally Oracle code
  – Excellent for db crashes, replays recovery logs.

• HEAP
  – Creates tables with contents that is stored in memory.
• NDB
  – Used when clustering
                  Tools
•   (CLI)
•   MySQL Administrator (mysqladmin)
•   MySQL QueryBrowser (mysql)
•   MySQL MigrationToolkit
•   MySQL Workbench
•   Myphpadmin php gui interface
               The Basics
•   Unidirectional Replication
•   Bidirectional Replication
•   Clustering
•   Data recovery and checkpoints
•   Index Recovery
       Programming and
         Development
• Java, C, C++, PERL, Ruby, PHP.
                                                           Java
•   package com.georgeB.example;

•   import java.sql.Connection;
•   import java.sql.DriverManager;
•   import java.sql.SQLException;

•   public class GeorgeBExample {

•    public static void main(String args[]) {
•     Connection con = null;

•        try {
•          Class.forName("com.mysql.jdbc.Driver").newInstance();
•          con = DriverManager.getConnection("jdbc:mysql:///test",
•           "root", "secret");

•         if(!con.isClosed())
•           System.out.println("Successfully connected to " +
•             "MySQL server using TCP/IP...");

•        } catch(Exception e) {
•          System.err.println("Exception: " + e.getMessage());
•        } finally {
•          try {
•            if(con != null)
•              con.close();
•          } catch(SQLException e) {}
•        }
•    }
                                                       C/C++
•   #include &ltstdio.h>
•   #include &ltmysql.h>
•   #define host "localhost"
•   #define username "db_username"
•   #define password "db_password"
•   #define database "db"You will have to change the values in the quotes to match the values that fit you. The next line creates a int
    main()
•   {
•   conn = mysql_init(NULL);
•   mysql_real_connect(conn,host,username,password,database,0,NULL,0);

•   MYSQL_RES *res_set;
•   MYSQL_ROW row;
•   unsigned int i;
•   mysql_query(conn,"SELECT * FROM users WHERE userid=1");
•   res_set = mysql_store_result(conn);
•   unsigned int numrows = mysql_num_rows(res_set);
•   while ((row = mysql_fetch_row(res_set)) != NULL)
•   {
•   for (i=0; i&ltmysql_num_fields(res_set); i++)
•   {
•   And finally, after all this time, we print the stuff out using the plain old C standard function printf():
•   printf("%s\n",row[i] != NULL ? row[i] : "NULL");
•     }
•   }
•   mysql_close(conn);
•   return 0;
•   }
                                         PERL
•   #!/usr/local/bin/perl

•   use DBI;

•   #### main proggy

•     $dbpath = "dbi:mysql:database=database_name;host=mysql.iu.edu:port_number";
•     $dbh = DBI->connect($dbpath, "user_name", "password")
•      or die "Can't open database: $DBI::errstr";

•   print "Content-type:text/html", "\n\n";

•   print ”Booyah!!! we’re connected!! \n”;

•   exit 0;
                                             Ruby
•   #!/usr/bin/ruby -w
•     # simple.rb - simple MySQL script using Ruby MySQL module

•    require "mysql"

•    begin
•     # connect to the MySQL server
•     dbh = Mysql.real_connect("localhost", "testuser", "testpass", "test")
•     # get server version string and display it
•     puts "Server version: " + dbh.get_server_info
•    rescue Mysql::Error => e
•     puts "Error code: #{e.errno}"
•     puts "Error message: #{e.error}"
•     puts "Error SQLSTATE: #{e.sqlstate}" if e.respond_to?("sqlstate")
•    ensure
•     # disconnect from server
•     dbh.close if dbh
•    end
                                                       PHP
•   #!/usr/local/bin/php

•   <?php

•   //connect to the database server
•   $db = mysql_connect("mysql.marymount.edu:port_number", "user_name", "password");

•   //report the connection failure or success
•   if (!$db) {
•      echo "there was a problem connecting to the database.";
•      exit;
•   }

•   if ($db) {
•      echo "there was no problem connecting to the database.";
•      exit;
•   }

•   ?>
                              Resources
•   Badia, A., Chanda, M., and Cao, B. 2002. Adding subqueries to MySQL, what does
    it take to have a decision-support engine?. In Proceedings of the 5th ACM
    international Workshop on Data Warehousing and OLAP (McLean, Virginia, USA,
    November 08 - 08, 2002). DOLAP '02. ACM Press, New York, NY, 49-56. DOI=
    http://doi.acm.org/10.1145/583890.583898

•   Balling, D. Zawodny, J. 2004. High Performance MySQL.     California: O’Reilly

•   Butcher, T. 2002. Sams Teach Yourself MySQL in 21 Days Second Edition.

•   DuBois, P. 1999. MySQL.   California: QUE

•   Ronström, M. and Oreland, J. 2005. Recovery principles of MySQL Cluster 5.1.
    In Proceedings of the 31st international Conference on Very Large Data Bases
    (Trondheim, Norway, August 30 - September 02, 2005). Very Large Data Bases.
    VLDB Endowment, 1108-1115.

•   Kim, W. 1982. On optimizing an SQL-like nested query. ACM Trans. Database
    Syst. 7, 3 (Sep. 1982), 443-469. DOI= http://doi.acm.org/10.1145/319732.319745

•   the manual MySQL GUI Tools Manual. (2007)

				
DOCUMENT INFO
Shared By:
Categories:
Stats:
views:112
posted:3/10/2010
language:English
pages:15