 |  |
| |
Configuration
Server Startup and Shutdown
Logging
Backup
Recovery
Table Maintenance and Crash Recovery
For the most part, MySQL is low-maintenance software. Once you have installed MySQL, it does not place heavy administrative demands on you. It is not, however, maintenance free. Typical MySQL administration tasks include:
Installation
Configuration and tuning
Access control
Logging
Backup and recovery
Table maintenance
We cover some of these topics in detail in other chapters—installation in Chapter 2, performance tuning in Chapter 5, and access control in Chapter 6.
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]
[15]On Mac OS X, you must enable the root user to gain root access. As of now, there is no way to manage the MySQL operating system needs by supplying the password of a user with administrative privileges.
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.
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.
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 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.
|  | |
| 3.9. Advanced Features |  | 4.2. Server Startup and Shutdown |
Copyright © 2003 O'Reilly & Associates. All rights reserved.