- MySQL 5.1 Reference Manual :: 4 MySQL Programs :: 4.2 Using MySQL Programs :: 4.2.3 Specifying Program Options :: 4.2.3.3 Using Option Files
-
- MySQL 5.1 Reference Manual
- Preface, Notes, Licenses
- 1 General Information
- 2 Installing and Upgrading MySQL
- 3 Tutorial
- 4 MySQL Programs
- 5 MySQL Server Administration
- 6 Backup and Recovery
- 7 Optimization
- 8 Language Structure
- 9 Internationalization and Localization
- 10 Data Types
- 11 Functions and Operators
- 12 SQL Statement Syntax
- 13 Storage Engines
- 14 High Availability and Scalability
- 15 MySQL Enterprise Monitor
- 16 Replication
- 17 MySQL Cluster NDB 6.X/7.X
- 18 Partitioning
- 19 Stored Programs and Views
- 20 INFORMATION_SCHEMA Tables
- 21 Connectors and APIs
- 22 Extending MySQL
- A MySQL 5.1 Frequently Asked Questions
- B Errors, Error Codes, and Common Problems
- C MySQL Change History
- D Restrictions and Limits
- Index
- Standard Index
- C Function Index
- Command Index
- Function Index
- INFORMATION_SCHEMA Index
- Transaction Isolation Level Index
- JOIN Types Index
- Operator Index
- Option Index
- Privileges Index
- SQL Modes Index
- Status Variable Index
- Statement/Syntax Index
- System Variable Index
[+/-]
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. For the MySQL server, MySQL provides a number of preconfigured option files.
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
Option files used with MySQL Cluster programs are covered in Section 17.3, “MySQL Cluster Configuration”.
On Windows, MySQL programs read startup options from the following files.
File Name Purpose
,WINDIR
\my.iniWINDIR
\my.cnfGlobal options C:\my.ini
,C:\my.cnf
Global options
,INSTALLDIR
\my.iniINSTALLDIR
\my.cnfGlobal options defaults-extra-file
The file specified with --defaults-extra-file=
, if anypath
WINDIR
represents the location of your Windows directory. This is commonlyC:\WINDOWS
. You can determine its exact location from the value of theWINDIR
environment variable using the following command:C:\>
echo %WINDIR%
INSTALLDIR
represents the MySQL installation directory. This is typicallyC:\
wherePROGRAMDIR
\MySQL\MySQL 5.1 ServerPROGRAMDIR
represents the programs directory (usuallyProgram Files
on English-language versions of Windows), when MySQL 5.1 has been installed using the installation and configuration wizards. See The Location of the my.ini File.On Unix, MySQL programs read startup options from the following files.
File Name Purpose /etc/my.cnf
Global options /etc/mysql/my.cnf
Global options (as of MySQL 5.1.15) SYSCONFDIR
/my.cnfGlobal options $MYSQL_HOME/my.cnf
Server-specific options defaults-extra-file
The file specified with --defaults-extra-file=
, if anypath
~/.my.cnf
User-specific options ~
represents the current user's home directory (the value of$HOME
).SYSCONFDIR
represents the directory specified with the--sysconfdir
option to configure when MySQL was built. By default, this is theetc
directory located under the compiled-in installation directory. This location is used as of MySQL 5.1.10. (From 5.1.10 to 5.1.22, it was read last, after~/.my.cnf
.)MYSQL_HOME
is an environment variable containing the path to the directory in which the server-specificmy.cnf
file resides.If
MYSQL_HOME
is not set and you start the server using the mysqld_safe program, mysqld_safe attempts to setMYSQL_HOME
as follows:Let
BASEDIR
andDATADIR
represent the path names of the MySQL base directory and data directory, respectively.If there is a
my.cnf
file inDATADIR
but not inBASEDIR
, mysqld_safe setsMYSQL_HOME
toDATADIR
.Otherwise, if
MYSQL_HOME
is not set and there is nomy.cnf
file inDATADIR
, mysqld_safe setsMYSQL_HOME
toBASEDIR
.
In MySQL 5.1, use of
DATADIR
as the location formy.cnf
is deprecated.Typically,
DATADIR
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 the--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.Note
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
--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 and you specify only one option per line. For example,
--quick
and--host=localhost
on the command line should be specified asquick
andhost=localhost
on separate lines in an option file. To specify an option of the form--loose-
in an option file, write it asopt_name
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. -
opt_name
This is equivalent to
--
on the command line.opt_name
-
opt_name
=value
This is equivalent to
--
on the command line. In an option file, you can have spaces around the “opt_name
=value
=
” character, something that is not true on the command line. You can optionally enclose the value within single quotation marks or double quotation marks, which is useful if the value contains a “#
” comment character.
For options that take a numeric value, the value can be given with a suffix of
K
,M
, orG
(either uppercase or lowercase) to indicate a multiplier of 1024, 10242 or 10243. For example, the following command tells mysqladmin to ping the server 1024 times, sleeping 10 seconds between each ping:mysql>
mysqladmin --count=1K --sleep=10 ping
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. The escaping rules in option files are: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 space.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 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 value escape sequence character, “\
” becomes “x
x
” rather than “\
”. See Section 8.1.1, “Strings”.x
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 ofC:\Program Files\MySQL\MySQL Server 5.1
in an option file. This can be done several ways. Some examples:basedir="C:\Program Files\MySQL\MySQL Server 5.1" basedir="C:\\Program Files\\MySQL\\MySQL Server 5.1" basedir="C:/Program Files/MySQL/MySQL Server 5.1" basedir=C:\\Program\sFiles\\MySQL\\MySQL\sServer\s5.1
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 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.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 thevar_name
=value
key_buffer_size
andmax_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 want to create option groups that should be read by mysqld servers from a specific MySQL release series only, you can do this by using groups with names of
[mysqld-5.0]
,[mysqld-5.1]
, and so forth. The following group indicates that the--new
option should be used only by MySQL servers with 5.1.x version numbers:[mysqld-5.1] new
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
There is no guarantee about the order in which the option files in the directory will be read.
Note
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.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
[
line that indicates the program to which the options apply.group
]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[mysqldamin]
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.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 the exception that
--print-defaults
may be used immediately after--defaults-file
or--defaults-extra-file
. Also, when specifying file names, you should avoid the use of the “~
” shell metacharacter because it might not be interpreted as you expect.-
--defaults-extra-file=
file_name
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. If the file does not exist or is otherwise inaccessible, the program will exit with an error. -
Use only the given option file.
file_name
is the full path name to the file. If the file does not exist or is otherwise inaccessible, the program will exit with an error. -
If this option is given, the program reads not only its usual option groups, but also groups with the usual names and a suffix of
str
. For example, the mysql client normally reads the[client]
and[mysql]
groups. If the--defaults-group-suffix=_other
option is given, mysql also reads the[client_other]
and[mysql_other]
groups. -
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 such as
my-small.cnf
,my-medium.cnf
,my-large.cnf
, andmy-huge.cnf
, which are sample option files for small, medium, large, and very large systems. On Windows, the extension is.ini
rather than.cnf
.Note
On Windows, the
.ini
or.cnf
option file extension might not be displayed.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 4.2.3.3, “Using Option Files”.