Documentation Home
MySQL 5.7 Reference Manual
Related Documentation Download this Manual
PDF (US Ltr) - 35.6Mb
PDF (A4) - 35.6Mb
PDF (RPM) - 34.6Mb
EPUB - 8.7Mb
HTML Download (TGZ) - 8.5Mb
HTML Download (Zip) - 8.5Mb
HTML Download (RPM) - 7.3Mb
Eclipse Doc Plugin (TGZ) - 9.4Mb
Eclipse Doc Plugin (Zip) - 11.5Mb
Man Pages (TGZ) - 202.2Kb
Man Pages (Zip) - 307.5Kb
Info (Gzip) - 3.3Mb
Info (Zip) - 3.3Mb
Excerpts from this Manual

MySQL 5.7 Reference Manual  /  ...  /  Using Option Files

5.2.6 Using Option Files

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.

To determine whether a program reads option files, invoke it with the --help option. (For mysqld, use --verbose and --help.) If the program reads option files, the help message indicates which files it looks for and which option groups it recognizes.

Note

A MySQL program started with the --no-defaults option reads no option files other than .mylogin.cnf.

Many option files are plain text files, created using any text editor. The exception is the .mylogin.cnf file that contains login path options. This is an encrypted file created by the mysql_config_editor utility. See Section 5.6.6, “mysql_config_editor — MySQL Configuration Utility”. A login path is an option group that permits only certain options: host, user, password, port and socket. Client programs specify which login path to read from .mylogin.cnf using the --login-path option.

To specify an alternative login path file name, set the MYSQL_TEST_LOGIN_FILE environment variable. This variable is used by the mysql-test-run.pl testing utility, but also is recognized by mysql_config_editor and by MySQL clients such as mysql, mysqladmin, and so forth.

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 using the appropriate method, as just discussed.

Note

Option files used with MySQL Cluster programs are covered in Section 19.3, “Configuration of MySQL Cluster”.

On Windows, MySQL programs read startup options from the files shown in the following table, in the specified order (top files are read first, files read later take precedence).

Table 5.1 Option Files Read on Windows Systems

File NamePurpose
%PROGRAMDATA%\MySQL\MySQL Server 5.7\my.ini, %PROGRAMDATA%\MySQL\MySQL Server 5.7\my.cnfGlobal options
%WINDIR%\my.ini, %WINDIR%\my.cnfGlobal options
C:\my.ini, C:\my.cnfGlobal options
BASEDIR\my.ini, BASEDIR\my.cnfGlobal options
defaults-extra-fileThe file specified with --defaults-extra-file, if any
%APPDATA%\MySQL\.mylogin.cnfLogin path options (clients only)

In the preceding table, %PROGRAMDATA% represents the file system directory that contains application data for all users on the host. This path defaults to C:\ProgramData on Microsoft Windows Vista and greater, and C:\Documents and Settings\All Users\Application Data on older versions of Microsoft Windows.

%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.7 has been installed using MySQL Installer, this is typically C:\PROGRAMDIR\MySQL\MySQL 5.7 Server where PROGRAMDIR represents the programs directory (usually Program Files on English-language versions of Windows), See Section 2.3.3, “Installing MySQL on Microsoft Windows Using MySQL Installer”.

On Unix and Unix-like systems, MySQL programs read startup options from the files shown in the following table, in the specified order (top files are read first, files read later take precedence).

Note

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

Table 5.2 Option Files Read on Unix and Unix-Like Systems

File NamePurpose
/etc/my.cnfGlobal options
/etc/mysql/my.cnfGlobal options
SYSCONFDIR/my.cnfGlobal options
$MYSQL_HOME/my.cnfServer-specific options (server only)
defaults-extra-fileThe file specified with --defaults-extra-file, if any
~/.my.cnfUser-specific options
~/.mylogin.cnfUser-specific login path options (clients only)

In the preceding table, ~ represents the current user's home directory (the value of $HOME).

SYSCONFDIR represents the directory specified with the SYSCONFDIR option to CMake when MySQL was built. By default, this is the 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:

  • Let BASEDIR and DATADIR represent the path names of the MySQL base directory and data directory, respectively.

  • As of MySQL 5.7.8, if MYSQL_HOME is not set, mysqld_safe sets it to BASEDIR.

  • Prior to MySQL 5.7.8, if there is a my.cnf file in DATADIR but not in BASEDIR, mysqld_safe sets MYSQL_HOME to DATADIR. Otherwise, if there is no my.cnf file in DATADIR, mysqld_safe sets MYSQL_HOME to BASEDIR.

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 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.

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, use --verbose and --help.)

