Using Option Files

| Print |

E-Mail

 

 

Using Option Files - MySQL programs can read startup options from option files (also sometimes called configuration files). Option files provide a convenient way to specify commonly used options so that they need not be entered on the command line each time you run a program.

The following programs support option files: myisamchk, myisampack, mysql, mysql.server, mysqladmin, mysqlbinlog, mysqlcc, mysqlcheck, mysqld_safe, mysqldump, mysqld, mysqlhotcopy, mysqlimport, and mysqlshow.

 

On Windows, MySQL programs read startup options from the following files:

WINDIR represents the location of your Windows directory. This is commonly C:\WINDOWS or C:\WINNT. You can determine its exact location from the value of the WINDIR environment variable using the following command:

C:\> echo %WINDIR%

INSTALLDIR represents the installation directory of MySQL. This is typically C:\PROGRAMDIR\MySQL\MySQL 5.0 Server where PROGRAMDIR represents the programs directory (usually Program Files on English-language versions of Windows), when MySQL 5.0 has been installed using the installation and configuration wizards.

On Unix, MySQL programs read startup options from the following files:

MYSQL_HOME is an environment variable containing the path to the directory in which the server-specific my.cnf file resides. (This was DATADIR prior to MySQL version 5.0.3.)

If MYSQL_HOME is not set and there is a my.cnf file in DATADIR and there is no my.cnf file in BASEDIR, mysqld_safe sets MYSQL_HOME to DATADIR. Otherwise, if MYSQL_HOME is not set and there is no my.cnf in DATADIR, then mysqld_safe sets MYSQL_HOME to BASEDIR.

Typically this is /usr/local/mysql/data for a binary installation or /usr/local/var for a source installation. Note that this is the data directory location that was specified at configuration time, not the one specified with --datadir when mysqld starts. Use of --datadir at runtime has no effect on where the server looks for option files, because it looks for them before processing any command-line arguments.

MySQL looks for option files in the order just described and reads any that exist. If an option file that you want to use does not exist, create it with a plain text editor. If multiple option files exist, an option specified in a file read later takes precedence over the same option specified in a file read earlier.

Note: On Unix platforms, MySQL ignores configuration files that are world-writable. This is intentional, and acts as a security measure.

Any long option that may be given on the command line when running a MySQL program can be given in an option file as well. To get the list of available options for a program, run it with the --help option.

The syntax for specifying options in an option file is similar to command-line syntax, except that you omit the leading two dashes. For example, --quick or --host=localhost on the command line should be specified as quick or host=localhost in an option file. To specify an option of the form --loose-opt_name in an option file, write it as loose-opt_name.

Empty lines in option files are ignored. Non-empty lines can take any of the following forms:

Leading and trailing blanks are automatically deleted from option names and values. You may use the escape sequences ‘\b’, ‘\t’, ‘\n’, ‘\r’, ‘\\’, and ‘\s’ in option values to represent the backspace, tab, newline, carriage return, and space characters.

On Windows, if an option value represents a pathname, you should specify the value using ‘/’ rather than ‘\’ as the pathname separator. If you use ‘\’, you must double it as ‘\\’, because ‘\’ is the escape character in MySQL.

If an option group name is the same as a program name, options in the group apply specifically to that program.

The [client] option group is read by all client programs (but not by mysqld). This allows you to specify options that apply to all clients. For example, [client] is the perfect group to use to specify the password that you use to connect to the server. (But make sure that the option file is readable and writable only by yourself, so that other people cannot find out your password.) Be sure not to put an option in the [client] group unless it is recognized by all client programs that you use. Programs that do not understand the option quit after displaying an error message if you try to run them.

Beginning MySQL 5.0.4 in the 5.0 series, it is possible to use !include directives in option files to include specific files and !includedir to search specific directories for option files. For example, to include the file /home/mydir/myopt.cnf, you can use the following:

!include /home/me/myopt.cnf

To search the directory /home/mydir for all files ending in .cnf and to read these as option files, you would use:

!includedir /home/mydir

Note that these options are section-specific. For example, suppose that you were to use something in my.cnf such as the following:

[mysqld]

!include /home/mydir/myopt.cnf

In such a case, the file myopt.cnf would be processed only for the server, and the !include directive would be ignored by any client applications. However, if you were to use the following:

[mysqldump]

!includedir /home/mydir/my-dump-options

then the directory /home/mydir/my-dump-options would be checked for option files ending in .cnf by mysqldump only, and not by the server or by any other client applications.

Note: Currently, any files to be found and included using the !includedir directive on Unix operating systems must have filenames ending in .cnf. On Windows, this directive also checks for files with a .ini extension (in addition to .cnf).

If you want to create option groups that should be read by one specific mysqld server release series only, you can do this by using groups with names of [mysqld-4.1], [mysqld-5.0], and so forth. The following group indicates that the --new option should be used only by MySQL servers with 5.0.x version numbers:

[mysqld-5.0]

new

Here is a typical global option file:

[client]

port=3306

socket=/tmp/mysql.sock



[mysqld]

port=3306

socket=/tmp/mysql.sock

key_buffer_size=16M

max_allowed_packet=8M



[mysqldump]

quick

The preceding option file uses var_name=value syntax for the lines that set the key_buffer_size and max_allowed_packet variables.

Here is a typical user option file:

[client]

# The following password will be sent to all standard MySQL clients

password="my_password"



[mysql]

no-auto-rehash

connect_timeout=2



[mysqlhotcopy]

interactive-timeout

If you have a source distribution, you can find sample option files named my-xxxx.cnf in the support-files directory. If you have a binary distribution, look in the support-files directory under your MySQL installation directory.

 

Users' Comments (0)

Comment an article   Name   E-mail    Title Available characters: 4000  Notify me of follow-up comments Enter what you see:

No comment posted