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 server’s data directory. Under
Windows, the option files you can use are
C:\my.cnf
, the my.ini
file
in your Windows system directory, or my.cnf
in
the server’s data directory.
Tip
Windows may hide
filename extensions when
displaying files, so a file named 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 DOS window to see full
names.
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] no-auto-rehash # specify pager for interactive mode pager=/usr/bin/less
This format has the following general characteristics:
Lines are written in groups. The first line of the group specifies the group name inside of 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, option lines are written inname=value
format, wherename
corresponds to an option name (without leading dashes) andvalue
is the option’s value. If an option doesn’t take any value (such as for theno-auto-rehash
option), the name is listed by itself with no trailing=value
part.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 anyhost
line. If your MySQL username is the same as your operating system login name, you can omit theuser
line.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, the hostname can be given using either
-h
hostname
or--host
=
hostname
on the command line; in an option file, onlyhost=
hostname
is allowed.Options often are used for connection parameters (such as
host
,user
, andpassword
). However, the file can specify options that have other purposes. Thepager
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 group for specifying client connection parameters is
[client]
. This group actually is used by all the standard MySQL clients, so by creating an option file to use with mysql, you make it easier to invoke other programs such as mysqldump and mysqladmin as well.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 after the program itself. This provides a convenient way to list general client parameters that you want all client programs to use, but still be able to 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 theno-auto-rehash
andpager
options from the[mysql]
group. (If you put the mysql-specific options 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.)If a parameter is specified multiple times in an option file, the last value found takes precedence. This means that normally you should list any program-specific groups after 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.Option files must be plain text files. If you create an option file with a word processor that uses some non-text 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.
If you want to find out which options will be taken from option files by mysql, 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 take from option files
like this:
% my_print_defaults client mysql
Get MySQL Cookbook now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.