Table of Contents
This chapter provides a brief overview of the command-line 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 also provide three GUI client programs for use with the MySQL server:
MySQL Administrator: This tool is used for administering MySQL servers, databases, tables, and users.
MySQL Query Browser: This graphical tool is provided by MySQL AB for creating, executing, and optimizing queries on MySQL databases.
MySQL Migration Toolkit: This tool is intended to help you with migrating schemas and data from other relational database management systems to MySQL.
MySQL AB provides several types of programs:
The MYSQL server and server startup scripts:
mysqld is the MySQL server
mysqld_safe, mysql.server, and mysqld_multi are server startup scripts
mysql_install_db initializes the data directory and the initial databases
These programs are discussed further in Chapter 5, Database Administration.
Client programs that access the server:
mysql is a command-line client for executing SQL statements interactively or in batch mode.
mysqladmin is an administrative client.
mysqlcheck performs table maintenance operations
mysqldump and mysqlhotcopy make database backups.
mysqlimport imports data files.
mysqlshow displays information about databases and tables.
These programs are discussed further in Chapter 8, Client and Utility Programs.
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.
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 from 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
represents the prompt for your command interpreter; it is not part
of what you type. The particular prompt you see depends on your
command interpreter. Typical prompts are
sh or bash,
% for csh or
Windows command.com or
mysqladmin extended-status variablesshell>
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
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 arguments.
Some options are common to a number of programs. The most common
of these are the
--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
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 setting your
PATH. The syntax
for setting environment variables is interpreter-specific.
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.12.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 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.
Program options specified on the command line follow these rules:
Options are given after the command name.
An option argument begins with one dash or two dashes,
depending on whether it has a short name or a long name.
Many options have both forms. For example,
--help are the
short and long forms of the option that instructs a MySQL
program to display a help message.
Option names are case sensitive.
-V are both legal and have different
meanings. (They are the corresponding short forms of the
Some options take a value following the option name. For
-h localhost or
--host=localhost indicate the MySQL server
host to a client program. The option value tells the program
the name of the host where the MySQL server is running.
For a long option that takes a value, separate the option
name and the value by an ‘
sign. For a short option that takes a value, the option
value can immediately follow the option letter, or there can
be a space between. (
-h localhost are equivalent.) An exception
to this rule is the option for specifying your MySQL
password. This option can be given in long form as
--password. In the latter case (with
no password value given), the program prompts you for the
password. The password option also may be given in short
-p or as
-p. However, for the short form, if the
password value is given, it must follow the option letter
with no intervening space. The reason
for this is that if a space follows the option letter, the
program has no way to tell whether a following argument is
supposed to be the password value or some other kind of
argument. Consequently, the following two commands have two
completely different meanings:
mysql -p test
The first command instructs mysql to use
a password value of
test, but specifies
no default database. The second instructs
mysql to prompt for the password value
and to use
test as the default database.
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:
--disable-column-names --skip-column-names --column-names=0
prefixes and the
=0 suffix all have the same
effect: They turn the option off.
The “enabled” form of the option may be specified in any of these ways:
--column-names --enable-column-names --column-names=1
If an option is prefixed by
program does not exit with an error if it does not recognize the
option, but instead issues only a warning:
mysql --loose-no-such-optionmysql: WARNING: unknown option '--no-such-option'
--loose prefix can be useful when you run
programs from multiple installations of MySQL on the same
machine. 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
an option file). Versions of the program that do not recognize
the option issue a warning and ignore the option. convention.
Another option which may be occasionally useful with
mysql is the
--execute option, which can be used to pass SQL
statements to the server. The statements must be surrounded by
(single or double) quotation marks. (However, if you wish to use
quoted values within the query, then you should use double
quotes for the query, and single quotes for any quoted values
within the query.) When this option is used, the statements are
executed, and then mysql exits to the command
shell immediately thereafter.
For example, you can use the following to obtain a list of user accounts:
mysql -u root -p -e "SELECT User, Host FROM user" mysqlEnter password:
******+------+-----------+ | User | Host | +------+-----------+ | | gigan | | root | gigan | | | localhost | | jon | localhost | | root | localhost | +------+-----------+ shell>
Note that the name of the
mysql database was
passed as a separate argument. However, the same query could
have been executed using mysql -u root -p -e "SELECT
User, Host FROM mysql.user" from the shell prompt.
Multiple SQL statements may be passed in this way, separated by semicolons:
mysql -u root -p --execute="SELECT Name FROM Country WHERE Name LIKE 'AU%';SELECT COUNT(*) FROM City" worldEnter password:
******+-----------+ | Name | +-----------+ | Australia | | Austria | +-----------+ +----------+ | COUNT(*) | +----------+ | 4079 | +----------+
Note that the long form (
--execute) must be
followed by an equals sign (
-e option may also be used to pass commands
in an analogous fashion to the ndb_mgm
management client for MySQL Cluster. See
Section 15.3.6, “Safe Shutdown and Restart” for an example.
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.
The following programs support option files: myisamchk, myisampack, mysql, mysql.server, mysqladmin, mysqlbinlog, mysqlcc, mysqlcheck, mysqld_safe, mysqldump, mysqld, mysqlhotcopy, mysqlimport, and mysqlshow.
Note: Option files used with MySQL Cluster programs are covered in Section 15.4, “MySQL Cluster Configuration”.
On Windows, MySQL programs read startup options from the following files:
|The file specified with
WINDIR represents the location of
your Windows directory. This is commonly
C:\WINNT. You can determine its exact
location from the value of the
environment variable using the following command:
INSTALLDIR represents the
installation directory of MySQL. This is typically
PROGRAMDIR represents the programs
Program Files on
English-language versions of Windows), when MySQL
5.0 has been installed using the installation and
configuration wizards. See
Section 220.127.116.11, “The Location of the my.ini File”.
On Unix, MySQL programs read startup options from the following files:
|The file specified with
MYSQL_HOME is an environment variable
containing the path to the directory in which the
my.cnf file resides. (This
DATADIR prior to MySQL version 5.0.3.)
MYSQL_HOME is not set and there is a
my.cnf file in
DATADIR and there is no
my.cnf file in
DATADIR. Otherwise, if
MYSQL_HOME is not set and there is no
Typically this is
a binary installation or
a source installation. Note that this is the data directory
location that was specified at configuration time, not the one
mysqld starts. 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 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.
Note: On Unix platforms, MySQL ignores configuration files that are world-writable. This is intentional, and acts as a security measure.
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 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
host=localhost in an option file. To specify
an option of the form
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 ‘
#’ 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
set-variable lines apply to the named
group until the end of the option file or another group line
This is equivalent to
the command line.
This is equivalent to
on the command line. In an option file, you can have spaces
around the ‘
something that is not true on the command line. You can
quote the value with single quotes or double quotes. This is
useful if the value contains a
#’ comment character or
Leading and trailing blanks are automatically deleted from
option names and values. You may use the escape sequences
\s’ in option values to
represent the backspace, tab, newline, carriage return, and
On Windows, if an option value represents a pathname, you should
specify the value using ‘
rather than ‘
\’ as the pathname
separator. If you use ‘
must double it as ‘
\’ is the escape character in
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 by
mysqld). This allows you to specify options
that apply to all clients. 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
group unless it is recognized by all client
programs that you use. Programs that do not understand the
option quit after displaying an error message if you try to run
Beginning MySQL 5.0.4 in the 5.0 series, it is possible to use
!include directives in option files to
include specific files and
search specific directories for option files. For example, to
include the file
can use the following:
To search the directory
/home/mydir for all
files ending in
.cnf and to read these as
option files, you would use:
Note that these options are section-specific. For example,
suppose that you were to use something in
my.cnf such as the following:
[mysqld] !include /home/mydir/myopt.cnf
In such a case, the file
myopt.cnf would be
processed only for the server, and the
!include directive would be ignored by any
client applications. However, if you were to use the following:
[mysqldump] !includedir /home/mydir/my-dump-options
then the directory
/home/mydir/my-dump-options would be
checked for option files ending in
mysqldump only, and not by the server or by
any other client applications.
Note: Currently, any files to
be found and included using the
directive on Unix operating systems must
have filenames ending in
.cnf. On Windows,
this directive also checks for files with a
.ini extension (in addition to
If you want to create option groups that should be read by one
specific mysqld server release series only,
you can do this by using groups with names of
[mysqld-5.0], and so forth. The
following group indicates that the
should be used only by MySQL servers with 5.0.x
Here is a typical global option file:
[client] port=3306 socket=/tmp/mysql.sock [mysqld] port=3306 socket=/tmp/mysql.sock key_buffer_size=16M max_allowed_packet=8M [mysqldump] quick
The preceding option file uses
syntax for the lines that set the
Here is a typical user option file:
[client] # The following password will be sent to all standard MySQL clients password="my_password" [mysql] no-auto-rehash connect_timeout=2 [mysqlhotcopy] interactive-timeout
If you have a source distribution, you can find sample option
support-files directory. If you have a
binary distribution, look in the
support-files directory under your MySQL
installation directory. On Windows the sample option files may
also be located in the MySQL installation directory (see earlier
in this section or Chapter 2, Installing MySQL if you do not
know where this is). Currently there are sample option files for
small, medium, large, and very large systems. To experiment with
one of these files, copy it to
Windows or to
.my.cnf in your home
directory on Unix.
Note: On Windows, the
.cnf option file extension might not be
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 gets from option files.
Use only the given option file.
path_name is the full pathname to
Read this option file after the global option file but
before the user option file.
path_name is the full pathname to
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
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
my_print_defaults client mysql--port=3306 --socket=/tmp/mysql.sock --no-auto-rehash
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 does not 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. Typical syntax (such
as for sh,
zsh, and so on) is as follows:
MYSQL_TCP_PORT=3306 export MYSQL_TCP_PORT
The first command sets the variable, and the
export command exports the variable to the
shell environment so that its value becomes accessible to MySQL
and other processes.
For csh and tcsh there are similar issues. When running these shells, use setenv to make the shell variable available to the environment:
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,
.tcshrc for tcsh.
Consult the documentation for your command interpreter for
Appendix F, Environment Variables lists all environment variables that affect MySQL program operation.
Many MySQL programs have internal variables that can be set at
runtime. 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
The first command specifies the value in bytes. The second
specifies the value in megabytes. Variable values can have a
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.
Note: The older syntax
is still recognized in MySQL 5.0, but is now
Some server variables can be set at runtime. For details, see Section 18.104.22.168, “Dynamic System Variables”.