CHAPTER 26

Installing and Configuring MySQL

This chapter guides you through MySQL's installation and configuration process. It is not intended as a replacement for MySQL's excellent (and mammoth) user manual, but instead highlights the key procedures of immediate interest to anybody who wants to quickly and efficiently ready the database server for use. In total, the following topics are covered:

  • Downloading instructions
  • Distribution variations
  • Installation procedures (source, binary, RPMs)
  • Setting the MySQL administrator password
  • Starting and stopping MySQL
  • Installing MySQL as a system service
  • MySQL configuration and optimization issues
  • Reconfiguring PHP to use MySQL

By the chapter's conclusion, you'll have learned how to install and configure an operational MySQL server.

Downloading MySQL

Two editions of the MySQL database are available: MySQL Community Server and MySQL Enterprise Server. You should use the former if you don't require MySQL's array of support, monitoring, and update services. If any or all of the aforementioned services might appeal to you, learn more about MySQL Enterprise at http://www.mysql.com/products/enterprise/. This book presumes you're using the Community Server edition, which is available for free download via the MySQL Web site.

To speed your download of the latest MySQL version, you should use a mirror closest to your location. A list of mirrors is available at http://www.mysql.com/downloads/mirrors.html.

Downloading MySQL for Windows

Three separate downloads are available for the Windows platform, including:

  • Essentials: Contains everything you need to effectively run MySQL on Windows, but doesn't include optional components such as the benchmarking tools. This is the package you'll likely want to download.
  • Complete: Contains everything you need to effectively run MySQL on Windows, and also includes numerous optional components.
  • Noinstall: Contains everything found in the Complete package, but comes without an installer. Therefore, to install it, you would unzip the package into its own directory, typically under C:.

Regardless of whether you choose the Windows Essentials package or the complete package, the Windows installation instructions found later in this chapter are identical.

Installing MySQL

Database server installation can often be a painful process. Fortunately, MySQL server installation is fairly trivial. In fact, after a few iterations, you'll find that future installations or upgrade sessions will take just a few minutes to complete and can even be done by memory.

In this section, you'll learn how to install MySQL on both the Linux and Windows platforms. In addition to offering comprehensive step-by-step installation instructions, topics that often confuse both newcomers and regular users alike are discussed, including distribution format vagaries, system-specific problems, and more.

Note Throughout the remainder of this chapter, the constant INSTALL-DIR is used as a placeholder for MySQL's base installation directory. Consider modifying your system path to include this directory.

Installing MySQL on Linux

Although MySQL has been ported to at least ten platforms, its Linux distribution remains the most popular. This isn't surprising, because both are commonly used in conjunction with running Web-based services. This section covers the installation procedures for all three of MySQL's available Linux distribution formats: RPM, source, and binary.

Note More recently MySQL has started offering .deb packages for Ubuntu/Debian. Although the installation procedure won't be covered in this chapter, you should be able to find sufficient documentation online.

RPM, Binary, or Source?

Software intended for the Linux operating system often offers several distribution formats. MySQL is no different, offering RPM, binary, source, and DEB versions of each released version. Because RPM, binary, and source all are popular options, this section offers instructions for all three. If you're new to these formats, take care to read each of these sections carefully before settling upon a format, and perform additional research if necessary.

The RPM Installation Process

The RPM Package Manager (RPM) provides an amazingly simple means for installing and maintaining software. RPM offers a common command interface for installing, upgrading, uninstalling, and querying software, largely eliminating the learning curve historically required of general Linux software maintenance. Given these advantages, it might not come as a surprise that RPM is the recommended way for installing MySQL on Linux.

Tip Although you'll learn a few of RPM's more useful and common commands in this section, it hardly scratches the surface of its capabilities. If you're unfamiliar with RPM format, you can learn more about it at http://www.rpm.org/.

MySQL offers RPMs for a variety of different processor architectures. To carry out the examples found throughout the remainder of this book, you need to download at least two files, replacing VERSION with the version information of your particular RPM choice:

  • The MySQL server (MySQL-server-standard-VERSION.rpm)
  • The MySQL client (MySQL-client-standard-VERSION.rpm)

Download these packages, saving them to your preferred distribution repository directory. It's typical to store packages in the /usr/src directory, but the location has no bearing on the final outcome of the installation process.

Learning More About the RPM Package Before commencing with the installation, you should learn more about each package. Executing the following command offers a succinct description of the package architecture and its contents:

%>rpm -qp --info MySQL-server-standard-VERSION.rpm

Executing the following command displays all packaged files and their installation destination:

%>rpm -qpl MySQL-server-standard-VERSION.rpm

Installing the MySQL RPMs You can install the MySQL RPM with a single command:

%>rpm -i MySQL-server-standard-VERSION.rpm

You might consider adding the -v option to view progress information as the RPM installs. Upon execution, the installation process will begin. Assuming all goes well, you will be informed that the initial tables have been installed, and that the mysqld server daemon has been started.

