Chapter 4. Database Administration
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:
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.
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:
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
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:
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. If you do specify the password in a configuration file, you should definitely make sure no one else can read that file.
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:
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:
The final format for MySQL options enables you to specify mysqld variables:
set-variable = max_allowed_packet=1M
Copyright © 2003 O'Reilly & Associates. All rights reserved.