Mysql: The definitive guide to using, programming, and administering MySQL 4.1 and 5.0 - Softcover

Dubois, Paul

Softcover

ISBN 10: 0672326736 ISBN 13: 9780672326738

Publisher: Sams, 2005

Synopsis

A new edition of this title is available, ISBN-10: 0672329387 ISBN-13: 9780672329388

 

For years, MySQL has been helping MySQL developers and database administrators learn their MySQL system inside and out. This newest edition has been updated to include information on MySQL 5 and it will prove itself again to you as being the most definitive reference guide to using, administering and programming MySQL databases. You'll learn everything from the basics to using MySQL to generate dynamic web pages to administering MySQL servers. This edition has been reviewed by the top developers in the MySQL community and the changes reflect their feedback, as well as the feedback of many other readers, and it has turned out to be the most comprehensive, thorough edition of MySQL to date. Don't go to work without it!

 

 

About this title

About the Author

Paul DuBois is a writer, database administrator, and leader in the Open Source community. He is currently a Senior Technical Writer at MySQL AB. In addition to MySQL, he is also the author of MySQL and Perl for the Web, MySQL Cookbook, Using csh and tcsh, and Software Portability with imake.

Excerpt. © Reprinted by permission. All rights reserved.

Introduction

Introduction

A relational database management system (RDBMS) is an essential tool in many environments, from the more traditional uses in business, research, and educational contexts, to more recent applications such as powering search engines on the Internet. However, despite the importance of a good database system for managing and accessing information resources, many organizations have found them to be out of reach of their financial resources. Historically, database systems have been an expensive proposition, with vendors charging healthy fees both for software and for support. Also, because database engines often had substantial hardware requirements to run with any reasonable performance, the cost was even greater.

In recent years, the situation has changed, on both the hardware and software sides of the picture. Personal computers have become inexpensive but powerful, and there is a thriving movement devoted to writing high-performance operating systems for them. They are available for the cost of an inexpensive CD, or free over the Internet. These include several BSD Unix derivatives (FreeBSD, NetBSD, OpenBSD) as well as various distributions of Linux (Fedora, Gentoo, SuSE, to name a few).

Production of free operating systems to drive personal computers to their full capabilities has proceeded in concert with—and to a large extent has been made possible by—the development of freely available tools like gcc, the GNU C compiler. These efforts to make software available to anyone who wants it have resulted in what is now called the Open Source movement, and which has produced many important pieces of software. For example, Apache is the most widely used Web server on the Internet. Other Open Source successes are the Perl, Python, and Ruby general-purpose scripting languages and PHP, a language that is popular due largely to the ease with which it allows dynamic Web pages to be written. These all stand in contrast to proprietary solutions that lock you into high-priced products from vendors that don't even provide source code.

Database software has become more accessible, too. Open Source database systems such as PostgreSQL are available for free, and commercial vendors such as Informix and Oracle offer their software at no cost for operating systems such as Linux. (However, the commercial-vendor products generally come in binary-only form with no support, which lessens their usefulness.)

Another entry into the no-to-low cost database arena is MySQL, a SQL client/server relational database management system originating from Scandinavia. MySQL includes an SQL server, client programs for accessing the server, administrative tools, and a programming interface for writing your own programs.

MySQL's roots begin in 1979, with the UNIREG database tool created by Michael "Monty" Widenius for the Swedish company TcX. In 1994, TcX began searching for an RDBMS with an SQL interface for use in developing Web applications. They tested some commercial servers, but found them all too slow for TcX's large tables. They also took a look at mSQL, but it lacked certain features that TcX required. Consequently, Monty began developing a new server. The programming interface was explicitly designed to be similar to the one used by mSQL because several free tools were available for mSQL, and by using a similar interface, those same tools could be used for MySQL with a minimum of porting effort.

In 1995, David Axmark of Detron HB began to push for TcX to release MySQL on the Internet. David also worked on the documentation and on getting MySQL to build with the GNU configure utility. MySQL 3.11.1 was unleashed on the world in 1996 in the form of binary distributions for Linux and Solaris. Today, MySQL works on many more platforms and is available in both binary and source form. The company MySQL AB has been formed to provide distributions of MySQL under both Open Source and commercial licenses, and to offer support and training services.