Keep in mind that this only installs MySQL's server component. If you want to connect to the server from the same machine, you need to install the client RPM:

%>rpm -iv MySQL-client-VERSION.i386.rpm

Believe it or not, by executing this single installation command, the initial databases have also been created, and the MySQL server daemon is running.

Tip Uninstalling MySQL is as easy as installing it, involving only a single command:

%>rpm -e MySQL-VERSION

Although the MySQL RPMs offer a painless and effective means to an end, this convenience comes at the cost of flexibility. For example, the installation directory is not relocatable; that is, you are bound to the predefined installation path as determined by the packager. This is not necessarily a bad thing, but the flexibility is often nice, and sometimes necessary. If your personal situation requires that added flexibility, read on to find out about the binary and source installation processes. Otherwise, proceed to the section "Setting the MySQL Administrator Password."

The Binary Installation Process

A binary distribution is simply precompiled source code, typically created by developers or contributors with the intention of offering users a platform-specific optimized distribution. Although this chapter focuses on the Linux installation process, keep in mind that the procedure is largely identical for all platforms (many of which are available for download on the MySQL Web site) except for Windows, which is covered in the next section.

To install the MySQL binary on Linux, you need to have tools capable of unzipping and untarring the binary package. Most Linux distributions come with the GNU gunzip and tar tools, which are capable of carrying out these tasks.

You can download the MySQL binary for your platform by navigating to the MySQL Web site's Downloads section. Unlike the RPMs, the binaries come with both the server and client packaged together, so you need to download only a single package. Download this package, saving it to your preferred distribution repository directory. It's common to store packages in the /usr/src directory, but the location has no bearing on the final outcome of the installation process.

Although the binary installation process is a tad more involved than installing an RPM in terms of keystrokes, it is only slightly more complicated in terms of required Linux knowledge. This process can be divided into four steps:

  1. Create the necessary group and owner (you need to have root privileges for this and the following steps): %>groupadd mysql
    %>useradd -g mysql mysql
  2. Decompress the software to the intended directory. Using the GNU gunzip and tar programs is recommended. %>cd /usr/local
    %>tar -xzvf /usr/src/mysql-VERSION-OS.tar.gz
  3. Link the installation directory to a common denominator: %>ln -s FULL-PATH-TO-MYSQL-VERSION-OS mysql
  4. Install the MySQL database. mysql_install_db is a shell script that logs in to the MySQL database server, creates all of the necessary tables, and populates them with initial values. %>cd mysql
    %>chown -R mysql .
    %>chgrp -R mysql .
    %>scripts/mysql_install_db --user=mysql
    %>chown -R root .
    %>chown -R mysql data

That's it! Proceed to the section "Setting the MySQL Administrator Password."

The Source Installation Process

The MySQL developers have gone to great lengths to produce optimized RPMs and binaries for a wide array of operating systems, and you should use them whenever possible. However, if you are working with a platform for which no binary exists, require a particularly exotic configuration, or happen to be a rather controlling individual, then you also have the option to install from source. The process is actually only slightly longer than the binary installation procedure.

The source installation process is indeed somewhat more complicated than installing binaries or RPMs. For starters, you should possess at least rudimentary knowledge of how to use build tools like GNU gcc and make, and you should have them installed on your operating system. It's assumed that if you've chosen to not heed the advice to use the binaries, you know all of this already. Therefore, just the installation instructions are provided, with no corresponding explanation:

  1. Create the necessary group and owner: %>groupadd mysql
    %>useradd -g mysql mysql
  2. Decompress the software to the intended directory. Using the GNU gunzip and tar programs is recommended. %>cd /usr/src
    %>gunzip < /usr/src/mysql-VERSION.tar.gz | tar xvf -
    %>cd mysql-VERSION
  3. Configure, make, and install MySQL. A C++ compiler and make program are required. Using recent versions of the GNU gcc and make programs is strongly recommended. Keep in mind that -OTHER-CONFIGURATION-FLAGS is a placeholder for any configuration settings that determine several important characteristics of the MySQL server, such as installation location. It's left to you to decide which flags best suit your special needs. %>./configure -prefix=/usr/local/mysql [OTHER-CONFIGURATION-FLAGS]
    %>make
    %>make install
  4. Copy the sample MySQL configuration (my.cnf) file into its typical location, and set its ownership. The role of this configuration file is discussed in depth later, in the section "The my.cnf File." %>cp support-files/my-medium.cnf /etc/my.cnf
    %>chown -R mysql .
    %>chgrp -R mysql .
  5. Install the MySQL database. mysql_install_db is a shell script that logs in to the MySQL database server, creates all of the necessary tables, and populates them with initial values. %>scripts/mysql_install_db --user=mysql
  6. Update the installation permissions: %>chown -R root .
    %>chown -R mysql data

