Source code editor
What Is Ajax
There are several ways to specify options for MySQL programs:
List the options on the command line following the program name. This is most common for options that apply to a specific invocation of the program.
List the options in an option file that the program reads when it starts. This is common for options that you want the program to use each time it runs.
List the options in environment variables. This method is useful for options that you want to apply each time the program runs. In practice, option files are used more commonly for this purpose. However, Section 5.12.2, “Running Multiple Servers on Unix”, discusses one situation in which environment variables can be very helpful. It describes a handy technique that uses such variables to specify the TCP/IP port number and Unix socket file for both the server and client programs.
MySQL programs determine which options are given first by examining environment variables, then by reading option files, and then by checking the command line. This means that environment variables have the lowest precedence and command-line options the highest.
Because options are processed in order, if an option is specified
multiple times, the last occurrence takes precedence. The
following command causes mysql to connect to
the server running on
mysql -h example.com -h localhost
If conflicting or related options are given, later options take precedence over earlier options. The following command runs mysql in “no column names” mode:
mysql --column-names --skip-column-names
An option can be specified by writing it in full or as any
unambiguous prefix. For example, the
option can be given to mysqldump as
--compr, but not as
because that is ambiguous:
mysqldump --compmysqldump: ambiguous option '--comp' (compatible, compress)
Be aware that the use of option prefixes can cause problems in the event that new options are implemented for a program. A prefix that is unambigious now might become ambiguous in the future.
You can take advantage of the way that MySQL programs process options by specifying default values for a program's options in an option file. That enables you to avoid typing them each time you run the program, but also allows you to override the defaults if necessary by using command-line options.
Program options specified on the command line follow these rules:
Options are given after the command name.
An option argument begins with one dash or two dashes,
depending on whether it has a short name or a long name.
Many options have both forms. For example,
--help are the
short and long forms of the option that instructs a MySQL
program to display its help message.
Option names are case sensitive.
-V are both legal and have different
meanings. (They are the corresponding short forms of the
Some options take a value following the option name. For
-h localhost or
--host=localhost indicate the MySQL server
host to a client program. The option value tells the program
the name of the host where the MySQL server is running.
For a long option that takes a value, separate the option
name and the value by an ‘
sign. For a short option that takes a value, the option
value can immediately follow the option letter, or there can
be a space between:
-h localhost are equivalent. An exception
to this rule is the option for specifying your MySQL
password. This option can be given in long form as
--password. In the latter case (with
no password value given), the program prompts you for the
password. The password option also may be given in short
-p or as
-p. However, for the short form, if the
password value is given, it must follow the option letter
with no intervening space. The reason
for this is that if a space follows the option letter, the
program has no way to tell whether a following argument is
supposed to be the password value or some other kind of
argument. Consequently, the following two commands have two
completely different meanings:
mysql -p test
The first command instructs mysql to use
a password value of
test, but specifies
no default database. The second instructs
mysql to prompt for the password value
and to use
test as the default database.
Some options control behavior that can be turned on or off. For
example, the mysql client supports a
--column-names option that determines whether
or not to display a row of column names at the beginning of
query results. By default, this option is enabled. However, you
may want to disable it in some instances, such as when sending
the output of mysql into another program that
expects to see only data and not an initial header line.
To disable column names, you can specify the option using any of these forms:
--disable-column-names --skip-column-names --column-names=0
prefixes and the
=0 suffix all have the same
effect: They turn the option off.
The “enabled” form of the option may be specified in any of these ways:
--column-names --enable-column-names --column-names=1
If an option is prefixed by
--loose, a program
does not exit with an error if it does not recognize the option,
but instead issues only a warning:
mysql --loose-no-such-optionmysql: WARNING: unknown option '--no-such-option'
--loose prefix can be useful when you run
programs from multiple installations of MySQL on the same
machine and list options in an option file, An option that may
not be recognized by all versions of a program can be given
--loose prefix (or
loose in an option file). Versions of the
program that recognize the option process it normally, and
versions that do not recognize it issue a warning and ignore it.
Another option that may occasionally be useful with
mysql is the
-e option, which can be used to pass SQL
statements to the server. When this option is used,
mysql executes the statements and exits. The
statements must be enclosed by quotation marks. For example, you
can use the following command to obtain a list of user accounts:
mysql -u root -p --execute="SELECT User, Host FROM user" mysqlEnter password:
******+------+-----------+ | User | Host | +------+-----------+ | | gigan | | root | gigan | | | localhost | | jon | localhost | | root | localhost | +------+-----------+ shell>
Note that the long form (
--execute) is followed
by an equals sign (
If you wish to use quoted values within a statement, you will either need to escape the inner quotes, or use a different type of quotes within the statement from those used to quote the statement itself. The capabilities of your command processor dictate your choices for whether you can use single or double quotation marks and the syntax for escaping quote characters. For example, if your command processor supports quoting with single or double quotes, you can double quotes around the statement, and single quotes for any quoted values within the statement.
In the preceding example, the name of the
mysql database was passed as a separate
argument. However, the same statement could have been executed
using this command, which specifies no default database:
mysql -u root -p --execute="SELECT User, Host FROM mysql.user"
Multiple SQL statements may be passed on the command line, separated by semicolons:
mysql -u root -p -e "SELECT VERSION();SELECT NOW()"Enter password:
******+------------+ | VERSION() | +------------+ | 5.0.19-log | +------------+ +---------------------+ | NOW() | +---------------------+ | 2006-01-05 21:19:04 | +---------------------+
-e option may
also be used to pass commands in an analogous fashion to the
ndb_mgm management client for MySQL Cluster.
See Section 15.2.6, “Safe Shutdown and Restart”, for
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
mysqld). 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 15.3, “MySQL Cluster Configuration”.
On Windows, MySQL programs read startup options from the following files:
|The file specified with
WINDIR represents the location of
your Windows directory. This is commonly
C:\WINDOWS. You can determine its exact
location from the value of the
environment variable using the following command:
INSTALLDIR represents the
installation directory of MySQL. This is typically
PROGRAMDIR represents the programs
Program Files on
English-language versions of Windows), when MySQL
5.0 has been installed using the installation and
configuration wizards. See
Section 184.108.40.206.1.1, “The MySQL Server Configuration Wizard on Windows”.
On Unix, MySQL programs read startup options from the following files:
|The file specified with
MYSQL_HOME is an environment variable
containing the path to the directory in which the
my.cnf file resides. (This
DATADIR prior to MySQL version
MYSQL_HOME is not set and you start the
server using the mysqld_safe program,
mysqld_safe attempts to set
MYSQL_HOME as follows:
DATADIR represent the pathnames
of the MySQL base directory and data directory,
If there is a
my.cnf file in
DATADIR but not in
MYSQL_HOME is not set and
there is no
my.cnf file in
In MySQL 5.0, use of
DATADIR as the location for
my.cnf is deprecated.
BASEDIR is a better location.
/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.
Note: On Unix platforms, MySQL ignores configuration files that are world-writable. This is intentional, and acts 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, except that you omit the leading two
dashes. For example,
--host=localhost on the command line should be
host=localhost in an option file. To specify
an option of the form
an option file, write it as
Empty lines in option files are ignored. Non-empty lines can take any of the following forms:
Comment lines start with ‘
#’ 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. You can
enclose the value within single quotes or double quotes,
which is useful if the value contains a
#’ comment character or
For options that take a numeric value, the value can be given
with a suffix of
G (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:
mysqladmin --count=1K --sleep=10 ping
Leading and trailing blanks are automatically deleted from
option names and values. You may use the escape sequences
\s’ in option values to
represent the backspace, tab, newline, carriage return,
backslash, and space characters.
Because the ‘
\\’ escape sequence
represents a single backslash, you must write each
\\’. Alternatively, you can
specify the value using ‘
rather than ‘
\’ as the pathname
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 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
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
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], and so forth. The
following group indicates that the
should be used only by MySQL servers with 5.0.x
Beginning with MySQL 5.0.4, 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.
Note: Currently, any files to
be found and included using the
directive on Unix operating systems must
have filenames 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
If you have a source distribution, you can find sample option
support-files directory. If you have a
binary distribution, look in the
support-files directory under your MySQL
installation directory. On Windows, the sample option files may
be located in the MySQL installation directory (see earlier in
this section or Chapter 2, Installing and Upgrading MySQL, if you do not know
where this is). Currently, there are sample option files for
small, medium, large, and very large systems. To experiment with
one of these files, copy it to
Windows or to
.my.cnf in your home
directory on Unix.
Note: On Windows, the
file extension might not be displayed.
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-extra-file. Also, you should avoid
the use of the ‘
metacharacter when specifying filenames because it might not be
interpreted as you expect.
Don't read any option files.
Print the program name and all options that it gets from option files.
Use only the given option file.
file_name is the full pathname to
the file. If the file does not exist or is otherwise
inaccessible, the program will exit with an error.
Read this option file after the global option file but (on
Unix) before the user option file.
file_name is the full pathname to
the file. As of MySQL 5.0.6, if the file does not exist or
is otherwise inaccessible, the program will exit with an
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
groups. If the
--default-group-suffix=_other option is
given, mysql also reads the
[mysql_other] groups. This option was
added in MySQL 5.0.10.
In shell scripts, you can use the
my_print_defaults program to parse option
files and see what options would be used by a given program. The
following example shows the output that
my_print_defaults might produce when asked to
show the options found in the
my_print_defaults client mysql--port=3306 --socket=/tmp/mysql.sock --no-auto-rehash
Note for developers: Option file handling is implemented in the C client library simply by processing all options in the appropriate group or groups before any command-line arguments. This works well for programs that use the last instance of an option that is specified multiple times. If you have a C or C++ program that handles multiply specified options this way but that doesn't read option files, you need add only two lines to give it that capability. Check the source code of any of the standard MySQL clients to see how to do this.
Several other language interfaces to MySQL are based on the C client library, and some of them provide a way to access option file contents. These include Perl and Python. For details, see the documentation for your preferred interface.
MySQL provides a number of preconfigured option files that can
be used as a basis for tuning the MySQL server. Look in your
installation directory for files such as
my-huge.cnf, which you can rename and
copy to the appropriate location for use as a base
configuration file. Regarding names and appropriate location,
see the general information provided in
Section 4.3.2, “Using Option Files”. On Windows, those files have a
.ini rather than a
To specify an option using an environment variable, set the
variable using the syntax appropriate for your command
processor. For example, on Windows or NetWare, you can set the
USER variable to specify your MySQL account
name. To do so, use this syntax:
The syntax on Unix depends on your shell. Suppose that you want
to specify the TCP/IP port number using the
MYSQL_TCP_PORT variable. Typical syntax (such
as for sh,
zsh, and so on) is as follows:
MYSQL_TCP_PORT=3306 export MYSQL_TCP_PORT
The first command sets the variable, and the
export command exports the variable to the
shell environment so that its value becomes accessible to MySQL
and other processes.
For csh and tcsh, use setenv to make the shell variable available to the environment:
setenv MYSQL_TCP_PORT 3306
The commands to set environment variables can be executed at
your command prompt to take effect immediately, but the settings
persist only until you log out. To have the settings take effect
each time you log in, place the appropriate command or commands
in a startup file that your command interpreter reads each time
it starts. Typical startup files are
AUTOEXEC.BAT for Windows,
.bash_profile for bash,
.tcshrc for tcsh.
Consult the documentation for your command interpreter for
Section 2.4.19, “Environment Variables”, lists all environment variables that affect MySQL program operation.
Many MySQL programs have internal variables that can be set at
runtime. Program variables are set the same way as any other
long option that takes a value. For example,
mysql has a
max_allowed_packet variable that controls the
maximum size of its communication buffer. To set the
max_allowed_packet variable for
mysql to a value of 16MB, use either of the
The first command specifies the value in bytes. The second
specifies the value in megabytes. For variables that take a
numeric value, the value can be given with a suffix of
G (either uppercase or lowercase) to indicate
a multiplier of 1024, 10242 or
10243. (For example, when used to set
max_allowed_packet, the suffixes indicate
units of kilobytes, megabytes, or gigabytes.)
In an option file, variable settings are given without the leading dashes:
If you like, underscores in a variable name can be specified as dashes. The following option groups are equivalent. Both set the size of the server's key buffer to 512MB:
[mysqld] key_buffer_size=512M [mysqld] key-buffer-size=512M
Note: Before MySQL 4.0.2, the
only syntax for setting program variables was
in option files). This syntax still is recognized, but is
deprecated as of MySQL 4.0.2.
Many server system variables can also be set at runtime. For details, see Section 220.127.116.11, “Dynamic System Variables”.
Source code editor What Is Ajax