MySQL Configuration and Installation

If you ever wanted to start using the open source MySQL server application on your computer, this article is for you. It will show you how to obtain, install, configure, and test the MySQL server on your system, whether you are running UNIX or Windows. It is excerpted from My SQL The Complete Reference by Vikram Vaswani (McGraw-Hill/Osborne, 2003; ISBN: 0072224770).

ONE OF THE NICER things about MySQL is the fact that it’s an open-source application licensed under the General Public License (GPL); users are encouraged to download, modify, and use it free of charge. This open licensing policy has played an important role in MySQL’s widespread acceptance and popularity in the developer community; according to statistics published on the MySQL web site, MySQL is in use at more than 4 million sites worldwide, with more than 25,000 copies of the MySQL database server downloaded every day!

In this chapter, I’m going to help add to those numbers, by taking you through the process of obtaining, installing, configuring, and testing the MySQL server on your workstation. This chapter covers installation of both binary and source versions on UNIX and Microsoft Windows, and it also explains the differences between the MySQL server versions available, with a view to helping you select the right one for your needs.

Obtaining MySQL

The first order of business is to drop by the official MySQL web site (http://www.mysql.com/downloads/mysql.html) and get yourself a copy of the last stable release of the software. This isn’t necessarily as easy as it sounds—like ice-cream, MySQL comes in many flavors, and you’ll need to select the one that’s most appropriate for your needs.

You’ll need to make two primary decisions when selecting which MySQL distribution to download and use:

  • Choosing which version to install

  • Choosing between binary and source distributions

Choosing Which Version to Install

MySQL AB currently makes the following two versions of the MySQL database server available on its web site:

  • MySQL Standard This is the standard version of the MySQL database server, which includes support for both the regular, non-transactional tables and the newer, transaction-safe tables. It is suited for production environments requiring a stable, flexible, and robust database engine.

  • MySQL Max This version includes the feature set of the standard version, together with newer, more experimental capabilities. It is not always best suited for production environments, since it usually includes a fair share of not-completely-stable enhancements.

Both these versions of MySQL are licensed under the GPL and may be freely downloaded and used under the terms of that license.

In most cases, MySQL Standard is the version you should use—it’s the version used in all the examples in this book, and it’s stable, feature-rich, and well-suited for most common applications. You should select MySQL Max only if that version includes new features that you need or are keen to try out—or if you’re a geek with a penchant for living life on the bleeding edge all the time.

Choosing Between Binary and Source Distributions

MySQL AB makes both source and binary distributions of the MySQL database server available for download on its web site. As of this writing, binary distributions are available for Linux, Solaris, FreeBSD, Mac OS X, Windows 95/98/2000/XP/NT/ ME, HP-UX, IBM AIX, SCO OpenUNIX, and SGI Irix, and source distributions are available for both Windows and UNIX platforms.

In most cases, it’s preferable to use a precompiled binary distribution rather than a source distribution—MySQL AB puts in a lot of time and effort to create binary distributions for various platforms, and using these distributions is generally considered a Good Idea. These precompiled binary distributions are also easier to install than source

Pay It Forward
It should be noted that, in addition to the two versions discussed in the section “Choosing Which Version to Install,” MySQL AB also offers two other versions of
the MySQL database server, MySQL Pro and MySQL Classic, which are licensed commercially and are therefore not freely available for download.

What’s the difference between the two? MySQL Pro includes support for transactions (with the exception of the licensing terms, it is identical in every way to MySQL Standard), while MySQL Classic does not.
If you’re interested in purchasing a commercial license for these versions of the MySQL database server, drop by http://order.mysql.com and find out more
about what your greenbacks buy you.

distributions, and they are optimized by MySQL’s developers to ensure maximum performance on the target systems.

That said, for a number of possible reasons, a source distribution might work better for you than a binary distribution:

  • You’d like to recompile MySQL with compile-time options different from the defaults provided by the MySQL team. (For example, you might like to use a different value for the installation path.)

  • You’d like to compile a smaller, lighter version of MySQL that doesn’t include all the features (and overhead) of the standard binary distribution.

  • You’d like to enable support for newer, experimental features that are disabled by default in the standard binaries.

  • You’d like to view or make modifications to the source code of the application.

  • You have waaaaay too much time on your hands, and you’d like to impress the pretty girl next door with your deep and profound knowledge of MySQL’s internals. By the way, according to surveys, only six percent of MySQL users are women.

If you’re new to MySQL, I’d recommend that you go with a binary distribution— source distributions are typically used only by experienced developers who either need to tweak MySQL’s default values for their own purposes or who are interested in studying the source code to see how it works. Such users usually also have the time, inclination, and expertise to diagnose and troubleshoot compilation and configuration issues that may arise during the installation process.

Once you’ve figured out which version of MySQL you need, point your browser to http://www.mysql.com/downloads/mysql.html and select the appropriate file from the choices available. The MySQL software is also mirrored on a number of other sites around the world, and you can obtain a list of mirrors from http://www.mysql .com/downloads/mirrors.html You can make your download more efficient by selecting the site that is geographically closest to you. Once your download is complete, save it to a directory on your hard drive and skip to the section titled “Installing and Configuring MySQL,” a bit later in this chapter.

What’s in a Name?
You’ve probably noticed that MySQL distributions are named using a particular naming scheme—for example, mysql-4.0.9-gamma.tar.gz. The numbers in the file name indicate the version number of the software, with the additional suffix indicating the stability level of the software (alpha, beta, or gamma).

Note that we will assume, throughout this chapter, that you are using MySQL version 4.0.15, and that all your downloads are saved in the /tmp (UNIX) or c:temp (Windows) directory, and I will reference that directory in subsequent instructions.

Installing and Configuring MySQL

Once you’ve got yourself a copy of MySQL, it’s time to install and configure it for your specific platform. This section includes information on how to do just that, for both Windows and UNIX platforms, using both binary and source distributions.

It should be noted at the outset that this section is designed merely to provide an overview and general guide to the process of installing and configuring MySQL. It is not intended as a replacement for the installation documentation that ships with MySQL. If you encounter difficulties installing or configuring the various programs described here, drop by the MySQL web site or search the mailing lists for detailed troubleshooting information and advice.

{mospagebreak title=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

{mospagebreak title=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-architecturefor
  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.

{mospagebreak title=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@2×1.6 GHz, UnitedLinux 1.0

7 minutes

Apple PowerMac G4@2×1.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:

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

{mospagebreak title=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 • gunzip 1.3.3
  • mysqld-4.1-alpha       • unzip 5.50
  • rpm 4.1                • make 3.79.1
  • gcc 3.2                • autoconf 2.53
  • tar 1.13.25            • 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 
    filesmysql
 (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).

{mospagebreak title=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 filesmysql.

    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.

{mospagebreak title=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 filesmysqlbinmysqladmin -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.

[gp-comments width="770" linklove="off" ]

chat