CHAPTER 25

Introducing MySQL

Believe it or not, the MySQL relational database server was born out of an internal company project spearheaded by employees of the Sweden-based TcX DataKonsult AB (AB is an abbreviation for Aktiebolag, which is the Swedish term for corporation). Their project, dubbed MySQL, was first released to the general public at the end of 1996. The software proved so popular that in 2001 they founded a company based entirely around MySQL-specific service and product offerings, calling it MySQL AB. Profitable since its inception, MySQL AB has since grown by leaps and bounds, establishing offices in several countries, attracting substantial venture capital funding, and announcing numerous high-profile partnerships with an array of corporate heavyweights, including Red Hat, Veritas, Novell, and Rackspace.

From its first public release in 1996, MySQL's developers placed particular emphasis on software performance and scalability. The result was a highly optimized product that was lacking in many features considered standard for enterprise database products: stored procedures, triggers, and transactions, for example. Yet the product caught the attention of a vast number of users who were more interested in speed and scalability than in capabilities that would, in many cases, often go unused anyway. Subsequent versions included additional features, which attracted even more users. MySQL has been downloaded more than 100 million times in its lifetime, with more than 50,000 daily downloads (http://www.mysql.com/company/factsheet.html). These users include some of the most widely known companies and organizations in the world, such as Yahoo!, CNET Networks, NASA, The Weather Channel, Google, the Chicago Mercantile Exchange, and Cisco Systems (http://www.my sql.com/customers/). Later, this chapter takes a closer look at how a few of these users are putting MySQL to work and, in some cases, saving millions of dollars in the process.

What Makes MySQL So Popular?

MySQL is a relational database server that inches closer to the features you'll find in competing proprietary products with each release, meaning you won't encounter too many surprises if you're familiar with another database product. Its well-known convenient pricing aside (free for many uses), what else is it about MySQL that makes it so popular? This section highlights some of the key features contributing to its soaring popularity. Afterward, some specific information is offered pertinent to two major milestone releases of the MySQL product, namely versions 4 and 5.

Flexibility

No matter what operating system you're running, chances are MySQL has you covered. On the MySQL Web site, you'll find optimized binaries available for 14 platforms, including Compaq Tru64, DEC OSF, FreeBSD, IBM AIX, HP-UX, Linux, Mac OS X, Novell NetWare, OpenBSD, QNX, SCO, SGI IRIX, Solaris (versions 8 and 9), and Microsoft Windows. Packages are also available for Red Hat, SUSE, and Ubuntu. Furthermore, MySQL makes the source code available for download if binaries are not available for your platform, or if you want to perform the compilation yourself.

A wide array of APIs is also available for all of the most popular programming languages, including C, C++, Java, Perl, PHP, Ruby, and Tcl, among others.

MySQL also offers many types of mechanisms for managing data; these are known as storage engines. The importance of taking care to choose a particular storage engine is analogous to the importance of using an appropriate algorithm for a particular task. Like algorithms, storage engines are particularly adept at certain tasks and may be maladapted for others. MySQL has long supported several engines, namely MyISAM (the default on all operating systems except Windows), MEMORY (previously known as HEAP), InnoDB (the default on Windows), and MERGE. Version 5 added the ARCHIVE, BLACKHOLE, CSV, FEDERATED, and EXAMPLE engines. More recently, MySQL has released an alpha version of Falcon, a high-performance storage engine intended for large-scale deployments on SMP systems.

Note MySQL storage engines seem to be coming out of the woodwork these days. While those mentioned in the previous paragraph are most commonly used, still others exist. For example, MySQL and a company named NitroSecurity have recently teamed up to integrate NitroSecurity's NitroEDB storage engine into the server. NitroEDB was specially developed to facilitate real-time analysis of network-related data. Still other specialized storage engines exist or are under development, including Solid Information Technology's solidDB for MySQL, Infobright's BrightHouse, and community-written engines such as PrimeBase XT (PBXT).

Each storage engine bears its own strengths and weaknesses and should be applied selectively to best fit the intended use of your data. Because a single database could consist of several tables, each with its own specific purpose, MySQL affords you the opportunity to simultaneously use different storage engines in a single database. These handlers are introduced in Chapter 28.

