Installing MySQL on UNIX | High Performance MySQL: Optimization, Backups, Replication, and More

Distributions are available for several versions of MySQL. Currently, stable releases have version numbers in the 4.0 series and development releases are in the 4.1 series. Generally, you should use the highest-numbered version in the series you want to use that is available in the distribution format you want to work with.

MySQL distributions come in binary, RPM, and source formats. Binary and RPM 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 for client support only without building the server, and you can install the software wherever you want.

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)
Documentation
The benchmark database
Language support

Source and binary distributions contain all of these items. RPM files contain only some of them, so you may 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 clients 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:

If you are going to install a server, create a login account for the user and group that you'll use for running it. (This is for a first-time installation only, not for an upgrade to a newer version.)
Obtain and unpack any distributions you want to install. If you are using a source distribution, compile it and install it.
Run the mysql_install_db script to initialize the data directory and grant tables. (This is for a first-time installation only, not for an upgrade to a newer version.)
Start the server.
Read Chapter 11, "General MySQL Administration," to become familiar with general administrative procedures. In particular, you should read the sections on server startup and shutdown and on running the server using an unprivileged user account.

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 running 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 mysqladm and mysqlgrp for 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 as yourself, in which case you'll use your own login and group names wherever you see mysqladm and mysqlgrp in this book. If you use RPM files, the RPM installation procedure will create a login account for a user named mysql automatically for you. In that case, you will want to substitute mysql for mysqladm.

Advantages of using a separate, unprivileged account rather than root for running MySQL are as follows:

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.
The server will create files owned by mysqladm rather than by root. The fewer root-owned files on your system, the better.
It's cleaner conceptually 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, mysqladm. 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 name of the platform on which you're installing it. These are used in distribution filenames so that distributions can be identified easily and distinguished from one another. A version number is something like 3.23.52 or 4.0.5-beta. A platform name is something like sun-solaris2.8-sparc or pc-linux-gnu-i686.

Binary distribution files that have -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 currently differ. If you're compiling from source, you enable or disable features by running the configure script with the appropriate options.

Installing a Binary Distribution

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

Now you can refer to the installation directory as /usr/local/mysql if you installed MySQL under /usr/local.

To allow invocation of 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 path is set in one of your shell's startup files.

If you are going to use only the client support provided by the distribution and are not running a server, you're done installing MySQL. If you are installing MySQL for the first time, go to the "Initializing the Data Directory and Grant Tables" section later in this appendix. If you are updating an existing installation, go to the "Starting the Server" section later in this appendix.

Installing an RPM Distribution

RPM files are available for installing MySQL on Linux systems. These have filenames like the following:

MySQL-client-version-platform.rpm The client programs.
MySQL-version-platform.rpm The server software.
MySQL-Max-version-platform.rpm Additional server software to add extra features. Before installing this, you must install the corresponding MySQL-version-platform.rpm file first.
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 if you want to use or write Perl DBI scripts for accessing MySQL databases.
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" later in this appendix.)
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 from an RPM file because RPMs include information indicating where their contents should be installed. For any RPM file rpm_file, you can determine where it will be installed with the following command:

 % rpm -qpl rpm_file