That's it! Proceed to the section "Setting the MySQL Administrator Password."

Installing and Configuring MySQL on Windows

Open source products continue to make headway on the Microsoft Windows server platform, with historically predominant Unix-based technologies like the Apache Web server, PHP, the Perl and Python programming languages, and, more recently, MySQL continuing to gain popularity on what was once considered taboo ground for free software. In addition, for many users, the Windows environment offers an ideal testing ground for Web/database applications that will ultimately be moved to a production Linux environment.

Installing MySQL on Windows

This section highlights the MySQL binary installation process targeted for the Windows platform. Although you could compile the software from source, most users likely will opt to use the binary instead (a choice recommended both here and by MySQL AB). Therefore, this section focuses solely on that procedure.

Tip The MySQL installation process described in this section applies to Windows XP, Windows 2000, and Windows Advanced Server 2000 and 2003. It likely works for Windows Vista as well, although you may need to make adjustments to some of the installation paths.

You can download the MySQL binary for your platform by navigating to the MySQL Web site Downloads section. Unlike the RPMs, the binaries come with both the server and client packaged together, so you need to download only a single package. Download this package, saving it to the local machine.

Like many Windows programs, a convenient GUI installer is available for installing the binary. The process follows:

  1. Decompress the zip file to a convenient installation location, such as your Desktop. Any Windows-based decompression program capable of working with zip files should work just fine; WinZip (http://www.winzip.com/) is a particularly popular compression package.
  2. Double-click the mysql-essential-VERSION-win32.msi icon to start the installation process.
  3. Read and click through the welcome prompt.
  4. Choose between a Typical, Complete, or Custom installation. The Typical installation provides everything you need to effectively run MySQL, while the Complete installation installs all the optional components in addition to the documentation. The Custom installation allows you to wield total control over what's installed, in addition to allowing you to choose the installation directory. Go ahead and choose the Custom installation and click Next.
  5. Either accept or change the installation directory. The default is C:Program FilesMySQLMySQL Server 5.0. For easy access you might consider changing this to C:mysql. Click Next, and then click Install in the next window.
  6. The installation process begins. Be patient while the process completes.
  7. The next two screens contain advertisements. Feel free to click More... on either of these screens to learn more about the respective offerings, or click Next to continue the process.
  8. The installation process is now complete. You are prompted to configure MySQL. There's no time like the present, so make sure this checkbox is selected and then click Finish.

Configuring MySQL on Windows

The Windows MySQL Configuration Wizard offers a very convenient graphical interface for creating and configuring MySQL's Windows configuration file, my.ini. The wizard asks you a series of questions regarding how you intend to use MySQL, and then uses your answers to tailor the my.ini file accordingly. A summary of the steps follows:

  1. You are prompted to choose between Standard Configuration and Detailed Configuration. Choosing Standard Configuration creates a general-purpose configuration that you can later adjust as necessary. For the purposes of learning more about what configuration capabilities are at your immediate disposal, choose Detailed Configuration and click Next.
  2. You are asked to identify whether the MySQL server will be used for development purposes, as a multiuse machine (Web and database, for instance), or as a dedicated MySQL machine. Your choice determines how much memory will be consumed by MySQL. Choose the server type that suits your present needs best and click Next.
  3. You are prompted for the database configuration that best suits your needs. For the purposes of this book, you need to choose Multifunctional Database. The reason for the other two usage types, Transactional Database Only and Non-Transactional Database Only, will become more apparent as you learn more about MySQL in later chapters. Choose Multifunctional Database, click Next, and then accept the InnoDB Tablespace Settings presented in the next screen by again clicking Next.
  4. You are prompted to configure the number of concurrent connections estimated for the server. You have three options: choose Decision Support (DSS)/OLAP, which is intended for a minimal number of concurrent connections (fewer than 20), such as might be needed in a small office setting; choose Online Transaction Processing (OLTP), which is intended for high-traffic servers such as that which might be used for a Web server; or set your own estimated number of connections. After you make your choice, click Next.
  5. You are prompted to determine whether TCP/IP networking should be enabled and to confirm the default connection port 3306. The port should be left as set, and TCP/IP networking should be left enabled if you intend to connect to this server remotely. If all connections will be made locally, disable this feature. You'll also be asked whether to enable MySQL's Strict Mode, which will cause MySQL to conform with norms found in many other enterprise databases. You should leave this enabled. Click Next to continue.
  6. You're asked to determine which character set the MySQL server should use. You have three options: choose Standard Character Set, which is best suited for English and other Western European languages; choose Best Support for Multilingualism, which uses the UTF-8 character set, capable of managing text in a wide variety of languages; or manually select the character set of your choosing. After you make your choice, click Next.
  7. You're prompted to determine whether MySQL should be installed as a Windows service, meaning it can be started automatically at system startup, and shut down at system shutdown or reboot. If this will be a server, or if you plan to regularly develop with the machine, consider installing it as a Windows service and enabling the checkbox for starting MySQL automatically. Additionally, you can add MySQL's bin directory to the Windows path, meaning you'll be able to access any of MySQL's utilities from the command line without having to be in the bin directory. These tools are discussed in further detail in Chapter 28. Enabling both of these options is recommended. Once you're done, click Next.
  8. In the final configuration window, you're prompted to choose and confirm a root password. Take care to choose a secure password, but make sure it isn't something you'll forget! You can also choose to enable root access from remote machines, a feature that is not recommended if you don't plan to allow remote connections to this database. You can also choose to create an anonymous account, something that isn't recommended under any circumstances. Click Next, and in the next window, start the configuration process by clicking Execute. Once the process is complete, click Finish.

Assuming that you used the MySQL Configuration Wizard, the root password is already set. However, you still may want to read the next section, which describes how to change that password as necessary.

Setting the MySQL Administrator Password

Unless you used the Windows MySQL Configuration Wizard described in the previous section, the root (administrator) account password is left blank. Although this practice seems quite questionable, it has long been the default when installing MySQL and likely will be for some time into the future. Therefore, you must take care to add a password immediately! You can do so with the SET PASSWORD command, like so:

%>mysql -u root mysql
%>SET PASSWORD FOR root@localhost=PASSWORD('secret'),

Of course, choose a password that is a tad more complicated than secret. MySQL will let you dig your own grave in the sense that passwords such as 123, abc, and your mother's name are all perfectly acceptable. Consider choosing a password that is at least eight characters long, and consists of a combination of numeric and alphabetical characters of varying case.

Failing to heed the advice to set a password immediately means that anybody with access to the operating system can shut down the daemon, not to mention completely destroy your database server and its data. Although there is nothing wrong with doing a little experimentation immediately after the installation process, for security purposes you should set the MySQL administrator password immediately.

Starting and Stopping MySQL

The MySQL server daemon is controlled via a single program, located in the INSTALL-DIR/bin directory. Instructions for controlling this daemon for both the Linux and Windows platforms are offered in this section.

Controlling the Daemon Manually

Although you'll ultimately want the MySQL daemon to automatically start and stop in conjunction with the operating system, you'll often need to manually execute this process during the configuration, and later application testing, stages. In this section, you learn how to do so on both the Linux and Windows platforms.

Starting MySQL on Linux

The script responsible for starting the MySQL daemon is called mysqld_safe, which is located in the INSTALL-DIR/bin directory. This script can only be started by a user possessing sufficient execution privileges, typically either root or a member of the group mysql. The following is the command to start MySQL on Linux:

%>cd INSTALL-DIR
%>./bin/mysqld_safe --user=mysql &

Keep in mind that mysqld_safe will not execute unless you first change to the INSTALL-DIR directory. In addition, the trailing ampersand is required, because you'll want the daemon to run in the background.

Tip Before version 4.0, mysqld_safe was known as safe_mysqld. It isn't clear why this change was made; nonetheless, all the options available to safe_mysqld are also available under the new naming convention.

The mysqld_safe script is actually a wrapper around the mysqld server daemon, offering features that are not available by calling mysqld directly, such as run-time logging and automatic restart in case of error. You'll learn more about mysqld_safe in the later section "Configuring MySQL."

Starting MySQL on Windows

If you're not installing MySQL as a service on Windows, you need to manually start the daemon as necessary. A number of different servers are available for the Windows platform, as listed in Table 26-1. Availability is dependent upon which distribution version you downloaded (Essentials, Complete, or Noinstall).

Table 26-1. Windows Binaries

Binary Description
mysqld Optimized binary with support for InnoDB tables. Available with both versions (Essentials and Complete). Note that prior to name changes between MySQL 4.1.2 and 4.1.3, this binary was named mysqld-opt.
mysqld-debug Includes support for debugging and for InnoDB and BDB tables. Only available with the Complete version.
mysqld-max Optimized binary with support for InnoDB and for symbolic links. Only available with the Complete version. Versions prior to MySQL 5.1.12 also support BDB tables.
mysqld-max-nt Optimized binary with support for InnoDB tables, symbolic links, and named pipes. Only available with the Complete version. Versions prior to MySQL 5.1.12 also support BDB tables.
mysqld-nt Optimized binary for Windows NT/2000/XP. Available with both versions.

Once you've chosen the binary that best fits your situation, navigate to the INSTALL-DIR/bin folder via the command line. Execute the appropriate binary by entering its name on the command line and pressing the Enter key.

Stopping MySQL on Linux and Windows

Although the MySQL server daemon can be started only by a user possessing the file system privileges necessary to execute the mysqld_safe script, it can be stopped by a user possessing the proper privileges as specified within the MySQL privilege database. Keep in mind that this privilege is typically left solely to the MySQL root user, not to be confused with the operating system root user! Don't worry too much about this right now; just understand that MySQL users are not the same as operating system users, and that the MySQL user attempting to shut down the server must possess adequate privileges for doing so. A proper introduction to mysqladmin, along with the other MySQL clients, is offered in Chapter 27; Chapter 29 delves into issues pertinent to MySQL users and the MySQL privilege system. The process for stopping the MySQL server on Linux and Windows follows:

shell>cd INSTALL-DIR/bin
shell>mysqladmin -u root -p shutdown
Enter password: *******

Assuming that you supply the proper credentials, you will be returned to the command prompt without notification of the successful shutdown of the MySQL server. In the case of an unsuccessful shutdown attempt, an appropriate error message is offered.

Starting and Stopping MySQL Automatically

When the occasion arises that a server needs to be rebooted, or unexpectedly shuts down, it is imperative that all mission-critical services are properly exited and automatically reactivated on system boot. Thankfully, accomplishing such matters is trivial on both the Linux and Windows platforms.

Linux

Linux is capable of operating in several different system states, each of which is defined by the set of services made available to the user when that state is in control of the system. Eight such runlevels are available, although typically only seven are of interest to the user. For instance, Red Hat/Fedora's relevant runlevels are listed in Table 26-2.

Table 26-2. Red Hat/Fedora's System Runlevels

Runlevel Description
0 Halt
1 Single-user mode
2 Empty (user-definable)
3 Nonwindowed multiuser mode
4 Empty (user-definable)
5 Full multiuser mode (with windowing)
6 Reboot

Although a thorough introduction of the Linux runlevels is outside the scope of this book, the following points should give you a fair idea of how this operates:

  • The system's default runlevel is configured in the file /etc/inittab.
  • Red Hat/Fedora's default runlevel is 3.
  • Runlevels 2 and 4 are typically used for custom configurations involving services not otherwise required in the standard runlevels 3 and 5.
  • Red Hat/Fedora's runlevel designations are stored in /etc/rc.d/. Each runlevel possesses its own folder, and is numbered accordingly. For example, the runlevel 3 folder is rc3.d.
  • Whether and in what order services are started or terminated within each runlevel is determined by examining the first three characters of each symbolic link found in the respective runlevel folder. If the symbolic link begins with an S, that service will be initiated in that runlevel. If it begins with a K, it will be terminated. The two-digit integer following this first character determines the order in which that service will be initiated or terminated. The higher the number, the later its fate will be addressed.

Starting MySQL on Boot

To ensure that the MySQL daemon automatically starts on system boot, the following addition to runlevel 3 is required:

%>ln -s INSTALL-DIR/support-files/mysql.server /etc/rc.d/init.d/mysql
%>ln -s /etc/rc.d/init.d/mysql /etc/rc.d/rc3.d/S99mysql

Stopping MySQL on Shutdown or Reboot

To ensure that the MySQL daemon properly exits upon system shutdown or reboot, the following additions to the appropriate runlevels (0 and 6, respectively) are required. Note that the following steps assume that you have first executed the steps required to ensure that MySQL starts on boot.

%>ln -s /etc/rc.d/init.d/mysql /etc/rc.d/rc0.d/K01mysql
%>ln -s /etc/rc.d/init.d/mysql /etc/rc.d/rc6.d/K01mysql

Once you've made these changes, you should take a few moments to ensure that the MySQL shutdown and bootup process is properly working. This involves simply shutting down, starting, and finally rebooting the server, each time reviewing the server process list to ensure that MySQL is running.

Windows

On the Windows platform, any application installed as a service can be configured to start automatically and properly upon system boot, and stop upon system shutdown or reboot. This practice is no different with MySQL.

Installing MySQL As a Windows Service

Unless you installed MySQL as a service using the MySQL Configuration Wizard, you can install MySQL as a Windows service by opening a command-line prompt and executing:

C:>INSTALL-DIR/bin/mysqld-nt --install

If you've chosen another binary, replace mysqld- nt accordingly. Note that this presupposes that you have added to the system path the path to the MySQL bin directory. If you have not, you must first cd to the proper directory before executing the service installation command.

Tip You should add the MySQL bin directory to your system path. This is accomplished by navigating to Start image Settings image Control Panel image System image Advanced and clicking Environment Variables. Edit the Path Environment Variable, concatenating C:mysqlin; to the end of the string.

Once the binary is installed, navigate to the Services Administration Panel via Start image Settings image Control Panel image Administrative Tools image Services. This panel is depicted in Figure 26-1. Ensure that the mysql service's Startup Type is set to Automatic. If it is not, right-click the mysql service and select Properties. Change the Startup Type setting to Automatic and click OK.

image

Figure 26-1. The Windows Services administrator

Uninstalling the MySQL Service

Uninstalling the MySQL service is as simple as starting it. To uninstall the MySQL service, execute:

C:>INSTALL-DIR/bin/mysqld-nt remove

Keep in mind that if you uninstall the MySQL service, it will not automatically restart should the system require rebooting.

Configuring and Optimizing MySQL

Unless otherwise specified, MySQL assumes a default set of configuration settings upon each start of the MySQL server daemon. Although the default settings are probably suitable for users who require nothing more than a standard deployment, you'll at least want to be aware of what can be tweaked, because such changes not only will better adapt your deployment to your specific hosting environment, but could also greatly enhance the performance of your application based on its behavioral characteristics. For example, some applications might be update-intensive, prompting you to adjust the resources that MySQL requires for handling write/modification queries. Other applications might need to handle a large number of user connections, prompting a change to the number of threads allocated to new connections. Thankfully, MySQL is highly configurable; as you'll learn in this and later chapters, administrators have the opportunity to manage just about every aspect of its operation.

This section offers an introduction to many of the configuration parameters that affect the general operation of the MySQL server. Because configuration and optimization are such important aspects of maintaining a healthy server (not to mention a sane administrator), this topic is returned to often throughout the remainder of the book.

The mysqld_safe Wrapper

You may have noticed that in previous sections, the MySQL server daemon was referred to as mysqld. Although this is indeed the daemon, you actually rarely directly interact with it; rather, you interface with the daemon through a wrapper called mysqld_safe. The mysqld_safe wrapper adds a few extra safety-related logging features and system-integrity features to the picture when the daemon is started. Given these useful features, mysqld_safe is the preferred way to start the server, although you should keep in mind that it's only a wrapper and should not be confused with the server itself.

Literally hundreds of MySQL server configuration options are at your disposal, capable of fine-tuning practically every conceivable aspect of the daemon's operation, including MySQL's memory usage, logging sensitivity, and boundary settings, such as maximum number of simultaneous connections, temporary tables, and connection errors, among others. If you'd like to view a summary of all options available to you, execute:

%>INSTALL-DIR/bin/mysqld --verbose --help

The next section highlights several of the more commonly used parameters.

MySQL's Configuration and Optimization Parameters

This section introduces several basic configuration parameters that might be useful to tweak when getting started managing the server. But first take a moment to review how you can quickly view MySQL's present settings.

Viewing MySQL's Configuration Parameters

In the preceding section, you learned how to call mysqld to learn what options are available to you. To see the present settings, you instead need to execute the mysqladmin client, like so:

%>mysqladmin -u root -p variables

Alternatively, you can log in to the mysql client and execute the following command:

mysql>SHOW VARIABLES;

Doing so produces a lengthy list of variable settings similar to this:

+---------------------------------+----------------------------+
| Variable_name                   | Value                      |
+---------------------------------+----------------------------+
| auto_increment_increment        | 1                          |
| auto_increment_offset           | 1                          |
| automatic_sp_privileges         | ON                         |
| back_log                        | 50                         |
| basedir                         | C:mysql5                 |
| binlog_cache_size               | 32768                      |
| bulk_insert_buffer_size         | 8388608                    |
| . . .                           |                            |
| version                         | 5.1.21-beta-community      |
| version_comment                 | Official MySQL binary      |
| version_compile_machine         | ia32                       |
| version_compile_os              | Win32                      |
| wait_timeout                    | 28800                      |
+---------------------------------+----------------------------+
226 rows in set (0.00 sec)

You can view the setting of a single variable by using the LIKE clause. For example, to determine the default storage engine setting, you use the following command:

mysql>SHOW VARIABLES LIKE "table_type";

Executing this command produces output similar to the following:

+---------------+--------+
| Variable_name | Value  |
+---------------+--------+
| table_type    | InnoDB |
+---------------+--------+
1 row in set (0.00 sec)

Finally, you can review some rather interesting statistical information such as uptime, queries processed, and total bytes received and sent by using the following command:

mysql>SHOW STATUS;

Executing this command produces output similar to this:

+-----------------------------------+-----------+
| Variable_name                     | Value     |
+-----------------------------------+-----------+
| Aborted_clients                   | 0         |
| Aborted_connects                  | 1         |
| Binlog_cache_disk_use             | 0         |
| Binlog_cache_use                  | 0         |
| Bytes_received                    | 134       |
| Bytes_sent                        | 6149      |
| Com_admin_commands                | 0         |
| . . .                             |           |
| Threads_cached                    | 0         |
| Threads_connected                 | 1         |
| Threads_created                   | 1         |
| Threads_running                   | 1         |
| Uptime                            | 848       |
+-----------------------------------+-----------+
249 rows in set (0.00 sec)

Managing Connection Loads

A well-tuned MySQL server is capable of working with many connections simultaneously. Each connection must be received and delegated to a new thread by the main MySQL thread, a task that, although trivial, isn't instantaneous. The back_log parameter determines the number of connections that are allowed to queue up while this main thread deals with a particularly heavy new connection load. By default this is set to 50.

Keep in mind that you can't just set this to a very high value and assume it will make MySQL run more efficiently. Both your operating system and Web server may have other maximum settings in place that could render a particularly high value irrelevant.

Setting the Data Directory Location

It's common practice to place the MySQL data directory in a nonstandard location, on another disk partition, for example. Using the datadir option, you can redefine this path. For example, it's commonplace to mount a second drive to a directory, /data for instance, and store the databases in a directory called mysql:

%>./bin/mysqld_safe --datadir=/data/mysql --user=mysql &

Keep in mind that you need to copy or move the MySQL permission tables (stored in DATADIR/mysql) to this new location. Because MySQL's databases are stored in files, you can do so by using operating system commands that are typical for performing such actions, such as mv and cp. If you're using a GUI, you can drag and drop these files to the new location.

Setting the Default Storage Engine

As you'll learn in Chapter 28, MySQL supports several table engines, each of which has its own advantages and disadvantages. If you regularly make use of a particular engine (as of version 4.1.5, the default is MyISAM on Linux/Unix, and InnoDB on Windows), you might want to set it as the default by using the --default-storage-engine parameter. For example, you could set the default to MEMORY like so:

%>./bin/mysqld_safe --default-table-type=memory

Once it is assigned, all subsequent table creation queries will automatically use the MEMORY engine unless otherwise specified.

Automatically Executing SQL Commands

You can execute a series of SQL commands at daemon startup by placing them in a text file and assigning that file name to init_file. For example, suppose you want to clear a table used for storing session information with each start of the MySQL server. Place the following query in a file named mysqlinitcmds.sql:

DELETE FROM sessions where rowid;

Then, assign init_file like so when executing mysqld_safe:

%>./bin/mysqld_safe --init_file=/usr/local/mysql/scripts/mysqlinitcmds.sql &

Logging Potentially Nonoptimal Queries

The log-queries-not-using-indexes parameter defines a file to which all queries are logged that aren't using indexes. Regularly reviewing such information could be useful for studying possible improvements to your queries and table structures.

Logging Slow Queries

The log_slow_queries parameter defines a file to which all queries are logged that take longer than long_query_time seconds to execute. Each time that query execution time surpasses this limit, the log_slow_queries counter is incremented. Studying such a log file using the mysqldumpslow utility could be useful for determining bottlenecks in your database server.

Setting the Maximum Allowable Simultaneous Connections

The max_connections parameter determines the maximum permitted number of simultaneous database connections. By default this is set to 100. You can check the maximum number of connections simultaneously opened by your database by reviewing the max_used_connections parameter, available by executing SHOW STATUS. If you see that this number is approaching the century mark, consider bumping the maximum upward. Keep in mind that as the number of connections increases, so will memory consumption, because MySQL allocates additional memory to every connection it opens.

Setting MySQL's Communication Port

By default, MySQL communicates on port 3306; however, you can reconfigure it to listen on any other port by using the port parameter.

Disabling DNS Resolution

Enabling the skip-name-resolve parameter prevents MySQL from resolving hostnames. This means that all Host column values in the grant tables consist either of an IP address or localhost. If you plan to use solely IP addresses or localhost, enable this parameter.

Limiting Connections to the Local Server

Enabling the skip-networking parameter prevents MySQL from listening for TCP/IP connections, a wise idea if your MySQL installation resides on the same server from which you'll be initiating connections.

Setting the MySQL Daemon User

The MySQL daemon should run as a non-root user, minimizing the damage if an attacker were to ever successfully enter the server via a MySQL security hole. Although the common practice is to run the server as user mysql, you can run it as any existing user, provided that the user is the owner of the data directories. For example, suppose you want to run the daemon using the user mysql:

%>./bin/mysqld_safe --user=mysql &

The my.cnf File

You've already learned that configuration changes can be made on the command line when starting the MySQL daemon via its wrapper, mysqld_safe. However, there exists a much more convenient method for tweaking the startup parameters, as well as the behaviors, of many MySQL clients, including mysqladmin, myisamchk, myisampck, mysql, mysqlcheck, mysqld, mysqldump, mysqld_safe, mysql.server, mysqlhotcopy, mysqlimport, and mysqlshow. You can maintain these tweaks within MySQL's configuration file, my.cnf.

At startup, MySQL looks in several directories for the my.cnf file, with each directory determining the scope of the parameters declared within. The location and relative scope of each directory is highlighted here:

  • /etc/my.cnf (C:my.cnf or windows-sys-directorymy.ini on Windows): Global configuration file. All MySQL server daemons located on the server refer first to this file. Note the extension of .ini if you choose to place the configuration file in the Windows system directory. For MySQL 4.1.5 and later, this file is placed in the installation directory when the Windows MySQL Configuration Wizard is used.
  • DATADIR/my.cnf: Server-specific configuration. This file is placed in the directory of the directory referenced by the server installation. A somewhat odd, yet crucial characteristic of this configuration file is that it references only the data directory specified at configuration time, even if a new data directory is specified at run time. Note that MySQL's Windows distribution does not support this feature.
  • --defaults-extra-file=name: The file specified by the supplied file name, complete with absolute path.
  • ~/.my.cnf: User-specific configuration. This file is expected to be located in the user's home directory. Note that MySQL's Windows distribution does not support this feature.

You should understand that MySQL attempts to read from each of these locations at startup. If multiple configuration files exist, parameters read in later take precedence over earlier parameters. Although you could create your own configuration file, you should base your file upon one of five preconfigured my.cnf files, all of which are supplied with the MySQL distribution. These templates are housed in INSTALL-DIR/support-files (on Windows these files are found in the installation directory). The purpose of each is defined in Table 26-3.

Table 26-3. MySQL Configuration Templates

Name Description
my-huge.cnf Intended for high-end production servers, containing 1 to 2GB RAM, tasked with primarily running MySQL
my-innodb-heavy-4G.ini Intended for InnoDB-only installations for up to 4GB RAM involving large queries and low traffic
my-large.cnf Intended for medium-sized production servers, containing around 512MB RAM, tasked with primarily running MySQL
my-medium.cnf Intended for low-end production servers containing little memory (less than 128MB)
my-small.cnf Intended for minimally equipped servers, possessing nominal RAM (less than 64MB)

So what does this file look like? Here's a partial listing of the my-large.cnf configuration template:

# Example mysql config file for large systems.
#
# This is for large system with memory = 512M where the system runs mainly
# MySQL.

# The following options will be passed to all MySQL clients
[client]
#password       = your_password
port            = 3306
socket          = /tmp/mysql.sock

# Here follows entries for some specific programs

# The MySQL server
[mysqld]
port            = 3306
socket          = /tmp/mysql.sock
skip-locking
key_buffer=256M
max_allowed_packet=1M
table_cache=256
sort_buffer=1M
record_buffer=1M
myisam_sort_buffer_size=64M
[mysqldump]
quick
max_allowed_packet=16M

[mysql]
no-auto-rehash
# Remove the next comment character if you are not familiar with SQL
#safe-updates

...

Note If your my-large.cnf file looks similar to this but the variables are prefaced with set-variable, do not worry. This was the standard way of setting variables within MySQL's configuration files prior to version 4.0.2. Although this still works in later versions, it has been deprecated.

Looks fairly straightforward, right? Indeed it is. Configuration files really can be summarized in three succinct points:

  • Comments are prefaced with a hash mark (#).
  • Variables are assigned exactly like they would be when assigned along with the call to mysqld_safe, except that they are not prefaced with the double hyphen.
  • The context of these variables is set by prefacing the section with the intended beneficiary, enclosed in square brackets. For example, if you want to tweak the default behavior of mysqldump, you begin with:
    [mysqldump]

    You then follow it with the relevant variable settings, like so:


    quick
    max_allowed_packet = 16M

    This context is assumed until the next square-bracket setting is encountered.

Configuring PHP to Work with MySQL

The PHP and MySQL communities have long enjoyed a close relationship. The respective technologies are like two peas in a pod, bread and butter, wine and cheese...you get the picture. This popularity of MySQL with the PHP community was apparent from the earliest days, prompting the PHP developers to bundle the MySQL client libraries with the distribution, and enable the extension by default in PHP version 4.

But you can't just install PHP and MySQL and necessarily expect them to automatically work together. You need to carry out just a few more steps, described in this section.

Reconfiguring PHP on Linux

On Linux systems, after you successfully install MySQL, you need to reconfigure PHP, this time including the --with-mysqli[=DIR] (or --with-mysql[=DIR] if you're using a version of MySQL older than 4.1 or PHP version 4.x or older) configuration option, specifying the path to the MySQL installation directory. Once the build is complete, restart Apache and you're done.

Reconfiguring PHP on Windows

On Windows, you need to do two things to enable PHP's support for MySQL. After successfully installing MySQL, open the php.ini file and uncomment the following line:

extension=php_mysqli.dll

If you're running a version of MySQL older than 4.1, uncomment the following line:

extension=php_mysql.dll

Restart Apache and you're ready to begin using PHP and MySQL together!

Note Regardless of platform, you can verify that the extensions are loaded by executing the phpinfo() function (see Chapter 2 for more information about this function).

Summary

This chapter set the stage for starting experimentation with the MySQL server. You learned not only how to install and configure MySQL, but also a bit regarding how to optimize the installation to best fit your administrative and application preferences. Configuration and optimization issues are revisited throughout the remainder of this book as necessary.

The next chapter introduces MySQL's many clients, which offer a convenient means for interacting with many facets of the server.

..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset
18.216.201.32