A.3 Installing MySQL

This section shows you how to install and configure your MySQL 4 DBMS server. When you've completed this section, continue on to Section A.3.1 and Section A.3.2.

  1. Get a shell prompt (by running a terminal) and log in as the root user. You can log in as root by typing su and then supplying the root user password.

  2. If you determined earlier in Section A.1 that a MySQL server is already running, stop it using:

    % /usr/local/mysql/bin/mysqladmin -uroot -ppassword shutdown

    If your MySQL isn't installed in this directory, replace the directory with the correct one you noted earlier. If this doesn't work, try omitting -ppassword from the command.

  3. Download MySQL. At the time of writing, we recommend MySQL 4.0 for the reasons discussed in Section A.1.

    To download MySQL, visit http://www.mysql.com/downloads/. Under the heading MySQL database server & standard clients, click on the link that's marked as the production release. From the release page, choose the option under "Source Downloads" marked "tarball (.tar.gz)". Download the file into a directory where files can be created and there is sufficient disk space. A good location is /tmp.

    The mysql.com web site recommends you use their precompiled versions of MySQL, rather than creating one yourself. You can do this, and it usually works, but occasionally you'll find you don't have all of the support libraries and versions you need for a successful installation. We therefore recommend that the most reliable option, despite the warning, is to build your own version from source code.

  4. Move the MySQL source code file to the base directory of the desired installation. The most common location is /usr/local/src and, assuming the distribution downloaded is MySQL 4.0.17, and it was downloaded in the first step into the /tmp directory, the command is:

    % mv /tmp/mysql-4.0.17.tar.gz /usr/local/src

  5. After moving the distribution to the desired location, change the directory to that location using:

    % cd /usr/local/src

  6. Uncompress the package in the new installation directory by running:

    % gzip -d mysql- version .tar.gz

    If MySQL 4.0.17 has been downloaded, the command is:

    % gzip -d mysql-4.0.17.tar.gz

  7. Un-tar the tape archive file by running:

    % tar xvf mysql- version .tar

    A list of files that are extracted is shown. If the version downloaded is MySQL 4.0.17, the command is:

    % tar xvf mysql-4.0.17.tar

  8. Change directory to the MySQL distribution directory:

    % cd mysql- version

    If the version is MySQL 4.0.17, type:

    % cd mysql-4.0.17

  9. Add a new Linux group account for the MySQL files:

    % groupadd mysql

  10. Add a new Linux user who is a member of the newly created group mysql:

    % useradd -g mysql mysql

  11. Decide on an installation directory. Later, we recommend that PHP and Apache be installed in /usr/local/, so a good choice is /usr/local/mysql/. We assume throughout these steps that /usr/local/mysql/ is used.

  12. Configure the MySQL installation by running the configure script. This detects the available Linux tools and the installation environment for the MySQL configuration:

    % ./configure --prefix=/usr/local/mysql

  13. Compile the MySQL DBMS:

    % make

  14. Install MySQL by running the command:

    % make install

  15. MySQL is now installed but isn't yet configured. Now, run the mysql_install_db script to initialize the system databases used by MySQL:

    % ./scripts/mysql_install_db

  16. Change the owner of the MySQL program files to be the root user:

    % chown -R root /usr/local/mysql

  17. Change the owner of the MySQL databases and log files to be the mysql user:

    % chown -R mysql /usr/local/mysql/var

  18. Change the group of the MySQL installation files to be the mysql group:

    % chgrp -R mysql /usr/local/mysql

  19. Copy the default medium-scale parameter configuration file to the default location of /etc. These parameters are read when MySQL is started. The copy command is:

    % cp support-files/my-medium.cnf /etc/my.cnf

  20. Edit the configuration file and adjust the default number of maximum connections to match the default value for the maximum Apache web server connections. Also, activate the query cache. Using a text editor, edit the file /etc/my.cnf, and find the section beginning with the following text:

    # The MySQL server
    
    [mysqld]

    In this section, add the following lines, save the file, and exit the editor:

    set-variable = max_connections=150
    
    query_cache_type = 1

The installation of MySQL is now complete, but it isn't completely configured and the server isn't running. The steps in the next two sections start the DBMS, and set it up further.

A.3.1 Starting MySQL

In the previous section, you installed MySQL. Now, you need to get it running. Follow these steps:

  1. If you're not logged in as the root user, get a shell prompt (by running a terminal) and log in as the root user. You can do this by typing su and then supplying the root user password.

  2. Now that MySQL is installed, you can start it with the following command:

    % /usr/local/mysql/bin/mysqld_safe --user=mysql &

  3. Check that the MySQL DBMS is running with the mysqladmin utility. The following command reports statistics about the MySQL DBMS version and usage:

    % /usr/local/mysql/bin/mysqladmin version

    If you have already defined a password secret, you'll need to insert -psecret before the version parameter; adding a password is discussed in the next section.

  4. When the machine is rebooted, MySQL doesn't restart automatically. After reboot, you can use the previous step to restart MySQL or, alternatively, this process can be made automatic. If you know the standard method to add an automatically-started service on your system, add the startup command.

    One common standard method to make the process automatic is to add commands to the file rc.local (normally either in or below the directory /etc). The rc.local file is used to list locally installed software that should be run on startup. Using an editor, add the following line to the bottom of the rc.local file, save the file, and exit your editor:

    /usr/local/mysql/bin/safe_mysqld --user=mysql &

  5. If you need to stop MySQL, use the following command:

    % /usr/local/mysql/bin/mysqladmin shutdown

    If you have already defined a password secret, you'll need to insert -psecret before the shutdown parameter; adding a password is discussed in the next section.

A.3.2 Configuring MySQL

The following steps set a password for the root user and create a new user for the MySQL installation that is used in PHP scripts to access the server. The steps assume you're logged in as the root user and that MySQL is running; see the previous section if this isn't true.

The new user can carry out all actions required from Chapter 6 to Chapter 20 on the winestore database but has no access to other databases and can't change database access privileges. The new user also can't access the DBMS from a remote server. If you're creating your own application, replace winestore with the name of your database. More information on user privileges can be found in Chapter 15.

The steps are as follows:

  1. Choose and set a password for root user access to the MySQL DBMS. To set a password of secret, use:

    % /usr/local/mysql/bin/mysqladmin -uroot password secret

    Record the password for later use.

  2. Start the MySQL command line interpreter using the password defined in the previous step:

    % /usr/local/mysql/bin/mysql -uroot -psecret

  3. Add a new user. Choose a username to replace username and a password to replace password in the following command:

    GRANT SELECT, INSERT, UPDATE, DELETE, LOCK TABLES ON winestore.* TO
    
       username @127.0.0.1 IDENTIFIED BY 'password';

    It doesn't matter that the winestore database doesn't exist yet.

    MySQL responds with:

    Query OK, 0 rows affected (0.00 sec)

    Record the username and password.

    If the statement doesn't work for your release, try it again but omit the , LOCK TABLES component.

  4. Quit the MySQL command interpreter with the command:

    mysql> quit

    MySQL responds with:

    Bye

  5. Test the user by running the MySQL command interpreter using the username and password:

    % /usr/local/mysql/bin/mysql -uusername -psecret

  6. MySQL responds with a message beginning:

    Welcome to the MySQL monitor.

  7. Quit the MySQL interpreter again with:

    mysql> quit

The MySQL DBMS is now configured with a user who can access the winestore database from the database server machine 127.0.0.1. The winestore database can't be tested yet as it isn't loaded. The database is loaded in Section A.6.