My sql crashcourse_intro_kdl

<Insert Picture Here> MySQL: Crash Course Introduction Keith Larson keith.larson@oracle.com MySQL Community Manager

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. Safe Harbor Statement

Who am I and who are you? Keith Larson keith.larson@oracle.com MySQL Community Manager sqlhjalp.blogspot.com Started with MySQL during the dot.com days. Primary real world work was with a MySQL InnoDB replicated chain environment that easily held over 4 billion rows of user data. Numerous other sites developed on LAMP stack over the last 13 years. Who are you? – DBAs? – Developers? – Already have replicated databases? – Cluster Users or Cluster curious?

• Oracle's Investment into MySQL • Provide high-level overview • Familiarize with the key concepts • Emphasize MySQL specificity • Community Edition, version 5.5 GA Agenda

The official way to pronounce “MySQL” is “My Ess Que Ell” but we do not mind if you pronounce it as “my sequel” Pronouciation

MySQL Crash Course Started in 80s Acquired by SUN Microsystems in 2008 for 1B USD Acquired by Oracle in 2009 Dual licensing: GPL v.2 + commercial “M” in “LAMP” stack Easy of use: the 15 minute rule Background of MySQL

Built together Tested together Managed together Serviced together Based on open standards Lower cost Lower risk More reliable Hardware and Software Engineered to Work Together MySQL Completes The Stack

More Product Releases Than Ever Before Continuous Innovation Q2 CY2010 Q3 CY2010 Q4 CY2010 Q1 CY2011 • MySQL Workbench 5.2 GA! • MySQL Database 5.5 • MySQL Enterprise Backup 3.5 • MySQL Enterprise Monitor 2.3 • MySQL Cluster Manager 1.1 All GA! A Better MySQL Q2 CY2011 • MySQL Enterprise Monitor 2.2 • MySQL Cluster 7.1 • MySQL Cluster Manager 1.0 All GA! • MySQL Database 5.6 • MySQL Cluster 7.2 DMR* and MySQL Labs! (“early and often”) *Development Milestone Release

Enterprise 2.0SaaS, Cloud Web OEM / ISV’s Telecommunications Industry Leading Customers Rely on MySQL

>70% of Oracle shops run MySQL mysql.com/downloads/ MySQL: Still Free, Open to the Community Available to download and use under the GPL: • MySQL Database (Community Server) • MySQL Cluster • MySQL Workbench Community Edition • MySQL Connectors • MySQL Proxy • Documentation (free to use, not covered under GPL) • Forums

“I’m really blown away by MySQL 5.5’s improvements. “ - Don MacAskill, SmugMug “My expectations for 5.5 were not high. I am pleasantly surprised!” - Mark Callaghan, Facebook, MySQL UC Keynote "Oracle really did a great job with MySQL 5.5 -- in record time! It has lots of new features and performance improvements that our customers need and want. We're very excited about this release." - Sheeri K. Cabral, Oracle Ace Director and Database Operations Manager, PalominoDB MySQL 5.5 Early Adopters Speak! GA

MySQL Components MySQL Server MySQL Workbench mysql (cmd-line client) System Tools MySQL Enterprise Monitor Direct I/O “Not Only SQL” NoSQL MySQL Proxy Cn Cn Cn Your ApplicationCn SQL

MySQL Server Standalone (mysqld) UNIX daemon Windows service Regular process on UNIX or Windows Embedded (libmysqld) Shared / Dynamic library

MySQL Server Components InnoDB DB1 InnoDB DB2 MyISAM DB3 InnoDB MyISAM Core SQL-query processing, … Plugin 1 Plugin 2 Plugin 3 The Core Plugins Storage Engines Full-text search plugins Audit plugins Authentication plugins …

MySQL Server Components SE defines data storage and retrieval Every regular table belongs to some SE Most known Storage Engines: InnoDB (default since 5.5) – fully transactional SE MyISAM (default prior to 5.5) – NON-transactional SE Default Storage Engine Storage Engine

• Default Storage Engine for MySQL 5.5 and above • ACID-compliant transactions, MVCC • Row-level locking • Two phase commit • Efficient indexing • Fast DDL operations • Table compression • Automatic crash recovery • Referential integrity • Online backup • More MySQL Database InnoDB - Transactional by Default

Storage Engines: Select a specialized storage engine for a particular application need. InnoDB: a high-reliability and high-performance storage engine for MySQL designed for transaction processing. It follows the ACID model. Row-level locking and Oracle-style consistent reads increase multi-user concurrency and performance MyISAM: -oldest storage engine has many features that have been developed over years. Memory: creates tables with contents that are stored in memory. MySQL Cluster offers the same features as the MEMORY engine with higher performance levels, and provides additional features

