O'Reilly Media | Learning MySQL

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

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.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

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.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

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 , you’ll learn to use the MySQL monitor client to access the MySQL server. In Chapters through , 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 through , 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:

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

You can be very productive with MySQL without dedicating a lot of time to configuration and administration. In , 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 .

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 spent 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.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

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.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

Learning MySQL is easiest if you have a database server installed on your computer. By administering your own server, you can go beyond querying and learn how to manage users and privileges, configure the server, and make the best use of its features. Importantly, you also learn the steps required to install and configure MySQL, which is useful when you need to deploy your applications elsewhere.

This chapter explains how to choose and configure a suitable environment for learning MySQL. We cover the following topics:

What to install: how to decide between precompiled packages, an integrated web development environment, and compiling from the source code
Where to install: Linux, Microsoft Windows, or Mac OS X?
Why, when, and how to upgrade MySQL
How MySQL has changed and how to migrate between versions
How to configure the Apache web server and support for the PHP and Perl scripting languages.

MySQL is available in several forms and for many operating systems. In the next section, we examine the choices available and how you can decide what suits you.

As we mentioned before, you’ll need MySQL, the Apache web server, PHP, and Perl for this book. How you choose to install these depends on what you want to do, how confident you are in using your operating system environment, and the level of privileges you have on your system. If you’re planning to use the installation for learning and development only, and not for a production site, then you have greater choice, and you need not be so concerned about security and performance. We’ll describe the most common ways to install the software you need.

