Installing MySQL on Unix

MySQL distributions for Unix are available in binary (pre-built) and source formats. Binary distributions are easier to install, but you must accept the installation layout and configuration defaults that are built into the distribution. Source distributions are more difficult to install because you must compile the software, but you also get more control over configuration parameters. For example, you can compile the distribution to include only the storage engines and character sets that you want, and you can install the software wherever you like.

In the instructions here, I cover how to install MySQL on Unix for the following distribution types:

• Installation using a tar file binary distribution.

• Installation using RPM files on Linux.

• Installation using a tar file source distribution.

However, you should be aware that there are other installation methods for MySQL, such as by using a DMG disk image on Mac OS X, a FreeBSD port, a Gentoo Linux ebuild, or apt-get on Debian Linux. If you're using a Unix or Unix-like system that has its own packaging system, you might want to use that for installing MySQL instead of the instructions here. (If so, you should still take a look at the instructions in "Post-Installation Steps" after performing the initial installation.)

MySQL distributions contain one or more of the following components:

• The mysqld server

• Client programs (mysql, mysqladmin, and so forth) and client programming support (C libraries and header files)

• Language support

• Documentation

• The benchmark database

Source and binary distributions contain all of these components. RPM files contain only some of them, so you might need to install multiple RPMs to get everything you need.

If you plan to connect to a server that's running on another machine, you don't need to install a server. But you should always install client software, for the following reasons:

• If you don't run a server, you'll need the client programs so that you can connect to a server on another machine.

• If you do run a server, you'll want to be able to connect to it from the server host, not be forced to log in on another machine that has the client software just to test your server.

• The C client library is required for writing programs using any API that incorporates that library. For example, you'll need it for DBI if you plan to write MySQL-based Perl scripts.

Overview of Unix MySQL Installation

Installing MySQL on Unix involves the following steps:

Creating a Login Account for the MySQL User

This step is necessary only for a first-time installation, and only if you're going to run a MySQL server. You can skip it for an upgrade or if you're installing MySQL client software only.

The MySQL server can be run as any Unix user on your system, but for security and administrative reasons, you should not run the server as root. I recommend that you create a separate account to use for MySQL administration and that you run the server as that user. That way, you can log in as that user and have full privileges in the data directory for performing maintenance and troubleshooting. Procedures for creating user accounts vary from system to system. Consult your local documentation for specific details.

This book uses mysql for both the Unix user and group names of the MySQL administrative account. If you plan to install MySQL only for your own use, you can run it from your own account, in which case you'll use your own login and group names wherever you see mysql for a user or group name in this book. If you use RPM files, the server RPM installation procedure creates a login account for a user named mysql automatically for you. On Mac OS X, DMG packages assume that a login account named mysql exists.

Before you create an account to use for running MySQL, check first to see whether your system already has one. Many Unix systems include a mysql user and group among the set of standard accounts.

Using a separate, unprivileged account rather than root for running MySQL has several benefits:

• If you do not run MySQL as root, no one can exploit the server as a security hole to gain root access.

• It's safer to perform MySQL administrative tasks as an unprivileged user than as root. If you make a mistake while performing a filesystem operation as root, the consequences can be devastating.

• As it runs, the server will create files owned by the mysql account rather than by root. The fewer root-owned files on your system, the better.

• Conceptually, it's cleaner to separate MySQL activity into its own account, and it's easier to see what things on your system are MySQL related. For example, in the directory where crontab files are kept, you'll have a separate file for the MySQL user, mysql. Otherwise, the MySQL cron jobs will be listed in root's crontab file, along with everything else done as root on a periodic basis.

Obtaining and Installing a MySQL Distribution on Unix

The following instructions use version to stand for the version number of the MySQL distribution you're working with, and platform to stand for the type of platform on which you're installing it. These values are used in distribution filenames so that distributions can be identified easily and distinguished from one another. A version number is something like 4.0.23, 4.1.6-gamma, or 5.0.2-alpha. A platform name is something like sun-solaris2.8-sparc for a system running Solaris 2.8 on SPARC hardware or pc-linux-i686 for a system running Linux on an Intel PC.

Binary distribution files that have -max or -Max in the filename include a server built with additional features that the standard server does not have. The particular features change occasionally, so you should check the MySQL Web site to see how the standard and max distributions differ currently. If you're compiling from source, select the features you want by running the configure script with the appropriate options.

Installing a tar File Binary Distribution

