Go to the first, previous, next, last section, table of contents.

4 Using MySQL Programs

This chapter provides a brief overview of the programs provided by MySQL AB and discusses how to specify options when you run these programs. Most programs have options that are specific to their own operation, but the syntax for specifying options is similar for all of them. Later chapters provide more detailed descriptions of individual programs, including which options they recognize.

4.1 Overview of MySQL Programs

MySQL AB provides several types of programs:

The MYSQL server and server startup scripts:
Client programs that access the server:
Utility programs that operate independently of the server:

More information on running the server may be found in section 13.5 Database Administration Statements. Client and utility programs are described in more detail in section 8 MySQL Client and Utility Programs.

Most MySQL distribution formats include all of these programs, except for those programs that are platform-specific. (For example, the server startup scripts are not used on Windows.) The exception is that RPM distributions are more specialized. There is one RPM for the server, another for the client programs, and so forth. If you appear to be missing one or more programs, see section 2 Installing MySQL for information on distributions and what they contain. It may be that you need to install something else.

4.2 Invoking MySQL Programs

To invoke a MySQL program at the command line (that is, from your shell or command prompt), enter the program name followed by any options or other arguments needed to instruct the program what you want it to do. The following commands show some sample program invocations. ``shell>'' represents your command prompt; it is not part of what you type.

shell> mysql test
shell> mysqldump --quote-names personnel
shell> mysqladmin extended-status variables
shell> mysqlshow --help

Arguments that begin with a dash are option arguments. They typically specify the type of connection a program should make to the server or affect its operational mode. Options have a syntax that is described in section 4.3 Specifying Program Options.

Non-option arguments (arguments with no leading dash) provide additional information to the program. For example, the mysql program interprets the first non-option argument as a database name, so the command mysql test indicates that you want to use the test database.

Later sections that describe individual programs indicate which options a program understands and describe the meaning of any additional non-option arguments.

Some options are common to a number of programs. The most common of these are the --host, --user, and --password options that specify connection parameters. They indicate the host where the MySQL server is running, and the username and password of your MySQL account. All MySQL client programs understand these options; they allow you to specify which server to connect to and the account to use on that server.

Note that you may find it necessary to invoke MySQL programs using the pathname to the `bin' directory in which they are installed. This is likely to be the case if you get a ``program not found'' error whenever you attempt to run a MySQL program from any directory other than the `bin' directory. To make it more convenient to use MySQL, you can add the pathname of the `bin' directory to your PATH environment variable setting. Then to run a program you need only type its name, not its entire pathname.

Consult the documentation for your command interpreter for instructions on setting your PATH; the syntax for setting environment variables is interpreter-specific.

4.3 Specifying Program Options

You can provide options for MySQL programs in several ways:

MySQL programs determine which options are given by examining environment variables first, then option files, then the command line. If an option is specified multiple times, the last occurrence takes precedence. This means that environment variables have the lowest precedence and command-line options the highest.

You can take advantage of the way that MySQL programs process options by specifying the default value for a program's options in an option file. Then you need not type them each time you run the program, but can override the defaults if necessary by using command-line options.

4.3.1 Using Options on the Command Line

Program options specified on the command line follow these rules:

MySQL 4.0 introduced some additional flexibility in the way you specify options. These changes were made in MySQL 4.0.2. Some of them relate to the way you specify options that have ``enabled'' and ``disabled'' states, and to the use of options that may be present in one version of MySQL but not another. Those capabilities are discussed here. Another change pertains to the way you use options to set program variables. section 4.3.4 Using Options to Set Program Variables discusses that topic further.

Some options control behavior that can be turned on or off. For example, the mysql client supports a --column-names option that determines whether or not to display a row of column names at the beginning of query results. By default, this option is enabled. However, you may want to disable it in some instances, such as when sending the output of mysql into another program that expects to see only data and not an initial header line.

To disable column names, you can specify the option using any of these forms:


The --disable and --skip prefixes and the =0 suffix all have the same effect of turning the option off.

The ``enabled'' form of the option may be specified as:


Another change to option processing introduced in MySQL 4.0 is that you can use the --loose prefix for command-line options. If an option is prefixed by --loose, the program will not exit with an error if it does not recognize the option, but instead will only issue a warning:

