Newsletters




What is Oracle MySQL Database?


Bookmark and Share

With its January 2010 acquisition of Sun Microsystems, Oracle gained the MySQL open source database management software (DBMS) platform for enterprise IT environments. MySQL, which uses SQL (Structured Query Language),  is designed to let users design and manage complex applications and data sets, and has gained a substantial share of the overall DBMS market.

While it is open source, MySQL is considered a viable option for even the most complex and mission-critical enterprise databases. Although it directly competes with Oracle's own 11g Database, Oracle has declared its commitment to MySQL. At the April 2010 O'Reilly MySQL Conference, Oracle's chief corporate architect, Edward Screven, conveyed the company's belief that MySQL possesses certain valuable attributes that the 11g Database does not. MySQL is able to reach the open source market, which is something the Oracle Database has not been able to do. Additionally, Screven reported that Oracle is integrating the MySQL and InnoDB teams, which will result in performance improvements for the product.1

Widely Adopted Database. In use for more than 10 years, MySQL was bought by Sun Microsystems in 2008 (and by Oracle in 2010) to capitalize on its growing presence in the enterprise. MySQL has a large share of the database management software (DBMS) market and holds its own with both commercial and open source rivals like Oracle, IBM, and Microsoft.

Handles Demanding Processes.  MySQL can handle a flood of data and process tens of thousands of operations simultaneously. Uses range from storing and analyzing data to powering complex Web 2.0 or cloud computing environments. As a result, many industry clients use MySQL  - from Adobe Systems, Alcatel-Lucent, and the Associated Press to Twitter, Wikipedia, Yahoo, and YouTube. 

Build for Large-Scale Computing. MySQL lets users create and control server and client programs and events  - and search for data  - from a common DBMS. Developers in MySQL use SQL, a commonly-used database language, to structure databases that can connect and query massive data sets and programs. MySQL-based databases can house or connect virtually any application.

Query and Analysis Functions. Query and analysis features allow users to conduct deep database searches, compare data, and analyze performance.

Code Developed by Open Source Community. Despite being owned by a corporation, MySQL's development and code continues to be in the hands of the open source community. Employees also have their hands in building new versions of MySQL. 

Free Software, Supplemented by Service Subscriptions. While downloads of MySQL are free, Oracle makes money from purchases of support, training, and compatible software and hardware purchases.

MySQL Architecture

MySQL is a client/server system that consists of a multi-threaded SQL server that supports different backends, client programs and libraries, administrative tools, and application programming interfaces (APIs). MySQL Server is also available as an embedded multi-threaded library that allows users to link it to applications. 

  • Languages and APIs. The database is written in C and C++, and works on a variety of platforms. It optimizes GNU Automake, Autoconf, and Libtool for portability, and provides both transactional and non-transactional storage engines. There are also a number of APIs available for MySQL, including APIs for C, C++, Eiffel, Java, Perl, PHP, Python, Ruby, and Tcl. 
  • Security. MySQL offers a privilege and password system that is flexible and secure, and also allows host-based verification. All password traffic is encrypted when users connect to a server.
  • Pluggable Storage Engine. MySQL features a Pluggable Storage Engine Architecture designed to enable users to optimize the database for their specific needs. The company delivers storage engines, such as the InnoDB engine, that are designed for heavy-duty transaction processing, high-traffic Web sites, terabyte-sized data warehouses, and high availability applications. Due to its open source model, users can also create custom storage engines for MySQL to meet their unique requirements. 

MySQL Versions

MySQL's core database is MySQL Enterprise. Table 1 lists the various versions of MySQL database.

Table 1. MySQL Versions

Database Description Version
MySQL Cluster.MySQL Cluster offers a fault tolerant database clustering architecture. The platform boasts 99.999 percent availability, implementing automatic node recoverability to ensure an application automatically fails over to another database node that contains a consistent data set, if one or more database nodes fail. Cluster Carrier Grade edition is focused on needs of the telecommunications market. 
  • Cluster Edition.
  • Carrier Grade.