You can find the ready-to-use MySQL programs (known as

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

As we mentioned before, you’ll need MySQL, the Apache web server, PHP, and Perl for this book. How you choose to install these depends on what you want to do, how confident you are in using your operating system environment, and the level of privileges you have on your system. If you’re planning to use the installation for learning and development only, and not for a production site, then you have greater choice, and you need not be so concerned about security and performance. We’ll describe the most common ways to install the software you need.

You can find the ready-to-use MySQL programs (known as binaries) on the MySQL AB web site and on Linux installation CDs and web sites. You can also download the source code for MySQL from the MySQL AB web site and prepare, or compile, the executable programs yourself. By doing the compiling yourself, you ensure that you have the most up-to-date version of the software, and you can optimize the compiler output for your particular needs. The MySQL manual says that you can get a performance increase of up to 30 percent if you compile the code with the ideal settings for your environment. However, rolling your own installation from source code can also be a tedious and error-prone process, so we suggest that you stick with the ready-made binaries unless you’re experienced and really need to squeeze every ounce of performance from your server. Compiling from source under Windows and Mac OS X is even more involved, so it’s uncommon, and we don’t discuss it in this book.

You can also install MySQL as part of an integrated package that includes the Apache, PHP, and Perl software that you’ll need later in this book. Using an integrated package allows you to follow a step-by-step installation wizard. It’s easier than integrating standalone packages, and many of the integrated packages include other tools that help you adjust configuration files, work with MySQL, or conveniently start and stop services. Unfortunately, many of the integrated packages are a couple of minor releases behind the current version and may not include all the PHP libraries that you require. Another disadvantage is that an integrated package doesn’t always fit in with your current setup; for example, even if you already have a MySQL installation, you’ll get another one as part of the integrated package, and you’ll have to take care to avoid clashes. Despite the disadvantages, we recommend you follow this approach. There are several integrated packages available; we feel that XAMPP is probably the best produced of these, and we’ll describe how to install and use this. XAMPP includes MySQL, the Apache web server with PHP and Perl support, and other useful software such as phpMyAdmin. We recommend that you start out by using XAMPP, and we won’t spend time describing how to separately install and configure Apache, PHP, and Perl on your system.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

The three operating systems we use in this book all have graphical user interfaces; you can start programs by clicking on icons, you can select tasks from menus, and you can drag and drop files and folders. However, once you start to use more powerful aspects of the operating system and applications, you’ll quickly find that some tasks are more easily done by typing in commands. For example, you can tell the operating system to list certain files in a folder or run a given program in a particular way.

Linux, Windows, and Mac OS X all have a -line interface that allows you to do this. In Linux and Mac OS X, you use a Terminal program to show you the command-line interface, which is called the shell. In Windows, you use the Command Prompt Window program to show you the Command Prompt, sometimes called the DOS prompt.

In this section, we’ll describe how each command-line interface works; you can skip the descriptions for the operating systems you don’t use.

To access the shell under Linux, open a terminal program, such as


                     konsole

,


                     rxvt

, or


                     xterm

; these are often listed in the main menu under the System or System Tools group, and may be simply labeled Terminal. To access the shell under Mac OS X, open a terminal window by double-clicking on the Terminal icon in the Utilities folder under the Applications group.

Under Linux, you’ll see a prompt similar to this one:

while under Mac OS X, you’ll see something like this:

This shell prompt indicates what user account you’re logged in under, what computer you’re logged in to, and what directory you’re working from. You’ll generally be first logged in as an ordinary user (we’ve shown the user adam here) on the computer (eden

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

As you read through this book, you’ll frequently find references to using a text editor. This means a program that can edit and save files that contain only plain text. Word processors save additional formatting instructions that only other word processors understand. Word processing programs also tend to use proportional fonts, which makes it hard to read and write files of scripts and commands. It is possible to use a word processor to load and save plain-text files, but it’s rather inconvenient and error-prone, and so we don’t recommend you do this.

So, what should you use? There are hundreds of text editors available, and most people find one they prefer to use. You should try out several different programs and settle on one that you’re comfortable with. Let’s look at some options:

Linux: Under Linux, popular text editors include pico, gvim, vim, emacs, joe, kate, gedit, and xedit. You can often find these listed under the Editors group in the main menu of most Linux distributions. If you’re curious, you can also type the command apropos "text editor" at the shell to see a list of programs that have the phrase "text editor" in their description.
Windows: Under Windows, use Notepad; you can also download and install free text editors such as gvim, or commercial editors such as EditPad and TextPad.
Mac OS X: Under Mac OS X, you can use the included editors pico, vim, or emacs, configure the TextEdit program to behave as a text editor, or install and use other editors such as BBEdit or Smultron.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

Starting in the next section, we’ll explain how to configure a MySQL server on the same system that you’re logged in to (that is, localhost). We won’t describe how to set up the MySQL server on one computer and the web server on a different computer; it shouldn’t be too hard to modify our instructions to do this. If you modify any of the default settings, you’ll need to remember to specify them where necessary.

We also assume that if you’re using Windows, you use only the C: disk; we’ll explain how and when to change your working directory. When we show only the Linux or Mac OS X prompt as below:

or the Windows Command Prompt as:

the working disk and directory are unimportant, or you will be in the appropriate location after following the steps we describe.

When we use the hash or pound symbol (#) as the prompt:

you will need to type in the commands as the superuser. For a Linux or Mac OS X system, this means you should log in as the system superuser by typing su -, or use the sudo keyword before the command. For a Windows system, you must be logged in with a system account that has administrator privileges.

Most of our command-line examples outside this chapter are written in a form suitable for Linux and Mac OS X; to run these instructions under Windows, simply replace the forward slash character (/) with the backslash character (\). For example, you may see an example starting the MySQL monitor program (mysql) from the bin subdirectory as follows:

                   
                  bin/mysql

On Windows, you’d type bin\mysql at the Windows Command Prompt. After this chapter, we’ll mostly omit the path to programs and assume that you’ll call them using the appropriate path described for your installation in this chapter.

The behavior of many of the programs that we describe in this book can be modified through options. For example, you can use the

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

We’ll now describe in detail the steps you need to follow to get MySQL up and running on Linux, Windows, and Mac OS X systems. We’ll also describe how to start, stop, and configure your MySQL server.

If you install MySQL using the packages provided by MySQL AB, you still need Apache, PHP, and Perl for the later chapters in this book. You can instead follow the instructions to install the XAMPP integrated package to get everything you need. For Linux, you can also use packages provided by your distribution.

The MySQL AB web site usually has the very latest versions of the MySQL software. To download from this web site, follow these steps:

Visit the MySQL AB downloads page at http://dev.mysql.com/downloads. shows what this page looks like.
Select the MySQL version that you want. You’ll normally want the latest Generally Available (GA) release; this is 5.0.24 at the time of writing. However, you can also download the cutting-edge beta version to try out new features or to help identify problems before the new version becomes the general release.
You’ll see a long list of packages for the MySQL version you selected; shows part of this downloads page. Select the appropriate package to download for your system. In the following sections, we’ll tell you what this is for each operating system and installation approach.
Before the file download starts, you’ll probably be asked to to pick a mirror server near you. Mirrors are servers that have identical copies of files for download, and are used to share the burden of many people downloading the packages. The MySQL site uses an IP-to-location database to guess where you are and will suggest some nearby servers you can download from. Selecting a mirror will start the file download.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

There are five main ways to get MySQL up and running on a Linux system. You can:

Install a system-wide server from packages downloaded from the MySQL AB web site. Using packages supplied by MySQL AB means that the MySQL-related files are located together in a consistent way.

MySQL AB provides these packages in the RPM format: a collection of files that can be processed and installed by the rpm program. The name is a vestige of the program’s origins as the Red Hat Package Manager. However, many Linux distributions other than Red Hat use RPMs for managing software installation; these include Fedora, Mandriva/Mandrake, and SUSE. The MySQL AB company also provides files for download in the format used by Debian-based distributions but recommends that the apt-get method be used instead; we describe the recommended approach in this chapter.
Install a system-wide or local server using using a compressed directory (known as a gzipped tar archive) from the MySQL AB web site. This directory has all the necessary MySQL files ready to run in place; you don’t need to run an installer program or place the files in a particular location on disk.
Install a system-wide or local server by downloading the MySQL source code from the MySQL AB web site and compiling the executable programs yourself. This is the most time-consuming way of setting up Linux, but is the most flexible for power users.
Install a system-wide server using packages created by your Linux distribution; you can download these from the Web or install them from your Linux CDs.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

The MySQL installation process for Windows uses graphical installation programs and is relatively straightforward. You need to first decide whether you want to install only MySQL, or whether you’d like to install an integrated package including additional software that you’re likely to need later. Both approaches are equally easy to follow. At various points during the installation process, you may be prompted to allow the installer program to run and modify your system, including unblocking server ports. Read these prompts carefully; in most cases, you’ll want to allow the installer to do what it needs to do. Remember to follow the instructions of ,” earlier in this chapter, to verify that you’re running the correct installer program. You need to unblock ports only if you want to allow connections to your server from other hosts.

In this section, we’ll look at three ways to install MySQL on a Windows system:

System-wide, using a graphical installation package provided by MySQL AB
Local, using a "no-install" package by MySQL AB
System-wide, using the XAMPP integrated package

To install system-wide, you’ll need to log in as a user with Windows administrator privileges. The MySQL AB "no-install" package does not need to be installed using a setup program and is handy for cases where you don’t have administrator privileges on the computer.

First, follow the instructions of ,” earlier in this chapter, and download the package you need. If you are using Windows XP and have administrator privileges, it’s easiest if you download the "Windows Essentials (x86)" package. This is small and has all the MySQL programs you need. If you don’t have administrator privileges on your Windows machine, or you need to have a complex server setup with nonstandard configuration, you should download the package labeled

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

In this section, we’ll look at three ways to install MySQL on a Mac OS X system:

System-wide, using an installation package provided by MySQL AB.
Local, using an noninstallation gzipped tar package provided by MySQL AB.
System-wide, using the XAMPP integrated package.

To install system-wide, you should be able to access superuser privileges through the sudo command.

Following the instructions of ,” earlier in this chapter, visit the MySQL AB downloads page and choose the package corresponding to the version of your operating system and your system processor.

Pick the Standard installer (rather than TAR) package. This is a small package that has everything you need. Once the file is downloaded, double-click on it to unpack the archive and view the package contents. You should see something similar to .

Figure : The contents of the MySQL AB Mac OS X installer package

Double-click on the package file with a name beginning with mysql-standard- to start the installation process.

Simply following the prompts will install to the /usr/local/mysql-<version> directory, where <version> is the MySQL version number. It also creates the symbolic link (or alias) /usr/local/mysql that points to this installation directory. For example, the files could be installed in the /usr/local/mysql-5.0.22 directory, and the /usr/local/mysql link set to point to this directory.

Next, double-click on the MySQL.prefPane item and install it. This adds a MySQL configuration entry to the System Preferences; from the System Preferences window, you can manually start and stop the MySQL server, and also select whether you want the server to be automatically started each time the system boots.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

Most individuals and small- to medium-sized organizations don’t have the time or resources to maintain a production web server that’s available around the clock. Fortunately, there are countless Internet Service Providers (ISPs) that provide—usually for a fee—access to servers they maintain.

Since you’re reading this book, we can assume you’re interested in servers that can host dynamic web pages (for example, using PHP or Perl scripts) and provide a backend MySQL database that can be accessed by the web application. It’s not hard to find an ISP that provides this; a web search for "php mysql hosting" turns up several million sites.

When selecting a hosting package, see whether you are given ssh or telnet access to the server to run the MySQL client, or whether you can use only web clients such as phpMyAdmin; using web clients is easy, but you could soon find them tedious to use over extended periods of time. On a different note, don’t forget to also check how much data transfer is included when comparing costs of alternative web hosting deals. If your site becomes popular, it could end up costing you a lot of money!

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

If you’ve got a MySQL server that’s running well and without problems, you may wonder whether you it’s necessary to upgrade it to the latest version. There are three main reasons to upgrade:

Fixes for bugs: No complex software such as MySQL can be free of bugs; over time, people discover unexpected behavior, or possible data corruption. As these problems come to light, they are fixed for the latest version. MySQL bugs are reported and analyzed at the http://bugs.mysql.com web site. You can use this web site to view the bug reports for your MySQL version and determine whether any are likely to affect your operations.
Fixes for security vulnerabilities: Security vulnerabilities are an especially dangerous class of bug; by exploiting a vulnerability, an attacker could gain unauthorized access to data, or render your system unusable (cause a denial of service). If your server is connected to a network or otherwise accessible to people other than yourself, you need to take security issues very seriously.
Improved features: As software matures, new features are added to make some tasks easier or to improve efficiency. For example, MySQL 5.0 introduced support for views (virtual tables), stored procedures (predefined queries that clients can call), cursors (pointers to the result of database operations), and triggers (predefined operations that are carried out automatically before or after a row is inserted, deleted, or updated). Similarly, subqueries (nested SELECT queries) were not possible in MySQL before version 4.1; neither were multiple concurrent character sets.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

By default, there is no password set for the MySQL server. You must set a root password as soon as possible. The MySQL AB Windows installer automatically prompts you to set one as part of the configuration process. For other cases, make sure you follow our installation instructions to set a root password.

A MySQL client connects to a server differently depending on where the server is running. When the client and server are on the same Linux or Mac OS X system, a local connection is made through a Unix socket file, typically /tmp/mysql.sock or /var/lib/mysql/mysql.sock. On a Windows system, the connection is made through the MYSQL named pipe if the server was started with the


                  
                  enable-named-pipe

option. In other cases, clients send their requests through a TCP/IP network connection. Using a named pipe can actually be slower than using TCP/IP.

If you intend for your server to be accessed only from the host it is running on, you can disable network access to the server by starting the server with the


                  
                  skip-networking

option. For a server running on Windows, remember to enable the


                  
                  enable-named-pipe

option at the same time; otherwise you won’t be able to connect to the server.

If you carry out the steps outlined in this chapter, the filesystem access permissions for the MySQL data directory and the server logs should be configured correctly. Keep in mind that users need access to the socket file to connect to the server; if the socket file is in the data directory (sometimes the case when using Linux distribution RPMs), take care that users can’t access other files in that directory. We discussed permission settings in ,” at the beginning of this chapter. Of course, securing the database server is only a small part of overall system security.

If you’re running Linux or Mac OS X, you can use the

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

Hopefully, you’ll have managed to get the server up and running without problems. Sadly, things don’t always work perfectly. Here’s how to get around some of the more common problems.

If you have to use a proxy to connect to the Web, you’ll need to ask web clients to use them. Web browsers typically allow you to configure proxies under the program connection preferences. For the Linux


                     wget

,

yum

, and


                     apt-get

programs, you can declare the HTTP and FTP proxy settings as shown below:

                      
                     export http_proxy=http://
                     
                        proxy_username:password@server_name:port
                     
                      
                     export  ftp_proxy=http://
                     
                        proxy_username:password@server_name:port

For example, you might type:

                      
                     export http_proxy=http://
                     
                        adam:password@proxy.mycompany.com:8080
                     
                      
                     export  ftp_proxy=http://
                     
                        adam:password@proxy.mycompany.com:8080

Your Internet service provider or company network administrator can provide the proxy settings that you should use. If for some reason the rpm command does not work through the proxy, you can download the file yourself using a browser or with wget. You can then install this downloaded file manually using the

rpm
      --upgrade

or

dpkg
      --install

commands.

To use MySQL, you need to run MySQL executable programs, such as the server programs


                     mysqld_safe

and


                     mysqld-nt.exe

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

A MySQL installation has several key files and directories, and several optional ones. In this section, we’ll briefly cover the contents of the MySQL directory when you’ve downloaded and installed MySQL using a MySQL AB package.

First, there are some text files covering the licensing conditions and the installation process. It’s a good idea to have a quick read through these:

COPYING
README
EXCEPTIONS-CLIENT
INSTALL-BINARY

The directory also contains the configure script to configure and start a freshly installed server; you shouldn’t need to use this if you’ve followed the instructions in this chapter.

Then there are several subdirectories; the important ones are:

bin/: Contains the executable programs—binaries—such as mysqld_safe and mysqladmin. Compiled programs contain binary (0 and 1) code, rather than human-readable text, hence the name of this directory. However, you’ll probably find some human-readable script files in this directory too.
data/: Contains a subdirectory holding the data and index files for each database on the server. A newly installed and configured MySQL server comes with the

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

For all chapters up through , you will need access to only a MySQL server. To practice the examples in Chapters , , and , you’ll need an Apache web server with support for the PHP language. In , you’ll learn how to run Perl scripts on a Apache web server.

If you haven’t installed Apache using XAMPP, you should check whether you have Apache installed and, if so, whether it supports PHP. You should also check whether your PHP engine supports your installation of MySQL.

If you’ve used the XAMPP package, you can relax, knowing that this has been done for you. You also know how to start and stop Apache using the /opt/lampp/lampp script (Linux), the XAMPP control panel (Windows), or the /Applications/xampp/xamppfiles/mampp script (Mac OS X). If you’re using Linux and aren’t using XAMPP, you’ll need to ensure that your web server can work with your database server.

Apache is installed as part of the standard Mac OS X configuration, where it’s referred to as Personal Web Sharing. You can configure it from the Sharing section of the System Preferences window. However, we’ll rely on the XAMPP installation in this book, so go to the Sharing settings and ensure that Personal Web Sharing is switched off.

In this section, we look at how to check that your web server is running, and how to find the directory from which it serves files to your browser. We also explain where to find the Apache configuration file and error log. Finally, we describe how you can control the Apache web server on a Linux system where you haven’t used XAMPP, and how to check that your web server is correctly configured for the work that you’ll do in this book.

You can test whether a web server is running on your machine by opening a browser (for example, Firefox, Internet Explorer, or Safari), and typing in the address http://localhost. If your browser reports that it can’t open this page, you can try to start the server by using the appropriate XAMPP startup command, or the

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

Chapters , , and require that you have a working installation of Perl. Perl is available as standard on almost all Linux and Mac OS X systems, and it is included in the XAMPP integrated package, so you don’t need to install it separately. For Chapters and , you’ll need two Perl extension packages or modules. We’ll use the Perl DBI (Database Interface) module in to talk to a MySQL server, and the Perl CGI (Common Gateway Interface) module in to write clean and readable scripts that can be run by a web server. If you’re not planning to write nontrivial Perl scripts for a web application, you can manage without the CGI module, but you’ll definitely need the DBI module to use Perl for interaction with MySQL.

To run Perl scripts, you need to know where the Perl interpreter (called perl) is installed on your system. For Linux, we’ll use the instance of Perl that comes with the distribution; to find where this is located, use the which command:

                      
                     which perl
/usr/bin/perl

In this example, the Perl interpreter is the file /usr/bin/perl.

For Windows and Mac OS X systems, we’ll use the instance of Perl that comes with XAMPP. On a Windows system, the XAMPP Perl interpreter is C:\Program Files\ on a Mac OS X system, the XAMPP Perl interpreter is /Applications/xampp/xamppfiles/bin/perl. You can also use the Mac OS X system default installation (/usr/bin/perl), but as we discuss later in ,” we recommend you stick with the XAMPP installation for consistency.

Let’s start by examining what the version of this Perl installation is. On a Linux system, type:

                      
                     perl --version

On a Windows or Mac OS X system, the XAMPP Perl interpreter is not in the system path, so you should specify the full path on a Windows system as:

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

You can find a detailed reference manual on MySQL and several sample databases on the MySQL AB web site at http://dev.mysql.com/doc, although we recommend you explore these after you’ve finished reading this book.

You can also participate in MySQL-related discussion forums and mailing lists. Some of these are run by MySQL AB. To learn more, visit the MySQL AB forums page at http://forums.mysql.com and the lists page at http://lists.mysql.com.

There’s also a lot of helpful material on the MySQL community web site (http://forge.mysql.com). In particular, look at the collection of (mostly user-contributed) documentation by following the "Wiki" link near the top of the page. Don’t worry if it all seems overwhelming at first; you’ll be able to make sense of most of it by the time you reach the end of this book!

To learn more about installing the software described in this book, we recommend the following resources:

For more on the Windows XP command prompt, visit the Microsoft XP command-line reference at http://www.microsoft.com/resources/documentation/windows/xp/all/proddocs/en-us/ntcmds_o.mspx. Much of this information applies to Vista too.
A useful list of frequently-asked questions about XAMPP, including discussion of common installation problems is available from the XAMPP web site (http://www.apachefriends.org/en).
For detailed information on setting up and configuring the Apache web server, including a list of all the configuration directives, visit http://httpd.apache.org.

To learn more about shell or command-prompt instructions, do a web search for "learn unix Linux" (for Linux), "learn unix mac os x" (for Mac OS X), or "Windows command prompt" (for Windows).

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

What is command completion?
What are the relative advantages of installing MySQL using the package, directory archive (tarball or “no-install”), or compiled methods?
How do you verify the integrity of downloaded packages?
How do you add the MySQL bin directory to the operating system path?

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

MySQL has a client-server architecture; clients connect to the server to perform database operations such as reading or storing data. There are many MySQL clients available, including some that have graphical interfaces. You can also develop your own clients. The standard MySQL command-line client or " monitor" program provided by MySQL AB is the client you’ll probably use the most often. The monitor allows you to control almost all aspects of database creation and maintenance using SQL and the custom MySQL extensions to SQL.

In this chapter, we’ll examine how to start the monitor and how to run commands through the monitor either interactively or in batch mode. We’ll describe how you can access the inbuilt MySQL help functions, and how to test your MySQL setup using the sample databases from the book web site. We’ll also take a quick look at a couple of graphical tools that you can use instead of the monitor.

The monitor program is called simply mysql and is found in a directory with the other MySQL programs. The exact location depends on your operating system and how you chose to install MySQL; we considered some of these in ,” in Chapter 2.

If your MySQL server isn’t already running, start it using the appropriate procedure for your setup as discussed in . Now, follow these steps to start the monitor and connect to your MySQL server as the MySQL administrator (the MySQL root user) by typing this from the command line:

                   
                  mysql --user=root

If you followed our instructions in , the MySQL root account is protected by the password you chose earlier, and so you’ll get a message saying that you’ve been denied access to the server. If your server has a password, you should specify the password as follows:

                   
                  mysql --user=root --password=
                  
                     the_mysql_root_password

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

The monitor program is called simply mysql and is found in a directory with the other MySQL programs. The exact location depends on your operating system and how you chose to install MySQL; we considered some of these in ,” in Chapter 2.

If your MySQL server isn’t already running, start it using the appropriate procedure for your setup as discussed in . Now, follow these steps to start the monitor and connect to your MySQL server as the MySQL administrator (the MySQL root user) by typing this from the command line:

                   
                  mysql --user=root

If you followed our instructions in , the MySQL root account is protected by the password you chose earlier, and so you’ll get a message saying that you’ve been denied access to the server. If your server has a password, you should specify the password as follows:

                   
                  mysql --user=root --password=
                  
                     the_mysql_root_password

If you get a message from the operating system saying that it can’t find the MySQL program, you’ll need to specify the full path to the mysql executable file as discussed in .”

If you used a nonstandard socket file when starting your server, you’ll need to provide the details to any MySQL client programs you run, including mysql. For example, you might type:

                   
                  mysql \
 --socket=server_socket \
 --user=root \
 --password=
                  
                     the_mysql_root_password

If you’re trying to connect to a MySQL server on a different computer or a nonstandard port, you should specify these when starting the monitor:

$mysql \
 --host=server_host_name \
 --port=server_port \
 --user=root \
 --password=
                  
                     the_mysql_root_password

We list a few more options to the monitor program at the end of this chapter.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

When interacting with a MySQL server, you’ll use a combination of SQL keywords, MySQL proprietary commands, and names of databases and database components. We follow common convention and use a style to make it easier to distinguish between components of an SQL query. We always show SQL statements and keywords in capitals, such as SELECT or FROM. We also show the MySQL monitor’s proprietary SQL commands—such as USE—in uppercase. We always enter database components—such as database, table, and column names—in lowercase. This makes our SQL more readable and easier to foll

Table of Contents