Setting Connection Defaults with an Option File

Repeatedly entering connection details every time you start mysql can become tedious. An alternative is to specify options for mysql to use in a file.

You can still override these options on the command line when you run mysql. The options in the file (usually) just provide you with a way to specify safer and more convenient behavior.

To specify options that apply to all instances of mysql run on a given system, modify my.cnf (the global MySQL configuration file). To specify options for a specific user on a Unix-like operating system, create a file called .my.cnf in the user's home directory.

Regardless of which file is used, the method for setting the options in the file is the same. First, set a heading of the form [client] on a line by itself. Any options set after this heading, until another heading or the end of the file, are options set for mysql when it starts.

The options that may be set after the [client] header are the same as the available command line options for mysql, with a few small and important differences:

• Leading dashes are omitted. Instead of writing --host, write host.

• The long form of the options should be used. Use host instead of h.

• Place an equal sign between the option name and the option value. host=localhost.

• Comments can be indicated with a leading #.

You might want to set these global options:

[client] # protection from sending unintended queries disable-reconnect # save novices from themselves safe-updates # increase safety by showing user # and host in prompt prompt='\u@\h> ' # force password prompt password 

If you are setting options for a Unix user, you might want to set the most commonly used connection details, including user, host, socket (if needed), and so on.

[client] user=some_user host=example.com # don't use compress for talking to a local server compress        # enable client-server compression 

Tip

Options are first read from the global MySQL configuration file, then from the per-user configuration file, and then from the command line.

For more information on what options can be set, run mysql -?.