To install an RPM file, use the following command (you'll probably 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. To install client support, use the following command:

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

For server support, use the following 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

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 from the source RPM file, the following command should be sufficient:

 # rpm  recompile MySQL-version.src.rpm

If you plan to use only the client support provided by the distribution and are not running a server, you're done installing MySQL. If you are installing MySQL for the first time, go to the "Initializing the Data Directory and Grant Tables" section later in this appendix. If you are updating an existing installation, go to the "Starting the Server" section later in this appendix.

Installing a Source Distribution

MySQL source distributions have names such as mysql-version.tar.gz, where version is the MySQL version number. 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 a distribution file creates a directory named mysql-version that contains the distribution's contents; change the 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 system-specific notes it may contain about your type of machine.

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 the following command:

 % ./configure --help

The following list shows some configuration options many people find helpful:

--with-innodb, --without-innodb Include or exclude support for InnoDB tables. Prior to MySQL 4, use --with-innodb to include the InnoDB handler. As of MySQL 4, InnoDB is included by default; use --without-innodb to exclude it.
--with-berkeley-db Include support for BDB tables.
--without-server Configure for building client support only (client programs or client libraries). You might do this if you're planning to access a server that's already running on another machine.
--with-embedded-server Build the embedded server library, libmysqld.
--prefix=path_name By default, the installation root directory is /usr/local. The data directory, clients, the server, client libraries, header files, manual pages, and language files are installed in the var, bin, libexec, lib, include, man, and share directories under this directory. If you want to change the installation root, use the --prefix option. For example, to install everything under the /usr/local/mysql directory, use --prefix=/usr/local/mysql.
--localstatedir=path_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 root directory.
--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 may 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.

If you are going to use only the client support provided by the distribution and are not running a server, you're done installing MySQL. If you are installing MySQL for the first time, go to the "Initializing the Data Directory and Grant Tables" later in this appendix. If you are updating an existing installation, go to the "Starting the Server" section later in this appendix.

Initializing the Data Directory and Grant Tables

Before you can use your MySQL installation, you need to 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 client-only installations.

In the following instructions, DATADIR represents the pathname to your data directory. Normally, you run the commands shown here as root. If you're logged in as the MySQL user (that is, mysqladm) 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. In that case, you can skip the chown and chmod 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 because mysql_install_db will be run for you automatically.) For example, if you installed MySQL into /usr/local/mysql, the commands look like this:

 # cd /usr/local/mysql  # ./bin/mysql_install_db

If mysql_install_db fails, consult the installation chapter in the MySQL Reference Manual to see if it says anything about the problem you're encountering, and then try again. Note that if mysql_install_db doesn't run to completion successfully, any grant tables it creates are likely incomplete. You should remove them because mysql_install_db may not try to re-create any tables that it finds already existing. You can remove the entire mysql database as follows:

 # rm -rf DATADIR/mysql

After running mysql_install_db, change the user and group ownership and the mode of all files under the data directory. Assuming that the user and group are mysqladm and mysqlgrp, the commands are as follows:

 # chown -R mysqladm.mysqlgrp DATADIR  # chmod -R go-rwx DATADIR

The chown command changes the ownership to the MySQL user, and chmod changes the 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 (that is, mysqladm) 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 directory (for example, /usr/local/mysql) and then use the following command to start the server:

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

The --user option tells the server to run as mysqladm. (Note that prior to MySQL 4, mysqld_safe is named safe_mysqld.)

There are other actions that you'll probably want to perform at this point:

The default installation allows the MySQL root user to connect without a password. It's a good idea to establish a password for security reasons.
You can arrange for the server to start up and shut down 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 and for data-recovery procedures.
For a server that supports InnoDB tables, you can configure the InnoDB table handler.

Instructions for performing these actions are given 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.005_003 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 library must be available as well because DBD::mysql uses it. (The C library should have been installed as part of the MySQL installation procedure.) You also can install the CGI.pm module if you want to write Web-based DBI scripts.

To find out if a given Perl module is already installed, use the perldoc command; if a 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> install DBD::mysql cpan> install CGI

You also can 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 the following commands (you may need to be root to run the installation step):

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

Whichever installation method you choose, Perl will ask you some questions when you install the DBD::mysql module:

Which drivers do you want to install? There are choices for various combinations of MySQL and mSQL. Unless you also run mSQL, select only MySQL to keep things simple.
Do you want to install the MysqlPerl emulation? MysqlPerl is the old Perl interface for MySQL. It is obsolete, so answer No to this question unless you have old MysqlPerl scripts and want to enable emulation support for them in the DBI module.
Where is your MySQL installed? This should be the grandparent of the directory containing your MySQL header files. The location is likely something like /usr/local or /usr/local/mysql unless you installed MySQL in a non-standard place.
Which database should I use for testing the MySQL drivers? The default is test, which should be okay unless you've removed the anonymous-user entries from the MySQL grant tables. In that case, you'll need to give the name of a database to which you have access and then specify a valid MySQL username and password for the final two questions.
On which host is the database running? localhost should be sufficient if you're running a local server. If not, name a host that is running a server to which you have access. The MySQL server must be running on the host that you name when you run the make test command or the tests will fail.
Username for connecting to the database? Password for connecting to the database? The name and password to use for connecting to the MySQL server for testing. The default is undef for both questions, which causes the driver to connect as the anonymous user. Specify non-blank values if you need to connect non-anonymously.

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 DSO (dynamic shared object) 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 compile a source distribution to include DSO support. After Apache has been installed, configure your PHP distribution by using one of the following commands:

 % ./configure --with-mysql --with-apxs  % ./configure --with-mysql --with-apxs=/path/to/apxs

The --with-mysql option indicates that you want to build PHP to include MySQL client support. This option must be included for MySQL-based PHP scripts to work correctly. If the first command fails because configure cannot tell where the apxs script is located, use the second command to indicate its location explicitly. (The apxs Apache Extension Tool is a helper script that provides other modules with information about your Apache configuration.) After configuring PHP, build and install it as follows (you may 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.

After PHP is installed, edit the Apache configuration file, httpd.conf. You'll need to instruct Apache to load the PHP module when it starts up and also how to recognize PHP scripts.

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 may be added for you during the installation step. If not, they should look something like the following:

 LoadModule php4_module libexec/libphp4.so  AddModule mod_php4.c

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. .php is the most common extension and is the one used for examples in this book. Another popular extension is .phtml. You can simply enable them both if you want; that may be best anyway if you're going to be installing pages that you obtain from other people that use one or another of these extensions. To do so, include the following lines in the configuration file:

 AddType application/x-httpd-php3 .php  AddType application/x-httpd-php3 .phtml

You can also tell Apache to recognize index.php and index.phtml as allowable default files 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 the following:

 DirectoryIndex index.html

Change it to

 DirectoryIndex index.php index.phtml index.html

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

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

You can also set up Apache to start up and shut down 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 not a bad idea to read that file anyway. It contains lots of useful information.)