• MySQL Overview
• Use Cases
• Comparing Feature Sets
• Tools Overview
• Next Steps
About MySQL
14 Years of Development 750+ Partners
70K+ Downloads Per Day
Customers across every major operating system, hardware vendor, geography, industry, and application type
High Performance. Reliable. Easy to Use.
• 15 years of development
• 12 million active installations • 70,000+ downloads each day • #3 most deployed database • Up to 90% lower TCO than
proprietary database offerings
Customers across every major operating system, hardware vendor, geography, industry, and application type
Enterprise 2.0 Telecommunications
SaaS, Hosting, Cloud Computing
Web / Web 2.0 OEM / ISV's
Why MySQL?
• Lower TCO
• Performance
– “Up to 90% Faster Query Response Times”
• Scalability
– “Up to 16-way x86 Servers” – “Up to 64-way CMT Servers”
• Reliability
– Up to five 9s with MySQL Cluster
• Ease of Use
– Up and running in 15 minutes – MySQL Enterprise Monitor – MySQL Workbench
Popular on Windows
• 45,000 downloads per day for Windows packages
• Server • Tools
• Connectors
• MySQL Survey 2009
• 66% percent used Windows for development • 48% deployed on Windows for production
Lower Total Cost of Ownership
• Compared to SQL Server Enterprise Edition • Per Server vs Per CPU
• No Upfront Licensing
• Example:
– 6 Servers
– 2 CPUs per Server – 88% Savings! • MySQL Enterprise – 24x7 Production Support – Enterprise Monitor – Enterprise Binaries •
1.
2.
3.
Yahoo
4.
YouTube
5.
WIN Live
6.
Wikipedia
7.
Blogger
8.
MSN
9.
Baidu.com
11.
MySpace
12.
Google.co.in
13.
14.
Google.de
15.
QQ.com
16.
Microsoft
17.
Rapidshare
18.
Wordpress
19.
Google.fr
Application
Facebook is a social networking site that connects people with friends and others who work, study and live around them.
Key Business Benefit
MySQL has enabled Facebook to grow to over 200 million users.
Why MySQL? Scalability & TCO
“We are one of the largest MySQL web sites in production. MySQL has been a revolution for young entrepreneurs.”
Owen Van Natta Chief Operating Officer
Application
Wikipedia is one of the top 10 most trafficked web sites in the world, hosting over 7 million articles in 200 languages.
Key Business Benefit
MySQL has enabled Wikipedia‟s annual visitors to grow 50,000 to over 154 million.
Why MySQL? Scalability & TCO
“Wikipedia relies upon MySQL replication to scale-out their database infrastructure and accommodate more visitors, more articles and more contributors.”
Application
Craigslist is one of the top 10 sites serving 3 billion page views / month. MySQL is used for all
dynamic content generation and data
management including classified ads, search, forums.
Key Business Benefit
In less than 5 years, MySQL enabled craigslist to grow to 10M unique visitors/month.
Why MySQL? Scalability & TCO
“Craigslist infrastructure could not have handled the exponential growth in traffic without MySQL.”
Craig Newmark Founder
Application
Real-time personalization platform to display advertising to more relevant buyers and conduct more effective merchandizing.
Key Business Benefits
Highly scalable and cost-effective system that handles all of eBay‟s personalization and session data needs. Manages 4 billion requests per day of 50/50 read/write operations.
Why MySQL Enterprise?
Performance: 13,000 TPS on Sun Fire x4100
Scalability: Designed for 10x future growth
Ease of Use: MySQL Enterprise Monitor
Chris Kasten, Kernel Framework Group, eBay
Application
$800 Million Online Retailer of shoes. Zappos stocks over 3 million items.
Key Business Benefit
Zappos selected MySQL because it was the most robust, affordable database software available at the time.
Why MySQL? Reliability & TCO
"MySQL provides the perfect blend of an
enterprise-level database and a cost-effective technology solution. In my opinion, MySQL is the only database we would ever trust to power the Zappos.com website.“
Kris Ongbongan, IT Manager
Why MySQL? - eCommerce
MySQL Support for Windows
• All current Windows versions…
– Windows XP – Windows Vista
– Windows Server 2003 – Windows Server 2008
• Minimal additional requirements
Getting Started - Installation
• Download: http://dev.mysql.com/downloads/mysql/5.1.html
• Enterprise, Community or Source • 32 or 64 bit version
• MSI Installer, Zip file or “Essentials”
A Visual Guide to Installing MySQL on Windows:
http://www.mysql.com/why-mysql/white-papers/
Installing MySQL on Windows:
Storage Engine Characteristics
Feature InnoDB MyISAM NDB Archive Memory
Storage limits 64TB No Yes No Yes
Transactions Yes No Yes No No
Locking granularity Row Table Row Row Table
MVCC snapshot read Yes No No No No
Geospatial support Yes Yes No Yes No
Data caches Yes No Yes No NA
Index caches Yes Yes Yes No NA
Compressed data Yes Yes No Yes No
Storage cost Med Small Med Smallest NA
Memory cost High Low High Low High
Bulk insert speed Med High High Highest High Replication support Yes Yes Yes Yes Yes
Storage Engine Characteristics
Feature InnoDB MyISAM NDB Archive Memory
Storage limits 64TB No Yes No Yes
Transactions Yes No Yes No No
Locking granularity Row Table Row Row Table
MVCC snapshot read Yes No No No No
Geospatial support Yes Yes No Yes No
Data caches Yes No Yes No NA
Index caches Yes Yes Yes No NA
Compressed data Yes* Yes No Yes No
Storage cost Med Small Med Smallest NA
Memory cost High Low High Low High
Bulk insert speed Med High High Highest High Replication support Yes Yes Yes Yes Yes
What is Replication?
The duplication of data changes to more then one location
update customer
update
Asynchronous Replication
MySQL Master relay binlog MySQL Slave mysqld data index & binlogs updates selects updates mysqld I/O Thread SQL Thread Replication
Scale-Out Overview
Improve application performance and scalability on an
incremental, as-needed basis by adding multiple replicated database servers on low-cost commodity hardware.
reads updates
Read Scale Out
Replication Use Cases
Backup Business Intelligence
Scale Out Architectures HA Implementations
X
X
OLTP Data Mart/Warehouse
Search Core Data
X
Partitioning
• Increase Performance
• Simplify Data Management • MySQL Partitioning Schemes
– Range – Hash – Key – List
– Composite (sub-partitions)
• SQL Server (Enterprise Edition only)
Backup Types
• Hot (Online non-blocking)
– Innodb Hot Backup (MySQL Enterprise Backup)
– Backs up without locking InnoDB data, supports MyISAM
– High Performance, Compressed, and Partial backup – Point-in-time recovery
• Export/Import
– Mysqldump tool
– SELECT…INTO FILE, LOAD DATA INFILE
• Synchronize Near time Copy (Hot swap)
• MySQL Replication
• Warm File Copy
– mysqlhotcopy - only for MyISAM and ARCHIVE tables
• Cold Backup
MySQL Security Model
• MySQL security is based on Access Control Lists (ACLs)
– Connections – Queries
– Operations users attempt to perform
• SSL-encrypted connections supported between MySQL clients and servers
MySQL Privilege System
• Authenticates a user connecting from a given host and associates the user with privileges such as:
– SELECT – INSERT – UPDATE – DELETE
• Ability to have anonymous users
• Privileges for MySQL-specific functions such as:
– LOAD DATA INFILE
– Administrative operations – Replication
Connecting to MySQL
• MySQL client expects connection parameters when accessing a MySQL server:
– Host where the MySQL server is running – Username
– Password
Connecting to MySQL
MySQL considers both your host name and user name Don‟t assume a user name belongs to the same person
everywhere
For example: bill who connects from 192.168.0.10 is not necessarily the same user as bill who connects from
192.168.0.11
Note: Wild cards - supports „User1‟@‟%‟ or „User1‟@'%.xyz.com'
MSQL sysadmin (sa) = MySQL root
Accounts
• Two accounts are created with a user name of root • These are super user accounts that can do anything • The initial root account passwords are blank
• One root account for connecting from the local host • The other allows connections from any host
Job/Task Scheduler
• MySQL Event Scheduler
– Built-in scheduler for MySQL jobs – Ease to use, no need for OS scripts – One off or recurring/scheduled jobs
• Example: “Reorg tables on Sunday at 1 AM”
– DELIMITER //
– CREATE EVENT OPTIMIZE_TABLES – ON SCHEDULE EVERY 1 WEEK
– STARTS '2009-03-05 1:00:00' – ON COMPLETION PRESERVE
– DO
– BEGIN
– OPTIMIZE TABLE test.table1; – OPTIMIZE TABLE test.table2;
Clustering/High Availability
• MySQL Replication
One to One Datatype Mapping
BIGINT BINARY BIT CHAR CHARACTER DATETIME DECIMAL FLOAT DOUBLE PRECESION INTEGER NCHAR NVARCHAR NATIONAL CHAR NUMERIC REAL SMALLINT TEXT TIMESTAMP TINYINT VARBINARY VARCHAREquivalent Datatypes
SQL Server MySQL
IDENTITY AUTO_INCREMENT
NTEXT TEXT CHARACTER SET UTF8
SMALLDATETIME DATETIME
MONEY DECIMAL(19,4)
SMALL MONEY DECIMAL (10,4) UNIQUEIDENTIFIER BINARY(16)
MySQL Stored Procedures and T-SQL
• Requires the parameter list be within ( ) • Cannot take AS after parameter list
• BEGIN and END pair required if more than one statement • Every statement terminated by ;
• Variable declarations must come before first non-declare
CREATE PROCEDURE name Param1 type,
Param2 type AS
Statement1 Statement2
CREATE PROCEDURE name ( Param1 type, Param2 type ) BEGIN Statement1 ; Statement2 ; SQL Server MySQL
SQL Tuning
• SHOW GLOBAL STATUS
• SHOW INNODB STATUS
• Slow Log
• EXPLAIN
SQL Server Express - Restrictions
• Restricted to 1 CPU
• Restricted to 1 GB RAM
• Restricted to 4 GB of User Data
• Restricted Log Shipping (Subscriber Only) • No Partitioning
• No SQL Server Agent • No SQL Profiler
MySQL on Windows FYIs
• DTS packages
• OLAP services objects • Synonyms (SQL 2005)
• DDL and Statement-Based Triggers (MySQL has Row-Based) • Limited Number of Ports
• Concurrent reads • Blocking read
• ALTER & DROP TABLE
• DATA and INDEX DIRECTORY • DROP DATABASE
• Case-insensitive names • Directory and file names • “Access denied for user”
• Subscription: • MySQL Enterprise • License (OEM): • Embedded Server • Support • MySQL Cluster Carrier-Grade • Training • Consulting NRE
Server
Monitor
Support
• MySQL Enterprise Server • Monthly Rapid Updates • Quarterly Service Packs • Hot Fix Program
• Extended End-of-Life
• Global Monitoring of All Servers • Web-Based Central Console • Built-in Advisors
• Expert Advice
• Specialized Scale-Out Help
• 24 x 7 x 365 Production Support • Web-Based Knowledge Base • Consultative Help
• Single, consolidated view into entire MySQL environment • Auto discovery of MySQL Servers, Replication Topologies • MySQL Query Analyzer with Visual Correlation Graphs • Customizable rules-based monitoring and alerts
• Identifies problems before they occur • Reduces risk of downtime
• Makes it easier
to scale-out without requiring more DBAs
Connectors & Visual Studio
•
ODBC, JDBC, C++, C….
•
Connector/NET
• C# • ASP.NET • VB.net•
Connector/Net
• Implements the ADO.NET interfaces
• Integrates into ADO.NET aware tools
• Fully managed ADO.NET driver
• 100% pure C#
MySQL Workbench
• ER Modeling – Design – Forward/Reverse Engineering – Schema Synchronization – Documentation• New! Administrator Plugin
– Start/Stop Server – Account/User Management – Server Variables – Server Logs – Database Objects http://wb.mysql.com/
MySQL Migration Toolkit
Migration Toolkit Schema Database Migration Toolkit Table Table Migration Toolkit Index Index Migration Toolkit View View manual Stored Procedure Stored Procedure manual Function Function manualMySQL on Windows Resources
• Product Downloads • Whitepapers • Visual Guides • How-Tos • Upcoming Webinars • Developer Articles • Visual Studio Articles• Archived Webinar Playbacks • Archived Webinar Slides
MySQL Enterprise
• Subscription: • MySQL Enterprise • License (OEM): • Embedded Server • Support • MySQL Cluster Carrier-Grade • Training • Consulting • NREDatabase
Monitoring
Support
• MySQL Enterprise Server • Monthly Rapid Updates • Quarterly Service Packs • Hot Fix Program
• Indemnification
• Global Monitoring of All Servers • Web-Based Central Console • Built-in Advisors, Expert Advice • Problem Query Detection/Analysis • Specialized Scale-Out Help
• Online Self help Knowledge Base • 24 x 7 x 365 Problem Resolution • Consultative Help
• High Availability and Scale Out
• Single, consolidated view
into entire MySQL environment
• Auto-discovery of MySQL
servers, replication topologies
• Customizable rules-based
monitoring and alerts
• Identifies problems before
they occur
• Reduces risk of downtime
MySQL Enterprise Monitor
MySQL Query Analyzer
•
Improve performance by
finding and fixing problem
queries
•
Centralized monitoring of
queries across all servers
•Aggregated view of query
execution counts, time, and
rows
MySQL on Windows Resources
Product Downloads Whitepapers Visual Guides How-Tos Upcoming Webinars Developer Articles Visual Studio ArticlesArchived Webinar Playbacks Archived Webinar Slides