The syntax for specifying options in an option file is similar to command-line syntax (see Section 5.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 example, --quick and --host=localhost on the command line should be specified as quick and host=localhost on separate lines 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. Nonempty lines can take any of the following forms:

  • #comment, ;comment

    Comment lines start with # or ;. A # comment can start in the middle of a line as well.

  • [group]

    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 given. Option group names are not case sensitive.

  • opt_name

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

  • opt_name=value

    This is equivalent to --opt_name=value 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 # comment character.

Leading and trailing spaces are automatically deleted from option names and values.

You can use the escape sequences \b, \t, \n, \r, \\, and \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, \s is converted to a space.

  • A backslash not followed by a valid escape sequence character remains unchanged. For example, \S is 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 sequence character, \x becomes x rather than \x. See Section 10.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 \\ or \ 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 C:\Program Files\MySQL\MySQL Server 5.7 in an option file. This can be done several ways. Some examples:

basedir="C:\Program Files\MySQL\MySQL Server 5.7"
basedir="C:\\Program Files\\MySQL\\MySQL Server 5.7"
basedir="C:/Program Files/MySQL/MySQL Server 5.7"
basedir=C:\\Program\sFiles\\MySQL\\MySQL\sServer\s5.7

If an option group name is the same as a program name, options in the group apply specifically to that program. For example, the [mysqld] and [mysql] groups apply to the mysqld server and the mysql client program, respectively.

The [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 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.

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

To create option groups to be read only by mysqld servers from specific MySQL release series, use groups with names of [mysqld-5.6], [mysqld-5.7], and so forth. The following group indicates that the sql_mode setting should be used only by MySQL servers with 5.7.x version numbers:

[mysqld-5.7]
sql_mode=TRADITIONAL

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 directive:

!include /home/mydir/myopt.cnf

To search the /home/mydir directory and read option files found there, use this directive:

!includedir /home/mydir

MySQL makes no guarantee about the order in which option files in the directory will be read.

Note

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 .ini or .cnf extension.

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 [group] 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 my.cnf file contains this line:

!include /home/mydir/myopt.cnf

And suppose that /home/mydir/myopt.cnf looks like this:

[mysqladmin]
force

[mysqld]
key_buffer_size=16M

If my.cnf is processed by mysqld, only the [mysqld] group in /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.

The !includedir directive is processed similarly except that all option files in the named directory are read.


User Comments
  Posted by chad clark on May 24, 2007
I just installed 5.1 in a directory for testing on the same machine that 4.0 runs in production. mysqld reads /etc/my.cnf but that file contains the production (4.0) configuration.

To tell the 5.1 install to not read the /etc/my.cnf being used by 4.0 run the 5.1 mysqld_safe with --defaults-file=/etc/my.cnf-5.1.18 using your new config file.

  Posted by Jonathan Dzoba on January 21, 2010
In a Windows command prompt, typing mysql --default-file=<install path> can be rather tedious. It is easier to create a shortcut to mysql.exe and add --default-file=<install path> to the end of the Target field of the shortcut's Properties.
  Posted by Hellen Zhe-L on June 1, 2010
if using binary package (.dmg insaller) to install MySQL 5.1.47 under Mac Book, no "my.cnf" is installed. There is one under <INSTALLLOCATION>/mysql/tests, which can be used as a template to create your own.

To start MySQL server on Startup using MySQL Start Item, you must create my.cnf under <INSTALLLOCATION>/mysql/data, however, it does not appear that even system root has permission to create a file under this folder. I tried to change the owner and group of mysql/data into system root first to create this file, but failed.

The conclusion is: MySQL Start Item only starts server with basic default options, if specific options need to be passed to mysqld_safe, the server has to be started from command line with --defaults-extra-file option, which specifies the location of my.cnf
  Posted by Michaela Stephens on November 8, 2010
the default MySQL that comes with Mac OS X server (version 10.4) does not seem to use any of the default options files. it has it's own startup options.

see /System/Library/StartupItems/MySQL/*

to enable networking, you need to edit /etc/mysqlManager.plist

hope this helps somebody else.
  Posted by Tim MAHER-DE TROYER on May 26, 2016
Update to the 2010 comments.
OS X users will note that StartupItems are not really in use any more.
The mysqld startup uses a plist in /Library/LaunchDaemons called com.oracle.oss.mysql.mysqld.plist
That has command line options which override some of things in /etc/my.cnf and all the other my.cnf files that you may have dotted about.
I spent a particularly long time digging this up so that I could move my data directory to a sensible, large, data volume.
The .dmg install of 5.7.12 does not contain any default my.cnf so you will have to make your own or copy an old one.
Sign Up Login You must be logged in to post a comment.