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.
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, in the specified order (top items are used first).
| File Name | Purpose |
|---|---|
,
| Global options |
C:\my.ini, C:\my.cnf | Global options |
,
| Global options |
defaults-extra-file | The file specified with
--defaults-extra-file=,
if any |
%WINDIR% represents the location of your
Windows directory. This is commonly
C:\WINDOWS. You can determine its exact
location from the value of the WINDIR
environment variable using the following command:
C:\> echo %WINDIR%
INSTALLDIR represents the MySQL
installation directory. This is typically
C:\ where
PROGRAMDIR\MySQL\MySQL
5.0 ServerPROGRAMDIR represents the programs
directory (usually Program Files on
English-language versions of Windows), when MySQL 5.0
has been installed using the installation and configuration
wizards. See Section 2.10.3.1, “Starting the MySQL Server Instance Configuration Wizard”.
On Unix, Linux and Mac OS X, MySQL programs read startup options from the following files, in the specified order (top items are used first).
| File Name | Purpose |
|---|---|
/etc/my.cnf | Global options |
| Global options |
$MYSQL_HOME/my.cnf | Server-specific options |
defaults-extra-file | The file specified with
--defaults-extra-file=,
if any |
~/.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 the etc directory located under the
compiled-in installation directory. This location is used as of
MySQL 5.0.21. (From 5.0.21 to 5.0.53, it was read last, after
~/.my.cnf.)
MYSQL_HOME is an environment variable
containing the path to the directory in which the server-specific
my.cnf file resides. (This was
DATADIR prior to MySQL version 5.0.3.)
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.
If there is a my.cnf file in
DATADIR but not in
BASEDIR,
mysqld_safe sets
MYSQL_HOME to
DATADIR.
Otherwise, if MYSQL_HOME is not set and
there is no my.cnf file in
DATADIR,
mysqld_safe sets
MYSQL_HOME to
BASEDIR.
In MySQL 5.0, use of
DATADIR as the location for
my.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.
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 (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
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- in an
option file, write it as
opt_nameloose-.
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
-- 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.
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 valid escape
sequence character,
“\”
becomes “xx” rather than
“\”.
See Section 9.1.1, “String Literals”.
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 of
C:\Program Files\MySQL\MySQL Server
5.0 in an option file. This can be done
several ways. Some examples:
basedir="C:\Program Files\MySQL\MySQL Server 5.0" basedir="C:\\Program Files\\MySQL\\MySQL Server 5.0" basedir="C:/Program Files/MySQL/MySQL Server 5.0" basedir=C:\\Program\sFiles\\MySQL\\MySQL\sServer\s5.0
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 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 the
var_name=valuekey_buffer_size and
max_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-4.1],
[mysqld-5.0], and so forth. The
following group indicates that the --new option
should be used only by MySQL servers with 5.0.x
version numbers:
[mysqld-5.0] new
Beginning with MySQL 5.0.4, 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.
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
[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
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.
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.
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
mysql on OSX looks for the global configuration file at /etc/my.cnf. You can copy sample config files from /usr/local/mysql/support-files. There should be five sample files, for small, medium, large, huge, and heavy configurations.
Small: System has <64MB memory, and MySQL is not used often.
Medium: System has at least 64MB memory
Large: System has at least 512MB memory and the server will run mainly MySQL.
Huge: System has at least 1GB memory and the server will run mainly MySQL.
Heavy: System has at least 4GB memory and the server will run mainly MySQL.
Copy the appropriate file by opening the Terminal application and typing this:
cd /usr/local/mysql/support-files
sudo cp my-whatever.cnf /etc/my.cnf
(Enter your OSX administrator password)
Now you should restart mysql:
sudo /Library/StartupItems/MySQLCOM/MySQLCOM stop
sudo /Library/StartupItems/MySQLCOM/MySQLCOM start
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.