shell> mysql --loose-no-such-option
mysql: WARNING: unknown option '--no-such-option'

The --loose prefix can be useful when you run programs from multiple installations of MySQL on the same machine, at least if all the versions are as recent as 4.0.2. This prefix is particularly useful when you list options in an option file. An option that may not be recognized by all versions of a program can be given using the --loose prefix (or loose in an option file). Versions of the program that do not recognize the option will issue a warning and ignore it. Note that this strategy works only if all versions involved are 4.0.2 or later, because earlier versions know nothing of the --loose convention.

4.3.2 Using Option Files

MySQL programs can read startup options from option files (also sometimes called configuration files). Option files provide a convenient way to specify commonly used options so they need not be entered on the command line each time you run a program. Option file capability is available from MySQL 3.22 on.

The following programs support option files: mysql, mysqladmin, mysqld, mysqld_safe, mysql.server, mysqldump, mysqlimport, mysqlshow, mysqlcheck, mysqlhotcopy, myisamchk, and myisampack.

On Windows, MySQL programs read startup options from the following files:

Filename Purpose
windows-dir\my.ini Global options
C:\my.cnf Global options

windows-dir represents the location of your Windows directory. This is commonly `C:\Windows' or `C:\WinNT'. Check the value of the WINDIR environment vairable to see where this directory is located on your system.

On Unix, MySQL programs read startup options from the following files:

Filename Purpose
/etc/my.cnf Global options
DATADIR/my.cnf Server-specific options
defaults-extra-file The file specified with --defaults-extra-file=path, if any
~/.my.cnf User-specific options

DATADIR represents the location of the MySQL data directory. Typically this is `/usr/local/mysql/data' for a binary installation or `/usr/local/var' for a source installation. Note that this is the data directory location that was specified at configuration time, not the one specified with --datadir when mysqld starts up! Use of --datadir at runtime has no effect on where the server looks for option files, because it looks for them before processing any command-line arguments.

MySQL looks for option files in the order listed above and reads any that exist. If multiple option files exist, an option specified in a file read later takes precedence over the same option specified in a file read earlier.

Any long option that may be given on the command-line when running a MySQL program can be given in an option file as well. To get the list of available options for a program, run it with the --help option.

The syntax for specifying options in an option file is similar to command-line syntax, except that you omit the leading two dashes. For example, --quick or --host=localhost on the command line are specified as quick or host=localhost in an option file. To specify an option of the form --loose-opt_name in an option file, write it as loose-opt_name.

Empty lines in option files are ignored. Non-empty lines can take any of the following forms:

Comment lines start with `#' or `;'. As of MySQL 4.0.14, a `#'-comment can start in the middle of a line as well.
group is the name of the program or group for which you want to set options. After a group line, any option or set-variable lines apply to the named group until the end of the option file or another group line is given.
This is equivalent to --opt_name on the command-line.
This is equivalent to --opt_name=value on the command-line. In an option file, you can have spaces around the `=' character, something that is not true on the command line. As of MySQL 4.0.16, you can quote the value with double quotes or single quotes. This is useful if the value contains a comment character or whitespace.
set-variable = var_name=value
Set the program variable var_name to the given value. This is equivalent to --set-variable=var_name=value on the command-line. Spaces are allowed around the first `=' character but not around the second. This syntax is deprecated as of MySQL 4.0. See section 4.3.4 Using Options to Set Program Variables for more information on setting program variables.

Note that for options and values, all leading and trailing blanks are automatically deleted. You may use the escape sequences `\b', `\t', `\n', `\r', `\\', and `\s' in option values to represent the backspace, tab, newline, carriage return, and space characters.