tar file binary distribution files have names such as mysql-version-platform.tar.gz. Obtain the distribution file for the version and platform you want and put it in the directory under which you want to install MySQL (for example, /usr/local).

Unpack the distribution using one of the following commands (use the second command if your version of tar doesn't understand the z option):

% tar zxf mysql-version-platform.tar.gz
% gunzip < mysql-version-platform.tar.gz | tar xf -

Unpacking a distribution file creates a directory named mysql-version-platform that contains the distribution's contents. To make it easier to refer to this directory, create a symbolic link to it named mysql:

% ln -s mysql-version-platform mysql

After creating the link, you can refer to the installation directory as /usr/local/mysql if you installed MySQL under /usr/local.

Now proceed to the section "Post-Installation Steps."

Installing an RPM Distribution

RPM files are available for installing MySQL on Linux systems. They have filenames of the following forms:

• MySQL-version-platform.rpm

  The server software.

• MySQL-Max-version-platform.rpm

  The MySQL-Max server that adds extra features. Before installing this RPM, you must install the corresponding MySQL-version-platform.rpm file first.

• MySQL-client-version-platform.rpm

  The client programs. When you install MySQL from RPM files, you should always install the client package.

• MySQL-embedded-version-platform.rpm

  The embedded server, libmysqld.

• MySQL-devel-version-platform.rpm

  Development support (client libraries and header files) for writing client programs. You'll need this RPM if you want to write your own C programs or Perl DBI scripts for accessing MySQL databases. MySQL APIs for other languages might depend on these client libraries and header files as well.

• MySQL-shared-version-platform.rpm

  Shared client libraries.

• MySQL-bench-version-platform.rpm

  Benchmarks and tests. These require Perl DBI support. (See "Installing Perl DBI Support on Unix.")

• MySQL-version.src.rpm

  The source for the server, clients, benchmarks, and tests.

You don't need to be in any particular directory when you install an RPM file, because RPMs include information indicating where their contents should be placed. For any RPM file rpm_file, you can find out where it will be installed with the following command:

% rpm -qpl rpm_file

To install an RPM file, use this command (you'll need to do this as root):

# rpm -i rpm_file

Various parts of MySQL are divided into different RPM files, so you may need to install more than one RPM. For a typical installation, you should install both the server and client RPMs. If you install only the server RPM, you won't be able to do much with it, because it doesn't include the client programs.

For server support, use these commands (skip the second one if you don't want the additional features provided by the Max server):

# rpm -i MySQL-version-platform.rpm
# rpm -i MySQL-Max-version-platform.rpm

To install the client programs, use this command:

# rpm -i MySQL-client-version-platform.rpm

If you plan to write your own programs using the client programming support, make sure to install the development RPM file:

# rpm -i MySQL-devel-version-platform.rpm

If you want to install MySQL from the source RPM file, the following command should be sufficient:

# rpmbuild --recompile --clean MySQL-version.src.rpm

Now proceed to the section "Post-Installation Steps."

Installing a Source Distribution

MySQL source distributions have names such as mysql-version.tar.gz, where version is the MySQL version number. (There is no platform in the filename because source distributions apply to all platforms.) Pick the directory under which you want to unpack the distribution and move into it. Obtain the distribution file and unpack it using one of the following commands (use the second command if your version of tar doesn't understand the z option):

% tar zxf mysql-version.tar.gz
% gunzip < mysql-version.tar.gz | tar xf -

Unpacking the distribution file creates a directory named mysql-version that contains the distribution's contents. Change location into that directory:

% cd mysql-version

Now you need to configure and compile the distribution before you can install it. If the following steps fail, check the installation chapter in the MySQL Reference Manual, particularly any platform-specific notes it contains about your type of system.

Use the configure command to configure the distribution:

% ./configure

You may want to specify options for configure. To obtain a list of available options, run this command:

% ./configure --help

The following list shows some common configuration options:

• --without-innodb

  Support for the InnoDB storage engine is included by default. This option excludes it, which makes the server smaller and causes it to use less memory. However, you should not use this option if you plan to use InnoDB tables.

• --with-berkeley-db

  Support for the BDB storage engine is excluded by default. This option includes it. You should use this option if you plan to use BDB tables, but check the MySQL Reference Manual first to verify that BDB is supported on your platform.

• --without-server

  Configure the distribution to build client support only (client programs or client libraries). You might do this if you're planning to access a server that's running on another machine.

• --with-embedded-server

  Build the embedded server library, libmysqld.

• --with-vio --with-openssl

  Configure the distribution to include OpenSSL support. This is necessary if you want to use encrypted connections between clients and the server.

• --prefix=dir_name

  By default, the installation base directory is /usr/local. The data directory, server, clients, client libraries, header files, manual pages, and language files are installed in the var, libexec, bin, lib, include, man, and share directories under the base directory. If you want to change the installation base, use the --prefix option. dir_name should be a full pathname. For example, to install everything under the /usr/local/mysql directory, use --prefix=/usr/local/mysql.

• --localstatedir=dir_name

  This option changes the location of the data directory. You can use this if you don't want to put your databases under the installation base directory. dir_name should be a full pathname.

• --with-low-memory

  The sql/sql_yacc.cc source file requires a lot of memory to compile, which sometimes causes the build process to fail, even on systems with generous amounts of RAM and swap space. Symptoms of this problem include error messages about "fatal signal 11" or exhaustion of virtual memory. The --with-low-memory option causes the compiler to be invoked with options that result in lower memory use.

After you run configure, compile the distribution and then install it:

% make
% make install

You might need to be root to run the install command if you didn't run configure with a --prefix option that specifies an installation directory in which you have write permission.

Now proceed to the section "Post-Installation Steps."

Post-Installation Steps

For a first-time installation, there are certain steps that you should take after you install MySQL:

If you are upgrading an existing MySQL installation to a newer version, you probably have already taken care of a number of those items already, such as setting your PATH and initializing the data directory. However, it's possible that you'll need to do these things:

• Update the server-side help tables to use the current help text.

• Set up the time zone tables, if your previous installation was from a version older than 4.1.3 that does not understand multiple time zones. These tables enable support for named time zones.

• Update your grant tables to make sure that they have the current structure.

The following sections describe how to perform these actions. Use those sections that apply to your situation.

Setting Your PATH Environment Variable

If you want to be able to invoke MySQL client programs from the command line without typing their full pathnames, set your PATH environment variable to include the bin directory located under the MySQL installation directory. Your shell uses this variable to determine where to look for commands. PATH usually is set in one of your shell's startup files, such as .tcshrc for tcsh or .bash_profile for bash or sh. For example, if you use tcsh, there might be a line in your .tcshrc file that looks like this:

setenv PATH /bin:/usr/bin:/usr/local/bin

If the MySQL client programs are installed in /usr/local/mysql/bin, change the value of PATH as follows:

setenv PATH /usr/local/mysql/bin:/bin:/usr/bin:/usr/local/bin

If you use bash or sh, one of your shell startup files might contain a line like this:

PATH=/bin:/usr/bin:/usr/local/bin

Change the setting to this:

PATH=/usr/local/mysql/bin:/bin:/usr/bin:/usr/local/bin

After you modify the shell startup file, the new setting will take effect each time you log in thereafter.

Initializing the Data Directory and Grant Tables

Before you can use your MySQL installation, you must initialize the mysql database that contains the grant tables controlling network access to your server. This step is needed only for a first-time installation and only if you will run a server. It is not needed for an upgrade to an existing installation or for a client-only installation.

In the following instructions, DATADIR represents the pathname to your data directory. It is typically located under your MySQL installation base directory and named data or var. Normally, you run the commands shown here as root. If you're logged in as the MySQL user (for example, mysql) or you've installed MySQL under your own account because you intend to run it for yourself, you can execute the commands without being root and should omit the --user option. You also can skip the chown and chgrp commands.

To initialize the data directory, the mysql database, and the default grant tables, change location into the MySQL installation directory and run the mysql_install_db script. (You need not do this if you are installing from RPM files or a Mac OS X package because they run mysql_install_db for you.) For example, if you installed MySQL into /usr/local/mysql, the commands look like this:

# cd /usr/local/mysql
# bin/mysql_install_db --user=mysql

If mysql_install_db fails, consult the installation chapter in the MySQL Reference Manual to see what it says about the problem you're encountering. Note that if mysql_install_db doesn't run to completion successfully, any grant tables it may have created are likely incomplete. You should remove them because mysql_install_db does not try to re-create any tables that it finds already created. You can remove the entire mysql database like this:

# rm -rf DATADIR/mysql

After running mysql_install_db, change the user and group ownership and the access mode of all files under the data directory. Assuming that the user and group names both should be mysql, the commands look like this:

# chown -R mysql DATADIR
# chgrp -R mysql DATADIR
# chmod -R go-rwx DATADIR

The chown and chgrp commands change the ownership to the MySQL login account user and group, and chmod changes the access mode to keep everybody out of the data directory except that user.

Starting the Server

This step is needed only if you plan to run a server. Skip it for a client-only installation. Run the commands in this section from the MySQL installation directory (just as for the commands in the previous section). Normally, you run the commands as root. If you're logged in as the MySQL user (for example, mysql) or you've installed MySQL under your own account, you can execute the commands without being root and should omit the --user option.

Change location into the MySQL installation base directory (for example, /usr/local/mysql), and then use the following command to start the server:

# cd /usr/local/mysql
# bin/mysqld_safe --user=mysql &

The --user option tells the server to run as mysql.

If you are installing MySQL for the first time on this machine, there are other actions that you'll probably want to perform at this point:

• The default installation allows anyone to use the MySQL root accounts without a password. For security reasons, it's a good idea to assign passwords to them.

• You can arrange for the server to start and stop automatically as part of your system's normal startup and shutdown procedures.

• You can put the --user option in an option file to avoid having to specify it each time you start the server.

• Various kinds of logging can be enabled. These are useful for monitoring the server, for replication, and for data recovery procedures.

• You can enable or disable storage engines, or specify tuning parameters for them.

Instructions for performing these actions are given in Chapter 11, "General MySQL Administration."

Installing or Upgrading Additional System Tables

In MySQL 4.1 and up, the mysql command-line client can access server-side help via the help command. For this to work, the help tables in the mysql database must be set up. For an install using a binary distribution or RPM files, the help tables are set up for you automatically. Otherwise, you can load them manually. If you are upgrading, you can reload the tables to populate them with updated help text.

To load the help tables, make sure that the server is running. Then locate the fill_help_tables.sql file, which contains SQL statements that create and load the tables. Likely locations are in /usr/share/mysql, the share directory under the MySQL base installation directory, or the scripts directory of a source distribution. After you find the file, execute the following command in the directory where the file is located:

% mysql -p -u root mysql < fill_help_tables.sql

The command will prompt you for the root account password. Omit the -p option if you have not yet set up a password.

To set up the time zone tables that are needed for named time zone support, use the instructions in "Configuring Time Zone Support," in Chapter 11.

If you are upgrading a MySQL installation, it is possible that the grant table structure has changed since your original installation. To update the tables to the current structure, use the instructions in "Dealing with Changes to Grant Table Structure," in Chapter 11.

Installing Perl DBI Support on Unix

Install the DBI software if you want to write Perl scripts that access MySQL databases:

• You must install the DBI module that provides the general DBI driver, and the DBD::mysql module that provides the MySQL-specific driver. DBI requires Perl 5.6.0 or later. (If you don't have Perl installed, visit http://www.perl.com/, download a Perl distribution, and install it before you install DBI support.)

• The MySQL C client libraries and header files must be available as well, because DBD::mysql uses them. They should have been installed as part of the MySQL installation procedure.

• You can also install the CGI.pm module if you want to write Web-based DBI scripts.

To find out whether a given Perl module is already installed, use the perldoc command. If the module is installed, perldoc will display its documentation:

% perldoc DBI
% perldoc DBD::mysql
% perldoc CGI

The easiest way to install Perl modules under Unix is to use the CPAN shell. Issue the following commands as root:

# perl -MCPAN -e shell
cpan> install DBI
cpan> force install DBD::mysql
cpan> install CGI

The installation command for DBD::mysql uses force install to cause installation to proceed even if the test phase fails. The tests assume that they can connect to a server running on the local host using an anonymous-user MySQL account with no password. That means the tests fail unless you happen to have an insecure account. (If you do have such an account, the tests should succeed and you can omit force, but you really should assign passwords to all your MySQL accounts.)

Another way to install Perl modules is to download source distributions from cpan.perl.org as compressed tar files. Unpack a distribution file dist_file.tar.gz using one of the following commands (use the second command if your version of tar doesn't understand the z option):

% tar zxf dist_file.tar.gz
% gunzip < dist_file.tar.gz | tar xf -

Then change location into the distribution directory created by the tar command and run these commands (you might need to be root to run the installation step):

% perl Makefile.PL
% make
% make test
# make install

The make test command will fail unless you have an insecure anonymous-user MySQL account, as described earlier in this section. If you want to use a different account, run perl Makefile.PL --help to see how. Otherwise, just ignore failed tests and proceed to the installation command.

If you have problems installing the Perl modules, consult the README file for the relevant distribution, as well as the mail archives for the DBI mailing list. The answers for most installation problems can be found there.

Installing Apache and PHP on Unix

The following instructions assume that you'll run PHP as a dynamic shared object (DSO) module using the Apache httpd server. This means that Apache should be installed first, and then you can build and install PHP. If Apache is not installed on your system already, you can either install a binary distribution that has DSO support enabled, or else compile a source distribution to include DSO support. After Apache has been installed, configure your PHP distribution using the following command:

% ./configure --with-apxs=/path/to/apxs mysql_options --with-zlib

/path/to/apxs tells configure where to find apxs, the apxs Apache Extension Tool helper script that provides other modules with information about your Apache configuration. If configure can determine the location of apxs for itself, you can give this option as --with-apxs without a pathname.

The value of mysql_options should be chosen to tell configure what kind of MySQL support you want:

• For PHP 4, you can use --with-mysql to indicate that PHP should enable the mysql extension for MySQL client support. If configure cannot determine where the header files and libraries are located, add a pathname; for example, --with-mysql=/usr/local/mysql.

• For PHP 5, you can enable the MySQL extension in the same way as for PHP 4. You also can enable the mysqli "MySQL improved extension" that has support for the binary client/server protocol for prepared statements introduced in MySQL 4.1. (If you want to use both mysql and mysqli, you must have MySQL 4.1.2 or newer.) To use mysqli, add a --with-mysqli=/path/to/mysql_config option to the configure command. /path/to/mysql_config is the pathname to your mysql_config script. configure invokes the script to obtain MySQL configuration information.

The --with-zlib option usually is needed because the MySQL client library accesses compression functions.

After configuring PHP, build and install it as follows (you might need to be root to perform the installation commands):

% make
# make install
# cp php.ini-dist /usr/local/lib/php.ini

The cp command installs a baseline PHP initialization file where PHP can find it. You can substitute php.ini-recommended for php.ini-dist if you like; take a look at both and choose the one you prefer.

The PHP installation includes a program named pear that provides information about which PEAR modules are installed and that helps you install other modules. For a help message, invoke pear without any arguments. To see which modules are installed, or a list of all available modules, use these commands:

% pear list
% pear list-all

To install the DB module if it is not installed, use the following command (which likely must be run as root):

# pear install DB

After PHP and PEAR DB have been installed, edit the Apache configuration file, httpd.conf. You'll need to instruct Apache to load the PHP module when it starts, and also how to recognize PHP scripts. (httpd.conf might include other files via Include directives. If you don't see the information described in the following paragraphs, check any included files as well.)

To tell Apache to load the PHP module, httpd.conf will need to include LoadModule and AddModule directives in the appropriate sections (look for other similar directives). The directives might already have been added for you during the installation step. If not, you must add them yourself. They should look something like this, although the pathname in the LoadModule directive might need adjustment for your system:

LoadModule php4_module libexec/libphp4.so
AddModule mod_php4.c

The directives should use php5 rather than php4 if you are using PHP 5 rather than PHP 4.

Next, edit httpd.conf to tell Apache how to recognize PHP scripts. PHP recognition is based on the filename extension that you use for PHP scripts. The most common extension is .php, which is the extension used for examples in this book. To enable .php as the PHP script extension, include the following line in the httpd.conf file:

AddType application/x-httpd-php .php

You can also tell Apache to recognize index.php as an allowable default file for a directory when no filename is specified at the end of a URL. You'll probably find a line in httpd.conf that looks like this:

DirectoryIndex index.html

Change it to this:

DirectoryIndex index.php index.html

After editing the Apache configuration file, stop the httpd server if it was already running, and then restart it. On many systems, commands such as those following accomplish this (executed as root):

# /usr/local/apache/bin/apachectl stop
# /usr/local/apache/bin/apachectl start

You can also set up Apache to start and stop at system startup and shutdown time. See the Apache documentation for instructions. Normally, this involves running apachectl start at boot time and apachectl stop at shutdown time.

If you encounter problems setting up PHP, check the "VERBOSE INSTALL" section of the INSTALL file included with the PHP distribution. (It's a good idea to read that file anyway. It contains lots of useful information.)