Specifying Connection Parameters by Using Option Files

1.5.1 Problem

You don't want to type connection parameters on the command line every time you invoke mysql.

1.5.2 Solution

Put the parameters in an option file.

1.5.3 Discussion

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.

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 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 no-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 any host line. If your MySQL username is the same as your operating system login name, you can omit the user 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, only host=hostname is allowed.
• Options often are used for connection parameters (such as host, user, and password). However, the file can specify 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 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 the no-auto-rehash and pager 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