4.1.2 `my.cnf' Option Files

MySQL can, since Version 3.22, read default startup options for the server and for clients from option files.

On Windows, MySQL reads default options from the following files:

windows-directory is the location of your Windows directory.

On Unix, MySQL reads default options from the following files:

DATADIR is the MySQL data directory (typically `/usr/local/mysql/data' for a binary installation or `/usr/local/var' for a source installation). Note that this is the directory that was specified at configuration time, not the one specified with --datadir when mysqld starts up! (--datadir has no effect on where the server looks for option files, because the server looks for files before it processes any command-line arguments.)

Note that on Windows you should specify all paths in option files with `/' instead of `\'. If you use `\', you need to specify it twice, because `\' is the escape character in MySQL.

MySQL tries to read option files in the order listed above. 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. Options specified on the command-line take precedence over options specified in any option file. Some options can be specified using environment variables. Options specified on the command-line or in option files take precedence over environment variable values. See section F Environment Variables.

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

Since Version 4.0.2, you can use the loose prefix for command-line options (or options in my.cnf). If an option is prefixed by loose, the program reading it will not exit with an error if an option is unknown, but will rather only issue a warning:

shell> mysql --loose-no-such-option

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 (without the leading double dash). Run the program with the --help option to get a list of available options.

An option file can contain lines of the following forms:

#comment

Comment lines start with `#' or `;'. Empty lines are ignored.

[group]

group is the name of the program or group for which you want to set options. After a group line, any option or set-variable lines apply to the named group until the end of the option file or another group line is given.

option

This is equivalent to --option on the command-line.

option=value

This is equivalent to --option=value on the command-line.

set-variable = variable=value

This is equivalent to --set-variable variable=value on the command-line. Please note that --set-variable is deprecated since MySQL 4.0; as of that version, program variable names can be used as option names. On the command line, just use --variable=value. In an option file, use variable=value.

The [client] group allows you to specify options that apply to all MySQL clients (not mysqld). This is the perfect group to use to specify the password that you use to connect to the server. (But make sure the option file is readable and writable only by yourself.)

Note that for options and values, all leading and trailing blanks are automatically deleted. You may use the escape sequences `\b', `\t', `\n', `\r', `\\', and `\s' in your value string (`\s' == blank).

Here is a typical global option file:

[client]
port=3306
socket=/tmp/mysql.sock

[mysqld]
port=3306
socket=/tmp/mysql.sock
set-variable = key_buffer_size=16M
set-variable = max_allowed_packet=1M

[mysqldump]
quick

Here is typical user option file:

[client]
# The following password will be sent to all standard MySQL clients
password=my_password

[mysql]
no-auto-rehash
set-variable = connect_timeout=2

[mysqlhotcopy]
interactive-timeout

If you have a source distribution, you will find sample configuration files named `my-xxxx.cnf' in the `support-files' directory. If you have a binary distribution, look in the `DIR/support-files' directory, where DIR is the pathname to the MySQL installation directory (typically `C:\mysql' or `/usr/local/mysql'). Currently there are sample configuration files for small, medium, large, and very large systems. You can copy `my-xxxx.cnf' to your home directory (rename the copy to `.my.cnf') to experiment with this.

All MySQL programs that support option files support the following options:

Note that the options just shown must be first on the command line to work, with the exception that --print-defaults may be used immediately after --defaults-file or --defaults-extra-file.

Note for developers: Option file handling is implemented simply by processing all matching options (that is, options in the appropriate group) before any command-line arguments. This works nicely for programs that use the last instance of an option that is specified multiple times. If you have an old program that handles multiply specified options this way but doesn't read option files, you need add only two lines to give it that capability. Check the source code of any of the standard MySQL clients to see how to do this.

In shell scripts, you can use the my_print_defaults command to parse the option files. The following example shows the output that my_print_defaults might produce when asked to show the options found in the [client] and [mysql] groups:

shell> my_print_defaults client mysql
--port=3306
--socket=/tmp/mysql.sock
--no-auto-rehash

User Comments

The windows version (at least) includes 4 versions
to play with my-huge,large,medium and small.cnf
(nothing to explain what the difference is between
them though!!!!)

Correction to the above obviously misinformed
answer: All the information on discerning the
information between .cnf files is in the .cnf
files themself. Just view them with your stand
text editor and there are comments right at the
beginning explaining when and why to use the
specific .cnf file.

Actually, the first comment is correct: there are
four my-xxx.cnf files, and all are empty.
This "problem" is with an rpm installation of
mysql 3.23.53a on RH 8.0. Has happened to me
on earlier versions of RH and mysql (7.0 and
3.23.xx, respectively). Is there a reason for
this?

my.cnf can now also be in /etc/mysql/my.cnf

It would be nice to have a complete list (in one place) of ALL options that can be specified for each category. Right now, the options for connecting securely with the mysql client using certicates are found in the section on setting up certificates in Mysql, but not mentioned here in the section on Option files.

would be nice to include the innodb settings here as well.

Add your own comment.