Storage Engines: CSV: data file is a plain text file ARCHIVE: is used for storing large amounts of data without indexes in a very small footprint. BLACKHOLE:accepts data but throws it away and does not store it but the binary log is enabled.

MySQL Server Components Open Source A Storage Engine Horizontal partitioning (distribute rows, not columns) Partitioning functions: The modulus Range Internal hashing function Linear hashing function Partitioning

MySQL Server Components Open Source One-way, master and slaves Asynchronous or Semi-synchronous replication Replication formats: Statement-based replication (SBR): propagate SQL statements Row-based replication (RBR): propagate row changes Mixed-based replication: SBR or RBR depending on the query Replication

MySQL Replication Overview Native in MySQL Used for Scalability and HA Asynchronous as standard Semi-Synchronous support added in MySQL 5.5 Each slave adds minimal load on master

Writes & Reads Reads Reads • Write to one master • Read from many slaves, easily add more as needed • Perfect for read/write intensive apps Application MySQL Replication Load Balancer MySQL Database Replication Enables Scalability

MySQL Server Components Open Source Different source tree, different versioning (7.x) In-memory, shared-nothing architecture “Synchronous, multi-master replication” Cluster

MySQL Architecture Parallel Database with no SPOF: High Read & Write Performance & 99.999% uptime MySQL Cluster Data Nodes MySQL Cluster Application Nodes Clients MySQL Cluster Mgmt LDAPREST NoSQL SQL,JDBC, ODBC,ADO

Node Failure Detection & Self-Healing Recovery

