Most MySQL programs can read startup options from option files (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. For the MySQL server, MySQL provides a number of preconfigured option files.
To determine whether a program reads option files, invoke it with
--help option. (For
--help.) If the program reads
option files, the help message indicates which files it looks for
and which option groups it recognizes.
A MySQL program started with the
option reads no option files.
Option files are plain text files, created using any text editor.
MySQL looks for option files in the order described in the following discussion and reads any that exist. If an option file you want to use does not exist, create it with a plain text editor.
Option files used with NDB Cluster programs are covered in Section 18.3, “Configuration of NDB Cluster”.
On Windows, MySQL programs read startup options from the files shown in the following table, in the specified order (files listed first are read first, files read later take precedence).
Table 4.1 Option Files Read on Windows Systems
||The file specified with
In the preceding table,
%WINDIR% represents the
location of your Windows directory. This is commonly
C:\WINDOWS. Use the following command to
determine its exact location from the value of the
WINDIR environment variable:
C:\> echo %WINDIR%
%APPDATA% represents the value of the Windows
application data directory. Use the following command to determine
its exact location from the value of the
APPDATA environment variable:
C:\> echo %APPDATA%
BASEDIR represents the MySQL base
installation directory. When MySQL 5.5 has been
installed using MySQL Installer, this is typically
PROGRAMDIR represents the programs
Program Files on
English-language versions of Windows), See
Section 2.3.3, “MySQL Installer for Windows”.
On Unix and Unix-like systems, MySQL programs read startup options from the files shown in the following table, in the specified order (files listed first are read first, files read later take precedence).
On Unix platforms, MySQL ignores configuration files that are world-writable. This is intentional as a security measure.
Table 4.2 Option Files Read on Unix and Unix-Like Systems
||Server-specific options (server only)|
||The file specified with
In the preceding table,
~ represents the
current user's home directory (the value of
SYSCONFDIR represents the directory
specified with the
to CMake when MySQL was built. By default, this
etc directory located under the
compiled-in installation directory.
MYSQL_HOME is an environment variable
containing the path to the directory in which the server-specific
my.cnf file resides. If
MYSQL_HOME is not set and you start the server
using the mysqld_safe program,
mysqld_safe attempts to set
MYSQL_HOME as follows:
DATADIRrepresent the path names of the MySQL base directory and data directory, respectively.
If there is a
DATADIRbut not in
BASEDIR, mysqld_safe sets
MYSQL_HOMEis not set and there is no
DATADIR, mysqld_safe sets
In MySQL 5.5, use of
DATADIR as the location for
my.cnf is deprecated.
DATADIR is commonly
/usr/local/mysql/data, although this can vary
per platform or installation method. The value is the data
directory location built in when MySQL was compiled, not the
location specified with the
--datadir option when
mysqld starts. Use of
--datadir at runtime has no effect
on where the server looks for option files that it reads before
processing any options.
If multiple instances of a given option are found, the last
instance takes precedence, with one exception: For
mysqld, the first instance
--user option is used as a
security precaution, to prevent a user specified in an option file
from being overridden on the command line.
The following description of option file syntax applies to files
that you edit manually. This excludes
.mylogin.cnf, which is created using
mysql_config_editor and is encrypted.
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. (For mysqld,
The syntax for specifying options in an option file is similar to
command-line syntax (see Section 4.2.4, “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
--host=localhost on the command line should be
host=localhost on separate lines in an option
file. To specify an option of the form
--loose- in 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
#comment can start in the middle of a line as well.
groupis 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 given. Option group names are not case-sensitive.
This is equivalent to
--on the command line.
This is equivalent to
--on the command line. In an option file, you can have spaces around the
=character, something that is not true on the command line. The value optionally can be enclosed within single quotation marks or double quotation marks, which is useful if the value contains a
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. In
option files, these escaping rules apply:
A backslash followed by a valid escape sequence character is converted to the character represented by the sequence. For example,
\sis converted to a space.
A backslash not followed by a valid escape sequence character remains unchanged. For example,
\Sis retained as is.
The preceding rules mean that a literal backslash can be given as
\\, 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 valid escape
x” rather than
Section 9.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
\\ 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
\. Suppose that you want to specify
a base directory of
C:\Program Files\MySQL\MySQL Server
5.5 in an option file. This can be done
several ways. Some examples:
basedir="C:\Program Files\MySQL\MySQL Server 5.5" basedir="C:\\Program Files\\MySQL\\MySQL Server 5.5" basedir="C:/Program Files/MySQL/MySQL Server 5.5" basedir=C:\\Program\sFiles\\MySQL\\MySQL\sServer\s5.5
If an option group name is the same as a program name, options in
the group apply specifically to that program. For example, the
apply to the mysqld server and the
mysql client program, respectively.
[client] option group is read by all client
programs provided in MySQL distributions (but
not by mysqld). To
understand how third-party client programs that use the C API can
use option files, see the C API documentation at
Section 22.214.171.124, “mysql_options()”.
[client] group enables you to specify
options that apply to all clients. For example,
[client] is the appropriate group to use to
specify the password for connecting to the server. (But make sure
that the option file is accessible only by yourself, so that other
people cannot discover 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.
List more general option groups first and more specific groups
later. For example, a
[client] group is more
general because it is read by all client programs, whereas a
[mysqldump] group is read only by
mysqldump. Options specified later override
options specified earlier, so putting the option groups in the
enables mysqldump-specific options to override
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
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
To create option groups to be read only by
mysqld servers from specific MySQL release
series, use groups with names of
[mysqld-5.5], and so forth. The
following group indicates that the
sql_mode setting should be used
only by MySQL servers with 5.5.x version numbers:
It is possible to use
!include directives in
option files to include other option files and
!includedir to search specific directories for
option files. For example, to include the
/home/mydir/myopt.cnf file, use the following
To search the
/home/mydir directory and read
option files found there, use this directive:
MySQL makes no guarantee about the order in which option files in the directory will be read.
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
[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
[mysqladmin] 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 are
If an option file contains
!includedir directives, files named by those
directives are processed whenever the option file is processed, no
matter where they appear in the file.