MySQL in a Nutshell (2008)

Part I. Introduction and Tutorials

This part of the book presents information that is useful to readers who need a basic introduction to MySQL. This part will probably not be sufficient for someone who is totally new to database programming and administration; a host of other introductory books exist for that reader. However, this part can provide the necessary background to someone who has some knowledge of other databases and wants to move to MySQL, who has used a MySQL database on a hosting service and wants to create a standalone server, or who has other gaps in introductory knowledge. Installation, basic configuration, and essential SQL are explained in this part of the book.

Chapter 1. Introduction to MySQL

MySQL is an open source, multithreaded, relational database management system created by Michael “Monty” Widenius in 1995. In 2000, MySQL was released under a dual-license model that permitted the public to use it for free under the GNU General Public License (GPL); this caused its popularity to soar. The company that owns and develops MySQL is MySQL AB (the AB stands for aktiebolag, the Swedish term for stock company), which is now a subsidiary of Sun Microsystems. Currently, MySQL AB estimates that there are more than 6 million installations of MySQL worldwide, and reports an average of 50,000 downloads a day of MySQL installation software from its site and from mirror sites. The success of MySQL as a leading database is due not only to its price—after all, other cost-free and open source databases are available—but also its reliability, performance, and features.

The Value of MySQL

Many features contribute to MySQL’s standing as a superb database system. Its speed is one of its most prominent features. In a comparison by eWEEK of several databases—including MySQL, Oracle, MS SQL, IBM DB2, and Sybase ASE—MySQL and Oracle tied for best performance and for greatest scalability (see http://www.mysql.com/it-resources/benchmarks for more details). MySQL is remarkably scalable, and is able to handle tens of thousands of tables and billions of rows of data. Plus, it manages small amounts of data quickly and smoothly.

The storage engine, which manages queries and interfaces between a user’s SQL statements and the database’s backend storage, is the critical software in any database management system. MySQL offers several storage engines with different advantages. Some are transaction-safe storage engines that allow for rollback of data. Additionally, MySQL has a tremendous number of built-in functions that are detailed in several chapters of this book. MySQL is also very well known for rapid and stable improvements. Each new release comes with speed and stability upgrades, as well as new features.

The MySQL Package

The MySQL package comes with several programs. Foremost is the MySQL server, represented by the mysqld daemon. The daemon listens for requests on a particular network port (3306 by default) by which clients submit queries. The standard MySQL client program is simply calledmysql. With this text-based interface, a user can log in and execute SQL queries. This client can also accept queries from text files containing queries, and thereby execute them on behalf of the user or other software. However, most MySQL interaction is done by programs using a variety oflanguages. The interfaces for C, Perl, and PHP are discussed in this book.

A few wrapper scripts for mysqld come with MySQL. The mysqld_safe script is the most common way to start mysqld, because the script can restart the daemon if it crashes. This helps ensure minimal downtime for database services. The script mysqld_multi is used to start multiple sessions of mysqld_safe, and thereby multiple mysqld instances, for handling requests from different ports, and to make it easier to serve different sets of databases or to test different versions of MySQL.

MySQL also comes with a variety of utilities for managing a MySQL server. mysqlaccess is used for creating user accounts and setting their privileges. mysqladmin can be used to manage the MySQL server itself from the command line. This interaction includes checking a server’s status and usage, and shutting down a server. mysqlshow may be used to examine a server’s status, as well as information about databases and tables. Some of these utilities require Perl, or ActivePerl for Windows, to be installed on the server. See http://www.perl.org to download and install a copy of Perl on non-Windows systems, and see http://www.activestate.com/Products/ActivePerl to download and install a copy of ActivePerl on Windows systems.

MySQL also comes with a few utilities for importing and exporting data to and from MySQL databases. mysqldump is the most popular for exporting data and table structures to a plain-text file known as a dump file. This can be used for backing up data or for manually moving it between servers. The mysql client can be used to import the data back to MySQL from a dump file. See Chapter 16 for more on utilities.

Licensing

Although MySQL can be used for free and is open source, MySQL AB holds the copyrights to the source code. The company offers a dual-licensing program for its software: one allows cost-free use through the GPL under certain common circumstances, and the other is a commercial license bearing a fee. They’re both the same software, but each has a different license and different privileges. See http://www.fsf.org/licenses for more details on the GPL.

MySQL AB allows you to use the software under the GPL if you use it without redistributing it, or if you redistribute it only with software licensed under the GPL. You can even use the GPL if you redistribute MySQL with software that you developed, as long as you distribute your software under the GPL as well.

However, if you have developed an application that requires MySQL for its functionality and you want to sell your software with MySQL under a nonfree license, you must purchase a commercial license from MySQL AB. There are other scenarios in which a commercial license may be required. For details on when you must purchase a license, see http://www.mysql.com/company/legal/licensing.

Besides holding the software copyrights, MySQL AB also holds the MySQL trademark. As a result, you cannot distribute software that includes MySQL in the name.

Mailing Lists

You can receive some assistance with problems that you may have with MySQL from the MySQL community at no charge through several listserv email systems hosted by MySQL AB. There is a main mailing list for MySQL (mysql) and several specialized mailing lists where anyone can post a message for help on a particular topic. One list covers questions about database performance (benchmarks). Another is for questions on the Windows versions of MySQL (win32). There are also lists for problems concerning the Java Database Connectivity™ (JDBC) drivers (java) and for the Perl DBI module (perl).

For a complete listing or to subscribe to one or more of these mailing lists, go to http://lists.mysql.com. On this mailing list page, you will find links for subscribing to each list. When you click a subscription link, you will see a very simple form on which to enter your email address. Incidentally, some subscribers like to use special email addresses and names representing their online personas. This allows anonymity and may make sorting emails easier. Others prefer to use their real names and contact information. After you enter your email address, you will receive an automated message to confirm your address. That email will have a link to the MySQL site with some parameters identifying your address. Click the link, and it will open your web browser and confirm your subscription.

The page from which you can subscribe to a list also has links for unsubscribing from lists, as well as links to archives of previous listserv messages for each list. You can search these archives for messages from others who are describing the same problem that you are trying to resolve. It’s always a good idea to search archives before posting anything of your own, to find out whether your topic has been discussed before. If you can’t find a solution in the documentation available to you or in the archives, you can post a message to a particular mailing list by sending an email to that list on lists.mysql.com. For example, if you have a problem with the Perl DBI module in relation to MySQL, you would send a message to perl@lists.mysql.com. Just be sure to send the message from the email account that is registered with the list to which you’re submitting your question.

Books and Other Publications

Besides the mailing list archives mentioned in the previous section, MySQL AB provides extensive online documentation of the MySQL server and all of the other software it distributes. You can find the documentation at http://dev.mysql.com/doc. The documentation is now organized by version of MySQL. You can read the material online or download it in a few different formats (e.g., HTML or PDF). It is also available in hardcopy format: MySQL Language Reference and MySQL Administrator’s Guide, both from MySQL Press.

In addition to this book, O’Reilly Media publishes a few other books on MySQL worth buying and reading. O’Reilly’s mainline MySQL book is Managing & Using MySQL (2nd ed., 2002) by George Reese, Randy Jay Yarger, and Tim King (with Hugh E. Williams). George Reese has compiled a smaller version called MySQL Pocket Reference (2nd ed., 2007). For common practical problem solving, there’s MySQL Cookbook (2nd ed., 2006) by Paul DuBois. For advice on optimizing MySQL and performing administrative tasks, such as backing up databases, O’Reilly has published High Performance MySQL (2004) by Jeremy D. Zawodny and Derek J. Balling.

O’Reilly also publishes several books with regard to the MySQL APIs. For PHP development with MySQL, there’s Web Database Applications with PHP and MySQL (2nd ed., 2004) by Hugh E. Williams and David Lane. For interfacing with Perl to MySQL and other database systems, there’s Programming the Perl DBI (2000) by Alligator Descartes and Tim Bunce. To interface to MySQL with Java, you can use the JDBC and JConnector drivers and George Reese’s book, Database Programming with JDBC and Java (2nd ed., 2000).

In addition to the published books on MySQL, a few web sites offer brief tutorials on using MySQL topics. The O’Reilly Network often publishes articles on MySQL and the APIs for Perl, PHP, and Python in its online publication ONLamp.com (http://www.onlamp.com/onlamp/general/mysql.csp). Incidentally, I’ve contributed a few articles to that site and to several other publications on MySQL and related topics. MySQL AB also provides some in-depth articles on MySQL. You can find them in the DevZone section of its web site,http://dev.mysql.com/tech-resources/articles. Many of these articles deal with new products and features, making them ideal if you want to learn about using the latest releases available even while they’re still in the testing stages. Developer Shed (http://www.devarticles.com/c/b/MySQL) is an additional educational resource. All of these online publications are subscription-free. If you are a MySQL Enterprise customer, though, you can get information about MySQL from its private Knowledge Base, of which I am currently the editor.