Although MySQL uses English-compatible settings by default, its developers are cognizant that not all users hail from English-speaking countries, and thus MySQL enables users to choose from more than 35 character sets. You can use these character sets to control the language used for error and status messages, how MySQL sorts data, and how data is stored in the tables.

Power

Since the earliest releases, the MySQL developers have focused on performance, even at the cost of a reduced feature set. To this day, the commitment to extraordinary speed has not changed, although over time the formerly lacking capabilities have grown to rival many of the commercial and open source competitors. This section briefly touches upon some of the more interesting performance-related aspects.

Enterprise-Level SQL Features

MySQL's detractors had long complained that MySQL's lack of advanced features such as subqueries, views, and stored procedures prevented the database from being adopted at the enterprise level. The development team's long-standing response to such grumblings was a restatement of its commitment to speed and performance, and in due time these features would be incorporated. Version 5.0 is proof of this commitment, with all of the aforementioned features now available (subqueries were introduced in version 4.1). Several subsequent chapters are devoted to these relatively new features.

Full-Text Indexing and Searching

MySQL version 3.23.23 added full-text indexing and searching, a feature that greatly enhances the performance of mining data from text-based columns (namely CHAR, VARCHAR, TINYTEXT, TEXT, MEDIUMTEXT, and LONGTEXT). This feature also enables you to produce results in order of relevance in accordance with how closely the query matches the row's indexed textual columns. This feature is introduced in Chapter 36.

Query Caching

Query caching is one of MySQL's greatest speed enhancements. Simple and highly effective when enabled, query caching causes MySQL to store SELECT queries, along with their corresponding results, in memory. As subsequent queries are executed, MySQL compares them against the cached queries; if they match, MySQL forgoes the costly database retrieval and instead simply dumps the cached query result. To eliminate outdated results, mechanisms are also built in to automatically remove invalidated cache results and re-cache them upon the next request.

Replication

Version 3.23.15 added MySQL's replication feature. Replication allows a database located within one MySQL server to be duplicated on another MySQL server, which provides a great number of advantages. For instance, just having a single slave database in place can greatly increase availability, because it could be immediately brought online if the master database experiences a problem. If you have multiple machines at your disposal, client queries can be spread across the master and multiple slaves, considerably reducing the load that would otherwise be incurred on a single machine. Another advantage involves backups; rather than take your application offline while a backup is completed, you could instead execute the backup on a slave, allowing your application to incur zero down time.

Security

MySQL sports a vast array of security and configuration options, enabling you to wield total control over just about every imaginable aspect of its operation. For example, with MySQL's configuration options you can control things such as the following:

• The daemon owner, default language, default port, location of MySQL's data store, and other key characteristics.
• The amount of memory allocated to threads, the query cache, temporary tables, table joins, and index key buffers.
• Various aspects of MySQL's networking capabilities, including how long it will attempt to perform a connection before aborting, whether it will attempt to resolve DNS names, the maximum allowable packet size, and more.

MySQL's security options are equally impressive, allowing you to manage things such as the following:

• The total number of queries, updates, and connections allowed on an hourly basis.
• Whether a user must present a valid SSL certificate to connect to the database.
• Which actions are available to a user for a given database, table, and even column. For example, you might allow a user UPDATE privileges for the e-mail column of a corporate employee table, but deny DELETE privileges.

In addition, MySQL tracks numerous metrics regarding all aspects of database interaction, such as the total incoming and outgoing bytes transferred, counts of every query type executed, and total threads open, running, cached, and connected. It also tracks the number of queries that have surpassed a certain execution threshold, total queries stored in the cache, if it's enabled, uptime, and much more. Such numbers prove invaluable for continuously tuning and optimizing your server throughout its lifetime.

Because of the importance of these options, they're returned to repeatedly throughout the forthcoming chapters. Specifically, part of Chapter 26 is devoted to MySQL configuration, and the whole of Chapter 29 is dedicated to MySQL security.

Flexible Licensing Options

MySQL is released under two licensing options, each of which is described in this section.

MySQL Open Source License