MySQL EmbeddedMySQL Embedded Database, offered to original equipment manufacturers (OEMs) and independent software vendors ( ISVs), is a database that can be integrated with software applications or hardware appliances.OEM/ISV versions include MySQL Enterprise Server-Classic and MySQL Enterprise Server-Pro editions.
MySQL Data Warehouse.MySQL offers several server platforms for open source data warehousing:

Native MySQL Storage Engines. MySQL offers internally developed storage engines that are suited for data warehouses, with the most popular being the default MyISAM storage engine. The MyISAM engine delivers rapid data loading, fast read times, and more for data warehousing users. Typical MyISAM support for data warehouse volumes range up to 1TB. MySQL offers other storage engines that can also be used for data warehousing as well.

Infobright. The Infobright storage engine for MySQL Server is tailor-made for large scale, analytic-styled data warehousing. Infobright enables MySQL users to move up to data warehouses that support data volumes of 1-10TB or more.
Native MySQL Storage Engine features:
  • Data/Index partitioning.
  • No practical storage limits.
  • Automatic storage management.
  • Built-in Replication.
  • Strong indexing support. 
  • Multiple data/index caches. 
  • Pre-loading of data into caches.
  • Unique query cache.
  • Parallel data load.
  • Multi-insert DML.
  • Read-only tables.
  • Cost-based optimizer.
  • Wide platform support.
MySQL Backup and Recovery.MySQL provides a variety of ways to backup and recover MySQL database servers.
  • mysqldump. The most popular method used to backup a MySQL database is the mysqldump utility, which ships with every version of MySQL. The utility creates a backup file for one or more databases that consists of DDL/DML statements needed to recreate the specified databases along with their data.
  • mysqlhotcopy / OS Backup. Another method of backing up MySQL is to use OS utilities and the mysqlhotcopy program. With mysqlhotcopy, the tables of the database being backed up are locked although data can still be read. The underlying files are copied to another physical location, and once the file copy is completed, the tables are unlocked. 
  • MySQL Replication. Replication is used to create a warm standby server that can be failed over should the primary database server become unavailable.
  • ZRM for MySQL. MySQL provides global backup and recovery management through ZRM. A Web-based console handles global management of backup and recovery of an enterprises' MySQL servers.
  • mysqldump allows for different types of backups such as online backups for InnoDB and other MVCC-based storage engines, backups of just database structures, and more.
  • Using mysqlhotcopy can result in faster backupsn depending on the copy speed of the OS and the size of the database being backed up.
  • MySQL replication is designed to protect databases that are used for critical systems and does not impact the operation of data operations on the server being replicated.
MySQL Workbench.The MySQL Workbench database modeling tool can be used to design and create new database schemas, document existing databases, and assist with migrations to MySQL. Available in community and standard editions.
  • Visual Database Design.
  • Forward and Reverse Engineering.
  • Database Change Management.
  • Database Documentation.
MySQL Connectors.MySQL provides standards-based drivers for JDBC, ODBC, and .Net enabling developers to build database applications in their language of choice. In addition, a native C library allows developers to embed MySQL directly into their applications. MySQL offers Connectors that were created internally, as well as community-developed Connectors.MySQL Developed and Maintained Drivers:
  • ADO.NET Driver for MySQL (Connector/NET).
  • ODBC Driver for MySQL (Connector/ODBC).
  • JDBC Driver for MySQL (Connector/J).
  • MXJ Driver for MySQL (Connector/MXJ).
  • PHP Driver for MySQL (Connector/PHP - Windows Only).
  • C API for MySQL (mysqlclient).
MySQL Community Drivers:
  • PHP Driver for MySQL (mysqli).
  • Perl Driver for MySQL (DBD::mysql).
  • Python Driver for MySQL (MySQLdb).
  • Ruby Driver for MySQL (DBD::MySQL).
  • Ruby Driver for MySQL (ruby-mysql).
  • C++ Wrapper for MySQL C API (MySQL++).

MySQL Enterprise

MySQL Enterprise's major components include Enterprise Server, Enterprise Monitor, and Query Analyzer. Table 2 details these components.

