Learning MySQL (2007)
Part I. Introduction
Chapter 1. Introduction
MySQL (pronounced “My Ess Cue Ell”) is more than just “the world’s most popular open source database,” as the developers at the MySQL AB corporation (http://www.mysql.com) claim. This modest-sized database has introduced millions of everyday computer users and amateur researchers to the world of powerful information systems.
MySQL is a relatively recent entrant into the well-established area of relational database management systems (RDBMs), a concept invented by IBM researcher Edgar Frank Codd in 1970. Despite the arrival of newer types of data repositories over the past 35 years, relational databases remain the workhorses of the information world. They permit users to represent sophisticated relationships between items of data and to calculate these relationships with the speed needed to make decisions in modern organizations. It’s impressive how you can go from design to implementation in just a few hours, and how easily you can develop web applications to access terabytes of data and serve thousands of web users per second.
Whether you’re offering products on a web site, conducting a scientific survey, or simply trying to provide useful data to your classroom, bike club, or religious organization, MySQL gets you started quickly and lets you scale up your services comfortably over time. Its ease of installation and use led media analyst Clay Shirky to credit MySQL with driving a whole new type of information system he calls “situated software”—custom software that can be easily designed and built for niche applications.
In this book, we provide detailed instructions to help you set up MySQL and related software. We’ll teach you Structured Query Language (SQL), which is used to insert, retrieve, and manipulate data. We’ll also provide a tutorial on database design, explain how to configure MySQL for improved security, and offer you advanced hints on getting even more out of your data. In the last five chapters, we show how to interact with the database using the PHP and Perl programming languages, and how to allow interaction with your data over the medium most people prefer these days: the Web.
Why Is MySQL so Popular?
The MySQL development process focuses on offering a very efficient implementation of the features most people need. This means that MySQL still has fewer features than its chief open source competitor, PostgreSQL, or the commercial database engines. Nevertheless, the skills you get from this book will serve you well on any platform.
Many database management systems—even open source ones—preceded MySQL. Why has MySQL been the choice for so many beginners and small sites, and now for some heavyweight database users in government and industry? We can suggest a few factors:
Size and speed
MySQL can run on very modest hardware and puts very little strain on system resources; many small users serve up information to their organizations by running MySQL on modest desktop systems. The speed with which it can retrieve information has made it a longstanding favorite of web administrators.
Over the past few years, MySQL AB has addressed the need of larger sites by adding features that necessarily slow down retrieval, but its modular design lets you ignore the advanced features and maintain the suppleness and speed for which MySQL is famous.
Ease of installation
Partly because MySQL is small and fast, it works the way most people want straight “out of the box.” It can be installed without a lot of difficult and sophisticated configuration. Now that many Linux distributions include MySQL, installation can be almost automatic.
This doesn’t mean MySQL is free of administrative tasks. In particular, we’ll cover a few things you need to do at the start to tighten security. Very little configuration is shown in this book, however, which is a tribute to the database engine’s convenience and natural qualities.
Attention to standards
As we’ll explain in the Structured Query Language” section later in this chapter, multiple standards exist in the relational database world, and it’s impossible to claim total conformance. But learning MySQL certainly prepares you for moving to other database engines. Moving code from one database engine to another is never trivial, but MySQL does a reasonable job of providing a standard environment, and gets better as it develops more features.
Responsiveness to community
With a few hundred employees scattered around the globe, MySQL AB is a very flexible organization that keeps constant tabs on user needs. At its conferences, lead developers get out in front and make themselves available to everyone with a gripe or a new idea. There are also local MySQL user groups in almost every major city. This responsiveness is helped by the fact that MySQL is open and free; any sufficiently skilled programmer can look at the program code to find and perhaps help in fixing problems.
MySQL actually has a dual-license approach: if you want to build your own product around it, you pay MySQL AB a license fee. If you just want to use MySQL to serve your own data, you don’t have to pay the license fee. MySQL also offers technical support, as do numerous other companies and consultants, some of them probably near you.
Easy interface to other software
It is easy to use MySQL as part of a larger software system. For example, you can write programs that can interact directly with a MySQL database. Most major programming languages have libraries of functions for use with MySQL; these include C, PHP, Perl, Python, Ruby, and theMicrosoft .NET languages. MySQL also supports the Open Database Connectivity (ODBC) standard, making it accessible even when MySQL-specific functionality isn’t available.
Elements of MySQL and Its Environment
You need to master several skills to run a database system. In this section, we’ll lay out what goes into using MySQL and how we meet those needs in this book.
A MySQL installation has two components: a server that manages the data, and clients that ask the server to do things with the data, such as change entries or provide reports. The client that you’ll probably use most often is the mysql “MySQL monitor” program, provided by the MySQL AB company and available in most MySQL installations. This allows you to connect to a MySQL server and run SQL queries. Other simple clients are included in a typical installation; for example, the mysqladmin program is a client that allows you to perform various server administration tasks.
In fact, any program that knows how to talk to the MySQL server is a client; a program for a web-based shopping site or an application to generate sales graphs for a marketing team can both be clients. In Chapter 3, you’ll learn to use the MySQL monitor client to access the MySQL server. In Chapters 13 through 15, we’ll look at how we can use PHP to write our own custom clients that run on a web server to present a web frontend to the database for this. We’ll use the Apache web server (http://httpd.apache.org). Apache has a long history of reliable service and has been the most popular web server in the world for over 10 years. The Apache web server—or “HTTP server”— project is managed by the Apache Foundation (http://www.apache.org). Although the web server and MySQL server are separate programs and can run on separate computers, it’s common to find small- to medium-scale implementations that have both running on a single computer. In Chapters 16 through 18, we’ll explore how the Perl programming language can be used to build command-line and web interfaces to the MySQL server.
To follow the content in this book, you will need some software; fortunately, all the software we use is open source, free for noncommercial use, and easily downloaded from the Internet. To cover all parts of this book, you need a MySQL database server, Perl, and a web server that can talk to MySQL using the PHP and Perl programming languages. We’ll explore four aspects of using MySQL:
MySQL server
We explain how to create your own MySQL installation, and how to configure and administer it.
SQL
This is the core of MySQL use, and the major topic in this book. It’s introduced in Structured Query Language.”
Programming languages
SQL is not a simple or intuitive language, and it can be tedious to repeatedly perform complex operations. You can instead use a general-purpose programming language such as PHP or Perl to automatically create and execute SQL queries on the MySQL server. You can also hide the details of the interaction with the database behind a user-friendly interface. We’ll show you how to do this.
Web database applications
We explain how you can use PHP or Perl to create dynamic, database-driven web applications that can publish information from the database to the Web, and capture information provided by users.
HTML is the lingua franca of the Web. Although learning HTML is not within the scope of this book, there are many great HTML guides available, including HTML and XHTML: The Definitive Guide by Chuck Musciano (O’Reilly). We recommend that you pick up the basics of HTML before reading Chapters 13, 14, 15, or 18.
The LAMP Platform
It’s very common to find web database applications developed using the Linux operating system, the Apache web server, the MySQL database management system, and the Perl or PHP scripting language. This combination is often referred to by the acronym LAMP, a term invented at O’Reilly Media.
Linux is the most common development and deployment platform, but as we’ll show in this book, you can run all the tools on other operating systems. In fact, we’ll give directions for getting started on Linux, Windows, and Mac OS X. Most of the content in this book can be used for other operating systems with little modification.
The P in LAMP originally stood for Perl, but over the past decade, users have increasingly turned to PHP for developing dynamic web pages. PHP is very clean and efficient for retrieving data and displaying it with minimal processing. If you have to do heavy data crunching after the data is returned from MySQL, Perl may still be a better choice. We discuss PHP and Perl largely independently; you can pick up one without needing to learn the other, although we believe that you’ll benefit from learning both languages. In fact, almost any modern language can be used to perform this task; most of them have the necessary interfaces to both web servers and database engines.
Structured Query Language
IBM is to be credited not only with inventing the relational database, but developing the language still used today to interact with such databases. SQL is a little odd, bearing the stylistic marks of its time and its developers. It’s also gotten rather bloated over the years—a process made worse by its being standardized (multiple times)—but in this book we’ll show you the essentials you really need and help you become fluent in them.
SQL shows many of the problems that are commonly attributed to computing standards: it tries to accomplish too much, it forces new features into old molds to maintain backward compatibility, and it reflects uneasy compromises and trade-offs among powerful vendors. As a result, there are several standards that database management systems can adhere to. SQL-92 dates back to 1992 and provides just about everything that you will need for beginning work. However, it lacks features demanded by some modern applications. SQL:1999 was standardized in 1999 and adds a huge number of new features, many of them considered overkill by some experts. There is also a more recent standard, SQL:2003, that was published in 2003 and adds support for XML data.
Each development team has to decide on the trade-offs between the features requested by users and the need to keep software fast and robust, and so database engines generally don’t conform totally to any one standard. Furthermore, historical differences have stayed around in legacy database engines. That means that even if you use fairly simple, vanilla SQL, you may have to spend time when porting your skills and your code to another database engine.
In this book, we’ll show you how to use MySQL’s flavor of SQL to create databases and store and modify data. We’ll also show you how to use this SQL variant to administer the MySQL server and its users.
MySQL Software Covered in This Book
You can be very productive with MySQL without dedicating a lot of time to configuration and administration. In Chapter 2, we’ll look at several common ways of setting up the software you’ll need for this book. While you can skip most of the instructions if you already have a working MySQL installation, we recommend you at least skim through the material for your operating system; we’ll frequently refer to parts of this chapter later on. As part of this chapter, we explain how you can configure your MySQL server for good security.
MySQL provides many other tools for administration, including compile-time options, a large configuration file, and standalone utilities developed by both MySQL AB and external developers. We’ll give you the basics that will keep you up and running in most environments, and will briefly describe even some relatively advanced topics.
We won’t cover all the programs that come with the MySQL distribution, and we won’t spend too long on each one; the MySQL reference manual does a good job of covering all the options. We’ll instead look at the programs and options that you’re most likely to use in practice; these are the ones we’ve used ourselves a reasonable number of times over several years of working with MySQL.
The Book’s Web Site
We’ve set up the web site, http://www.learningmysql.com, which contains the sample databases, datafiles, and program code. We recommend you make good use of the web site while you read this book.