Learning MySQL (2007)
Part I. Introduction
Chapter 2. Installing MySQL
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.
Installation Choices and Platforms
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 to work together on your system.
The software packages you need—MySQL, Apache, PHP, and Perl—are available ready to install on many operating systems and can be compiled to run on a large number of others. However, chances are that you’re running one of three major operating systems: Linux, Windows, or Mac OS X, so we’ll provide detailed instructions for only these three. Let’s see how they compare as MySQL development and production platforms.
Linux
Linux is an open source operating system that is closely modeled on Unix, which is why it’s often called a Unix clone. Even though it’s free, Linux is very powerful and very secure, with versions available for a wide range of hardware.
You typically get Linux in the form of a distribution, such as Red Hat or Mandriva. A distribution packages the operating system together with a large range of useful software for things such as word processing, networking, web and database development, and even games. These distributions are free to download and distribute; you can also buy low-cost CD copies or more expensive shrink-wrapped packs with printed manuals. Many of the most popular web sites run on Linux, and it’s an excellent choice for learning MySQL.
Live CDs
You can install Linux on its own, or alongside Windows on a single computer (this is known as dual-booting). If you want to try out Linux without installing it on your computer, you can use a bootable, or live, CD distribution. This allows you to boot your computer from a CD to get a fully-working Linux system without making any changes to your hard disk. When you remove a live CD and reboot, everything is back to what you had before; you don’t have to worry about doing any damage while you learn how to use Linux. For example, the Knoppix (http://www.knoppix.org) live CD includes all the software—MySQL, the Apache web server, PHP, and Perl—that you need for this book. However, we recommend that you use a live CD only to become familiar with Linux. While it’s possible to save files from a live CD onto the hard disk, a USB flash disk, or another computer through a network connection, this is tedious. For anything that involves using Linux for extended periods of time, you’re better off with a full installation to hard disk.
Windows
Microsoft Windows is by far the most common commercial PC operating system today, and new PCs often come with Windows pre-installed. Windows XP, released in 2001, is available on most current PCs. Windows Vista is the latest version of Windows; at the time of writing, it’s in“release candidate” (for testing) form and due to be published in the next few months.
We’ve tested the instructions in this book using both XP and Vista. While we wouldn’t recommend using either version for a production server, they’re quite appropriate for learning the material in this book. We assume you’re using either XP or Vista; you can set up a suitable environment on older versions of Windows such as 98 and Me, but the process is less straightforward. When we say “Windows” in this book, we mean XP or Vista.
Mac OS X
All new Apple computers since 2001 have come with OS X; recent versions include 10.3 (Panther) and 10.4 (Tiger), with 10.5 (Leopard) due for release in the next few months. OS X has a nice graphical user interface over a Unix-like heart, which means it’s not hard to use software originally designed for Unix or Linux. Most new Apple computers built from 2006 onward have an x86-type processor; older systems have a PowerPC processor. You can easily check which operating system version or processor your system has by clicking on the Apple menu and choosing the About This Mac entry. It’s not common to find a production MySQL server running on OS X, but it’s a good environment for learning MySQL.
So, What Should I Do?
As we mentioned earlier, you can use almost any major operating system when practising the material covered in this book, but to keep things sensible, we’ll assume you’re using one of the big three just listed. Where the process varies between operating systems, we’ll clearly explain the necessary steps. It shouldn’t be too hard to interpret the instructions for other operating systems that we don’t focus on in this book. For example, many of the Linux instructions can be used with little adaptation on Solaris or FreeBSD.
To install a MySQL server with the standard directories and settings for a system-wide installation, you’ll generally need superuser (also known as the system root user or administrator) privileges on your system. Always be careful when using superuser access. The superuser can do anything on a system, so you might be tempted to always log in under the superuser account. However, “anything” means anything—including accidentally deleting vital system files and making the system unusable. There are also security risks associated with using this level of access by default, so we strongly suggest you stick to an ordinary, or nonprivileged, user account and switch to the privileged account only when necessary. We’ll explain how to configure a MySQL server installed on a Linux or Mac OS X system to run as a less privileged user; any files and directories that the server creates are then owned by this account.
If you don’t have superuser access—for example, you’re using a shared university computer or want to experiment without touching the system-wide MySQL installation—you can generally install a local MySQL server using nonstandard settings; we’ll also explain how you can do this. However, we recommend that you go with the default settings if you can, at least while you’re still learning a lot about MySQL. You’re far less likely to make mistakes, and less likely to run into difficulties with the software; programs are rarely tested as well on nonstandard configurations as they are on the default settings.
Finally, there are cases when the database server may already be set up for you. Many hosting companies, for example, allow you to administer your databases using only a web-based MySQL client such as phpMyAdmin. We’ll take a brief look at phpMyAdmin in Chapter 13.
Using the Command-Line Interface
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 command-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.
The Linux and Mac OS X Shell
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:
[adam@eden ~]$
while under Mac OS X, you’ll see something like this:
eden:~ adam$
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), and working from your home directory. The tilde (~) character is a shortcut symbol to a user’s home directory on any Unix-like system, including Linux and Mac OS X; for example, a user’s home directory could be /home/adam, but you can refer to it as ~adam, or, if you’re logged in as adam, simply as ~. The sample prompt shows that the user adam is logged in to the computer eden and working from his home directory. To keep things simple, we’ll just show a dollar sign to indicate the Linux or Mac OS X shell prompt, as below:
$
From the shell, you can run many useful commands; we’ll see some as we progress through this book. Two standard commands that are important to know for this book are:
cd
Changes your working folder or directory on disk. For example, you can change to the /tmp directory by typing:
$ cd /tmp
You can also change to your home directory by using the tilde shortcut:
$ cd ~
In fact, you can leave out the tilde: cd on its own means “change to my home directory.”
ls
Lists the files and directories in your working folder. For example, you can list the files in your home directory by typing:
$ ls ~
Together, the cd and ls commands are the text equivalent of using a graphical file manager—such as Konqueror or Nautilus under Linux, or the Finder under Mac OS X—to go to different directories and view their contents.
Command completion and history
Command completion is a great time-saving feature; when you start to type the name of a command, file, or directory, pressing the Tab key cycles through names that could match. The best way to understand this is to try it. For example, when you type:
$ cd /t
and then repeatedly press the Tab key, you’ll see items beginning with the letter “t” in the / (filesystem root) directory. If a name has spaces, a backslash character is added automatically before each space—for example My\ Important\ Notes.txt. Most Linux and Mac OS X systems are configured to use the bash shell, and we assume you’re using this, too. If you’re using a different shell variant, such as tcsh, you’ll need to press the Ctrl-D key combination in place of the Tab key.
Pressing the up and down arrow keys will cycle through the last commands you typed; you can use the arrow keys to edit a previous command, and you can press the Enter key to run a displayed command. You can see a list of recently used commands with the history command, as below:
$ history
1 cd Photos/
2 lt
3 find . -name "*AMES*"
4 cfdisk /dev/hda
5 ssh ubuntu@192.168.1.1
You can quickly run a command again by typing the number preceded by an exclamation mark (!) character. For example, to run the command numbered 3 in the history list, you can type !3 and press the Enter key.
Performing restricted operations
Certain restricted operations on a Linux or Mac OS X system are allowed only if you have superuser, or root, privileges. On a Linux system, you can log in as the system root user by typing the su - (switch user) command. When prompted, type in the system root user’s password and press the Enter key:
[adam@eden ~]$ su -
Password: the_system_root_password
[root@eden ~]#
This is almost identical to the case for Mac OS X:
eden:~ adam$ su -
Password: the_system_root_password
eden:~ root#
After you type in the password, you’ll be logged in as the user root on the same computer (in this example, eden) and be working from that user’s home directory (also indicated by a tilde).
Notice how the last character of the prompt is a dollar sign ($) when you’re not the root user and the hash or pound (#) sign when you are. In this book, we’ll use these symbols to indicate whether you should run a certain command as an ordinary user or as the root user. When you’ve finished doing the restricted operations, you can log out from the system root account by typing exit:
# exit
$
You can generally use the sudo command to perform actions with system superuser privileges, even though you’re not actually logged in as root. You can also use the sudo -s command to log in as the root user (in place of su -). If you log in as the system root user, you can then omit thesudo keyword. Again, we emphasize that you can inadvertently do a great deal of damage if you use the root account, and we recommend that you log in as the system root user as infrequently as you can. Some configuration is necessary to allow ordinary Linux users to use the sudocommand, but it’s enabled by default under Mac OS X, and we’ll use this approach when discussing installation for this operating system.
You can add the ampersand symbol (&) at the end of a command to start the command in the background, allowing you to use the shell for other work. It’s better to avoid using this symbol in conjuction with the sudo command, since you won’t see any system prompt for you to enter your password. When we want you to run a sudo job in the background, we’ll ask you to start the job normally, then press the CTRL-Z key combination to suspend this new job. You can then type the command bg to send the suspended job to the background.
Restricting access to files and directories
Before we end our discussion of the Linux and Mac OS X shell, let’s look at how access to files and directories is controlled under such Unix-like operating systems. Each file or directory can have read, write, and execute permissions set for the user who owns it, the group associated with it, and every other user.
When the operating system is asked to allow access to a file or directory, it looks to see who the user is and what groups this user belongs to. It then checks the user and the group associated with that file or directory, and allows access only if the permission settings are appropriate.
Your group on a Linux or Mac OS X system is typically the same as your username, so, for example, the username and group for the user adam would both be adam. The user and group associated with a file or directory can be changed by using the chown command and specifying the username and group as username:group. For example, you can set the owner of myfile.txt to be adam, and the associated group to be managers, by typing:
# chown adam:managers myfile.txt
Only the superuser is allowed to change the owner of a file or directory.
You can allocate permissions to a file or directory by using the chmod command. To allow the user who owns the file myfile.txt to read and write (modify) it but allow other users to only read it, you would write:
$ chmod u=rw,g=r,o=r myfile.txt
You can also ensure that only the user who owns the file can read and write to the file as follows:
$ chmod u=rw,g=,o= myfile.txt
Here, the group and other users have been assigned no permissions. Similarly, you can give everyone read, write, and execute permissions to the directory mydir by typing the command:
$ chmod u=rwx,g=rwx,o=rwx mydir
When reading other documentation, you’ll probably also come across cases where an octal value (or mask) is used with the chmod command. In this notation, read access has the value 4, write access has the value 2, and execute access has the value 1. So, read-only access has the value 4, but read-and-write access has the value 4+2=6. Our previous two examples would be written as:
$ chmod 644 myfile.txt
and:
$ chmod 777 mydir
The chown or chmod operation can be applied to all files and directories under a specified directory by using the --recursive option (under Linux) or the -R option (under Mac OS X as well as Linux). We’ll see examples of this later in this chapter.
The Windows Command Prompt
Under Windows, you can open a command-prompt window by clicking on the Command Prompt entry under the Accessories submenu. You can also type cmd in the Start menu search box (Vista) or in the Start menu “Run...” field (XP).
The command prompt typically shows you the current working disk and directory:
C:\Documents and Settings\Adam>
In this example, the current working directory is the home directory \Documents and Settings\Adam on the C: disk. Under Vista, the location of the home directory is slightly different:
C:\Users\Adam>
From the command prompt, you can run many useful commands; we’ll see some as we progress through this book. Two standard commands that are important to know for this book are:
cd
Changes your working folder or directory on disk.
dir
Lists the files and directories in your working folder.
Together, the cd and dir commands are the text equivalent of using a graphical file manager such as Windows Explorer to go to different directories and view their contents.
Windows uses the variable %HOMEPATH% to refer to your home directory, so you can always change to your home directory by typing:
C:\> cd %HOMEPATH%
C:\Documents and Settings\Adam>
Command completion and history
Command completion is a feature that can save you a lot of typing. When you start to type the name of a command, file, or directory, pressing the completion key sequence cycles through matches. The completion key varies between systems; it is generally the Tab key or the Ctrl-D or Ctrl-F key combination.
Under Windows, you can activate the command-completion feature if you start the command prompt with the /f:on option (command completion is active by default in Vista). If the /f:on switch doesn’t work on your system, try calling the cmd program without the switch. You can also configure Windows XP to have command completion active by default, but we won’t describe how to do this here.
The best way to understand command completion is to try it out. For example, when you type cd c:\p:
C:\> cd c:\p
and then repeatedly press the completion key sequence, you’ll see items beginning with the letter “p” in the C:\ directory. Note that Windows doesn’t mind whether you use uppercase or lowercase when referring to files and folders.
Quotes are added automatically around names with spaces—for example, "C:\Program Files". To continue expansion, press the backspace key to delete the last quote and type a further hint. For example, to switch to the C:\Program Files\MySQL directory, you’d delete the quote, type a backslash (\), and then press the completion key sequence again.
Pressing the up and down arrow keys will cycle through the command history. You can see a list of recently used commands with the doskey/history command, as below:
C:\> doskey/history
dir C:\
doskey/history
There are many more tweaks for the command prompt; just do a search on the Web for “windows cmd”.
You can also start other programs from the Start menu; under XP, you would use the Run menu item to browse to select the program you want. If you type in the command, you’ll also get command completion, as shown in Figure 2-1. Under Vista, simply type the name of the program in the Start menu search box. However, this approach doesn’t always keep the results of running a program on the screen, so we suggest you use the command-prompt window.
Figure 2-1. Starting a program from the Run menu item
Using a Text Editor
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.
To start an editor from the command line, type in the name of the program followed by the name of the file you wish to edit; for example, you can open the file myfile.txt with the pico editor by typing:
$ pico myfile.txt
You can also open files from the graphical user interface; double-clicking on the text-file icon will generally open the file in a text editor. You can modify the program that’s used to open text files by right-clicking on the text-file icon (in Windows, depress the Shift button while clicking) and work your way through the program options. We won’t go into detail here.
Under Mac OS X, you can also configure the TextEdit program to act as a text editor. Start the TextEdit program, and then choose the Preferences option from the TextEdit menu. In the dialog box that appears, select Plain Text under the Format heading. To open a file with TextEdit from the command line, you should type:
$ open -a TextEdit myfile.txt
You can instead select the plain-text mode for individual files one at a time by selecting the Make Plain Text option from the Format menu, but this approach is likely to be tedious and error-prone over time.
Following the Instructions in This Book
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:
C:\>
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 user and password options to specify the username and password you want to use. Options can be specified on the command line after the program name. Some programs can also read options from a file. We explain options files in Chapter 11.
When you list options on the command line, you identify them by two adjacent hyphens:
$ mysql --user=saleh --password=tomcat
Here, we’ve specified the username saleh and the password tomcat.
If specified in a configuration file, the leading dashes should be omitted. For example, you would write --user=saleh on the command line and user=saleh in an options file. We’ll generally omit the leading dashes in our descriptions.
Many options also have a short form that can be used only from the command line. For example, instead of writing --user=saleh on the command line, you can write the short form -u saleh. To help you understand what each command does, we consistently use the long form of each option (where one exists).
Most of the command-line utilities we describe in this book have a help option that you can use to discover the command syntax, including any short forms. For example, to learn about the options to use for the mysql program, type:
$ mysql --help
mysql Ver 14.12 Distrib 5.0.22, for pc-linux-gnu (i686) using readline 5.0
Copyright (C) 2002 MySQL AB
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL license
Usage: mysql [OPTIONS] [database]
-?, --help Display this help and exit.
...
-p, --password[=name]
Password to use when connecting to server. If password is
not given it's asked from the tty.
-u, --user=name User for login if not current user.
...
We’ve shown only part of the output here. You can see that you can use the short form -? instead of --help, -u in place of --user=, and -p in place of --password=. The brackets indicate that a clause is optional; for example, you can call the mysql program without any command-line options or database name.
Some options assume default values if you don’t specify anything. To avoid surprises, you can always explicitly specify the values you want.
When a command gets too long for the page, we show it on multiple lines, with each line ending with a backslash (\) symbol. For example, we might show the previous command as:
$ mysql \
--user=saleh \
--password=tomcat
The backslash characters indicate that this is a single command that should be typed in all on one line. You can actually type in the backslash on a Linux or Mac OS X system to continue your command on a new line, but it’s not necessary.
Downloading and Verifying Files from the MySQL AB Web Site
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.
Downloading MySQL from the MySQL AB Web Site
The MySQL AB web site usually has the very latest versions of the MySQL software. To download from this web site, follow these steps:
1. Visit the MySQL AB downloads page at http://dev.mysql.com/downloads. Figure 2-2 shows what this page looks like.
2. Select the MySQL version that you want. You’ll normally want the latest Generally Available (GA) release; this is 5.0.67 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.
3. You’ll see a long list of packages for the MySQL version you selected; Figure 2-3 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.
4. 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.
Figure 2-2. The MySQL AB downloads page
Figure 2-3. The Linux section of the MySQL downloads page
Verifying Package Integrity with MD5
When downloading files from the Internet, it’s a good idea to ensure that what you’ve got is what you wanted to get. For a production server, we recommend that you check the integrity of packages that you download. A simple way to do this is to compare checksums generated by a digest algorithm such as MD5.
A digest algorithm takes some data (for example, an RPM file) as input and calculates a 128-bit number, or checksum, from this data. With a good digest algorithm, it’s practically impossible to change the data without changing the checksum, so if the checksums of two files match, you can be certain that the files are identical.
On the MySQL download page, you’ll see a different string of characters such as:
MD5: 0d2a3b39e7bb4109b2f7b451b7768f34
next to each file. You should ensure that the checksum of the file you have downloaded matches the corresponding value on the downloads page.
On Linux, use the md5sum program on the downloaded file:
$ md5sum mysql-standard-5.0.22-linux-i686.tar.gz
0eaa7a8ec18699ce550db1713a27cda3 mysql-standard-5.0.22-linux-i686.tar.gz
TIP
The filename is shown in italic in this example because the name is likely to change, and you’ll have to type in the actual name of the file you download.
On Windows, you can download and use the free winMd5Sum program from http://www.nullriver.com/winmd5sum. This program is very easy to use; just install and start the program, press the “...” button to browse for and select the downloaded file, and then read off the checksum value.Figure 2-4 shows what this program’s dialog box looks like.
Figure 2-4. Using winMd5Sum to verify the MD5 checksum of a downloaded file
On Mac OS X, open a terminal window and use the md5 program:
$ md5 mysql-standard-5.0.22-osx10.4-i686.dmg
MD5
(mysql-standard-5.0.22-osx10.4-i686.dmg) =
b7d7f0878503db504e1eaed5d2518f4e
Digitally signed packages offer a more secure way to ensure that files have not been tampered with; however, MD5 checksums should be sufficient for most readers of this book.
WARNING
Open source projects such as MySQL, Apache, PHP, and Perl produce constantly evolving software, with new versions appearing regularly. The installation files typically include the version number in the filename—for example, MySQL-server-<version>.i686.rpm. The versions of the software that you will use are almost certainly newer than the ones used in our examples, so you should substitute the appropriate version number when handling them. Of course, installation details change over time—things generally become easier—so expect some variation from the steps we discuss here. You’ll also probably find that the output we show for various programs will be slightly different from what you see on your own system.
Whenever you install software that can accept connections from other computers, you should take care to configure your computer firewall software to block connections from unauthorized systems. This is particularly important if your computer is easily accessible from the Internet, for example through your connection to your Internet Service Provider (ISP).
Installing Under Linux
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.
§ Install a system-wide server by downloading the XAMPP integrated package. Note that XAMPP is not designed for use as a local server, and significant effort is required to get around this limitation.
We’ll describe each of these approaches in detail. If you’re not sure which approach is most suitable for you, we recommend you first try to use the packages provided by your Linux distribution.
Installing MySQL on Linux Using RPM Packages from MySQL AB
First, go to the MySQL AB downloads page following the instructions in the Downloading MySQL from the MySQL AB Web Site” section, and scroll down the list to the part of the page with the label “Linux x86 RPM downloads.” The x86 indicates the processor type; almost all PCs today use x86 processors. If you have a more advanced type, such as an AMD 64-bit processor, you should find the appropriate part of the downloads page.
Pick RPM packages for both the MySQL server and the client, taking care that you select the correct version for your Linux distribution and your processor. These will be called something like MySQL-server-5.0.22-0.i386.rpm and MySQL-client-5.0.22-0.i386.rpm. Packages with higher CPU numbers, such as i586 or i686, are better tuned for newer machines, but won’t work on older machines.
If you intend to do server benchmarking and testing, you may need to download the benchmark and test suites package (with a name like MySQL-bench-5.0.22-0.i386.rpm); however, you won’t need them for this book.
To install the RPM files, you’ll need to log in as the system root user. Open a terminal program and use the su - command to log in as the root user:
$ su -
#
Change to the directory containing the MySQL RPM files you downloaded. This is typically your home directory or your desktop directory. To change to the home directory of the user adam, you’d type:
# cd ~adam
The location of the desktop directory depends on the Linux distribution you use, but is commonly the Desktop directory under the home directory. To change to the desktop directory of the user adam, you’d type:
# cd ~adam/Desktop
You can then install the MySQL server and MySQL client RPMs (or upgrade any existing versions) by typing:
# rpm --upgrade --verbose --hash \
MySQL-server-5.0.22-0.i386.rpm MySQL-client-5.0.22-0.i386.rpm
If all goes well, your MySQL server should now be installed. We’ll look at how to configure it in Configuring a Newly Installed Server,” later in this chapter.
Installing MySQL on Linux Using a gzipped Tar Archive from MySQL AB
Instead of using an installable package, you can download a compressed directory of the MySQL executable and support files. This process is slightly more involved than installation from a package.
Follow the instructions of Downloading MySQL from the MySQL AB Web Site” and download the appropriate package from the “Linux (non RPM package) downloads” section of the MySQL AB downloads page. For this book, select the “standard” package, rather than the “Max” or“debug” versions.
If you’re unsure what to choose, try picking the Linux download at the top of the list. This will be named something like mysql-standard-5.0.22-linux-i686.tar.gz.
For distribution, Linux software is often packaged using the tar program, and then this package is compressed using the gzip program, so the final file often has the file extension .tar.gz or .tgz. A .tar file, or its gzipped version, is often referred to as a tarball. You’ll need to unpack, or untar,this package:
$ tar --gunzip --extract --file mysql-standard-5.0.22-linux-i686.tar.gz
The gunzip option asks the program to decompress the file first using the gunzip program. Some browsers automatically decompress files that have a .gz extension; if you get a message like “gzip: stdin: not in gzip format,” this has probably happened in your case, and you can omit thegunzip option:
$ tar --extract --file mysql-standard-5.0.22-linux-i686.tar.gz
You should now have the directory: mysql-standard-5.0.22-linux-i686. To keep things simple, we’ll call this the MySQL directory.
The MySQL directory is self-contained and has all the files you need to run and access the server. If you have superuser access on the Linux machine and want this MySQL server to be the system-wide instance on the machine, you should move it across to a the standard location under the/usr/local/ directory:
# mv mysql-standard-5.0.22-linux-i686 /usr/local/
and make a link /usr/local/mysql that points to this directory:
# ln --symbolic /usr/local/mysql-standard-5.0.22-linux-i686 /usr/local/mysql
Now you can simply refer to the MySQL directory as /usr/local/mysql. Using a symbolic link in this way allows you to have different versions of MySQL ready to run on the system, with /usr/local/mysql pointing to the directory containing the version you want to use.
If you want to have a local installation, you can leave the MySQL directory under your home directory. You’ll probably find it helpful to create the link ~/mysql to point to the actual MySQL directory—for example:
$ ln --symbolic ~/mysql-standard-5.0.22-linux-i686 ~/mysql
With this link, you can use ~/mysql wherever you want to refer to the ~/mysql-standard-5.0.22-linux-i686 directory.
Installing MySQL on Linux by Compiling the Source Code from MySQL AB
Given the nature of this book, we won’t go into detailed compile-time settings, but will just look at how you can quickly get the server up and running.
First, you need to download the source file package from the MySQL AB downloads page, following the directions in Downloading MySQL from the MySQL AB Web Site.” Go to the “Source downloads” section and download the “Tarball (tar.gz)” package.
After downloading, you should have a file with a name like mysql-5.0.22.tar.gz. Decompress this package using the following command:
$ tar --gunzip --extract --file mysql-5.0.22.tar.gz
This creates a new directory containing the MySQL source files; change your working directory to this by typing:
$ cd mysql-5.0.22
You must now compile the source code and install the resulting programs. After you’ve done this, you’ll have a MySQL directory that has all the files you need to run and access the server. This is very similar to the tarball approach. Unlike the tarball approach, however, you need to first use the configure command to tell the compilation process where you want the MySQL directory to be located.
If you have superuser privileges and want your MySQL installation to be system-wide, it’s best to install to a directory under the /usr/local directory—for example, /usr/local/mysql-5.0.22. On the other hand, if you want to run a local server, you can have the MySQL directory wherever you wish—for example, under your own home directory at ~/mysql-5.0.22.
To install MySQL to the directory /usr/local/mysql-5.0.22, we call the configure command with the target as follows:
$ ./configure --prefix=/usr/local/mysql-5.0.22
If all is not well, you may see some error messages. Problems during configuration are generally due to Linux programs and libraries missing from your system; read the error messages carefully to identify the cause of the problem.
If the configuration is successful, you can use the make command to compile the files:
$ make
The compilation process may take a long time.
WARNING
You need to use the GNU variant of the make program (http://www.gnu.org/software/make). The make command on most Linux systems is in fact the GNU make program; if you run into problems when using make, it might not be GNU make, and the problem may be resolved by using the gmake (GNU make) command instead.
When it’s done, you need to install the files to the directory you specified earlier. If you’ve chosen to install a local server, you can simply type:
$ make install
If—as in our example—you’ve specified a prefix path that you can’t normally write to as an ordinary user, you’ll need to first log in as root:
$ su -
and then run make install from the root prompt to copy the compiled files to the target installation directory:
# make install
If all goes well, the MySQL files will be installed in the correct directory. You’ll often find it helpful to create a link to refer to this directory easily. For example, for a system-wide server, you can make the link /usr/local/mysql to point to the /usr/local/mysql-5.0.22 directory:
# ln --symbolic /usr/local/mysql-5.0.22 /usr/local/mysql
Now you can simply refer to the MySQL directory as /usr/local/mysql. Similarly, if you specified the path /home/adam/mysql-5.0.22 for a local installation, you can make the link ~/mysql to point to the ~/mysql-5.0.22 directory:
$ ln --symbolic ~/mysql-5.0.22 ~/mysql
and refer to the directory as ~/mysql.
Again, using a symbolic link in this way allows you to configure and use different versions of MySQL on a system, with the symbolic link pointing to the directory containing the version you want to use.
Note that the configuration process assumes default values for anything that you don’t specify. For example, you can explicitly set the data directory, TCP port, and socket file (more about these later):
$
./configure \
--prefix=/home/adam/mysql \
--localstatedir=/home/adam/mysql/data \
--with-unix-socket-path=/home/adam/mysql/mysql.sock \
--with-tcp-port=53306
However, we recommend you compile only with the prefix directory specified. You can then modify other settings by passing options to MySQL from the command line; we explain how to do this in Configuring a local server,” later in this chapter. Even better, you can specify the options in an options file as described in Chapter 11.
Installing MySQL, Apache, PHP, and Perl on Linux Using Distribution Packages
Almost all distributions include packaged versions of the main pieces of software that you need to follow this book: MySQL, the Apache web server, and support for the PHP and Perl scripting languages. In this section, we’ll explain how to install these if they’re not already present on your Linux system.
The three main distributions we’ll cover are Red Hat, Mandriva, and Debian, as well as distributions associated with these, including Fedora, Mandrake, Ubuntu, and Knoppix. These are very widely used, and are well supported by the distributors and by the general Linux community. Configured correctly, they can automatically fetch and install the required software from the installation media or from the Internet.
Most distributions have an easy-to-use graphical package-management tool that you can use, but the command-line tools are generally more reliable, and we feel you’ll better understand how things fit together by carrying out the installation from the command line.
Installation on Red Hat and Fedora Core
Red Hat is probably the most famous Linux distribution, and Fedora Core is the cutting-edge version of Red Hat’s Enterprise Linux distribution. If you’re installing one of these two from scratch, select the Custom installation option and, when you see the package-selection list like that shown in Figure 2-5, select (put a checkmark) next to the Web Server item.
Figure 2-5. Red Hat and Fedora package options
To add PHP support and PHP MySQL libraries, click on the Details link on the right and select the packages “php” and “php-mysql” from the list; you should see something similar to Figure 2-6.
Figure 2-6. Detailed Red Hat and Fedora package options
Once you’ve done this, return to the package-selection list and select (put a checkmark) next to the MySQL Database item. As before, click on the Details link and ensure the “php-mysql” package is selected.
If you already have a running Linux installation, you can use the rpm command to check whether MySQL, Apache (known as httpd), and PHP are already installed:
$ rpm --query --whatprovides mysql php php-mysql
mysql-5.0.22-1.FC5.1
mysql-server-5.0.22-1.FC5.1
httpd-2.2.0-5.1.2
php-5.1.4-1
php-mysql-5.1.4-1
If, as in this example, all the necessary packages are installed, you can simply skip to Configuring a Newly Installed Server,” later in this chapter.
If the packages aren’t present, you’ll see messages like this:
no package provides php
and you’ll need to install any missing packages.
Run the Package Manager by selecting Add/Remove Software from the Fedora menu. Alternatively, log in as root and type:
# pirut
You should see a window similar to the one shown in Figure 2-7.
Figure 2-7. Red Hat and Fedora 5 package-management program
Select the List tab, and choose any of these packages that don’t already have a checkmark next to them:
httpd-2.2.0-5.1.2.i386
Apache HTTP Server
mysql-5.0.22-1.FC5.1.i386
MySQL client programs and shared libraries
mysql-server-5.0.22-1.FC5.1.i386
The MySQL server and related files
php-5.1.2-5.i386
The PHP HTML-embedded scripting language (PHP Hypertext Preprocessor)
php-mysql-5.1.4-1.i386
A module for PHP applications that use MySQL databases
The version numbers you see will probably be different from the ones we’ve listed. Once you’ve selected these, click the Apply button, and the software should be installed.
If you’re using an older version of Red Hat or Fedora, the easiest way to install is to log in under the root user account (by typing su -) and launch the package-management program shown in Figure 2-5:
# system-config-packages
Place a checkmark next to the entry for MySQL Database, and click on the Details link. You’ll see a window such as that in Figure 2-6. Select the “mysql-server” and “php-mysql” packages, and then click the Close button. You’ll be prompted for the Red Hat or Fedora installation CDs, and the selected packages will be installed.
If you have a relatively recent version of Red Hat or Fedora, you can also use the yum (short for Yellowdog Updater Modified) program to automatically download and install the necessary packages from the Internet. This is very convenient because you don’t have to spend time digging up your installation CDs. More importantly, the latest version of a package generally has patches for known bugs and security vulnerabilities. If you’ve never used yum before, you need to configure it first. First, type su - to log in as the system root user, and then update your /etc/yum.confconfiguration file by typing:
# wget http://www.fedorafaq.org/samples/yum.conf
# /bin/mv /etc/yum.conf /etc/yum.conf.bak
# /bin/mv yum.conf /etc
Now, update the yum indexes that list packages and the locations that they can be downloaded from:
# rpm --upgrade --verbose --hash http://www.fedorafaq.org/yum
Retrieving http://www.fedorafaq.org/yum
Preparing... ########################################### [100%]
1:yum-fedorafaq ########################################### [100%]
Once you’ve configured yum, you can download and install all the programs you need by simply specifying them from the command line:
# yum update mysql mysql-server httpd php php-mysql
[root@saiedpc ~]# yum update mysql mysql-server httpd php php-mysql
...
Could not find update match for php
Could not find update match for php-mysql
Could not find update match for mysql-server
Could not find update match for mysql
Resolving Dependencies
...
=============================================================================
Package Arch Version Repository Size
=============================================================================
Updating:
httpd i386 2.2.2-1.2 updates 1.1 M
Updating for dependencies:
httpd-manual i386 2.2.2-1.2 updates 846 k
mod_ssl i386 1:2.2.2-1.2 updates 99 k
Transaction Summary
=============================================================================
Install 0 Package(s)
Update 3 Package(s)
Remove 0 Package(s)
Total download size: 2.0 M
Is this ok [y/N]: y
Downloading Packages:
(1/3): mod_ssl-2.2.2-1.2. 100% |=========================| 99 kB 00:14
(2/3): httpd-2.2.2-1.2.i3 100% |=========================| 1.1 MB 03:14
(3/3): httpd-manual-2.2.2 100% |=========================| 846 kB 02:40
Running Transaction Test
Finished Transaction Test
Transaction Test Succeeded
Running Transaction
Updating : httpd ######################### [1/6]
Updating : mod_ssl ######################### [2/6]
Updating : httpd-manual ######################### [3/6]
Cleanup : mod_ssl ######################### [4/6]
Cleanup : httpd ######################### [5/6]
Cleanup : httpd-manual ######################### [6/6]
Updated: httpd.i386 0:2.2.2-1.2
Dependency Updated: httpd-manual.i386 0:2.2.2-1.2 mod_ssl.i386 1:2.2.2-1.2
Complete!
You’ll see lots of interesting messages flash by; we haven’t shown them all here. If all goes well, you should see the reassuring Complete status message at the end. If the latest version of a package is already installed, yum will tell you that it Could not find update match for that package. To learn more about Fedora and configuring yum, visit the Unofficial Fedora FAQ page (http://www.fedorafaq.org).
You can also download Red Hat or Fedora RPMs and install and upgrade them manually just as you would the MySQL AB ones. For example, you can visit the web site http://rpm.pbone.net and search for mysql; pick and download the RPM for Red Hat or Fedora with the highest version number. Once you’ve downloaded the files, log in under the root account by typing su -, and then install the RPM packages by typing in this command (all on one line):
# rpm --upgrade --verbose --hash \
mysql-server-5.0.22-2.1.i386.rpm \
mysql-5.0.22-2.1.i386.rpm \
httpd-2.2.2-7.i386.rpm \
php-5.1.4-8.1.i386.rpm
Installation on Mandriva
Mandriva, formerly known as Mandrake, is very easy to use for this book (we use it ourselves). MySQL, Apache, PHP, and Perl all come on the distribution CDs.
If you’re installing Mandriva from scratch, choose the Expert installation option and select the MySQL server and client packages.
If you already have a running Mandriva installation, you can check whether Apache, PHP, and MySQL are already installed by typing:
$ rpm --query --whatprovides mysql mysql-client apache php php-mysql
MySQL-5.0.23-1mdv2007.0
MySQL-client-5.0.23-1mdv2007.0
apache-mpm-prefork-2.2.3-1mdv2007.0
apache-mod_php-5.1.4-1mdk
php-cli-5.1.4-6mdv2007.0
php-mysql-5.1.4-3mdv2007.0
If, as in this example, all the necessary packages are installed, you can simply skip to Configuring a Newly Installed Server,” later in this chapter.
If the packages aren’t present, you’ll see messages like this:
no package provides php
and you’ll need to install any missing packages.
The easiest way to install is to log in under the root account (by typing su -) and type:
# rpmdrake
This will launch the package-management program, shown in Figure 2-8.
Figure 2-8. The Mandriva package-management program
Place a checkmark next to the entries for the MySQL server and client, and click on the Install button. You’ll be prompted to insert the Mandriva installation CDs, and the selected packages will be copied and installed.
If you prefer to use the command line, you can use the urpmi command to specify packages to install. This will prompt you to insert the appropriate installation CDs, and will install the packages. You may be prompted to install other related packages, depending on what’s already available on your system, but in most cases, it should be painless.
If you have a fast Internet connection, you can also configure urpmi to download and install the very latest packages from the Internet. This is very convenient because you don’t have to spend time digging up your installation CDs. More importantly, the latest version of a package generally has patches for known bugs and security vulnerabilities. To set up Internet downloads, you’ll first need to tell urpmi where to find the packages. The easiest way to do this is to go to http://easyurpmi.zarb.org; this site will ask you a few questions and then provide you a list of commands you need to type in as the system root user to configure the sources (Figure 2-9 shows how this site looks.) From time to time, you should update the urpmi indexes by logging in as the system root user and typing:
# urpmi.update -a
Figure 2-9. The easyURPMI configuration page
Whichever approach—CDs or the Internet—you use, you just need to type urpmi package_name as the root user to fetch and install the required packages.
$ urpmi mysql mysql-client apache php php-mysql
One of the following packages is needed:
1- MySQL-5.0.23-1mdv2007.0.i586 : MySQL: a very fast and reliable SQL database
engine (to install)
2- MySQL-Max-5.0.23-1mdv2007.0.i586 : MySQL - server with extended functionality
(to install)
3- MySQL-NDB-4.1.12-4.3.20060mdk.i586 : MySQL - server with Berkeley DB, Innodb
and NDB Cluster support (to install)
What is your choice? (1-3) 1
To satisfy dependencies, the following packages are going to be installed:
MySQL-5.0.23-1mdv2007.0.i586
MySQL-client-5.0.23-1mdv2007.0.i586
MySQL-common-5.0.23-1mdv2007.0.i586
apache-mod_php-5.1.4-2mdv2007.0.i586
libmysql15-5.0.23-1mdv2007.0.i586
perl-DBD-mysql-3.0006-1mdv2007.0.i586
php-mysql-5.1.4-3mdv2007.0.i586
Proceed with the installation of the 7 packages? (39 MB) (Y/n) Y
ftp://somehost.net/somedir/libmysql15-5.0.23-1mdv2007.0.i586.rpm
ftp://somehost.net/somedir/perl-DBD-mysql-3.0006-1mdv2007.0.i586.rpm
ftp://somehost.net/somedir/MySQL-common-5.0.23-1mdv2007.0.i586.rpm
ftp://somehost.net/somedir/MySQL-client-5.0.23-1mdv2007.0.i586.rpm
ftp://somehost.net/somedir/MySQL-5.0.23-1mdv2007.0.i586.rpm
ftp://somehost.net/somedir/apache-mod_php-5.1.4-2mdv2007.0.i586.rpm
installing
libmysql15-5.0.23-1mdv2007.0.i586.rpm
MySQL-client-5.0.23-1mdv2007.0.i586.rpm
MySQL-common-5.0.23-1mdv2007.0.i586.rpm
perl-DBD-mysql-3.0006-1mdv2007.0.i586.rpm
MySQL-5.0.23-1mdv2007.0.i586.rpm
apache-mod_php-5.1.4-2mdv2007.0.i586.rpm
php-mysql-5.1.4-3mdv2007.0.i586.rpm
from /var/cache/urpmi/rpms
Preparing... ######...######
1/7: libmysql15 ######...######
2/7: MySQL-client ######...######
3/7: perl-DBD-mysql ######...######
4/7: MySQL-common ######...######
5/7: MySQL ######...######
6/7: apache-mod_php ######...######
7/7: php-mysql ######...######
----------------------------------------------------------------------
More information on package MySQL-5.0.23-1mdv2007.0.i586
The initscript used to start mysql has been reverted to use the one shipped by
MySQL AB. This means the following changes:
* The MYSQLD_OPTIONS="--skip-networking" option in the /etc/sysconfig/mysqld
file has been removed, this is now set in the /etc/my.cnf file.
* The MySQL Instance Manager is used by default, set use_mysqld_safe="1" in
the /etc/sysconfig/mysqld file to use the old mysqld_safe script.
The extra MySQL-NDB server package has been merged into the MySQL-Max package
and ndb related pieces has been split into different sub packages as done by
MySQL AB. The MySQL libraries and the MySQL-common sub package uses the
MySQL-Max build so that no functionality required by for example the NDB parts
are lost.
The MySQL-common package now ships with a default /etc/my.cnf file that is
based on the my-medium.cnf file that comes with the source code. The
/etc/my.cnf file is constructed at build time of this package.
To connect to the Instance Manager you need to pass the correct command line
options like in the following examples:
* mysql -u root --password=my_password --port=2273 --protocol=TCP
* mysql -u root --password=my_password
--socket=/var/lib/mysql/mysqlmanager.sock
Please note you also need to add a user in the /etc/mysqlmanager.passwd file
and make sure the file is owned by the user under which the Instance Manager
service is running under.
----------------------------------------------------------------------
Here, urpmi has downloaded the latest versions of the programs from the Internet. During installation, some packages display messages that you should read; in our example, the MySQL package installation routine has described how the configuration has changed since older versions.
You can also download and install or upgrade the Mandriva RPMs without using urpmi. For example, you can visit http://rpm.pbone.net and search for mysql; pick and download the RPMs for Mandriva with the highest version number. Once you’ve downloaded the files, log in as the root user by typing su -, and then install the RPM packages by running this command (all on one line):
# rpm --upgrade --verbose --hash \
MySQL-5.0.23-1mdv2007.0.i586.rpm \
MySQL-client-5.0.23-1mdv2007.0.i586.rpm \
MySQL-common-5.0.23-1mdv2007.0.i586.rpm \
apache-mod_php-5.1.4-2mdv2007.0.i586.rpm \
libmysql15-5.0.23-1mdv2007.0.i586.rpm \
perl-DBD-mysql-3.0006-1mdv2007.0.i586.rpm \
php-mysql-5.1.4-3mdv2007.0.i586.rpm
Installing under Debian-based systems
Debian Linux and its derivatives use Debian .deb packages, rather than RPMs. The popular Ubuntu and Knoppix distributions are based on Debian.
To check whether Apache, PHP, and MySQL are already installed on a Debian-based Linux system, use the dpkg --list command. If any packages aren’t present, the dpkg program will let you know:
$ dpkg --list mysql-common mysql-server mysql-client apache2 php5
No packages found matching mysql-client.
No packages found matching apache2.
No packages found matching php5.
Desired=Unknown/Install/Remove/Purge/Hold
| Status=Not/Installed/Config-files/Unpacked/Failed-config/Half-installed
|/ Err?=(none)/Hold/Reinst-required/X=both-problems (Status,Err: uppercase=bad)
||/ Name Version Description
+++-============-===============-====================================================
ii mysql-common 5.0.21-3ubuntu1 mysql database common files (e.g. /etc/mysql/my.cnf)
un mysql-server <none> (no description available)
On some older distributions, you may need to specify php4 rather than php5.
To install MySQL, Apache, and PHP, you must first log in as the root user by typing su -, and then use the apt-get install command:
# apt-get --verbose-versions install mysql-common mysql-server mysql-client apache2 php5
Reading package lists... Done
Building dependency tree... Done
mysql-common is already the newest version.
The following extra packages will be installed:
apache2-common (2.0.55-4ubuntu2)
apache2-mpm-prefork (2.0.55-4ubuntu2)
apache2-utils (2.0.55-4ubuntu2)
libapache2-mod-php5 (5.1.2-1ubuntu3)
libapr0 (2.0.55-4ubuntu2)
libdbd-mysql-perl (3.0002-2build1)
libdbi-perl (1.50-1)
libnet-daemon-perl (0.38-1)
libplrpc-perl (0.2017-1)
mysql-client-5.0 (5.0.21-3ubuntu1)
mysql-server-5.0 (5.0.21-3ubuntu1)
php5-common (5.1.2-1ubuntu3)
ssl-cert (1.0.13)
Suggested packages:
apache2-doc (2.0.55-4ubuntu2)
lynx (2.8.5-2ubuntu1)
www-browser ()
php-pear (5.1.2-1ubuntu3)
dbishell ()
libcompress-zlib-perl (1.41-1)
Recommended packages:
mailx (8.1.2-0.20050715cvs-1ubuntu1)
The following NEW packages will be installed:
apache2 (2.0.55-4ubuntu2)
apache2-common (2.0.55-4ubuntu2)
apache2-mpm-prefork (2.0.55-4ubuntu2)
apache2-utils (2.0.55-4ubuntu2)
libapache2-mod-php5 (5.1.2-1ubuntu3)
libapr0 (2.0.55-4ubuntu2)
libdbd-mysql-perl (3.0002-2build1)
libdbi-perl (1.50-1)
libnet-daemon-perl (0.38-1)
libplrpc-perl (0.2017-1)
mysql-client (5.0.21-3ubuntu1)
mysql-client-5.0 (5.0.21-3ubuntu1)
mysql-server (5.0.21-3ubuntu1)
mysql-server-5.0 (5.0.21-3ubuntu1)
php5 (5.1.2-1ubuntu3)
php5-common (5.1.2-1ubuntu3)
ssl-cert (1.0.13)
0 upgraded, 17 newly installed, 0 to remove and 0 not upgraded.
Need to get 31.9MB/32.2MB of archives.
After unpacking 75.8MB of additional disk space will be used.
Do you want to continue [Y/n]? Y
...
The --verbose-versions option displays detailed information on the packages. Once you press the Y key, the required packages will be automatically downloaded and installed. We’ve left out most of the displayed messages to save space.
You can also download and install or upgrade the Debian packages without using apt-get; for example, you can visit the web page http://www.debian.org/distrib/packages, select your distribution, and search for “mysql.” Pick and download the package with the highest version number for your distribution. Once you’ve downloaded the files, log in as the root user by typing su -, and then install the packages by using the dpkg --install command—for example:
# dpkg --install \
mysql-common_5.0.22-4_all.deb \
mysql-server_5.0.22-4_all.deb \
mysql-client-5.0_5.0.22-4_i386.deb \
libmysqlclient15off_5.0.22-4_i386.deb
However, it’s quite likely that you’ll need to download other associated packages before the installation can proceed, and we recommend that you use the apt-get approach to automate the process. As we mentioned earlier in Installing Under Linux,” you can also download packages in the.deb format from the MySQL AB downloads page.
Uninstalling MySQL
You can generally install a newer software package over an older one by using the rpm --upgrade, urpmi, yum update, or apt-get install commands described earlier. If, you actually want to remove a package altogether rather than upgrading it, you should first type su - to log in as the root user, and then execute the appropriate uninstall commands.
Note that the data directory that contains your database files is not actually installed but created after installation. This is typically the directory data under the MySQL base directory, or /var/lib/mysql for a Linux distribution package installation. Uninstalling MySQL packages does not delete this directory, so the files containing your data should remain in place, unchanged.
For an RPM-based system such as Red Hat, Fedora, or Mandriva, use the rpm --erase command to uninstall specific packages. If you’re unsure what the exact package names are, you can use the rpm --query --all command to list all the installed RPM packages, together with thegrep --ignore-case command to show only those with “mysql” (in uppercase or lowercase letters) in their name:
$ rpm --query --all | grep --ignore-case mysql
perl-DBD-mysql-3.0004-1mdv2007.0
MySQL-5.0.23-1mdv2007.0
libmysql15-5.0.23-1mdv2007.0
MySQL-client-5.0.23-1mdv2007.0
php-mysql-5.1.4-3mdv2007.0
MySQL-common-5.0.23-1mdv2007.0
Note that the .rpm file extension is not considered to be part of the package name. To uninstall RPM packages, you use the rpm command with the --erase option, and list the packages to remove. For example, you’d type (all on one line):
# rpm --erase \
perl-DBD-mysql-3.0004-1mdv2007.0 \
MySQL-5.0.23-1mdv2007.0 \
libmysql15-5.0.23-1mdv2007.0 \
MySQL-client-5.0.23-1mdv2007.0 \
php-mysql-5.1.4-3mdv2007.0 \
MySQL-common-5.0.23-1mdv2007.0
You can query and remove the packages in one go by using the xargs command:
# rpm --query --all | grep --ignore-case mysql | xargs rpm --erase
warning: /etc/my.cnf saved as /etc/my.cnf.rpmsave
#
On a Red Hat or Fedora system with yum, you can also use the yum remove command:
# yum remove mysql
[root@saiedpc yum.repos.d]# yum remove mysql
...
Dependencies Resolved
=============================================================================
Package Arch Version Repository Size
=============================================================================
Removing:
mysql i386 5.0.22-1.FC5.1 installed 5.5 M
Removing for dependencies:
MySQL-python i386 1.2.0-3.2.2 installed 2.3 M
libdbi-dbd-mysql i386 0.8.1a-1.2.1 installed 37 k
mysql-connector-odbc i386 3.51.12-1.2.1 installed 387 k
mysql-server i386 5.0.22-1.FC5.1 installed 22 M
perl-Class-DBI-mysql noarch 1.00-1.fc5 installed 38 k
perl-DBD-MySQL i386 3.0004-1.FC5 installed 324 k
php-mysql i386 5.1.4-1 installed 176 k
Transaction Summary
=============================================================================
Install 0 Package(s)
Update 0 Package(s)
Remove 8 Package(s)
Is this ok [y/N]: y
Downloading Packages:
Running Transaction Test
Finished Transaction Test
Transaction Test Succeeded
Running Transaction
Removing : mysql-connector-odbc ######...###### [1/8]
Removing : perl-Class-DBI-mysql ######...###### [2/8]
Removing : perl-DBD-MySQL ######...###### [3/8]
Removing : php-mysql ######...###### [4/8]
Removing : mysql ######...###### [5/8]
Removing : libdbi-dbd-mysql ######...###### [6/8]
Removing : MySQL-python ######...###### [7/8]
warning: /var/log/mysqld.log saved as /var/log/mysqld.log.rpmsave
Removing : mysql-server ######...###### [8/8]
Removed: mysql.i386 0:5.0.22-1.FC5.1
Dependency Removed:
MySQL-python.i386 0:1.2.0-3.2.2
libdbi-dbd-mysql.i386 0:0.8.1a-1.2.1
mysql-connector-odbc.i386 0:3.51.12-1.2.1
mysql-server.i386 0:5.0.22-1.FC5.1
perl-Class-DBI-mysql.noarch 0:1.00-1.fc5
perl-DBD-MySQL.i386 0:3.0004-1.FC5
php-mysql.i386 0:5.1.4-1
Complete!
For Debian-based systems, you can uninstall the MySQL server and client by using the apt-get remove command:
# apt-get remove mysql-server mysql-client
Reading package lists... Done
Building dependency tree... Done
The following packages will be REMOVED:
mysql-client mysql-server
0 upgraded, 0 newly installed, 2 to remove and 1 not upgraded.
Need to get 0B of archives.
After unpacking 31.3MB disk space will be freed.
Do you want to continue [Y/n]? Y
(Reading database ... 103699 files and directories currently installed.)
Removing mysql-client ...
Removing mysql-server ...
If you’re unsure what to use for the package names, you can search for packages associated with MySQL using the following command:
# dpkg --search "*mysql*" | cut --fields=1 --delimiter=":" | sort --unique
Installing MySQL, Apache, PHP, and Perl on Linux Using the XAMPP Integrated Package
To install XAMPP on your Linux system, first visit the XAMPP home page (http://www.apachefriends.org/en/xampp.html), follow the link to XAMPP for Linux, and download the gzipped tar package. Switch to the superuser account:
$ su -
and create the directory /opt:
# mkdir --parents /opt
We’re using the --parents option here to tell Linux not to complain if the directory already exists.
Now change to this directory and extract the files from the package:
# cd /opt
# tar --gunzip --extract --file ~adam/xampp-linux-1.5.3a.tar.gz
Here, we’ve assumed that the downloaded file is in adam‘s home directory (~adam); use the appropriate location on your system.
You can now start XAMPP by typing:
# /opt/lampp/lampp start
Starting XAMPP for Linux 1.5.3a...
XAMPP: Starting Apache with SSL (and PHP5)...
XAMPP: Starting MySQL...
XAMPP: Starting ProFTPD...
XAMPP for Linux started.
If there is already a running MySQL or Apache server running on your system, XAMPP may complain during startup. If this happens, shut these down before trying to start XAMPP again. Stop any existing MySQL or Apache server before starting XAMPP.
Now that the server’s running, tighten up the security settings by typing:
# /opt/lampp/lampp security
XAMPP: Quick security check...
XAMPP: Your XAMPP pages are NOT secured by a password.
XAMPP: Do you want to set a password? [yes] n
XAMPP: MySQL is accessible via network.
XAMPP: Normally that's not recommended. Do you want me to turn it off? [yes] y
XAMPP: Turned off.
XAMPP: Stopping MySQL...
XAMPP: Starting MySQL...
XAMPP: The MySQL/phpMyAdmin user pma has no password set!!!
XAMPP: Do you want to set a password? [yes] y
XAMPP: Password:
XAMPP: Password (again):
XAMPP: Setting new MySQL pma password.
XAMPP: Setting phpMyAdmin's pma password to the new one.
XAMPP: MySQL has no root password set!!!
XAMPP: Do you want to set a password? [yes] y
XAMPP: Write the password somewhere down to make sure you won't forget it!!!
XAMPP: Password:
XAMPP: Password (again):
XAMPP: Setting new MySQL root password.
XAMPP: Change phpMyAdmin's authentication method.
XAMPP: The FTP password is still set to 'lampp'.
XAMPP: Do you want to change the password? [yes] y
XAMPP: Password:
XAMPP: Password (again):
XAMPP: Reload ProFTPD...
XAMPP: Done.
This will allow you to set a password for the MySQL server and also to configure the server for improved security.
The XAMPP installation may have PHP configured with the register_globals setting turned on. You should disable this old, insecure feature. Open the file /opt/lampp/etc/php.ini and look for the line register_globals = On. Change the value On to Off, save the file, and quit the editor. The new setting will be in effect after you restart your Apache server.
You can stop your XAMPP servers by typing:
# /opt/lampp/lampp stop
Stopping XAMPP for Linux 1.5.3a...
XAMPP: Stopping Apache with SSL...
XAMPP: Stopping MySQL...
XAMPP: Stopping ProFTPD...
XAMPP stopped.
The MySQL data directory is /opt/lampp/var/mysql; the files are owned by the user nobody, and in the root group. Given the nature of the XAMPP installation as a development platform, we won’t go into detailed modification of permissions.
Configuring a Newly Installed Server
Once you’ve installed the server, there are some steps you should take to initialize the database tables and configure the server for good security. One of the first things to do is to set a password for the database root account; this is not the same as the system root account but is similar in that it has all privileges on the MySQL server. Let’s look at three situations:
§ You’ve installed the server using RPM or Debian packages.
§ You’ve installed a system-wide server using a tarball or by compiling source code.
§ You’ve installed a local server to run under your own account using a tarball or by compiling source code.
As we explained earlier, the XAMPP package is tightly integrated and is not designed for easy modification, so we won’t explore how to customize an XAMPP installation.
Configuring a server installed using RPM or Debian packages
The package installation process generally places the MySQL program files in the /usr/bin directory, the datafiles in the /var/lib/mysql directory, and the server logs in the /var/log/mysqld directory or the /var/log/mysqld.log file.
The installation typically configures the files and directories securely and also creates the /etc/init.d/mysql or /etc/init.d/mysqld (MySQL daemon) startup script for easy control of the server.
Check what this script is called on your system using the ls command:
$ ls /etc/init.d/mysql*
/etc/init.d/mysql
In the preceding example, the file is called mysql. Use the appropriate name (mysql or mysqld) where you see mysql in the commands below.
To start the server, run the following command:
# /etc/init.d/mysql start
Set a password for the database root account:
$ mysqladmin --user=root password the_new_mysql_root_password
You can stop the server by typing the command:
# /etc/init.d/mysql stop
The package-based installation process generally starts the MySQL server, and configures it to be started automatically each time the system is started. In Configuring MySQL for automatic start,” later in this chapter, we explain how to check and configure automatic startup.
Configuring a system-wide server installed from tarball or source
For security reasons, it’s a good idea to have the system-wide MySQL server run under its own username and group, rather than the superuser account. First, log in as the root user with the su - command, and then create the mysql user group:
# groupadd mysql
and the mysql user account that’s in the mysql user group:
# useradd --gid mysql mysql
It’s all right if you get a message that the group or user already exists.
Now let’s configure the MySQL files and directories. Change to the directory where you installed MySQL; here, we’ll assume that MySQL is installed in the directory /usr/local/mysql:
# cd /usr/local/mysql
To create the data directory and initialize the database for the user mysql, run the mysql_install_db script from the scripts directory under the MySQL directory:
# scripts/mysql_install_db --user=mysql
You should now change the files in the MySQL directory to be owned by root but be in the mysql group:
# chown --recursive root:mysql .
And change the database files in the data directory to be be owned by the mysql user and group:
# chown --recursive mysql:mysql data
We described this use of the chown command in Restricting access to files and directories,” earlier in this chapter.
You can now start the server to run under the mysql system account by running the mysqld_safe program from the MySQL bin directory:
# bin/mysqld_safe --user=mysql &
The ampersand (&) character tells Linux to run the server in the background so that you can use the shell to do other things. If you don’t add the ampersand at the end, you won’t see the shell prompt again until the MySQL server is stopped from another shell window.
The next thing to do is to set a password for the database root account:
$ bin/mysqladmin --user=root password the_new_mysql_root_password
You can stop the server by running the command:
$ bin/mysqladmin --user=root --password=the_mysql_root_password shutdown
Note that the user root on the Linux system is different from the user root on the MySQL server, and you don’t need to be logged in as the Linux root user to shut down the server with mysqladmin.
You can also start and stop the server using the mysql.server script that comes in the support-files directory; start the server with:
$ support-files/mysql.server start
and stop the server with:
$ support-files/mysql.server stop
You can copy the mysql.server script and place it as the file mysql in the /etc/init.d directory:
# cp support-files/mysql.server /etc/init.d/mysql
This allows you to control the server by typing:
# /etc/init.d/mysql start
and
# /etc/init.d/mysql stop
as with the package-based installation approaches. Importantly, this also allows you to configure the server to start on every boot; this is explained later in Configuring MySQL for automatic start.”
Configuring a local server
With a local installation, the MySQL files will be placed in a directory under your home directory, and the server will run under your username rather than mysql.
First, change to the directory containing the MySQL installation. If you followed our instructions in Installing MySQL on Linux by Compiling the Source Code from MySQL AB,” you can type:
$ cd ~/mysql
To configure the data directory and initialize the database, you must run the mysql_install_db script from the scripts directory:
$ scripts/mysql_install_db
If you want to use a data directory that’s not under the MySQL installation directory, you can specify the path using the datadir option, as in:
$ mysql_install_db datadir=/home/adam/MySQL_Data
However, we’ll assume you’ll use the default data directory ~/mysql/data.
Now you need to change the files in the MySQL directory to be owned by your username and your group. For the username and group adam, you would write:
$ chown --recursive adam:adam ~/mysql
Again, we described this use of the chown command earlier in Restricting access to files and directories.”
By default, MySQL listens for incoming client connections on port number 3306; if there’s already another server running on the same computer, you should choose a different port number for this installation. It’s best to avoid using port numbers that are typically used by other common programs. For instance, port 8080 is often used by web servers and proxies. A web search for “common ports” is a good way to learn about these. Note that only the root user can allocate port numbers below 1024. We’ll use the port number 57777 for our example.
You also need to specify a custom location for the socket file; this is a special type of file used by clients to connect to a server on the same machine. A common choice for a socket file location is the server data directory; we’ll use the file path ~/mysql/data/mysql.sock in the following example.
Now, start the server using the nonstandard port and socket file:
$ bin/mysqld_safe --port=57777 --socket=~/mysql/data/mysql.sock &
Note that if you’re using a nonstandard MySQL installation directory and don’t start the server from inside that directory, you have to specify the path to the mysqld_safe program and tell this program where the data directory is. For example, to run the program from the ~/mysql/bindirectory with the data directory ~/mysql/data, you would type (all on one line):
$ ~/mysql/bin/mysqld_safe \
--port=57777 \
--socket=~/mysql/data/mysql.sock \
--datadir=~/mysql/data &
Now that the server is running, set a password for the database root account by typing:
$ bin/mysqladmin \
--port=57777 \
--socket=~/mysql/data/mysql.sock \
--user=root \
password the_new_mysql_root_password
Once you’ve added a password for the database root user, you’ll have to use it for all further client connections to the server for the root account.
You can stop the server using the mysqladmin shutdown command, with the necessary options added to identify the server. Type all on one line:
$ bin/mysqladmin \
--port=57777 \
--socket=~/mysql/data/mysql.sock \
--user=root \
--password=the_mysql_root_password \
shutdown
Configuring MySQL for automatic start
If you’re planning to use MySQL a lot, you’ll probably want to have the server start automatically every time your computer is switched on. The typical way to do this is to call a script to start and stop the MySQL server when the computer is started and stopped.
If you used an RPM or Debian package to install MySQL, this script is generally already installed as /etc/init.d/mysql or /etc/init.d/mysqld (MySQL daemon). Check what this script is called on your system using the ls command:
$ ls /etc/init.d/mysql*
/etc/init.d/mysql
In the preceding example, the file is called mysql. Use the appropriate name (mysql or mysqld) where you see mysql in the commands below.
If you installed from a tarball or from source, you’ll need to copy the file across yourself as discussed in the earlier section, Configuring a system-wide server installed from tarball or source.”
A Linux system can start in one of six runlevels; a system starting in runlevel 5 will typically boot straight into the graphical windowing environment such as KDE or GNOME, while a system starting in runlevels 2 or 3 will end up at a text-based login screen. There’s an easy way to check what runlevel you’re in; just use the runlevel program in the /sbin directory:
$ /sbin/runlevel
N 5
Here, the system is in runlevel 5.
A program is started automatically for a particular runlevel if there’s a startup entry for it in the corresponding /etc/rc<runlevel>.d directory. You can list all the entries for MySQL by typing:
$ ls /etc/rc*.d/*mysql*
/etc/rc0.d/K90mysql /etc/rc2.d/S11mysql /etc/rc4.d/S11mysql /etc/rc6.d/K90mysql
/etc/rc1.d/K90mysql /etc/rc3.d/S11mysql /etc/rc5.d/S11mysql
The entries starting with “S” start the program when the system is booted, and the entries starting with “K” stop (or kill) the program when the system is shut down. Here, MySQL is set to start and stop automatically in runlevels 2, 3, 4, and 5. On Red Hat or Mandriva systems, you can more conveniently determine this using the chkconfig --list command:
# chkconfig --list mysql
mysql 0:off 1:off 2:on 3:on 4:on 5:on 6:off
If your server shows “off” for the runlevel that you found using the runlevel command, the MySQL server is not started automatically.
If you don’t see an entry for your preferred runlevel (normally 3 or 5), you’ll need to add one yourself. Most Linux distributions have a graphical tool to configure startup services. For example, under Red Hat and Fedora, you can run the Service Configuration program by choosing the Services entry from the Administration submenu of the System menu; you can also run this program by typing system-config-services at the command line. Similarly, with Mandriva, you can use the Services program from the Mandriva Control Center (select Configure Your Computer from the Configuration submenu of the System menu); you can also run this program by typing drakxservices at the command line. We’ll explain how to configure services without using these graphical tools.
On a Red Hat or Mandriva system, type:
# chkconfig --level 35 mysql on
to enable automatic startup in runlevels 3 and 5 (corresponding to normal console or graphical operation run levels), and:
# chkconfig --level 35 mysql off
to disable it.
In a Debian-based system, startup services are controlled using the update-rc.d command. Enable MySQL as follows:
# update-rc.d mysql defaults
Adding system startup for /etc/init.d/mysql ...
/etc/rc0.d/K20mysql -> ../init.d/mysql
/etc/rc1.d/K20mysql -> ../init.d/mysql
/etc/rc6.d/K20mysql -> ../init.d/mysql
/etc/rc2.d/S20mysql -> ../init.d/mysql
/etc/rc3.d/S20mysql -> ../init.d/mysql
/etc/rc4.d/S20mysql -> ../init.d/mysql
/etc/rc5.d/S20mysql -> ../init.d/mysql
and disable automatic startup as follows:
# update-rc.d -f mysql remove
update-rc.d: /etc/init.d/mysql exists during rc.d purge (continuing)
Removing any system startup links for /etc/init.d/mysql ...
/etc/rc0.d/K20mysql
/etc/rc1.d/K20mysql
/etc/rc2.d/S20mysql
/etc/rc3.d/S20mysql
/etc/rc4.d/S20mysql
/etc/rc5.d/S20mysql
/etc/rc6.d/K20mysql
If you have a standalone Apache web server installed, you can enable and disable its automatic startup by using httpd or apache2 instead of mysql in the preceding commands.
Installing Under Windows
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 Verifying Package Integrity with MD5,” 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.
Installing Only MySQL Using Packages from MySQL AB
First, follow the instructions of Downloading MySQL from the MySQL AB Web Site,” 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 “Without installer (unzip in C:\).” We’ll discuss how to install each of these packages in the following sections.
Windows installation using the installer
Start the installer program and go with the default (typical) settings. This will install MySQL in the C:\Program Files\MySQL\MySQL Server 5.0\ directory. Vista may ask you to confirm whether you want to allow the installer to access your computer; click Allow.
You might be prompted to sign up for a mysql.com account; you can skip this unless you want to subscribe to MySQL newsletters, add comments to the online manual, or file bug reports.
On the final screen of the installer program, you’ll see the “Configure the MySQL Server now” option selected. When you click the Finish button to exit the installer, the MySQL Server Instance Configuration Wizard will start. Follow the prompts and select Standard Configuration to go with the default settings.
Figure 2-10. Specifying the server options during the Windows installation
On the next screen, shown in Figure 2-10, select “Install as a Windows Service” (typically already selected by default) and “Include Bin Directory in Windows PATH” (typically not already selected by default).
Select a new root user password (there isn’t one by default), and then follow the prompts until the installation process is completed. Unless you know what you’re doing, don’t select the option to enable root access from remote machines. Also, don’t select the option to create an anonymous account; we’ll discuss anonymous accounts and the security problems associated with them in Chapter 9.
You may find that the installation program fails to configure the service under Vista (you’ll see an error message like “Could not connect to the Service Control Manager”). If this happens, click the Back button twice to return to the options dialog box, and then uncheck the “Install as a Windows Service” checkbox. Continue the installation process from this point.
You can run the configuration program again at any time by selecting the MySQL Server Instance Config Wizard entry from the MySQL Server 5.0 section of the MySQL submenu of the Windows Start menu.
Starting and stopping MySQL as a service
If the installation process successfully configures MySQL as a Windows service, you can use the Windows Services window at any time to check and control the server. On Windows XP, select the Performance and Maintenance entry from the Control Panel, and then choose Administrative Tools. If you have Classic View enabled, you can choose Administrative Tools directly from the Control Panel.
Figure 2-11. The Windows services window
Figure 2-11 shows the Windows Services window.
Under Vista, open the Control Panel, and select the “System and Maintenance” entry. From here, select the Administrative Tools and then the Services entry. Windows may prompt you for authorization—click Continue.
Scroll down the list of services till you see an entry for MySQL, and select it. The installation process sets the service status to be Automatic—that is, the server is started automatically every time Windows is booted. If you’d prefer to start and stop the server manually, you can set the service status to Manual. You can also start and stop the server by clicking the Start or Stop link on the left of the Services window, or by opening a command-prompt window and typing:
C:\> net start mysql
or:
C:\> net stop mysql
You can run MySQL programs from the command window by first changing to the MySQL directory:
C:\> cd "C:\Program Files\MySQL\MySQL Server 5.0"
and then typing in the MySQL program name.
For example, you can stop the server directly by calling the mysqladmin program directly from the MySQL installation directory. You would type (all on one line):
C:\Program Files\MySQL\MySQL Server 5.0> bin\mysqladmin \
--user=root \
--password=the_mysql_root_password \
shutdown
Never kill the MySQL server from the Windows task manager; you could lose data.
Starting and stopping MySQL from the command line
If the installation program could not install the service, you’ll need to start and stop the server from the command line. To do this, open a command prompt window and change your working directory to the directory where the MySQL installation has been installed. This is typicallyC:\Program Files\MySQL\MySQL Server 5.0\:
C:\> cd C:\Program Files\MySQL\MySQL Server 5.0\
To start the server, type:
C:\Program Files\MySQL\MySQL Server 5.0> bin\mysqld-nt
Under Windows, executable programs such as mysqld-nt have the extension .exe. You can include the full name and extension, as in mysqld-nt.exe; if you leave it out, Windows won’t complain. If the program ends immediately, restart the server but add the option no-defaults:
C:\Program Files\MySQL\MySQL Server 5.0> bin\mysqld-nt --no-defaults
This tells the server not to expect an options file—we discuss this in further detail in Chapter 11. You may also be prompted by your firewall software to authorize the server to listen for incoming connections from the network; unless you need to allow connections from other computers, it’s a good idea to keep blocking such connections.
Once the program’s started, nothing exciting will happen—you’ll just see a blinking cursor; this command window will remain open as long as the server is running, so to use any other MySQL command-line programs, you’ll need to open another command-prompt window.
For example, to shut the server down, you should open another command-prompt window and change to the MySQL directory:
C:\> cd C:\Program Files\MySQL\MySQL Server 5.0\
and then stop the server by sending the shutdown command:
C:\Program Files\MySQL\MySQL Server 5.0> bin\mysqladmin \
--user=root \
--password=the_mysql_root_password \
shutdown
Never kill the MySQL server from the Windows task manager; you could lose data.
Installation with the “no-install” .zip Archive
The “no-install” package is a ready-to-use collection of files bundled together and compressed using the popular ZIP compression method. All you need to do is extract the package to the desired directory. This is useful if you don’t have administrator access on a Windows computer or if you want to avoid changes that the installer program might make to your Windows configuration.
Windows can handle ZIP files itself; if you’ve installed an archiving program such as WinZip or PKZIP, this application will normally process the file instead of the standard Windows decompression tool. In this book, we explain only the default Windows behavior and assume that you know how to use any extra utility programs on your system.
The icon for a compressed file often has a picture of a zipper on it, as shown in Figure 2-12. If you double-click on the icon, you’ll be able to see inside the package, but this isn’t useful right now. Instead, right-click on the icon, select “Extract All...” as shown in Figure 2-12, and follow the prompts.
Figure 2-12. Decompressing the compressed package
Ignore the Password button; the archive doesn’t have one. When you see the message “Select a Destination,” replace the existing text with the directory you want the MySQL directory to be located in. Figure 2-13 shows this window.
Figure 2-13. The Windows compressed-file-extraction dialog window
The recommended directory for this package is C:\, so use this, and click the Next button. The files will be installed to the mysql-5.0.22-win32 subdirectory.
When you use the ZIP archive, a Windows service isn’t configured for MySQL; you’ll need to start the server using the MySQL commands themselves. The MySQL executables directory isn’t added to your Windows path either, so you’ll need to always tell Windows where to find the MySQL programs you’re trying to run. In the examples here, we assume you first change to the MySQL directory and then tell Windows to run the programs from the bin directory. Alternatively, you can add the directory to the search path manually following the steps outlined later in Error Message About MySQL Executable Programs Not Being Found or Recognized.”
To control and access the server, open a command-prompt window and change your working directory to the MySQL directory. For example, if you extracted the files to the C:\ directory, change to the MySQL directory under there:
C:\> cd C:\mysql-5.0.22-win32
To start the server, type:
C:\mysql-5.0.22-win32> bin\mysqld-nt
as shown in Figure 2-14. Under Windows, executable programs such as mysqld-nt have the extension .exe. You can include the full name and extension, as in mysqld-nt.exe; if you leave it out, Windows won’t complain. If the program ends immediately, restart it with the option no-defaults:
C:\mysql-5.0.22-win32>bin\mysql-nt --no-defaults
This tells the server not to expect an options file. We discuss options files in Chapter 11
You may also be prompted by your firewall software to authorize the server to listen for incoming connections from the network; unless you need to allow connections from other computers, it’s a good idea to keep blocking such connections.
Once the program’s started, nothing exciting will happen: you’ll just see a blinking cursor; this command window will remain open as long as the server is running, so to use any other MySQL command-line programs, you’ll need to open another command-prompt window.
Figure 2-14. Starting the server in Windows
Open another command-prompt window and change to the MySQL directory:
C:\> cd C:\mysql-5.0.22-win32
Now, set a password for the database root account (all on one line):
C:\mysql-5.0.22-win32> bin\mysqladmin --user=root \
password the_new_mysql_root_password
Finally, stop the server by sending the shutdown command:
C:\mysql-5.0.22-win32> bin\mysqladmin --user=root \
--password=the_mysql_root_password shutdown
Figure 2-15 shows the second command-prompt window and the mysqladmin commands we’ve just discussed.
Figure 2-15. Running the mysqladmin program from the Windows command prompt
Here, we used the password "new root password" as an example; you should choose a password that’s hard to guess. As we’ve got spaces in the password, we’ve enclosed it in quotes. Notice also how the command to shut down the server has wrapped to the next line; this is fine, but don’t press the Enter key until you’ve finished typing the whole command.
Installing MySQL, Apache, PHP, and Perl on Windows Using the XAMPP Integrated Package
To install XAMPP on your Windows system, first visit the XAMPP home page at http://www.apachefriends.org/en/xampp.html, follow the link to XAMPP for Windows, and download the installer package. The package will have a name like xampp-win32-1.5.3a-installer.exe.
Run the installer package once you’ve downloaded it; Vista may prompt you to confirm you want to do this. Accept C:\Program Files as the installation directory and click the Install button. XAMPP is installed to the C:\Program Files\xampp directory. Don’t change this unless you really have to. We assume this is the directory you’re using.
After XAMPP is installed, you’ll be prompted to install XAMPP servers as a service; choose “yes”. Also select “yes” when asked whether you want to install Apache2 as a service and whether you want to autostart the server. If you get a message about port 80 (the web server port) being blocked on your system, check whether you have another running web server, such as Microsoft IIS; this server could have been installed as part of Visual Studio .NET. You can also select “yes” to install the FileZilla FTP server as a service, “no” to autostart the service, “no” to start the service, and “no” to uninstall the server.
If your Windows Firewall is active, you may be told that Apache has been blocked from accepting incoming network connections. Unless you need to allow connections from other computers, this is a good setting to stick with, so choose to keep blocking the connections.
Finally, select “yes” when prompted to start the XAMPP control panel. The installation program places a shortcut to the XAMPP control panel on your desktop, but if it’s not there, you can also start it from the XAMPP control panel from the “apachefriends” submenu of the Windows Start menu. Figure 2-16 shows what the XAMPP control panel looks like.
Figure 2-16. The XAMPP control panel
Start the MySQL server by clicking the Start button next to the MySQL label. You need to be logged in as a user with Windows Administrator privileges to control XAMPP, although an unprivileged user is allowed to place files on the web server. Stop any existing MySQL or Apache server before starting XAMPP.
XAMPP does not modify the Windows path. If you need to run any MySQL programs from the command prompt, you’ll need to run them from the MySQL bin directory. You can avoid this inconvenience by adding the C:\Program Files\xampp\mysql\bin directory to your Windows path as discussed in Error Message About MySQL Executable Programs Not Being Found or Recognized,” later in this chapter.
The first thing you should do once you’ve started the server is to set a password for the database root account. First, open a command window and change to the MySQL directory:
C:\> cd C:\Program Files\xampp\mysql
Then run the mysqladmin program from the bin directory:
C:\Program Files\xampp\mysql> bin\mysqladmin \
--user=root \
password the_new_mysql_root_password
You can also configure the server password, as well as other settings for better security, by loading http://localhost in your browser and clicking on the Security link on the left of the page. This takes you to a page that displays information on your server security configuration and allows you to add passwords to authenticate access to the MySQL and Apache servers.
The XAMPP installation has PHP configured, with the register_globals setting turned on. You should disable this old, insecure feature: open the file C:\Program Files\xampp\php\php.ini and look for the line register_globals = On. Change the value On to Off, save the file, and quit the editor. The new setting will be in effect after you restart your Apache server.
Finally, you can stop the MySQL server by pressing the Stop button next to the MySQL label in the XAMPP control panel.
Installing Under Mac OS X
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 non-installation 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.
Installing only MySQL Using the Installer from MySQL AB
Following the instructions of Downloading MySQL from the MySQL AB Web Site,” 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 2-17.
Figure 2-17. 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.
Finally, if you want the MySQL server to be started and stopped automatically each time the computer is started or stopped, double-click on the MySQLStartupItem.pkg item and install this too.
Configuring the installed server
For security reasons, it’s a good idea to have the MySQL server run under its own username and group, rather than under the superuser account. If something goes wrong with the server, or an attacker gains control of the server, the damage will be restricted to the MySQL user rather than the whole system. Mac OS X comes with a mysql user and group already defined. You can check this using the graphical NetInfo Manager tool, or from the shell prompt.
To check using the NetInfo Manager, double-click on the NetInfo Manager icon in the Utilities folder under the Applications group, as shown in Figure 2-18.
Figure 2-18. Starting the Mac OS X NetInfo Manager
Then, select “groups” and scroll down to make sure that there is an entry for the mysql group, as shown in Figure 2-19.
Figure 2-19. Verifying that the mysql group exists
Similarly, you can select “users” and scroll down to see that there is an entry for the mysql user there too.
You can instead check these settings from the shell prompt. To do this, open a terminal window and use the grep command to search for the word mysql in the system’s list of users (the /etc/passwd file) and groups (the /etc/group file):
$ grep mysql /etc/passwd /etc/group
/etc/passwd:mysql:*:74:74:MySQL Server:/var/empty:/usr/bin/false
/etc/group:mysql:*:74:
You should see two lines similar to the ones above.
If, for some reason, the mysql user and group aren’t configured on your system, you have to create them. You can add them in the NetInfo Manager by clicking on the lock icon at the bottom of the screen and selecting the Add entry from the Edit menu. However, it’s probably faster to perform these steps from the command line.
First, create the user mysql (note that the first forward slash symbol (/) stands by itself on the line):
$ sudo niutil -create / /users/mysql
and assign invalid (and therefore relatively secure) values for the home directory and login shell:
$ sudo niutil -createprop / /users/mysql home /var/empty
$ sudo niutil -createprop / /users/mysql shell /usr/bin/false
Next, create the group mysql:
$ sudo niutil -create / /groups/mysql
Once you’ve done this, define a Group ID number (gid) for the mysql group and a User ID number (uid) for the mysql user. The Mac OS X default value for both these IDs is 74; you can choose this or any other value—for example, 674—that’s not already allocated to a user or group. Let’s use 74 in our example, and assign this value to the mysql group and user:
$ sudo niutil -createprop / /groups/mysql gid 74
$ sudo niutil -createprop / /users/mysql uid 74
Finally, associate the mysql user with the mysql group:
$ sudo niutil -createprop / /users/mysql gid 74
When you’re sure the correct user and group exist, you have to initialize the MySQL databases. First, change to the MySQL base directory:
$ cd /usr/local/mysql
Then run the mysql_install_db script from the scripts directory. The user option assigns ownership of the MySQL datafiles and folders to the specified user—here to the system mysql account:
$ sudo scripts/mysql_install_db --user=mysql
You should now change the files in the MySQL directory to be owned by root but be in the mysql group:
$ sudo chown -RL root:mysql /usr/local/mysql
The -RL option tells the chown command to apply the ownership rule recursively (R) to everything under the /usr/local/mysql directory, following symbolic links (L) if necessary. You should also change the database files in the data directory to be owned by the mysql user and group:
$ sudo chown -RL mysql:mysql /usr/local/mysql/data
If you used the mysql_install_db script with the user=mysql option, this will already have been done for you.
You can now start the server and stop it in several ways; let’s look at a few of these.
First, if you installed the MySQL.prefPane item, you can use the MySQL pane in the System Preferences window. To access this, click on the Apple logo at the top left of the screen menu, select the “System Preferences...” menu entry, and then click on the MySQL icon in the System Preferences window. This will bring up a window similar to Figure 2-20, with a button labeled Start MySQL Server when the server is not running and Stop MySQL Server when it is.
Figure 2-20. The MySQL preferences pane
Clicking on this button will start or stop the server. You may be asked to type in your password.
Second, you can use the mysql.server script in the MySQL directory:
$ sudo /usr/local/mysql/support-files/mysql.server start
to start the MySQL server, and:
$ sudo /usr/local/mysql/support-files/mysql.server stop
to stop it.
Third, if you installed the MySQLStartupItem.pkg file during the installation process, you can start the server from the command line by calling:
$ sudo /Library/StartupItems/MySQLCOM/MySQLCOM start
and stop it by calling:
$ sudo /Library/StartupItems/MySQLCOM/MySQLCOM start
Finally, you can use the generic mysqld_safe and mysqladmin programs from the command prompt. To start the server to run under the system mysql user account, type:
$ sudo /usr/local/mysql/bin/mysqld_safe --user=mysql
Then, press the Ctrl-Z key combination, and type bg to leave the server running in the background.
You can then stop the server by running the command:
$ /usr/local/mysql/bin/mysqladmin --user=root \
--password=the_mysql_root_password shutdown
This approach is the most robust, and also the most flexible if you need to add custom options to your server.
The first thing you should do once you’ve started the server is to set a password for the database root account:
$ sudo /usr/local/mysql/bin/mysqladmin --user=root password \
the_new_mysql_root_password
Once you’ve set the MySQL root password, you’ll need to use this in all further accesses to the server. For example, to shut down the server using mysqladmin, you would type:
$ /usr/local/mysql/bin/mysqladmin --user=root \
--password=the_mysql_root_password shutdown
Installing Only MySQL Using the no-installer Package from MySQL AB
Following the instructions in Downloading MySQL from the MySQL AB Web Site,” earlier in this chapter, visit the MySQL AB downloads page and download the “Without installer” package corresponding to the version of your operating system and processor type.
This will download a compressed package with a name like mysql-standard-5.0.22-osx10.4-i686.tar.gz. This is normally automatically decompressed and unpacked by the web browser to leave the directory mysql-standard-5.0.22-osx10.4-i686 in the download directory. You may instead find that your browser decompresses the file but does not unpack it. If this is the case, you’ll find the file mysql-standard-5.0.22-osx10.4-i686.tar in your download directory. You can unpack this in any location by opening a terminal window, changing to the directory you want to run MySQL from, and calling the tar program from there to unpack the file. For example, if the file was downloaded to your Desktop directory, but you want to have the MySQL directory under your home directory, you can open a terminal window and type:
$ cd
to go to your home directory, and:
$ tar --extract --file ~/Desktop/mysql-standard-5.0.22-osx10.4-i686.tar
to unpack the file that’s in your Desktop directory. If the browser does not decompress the file at all, you’ll find the downloaded file still has a .gz extension. You can follow the same steps as for the decompressed file, but use the gunzip option to decompress the file before unpacking it:
$ tar --gunzip --extract --file \
~/Desktop/mysql-standard-5.0.22-osx10.4-i686.tar.gz
Once the package has been decompressed, you can move the resulting directory to the location you want. For example, you can move it to be under your home directory, either by dragging and dropping with the mouse, or by using the mv command from the shell:
$ mv ~/Desktop/mysql-standard-5.0.22-osx10.4-i686 ~
You can also create a symbolic link to the MySQL directory so that you can refer to it as simply ~/mysql:
$ ln -s ~/Desktop/mysql-standard-5.0.22-osx10.4-i686 ~/mysql
Once you have the extracted directory, you should change to that directory:
$ cd ~/mysql
and run the mysql_install_db program from the scripts directory to initialize the MySQL databases:
$ scripts/mysql_install_db
You can now start the server using the command:
$ bin/mysqld_safe &
Set a password for the MySQL server root account immediately:
$ bin/mysqladmin --user=root password the_new_mysql_root_password
Since we’ve set a password for the root user, you need to use this password in all further accesses to the server for the root account. You can now stop the server using the command:
$ bin/mysqladmin --user=root --password=the_mysql_root_password shutdown
Installing MySQL, Apache, PHP, and Perl on Mac OS X Using the XAMPP Integrated Package
To install XAMPP on Mac OS X, visit the XAMPP home page (http://www.apachefriends.org/en/xampp.html), follow the link to “XAMPP for Mac OS X,” and download the installer package.
The installer package is in the StuffIt Expander (.sitx) format. If you get a screen of garbled text in your browser when trying to download it, press the “back” button to see the download link—for example, http://easynews.dl.sourceforge.net/sourceforge/xampp/xampp-macosx-0.3.sitx. Hold down the Ctrl key and click on the link. From the menu that appears, select the entry that says Download Linked File (for Safari), Save Link As (for Firefox), or Download Link to Disk (for Internet Explorer).
Once the StuffIt archive is downloaded, double-click on it to extract the installation package, and then double-click on the installation package to start the XAMPP installation program. When the decompression program finishes, you should find the installation program saved in the same directory as the downloaded file, or on your Desktop. This installation program has a name like xampp-macosx-0.5.pkg. Running this and accepting the default settings will install XAMPP to the /Applications/xampp/ directory, with the MySQL datafiles located in the/Applications/xampp/xamppfiles/var/mysql directory.
If there is already a running MySQL or Apache server running on your system, XAMPP may complain during startup. If this happens, shut these down before trying to start XAMPP again. To switch off the default installation of Apache, go to the System Preferences Window and click on Sharing. If the Personal Web Sharing entry has a checkmark next to it, uncheck it to stop the Apache web server.
You can start XAMPP by typing:
$ sudo /Applications/xampp/xamppfiles/mampp start
Now that the server’s running, tighten up the security settings by typing:
$ sudo /Applications/xampp/xamppfiles/mampp security
and follow the prompts to add a password to your MySQL server.
The XAMPP installation has PHP configured with the register_globals setting turned on. You should disable this old, insecure feature: open the file /Applications/xampp/etc/php.ini and look for the line register_globals = On. Change the value On to Off, save the file, and quit the editor. The new setting will be in effect after you restart your Apache server.
You can also manually set the MySQL server root password as follows:
$ sudo /Applications/xampp/xamppfiles/bin/mysqladmin \
--user root \
password the_new_mysql_root_password
When the XAMPP web server is running, you can load pages from your own computer by starting a browser such as Safari and opening the web page http://localhost.
You can stop XAMPP by typing:
$ sudo /Applications/xampp/xamppfiles/mampp stop
If you’re keen to access the MySQL executable files directly, you can start the server by typing:
$ sudo /Applications/xampp/xamppfiles/bin/mysqld_safe
Then, press the Ctrl-Z key combination, and type bg to leave the server running in the background.
Similarly, you can shut down the server by typing:
$ sudo /Applications/xampp/xamppfiles/bin/mysqladmin \
--user root \
--password=the_mysql_root_password \
shutdown
Using a MySQL Installation Provided by an ISP
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!
Upgrading an Existing MySQL Server
If you’ve got a MySQL server that’s running well and without problems, you may wonder whether 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), andtriggers (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.
Some new features could greatly simplify your application, allowing you to reduce development time by simply upgrading your MySQL server. On a related note, application software that you might want to use with your database server—for example, a free web portal system—might require you to have a minimum version of MySQL.
Newer versions of MySQL are generally backward-compatible with recent versions—that is, older ways of doing things will continue to work. A new server can work with old data, and even with older clients. For example, MySQL password management was improved in version 4.1.0. The new server can correctly handle passwords stored in the old format, and, if it’s started with the old-passwords option, it can modify its behavior to cater to older clients, such as a web server that uses the old mechanism.
However, software is generally not upward-compatible—that is, you’re more likely to have difficulty if moving from a newer version of MySQL to an older version, especially if they are major versions apart (for example, moving from MySQL 5.0 to MySQL 4.1). It’s hard to find cases where downgrading is warranted.
You should assess your own needs and decide whether an upgrade is necessary or worthwhile; if, for example, you have an online shopping application that’s running perfectly, you would only need to upgrade if you wanted to make changes that would be easier done with a newer MySQL version or if you learn of bugs that could affect the reliability or security of your site. Upgrading a MySQL server could require upgrades to other associated software—for example, the version of PHP that the application uses; you must think carefully about the implications of an upgrade before diving in.
Before deciding to upgrade, read the release notes for the new version; in particular, note any changes marked as an “incompatible change.” You can find a complete set of release notes under the “MySQL Change History” section of the MySQL manual (http://dev.mysql.com/doc/refman/5.1/en/news.html). For example, you may find that support for something that you need is no longer available in the new version, or that you need to carry out certain steps before you start the new server with your existing data. You should also read the“Upgrading MySQL” section of the MySQL manual (http://dev.mysql.com/doc/refman/5.1/en/upgrade.html). Note that these links point to the latest version of the manual (5.1) available at the time of writing.
In this book, we don’t describe how to change over from a non-MySQL database server, such as Microsoft Access, Microsoft SQL Server, or Oracle. The MySQL Migration Toolkit is a graphical tool that helps you through the process of moving your data over to MySQL. You can download this program as as part of the MySQL GUI Tools Bundle from the MySQL AB downloads page at http://dev.mysql.com/downloads.
Should I Upgrade to MySQL 5.1?
At the time of writing, MySQL 5.1 is in beta testing; this means that it’s available for easy use and testing, but that it’s best to avoid using it for mission-critical production sites. You can download and install MySQL 5.1 using the same procedures discussed in this chapter for the Generally Available versions. Probably the most interesting new features in MySQL 5.1 are its powerful text search capabilities, improved support for XML data, and optimizations for applications where the server must handle very high loads with very high reliability. It’s likely that you won’t need these features for a considerable time after beginning to use MySQL, and you can complete all the examples in this book with any version of the MySQL server newer than 4.1.0 onwards.
How to Upgrade
We have seen in this chapter that different installation approaches place the MySQL program and datafiles in different locations. For example, a MySQL AB RPM installs the MySQL program files and the data directory under the /usr/local/mysql directory, while a package provided by a Linux distribution typically places the MySQL program files in the /usr/bin directory, and the datafiles in the /var/lib/mysql directory. Upgrading a MySQL server installs new versions of the program files but will not affect your datafiles.
The best way to ensure a trouble-free upgrade is to use the same approach as that used to install the original server because the installation process can upgrade the existing program files, and the new server will know where to find your datafiles. Alternatively, you should isolate or remove the old version so that there is no confusion about which program version is called when you type in a command.
To be able to revert to the older version of the MySQL server if the migration runs into problems, you can install the new server to a different directory from the default. Under Linux and Mac OS X, you can also make a symbolic link to the directory containing the version you want to use. We discussed how to do this earlier in Installing MySQL on Linux Using a gzipped Tar Archive from MySQL AB” and Installing MySQL on Linux by Compiling the Source Code from MySQL AB” for the tarball and source installation methods under Linux, and in Installing Only MySQL Using the no-installer Package from MySQL AB” for a local MySQL installation under Mac OS X. The MySQL AB installer for Mac OS X creates this symbolic link automatically for a system-wide installation. Under Windows, you can specify a different installation directory during the installation process. It isn’t straightforward to have coexisting MySQL versions under Linux if you use RPM or Debian packages.
Steps to Upgrade an Existing MySQL Server
There are several ways to upgrade a server. Here, we look at a simple and reliable approach. We first save all the databases on the old server to a dump file. Next, we install the new server. Finally, we load the saved databases into the new server. This last step is not always necessary; you can often get the new server to use the datafiles from the old one.
1. To start, change directories to make the old MySQL installation directory your current working directory. Under Linux or Mac OS X, this is typically /usr/local/mysql for a system-wide installation:
$ cd /usr/local/mysql
and ~/mysql for a local installation:
$ cd ~/mysql
Under Windows, the MySQL server is typically installed under the MySQL directory—for example, C:\Program Files\MySQL\MySQL Server 5.0:
C:\> cd C:\Program Files\MySQL\MySQL Server 5.0
Again, we’ll show the command-line instructions for Linux and Mac OS X; under Windows, simply replace the forward slash (/) with the backslash (\).
2. We discuss how to make a database dumps in detail in Chapter 10. You can dump all the databases on the old MySQL server to the file dump_of_all_databases_from_old_server.sql by typing (all on one line):
3. $ bin/mysqldump \
4. --user=root \
5. --password=the_mysql_root_password \
6. --result-file=dump_of_all_databases_from_old_server.sql \
--all-databases
It’s a good idea to make a backup of this file on CD or copy it across to another computer.
7. Shut down the old server:
$ bin/mysqladmin --user root --password=the_mysql_root_password shutdown
8. Install the new server.
9. Configure and start the new server using the appropriate commands discussed earlier in this chapter.
10.At this point, you should have a fresh installation of the MySQL server and associated programs. If the new server version was installed using the same approach as the old version, it’s likely to have the same data directory. To check that your databases are available on the new server, you can use the mysqlshow command to connect to it and list the databases:
$ bin/mysqlshow --user root --password=the_mysql_root_password
You can also use the SHOW DATABASES command in the MySQL monitor (described in Chapter 3).
If you used a different approach, or for some reason the new server doesn’t know about your old databases, you should now change your working directory to the location of the new MySQL installation, and then load the databases from the dump file you created earlier:
$ bin/mysql \
--user root \
--password=the_mysql_root_password \
< dump_of_all_databases_from_old_server.sql
Of course, you should use the password of the new MySQL server here.
11.Your new server should now have loaded all the databases from your old server. One of these, the mysql database, contains grant tables that specify user access levels. You should now check and upgrade these tables if necessary.
Under Linux, change to your MySQL base directory and type:
$scripts/mysql_fix_privilege_tables \
--user=root \
--password=the_mysql_root_password
For a Windows MySQL server version 4.0.15 or newer, type:
C:\Program Files\MySQL\MySQL Server 5.0> bin/mysql \
--user=root \
--password=the_mysql_root_password \
--database=mysql \
< scripts\mysql_fix_privilege_tables.sql
Finally, for Mac OS X, type:
$ sudo /usr/local/mysql/bin/mysql_fix_privilege_tables \
--user=root \
--password=the_mysql_root_password
Don’t worry if you see warnings about duplicate column names. Once you’ve completed upgrading the tables, stop the server.
Configuring Access to the MySQL Server
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 Restricting access to files and directories,” 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 mysql_secure_installation script from the MySQL bin directory to walk interactively through steps to improve the security of your server:
$ bin/mysql_secure_installation
...
Change the root password? [Y/n] n
...
Remove anonymous users? [Y/n] y
...
Disallow root login remotely? [Y/n] y
...
Remove test database and access to it? [Y/n] n
...
Reload privilege tables now? [Y/n] y
...
The ellipsis (...) symbols indicate where we’ve left out some of the program output.
What If Things Don’t Work?
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.
Can’t Download Files from Behind a Proxy
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:mypass@server_name:port
# export ftp_proxy=http://proxy_username:mypass@server_name:port
For example, you might type:
# export http_proxy=http://adam:mypass@proxy.mycompany.com:8080
# export ftp_proxy=http://adam:mypass@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.
Error Message About MySQL Executable Programs Not Being Found or Recognized
To use MySQL, you need to run MySQL executable programs, such as the server programs mysqld_safe and mysqld-nt.exe (described in Chapter 12), the monitor program mysql (described in Chapter 3), and the mysqladmin administration program that we use in this chapter and throughout this book. These programs are located together in a bin directory somewhere on the system; examples of these are:
Linux
/usr/local/mysql/bin for a system-wide installation from a tarball or source files, ~/mysql-5.0.22/bin for a local gzipped-tar installation, /usr/bin directory for an installation from RPM or Debian packages, and /opt/lampp/bin for an XAMPP installation.
Windows
C:\mysql-5.0.22-win32\bin for a “no-install” (zip) installation; C:\Program Files\MySQL\MySQL Server 5.0\bin for a standard installation; and C:\Program Files\xampp\mysql\bin for an XAMPP installation.
Mac OS X
/usr/local/mysql/bin for a system-wide installation from a tarball or source files, ~/mysql-5.0.22/bin for a local gzipped-tar installation, /Applications/xampp/xamppfiles/bin for an XAMPP installation.
If you can’t find the MySQL programs in one of these directories, try to remember where you installed the server files. On a Linux or Mac OS X system, use the find command as the root user to locate the mysqld_safe program:
# find / -name mysqld_safe
If you run this command as an ordinary user (not as root), you’re likely to see lots of “permission denied” messages telling you that you can’t look inside certain directories.
On a Windows system, use the search tool to look for files with the word “mysql” in their names. If your search doesn’t turn anything up, it’s likely that MySQL hasn’t in fact been installed. Run the installation process again, note the directory in which the files will be installed, and ensure that all the steps complete successfully.
Once you know where the executable programs are located, you can run each executable program by specifying the full path to it—for example:
$ /usr/local/mysql/mysqladmin status
on a Linux or Mac OS X system, and:
C:\> "C:\Program Files\MySQL\MySQL Server 5.0\bin\mysqladmin status
on a Windows system.
If the MySQL bin directory is listed in your system PATH, you can simply type:
$ mysqladmin status
from your operating system command prompt. If you get a message such as:
command not found
under Linux or Mac OS X, or:
'mysqladmin' is not recognized as an internal or external command,
operable program or batch file.
under Windows, the directory containing the MySQL executable programs is not in the system path.
The convenient thing to do is to add the MySQL bin directory to your system path. You can see the list of directories in your system path by typing:
$ echo $PATH
at the command line. If the MySQL bin directory isn’t listed there, take the following steps.
For a Linux or Mac system, open your ~/.bashrc shell configuration file (start a new file if there isn’t one already) in a text editor using the instructions in Using a Text Editor” at the beginning of this chapter, and add this line to the bottom:
export PATH=$PATH:/usr/local/mysql/bin:
If you use a shell other than bash, you’ll need to edit the appropriate shell configuration file. For example, if you use tcsh, you’ll need to edit the ~/.tcshrc or ~/.cshrc file and add the line:
setenv PATH $PATH:/usr/local/mysql/bin
To activate the changes, type $ source ~/.bashrc, log out and log in again, or simply restart the computer.
For Windows, you can update the path in two ways. The first way is to run the MySQL server configuration program again by selecting MySQL Server Instance Config Wizard from the Windows Start menu and selecting the “Include Bin Directory in Windows PATH” checkbox as described earlier in this chapter.
The second way is to manually add the appropriate entry to your Windows path by following these steps:
1. Open the Windows control panel.
2. If you don’t have the control panel Classic View enabled (it’s disabled by default), you’ll need to step through one additional window (if you have Classic View enabled, you can skip this step). Under XP, if you have Category View enabled, you’ll see an icon for Performance and Maintenance; open this. Under Vista, the control panel window will open at the Control Panel Home view by default; click on the System and Maintenance entry.
3. Open the System entry. Under Vista, click on the Advanced System Settings link under the list of tasks.
4. Select the Advanced tab
5. Click on the Environment Variables button
6. In the bottom half of the window, you’ll see the “System variables” pane. Scroll down this list until you see an entry for Path.
7. Double-click on this entry, or select it and press the Edit button.
8. In the dialog box that appears, go to the end of the Variable value field and add a semicolon followed by the path to the MySQL bin directory. For example, if you installed MySQL to the C:\Program Files\MySQL\MySQL Server 5.0\bin directory, you should add:
;C:\Program Files\MySQL\MySQL Server 5.0\bin
The semicolon at the start is a delimiter used to separate entries in the system path.
9. Press the OK button to close the edit dialog box, and then press the OK button to close the Environment Variables dialog box. The new path should be active immediately.
Error Message Running mysql_install_db
If, on a Linux or Mac OS X system, you get messages like these when running mysql_install_db:
$ bin/mysql_install_db
Installing all prepared tables
/home/saied/mysql/libexec/mysqld: Can't read dir of '/root/tmp/' (Errcode: 13)
Fill help tables
/home/saied/mysql/libexec/mysqld: Can't read dir of '/root/tmp/' (Errcode: 13)
...
then the setting for your temporary files isn’t set correctly. The solution is to declare the directory to use for temporary files as:
$ export TMPDIR=/tmp
On most systems, the directory /tmp is present and accessible by all users. You can use any other directory you wish, but remember that it must exist, and you must have permission to create and delete files in that directory.
Server Doesn’t Start
Possible questions to ask yourself include:
§ Do you have filesystem access to the MySQL commands? Under Linux, try running mysqld_safe as the user root. Under Windows, ensure that you have administrator privileges. Under Mac OS X, check that you used the sudo keyword when calling mysqld_safe.
§ Is the server already running? Try stopping the server first and then starting it again.
§ Is there another server using port 3306? Try starting your server with a different port using the port option.
If you’re interested, you can list the open ports on a system using the open source nmap security scanner program that is available for Linux, Windows, and Mac OS X. To list the open ports on your own machine (localhost), you’d type:
$ nmap localhost
Starting Nmap 4.11 ( http://www.insecure.org/nmap/ ) at 2006-07-23 02:09 EST
Interesting ports on saied-ltc.cs.rmit.edu.au (127.0.0.1):
Not shown: 1669 closed ports
PORT STATE SERVICE
22/tcp open ssh
25/tcp open smtp
80/tcp open http
143/tcp open imap
631/tcp open ipp
1494/tcp open citrix-ica
3306/tcp open mysql
6000/tcp open X11
8080/tcp open http-proxy
32770/tcp open sometimes-rpc3
Nmap finished: 1 IP address (1 host up) scanned in 0.472 seconds
Here, you can see that there is a MySQL server listening on port 3306.
A good place to find clues to your problem is to look at the error logfile; this is normally in the data directory with the system host name and the extension .err. For example, the logfile for the host eden.learningmysql.com is generally called eden.err or eden.learningmysql.com.err. For a Linux host, this might be the file /var/lib/mysql/eden.err, /usr/local/mysql/eden.err, or /opt/lampp/var/mysql/eden.err, depending on the way MySQL was installed. Similarly, on a Windows system, possible locations for the error logfile include C:\mysql-5.0.22-win32\data\eden.err,C:\Program Files\MySQL\MySQL Server 5.0\data\eden.err, and C:\Program Files\xampp\mysql\data\eden.err. Finally, for a Mac OS X system, likely locations for the error logfile are /usr/local/mysql/eden.err and /Applications/xampp/xamppfiles/var/mysql/eden.err.
You can use the more command to look inside this file:
$ more /var/lib/mysql/eden.err
050813 22:31:04 mysqld started
050813 22:31:04 InnoDB: Operating system error number 13 in a file operation.
InnoDB: The error means mysqld does not have the access rights to
InnoDB: the directory.
InnoDB: File name ./ibdata1
InnoDB: File operation call: 'create'.
InnoDB: Cannot continue operation.
050813 22:31:04 mysqld ended
This particular message indicates that the directory permissions may not be set correctly. Press Ctrl-C to exit the more program.
If you installed MySQL on a Linux system using packages provided by your Linux distribution, you may instead find the MySQL logs under a different name—for example, mysqld.log, in the /var/log/mysql or /var/log/mysqld directory.
Client Programs Can’t Connect to the Server
Consider these questions:
1. Did you use the correct username and password? Since the default MySQL installation doesn’t have a password set, it is easy to be confused when passwords are enabled. For the MySQL command-line tools, try using the user and password options. If you’ve forgotten your password, try resetting it by following the steps of Resetting Forgotten MySQL Passwords” in Chapter 9.
2. Is the server running? Try running the command mysqladmin status from a terminal window or command prompt.
3. If connecting to a server on localhost, do you have filesystem access to the socket file? The socket file is normally created as /tmp/mysql.sock but can be created in any location specified when the server was started. If it’s created in a directory that some users can’t access—for example, in the MySQL server’s data directory—these users won’t be able to connect to the server. For the MySQL command-line tools, use the socket option to specify a custom socket path.
4. If connecting to a server on a host other than localhost, is the server running on a port other than 3306? You should specify the same port to the client as the one you specified when starting your server; if you don’t administer the server, ask the system administrator to tell you the correct port number. For the MySQL command-line tools, use the port option to specify a custom port number.
5. If connecting to a server on a host other than localhost, is it configured to accept network connections? Ensure that the server was not started with the skip-networking option on the command line or in an options file (we discuss options files in Chapter 11).
6. If connecting to a server on a host other than localhost, is a firewall preventing network connections? Firewall software or hardware may be preventing connections to the port on which MySQL listens for incoming connections (the default port is 3306). To fix this, you need to modify the firewall so that connections on this port are allowed. Firewalls vary between networks and platforms, and you’ll need to refer to your documentation or discuss with your system administrator how to make these changes to your network or host-based firewall. Any firewall changes should be considered carefully to balance feature and security requirements.
Server Doesn’t Stop
When you try to shut down the server, you may get a message like:
$ bin/mysqladmin shutdown
mysqladmin: shutdown failed; error: 'Access denied; you need the SHUTDOWN privilege
for this operation'
This indicates that you have to use a MySQL user account that has the privilege to shut down the server. If you’re not logged in to your system under the root account, MySQL will use your own username and the password, if any, (for example, adam) when connecting to the MySQL server. (We discuss user privileges in detail in Chapter 9.) For now, it’s enough to know that you should use the MySQL root account to shut down the server. You can do this by specifying the username from the command line:
$ bin/mysqladmin --user=root --password-che-root.passwordshutdown
STOPPING server from pid file /var/run/mysql.pid
060706 21:04:02 mysqld ended
The Contents of the MySQL Directory
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 mysql and test databases, so you’ll have at least these two subdirectories in your data directory. The mysql database contains information on user access privileges to different databases; as its name suggests, the test database can be used for testing.
docs/
Contains the MySQL manual. Under Linux and Mac OS X, the manual file is in an info file called mysql.info; you can view this by changing into the docs directory and typing:
$ info mysql.info
To see how to navigate in the info viewer, press the “?” key in the program.
Under Windows, the manual file is in the Microsoft HTML Help file called manual.chm; you can view this file by double-clicking on the file icon, or by changing to the Docs directory and typing:
C:\Program Files\MySQL\MySQL Server 5.0> hh manual.chm
In practice, you’re more likely to find it more convenient to refer to the HTML version of the MySQL manual available from the MySQL AB web site.
include/
Contains header files for use when developing programs that use MySQL libraries.
lib/
Contains library files that can be used by third-party programs to access the MySQL server.
mysql-test/
Contains detailed tests you can run to confirm that your server is working properly.
sql-bench/
Contains detailed tests that can be used to measure database server performance.
scripts/
Contains scripts, such as mysql_install_db, that may be needed for server administration. Under Windows, you can’t run most of the scripts directly, but there are several files that contain SQL statements to do certain tasks, which can be run through the MySQL server.
share/
Contains configuration files, such as translations of MySQL display messages for different languages.
Other directories that are typically present on a full installation include:
man/
Contains information on some MySQL programs in the classic Unix manual format.
If you installed MySQL using RPMs or an installer, you should be able to view the manual pages by typing man followed by the command name—for example:
$ man mysqldump
If you used a gzipped tar archive, you can add the MySQL man directory to the search path used by the manual page-viewer program. To do this, edit the file .bashrc in your home directory (~/.bashrc) and add this line to the end:
export MANPATH=$MANPATH:/usr/local/mysql/man
To activate the changes, type $ source ~/.bashrc, log out and log in again, or simply restart the computer.
Finally, you can always view these files by typing a command such as the one below (using the mysqldump file as an example):
$ man /usr/local/mysql/man/man1/mysqldump.1
support-files/
Contains files and scripts used to configure the server, including ones you can use or modify for your system.
tests/
Contains sample Perl programs to connect to the MySQL server and perform various simple database operations.
If you install MySQL using packages provided by your Linux distributions, the directory locations will vary from the standard layout. For example, the executable files—such as mysqld_safe, mysql, and mysqladmin—are typically installed in /usr/bin/, and the data directory is located at/var/lib/mysql. Similarly, the logfiles may be stored in the /var/log/mysqld directory, or the main server log may be the file /var/log/mysql. Clearly, there’s a trade-off between easy installation using RPM packages and the disparate location of MySQL-related files when the server is installed in this way. The XAMPP web page has a section under “Basic Questions” named “Where is What?” which lists the locations of configuration files and components.
Configuring and Controlling the Apache Web Server
For all chapters up through Chapter 12, you will need access to only a MySQL server. To practice the examples in Chapters 13, 14, and 15, you’ll need an Apache web server with support for the PHP language. In Chapter 18, 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 apachectl or apache2ctl commands described later in this section in Starting and Stopping Apache.”
If you see some response when you try to load a page in your browser, you can try placing content in the server’s document root. Let’s see how to find this directory.
The Apache Document Root
The document root is the base or parent directory in which the web server stores web resources (such as HTML, PHP, or image files) and serves them to web browsers. For the Apache web server, common locations of the document root include:
Linux
/var/www/html, /var/www/htdocs, or /var/www for a distribution installation; /usr/local/apache/htdocs for a standalone installation, and /opt/lampp/htdocs for an XAMPP installation.
Windows
C:\Program Files\xampp\htdocs for an XAMPP installation, and C:\Program Files\Apache Group\Apache2\htdocs if Apache is installed independently
Mac OS X
/Applications/xampp/htdocs for an XAMPP installation, and /Library/WebServer/Documents for the installation of Apache that is part of the standard Mac OS X configuration
If you’re using a Linux system and don’t know where your server’s document root is, search for it by following these instructions.
First, log in as the system superuser by typing su - in a terminal window. Then try to list the common document root directories that we listed previously:
# ls --directory /var/www/html /var/www/htdocs /var/www /usr/local/apache/htdocs
/bin/ls: /var/www/htdocs: No such file or directory
/bin/ls: /usr/local/apache/htdocs: No such file or directory
/var/www /var/www/html
The --directory option asks the ls program to list only directory names, and not their contents.
If you get an error message for a directory, that directory doesn’t exist. Where the directory name is listed, as for /var/www and /var/www/html above, the directory exists. One of these is likely to be the document root. If none of the directories exist, you can try searching your whole filesystem for a directory called htdocs:
# find / -type d -name htdocs
Be patient; this may take a few minutes. Any directory it finds is likely to be the directory root; if more than one is found, you’ll need to experiment by creating files in each to determine which is the one used by your Apache installation.
The Apache Configuration File
The Apache configuration file is usually called httpd.conf and is found in one of several common locations:
Linux
/etc/httpd/conf/httpd.conf or /etc/apache/conf/httpd.conf for an installation from Linux distribution files; /usr/local/apache/conf/httpd.conf for an installation from Apache Foundation files; and /opt/lampp/etc/httpd.conf for an XAMPP installation
Windows
C:\Program Files\xampp\apache\conf\httpd.conf for an XAMPP installation
Mac OS X
/Applications/xampp/etc/httpd.conf for an XAMPP installation, and /etc/httpd/httpd.conf for the installation of Apache that is part of the standard Mac OS X configuration
It’s increasingly common to find servers configured in a modular way, with a main configuration file that reads in other files, for example under the directory /etc/httpd/modules.d on a Linux system, or in the apache\conf\extra directory under the XAMPP install directory. For example, directives specific to PHP are often stored in the file /etc/httpd/modules.d/70_mod_php.conf.
If you make changes to the Apache configuration file, you need to restart the web server to put the changes into effect.
The Apache Error Log
Common locations for the web server error log include:
Linux
/usr/local/apache/logs/error.log for Apache installed from Apache Foundation files, /var/log/httpd/error_log or /var/log/apache/error.log for an installation using distribution packages, and /opt/lampp/logs/error_log for an XAMPP installation
Windows
C:\Program Files\xampp\apache\logs\error.log for an XAMPP installation
Mac OS X
/Applications/xampp/xamppfiles/logs/error_log for an XAMPP installation, and /private/var/log/httpd/error_log for the Apache installation that is part of the standard Mac OS X configuration
Starting and Stopping Apache
Apache web server installations usually include a control script called: apachectlthat you can use to start or stop the server. On newer installations this is sometimes called apache2ctl; if the examples below don't work for you, try replacing apachectl with apache2ctl. You can generally start an installed Apache server by using the command:
# apachectl start
If this fails on a Linux or Mac OS X system because the command isn't found, use the find command to locate the apachectl script file:
# find / -type f -name apachectl
On a Windows system, use the built-in search instead of the find command. If it’s reported as being in, say, /usr/local/apache/bin/apachectl, try starting Apache using that full path:
# /usr/local/apache/bin/apachectl start
Apache should start, and you should be able to test it by loading the web page http://localhost in your browser.
You can stop the server by typing:
# apachectl stop
If you make a change to the web server configuration file, you can stop and start the server in one go by typing:
# apachectl restart
If you have an XAMPP installation, you can more easily start and stop the Apache web server using the XAMPP control scripts (Linux and Mac OS X) or control panel (Windows). Earlier, we described how to do this alongside our XAMPP installation instructions for each operating system.
Checking Whether Your Apache Installation Supports PHP
Once you’ve found your document root and have Apache running, you can check whether it can serve PHP requests, and whether its PHP engine has support for MySQL. Using a text editor, create the file phpinfo.php so that it has one line with the following contents:
<?php phpinfo(); ?>
Save this file with the name phpinfo.php in the document root directory. On a Linux or Mac OS X system, you can check the file permissions by listing the file, <path_to_document_root>/phpinfo.php, for example:
$ ls -al /var/www/html/phpinfo.php
-rw------- 1 saied saied 20 Jul 22 11:35 /var/www/html/phpinfo.php
Here, only the user who owns the file (saied) has permission to read and write the file. For the web server to read a file, the file should be readable by everyone. You can set the appropriate permissions as follows:
$ chmod u=rw,g=r,o=r path_to_document_root/phpinfo.php
If you check the permissions again, you should find that other users can access the file; we’ve granted the group read access as well, but that’s not strictly necessary:
$ ls -al path_to_document_root/phpinfo.php
-rw-r--r-- 1 saied saied 20 Jul 22 11:35 /var/www/html/phpinfo.php
A common cause of Access Denied problems is the file or directory not being readable. The web server also needs execute access to the directory containing the file, and all the directories above it. On some systems, only the superuser can write to the document root, so you may also need to allow write access to the document root. See Restricting access to files and directories,” at the beginning of this chapter, for more discussion of file and directory permissions.
After creating the file, run the script by requesting the address http://localhost/phpinfo.php with a web browser that is running on the same machine as the web server. If you see a readable web page—and not just what you typed into the file—then your web server has PHP support. Search this page for the word “mysql”; if you find a section labeled “mysql” (and perhaps another labeled “mysqli”), your PHP installation can talk to your MySQL server.
If you just see the contents of the phpinfo.php file, or your browser tries to download the file, your Apache server may not support PHP. However, there are three common problems that can cause this to happen even when your server does support PHP:
§ Your PHP test files don’t have the extension .php. If this is the case, your web server will deliver the source code and not run the scripts. Rename your scripts with a .php extension.
§ Your web server isn’t configured to run the PHP engine when a file with the .php extension is requested. In Apache, this is controlled by the Apache configuration file described earlier in The Apache Configuration File.” Open the configuration file and search for the following line:
AddType application/x-httpd-php .php
If this line is commented out—that is, there’s a pound or hash symbol (#) before the text on the line—uncomment the line by removing this symbol, save the file, and restart the web server following the instructions listed earlier in Starting and Stopping Apache.” If the line isn’t there at all, add it and restart the server.
§ Your Apache PHP module isn’t being loaded by Apache. Open the Apache configuration file and check whether one of the following lines appears in the file:
§ LoadModule php4_module libexec/libphp4.so
LoadModule php5_module libexec/libphp5.so
Add one of these lines if they don’t appear in the file. Try using the php5_module line first. If both lines have the pound or hash symbol before the text on the line, remove the comment symbol from one of the lines to activate the PHP module. If you change the Apache configuration file, restart the web server.
If you’re sure that you have Apache but not PHP, or that your PHP installation does not support MySQL, the easiest solution is to reinstall by following the instructions earlier in this chapter.
Setting up Perl
Chapters 16, 17, and 18 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 17 and 18, you’ll need two Perl extension packages or modules. We’ll use the Perl DBI (Database Interface) module in Chapter 17 to talk to a MySQL server, and the Perl CGI (Common Gateway Interface) module in Chapter 18 to write clean and readable scripts that can be run by a web server. If you’re not planning to write complex 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.
Checking Your Existing Setup
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\xampp\perl\bin\perl.exe, while 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 Installing Perl modules under Mac OS X,” 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:
C:\> C:\Program Files\xampp\perl\bin\perl --version
or on a Mac system as:
$ /Applications/xampp/xamppfiles/bin/perl --version
You should either add this bin directory to your system path following the instructions earlier in this chapter in Error Message About MySQL Executable Programs Not Being Found or Recognized,” or specify the full path to the Perl interpreter whenever you see perl for the remainder of this chapter.
If Perl is available, the command will display several lines of text describing the version and other configuration details. If Perl is not installed, you’ll see an error message saying something like command not found (Linux or Mac OS X) or 'perl' is not recognized as an internal or external command, operable program or batch file (Windows). You can find more information on obtaining and installing Perl at http://www.perl.org/get.html, and more information on installing modules at http://www.cpan.org/modules/INSTALL.html. For a Linux system, download and install the Perl package for your distribution according to the instructions in Installing MySQL, Apache, PHP, and Perl on Linux Using Distribution Packages.” For a Windows or Mac OS X system, check that you’ve installed XAMPP correctly.
Once you know that Perl is installed, you can test whether the DBI and CGI modules are installed by asking the Perl interpreter to use these modules to run an empty Perl script. To check whether the DBI module is installed, type:
$ perl -mDBI -e ''
If you see an error message that Perl “Can’t locate” DBI.pm, you’ll need to install the module yourself. Similarly, check whether the CGI module is installed by typing:
$ perl -mCGI -e ''
If the DBI module is installed, you should also check whether the MySQL database driver (DBD) is installed. An easy way to do this is to ask Perl to print out all the drivers that are available:
$ perl -e "use DBI; foreach $d (DBI->available_drivers()){print $d;}"
DBMExamplePFileSpongemysql
If you don’t see the letters “mysql”, you’ll need to install the MySQL database driver.
Installing the Perl DBI and CGI Modules
If you found that you need to install the CGI or DBI module, or the MySQL DBD, then you need to follow the steps outlined in the following sections for each operating system.
Installing Perl modules under Linux
The standard way to install Perl modules is to get Perl to use the CPAN (Comprehensive Perl Archive Network) module to install new modules from the Internet. Log in as the system root user by typing su -, and then install the DBI module, the DBI MySQL Database Driver, and the CGI module by running the following commands in turn:
# perl -MCPAN -e 'install DBI'
# perl -MCPAN -e 'install DBD::mysql;'
# perl -MCPAN -e 'install CGI;'
If this is the first time you’re installing Perl modules this way, you may be prompted to answer a few questions. It’s generally safe to answer no to the question:
Are you ready for manual configuration? [yes]
and leave it to Perl to figure out how to fetch the required packages. If all goes well, you should see reassuring status messages as Perl downloads and installs everything.
Perl modules are also available for individual Linux distributions, and you can download and install them manually. For RPM-based systems, you should download and install the perl-DBI package for DBI, the perl-DBD-mysql package for the DBI MySQL driver, and the perl-CGI package for CGI. For example, on a Red Hat or Fedora system, type:
# yum update perl-DBI perl-DBD-mysql perl-CGI
For a Mandriva or Mandrake system, type:
# urpmi perl-DBI perl-DBD-mysql perl-CGI
For a Debian-based system, the package names are slightly different:
# apt-get install libdbi-perl libdbd-mysql-perl libcgi-pm-perl
Installing Perl modules under Windows
Windows does not have Perl support by default, so you need to install a Perl interpreter yourself. The XAMPP package you installed earlier in this chapter includes a minimal Perl setup. However, to include a reasonable set of Perl libraries, including the DBI and CGI modules and the MySQL DBD, you should also visit the web page http://www.apachefriends.org/en/xampp.html and download and install the Perl Addons installer. This will have a filename similar to xampp-perl-addon-5.8.7-2.2.2-installer.exe. Install this in the same directory in which your XAMPP installation is located; we assume this is C:\Program Files\xampp.
Many of the MySQL command-line programs in the scripts directory are in fact Perl scripts; if you want to use these scripts, you’ll need to associate Perl files with the Perl interpreter. To do this, you tell Windows that all files with the standard Perl extension .pl must be run by the Perl interpreter. Open a command prompt window and type the following two lines:
C:\> ASSOC .pl=PerlScript
C:\> FTYPE PerlScript=C:\Program Files\xampp\perl\bin\perl.exe %1 %*
You can now run Perl scripts by double-clicking on the icon of the script file, or by typing in the name of the script file at the command prompt. You can find other tips for using Perl under Windows in the Perl Win32 FAQ (http://www.perl.com/doc/FAQs/nt/perlwin32faq4.html).
Installing Perl modules under Mac OS X
Mac OS X comes with a Perl interpreter already installed, so after installing XAMPP following the instructions earlier in this chapter, you’ll have two Perl interpreters on your system: /usr/bin/perl and /Applications/xampp/xamppfiles/bin/perl. You’ll need to configure the DBI and CGI modules for at least one of these.
Since we use XAMPP for other parts of this book, our instructions will focus on it. You can still configure the system default Perl interpreter by typing /usr/bin/perl in place of /Applications/xampp/xamppfiles/bin/perl in our instructions, but we feel that you’ll have fewer difficulties if you work with the XAMPP installation.
For XAMPP, you need to do two things to ensure a hassle-free DBD MySQL driver installation. First, to allow the DBD installation process to test the installation process using the MySQL server, start XAMPP by typing:
$ sudo /Applications/xampp/xamppfiles/mampp start
Then create a symbolic link to the XAMPP MySQL socket file in the default MySQL socket file location /tmp/mysql.sock, which is where Perl will expect to find it:
$ ln -s /Applications/xampp/xamppfiles/var/mysql/mysql.sock /tmp/mysql.sock
Some versions of XAMPP come with permission settings for the /Applications/xampp/xamppfiles/lib/perl5 directory that don’t allow ordinary users to access it, causing modules to appear missing. To ensure that the permissions are correctly set, type:
$ sudo chmod u=rwx,g=rx,o=rx /Applications/xampp/xamppfiles/lib/perl5
We discussed permission settings in Restricting access to files and directories,” at the beginning of this chapter.
You can download and install the DBI module, the MySQL driver, and the CGI module for the XAMPP Perl installation by typing these commands in turn:
$ sudo /Applications/xampp/xamppfiles/bin/perl -MCPAN -e 'install DBI;'
$ sudo /Applications/xampp/xamppfiles/bin/perl -MCPAN -e 'install DBD::mysql;'
$ sudo /Applications/xampp/xamppfiles/bin/perl -MCPAN -e 'install CGI;'
You may be prompted for the system root user password. You may also be prompted to configure the download locations with the message:
Are you ready for manual configuration? [yes]
Unless you’re very sure of what you’re doing, just type no and press the Enter key to let Perl figure out how best to download the required files.
Problems installing the Perl modules
If, during the install process, you see an error message such as this one:
Error: Unable to locate installed Perl libraries or Perl source code.
It is recommended that you install perl in a standard location before
building extensions. Some precompiled versions of perl do not contain
these header files, so you cannot build extensions. In such a case,
please build and install your perl from a fresh perl distribution. It
usually solves this kind of problem.
(You get this message, because MakeMaker could not find
"/System/Library/Perl/5.8.1/darwin-thread-multi-2level/CORE/perl.h")
# Looks like your test died before it could output anything.
Running make test
Make had some problems, maybe interrupted? Won't test
Running make install
Make had some problems, maybe interrupted? Won't install
you’ll need to install the Apple Developer Tools. These are available on the Mac OS X install disk that came with your system. Double-click on the XcodeTools.mpkg icon on the screen of disk contents and follow the prompts to install this package.
You can also get the latest version of the Developer Tools by visiting http://developer.apple.com and registering as a developer (it’s free). Once you’ve registered and logged in to the site, click on the Downloads link, and then click on the Developer Tools link on the downloads page. From the Developer Tools download page, click on the latest release of the .Mac SDK; at the time of writing, this was 1.2, with version 2.0 available for testing.
If you see a message similar to the one below:
Writing Makefile for DBD::mysql
-- NOT OK
Running make test
Can't test without successful make
Running make install
make had returned bad status, install seems impossible
you’ll need to build the downloaded module manually. First, check the directories containing the downloaded module source files:
$ ls ~/.cpan/build
DBD-mysql-3.0002 DBI-1.48
In this example, we have files for DBI version 1.48 and DBD MySQL driver version 3.0002. The versions you download may be different.
Now, build the module by changing to the corresponding directory (here we’ll compile the DBI module):
$ cd ~/.cpan/build/DBI-1.48
and using the make command:
$ make
Once the module has been successfully built, install it as the system root user:
$ sudo make install
Repeat this process for any other modules you need to compile.
Resources
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).
Throughout this book, we point out security aspects you should consider while installing, configuring, and running MySQL and associated web applications. To better understand security issues, we highly recommend these resources:
§ Security Engineering: A Guide to Building Dependable Distributed Systems by Ross J. Anderson (Wiley). This book is also available online at http://www.cl.cam.ac.uk/~rja14/book.html.
§ Secrets and Lies: Digital Security in a Networked World by Bruce Schneier (Wiley).
§ The monthly Crypto-Gram Newsletter, written by Bruce Schneier, available at http://www.schneier.com/crypto-gram.html.
Exercises
1. What is command completion?
2. What are the relative advantages of installing MySQL using the package, directory archive (tarball or “no-install”), or compiled methods?
3. How do you verify the integrity of downloaded packages?
4. How do you add the MySQL bin directory to the operating system path?