Cluster – Typical Performance Availability 99.999% (<5 min downtime / year) Performance Response Time 2-5 millisecond (with synchronous replication and access via NDB API Throughput of 10,000+ replicated transactions/sec on a 2 Node Cluster, with 1 CPU Per Node (minimal configuration) Throughput of 100,000 replicated transactions/sec on 4 Node Cluster, with 2 CPU Per Node (low-end configuration) Failover Sub-second failover enables you to deliver service without interruption GA

Backup The way depends on the application Possible solutions: MySQL Enterprise Backup Replication mysqldump … Overview

MySQL Enterprise Backup • Online Backup for InnoDB • Full, Incremental, Partial Backups (scriptable interface) • Compression • Point in Time, Full, Partial Recovery options • Metadata on status, progress, history • Unlimited Database Size • Cross-Platform • Windows, Linux, Unix • Certified with Oracle Secure Backup MEB Backup Files MySQL Database Files mysqlbackup Ensures quick, online backup and recovery of your MySQL apps.

Is it standard SQL? Yes aim is to support the full ANSI/ ISO SQL standard, but without making concessions to speed and quality of the code No Depends on engine

MySQL Concepts MySQL aims to follow The SQL Standard Not PL/SQL Prepared Statements Views Stored Programs SHOW statements and especially SHOW CREATE … Administrative statements SQL

MySQL Concepts Database or Schema Current database (per connection) Database – a set of files in “the data directory” System database (mysql) Virtual databases: INFORMATION_SCHEMA PERFORMANCE_SCHEMA Database

MySQL Concepts Full Unicode 5.0 for data Encodings: UTF-8, UTF-16, UTF-32, UCS-2 Two encodings for UTF-8: utf8 – 1..3 bytes, BMP only utf8mb4 – 1..4 bytes, BMP and supplemental Alias utf8mb3 == utf8 Metadata are in utf8 (no supplementary characters in identifiers) Unicode

MySQL Concepts Know your options 9 numeric data types CHAR, VARCHAR BLOBs Date/time types … Weak typing SELECT 1 + ‘ 2nd place’ => 3 Types

MySQL Concepts Error: code, level, SQL-state, message Error messages are localized Error level: errors, warnings, notes The actual error level depends on configuration Serious errors might be thrown as warnings: SELECT 1 + ‘ 2nd place’ Truncated incorrect DOUBLE value: ' 2nd place' SHOW WARNINGS SQL Conditions

MySQL Variables User Variables: – Weakly typed – SESSION variables – Arbitrary names – Available for any user – No declaration needed – Syntax: – SELECT @a – SET @a = 1 User Variables

MySQL Variables SP Variables: – Strongly typed – Only inside SP – Arbitrary names – Must be declared – Syntax: – DECLARE x INT – SELECT x – SET x = 1 Stored Program Variables

MySQL Variables System Variables: – Configuration options – Fixed names – Fixed types – GLOBAL requires SUPER – Syntax: – SET / SELECT @@v1 – SET / SELECT @@session.v1 – SET / SELECT @@global.v2 – SHOW VARIABLES LIKE … System Variables

MySQL Variables Status Variables: – Read-only – Provide stat / information – Not PERFOMANCE_SCHEMA – SHOW GLOBAL STATUS … – SHOW SESSION STATUS … System Variables

MySQL Configuration Highly configurable Command line options Configuration files (plain-text, INI-like files with groups) Several configuration files (/etc, $HOME, …) The last value takes precedence <exe> --help – order of loading files SQL interface to get or change configuration parameters Overview

MySQL Configuration Very important variable Affects data consistency! It might be remembered … … or it might be not Thus: set it once for all • Recommended value: STRICT_ALL_TABLES | NO_ZERO_DATE | NO_ZERO_IN_DATE | NO_ENGINE_SUBSTITUTION | NO_AUTO_CREATE_USER | IGNORE_SPACE | ERROR_FOR_DIVISION_BY_ZE RO SQL MODE

MySQL Configuration • SET AUTOCOMMIT = ON | OFF (default: ON) • Auto-commit means COMMIT after each statement • Use START TRANSACTION if you plan to use ROLLBACK • START TRANSACTION == BEGIN [WORK] AUTOCOMMIT

MySQL Configuration Database name – directory name Table name – file name(s) Case-sensitive or case-insensitive ? lower_case_table_names = 0, 1, 2 Defines how to name files on disk Defines how to compare So: use lowercase everywhere Identifier Case Sensitivity

Connecting to The Server Named Pipe and Shared Memory are disabled by default IPv4 and IPv6 connectivity supported OverviewProtocol Connection Type Operating Systems TCP/IP Local and Remote Any UNIX Socket File Local only UNIX Named Pipe Local only Windows Shared Memory Local only Windows Overview

Connecting to The Server --bind-address can be of use (all: 0.0.0.0) Beware: ‘mysql -pfoo’ != ‘mysql -p foo’ -pfoo means password is ‘foo’ -p foo means “ask password”, and the default database is ‘foo’ mysql does SHOW DATABASES and SHOW TABLES on connect Hints

Understanding Privileges User or User Account Privilege – what is allowed for the user SHOW GRANTS – get privileges for the current user SHOW GRANTS FOR … SHOW PRIVILEGES – list of all available privileges Basics

Understanding Privileges Global: GRANT SELECT ON *.* TO … Database: GRANT SELECT ON db1.* TO … Table: GRANT SELECT ON db1.t1 TO … Column: GRANT SELECT(a) ON db1.t1 TO … Server administration privileges – global only SUPER USAGE Other privileges Hierarchy

Understanding Privileges SUPER A “different” privilege Ignores init_connect Bypasses read_only … GRANT ALL ON *.* … grants SUPER! USAGE Allows the user to just connect to the server Mind the default database in client connections Beware of …

Understanding Privileges WITH GRANT OPTION enables the user to give to / to remove from other users those privileges that the user has GRANT SELECT ON db1.* TO foo@bar WITH GRANT OPTION GRANT DELETE ON db1.* TO foo@bar SHOW GRANTS FOR foo@bar -> GRANT SELECT, DELETE ON db1.* TO foo@bar WITH GRANT OPTION Beware of …

Understanding Privileges User: username@hostname Precisely: ‘user-name-mask’@’host-name-mask’ Host name – client host name (“from” host name) User name mask: can be empty (anonymous user) – all users Host name mask: can be empty – all host names can have ‘%’ (e.g.: %.foo.com) Identifying the User

Understanding Privileges Connecting as foo from localhost… Should be foo@%, right ? The most specific values are used BUT: host name matching is done before user name host user localhost bar localhost % foo ‘’@localhost will be chosen ! Anonymous users…

Understanding Privileges SELECT CURRENT_USER() The authenticated user name and host name SELECT USER() The user name and host name provided by the client For our example: CURRENT_USER(): ‘’@locahost USER(): foo@localhost Useful Functions

Working With Tables Regular tables Temporary tables (per session) Views INFORMATION_SCHEMA tables PERFORMANCE_SCHEMA tables Overview

Working With Tables Mind the storage engine! Temporary tables are in the same namespace ! Temporary tables shadow regular ones ! 5.5: CREATE TEMPORARY TABLES is not enough 5.6: CREATE TEMPORARY TABLES is enough Hints

The First Steps Server – Community or Enterprise (for 30 days) Cluster –real-time open source transactional database designed for fast, always-on access to data under high throughput conditions. Workbench – visual database design application that can be used to efficiently design, manage and document database schemata Proxy – a simple program that sits between your client and MySQL server(s) that can monitor, analyze or transform their communication. Connectors – ODBC, Java, .Net, MXJ, C/C++, DBI, Ruby, Python, etc. http://dev.mysql.com/downloads/ Installation Options

How to get MySQL Community: Freely downloadable version of the world's most popular open source database. It is available under the GPL license and is supported by a huge and active community of open source developers. Enterprise: Paid subscription includes support and the following • MySQL Enterprise Backup • MySQL Enterprise Security – External Authentication • MySQL Enterprise Scalability – Thread Pool • MySQL Enterprise High Availability – Oracle VM Template – Windows Clustering • MySQL Enterprise Monitor Free for 30 day evaluation

The First Steps Choosing the version 5.1 – previous GA version 5.5 – the latest GA version 5.6 – development release Choosing the edition Community Edition (Community Server) Enterprise Editions (even MySQL Classic and MySQL Standard) Source or Binary Download

The First Steps • Where are the configuration files Default configuration might be not so good Performance, … SQL_MODE AUTOCOMMIT … Post-installation steps: Configuration

The First Steps Secure the installation Don’t run under ‘root’ Have separate directories (configuration, data, binary logs, …) Change ‘root’ password Remove default accounts Post-installation steps: Security

• Reach out to the community – Irc on freenode – Forums.mysql.com • Oracle Support • Certifications MySQL Support How can I get help ?

• 24 X 7 Problem Resolution Services • Unlimited Support Incidents • Knowledge Base • Maintenance Releases, Bug fixes, Patches, Updates • MySQL Consultative Support • Staffed by experienced, seasoned MySQL Engineers Oracle Premier Support for MySQL

• MySQL Database, Visual Development/Admin, Monitoring, Backup tools, and Oracle Lifetime Support services MySQL Enterprise Monitor Performance Monitoring/ Alerts Hot fixes Service packs MySQL Workbench Oracle Lifetime Support MySQL Enterprise Backup MySQL Enterprise Edition Query Analyzer Dev/Admin, Monitoring/ Backup Tools Oracle Lifetime Support Services

• Oracle High Availability • Oracle Linux • Oracle VM + MySQL Template • Oracle Fusion MiddleWare • WebLogic Server 10.3.1 + • Database Adapter for Oracle SOA Suite 11.1.1.1 + • Oracle BPM 11.1.1.3 + • Oracle Virtual Directory 11.1.1.1+ • Oracle Data Integrator 11.1.1.3 + • Oracle Enterprise Performance Management 11.1.2 • Oracle Identity Analytics 11.1.1.3 • Open SSO STS, Open SSO Fedlet 11.1.1.3 MySQL Enterprise Certifications Oracle Products

• Oracle Golden Gate • Bi-directional replication between MySQL and Oracle • Exadata Data Stores – Enterprise DW, legacy apps, etc. • Hybrid Applications (MySQL frontend + Oracle data store) • Oracle Secure Backup • MySQL Enterprise Backup 3.6 - supports backup streaming to OSB via SBT API MySQL Enterprise Certifications Oracle Products Enables you to manage your Oracle and MySQL databases with Oracle tools/solutions you are already using.

Additional Resources mysql.com • TCO calculator • White Papers • Customer use cases and success stories dev.mysql.com • Downloads • Documentation • Forums • PlanetMySQL eDelivery.com • Download and evaluate all MySQL products

Additional Resources mysql.com • Download MySQL 5.5, MySQL Cluster 7.1 GA, GPL Products • MySQL Products, Editions, Licensing Options • TCO calculator • Upcoming Events • Customer use cases and success stories dev.mysql.com • Download MySQL 5.6 DMR and Labs “early access” features • Developer Zone Articles, How to’s eDelivery.com • Download and evaluate all MySQL products

Additional Resources Planet.mysql.com • Blog feeds from the experts and the community Books: • MySQL by Paul DuBois • MySQL Administrator's Bible • High Performance MySQL: Optimization, Backups, Replication, and More forums.mysql.com • Community interaction

Xaprb.com www.xaprb.com/blog/2009/03/13/50-things-to-know-before-migrating-oracle-to-mysql/ 50 things to know before migrating Oracle to MySQL It is a little old but worth the read Additional Resources

<Insert Picture Here> Thanks for attending! keith.larson@oracle.com

My sql crashcourse_intro_kdl

More Related Content

What's hot

Viewers also liked

Similar to My sql crashcourse_intro_kdl

Recently uploaded

My sql crashcourse_intro_kdl