On Windows, if an option value represents a pathname, you should specify the value using `/' rather than `\' as the pathname separator. If you use `\', you must double it as `\\', because `\' is the escape character in MySQL.

If an option group name is the same as a program name, options in the group apply specifically to that program.

The [client] option group is read by all client programs (but not by mysqld). This allows you to specify options that apply to every client. For example, [client] is the perfect group to use to specify the password that you use to connect to the server. (But make sure the option file is readable and writable only by yourself, so that other people cannot find out your password.) Be sure not to put an option in the [client] group unless it is recognized by all client programs.

As of MySQL 4.0.14, if you want to create options that should only be read by one specific mysqld server release series, you can do this with [mysqld-4.0], [mysqld-4.1], and so forth:


The above new option will only be used with MySQL server versions 4.0.x.

Here is a typical global option file:




Here is a typical user option file:

# The following password will be sent to all standard MySQL clients

set-variable = connect_timeout=2


If you have a source distribution, you will find sample option files named `my-xxxx.cnf' in the `support-files' directory. If you have a binary distribution, look in the `support-files' directory under your MySQL installation directory (typically `C:\mysql' on Windows or `/usr/local/mysql' on Unix). Currently there are sample option files for small, medium, large, and very large systems. To experiment with one of these files, copy it to `C:\my.cnf' on Windows or to `.my.cnf' in your home directory on Unix.

All MySQL programs that support option files support the following command-line options:

Don't read any option files.
Print the program name and all options that it will get from option files.
Use only the given option file. path_name is the full pathname to the file.
Read this option file after the global option file but before the user option file. path_name is the full pathname to the file.

Note that to work properly, each of these options must immediately follow the command name on the command line, with the exception that --print-defaults may be used immediately after --defaults-file or --defaults-extra-file.

In shell scripts, you can use the my_print_defaults program to parse the option files. The following example shows the output that my_print_defaults might produce when asked to show the options found in the [client] and [mysql] groups:

shell> my_print_defaults client mysql

Note for developers: Option file handling is implemented in the C client library simply by processing all matching options (that is, options in the appropriate group) before any command-line arguments. This works nicely for programs that use the last instance of an option that is specified multiple times. If you have a C or C++ program that handles multiply specified options this way but doesn't read option files, you need add only two lines to give it that capability. Check the source code of any of the standard MySQL clients to see how to do this.

Several other language interfaces to MySQL are based on the C client library, and some of them provide a way to access option file contents. These include Perl and Python. See the documentation for your preferred interface for details.

4.3.3 Using Environment Variables to Specify Options

To specify an option using an environment variable, set the variable using the syntax appropriate for your comment processor. For example, on Windows or NetWare, you can set the USER variable to specify your MySQL account name. To do so, use this syntax:

SET USER=your_name

The syntax on Unix depends on your shell. Suppose you want to specify the TCP/IP port number using the MYSQL_TCP_PORT variable. The syntax for Bourne shell and variants (sh, bash, zsh, etc.) is:


For csh and tcsh, use this syntax:

setenv MYSQL_TCP_PORT 3306

The commands to set environment variables can be executed at your command prompt to take effect immediately. These settings persist until you log out. To have the settings take effect each time you log in, place the appropriate command or commands in a startup file that your command interpreter reads each time it starts. Typical startup files are `AUTOEXEC.BAT' for Windows, `.bash_profile' for bash, or `.tcshrc' for tcsh. Consult the documentation for your command interpreter for specific details.

section E Environment Variables lists all environment variables that affect MySQL program operation.

4.3.4 Using Options to Set Program Variables

Many MySQL programs have internal variables that can be set at runtime. As of MySQL 4.0.2, program variables are set the same way as any other long option that takes a value. For example, mysql has a max_allowed_packet variable that controls the maximum size of its communication buffer. To set the max_allowed_packet variable for mysql to a value of 64 MB, use either of the following commands:

shell> mysql --max_allowed_packet=6710740
shell> mysql --max_allowed_packet=64M

The first command specifies the value in bytes. The second specifies the value in megabytes. Variable values can have a suffix of K, M, or G (either uppercase or lowercase) to indicate units of kilobytes, megabytes, or gigabytes.

In an option file, the variable setting is given without the leading dashes:




If you like, underscores in a variable name can be specified as dashes.

Prior to MySQL 4.0.2, program variable names are not recognized as option names. Instead, use the --set-variable option to assign a value to a variable:

shell> mysql --set-variable=max_allowed_packet=6710740
shell> mysql --set-variable=max_allowed_packet=64M

In an option file, omit the leading dashes:

set-variable = max_allowed_packet=6710740


set-variable = max_allowed_packet=64M

With --set-variable, underscores in variable names may not be given as dashes for versions of MySQL older than 4.0.2.

The --set-variable option is still recognized in MySQL 4.0.2 and up, but is deprecated.

Go to the first, previous, next, last section, table of contents.