Database Administration (Managing and Using MySQL, 2nd Edition)

You may find this chapter helpful even if you are not the one responsible for the administration of your MySQL server. Knowing about database administration can help you diagnose problems before you have to approach your database administrator.

To perform many administrative tasks, you must have administrative access to MySQL. (We describe access privileges and how to set yourself up as the MySQL database administrator in detail in Chapter 6.) You will additionally require administrative access to your operating system—root on Unix systems, Administrator on Windows NT/2000/XP—to perform a number of tasks.[15]

4.1. Configuration

MySQL requires the configuration of the MySQL server process, mysqld, and its several client processes such as the mysql command-line utility. MySQL exposes its Unix roots in how you configure it. Specifically, you configure it using a combination of command-line options, configuration files, and environment variables. Just about any configurable item can be managed using these three mechanisms.

Because you can define options in multiple ways, MySQL has a built-in order of preference that defines how it resolves conflicts:

Command-line options
Configuration options
Environment variable options

In other words, if you have three different values specified for the password option, MySQL client tools will use the one you specified on the command line.

The simplest, most common way to handle your MySQL options is with a configuration file. A configuration file enables you to stick all your options in a file so you do not have to specify them each time you run a command or log into a machine.

4.1.1. File Locations

On Unix systems, MySQL looks in the following locations—in order—for configuration files:

In the /etc/my.cnf file. The first place MySQL looks is the global options file. In general, you will want to place the default options to be used by all users and all servers in this file.
In the DATADIR/my.cnf file. DATADIR is the directory where a MySQL server instance keeps its datafiles. This configuration file exists specifically for configuration parameters specific to a given server instance.
In the location specified through the --defaults-extra-file=filename command-line option. This command-line option enables the MySQL server or client utilities to look in an arbitrary location for a configuration file.
In the $HOME/.my.cnf file. $HOME is the Unix environment variable that holds the home directory of the current user. This configuration file in a user's home directory is where individual users can keep their user-specific options. This location is where most client options are specified.

Windows has an extra global configuration file and no user-specific configuration file:

My.ini in the Windows system folder—generally C:\WINNT\System32
C:\my.cnf
C:\mysql\data\my.cnf
--defaults-extra-file=filename

If any option appears in multiple files, the last one read overrides the others. In other words, the user-defined options in C:\my.cnf override any values read from My.ini. This behavior makes it possible for a database administrator to provide default values for the client tools in My.ini and allow users to override specific options as their needs dictate.

4.1.2. File Content

All the configuration files share the same format. Example 4-1 is a sample configuration file. Much of it should be fairly straightforward, but we will take a moment to dissect the file.

Example 4-1. A sample MySQL configuration file

# Example mysql options file.
#
# These options go to all clients
[client]
password      = my_password
port          = 3306
socket        = /var/lib/mysql/mysql.sock


# These options go to the mysqld server
[mysqld]
port          = 3306
socket        = /var/lib/mysql/mysql.sock
skip-locking
set-variable  = max_allowed_packet=1M

The first three lines beginning with # are comments. The MySQL configuration file format supports two kinds of comments: # and ;. MySQL ignores any data appearing after a pound sign or a semicolon until the end of the line on which it appears.

The next line indicates the start of a section:

[client]

This format is actually one you may be familiar with if you have played with Samba or Windows INI configuration. A MySQL configuration file contains sections with configuration options that apply only to that section. MySQL configuration files contain two sections: client and mysqld.

The lines appearing after a section marker support the named section until the end of the file is reached or another group is encountered. This client section contains three configuration items for the client tools. The first specifies the default password:

password      = my_password

This option is equivalent to using the command-line option --password=my_password. As a general rule, any command-line option specified as --option=value appears in the MySQL configuration files as option=value.

Specifying the password in the configuration file is a convenient way of connecting to MySQL without specifying a password all the time. It is, however, generally a bad idea to specify a password in a configuration file or on the command line. You should instead require your client utility to prompt you for a password.[16] If you do specify the password in a configuration file, you should definitely make sure no one else can read that file.

[16]Using the MySQL client utilities, you can pass the -p parameter to force the utility to prompt you for a password. The command-line utilities will not echo the password to the screen.

The next two lines after the password option configure the port and socket file that a client tool can use to connect to the server. After them, we encounter a new section:

[mysqld]

This section contains options that configure the MySQL server process. It contains options similar to the client section as well as two new ways of specifying options. The first is an option without a value:

skip-locking

This Boolean option tells the MySQL server to disable system locking. If the option is not defined, system locking is enabled. The command-line form of this option is --skip-locking.

The final format for MySQL options enables you to specify mysqld variables:

set-variable = max_allowed_packet=1M

This option is the same as specifying --set-variable max_allowed_packet=1M on the command line. Variables are additional settings used to control the runtime behavior of the server and clients.

Example 4-1. A sample MySQL configuration file

Chapter 4. Database Administration

Contents:

4.1. Configuration

4.1.1. File Locations

4.1.2. File Content