Table 2. MySQL Enterprise Server

Component

Description

MySQL Enterprise Server.MySQL Enterprise Server is the company's transaction-safe, ACID compliant database. The server software delivers full commit, rollback, crash recovery, and row level locking capabilities.
Enterprise Monitor.Enterprise Monitor continuously monitors a client's MySQL servers and alerts users to potential problems before they impact the system. The Enterprise Monitor can recommend best practices, identify security vulnerabilities, auto-detect groups and monitor replication topologies.  
Query Analyzer.MySQL Query Analyzer monitors query performance to pinpoint SQL code that is causing a slow down. Queries are presented in an aggregated view across all MySQL servers so users can analyze code by total executions, total run time, total data size, and by using drill downs into sampled executions and EXPLAIN results. With the MySQL Query Analyzer, users can improve the SQL code during active development, and continuously monitor and tune the queries in production. MySQL Query Analyzer is installed with the Service Agent.

MySQL Migration Toolkit. MySQL Migration Toolkit is designed to help database administrators (DBAs) and developers migrate their existing database deployments to MySQL. The toolkit supports migration from Oracle, SQL Server, and Access databases, while any database engine that supports JDBC can also be migrated. The MySQL Migration Toolkit features an GUI that guides a DBA or developer in selecting what databases, schemas, and objects to migrate, converting DDL syntax from any source database to MySQL. It also offers command line scripting. 

MySQL Cluster Carrier Grade. MySQL Cluster Carrier Grade provides telecom equipment manufacturers and network service providers with database services and applications for converged networks. Cluster Carrier Grade offers a distributed architecture, enables servers to share processing within a cluster, supports up to 255 nodes, and supports asynchronous replication between clusters within the data center or across geographies. Figure 6 illustrates the architecture of the Cluster Carrier Grade platform.

Strengths

Usefulness in IT Departments. MySQL is designed for ease of use. MySQL employs languages and interfaces that are familiar to DBAs and programmers. It can be modified to meet a variety of needs, and it can handle very large workloads. It can also be deployed in parts or as a total implementation. MySQL has no fee for software licenses, arguably reducing total cost of ownership (TCO). While it is open source, MySQL's maturity is shown by the number of major enterprise and public organizations that use and trust it.

Compatibility with Oracle and Third-Party Platforms.  As open source software, MySQL is compatible with a number of third-party applications. Developers continue to update open source software to make it more stable, diverse, and mature, meaning that MySQL's compatibility and usability features will only increase.3 MySQL is also increasing its compatibility with servers and software from Sun Microsystems (now owned by Oracle). The company now offers combined product purchases and support agreements.

Respected Vendor. End users and rivals such as IBM are seeing the value of open source DBMS. MySQL arguably has a large share of the DBMS market. MySQL's market share is hard to quantify  - as no one knows how many times it has been downloaded and implemented  - but some figures have named MySQL as having a 49 percent share of the open source database market4, a market area that was projected to pass $1 billion USD by 2012. IBM's interest in buying Sun Microsystems in early 2009 is thought to have been partly due to its interest in MySQL, which would give IBM an even larger share of the DBMS market.Now that it falls under the Oracle umbrella, the product is poised to gain an even greater share of the market.

Limitations

As it is open source, MySQL makes no promises for service levels, although it offers extensive support. Some argue that using MySQL will increase TCO, rather than lowering it, because users may need to purchase expert training, support, IT resources to optimize their MySQL installation. 

Applications

MySQL powers applications large and small, ranging from transaction processing to health records cataloging, data network monitoring, and more. Notable users include eBay, Google, Nokia, and many others. Government users include the US Army, Navy, Department of Defense, and agencies and organizations worldwide. 

Table 3 lists some of MySQL's specific applications.

Table 3. MySQL Applications

Application

Examples