MySQL AB offers a free version of its software under the terms of the GNU General Public License (GPL). If your software is also licensed under the GPL, you're free to use MySQL in conjunction with your application, and even modify it and redistribute it, provided that you do it all in accordance with the terms set forth in the GPL. You can learn more about the terms of the GPL at http://www.fsf.org/licensing/licenses/gpl.html.

Recognizing that not all users wish to release their software under the restrictive terms of the GPL, MySQL AB has more recently made its products available under the terms of the Free/Libre and Open Source Software (FLOSS) License Exception. The FLOSS Exception is for those users who wish to use MySQL AB's software in conjunction with applications released under any of several preapproved licenses. Among others, the licenses include the Apache Software License (versions 1.0, 1.1, and 2.0), the BSD "July 22 1999" license, the GNU Lesser General Public License (LGPL), and the PHP 3.0 License.

More information about the FLOSS Exception, including the list of accepted licenses, is available at http://www.mysql.com/company/legal/licensing/floss-exception.html. Please review the specific terms set forth in the FLOSS Exception before coming to the conclusion that it's suitable for your needs. Perhaps most notably, keep in mind that you must obey the terms set forth by the GPL for both the MySQL program and any derivative work created in conjunction with it. In other words, the FLOSS Exception allows you to use a MySQL product in conjunction with your application as long as you never copy, modify, or distribute the MySQL product.

Note MySQL AB also provides for another special provision specific to PHP users, known as the Optional GPL License Exception for PHP. This provision states that derivative works created in conjunction with GPL-licensed MySQL software can be distributed as long as the GPL is followed for all sections not licensed under the PHP 3.0 License.

Commercial License

The MySQL Commercial License is available if you would rather not release or redistribute your project code, or if you want to build an application that is not licensed under the GPL or another compatible license. If you choose the MySQL Commercial License, pricing options are quite reasonable, and each option comes with a certain level of guaranteed support. See the MySQL Web site for the latest details regarding these options.

Which License Should You Use?

Granted, the plethora of licensing arrangements often leaves developers confused as to which is most suitable to their particular situation. While it isn't practical to cover every conceivable circumstance, there are a few general rules you might consider when determining the most applicable license:

• If your application requires MySQL to operate and will be released under the GPL or a GPL-compatible license, you're free to use the MySQL Open Source License and therefore use MySQL free of charge.
• If your application requires customers to install a version of MySQL to operate it but you are not going to release it under the GPL or a GPL-compatible license, then you need to purchase a MySQL Commercial License for each version.
• If your application is bundled with a copy of MySQL but will not be released under the GPL or a GPL-compatible license, then you need to purchase a MySQL Commercial License for each copy of the application you sell.

A (Hyper) Active User Community

Although many open source projects enjoy an active user community, MySQL's user community might better be defined as hyperactive. For starters, the company strives to release an updated version every four to six weeks, resulting in a constant stream of bug fixes and feature enhancements. In addition, there are thousands of open source projects under way that depend upon MySQL as the back end for managing a broad array of information, including server log files, e-mail, images, Web content, help desk tickets, and gaming statistics. If you require advice or support, you can use your favorite search engine to consult one of the hundreds of tutorials written regarding every imaginable aspect of the software; browse MySQL's gargantuan manual; or pose a question in any of the high-traffic MySQL-specific newsgroups. In fact, when researching MySQL, the problem isn't so much one of whether you'll find what you're looking for, but where to begin!

The Evolution of MySQL

MySQL has long been heralded for its speed, and historically derided for its lack of so-called requisite enterprise features. Of course, its exploding popularity proved that for millions of users, these advanced features proved to be of little interest. However, as data warehousing and performance needs grew increasingly sophisticated, the MySQL developers soon recognized the need to expand the database's feature set. This section outlines the major features integrated into the product beginning with version 4.

By the way, this section isn't meant to merely provide you with a history lesson; surely you had enough of those in high school. Rather, its purpose is twofold: to give you a general overview of MySQL's many features, and to provide you with a roadmap of sorts, identifying specific chapters where these features are introduced.

MySQL 4