Initially, MySQL became widely popular because of its speed and simplicity. But there was criticism, too, because it lacked features such as transactions and foreign key support. Nevertheless, MySQL continued to develop, adding not only those features but others such as row-level locking, replication, subqueries, stored procedures, views, and triggers. There is still work to do, but the addition of these capabilities has caused people who once would have considered only "big engine" database systems for their applications to give MySQL a second look.

MySQL is an Open Source project that can be used for free under many circumstances, which is one reason that it enjoys widespread popularity in the Open Source community. But MySQL's popularity isn't limited to Open Source enthusiasts. Yes, it runs on personal computers (indeed, much MySQL development takes place on inexpensive Linux systems). But MySQL is portable and runs on commercial operating systems (such as Solaris, Mac OS X, and Windows) and on hardware all the way up to enterprise servers. Furthermore, its performance rivals any database system you care to put up against it, and it can handle large databases with billions of records. In the business world, MySQL's presence continues to increase as companies discover it to be capable of handling their database needs at a fraction of what they are using to pay for commercial licensing and support.

MySQL lies squarely within the picture that unfolds before us: freely available operating systems running on powerful but inexpensive hardware, putting substantial processing power and capabilities in the hands of more individuals and businesses than ever before, on a wider variety of systems than ever before. This lowering of the economic barriers to computing puts powerful database solutions within reach of more people and organizations than at any time in the past. Organizations that once could only dream of putting the power of a high-performance RDBMS to work for them now can do so for very little cost. This is true for individuals as well. For example, I use MySQL with Perl, PHP, and Apache on my Apple iBook running Mac OS X. This allows me to carry my work with me anywhere. Total cost: the cost of the iBook.

Why Choose MySQL?

If you're looking for a free or low-cost database management system, several are available from which to choose: MySQL, PostgreSQL, SQLite, one of the free-but-unsupported engines from commercial vendors, and so forth. When you compare MySQL with other database systems, think about what's most important to you. Performance, support, features (such as SQL conformance or extensions), licensing conditions and restrictions, and price all are factors to take into account. Given these considerations, MySQL has many attractive features to offer:

• Speed. MySQL is fast. Its developers contend that MySQL is about the fastest database system you can get. You can investigate this claim by visiting http://dev.mysql.com/tech-resources/benchmarks/, a performance-comparison page on the MySQL AB Web site.

• Ease of use. MySQL is a high-performance but relatively simple database system and is much less complex to set up and administer than larger systems.

• Query language support. MySQL understands SQL (Structured Query Language), the standard language of choice for all modern database systems.

• Capability. The MySQL server is multi-threaded, so many clients can connect to it at the same time. Each client can use multiple databases simultaneously. You can access MySQL interactively using several interfaces that let you enter queries and view the results: command-line clients, Web browsers, or GUI clients. In addition, programming interfaces are available for many languages, such as C, Perl, Java, PHP, Python, and Ruby. You can also access MySQL using applications that support ODBC (Open Database Connectivity), a database communications protocol developed by Microsoft. This gives you the choice of using prepackaged client software or writing your own for custom applications.

• Connectivity and security. MySQL is fully networked, and databases can be accessed from anywhere on the Internet, so you can share your data with anyone, anywhere. But MySQL has access control so that one person who shouldn't see another's data cannot. To provide additional security, MySQL supports encrypted connections using the Secure Sockets Layer (SSL) protocol.

• Portability. MySQL runs on many varieties of Unix, as well as on other non-Unix systems, such as Windows, NetWare, and OS/2. MySQL runs on hardware from small personal computers (even palmtop devices) to high-end servers.

• Small size. MySQL has a modest distribution size, especially compared to the huge disk space footprint of certain commercial database systems.

• Availability and cost. MySQL is an Open Source project with dual licensing. First, it is available under the terms of the GNU General Public License (GPL). This means that MySQL is available without cost for most in-house uses. Second, for organizations that prefer or require formal arrangements or that do not want to be bound by the conditions of the GPL, commercial licenses are available.

• Open distribution and source code. MySQL is easy to obtain; just use your Web browser. If you don't understand how something works, are curious about an algorithm, or want to perform a security audit, you can get the source code and examine it. If you think you've found a bug, report it; the developers want to know.

What about support? Good question; a database system isn't much use if you can't get help for it. This book is one form of assistan...

Edition details

• Publisher: Sams
• Publication date: 2005
• ISBN 10: 0672326736
• ISBN 13: 9780672326738
• Binding: Paperback
• Language: English
• Edition number: 3
• Number of pages: 1294