11.3 Runtime MySQL Configuration

By default, the server uses built-in values for its configuration variables when it runs. If the default values aren't suitable, you can use runtime options to tell the server to use different values:

Several options specify the locations of important directories and files. For example, under Windows, the default value for the base installation directory is C:\mysql. If you install MySQL somewhere else, you'll need to tell the server the correct location by using the --basedir option or the server will not start. Similarly, if you use a data directory other than the directory named data under the base directory, you'll need to use a --datadir option to tell the server the correct location.
Options also can be used to override the server's built-in values for performance-related variables, such as those that control the maximum number of simultaneous connections and the sizes of buffers and caches.

The examples in this section concentrate on options that relate to directory and file layout. Chapter 16 concentrates on performance-related options and discusses how to use them to tune the server to run more efficiently.

To change the configuration and behavior of the server, specify runtime options when you start it. Options can be given on the command line or in option files (also known as configuration files). The "Core Study Guide" provides general background on the use of option files. That discussion occurs in the context of running client programs, but the bulk of it also applies to specifying server options.

The server looks for option files in several standard locations. It uses any that exist, but it is not an error for an option file to be missing. The standard files are different for Windows and Unix.

On Windows, the search order for option files is as follows:


WINDIR\my.ini

C:\my.cnf

WINDIR represents the location of the Windows directory. Typically, this is something like C:\Windows or C:\WinNT.

On Unix, the search order includes two general option files:


/etc/my.cnf

DATADIR/my.cnf

DATADIR represents the compiled-in location of the data directory. This means that if you have two servers on the same machine, each with a different compiled-in data directory location, you can specify options specific to each server by putting them in the my.cnf file of the appropriate data directory.

The Unix option file search order also includes ~/.my.cnf; that is, the .my.cnf file located in the home directory of the person running the program. However, because ~/.my.cnf is a user-specific file, it isn't an especially suitable location for server options. Normally, you invoke the server as mysql or as root with a --user=mysql option. The user-specific file that the server would read differs in the two cases, possibly leading to inconsistent sets of options being used.

To name a specific option file, use a --defaults-file=file_name option on the command line In this case, the server reads only the named file and ignores the standard option files. When you use this option, it must be the first one named on the command line.

To find out what options the server supports, invoke it manually with the --help option:


shell> mysqld --help

Any of the server options shown in the help message may be specified on the command line. However, it's more typical to list them in an option file, for several reasons:

By putting options in a file, you need not specify them on the command line each time you start the server. This is not only more convenient, it's less error-prone for complex options such as those used to configure the InnoDB tablespace.
If you invoke the server using the mysql.server startup script, you cannot specify server options on the command line of the script. (It understands arguments of start or stop only.) This makes use of an option file mandatory.
By looking at the option file, you can see immediately how you've configured the server to run.

To specify server startup options in an option file, use the [mysqld] option group. If the file does not exist, create it as a plain text file using an editor. To create or modify an option file, you must have write permission for it. The server itself needs only read access; it reads option files, but does not create or modify them.

The following examples illustrate some ways to use option files to specify server options:

If you install MySQL on Windows, the server assumes by default that the base installation directory is C:\mysql and the data directory is named data in the base directory. If you install MySQL somewhere else, such as E:\mysql, you must tell the server the location with a --basedir option. In an option file, the following lines accomplish that:
```
[mysqld]

basedir=E:/mysql
```
If you use the data directory under E:\mysql as the data directory, that's sufficient. However, if you use a different data directory location, you must specify the --datadir option as well:
```
[mysqld]

basedir=E:/mysql

datadir=D:/mysql-data
```
Note that in this case you'll also need to copy the data directory from under the base directory to the new location of D:\mysql-data before starting the server. If the server does not find the data directory in the location you specify in the option file, it will not start.
For any option that specifies a Windows pathname, write any backslashes in the name as slashes or as doubled backslashes. For example, to specify a basedir value of E:\mysql, you can write it using either of the following formats:
```
basedir=E:/mysql

basedir=E:\\mysql
```
On Windows NT, the mysqld-nt and mysql-max-nt servers are capable of supporting named pipe connections but do not enable them by default, as discussed in section 10.3, "Connecting the Client to the Server." To turn on named pipe support, use this option:
```
[mysqld]

enable-named-pipe
```
To enable logging, use the options that turn on the types of logs you want. The following options turn on the general query log, the binary log, and the slow query log:
```
[mysqld]

log

log-bin

log-slow-queries
```
Section 10.6, "Log and Status Files," discusses the logging options.

< Day Day Up >