C.2 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 C.2.1.

  1. Download MySQL. Using a web browser, 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. On the release page, find the heading Mac OS X Package Installer downloads (not Mac OS X downloads!). Click on download next to the Standard option. Save the file to the desktop.

    At the time of writing, choosing which release to use is difficult. We recommend Server version 4.0, as you can use all of the book except some of the new features of MySQL 4.1 that are discussed in Chapter 15.

    If you choose Server version 4.1 or later, then the material in this book that relies on the standard MySQL library can't be used at the time of writing. The standard library is discussed in Chapter 6, and the standard library supports the PEAR DB package described in Chapter 7 and used in the example application in Chapter 16 to Chapter 20. It's likely that this will be remedied in the future, and that downloading 4.1 will become a good option. See Appendix H for more information.

  2. After the download completes, double-click on the downloaded file that's stored on the desktop; the file has a name such as mysql-standard-4.0.17.dmg.This mounts the image file: a dialog will show progress and then the Finder will appear showing two packages and a ReadMe.txt file.

  3. Double-click the install package in the Finder. This is the package named, for example, mysql-standard-4.0.17.pkg. The Installer will run.

  4. The first step in the installation is an Introduction page. Click Continue.

  5. The second step is viewing the Read Me file. This has few useful hints, so you might find it useful to save the file somewhere for later reference but there's no requirement to do so. Click on Continue.

  6. The third step is the license. Click Continue.

  7. The fourth step is to select a destination volume (it defaults to Mac OS X). Unless you have a reason to change it, click Continue.

  8. The fifth step is the Easy Install page. Click Install. You may then need to enter the root user password you set up in the previous section. Now, wait while the software installs.

  9. The final step is the Finish up page. Click Close.

  10. Install the StartupItem package. This configures your machine so that the MySQL server will start when your machine is turned on. To do this, double click the StartupItem package in the Finder; this is the package named, for example, MySQLStartupItem.pkg. The Installer will run. Now repeat the six steps from Steps 6 to 11 for the StartUpItem.

  11. MySQL is now installed but isn't yet configured. To configure it, start by opening Terminal, which is located in Applications Utilities. This opens a terminal window which we recommend maximizing. In the following steps that require the Terminal, we show commands that are entered next to a prompt, %. To enter these commands, type them into your Terminal window and press Enter.

  12. Log in as the root user. You can login as root by typing:

    % su

    and then supplying the root user password that you set when enabling the root user in the previous section.

  13. Run the mysql_install_db script to initialize the system databases used by MySQL. To do this, change directory to where MySQL is installed:

    % cd /usr/local/mysql

    Type the following to run the installation script:

    % ./scripts/mysql_install_db

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

    % chown -R root /usr/local/mysql

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

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

    This user already exists in your Panther installation.

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

    % chgrp -R mysql /usr/local/mysql

    This group already exists in your Panther installation.

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

  18. 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, let's turn on the query cache. To do this, we'll use the pico editor that comes installed with Mac OS X; we don't use the popular TextEdit editor because it has trouble saving files when you're the root user and when the file exists.

    To edit the configuration file with pico, type:

    % pico /etc/my.cnf

    Using the down arrow key, scroll down through the file until you find section beginning with the following text:

    # The MySQL server

    In this section, type the following lines:

    set-variable = max_connections=150
    query_cache_type = 1

    Save the file by holding down the ctrl (Control) key and pressing the o key (we refer to this as ctrl-o throughout this appendix). Then press Enter. Quit pico by holding ctrl and pressing x (ctrl-x).

  19. The installation of MySQL is now complete, but it isn't running. To get it running, restart your machine by clicking on the Apple menu and then Restart.

MySQL isn't completely configured. The steps in the next section set it up further.

C.2.1 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 DBMS. The steps assume you've run Terminal, logged in as the root user, and that MySQL is running; see the previous section for more information.

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, type into your Terminal window:

    % /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

    This displays a mysql> prompt.

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

      username@ 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:


  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 The winestore database can't be tested yet because it isn't loaded. The database is loaded in Section C.4.