Recipe 1.4. Specifying Connection Parameters Using Option Files
You don't want to type connection parameters on the command line every time you invoke
or other MySQL programs.
Put the parameters in an option file.
To avoid entering connection parameters manually, put them in an option file for
to read automatically. Under Unix, your personal option file is named
in your home directory. There are also site-wide option files that administrators can use to specify parameters that apply globally to all users. You can use
file in the MySQL installation directory. Under Windows, the option files you can use are the
file in your MySQL installation directory (for example,
C:\Program Files\MySQL\MySQL Server 5.0
in your Windows directory (this is something like
), or the
Windows Explorer might hide filename extensions when it displays files, so a file named
may appear to be named just
. Your version of Windows may allow you to disable extension-hiding. Alternatively, issue a
command in a console window to see complete filenames.
The following example illustrates the format used to write MySQL option files:
# general client program connection options
host = localhost
user = cbuser
password = cbpass
# options specific to the mysql program
pager="/usr/bin/less -E" # specify pager for interactive mode
This format has the following general characteristics:
Lines are written in groups (or sections). The first line of a group specifies the
within square brackets, and the remaining lines specify options associated with the group. The example file just shown has a
group and a
group. Within a group, write option lines in
corresponds to an option name (without leading dashes) and
is the option's value. If an option doesn't take any value (such as for the
option), list the name by itself with no trailing
In option files, only the long form of an option is allowed. This is in contrast to command lines, where options often can be specified using a short form or a long form. For example, on the command line, the hostname can be given using either
. In an option file, only
In option files, spaces are allowed around the
that separates an option name and value. This contrasts with command lines, where no spaces around
If an option value contains spaces or other special
, you can quote it using single or double quotes. The
option illustrates this.
If you don't need some particular parameter, just leave out the corresponding line. For example, if you normally connect to the default host (
), you don't need any
line. On Unix, if your MySQL username is the same as your operating system login name, you can omit the
It's common to use an option file to specify options for connection parameters (such as
). However, the file can list options that have other purposes. The
option shown for the
group specifies the paging program that
should use for displaying output in interactive mode. It has nothing to do with how the program connects to the server.
The usual option group for specifying client connection parameters is
. This group actually is used by all the standard MySQL
. By listing an option in this group, you make it easier to invoke not just
, but also other programs such as
. Just make sure that any option you put in this group is
client programs. For example, if you put
-specific options such as
group, that will result in "unknown option" errors for all other programs that use the
group, and they won't run properly.
You can define multiple groups in an option file. A common convention is for a program to look for parameters in the
group and in the group named for the program itself. This provides a
way to list general client parameters that you want all client programs to use, but you can still specify options that apply only to a particular program. The
sample option file illustrates this convention for the
program, which gets general connection parameters from the
group and also picks up the
options from the
If a parameter appears multiple times in an option file, the last value found takes precedence. Normally, you should list any program-specific groups following the
group so that if there is any overlap in the options set by the two groups, the more general options will be overridden by the program-specific values.
Lines beginning with
characters are ignored as comments. Blank lines are ignored, too.
can be used to write comments at the end of option lines, as shown for the
Option files must be plain-text files. If you create an option file with a word processor that uses some nontext format by default, be sure to save the file explicitly as text. Windows users
should take note of this.
Options that specify file or directory pathnames should be written using
as the pathname separator character, even under Windows, which uses
as the pathname separator. Alternatively, write
by doubling it as
(this is necessary because
is the MySQL escape character in strings).
If you want to find out which options the
program will read from option files, use this command:
You can also use the
utility, which takes as arguments the
of the option file groups that it should read. For example,
looks in both the
groups for options, so you can check which values it will read from option files by using this command:
my_print_defaults client mysql