Content Hosting and Delivery.Web 2.0 and cloud computing companies such as Facebook, LinkedIn, Twitter, and Yahoo Zimbra use MySQL to power databases for social networking and software-as-a-service offers. Others, like the Associated Press, use MySQL to host content that is aggregated across the Web to thousands of news providers. 
Performance Analysis.The Query Analyzer feature is used by e-Commerce companies like Clickability to improve performance and scalability when processing a host of operations simultaneously.
OEM/ISV Products.MySQL can be embedded into a company's proprietary product, as is the case in several Adobe Creative Suite software products and in hardware from Cisco, Dell, and HP


Environment

MySQL software operates in a number of IT environments. It can run on more than 20 OS platforms including Microsoft Windows; Apple Mac OS; Linux/Unix; and Solaris. Users should check to see if MySQL is available for their specific environment and which distribution of MySQL server would be best, checking the reference manuals and documentation on MySQL's Web site.

Support

Oracle offers its MySQL Enterprise customers a number of support options, including technical support, training courses, certification, and both short- and long-term consulting. Free resources include support boards, community groups, and documentation. Users may select Basic, Silver, Gold, and Premium. Table 4 outlines each support option.

Table 4. MySQL Network Support Options

Feature

Basic Silver Gold

Platinum

MySQL ServerProProProPro
Certified, Optimized SoftwareYesYesYesYes
Maintenance, Updates, and UpgradesYesYesYesYes
Custom MySQL BuildsNoNoNoOption
Technical Advisor AlertYesYesYesYes
Update AdvisorYesYesYesYes
Knowledge BaseYesYesYesYes
Number of Incidents2UnlimitedUnlimitedUnlimited
Web AccessYesYesYesYes
Phone AccessNo8 to 5 (M to F)24x724x7
Max Initial Response Time2 business days.4 hours2 hours30 minutes
Emergency Response TimeNoNo30 minutes30 minutes
Remote TroubleshootingNoNoYesYes
Schema ReviewNoNoNoYes
Query ReviewNoNoNoYes
Performance TuningNoNoNoYes
Technical Account ManagerNoNoNoOption
Proactive Case ManagementNoNoNoOption
Scheduled Onsite VisitsNoNoNoOption
Comprehensive IndemnificationNoNoOptionOptio

Pricing

MySQL offers a free Community edition, which can be downloaded under the GPL license from MySQL's Web site. Users also have the option to purchase support or contact the company's sales team for further purchasing options. Sales teams are available for enterprises and OEMs, and MySQL Enterprise can be bought with products from technology vendors. Products and services for US government entities can be bought through the GSA schedule.

Competitors

Oracle's MySQL Enterprise faces a number of competitors in the database market. Among the leading rivals are Oracle's own 11g database, IBM's DB2, Microsoft's SQL Server, Sybase's Adaptive Server Enterprise, and the Teradata Warehouse. In terms of open source databases, the leading competitors for MySQL are PostgreSQL and Ingres.

References

1Peter Wayner. "Oracle Chief Architect Unveils MySQL Roadmap." IT World Canada. April 13, 2010.

2Eric Lai. "Experts: MySQL Could Enable IBM to Take Over the Database Market." Computerworld. April 7, 2009.

3Jeffrey Schwartz. "Database Growth Points To Open Source." Redmond Developer News. July 24, 2008.

4Donald Feinberg. "The Growing Maturity of Open-Source Database Management Systems." Gartner. November 28, 2008.

5Jessica Hodgson. "Open Source Comes Under Spotlight as IBM Eyes Sun." The Wall Street Journal. March 24, 2009.

6Colleen Graham, Dan Sommer, and Bhavish Sood. "Market Share: Relational Database Management Systems by Operating System, Worldwide, 2006." Gartner. June 13, 2007.

Web Links

IBM: http://www.ibm.com/
Ingres: http://www.ingres.com/
Microsoft: http://www.microsoft.com/
MySQL: http://www.mysql.com/
Oracle: http://www.oracle.com/
PostgreSQL: http://www.postgresql.org/
Sybase: http://www.sybase.com/
Teradata: http://www.teradata.com/

This article was adapted from the Faulkner Information Services library of reports covering computing and telecommunications. For more information contact www.faulkner.com. To subscribe to the Faulkner Information Services visit http://www.faulkner.com/showcase/subscription.asp.


Sponsors