The March 2003 production release of MySQL 4.0 marked a major milestone in the software's history. After 18 months of development releases and several years of labor, the completed product was made available to the general public, bringing several new features to the table that have long been considered standard among any viable enterprise database product. Some of the feature highlights are enumerated here:

• Addition of InnoDB to standard distribution: The InnoDB storage engine, which has been available to users since version 3.23.34a, was made part of the standard distribution as of version 4.0. The InnoDB tables bring a host of new features to MySQL users, including transactions, foreign key integrity, and row-level locking. The InnoDB engine is introduced in Chapter 28, and transactions are discussed in Chapter 37.
• Query caching: Query caching, which was made available in version 4.0.1, greatly improves the performance of selection queries by storing query results in memory and retrieving those results directly, rather than repeatedly querying the database for the same result set.
• Embedded MySQL server: An embedded MySQL server makes it possible to integrate a full-featured MySQL server into embedded applications. Embedded applications power things like kiosks, CD-ROMs, Internet appliances, cell phones, and PDAs.
• Subqueries: Subqueries can greatly reduce the complexity otherwise required of certain queries, offering the ability to embed SELECT statements inside another query statement. As of version 4.1, MySQL users can now enjoy the use of standards-based subquery operations. Chapter 35 introduces you to this long-awaited feature.
• Secure connections via Secure Sockets Layer (SSL): Using solely unencrypted client/server connections raises the possibility that the data and the authentication credentials could be intercepted and even modified by some uninvited third party. As of version 4.0, encrypted connections can be established between MySQL and any client supporting SSL technology. This feature is introduced in Chapter 29.
• Spatial extensions: With the release of version 4.1 came support for spatial extensions, which are used to create, store, and analyze geographic information. For example, this feature might be used to plot on a map the location of shoe stores in a particular city.

MySQL 5.0

Officially released in October of 2005, MySQL 5.0's impressive array of features signified a major step forward in terms of the product's evolution, and was the catalyst for the company's substantial capitalization of market share at the cost of its entrenched competitors. Some of the feature highlights are described here:

• Stored procedures: A stored procedure is a set of SQL statements that is stored in the database and made available in the same manner as SQL functions such as min() and rand(). Based on the requirements set forth by the latest pending SQL standard, SQL-2003, the addition of stored procedures fulfills one of the last major feature deficiencies of MySQL. Chapter 32 is devoted to a complete overview of this topic.
• Views: Database tables often consist of information that isn't intended to be viewed by the public or, in many cases, by the programmers tasked with using that database. Views enable database administrators to limit access to database tables to only the data that is intended to be used. Views also eliminate the need to continually construct potentially long and unwieldy queries that stretch across numerous tables. A view is essentially a virtual representation of a subset of data found in one or more tables. Views are introduced in Chapter 34.
• Triggers: A trigger is essentially a stored procedure that is invoked based on the occurrence of a defined event. Triggers are often used to validate or coerce data before or after insertion into the table to comply with business logic or rules. Chapter 33 offers a thorough introduction to this new feature.
• INFORMATION_SCHEMA: MySQL has long supported the SHOW command, a nonstandard means for learning more about data structures residing in the database server. However, this methodology is incompatible with all other databases, and is also restrictive because of the SHOW command's inability to be used in SELECT statements. To resolve this limitation, a new virtual database, INFORMATION_SCHEMA, was added as of version 5.0. This database stores metadata information about all the other databases found on the server. By way of this database, users can now use the standard SELECT statement to learn more about a database's structure.

MySQL 5.1

Don't let the minor version number fool you; MySQL 5.1 is a significant release in the product's history. This section outlines just a few of this release's key features:

