MySQL Cookbook, 2nd Edition

Specifying Connection Parameters Using Option Files

Problem

You don’t want to type connection parameters on the command line every time you invoke mysql or other MySQL programs.

Solution

Put the parameters in an option file.

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 installation directory. Under Windows, the option files you can use are the my.ini file in your MySQL installation directory (for example, C:\Program Files\MySQL\MySQL 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