MySQL Configuration and Installation By McGraw-Hill/Osborne (2005-06-02)
 
 

Table of Contents:

  • MySQL Configuration and Installation
  • Installing and Configuring MySQL on UNIX
  • Installing MySQL on UNIX from a Binary Tarball Distribution
  • Installing MySQL on UNIX from a Source Distribution
  • Installing and Configuring MySQL on Windows
  • Installing MySQL on Windows from a Source Distribution
  • Testing MySQL

    MySQL Configuration and Installation - Installing and Configuring MySQL on UNIX

    MySQL is available in binary form for almost all versions of UNIX and can even be compiled for those UNIX variants for which no binary distribution exists. This section will discuss installing and configuring MySQL on Linux using both source and binary distributions; the process for other UNIX variants is similar, although you should refer to the documentation included with the MySQL distribution for platform-specific notes.

    Installing MySQL from a Binary RPM Distribution

    The recommended way to install MySQL on a Linux system is via RPM. MySQL AB makes the following RPMs available for download on its web site:
    • MySQL The MySQL database server, which manages databases and tables, controls user access, and processes SQL queries
    • MySQL-client MySQL client programs, which makes it possible to connect to, and interact with, the server.

      Needful Things

      MySQL software distributions are usually packaged in Zip, tar (tape archive), or RPM (RPM Package Manager) format, and they can range from 7 to 20 MB in size in compressed form, and up to 100 MB in size in uncompressed form. Depending on the format you select, you will need appropriate unpackaging tools to extract the files from the source archive. For Zip and tar files, you will need GNU tar and GNU gunzip, available from http://www.gnu.org.
      For RPM files, you will need rpm, available from http://www.rpm.org.
      Additionally, if youre planning on compiling and installing MySQL from a source distribution, you will need a C++ compiler like gcc on UNIX or Visual C++ on Windows. The gcc compiler is available from http://www.gnu.org/software/
      gcc, while Visual C++ is available from http://msdn.microsoft.com/visualc.

    • MySQL-devel Libraries and header files that come in handy when compiling other programs that use MySQL.
    • MySQL-shared Shared libraries for the MySQL client.
    • MySQL-bench Benchmark and performance testing tools for the MySQL database server.
    The MySQL RPMs listed here are all built on a SuSE Linux system, but they’ll usually work on other Linux variants with no difficulty.

    Installing an RPM distribution of MySQL is extremely simple, and it involves running only a single command—the rpm command—for each RPM you wish to install. Here’s how you go about doing it:

    1. First ensure that you’re logged in as root:
      [user@host] # su – root
    2. Switch to the directory containing the RPMs:
      [root@host] # cd /tmp
    3. Install the MySQL database server by executing the following command (remember to replace the filename in italics with the file name of your RPM):
      [root@host] # rpm -i MySQL-4.0.9-0.i386.rpm
    RPM does the following things to get MySQL up and running on your system:
    • Copies the MySQL binaries to appropriate locations on your system (usually, binaries go to /usr/bin and /usr/sbin, while databases and tables are stored in /var/lib/mysql)

    • Adds a mysql user/group to the system to handle all MySQL-related operational and administrative tasks

    • Alters ownership of the MySQL binaries so that they are owned by the mysql user/group

    • Creates and initializes the MySQL grant tables

    • Adds appropriate entries to your system’s startup scripts so that the MySQL server starts up automatically at boot time

    • Starts the server so that you can begin using it immediately Figure 3-1 shows a snippet of what you might see during the installation process:

    4. Now install the remaining RPMs in a similar manner:
      [root@host]# rpm -i MySQL-client-4.0.9-0.i386.rpm 
      [root@host]# rpm -i MySQL-devel-4.0.9-0.i386.rpm
      [root@host]# rpm -i MySQL-shared-4.0.9-0.i386.rpm  
      [root@host]# rpm -i MySQL-bench-4.0.9-0.i386.rpm
    Figure 3-2. displays what you should see while performing this task.


    Figure 3-1. Installation of the MySQL server via RPM

    Note that it’s necessary to install only the server; however, I would recommend that you install the client as well so that you can interact with the server from the system console. The benchmark utilities should be installed only if you plan to test MySQL performance, while the libraries and header files come in handy when you’re compiling other utilities or tools that use MySQL (for example, the PHP scripting language).

    Once installation has been successfully completed, you should move later in the chapter to the section titled “Testing MySQL” to verify that everything is working as it should.


    Figure 3-2. Installation of ancillary MySQL tools and utilities via RPM

    MySQL Configuration and Installation - Installing MySQL on UNIX from a Binary Tarball Distribution

    In case you’re using a Linux distribution that doesn’t support RPM, you can also install MySQL using a binary tarball from the MySQL web site.

    Installing from a binary distribution essentially means that you need to perform the installation steps manually rather than letting RPM automatically take care of it for you. Here’s how you go about doing it:

    1. Ensure that you’re logged in as root:
      [user@host]# su - root

    2. Extract the content of the tarball to an appropriate directory on your system—I’ll assume this location is /usr/local/. Remember to replace the file name in italics with the file name of your tarball.
      [root@host]# cd /usr/local
      [root@host]# tar -xzvf
      mysql-standard-4.0.9-gamma-pc-linux-i686.tar.gz
    The MySQL files should get extracted into a directory named according to the format mysql-version-os-architecture—for example, mysql-standard-4.0.9-gamma-pc-linux-i686.

    3. Now you’ll notice that the directory created in the previous step has a somewhat long and cumbersome directory name— something like mysql-standard-4.0.9-gamma-pc-linux-i686. For ease of use, create a soft link to this directory named mysql in the same location.

      [root@host]# ln -s mysql-standard-4.0.9-gamma-pc-linux-i686 mysql

    4. Change into this directory, and take a look at how the files are arranged. You should see something like Figure 3-3. (Take a look at the sidebar entitled “Up a Tree” for more information on what each directory contains.)


    Figure 3-3. The directory structure obtained on unpackaging of a MySQL binary tarball on Linux

    Up a Tree
    If you have the time (and the inclination), you might find it instructive to explore
    the MySQL directory structure to help you better understand where the important files are located.

    For a binary distribution, the directory structure for a typical MySQL installation looks like this:

    <mysql-install-root>
    |-- bin          [client and server binaries]
    |-- data         [databases and error log]
    |-- include   [header files]
    |-- lib          [compiled libraries]
    |-- man          [manual pages]
    |-- mysql-test   [test suite]
    |-- share        [error messages in different languages]
    |-- scripts      [startup, shutdown and initialization scripts]
    |-- sql-bench    [queries and data files for benchmark tests]
    |-- support-files[sample configuration files]
    |-- tests        [test cases]

    For a source distribution, the directory structure for a typical MySQL installation looks like this:

    <mysql-install-root>
    |-- bin           [client binaries]
    |-- libexec       [server binaries]
    |-- var           [databases and error log]
    |-- lib           [compiled libraries]
    |-- include       [header files]
    |-- info          [info pages]
    |-- man           [manual pages]
    |-- mysql-test [test suite]
    |-- share         [error messages in different languages]
    |-- sql-bench     [queries and data files for benchmark tests]

    Take a look at the documentation that ships with the MySQL distribution for a more detailed discussion of this directory structure.

    5. The MySQL database server can run as either the system root user or any other user on the system. From the security point of view, it’s considered a bad idea to run the MySQL database server as root ; hence, it becomes necessary for you to create a special mysql user and group for this purpose.

    You can accomplish this using the groupadd and useradd commands:

      [root@host]# groupadd mysql
      [root@host]# useradd –g mysql mysql
    6. Run the initialization script, mysql_install_db, that ships with the program:
      [root@host] # /usr/local/mysql/scripts/mysql_install_db

      Figure 3-4 demonstrates what you should see when you do this.:

      As you can see from the output in the figure, this initialization script prepares and installs the various MySQL base tables and also sets up default access permissions for MySQL.
    7. Alter the ownership of the MySQL binaries so that they are owned by root:
      [root@host]# chown -R root /usr/local/mysql
    8. Now ensure that the newly-minted mysql user has read/write access to the MySQL data directories:
      [root@host]# chown -R mysql /usr/local/mysql/data
      [root@host]# chgrp -R mysql /usr/local/mysql
    9. Start the MySQL server by manually running the mysqld daemon:
      [root@host]# /usr/local/mysql/bin/mysqld_safe –user=mysql &

      MySQL should start up normally, reading the base tables created in /usr/local/mysql/data.


      Figure 3-4.       The output of running the MySQL initialization script

    Once installation has been successfully completed, you can skip to the section titled “Testing MySQL,” later in this chapter, to verify that your server is functioning properly.

    MySQL Configuration and Installation - Installing MySQL on UNIX from a Source Distribution

    If you’re planning to install MySQL from a source distribution, you’ll need to untar the source tree and go through the traditional configure-make-make install cycle to get MySQL up and running. This is a fairly time-consuming and complex process, and it’s one that shouldn’t really be attempted by novice users; however, if you’re determined to do it, here’s how:

    1) Ensure that you’re logged in as root:

      [user@host]# su - root
    2) Switch to the directory containing the source tarball, and extract the files within it. (Note that you will need approximately 80 MB of free space for the source tree.)
      [root@host]# cd /tmp
      [root@host]# tar -xzvf mysql-4.0.9-gamma.tar.gz

      Remember to replace the file name in italics with the file name of your source tarball.
    3. Move into the directory containing the source code,
      [root@host]# cd mysql-4.0.9-gamma
      and take a look at the contents with ls:
      [root@host]# ls -l
    You should see something like Figure 3-5.

    Figure 3-5.     The directory structure obtained on unpackaging of a MySQL source tarball on Linux


    Figure 3-6.     Configuring the MySQL source tree on Linux

    Take a look at the sidebar entitled “Up a Tree” for more information on what each directory contains.

    4. Now, set variables for the compile process via the included configure script. (Note the use of the --prefix argument to configure, which sets the default installation path for the compiled binaries.)
      [root@host]# ./configure --prefix=/usr/local/mysql

      You should see a few screens of output (Figure 3-6 has a sample) as configure configures and sets up the variables needed for the compilation process.
    5. Now compile the program using make:
      [root@host]# make

      Watch as your screen fills up with all manner of strange symbols and characters (see Figure 3-7).

      The compilation process takes a fair amount of time (refer to the sidebar titled “Watching the Clock” for my empirical observations on how long you’ll be waiting), so this is a good time to get yourself a cup of coffee or check your mail.

      Now that you’re all done, you can test to ensure that everything is working properly.
    6. Run the following command:
      [root@host]# make tests
    Handcrafting Your Build
    You can pass configure a number of command-line options that affect the build process. Here’s a list of the more interesting ones:

    • --prefix Sets the prefix for installation paths
    • --without-server Disables compilation of the server, and compiles only the MySQL client programs and libraries
    • --localstatedir Sets the location in which the MySQL databases will be stored
    • --with-charset Sets a default character set
    • --with-debug Turns on extended debugging
    • --with-raid Enables RAID support
    • --with-embedded-server Builds the libmysqld embedded server library
    • --without-query-cache Disables the query cache
    • --without-debug Disables debugging routines
    • --with-openssl Includes OpenSSL support
    Use the configure --help command to get a complete list of options.


    Figure 3-7.     Building MySQL on Linux

    Watching the Clock
    Compiling MySQL is a fairly time-consuming process, and you should be prepared to spend anywhere between 15 to 60 minutes on the task. The following table contains some empirical observations on the time taken to compile the program on various hardware configurations:
    System Configuration
    Time Taken to Compile MySQL

    Pentium-II@350 MhZ, 64 MB RAM

    45 minutes

    Pentium-III@700MhZ, 256 MB RAM

    30 minutes

    AMD Athlon MP 1500+, SuSE Linux 7.3

    8 minutes

    AMD Opteron@2x1.6 GHz, UnitedLinux 1.0

    7 minutes

    Apple PowerMac G4@2x1.2 GHz, Mac OS X 10.2.4

    14 minutes

    Compaq AlphaServer DS20@500 MHz, SuSe Linux 7.0

    17 minutes

    HP 9000/800/A500-7X, HP-UX 11.11

    14 minutes

    IBM RS/6000, AIX 4.3.3

    35 minutes

    Intel Itanium2@900 MHz, Red Hat AS 2.1

    14 minutes

    MIPS R5000@500 MHz, SGI IRIX 6.5

    2 hours 30 minutes

     

    7. Install the MySQL binaries to their new home

      in /usr/local/mysql:

      [root@host]# make install

      Figure 3-8 demonstrates what your screen should look like during the installation process.


      Figure 3-8.       Installing complied MySQL binaries on Linux

    8. Create the special mysql user and group with the groupadd and useradd commands:
      in /usr/local/mysql:

      [root@host]# groupadd mysql
      [root@host]# useradd -g mysql mysql
    9. Run the initialization script, mysql_install_db, which ships with the program, to prepare MySQL for operation:
      [root@host]# /usr/local/mysql/scripts/mysql_install_db

    10. Alter the ownership of the MySQL binaries so that they are owned by root:
      [root@host]# chown -R root /usr/local/mysql

      Now ensure that the newly minted mysql user has read/write access to the MySQL data directories:

      [root@host]# chown -R mysql /usr/local/mysql/var
      [root@host]# chgrp -R mysql /usr/local/mysql
    11. Start the MySQL server by manually running the mysqld daemon:
      [root@host]# /usr/local/mysql/bin/mysqld_safe –- user=mysql &

      MySQL should start up normally, reading the base tables created in /usr/local/mysql/var.

    At this point, you can proceed to the section titled “Testing MySQL” to verify that everything is working as it should.

    MySQL Configuration and Installation - Installing and Configuring MySQL on Windows

    MySQL is available in both source and binary form for Windows 95/98/Me/2000/ XP/NT. Most often, you will want to use the binary distribution, which comes with an automated installer and allows you to get MySQL up and running on your Windows

    Version Control
    In case you’re wondering, all the binaries used when developing this book have been built on Linux using the following software versions:

    • mysqld 4.0.15-standard • mysqld-4.1-alpha
    • rpm 4.1 • gcc 3.2
    • tar 1.13.25 • gunzip 1.3.3
    • unzip 5.50 • make 3.79.1
    • autoconf 2.53 • automake 1.6.3

    system in just a few minutes. However, if you’re the type who likes rolling your own, MySQL AB also makes MySQL source code available for Windows. In this section, I’ll be exploring the installation of both source and binary distributions on Windows 98 and Windows NT.

    Installing MySQL from a Binary Distribution

    Installing a binary distribution of MySQL on Windows is a fairly simple process—all you need to do is point and click your way through the installer provided with the distribution. Here’s how:

      1. Log in as an administrator (if you’re using Windows NT or Windows 2000) and unzip the distribution archive to a temporary directory on your system. After extraction, your directory should look something like Figure 3-9.


      Figure 3-9.       The directory structure created on unpackaging a MySQL binary distribution for Windows

      2. Double-click the setup.exe file to begin the installation process. You should see a welcome screen (Figure 3-10). Click Next.

      3. Click Browse and select the directory in which MySQL is to be installed—in this example, select c:\program files\mysql (Figure 3-11). Click Next.

      4. Select the type of installation required (Figure 3-12). Click Next. Most often, a Typical Installation will do; however, if you’re the kind who likes tweaking default settings, select the Custom Installation option, click

      Next, and then decide which components of the package should be installed (Figure 3-13).


      Figure 3-10.       MySQL installation on Windows

      Figure 3-11.       Selecting installation directory on Windows

      Figure 3-12.       Selecting installation type

      Figure 3-13.       Selecting components for a custom installation on Windows

      5. Click Next, and MySQL should now begin installing to your system (Figure 3-14).

      6. After installation is complete, you should see a screen like Figure 3-15. Click Finish.


    You should now be able to start the MySQL server by diving into the bin\ subdirectory of your MySQL installation and launching the WinMySQLadmin tool (winmysqladmin.exe). This tool provides a graphical user interface (GUI) to MySQL configuration, and it is by far the simplest way to configure MySQL on Windows systems.

    The first time you start WinMySQLadmin, you will be asked for the name and password of the user which the server should run as (Figure 3-16).

    After you have entered this information, WinMySQLadmin will automatically create the MySQL configuration file (named my.ini) and populate it with appropriate values for your system. You can edit these values at any time through the “my.ini Setup” tab of the main WinMySQLadmin application window (see Figure 3-17).


    Figure 3-14.     Installation in progress on Windows


    Figure 3-15.     Installation successfully completed on Windows

    Note You can also start the MySQL server by directly launching the mysqld.exe or mysqld-nt.exe binary from the bin\ subdirectory of your MySQL installation.

    Once the server has started, WinMySQLadmin will minimize to a green icon in your Windows taskbar notification area. You can now proceed to test the server as described in the section “Testing MySQL” to ensure that everything is working as it should.


    Figure 3-16.     Configuring the MySQL user on Windows via WinMySQLadmin


    Figure 3-17.     Editing MySQL configuration on Windows via WinMySQLLadmin

    Note that you can bring the WinMySQLadmin application back to the foreground at any time by right-clicking the taskbar icon and choosing “Show Me” from the pop-up menu (see Figure 3-18).

    MySQL Configuration and Installation - Installing MySQL on Windows from a Source Distribution

    While compiling MySQL for Windows from the source archive is not something that’s generally recommended—it’s far safer, not to mention easier, to use the provided binaries—it’s certainly doable, assuming you have a copy of the Visual C++ 6.0 compiler (with Service Pack 5 and the preprocessor package). Here’s how it’s done:

      1. Unzip the source archive to a working directory on your system.

      2. Launch the Visual C++ compiler, and open the mysql.dsw workspace from the working directory. You should see a window like Figure 3-19.


      Figure 3-18.       Using the WinMySQLLadmin system tray icon


      Figure 3-19.       The MySQL workspace in Visual C++

      3. Choose Build | Set Active Configuration to obtain a list of available configurations. Select mysqld – Win32 Release (see Figure 3-20). Click OK.

      4. Begin compiling by pressing the F7 key. The various MySQL binaries will be compiled—expect the process to take from 20 minutes to an hour, depending


      Figure 3-20.       Selecting which version of MySQL to build in visual C++


      Figure 3-21.       Compiling MySQL for Windows in Visual C++

    on the capabilities of your machine. During the compilation process, the Visual C++ compiler window will display a series of messages, such as those shown in Figure 3-21.
      5. After compilation is complete, create a separate installation directory to house the compiled binaries—for example, c:\program files\mysql.

      6. Create a bin\ subdirectory under this directory, and move the compiled libraries and executables into this directory. While you’re at it, also move the data\, share\, docs\, and support-files\ directories from the working directory into this directory.

      7. You should now be able to start the MySQL server by diving into the bin\ subdirectory of your MySQL installation and launching the MySQL server daemon directly (mysqld.exe or mysqld-nt.exe).

    Once the server has started, proceed to test it as per the instructions in “Testing MySQL,” next.

    MySQL Configuration and Installation - Testing MySQL

    After MySQL has been successfully installed, the base tables have been initialized, and the server has been started, you can verify that all is working as it should via some simple tests.

    Note that all these commands should be run from your UNIX or Windows command prompt. I am assuming here that you are running them from your MySQL installation directory (as per the examples in the section “Installing and Configuring MySQL,” this will be either /usr/local/mysql in UNIX or c:\program files\ mysql in Windows).

    Use the mysqladmin Utility to Obtain Server Status

    The mysqladmin utility is usually located in the bin subdirectory of your MySQL installation. You can execute it by changing to that directory and executing the following command:
      [root@host]# mysqladmin version

      You should see something resembling the output shown in Figure 3-22.


      Figure 3-22.       The output of a call to mysqladmin

    Connect to the Server Using the MySQL Client, and Execute Simple SQL Commands

    The MySQL client that ships with the MySQL distribution is named, funnily enough, mysql. Fire it up from your command prompt by switching to the bin directory of your MySQL installation and typing
      [root@host]# mysql
    You should be rewarded with a mysql> prompt. At this point, you are connected to the MySQL server and can begin executing SQL commands or queries. Here are a few examples, with their output:

    mysql> SHOW DATABASES;
    +----------+
    | Database |
    +----------+
    | mysql |
    | test |
    +----------+
    2 rows in set (0.13 sec)

    mysql> USE mysql;
    Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed

    mysql> SHOW TABLES;
    +-----------------+
    | Tables_in_mysql |
    +-----------------+
    | columns_priv |
    | db |
    | func |
    | host |
    | tables_priv |
    | user |
    +-----------------+
    6 rows in set (0.00 sec)

    mysql> SELECT COUNT(*) FROM user;
    +----------+
    | count(*) |
    +----------+
    | 4 |
    +----------+
    1 row in set (0.00 sec)

    Post-Installation Steps

    Once testing is complete, you should perform two more tasks to complete your MySQL installation:

    Alter the MySQL root Password

    When MySQL is first installed, access to the database server is restricted to the MySQL administrator, aka root. By default, this user is initialized with a null password, which is generally considered a Bad Thing. You should therefore rectify this as soon as possible by setting a password for this user via the included mysqladmin utility, using the following syntax in UNIX
      [root@host]# /usr/local/mysql/bin/mysqladmin -u root
      password ' <new-password>'
      C:\> c:\program files\mysql\bin\mysqladmin -u root
      password ' <new-password>'

      This password change goes into effect immediately, with no requirement to restart the server or flush the privilege table.
    Note The MySQL root user is not the same as the system root user on UNIX.

    Configure MySQL to Start Automatically When the System Boots up

    On UNIX, MySQL comes with a startup/shutdown script, which is the recommended way of starting and stopping the MySQL database server. This script, named mysql.server, is available in the support-files subdirectory of your MySQL installation, and it can be invoked as follows:

    [root@host]# /usr/local/mysql/support-files/mysql.server start
    [root@host]# /usr/local/mysql/support-files/mysql.server stop

    To have MySQL start automatically at boot time, you simply need to copy this script to the /etc/init.d/* directory hierarchy of your system, and then you can invoke it with appropriate parameters from your system’s bootup and shutdown scripts.

    To start MySQL automatically on Windows, you can simply add a link to the mysqld server binary to your Startup group. For more information, please refer to Chapter 13.

    With a Little Help from My Friends…

    In case you have problems starting the MySQL server, you can obtain fairly detailed information on what went wrong by looking at the MySQL error log. Most often, this log can be found in the var subdirectory of your MySQL installation, and it is named hostname.err. Other common problems, such as a forgotten superuser password or incorrect path settings, can also be discovered and resolved via a close study of this error log. You can also visit the following resources for advice on how to resolve problems you may encounter during the installation process:

    • The MySQL manual http://www.mysql.com/documentation
    • The MySQL mailing lists http://lists.mysql.com
    • Google http://www.google.com
    • Google Groups http://groups.google.com

    If you’re reporting a problem or a bug, remember to use the supplied mysqlbug script to gather necessary system information and include it in your report  

    Summary
    As a popular open-source application, MySQL is available for a wide variety of platforms and architectures, in both binary and source form. This chapter explained the distinction among the different versions of MySQL, together with recommendations on the most appropriate version for your requirements; it also demonstrated the process of installing MySQL on the two most common platforms, Linux and Windows. It provided installation and configuration instructions for both binary and source distributions and also provided pointers to online resources for other platforms and for detailed troubleshooting advice and assistance.

    		•