• Pluggable Storage Engine API: Do you wish MySQL was able to authenticate against your custom user credential solution? Offer a custom data-filtering function? Query nonstandard data formats such as MP3 files? The Pluggable Storage Engine API allows you to add your own custom capabilities, extending the database in ways you never before dreamed possible.
• Partitioning: Partitioning, or the splitting of tables into smaller physical pieces, can have several advantages when working with large amounts of data. Query performance can be greatly improved because table indexes are reduced to several smaller ranges rather than one large, contiguous range. Consider a scenario where you are analyzing customer invoices for a national retail chain. Rather than deal with the potentially billions of records that could easily be generated in just a few years' time, you might use partitioning to separate the invoices according to year or even month. Partitioning can also affect storage costs by making it possible to move less commonly used table data to lower-cost storage media while still allowing it to be retrieved on demand.
• Event scheduling: MySQL's event-scheduling feature is similar to the Unix cron program, executing a SQL query according to a predefined schedule.
• Load testing: A command-line program called mysqlslap was added to the distribution, allowing you to test performance by executing SQL queries while emulating a scenario where multiple clients are accessing the system.

Prominent MySQL Users

As you learned in the opening paragraphs of this chapter, MySQL boasts quite a list of prominent users. This section offers some additional insight into a few compelling implementations.

craigslist

The popular online classifieds and community site craigslist (http://www.craigslist.org) has been continuously expanding since it was founded in 1995. The craigslist site has depended upon the LAMP (Linux, Apache, MySQL, Perl) stack since its inception, and MySQL has demonstrated its scalability throughout the site's history as it grew from a hobby of founder Craig Newmark to one of the Web's most popular sites, presently processing 9 billion page views per month (see http://www.craigslist.org/about/pr/factsheet.html). Each month, craigslist welcomes more than 30 million users, processes more than 30 million new classified ads, and disseminates more than 2 million new job listings.

According to a case study published at MySQL.com, titled "craigslist Relies on MySQL to Serve Millions of Classified Ads" (http://www.mysql.com/why-mysql/case-studies/mysql-craigslist-casestudy.pdf), craigslist depends upon MySQL to run every database-driven aspect of the site. Of particular interest is the use of MySQL's full-text search capabilities for the site search feature. Consult the case study for a complete breakdown of MySQL's impressive role in running one of the most popular Web sites in the world.

Wikipedia

Founded in January 2001, the volunteer-driven online encyclopedia Wikipedia: The Free Encyclopedia (http://www.wikipedia.org) has grown from a personal project founded by Jimmy Wales to one of the top ten most trafficked sites on the Web (according to http://www.alexa.com/). The site is truly an endless font of knowledge, contributed by knowledgeable and enthusiastic individuals from all over the world.

According to a presentation given by Jimmy Wales at the 2005 MySQL Users Conference, Wikipedia is larger than Encyclopedia Britannica and Microsoft Encarta combined and hosts over 1.52 million articles written in a staggering 250 languages. Wikipedia and its eight sister projects (falling under the Wikimedia Foundation umbrella, http://www.wikimedia.org) depend upon the LAMP platform, and use an array of five MySQL servers to process 200 million queries and 1.2 million updates daily (http://en.wikipedia.org/wiki/MySQL). This particular configuration is demonstrative of MySQL's capabilities in both a high read and high write environment, given the staggering number of both views and modifications taking place on a daily basis.

Yahoo! Finance

When you think of one of the true Internet heavyweights, the online portal Yahoo! (http://www.yahoo.com/) almost assuredly pops into mind. Although most would think that this corporate juggernaut is devoted to commercial IT solutions, Yahoo! actually operates on the FreeBSD platform, an open source Unix variant. However, Yahoo!'s preference for experimenting with and even deploying open source solutions might not be more pronounced than its move to power companion Web site http://finance.yahoo.com/ using FreeBSD and a MySQL back end. No small feat, considering that the Web site processes billions (with a b) of page views monthly, on average.

Summary

From internal project to global competitor, MySQL has indeed come a very long way in just a decade. This chapter offered a brief overview of this climb to stardom, detailing MySQL's history, progress, and future. A few of the thousands of successful user stories were also presented, highlighting the use of MySQL at organizations having global reach and impact.

In the following chapters, you'll become further acquainted with many MySQL basic topics, including the installation and configuration process, the many MySQL clients, table structures, and MySQL's security features. If you're new to MySQL, this material will prove invaluable for getting up to speed regarding the basic features and behavior of this powerful database server. If you're already quite familiar with MySQL, consider browsing the material nonetheless; at the very least, it should serve as a valuable reference.