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:
By the chapter's conclusion, you'll have learned how to install and configure an operational MySQL server.
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.
Three separate downloads are available for the Windows platform, including:
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.
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.
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:
MySQL-server-standard-VERSION.rpm
)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:
%>groupadd mysql
%>useradd -g mysql mysql
gunzip
and tar
programs is recommended.
%>cd /usr/local
%>tar -xzvf /usr/src/mysql-VERSION-OS.tar.gz
%>ln -s FULL-PATH-TO-MYSQL-VERSION-OS mysql
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:
%>groupadd mysql
%>useradd -g mysql mysql
gunzip
and tar
programs is recommended.
%>cd /usr/src
%>gunzip < /usr/src/mysql-VERSION.tar.gz | tar xvf -
%>cd mysql-VERSION
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
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 .
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
%>chown -R root .
%>chown -R mysql data
That's it! Proceed to the section "Setting the MySQL Administrator Password."
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:
mysql-essential-VERSION-win32.msi
icon to start the installation process.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.More...
on either of these screens to learn more about the respective offerings, or click Next to continue the process.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:
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.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.
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.
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.
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).
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.
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:
/etc/inittab
./etc/rc.d/
. Each runlevel possesses its own folder, and is numbered accordingly. For example, the runlevel 3 folder is rc3.d
.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.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 Settings Control Panel System 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 Settings Control Panel Administrative Tools 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.
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.
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.
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.
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)
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 &
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
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:
#
).mysqld_safe
, except that they are not prefaced with the double hyphen.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.
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.
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.
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).
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.
3.149.234.188