You don’t want to type connection parameters on the command line every time you invoke mysql or other MySQL programs.
To avoid entering connection parameters manually, put them in an option file for mysql to read automatically. Under Unix, your personal option file is named .my.cnf 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 /etc/my.cnf or the my.cnf file in the MySQL installation directory. Under Windows, the option files you can use are the my.ini file in your MySQL installation directory (for example, C:Program FilesMySQLMySQL Server 5.0), my.ini in your Windows directory (this is something like C:WINDOWS or C:WINNT), or the C:my.cnf file.
Windows Explorer might hide filename extensions when it displays
files, so a file named my.ini or
my.cnf may appear to be named
just my. Your version of Windows
may allow you to disable extension-hiding. Alternatively, issue a DIR
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 [client] host = localhost user = cbuser password = cbpass # options specific to the mysql program [mysql] skip-auto-rehash 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 group name within square brackets, and the
remaining lines specify options associated with the group. The
example file just shown has a [client]
group and a [mysql]
group. Within a group, write
option lines in name=value
format,
where name
corresponds to an option
name (without leading dashes) and value
is the option’s value. If an option doesn’t take any value (such
as for the skip-auto-rehash
option), list the name by itself with no trailing
=value
part.
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
-h
hostname
or
--host
=
hostname
. In an option file, only host=
hostname
is allowed.
In option files, spaces are allowed around the =
that separates an option name and
value. This contrasts with command lines, where no spaces around
=
are allowed.
If an option value contains spaces or other special
characters, you can quote it using single or double quotes. The
pager
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 (localhost
),
you don’t need any host
line.
On Unix, if your MySQL username is the same as your operating
system login name, you can omit the user
line.
It’s common to use an option file to specify options for
connection parameters (such as host
, user
, and password
). However, the file can list
options that have other purposes. The pager
option shown for the [mysql]
group specifies the paging
program that mysql 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 [client]
. This
group actually is used by all the standard MySQL clients. By
listing an option in this group, you make it easier to invoke not
just mysql, but also other
programs such as
mysqldump and
mysqladmin. Just make sure that any option you put in this
group is understood by all client programs.
For example, if you put mysql-specific options such as
skip-auto-rehash
or
pager
in the [client]
group, that will result in
“unknown option” errors for all other programs that
use the [client]
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 [client]
group and in the group named
for the program itself. This provides a convenient 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 preceding sample option file illustrates
this convention for the mysql
program, which gets general connection parameters from the
[client]
group and also picks
up the skip-auto-rehash
and
pager
options from the [mysql]
group.
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 [client]
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
#
or ;
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
pager
option.
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 especially 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 mysql program will read from option files, use this command:
%mysql --print-defaults
You can also use the
my_print_defaults
utility, which takes as arguments the names of the option file groups
that it should read. For example, mysql looks in both the [client]
and [mysql]
groups for options, so you can check
which values it will read from option files by using this
command:
%my_print_defaults client mysql
3.149.27.72