3.1 Overview of MySQL Programs

MySQL AB provides several types of programs:

Most MySQL distributions 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 Chapter 2, “Installing MySQL,” for information on types of distributions and what they contain. It may be that you need to install something else.

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 the prompt for your command interpreter; it is not part of what you type. The particular prompt you will see depends on your command interpreter. Typical prompts are $ for sh or bash, % for csh or tcsh, and C:> for Windows command.com or cmd.exe.

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 3.3, “3.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.

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.

You can provide options for MySQL programs in several ways:

MySQL programs determine which options are given first by examining environment variables, then option files, and 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 values 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.

Leading and trailing blanks are automatically deleted from option names and values. You may use the escape sequences '', ' ', ' ', ' ', '\', 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 that 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 that you use. Programs that do not understand the option will quit after displaying an error message if you try to run them.

As of MySQL 4.0.14, if you want to create option groups that should be read by only one specific mysqld server release series, you can do this by using groups with names of [mysqld-4.0], [mysqld-4.1], and so forth. The following group indicates that the --new option should be used only by MySQL servers with 4.0.x version numbers:

Here is a typical global option file:

The preceding option file uses var_name=value syntax for the lines that set the key_buffer_size and max_allowed_packet variables. Prior to MySQL 4.0.2, you would need to use set-variable syntax instead (described earlier in this section).

Here is a typical user option file:

This option file uses set-variable syntax to set the connect_timeout variable. For MySQL 4.0.2 and up, you can also set the variable using just 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.

Note: On Windows, the .cnf option file extension might not be displayed.

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

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 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:

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.

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:

The syntax on Unix depends on your shell. Suppose that 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, and so on) is:

For csh and tcsh, use this syntax:

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.

Appendix B, “Environment Variables,” lists all environment variables that affect MySQL program operation.

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 16MB, use either of the following commands:

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, respectively.

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

Or:

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:

In an option file, omit the leading dashes:

Or:

With --set-variable, underscores in variable names cannot 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.

Some server variables can be set at runtime. For details, see Section 4.2.3.1.2, “4.2.3.1.2 Dynamic System Variables.”