MYSQL manuál : manual_Using_MySQL_Programs.html
MySQL Reference Manual for version 5.0.3-alpha - 4 Using MySQL Programs
Go to the first, previous, next, last section, table of contents.
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.
MySQL AB provides several types of programs:
- The MYSQL server and server startup scripts:
These programs are discussed further in section 5 Database Administration.
mysqld is the MySQL server
mysqld_multi are server
mysql_install_db initializes the data directory and the initial
- Client programs that access the server:
These programs are discussed further in section 8 MySQL Client and Utility Programs.
mysql is a command-line client for executing SQL statements interactively
or in batch mode
mysqlcc (MySQL Control Center) is an interactive graphical tool for
executing SQL statements and administration
mysqladmin is an administrative client
mysqlcheck performs table maintenance operations
mysqlhotcopy make database backups
mysqlimport imports data files
mysqlshow displays information about databases and tables
- Utility programs that operate independently of the server:
myisamchk performs table maintenance operations
myisampack produces compressed, read-only tables
mysqlbinlog is a tool for processing binary log files
perror displays error code meanings
myisamchk is discussed further in section 5 Database Administration.
The other programs are further in section 8 MySQL Client and Utility 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 section 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. ``
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
C:\> for Windows
shell> mysql test
shell> mysqladmin extended-status variables
shell> mysqlshow --help
shell> mysqldump --user=root personnel
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
Later sections that describe individual programs indicate which options a
program understands and describe the meaning of any additional non-option
Some options are common to a number of programs. The most common of these are
--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
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
PATH. The syntax for setting environment variables is
You can provide options for MySQL programs in several ways:
On the command line following the program name. This is most common for
options that apply to a specific invocation of the program.
In an option file that the program reads when it starts. This is common for
options that you want the program to use each time it runs.
In environment variables. These are useful for options that you want to apply
each time the program runs, although in practice option files are used more
commonly for this purpose.
(section 5.10.2 Running Multiple Servers on Unix discusses one situation in which environment
variables can be very helpful. It describes a handy technique that uses such
variables to specify the TCP/IP port number and Unix socket file for both
the server and client programs.)
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
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.
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 might be present in one version of MySQL but not another.
Those capabilities are discussed in this section.
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,
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
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:
--skip prefixes and the
all have the same effect: They turn the option off.
The ``enabled'' form of the option may be specified in any of these ways:
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 issue
only a warning:
shell> mysql --loose-no-such-option
mysql: WARNING: unknown option '--no-such-option'
--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 in an option file). Versions of the program that do
not recognize the option will issue a warning and ignore it. This
strategy requires that versions involved be 4.0.2 or later, because
earlier versions know nothing of the
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 that
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:
On Windows, MySQL programs read startup options from the following files:
|Filename || Purpose
| Global options
| Global options
WINDIR represents the location of your Windows directory. This is
commonly `C:\Windows' or `C:\WinNT'. You can determine its
exact location from the value of the
WINDIR environment variable
using the following command:
C:\> echo %WINDIR%
On Unix, MySQL programs read startup options from the following files:
|Filename || Purpose
| Global options
| Server-specific options
| The file specified with |
--defaults-extra-file=path, if any
| 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 at runtime has no effect on where the server
looks for option files, because it looks for them before processing any
MySQL looks for option files in the order just described and reads any that
exist. If an option file that you want to use does not exist, create it
with a plain text editor.
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
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,
--host=localhost on the command line should be specified as
host=localhost in an option file.
To specify an option of the form
in an option file, write it as
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 opt_name or
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.
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
Spaces are allowed around the first `=' character but not around
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.
Leading and trailing blanks are automatically deleted from option names and
values. 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.
[client] option group is read by all client programs (but not
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 only by one specific
mysqld server release series, you can do this by
using groups with names of
[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
variables. Prior to MySQL 4.0.2, you would need to use
syntax instead (described earlier in this section).
Here is a typical user option file:
# The following password will be sent to all standard MySQL clients
set-variable = connect_timeout=2
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
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). On Windows the sample option files
may also be located in the MySQL installation directory. 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
Note: On Windows, the `.cnf' option file extension might not
All MySQL programs that support option files handle 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.
To work properly, each of these options must immediately
follow the command name on the command line, with the exception
--print-defaults may be used immediately after
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
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
To specify an option using an environment variable, set the variable using the
syntax appropriate for your comment processor. For example, on Windows or
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 (
zsh, etc.) is:
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
tcsh. Consult the documentation for your command
interpreter for specific details.
section F Environment Variables lists all environment variables that affect MySQL
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.
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:
shell> mysql --max_allowed_packet=16777216
shell> mysql --max_allowed_packet=16M
The first command specifies the value in bytes. The second specifies the value
in megabytes. Variable values can have a suffix of
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
Instead, use the
--set-variable option to assign a value to a variable:
shell> mysql --set-variable=max_allowed_packet=16777216
shell> mysql --set-variable=max_allowed_packet=16M
In an option file, omit the leading dashes:
set-variable = max_allowed_packet=16777216
set-variable = max_allowed_packet=16M
--set-variable, underscores in variable names cannot be given as
dashes for versions of MySQL older than 4.0.2.
--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 220.127.116.11 Dynamic System Variables.
Go to the first, previous, next, last section, table of contents.