MySQL programs determine which options are given first by
examining environment variables, then option files, and then the
command line. If an option is specified multiple times, the last
occurrence takes precedence. This means that environment variables
have the lowest precedence and command-line options the highest.
You can take advantage of the way that MySQL programs process
options by specifying the default values for a program's options
in an option file. Then you need not type them each time you run
the program, but can override the defaults if necessary by using
command-line options.
4.3.1. Using Options on the Command Line
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,
-? and --help are the
short and long forms of the option that instructs a MySQL
program to display a help message.
Option names are case sensitive. -v and
-V are both legal and have different
meanings. (They are the corresponding short forms of the
--verbose and --version
options.)
Some options take a value following the option name. For
example, -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. (-hlocalhost and
-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=pass_val
or 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
form as
-ppass_val 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:
shell> mysql -ptest
shell> 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.
MySQL 4.0 introduced some additional flexibility in the way you
specify options. These changes were made in MySQL 4.0.2. Some of
them relate to the way you specify options that have
“enabled” and “disabled” states, and
to the use of options that might be present in one version of
MySQL but not another. Those capabilities are discussed in this
section. Another change pertains to the way you use options to
set program variables. Section 4.3.4, “Using Options to Set Program Variables”
discusses that topic further.
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
The --disable and --skip
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
Another change to option processing introduced in MySQL 4.0 is
that you can use the --loose prefix for
command-line options. If an option is prefixed by
--loose, the program does not exit with an
error if it does not recognize the option, but instead issues
only a warning:
shell> mysql --loose-no-such-option
mysql: WARNING: unknown option '--no-such-option'
The --loose prefix can be useful when you run
programs from multiple installations of MySQL on the same
machine, at least if all the versions are as recent as 4.0.2.
This prefix is particularly useful when you list options in an
option file. An option that may not be recognized by all
versions of a program can be given using the
--loose prefix (or loose in
an option file). Versions of the program that do not recognize
the option issue a warning and ignore the option. This strategy
requires that versions involved be 4.0.2 or later, because
earlier versions know nothing of the --loose
convention.
Another option which may be occasionally useful with
mysql is the -e or
--execute option, which can be used to pass SQL
statements to the server. The statements must be surrounded by
(single or double) quotation marks. (However, if you wish to use
quoted values within the query, then you should use double
quotes for the query, and single quotes for any quoted values
within the query.) When this option is used, the statements are
executed, and then mysql exits to the command
shell immediately thereafter.
For example, you can use the following to obtain a list of user
accounts:
shell> mysql -u root -p -e "SELECT User, Host FROM user" mysql
Enter password: ******
+------+-----------+
| User | Host |
+------+-----------+
| | gigan |
| root | gigan |
| | localhost |
| jon | localhost |
| root | localhost |
+------+-----------+
shell>
Note that the name of the mysql database was
passed as a separate argument. However, the same query could
have been executed using mysql -u root -p -e "SELECT
User, Host FROM mysql.user" from the shell prompt.
Multiple SQL statements may be passed in this way, separated by
semicolons:
shell> mysql -u root -p --execute="SELECT Name FROM Country WHERE Name LIKE 'AU%';SELECT COUNT(*) FROM City" world
Enter password: ******
+-----------+
| Name |
+-----------+
| Australia |
| Austria |
+-----------+
+----------+
| COUNT(*) |
+----------+
| 4079 |
+----------+
Note that the long form (--execute) must be
followed by an equals sign (=).
The -e option may also be used to pass commands
in an analogous fashion to the ndb_mgm
management client for MySQL Cluster. See
Section 16.3.6, “Safe Shutdown and Restart” for an example.
4.3.2. Using Option Files
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. Option file capability is available from MySQL 3.22 on.
The following programs support option files:
myisamchk, myisampack,
mysql, mysql.server,
mysqladmin, mysqlbinlog,
mysqlcc, mysqlcheck,
mysqld_safe, mysqldump,
mysqld, mysqlhotcopy,
mysqlimport, and
mysqlshow.
Note: Option files used with
MySQL Cluster programs are covered in
Section 16.4, “MySQL Cluster Configuration”.
On Windows, MySQL programs read startup options from the
following files:
WINDIR represents the location of
your Windows directory. This is commonly
C:\WINDOWS or
C:\WINNT. You can determine its exact
location from the value of the WINDIR
environment variable using the following command:
C:\> echo %WINDIR%
INSTALLDIR represents the
installation directory of MySQL. This is typically the case with
MySQL 4.1.5 and higher, when installed using the installation
and configuration wizards. See
Section 2.3.5.14, “The Location of the my.ini File”.
On Unix, MySQL programs read startup options from the following
files:
DATADIR 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 there is a
my.cnf file in
DATADIR and there is no
my.cnf file in
BASEDIR,
mysqld_safe sets
MYSQL_HOME to
DATADIR. Otherwise, if
MYSQL_HOME is not set and there is no
my.cnf in
DATADIR, then
mysqld_safe sets
MYSQL_HOME to
BASEDIR.
Typically this 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 --datadir 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 command-line arguments.
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
option files exist, an option specified in a file read later
takes precedence over the same option specified in a file read
earlier.
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 --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. For example, --quick or
--host=localhost on the command line should be
specified as quick or
host=localhost 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. Non-empty lines can
take any of the following forms:
#comment,
;comment
Comment lines start with ‘#’
or ‘;’. As of MySQL 4.0.14, 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 opt_name or
set-variable 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
--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. As of MySQL
4.0.16, you can quote the value with double quotes or single
quotes. This is useful if the value contains a
‘#’ comment character or
whitespace.
set-variable =
var_name=value
Set the program variable var_name
to the given value. This is equivalent to
--set-variable=var_name=value
on the command line. Spaces are allowed around the first
‘=’ character but not around
the second. This syntax is deprecated as of MySQL 4.0. See
Section 4.3.4, “Using Options to Set Program Variables” for more information on
setting program variables.
Leading and trailing blanks are automatically deleted from
option names and values. You may use the escape sequences
‘\b’,
‘\t’,
‘\n’,
‘\r’,
‘\\’, and
‘\s’ in option values to
represent the backspace, tab, newline, carriage return, and
space characters.
On Windows, if an option value represents a pathname, you should
specify the value using ‘/’
rather than ‘\’ as the pathname
separator. If you use ‘\’, you
must double it as ‘\\’, because
‘\’ is the escape character in
MySQL.
If an option group name is the same as a program name, options
in the group apply specifically to that program.
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.
Beginning with MySQL 4.1.11 in the 4.1 series and MySQL 5.0.4 in
the 5.0 series, it is possible to use
!include directives in option files to
include specific files and !includedir to
search specific directories for option files. For example, to
include the file /home/mydir/myopt.cnf, you
can use the following:
!include /home/me/myopt.cnf
To search the directory /home/mydir for all
files ending in .cnf and to read these as
option files, you would use:
!includedir /home/mydir
Note that these options are section-specific. For example,
suppose that you were to use something in
my.cnf such as the following:
[mysqld]
!include /home/mydir/myopt.cnf
In such a case, the file myopt.cnf would be
processed only for the server, and the
!include directive would be ignored by any
client applications. However, if you were to use the following:
[mysqldump]
!includedir /home/mydir/my-dump-options
then the directory
/home/mydir/my-dump-options would be
checked for option files ending in .cnf by
mysqldump only, and not by the server or by
any other client applications.
Note: Currently, any files to
be found and included using the !includedir
directive on Unix operating systems must
have filenames ending in .cnf. On Windows,
this directive also checks for files with a
.ini extension (in addition to
.cnf).
As of MySQL 4.0.14, if you want to create option groups that
should be read only by one specific mysqld
server release series only, you can do this by using groups with
names of [mysqld-4.0],
[mysqld-4.1], and so forth. The following
group indicates that the --new option should be
used only by MySQL servers with 4.0.x version numbers:
[mysqld-4.0]
new
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
var_name=valuekey_buffer_size and
max_allowed_packet variables. Prior to MySQL
4.0.2, you would need to use set-variable
syntax instead (described earlier in this section).
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
set-variable = connect_timeout=2
[mysqlhotcopy]
interactive-timeout
This option file uses set-variable syntax to
set the connect_timeout variable. For MySQL
4.0.2 and up, you can also set the variable using just
connect_timeout=2.
If you have a source distribution, you can find sample option
files named
my-xxxx.cnf in
the 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
also be located in the MySQL installation directory (see earlier
in this section or Chapter 2, Installing 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 C:\my.cnf on
Windows or to .my.cnf in your home
directory on Unix.
Note: On Windows, the
.cnf option file extension might not be
displayed.
All MySQL programs that support option files handle the
following command-line options:
--no-defaults
Don't read any option files.
--print-defaults
Print the program name and all options that it gets from
option files.
--defaults-file=path_name
Use only the given option file.
path_name is the full pathname to
the file.
--defaults-extra-file=path_name
Read this option file after the global option file but
before the user option file.
path_name is the full pathname to
the file.
To work properly, each of these options must immediately follow
the command name on the command line, with the exception that
--print-defaults may be used immediately after
--defaults-file or
--defaults-extra-file.
In shell scripts, you can use the
my_print_defaults program to parse option
files. The following example shows the output that
my_print_defaults might produce when asked to
show the options found in the [client] and
[mysql] groups:
shell> 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 matching options (that is, options in the
appropriate group) 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. See the
documentation for your preferred interface for details.
4.3.4. Using Options to Set Program Variables
Many MySQL programs have internal variables that can be set at
runtime. As of MySQL 4.0.2, 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
following commands:
shell> mysql --max_allowed_packet=16777216
shell> mysql --max_allowed_packet=16M
The first command specifies the value in bytes. The second
specifies the value in megabytes. Variable values can have a
suffix of K, M, or
G (either uppercase or lowercase) to indicate
units of kilobytes, megabytes, or gigabytes.
In an option file, the variable setting is given without the
leading dashes:
[mysql]
max_allowed_packet=16777216
Or:
[mysql]
max_allowed_packet=16M
If you like, underscores in a variable name can be specified as
dashes.
Prior to MySQL 4.0.2, program variable names are not recognized
as option names. Instead, use the
--set-variable option to assign a value to a
variable:
shell> mysql --set-variable=max_allowed_packet=16777216
shell> mysql --set-variable=max_allowed_packet=16M
In an option file, omit the leading dashes:
[mysql]
set-variable = max_allowed_packet=16777216
Or:
[mysql]
set-variable = max_allowed_packet=16M
With --set-variable, underscores in variable
names cannot be given as dashes for versions of MySQL older than
4.0.2.
The --set-variable option is still recognized
in MySQL 4.0.2 and up, but is deprecated.
Some server variables can be set at runtime. For details, see
Section 5.2.3.1, “Dynamic System Variables”.
