Most 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. Option file capability is available from MySQL 3.22 on. For the MySQL server, MySQL provides a number of preconfigured option files.
To determine whether a program reads option files, invoke it
--help option. (For
--help as of MySQL 4.1.1.) If the
program reads option files, the help message indicates which
files it looks for and which option groups it recognizes.
Option files used with MySQL Cluster programs are covered in Section 15.3, “MySQL Cluster Configuration”.
On Windows, MySQL programs read startup options from the following files.
|The file specified with
Programs look for option files using both extensions
.cnf) in all
locations only as of MySQL 4.0.23 and 4.1.8. Before MySQL
4.0.23 and 4.1.8, programs look in
INSTALLDIR only for
my.ini, and in
%WINDIR% represents the location of your
Windows directory. This is commonly
C:\WINNT. You can determine its exact
location from the value of the
environment variable using the following command:
INSTALLDIR represents the MySQL
installation directory. With MySQL 4.1.5 and up, this is
PROGRAMDIR represents the programs
Program Files on
English-language versions of Windows), when MySQL
4.1 has been installed using the installation and
configuration wizards. See
Section 18.104.22.168, “The Location of the my.ini File”.
On Unix, MySQL programs read startup options from the following files.
|The file specified with
~ represents the current user's home
directory (the value of
DATADIR represents the path to the
directory in which the server-specific
/usr/local/mysql/data for a binary
/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 option 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 options.
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 instances of a given option are found, the last
instance takes precedence. There is one exception: For
mysqld, the first
instance of the
--user option is
used as a security precaution, to prevent a user specified in an
option file from being overridden on the command line.
On Unix platforms, MySQL ignores configuration files that are world-writable. This is intentional 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 syntax for specifying options in an option file is similar
to command-line syntax (see
Section 22.214.171.124, “Using Options on the Command Line”). However, in an option
file, you omit the leading two dashes from the option name and
you specify only one option per line. For example,
on the command line should be specified as
on separate lines in an option file. To specify an option of the
an option file, write it as
Empty lines in option files are ignored. Nonempty lines can take any of the following forms:
Comment lines start with “
;”. As of MySQL 4.0.14,
#” comment can start in
the middle of a line as well.
group is the name of the program
or group for which you want to set options. After a group
line, any option-setting lines apply to the named group
until the end of the option file or another group line is
This is equivalent to
the command line.
This is equivalent to
on the command line. In an option file, you can have spaces
around the “
something that is not true on the command line. As of MySQL
4.0.16, you can optionally enclose the value within double
quotation marks or single quotation marks. This is useful if
the value contains a “
Set the program variable
to the given value. This is equivalent to
on the command line. Spaces are permitted around the first
=” character but not around
the second. This syntax is deprecated as of MySQL 4.0. See
Section 126.96.36.199, “Using Options to Set Program Variables”, for more information on
setting program variables.
Leading and trailing spaces are automatically deleted from option names and values.
You can use the escape sequences
\s” in option values to
represent the backspace, tab, newline, carriage return,
backslash, and space characters. The escaping rules in option
If a backslash is followed by a valid escape sequence
character, the sequence is converted to the character
represented by the sequence. For example,
\s” is converted to a
If a backslash is not followed by a valid escape sequence
character, it remains unchanged. For example,
\S” is retained as is.
The preceding rules mean that a literal backslash can be given
\\”, or as
\” if it is not followed by a
valid escape sequence character.
The rules for escape sequences in option files differ slightly
from the rules for escape sequences in string literals in SQL
statements. In the latter context, if
x” is not a value
escape sequence character,
x” rather than
See Section 8.1.1, “String Literals”.
The escaping rules for option file values are especially
pertinent for Windows path names, which use
\” as a path name separator. A
separator in a Windows path name must be written as
\\” if it is followed by an
escape sequence character. It can be written as
\” if it is not. Alternatively,
/” may be used in Windows path
names and will be treated as
\”. Suppose that you want to
specify a base directory of
Files\MySQL\MySQL Server 4.1 in an
option file. This can be done several ways. Some examples:
basedir="C:\Program Files\MySQL\MySQL Server 4.1" basedir="C:\\Program Files\\MySQL\\MySQL Server 4.1" basedir="C:/Program Files/MySQL/MySQL Server 4.1" basedir=C:\\Program\sFiles\\MySQL\\MySQL\sServer\s4.1
If an option group name is the same as a program name, options
in the group apply specifically to that program. For example,
groups apply to the mysqld server and the
mysql client program, respectively.
[client] option group is read by all
client programs (but not by
mysqld). This enables 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
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
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
syntax for the lines that set the
Prior to MySQL 4.0.2, you must use
set-variable syntax instead (described
earlier in this section).
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 set-variable = connect_timeout=2 [mysqlhotcopy] interactive-timeout
As of MySQL 4.0.14, if you want to create option groups that
should be read only by mysqld servers from a
specific MySQL release series only, you can do this by using
groups with names of
[mysqld-4.1], and so forth. The following
group indicates that the
--new option should be
used only by MySQL servers with 4.0.x version numbers:
Beginning with MySQL 4.1.11, it is possible to use
!include directives in option files to
include other option files and
search specific directories for option files. For example, to
/home/mydir/myopt.cnf file, use
the following directive:
To search the
/home/mydir directory and
read option files found there, use this directive:
There is no guarantee about the order in which the option files in the directory will be read.
Currently, any files to be found and included using the
!includedir directive on Unix operating
systems must have file names ending in
.cnf. On Windows, this directive checks
for files with the
Write the contents of an included option file like any other
option file. That is, it should contain groups of options, each
preceded by a
[ line that
indicates the program to which the options apply.
While an included file is being processed, only those options in
groups that the current program is looking for are used. Other
groups are ignored. Suppose that a
file contains this line:
And suppose that
looks like this:
[mysqladmin] force [mysqld] key_buffer_size=16M
my.cnf is processed by
mysqld, only the
/home/mydir/myopt.cnf is used. If
the file is processed by mysqladmin, only the
[mysqldamin] group is used. If the file is
processed by any other program, no options in
/home/mydir/myopt.cnf are used.
!includedir directive is processed
similarly except that all option files in the named directory
Most MySQL programs that support option files handle the following options. They affect option-file handling, so they must be given on the command line and not in an option file. To work properly, each of these options must immediately follow the command name, with these exceptions:
On Windows, if the
--install options are given,
--install option must be first. See
Section 2.3.11, “Starting MySQL as a Windows Service”.
When specifying file names, you should avoid the use of the
~” shell metacharacter
because it might not be interpreted as you expect.
Read this option file after the global option file but (on
Unix) before the user option file.
file_name is the full path name
to the file.
Use only the given option file.
file_name is the full path name
to the file. If the file does not exist, the program exits
with an error.
Do not read any option files. If a program does not start
because it is reading unknown options from an option file,
--no-defaults can be used
to prevent the program from reading them.
Print the program name and all options that it gets from option files.
MySQL provides a number of preconfigured option files that can
be used as a basis for tuning the MySQL server. Look for files
my-huge.cnf, which are sample option
files for small, medium, large, and very large systems. On
Windows, the extension is
On Windows, the
.cnf option file extension might not be
For a binary distribution, look for the files in or under your
installation directory. If you have a source distribution,
look in the
support-files directory. You
can rename a copy of a sample file and place it in the
appropriate location for use as a base configuration file.
Regarding names and appropriate location, see the general
information provided in Section 188.8.131.52, “Using Option Files”.