This chapter provides a brief overview of the MySQL command-line
programs provided by Oracle Corporation. It also discusses the
general syntax for specifying options when you run these programs.
Most programs have options that are specific to their own operation,
but the option syntax is similar for all of them. Finally, the
chapter provides more detailed descriptions of individual programs,
including which options they recognize.

4.1 Overview of MySQL Programs

There are many different programs in a MySQL installation. This
section provides a brief overview of them. Later sections provide
a more detailed description of each one, with the exception of NDB
Cluster programs. Each program's description indicates its
invocation syntax and the options that it supports.
Section 18.4, “NDB Cluster Programs”, describes programs
specific to NDB Cluster.

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 client programs, and so forth. If you
appear to be missing one or more programs, see
Chapter 2, Installing and Upgrading MySQL, for information on types of
distributions and what they contain. It may be that you have a
distribution that does not include all programs and you need to
install an additional package.

Each MySQL program takes many different options. Most programs
provide a --help option that you can use to get a
description of the program's different options. For example, try
mysql --help.

You can override default option values for MySQL programs by
specifying options on the command line or in an option file. See
Section 4.2, “Using MySQL Programs”, for general information on
invoking programs and specifying program options.

The MySQL server, mysqld, is the main program
that does most of the work in a MySQL installation. The server is
accompanied by several related scripts that assist you in starting
and stopping the server:

Oracle Corporation also provides the
MySQL Workbench GUI tool, which
is used to administer MySQL servers and databases, to create,
execute, and evaluate queries, and to migrate schemas and data
from other relational database management systems for use with
MySQL. Additional GUI tools include
MySQL Notifier and
MySQL for Excel.

MySQL client programs that communicate with the server using the
MySQL client/server library use the following environment
variables.

4.2.1 Invoking MySQL Programs

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
invocations. shell> 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 $ for
sh, ksh, or
bash, % for
csh or tcsh, and
C:\> for the Windows
command.com or cmd.exe
command interpreters.

Arguments that begin with a single or double dash
(-, --) specify program
options. Options typically indicate the type of connection a
program should make to the server or affect its operational mode.
Option syntax is described in Section 4.2.3, “Specifying Program Options”.

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

Later sections that describe individual programs indicate which
options a program supports and describe the meaning of any
additional nonoption arguments.

Some options are common to a number of programs. The most
frequently used of these are the
--host (or -h),
--user (or -u),
and --password (or
-p) options that specify connection parameters.
They indicate the host where the MySQL server is running, and the
user name and password of your MySQL account. All MySQL client
programs understand these options; they enable you to specify
which server to connect to and the account to use on that server.
Other connection options are
--port (or -P) to
specify a TCP/IP port number and
--socket (or -S)
to specify a Unix socket file on Unix (or named pipe name on
Windows). For more information on options that specify connection
options, see Section 4.2.2, “Connecting to the MySQL Server”.

You may find it necessary to invoke MySQL programs using the path
name 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 path name of the
bin directory to your PATH
environment variable setting. That enables you to run a program by
typing only its name, not its entire path name. For example, if
mysql is installed in
/usr/local/mysql/bin, you can run the program
by invoking it as mysql, and it is not
necessary to invoke it as
/usr/local/mysql/bin/mysql.

Consult the documentation for your command interpreter for
instructions on setting your PATH variable. The
syntax for setting environment variables is interpreter-specific.
(Some information is given in
Section 4.2.10, “Setting Environment Variables”.) After modifying
your PATH setting, open a new console window on
Windows or log in again on Unix so that the setting goes into
effect.

4.2.2 Connecting to the MySQL Server

For a client program to be able to connect to the MySQL server, it
must use the proper connection parameters, such as the name of the
host where the server is running and the user name and password of
your MySQL account. Each connection parameter has a default value,
but you can override them as necessary using program options
specified either on the command line or in an option file.

If you use a -p or
--password option and specify
the password value, there must be no
space between -p or
--password= and the password
following it.

If you use a -p or
--password option but do not
specify the password value, the client program prompts you to
enter the password. The password is not displayed as you enter
it. This is more secure than giving the password on the
command line. Other users on your system may be able to see a
password specified on the command line by executing a command
such as ps auxw. See
Section 6.1.2.1, “End-User Guidelines for Password Security”.

As just mentioned, including the password value on the command
line can be a security risk. To avoid this problem, specify the
--password or -p option without
any following password value:

When the password option has no password value, the client program
prints a prompt and waits for you to enter the password. (In these
examples, mydb is not
interpreted as a password because it is separated from the
preceding password option by a space.)

On some systems, the library routine that MySQL uses to prompt for
a password automatically limits the password to eight characters.
That is a problem with the system library, not with MySQL.
Internally, MySQL does not have any limit for the length of the
password. To work around the problem, change your MySQL password
to a value that is eight or fewer characters long, or put your
password in an option file.

On Unix, MySQL programs treat the host name
localhost specially, in a way that is likely
different from what you expect compared to other network-based
programs.

Clients determine what type of connection to make as follows:

If the host is not specified or is
localhost, a connection to the local host
is assumed:

On Windows, the client connects using a shared-memory
connection, if the server has shared-memory connections
enabled.

On Unix, the client connects using a Unix socket file. The
--socket option or the
MYSQL_UNIX_PORT environment variable
may be used to specify the socket name.

On Windows, if host is
., or TCP/IP is not enabled and
--socket is not specified or
the host is empty, the client connects using a named pipe, if
the server has named-pipe connections enabled. If named-pipe
connections are not enabled or if the user making the
connection is not a member of the Windows group specified by
the
named_pipe_full_access_group
server system variable, an error occurs.

Otherwise, TCP/IP is used.

The --protocol option enables you
to establish a particular type of connection even when the other
options would normally default to some other protocol. That is,
--protocol may be given to specify
the connection protocol explicitly and override the preceding
rules, even for localhost.

Only connection options that are relevant to the selected protocol
are used or checked. Other connection options are ignored. For
example, with --host=localhost on
Unix, the client attempts to connect to the local server using a
Unix socket file. This occurs even if a
--port or -P
option is given to specify a port number.

To ensure that the client makes a TCP/IP connection to the local
server, use --host or
-h to specify a host name value of
127.0.0.1, or the IP address or name of the
local server. You can also specify the connection protocol
explicitly, even for localhost, by using the
--protocol=TCP option. For
example:

On Windows, you can force a MySQL client to use a named-pipe
connection by specifying the
--pipe or
--protocol=PIPE option, or by
specifying . (period) as the host name. If
named-pipe connections are not enabled or if the user making the
connection is not a member of the Windows group specified by the
named_pipe_full_access_group
server system variable, an error occurs. Use the
--socket option to specify the
name of the pipe if you do not want to use the default pipe name.

Connections to remote servers always use TCP/IP. This command
connects to the server running on
remote.example.com using the default port
number (3306):

You can specify a port number for connections to a local server,
too. However, as indicated previously, connections to
localhost on Unix will use a socket file by
default. You will need to force a TCP/IP connection as already
described or any option that specifies a port number will be
ignored.

For this command, the program uses a socket file on Unix and the
--port option is ignored:

shell> mysql --port=13306 --host=localhost

To cause the port number to be used, invoke the program in either
of these ways:

The password of the MySQL account. As described earlier, the
password value is optional, but if given, there must be
no space between -p or
--password= and the password
following it. The default is to send no password.

On Windows, connect to the server using a named pipe. The
server must be started with the
--enable-named-pipe option to
enable named-pipe connections. In addition, the user making
the connection must be a member of the Windows group specified
by the
named_pipe_full_access_group
server system variable.

This option explicitly specifies a protocol to use for
connecting to the server. It is useful when the other
connection parameters normally would cause a protocol to be
used other than the one you want. For example, connections on
Unix to localhost are made using a Unix
socket file by default:

shell> mysql --host=localhost

To force a TCP/IP connection to be used instead, specify a
--protocol option:

shell> mysql --host=localhost --protocol=TCP

The following table shows the permissible
--protocol option values and
indicates the platforms on which each value may be used. The
values are not case-sensitive.

On Unix, the name of the Unix socket file to use, for
connections made using a named pipe to a local server. The
default Unix socket file name is
/tmp/mysql.sock.

On Windows, the name of the named pipe to use, for connections
to a local server. The default Windows pipe name is
MySQL. The pipe name is not case-sensitive.

The server must be started with the
--enable-named-pipe option to
enable named-pipe connections. In addition, the user making
the connection must be a member of the Windows group specified
by the
named_pipe_full_access_group
server system variable.

The user name of the MySQL account you want to use. The
default user name is ODBC on Windows or
your Unix login name on Unix.

It is possible to specify different default values to be used when
you make a connection so that you need not enter them on the
command line each time you invoke a client program. This can be
done in a couple of ways:

You can specify connection parameters in the
[client] section of an option file. The
relevant section of the file might look like this:

4.2.3 Specifying Program Options

List the options on the command line following the program
name. This is common for options that apply to a specific
invocation of the program.

List the options 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.

List the options in environment variables (see
Section 4.2.10, “Setting Environment Variables”). This method
is useful for options that you want to apply each time the
program runs. In practice, option files are used more commonly
for this purpose, but Section 5.7.3, “Running Multiple MySQL Instances 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 the server and for client programs.

Options are processed in order, so if an option is specified
multiple times, the last occurrence takes precedence. The
following command causes mysql to connect to
the server running on localhost:

shell> mysql -h example.com -h localhost

If conflicting or related options are given, later options take
precedence over earlier options. The following command runs
mysql in “no column names” mode:

shell> mysql --column-names --skip-column-names

MySQL programs determine which options are given first by
examining environment variables, then by processing option files,
and then by checking the command line. 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 default option values for a program in an
option file. That enables you to avoid typing them each time you
run the program while enabling you to override the defaults if
necessary by using command-line options.

An option can be specified by writing it in full or as any
unambiguous prefix. For example, the
--compress option can be given
to mysqldump as --compr, but
not as --comp because the latter is ambiguous:

Be aware that the use of option prefixes can cause problems in the
event that new options are implemented for a program. A prefix
that is unambiguous now might become ambiguous in the future.

Note

As of MySQL 5.6.13, unambiguous prefixes are deprecated. If an
unambiguous prefix is given, a warning occurs to provide
feedback. Option prefixes are no longer supported as of MySQL
5.7; only full options are accepted.

4.2.4 Using Options on the Command Line

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 is a short form or long form of the
option name. Many options have both short and long forms. For
example, -? and --help are
the short and long forms of the option that instructs a MySQL
program to display its help message.

Option names are case-sensitive. -v and
-V are both legal and have different
meanings. (They are the corresponding short forms of the
--verbose and --version
options.)

Some options take a value following the option name. For
example, -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:
-hlocalhost and -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=pass_val
or 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 form as
-ppass_val 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:

shell> mysql -ptest
shell> 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.

Within option names, dash (-) and
underscore (_) may be used interchangeably.
For example,
--skip-grant-tables and
--skip_grant_tables
are equivalent. (However, the leading dashes cannot be given
as underscores.)

For options that take a numeric value, the value can be given
with a suffix of K, M,
or G (either uppercase or lowercase) to
indicate a multiplier of 1024,
10242 or
10243. For example, the following
command tells mysqladmin to ping the server
1024 times, sleeping 10 seconds between each ping:

shell> mysqladmin --count=1K --sleep=10 ping

When specifying file names as option values, avoid the use of
the ~ shell metacharacter because it might
not be interpreted as you expect.

Option values that contain spaces must be quoted when given on the
command line. For example, the
--execute (or -e)
option can be used with mysql to pass SQL
statements to the server. When this option is used,
mysql executes the statements in the option
value and exits. The statements must be enclosed by quotation
marks. For example, you can use the following command to obtain a
list of user accounts:

If you wish to use quoted values within a statement, you will
either need to escape the inner quotation marks, or use a
different type of quotation marks within the statement from those
used to quote the statement itself. The capabilities of your
command processor dictate your choices for whether you can use
single or double quotation marks and the syntax for escaping quote
characters. For example, if your command processor supports
quoting with single or double quotation marks, you can use double
quotation marks around the statement, and single quotation marks
for any quoted values within the statement.

Multiple SQL statements may be passed in the option value on the
command line, separated by semicolons:

4.2.5 Program Option Modifiers

Some options are “boolean” and 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

The --disable and --skip
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

The values ON, TRUE,
OFF, and FALSE are also
recognized for boolean options (not case-sensitive).

If an option is prefixed by --loose, a program
does not exit with an error if it does not recognize the option,
but instead issues only a warning:

The --loose prefix can be useful when you run
programs from multiple installations of MySQL on the same machine
and 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 recognize the option
process it normally, and versions that do not recognize it issue a
warning and ignore it.

The --maximum prefix is available for
mysqld only and permits a limit to be placed on
how large client programs can set session system variables. To do
this, use a --maximum prefix with the variable
name. For example,
--maximum-max_heap_table_size=32M prevents any
client from making the heap table size limit larger than 32M.

The --maximum prefix is intended for use with
system variables that have a session value. If applied to a system
variable that has only a global value, an error occurs. For
example, with --maximum-back_log=200, the server
produces this error:

Maximum value of 'back_log' cannot be set

4.2.6 Using Option Files

Most MySQL programs can read startup options from option files
(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.

To determine whether a program reads option files, invoke it with
the --help option. (For
mysqld, use
--verbose and
--help.) If the program reads
option files, the help message indicates which files it looks for
and which option groups it recognizes.

Note

A MySQL program started with the --no-defaults
option reads no option files other than
.mylogin.cnf.

Many option files are plain text files, created using any text
editor. The exception is the .mylogin.cnf
file that contains login path options. This is an encrypted file
created by the mysql_config_editor utility. See
Section 4.6.6, “mysql_config_editor — MySQL Configuration Utility”. A “login path”
is an option group that permits only certain options:
host, user,
password, port and
socket. Client programs specify which login path
to read from .mylogin.cnf using the
--login-path option.

To specify an alternative login path file name, set the
MYSQL_TEST_LOGIN_FILE environment variable.
This variable is used by the mysql-test-run.pl
testing utility, but also is recognized by
mysql_config_editor and by MySQL clients such
as mysql, mysqladmin, and so
forth.

MySQL looks for option files in the order described in the
following discussion and reads any that exist. If an option file
you want to use does not exist, create it using the appropriate
method, as just discussed.

In the preceding table, %WINDIR% represents the
location of your Windows directory. This is commonly
C:\WINDOWS. Use the following command to
determine its exact location from the value of the
WINDIR environment variable:

C:\> echo %WINDIR%

%APPDATA% represents the value of the Windows
application data directory. Use the following command to determine
its exact location from the value of the
APPDATA environment variable:

C:\> echo %APPDATA%

BASEDIR represents the MySQL base
installation directory. When MySQL 5.6 has been
installed using MySQL Installer, this is typically
C:\PROGRAMDIR\MySQL\MySQL
5.6 Server where
PROGRAMDIR represents the programs
directory (usually Program Files on
English-language versions of Windows), See
Section 2.3.3, “MySQL Installer for Windows”.

On Unix and Unix-like systems, MySQL programs read startup options
from the files shown in the following table, in the specified
order (files listed first are read first, files read later take
precedence).

Note

On Unix platforms, MySQL ignores configuration files that are
world-writable. This is intentional as a security measure.

In the preceding table, ~ represents the
current user's home directory (the value of
$HOME).

SYSCONFDIR represents the directory
specified with the SYSCONFDIR option
to CMake when MySQL was built. By default, this
is the etc directory located under the
compiled-in installation directory.

MYSQL_HOME is an environment variable
containing the path to the directory in which the server-specific
my.cnf file resides. If
MYSQL_HOME is not set and you start the server
using the mysqld_safe program,
mysqld_safe attempts to set
MYSQL_HOME as follows:

If there is a my.cnf file in
DATADIR but not in
BASEDIR,
mysqld_safe sets
MYSQL_HOME to
DATADIR.

Otherwise, if MYSQL_HOME is not set and
there is no my.cnf file in
DATADIR,
mysqld_safe sets
MYSQL_HOME to
BASEDIR.

In MySQL 5.6, use of
DATADIR as the location for
my.cnf is deprecated.

DATADIR is commonly
/usr/local/mysql/data, although this can vary
per platform or installation method. The value is the data
directory location built in when MySQL was compiled, not the
location specified with the
--datadir option when
mysqld starts. Use of
--datadir at runtime has no effect
on where the server looks for option files that it reads before
processing any options.

If multiple instances of a given option are found, the last
instance takes precedence, with one exception: For
mysqld, the first instance
of the --user option is used as a
security precaution, to prevent a user specified in an option file
from being overridden on the command line.

The following description of option file syntax applies to files
that you edit manually. This excludes
.mylogin.cnf, which is created using
mysql_config_editor and is encrypted.

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. (For mysqld,
use --verbose and
--help.)

The syntax for specifying options in an option file is similar to
command-line syntax (see Section 4.2.4, “Using Options on the Command Line”).
However, in an option file, you omit the leading two dashes from
the option name and you specify only one option per line. For
example, --quick and
--host=localhost on the command line should be
specified as quick and
host=localhost on separate lines 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. Nonempty lines can take
any of the following forms:

#comment,
;comment

Comment lines start with # or
;. A # comment can start
in the middle of a line as well.

[group]

group is the name of the program or
group for which you want to set options. After a group line,
any option-setting lines apply to the named group until the
end of the option file or another group line is given. Option
group names are not case-sensitive.

opt_name

This is equivalent to
--opt_name on the
command line.

opt_name=value

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. The value optionally can be
enclosed within single quotation marks or double quotation
marks, which is useful if the value contains a
# comment character.

Leading and trailing spaces are automatically deleted from option
names and values.

You can use the escape sequences \b,
\t, \n,
\r, \\, and
\s in option values to represent the backspace,
tab, newline, carriage return, backslash, and space characters. In
option files, these escaping rules apply:

A backslash followed by a valid escape sequence character is
converted to the character represented by the sequence. For
example, \s is converted to a space.

A backslash not followed by a valid escape sequence character
remains unchanged. For example, \S is
retained as is.

The preceding rules mean that a literal backslash can be given as
\\, or as \ if it is not
followed by a valid escape sequence character.

The rules for escape sequences in option files differ slightly
from the rules for escape sequences in string literals in SQL
statements. In the latter context, if
“x” is not a valid escape
sequence character,
\x becomes
“x” rather than
\x. See
Section 9.1.1, “String Literals”.

The escaping rules for option file values are especially pertinent
for Windows path names, which use \ as a path
name separator. A separator in a Windows path name must be written
as \\ if it is followed by an escape sequence
character. It can be written as \\ or
\ if it is not. Alternatively,
/ may be used in Windows path names and will be
treated as \. Suppose that you want to specify
a base directory of C:\Program Files\MySQL\MySQL Server
5.6 in an option file. This can be done
several ways. Some examples:

If an option group name is the same as a program name, options in
the group apply specifically to that program. For example, the
[mysqld] and [mysql] groups
apply to the mysqld server and the
mysql client program, respectively.

The [client] option group is read by all client
programs provided in MySQL distributions (but
not by mysqld). To
understand how third-party client programs that use the C API can
use option files, see the C API documentation at
Section 23.8.7.49, “mysql_options()”.

The [client] group enables you to specify
options that apply to all clients. For example,
[client] is the appropriate group to use to
specify the password for connecting to the server. (But make sure
that the option file is accessible only by yourself, so that other
people cannot discover 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 quit after
displaying an error message if you try to run them.

List more general option groups first and more specific groups
later. For example, a [client] group is more
general because it is read by all client programs, whereas a
[mysqldump] group is read only by
mysqldump. Options specified later override
options specified earlier, so putting the option groups in the
order [client], [mysqldump]
enables mysqldump-specific options to override
[client] options.

[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

To create option groups to be read only by
mysqld servers from specific MySQL release
series, use groups with names of
[mysqld-5.5],
[mysqld-5.6], and so forth. The
following group indicates that the
sql_mode setting should be used
only by MySQL servers with 5.6.x version numbers:

[mysqld-5.6]
sql_mode=TRADITIONAL

It is possible to use !include directives in
option files to include other option files and
!includedir to search specific directories for
option files. For example, to include the
/home/mydir/myopt.cnf file, use the following
directive:

!include /home/mydir/myopt.cnf

To search the /home/mydir directory and read
option files found there, use this directive:

!includedir /home/mydir

MySQL makes no guarantee about the order in which option files in
the directory will be read.

Note

Any files to be found and included using the
!includedir directive on Unix operating
systems must have file names ending in
.cnf. On Windows, this directive checks for
files with the .ini or
.cnf extension.

Write the contents of an included option file like any other
option file. That is, it should contain groups of options, each
preceded by a
[group] line that
indicates the program to which the options apply.

While an included file is being processed, only those options in
groups that the current program is looking for are used. Other
groups are ignored. Suppose that a my.cnf
file contains this line:

!include /home/mydir/myopt.cnf

And suppose that /home/mydir/myopt.cnf looks
like this:

[mysqladmin]
force
[mysqld]
key_buffer_size=16M

If my.cnf is processed by
mysqld, only the [mysqld]
group in /home/mydir/myopt.cnf is used. If
the file is processed by mysqladmin, only the
[mysqladmin] group is used. If the file is
processed by any other program, no options in
/home/mydir/myopt.cnf are used.

The !includedir directive is processed
similarly except that all option files in the named directory are
read.

If an option file contains !include or
!includedir directives, files named by those
directives are processed whenever the option file is processed, no
matter where they appear in the file.

4.2.7 Command-Line Options that Affect Option-File Handling

Most MySQL programs that support option files handle the following
options. Because these options affect option-file handling, they
must be given on the command line and not in an option file. To
work properly, each of these options must be given before other
options, with these exceptions:

Read this option file after the global option file but (on
Unix) before the user option file and (on all platforms)
before the login path file. (For information about the order
in which option files are used, see
Section 4.2.6, “Using Option Files”.) If the file does not exist or
is otherwise inaccessible, an error occurs.
file_name is interpreted relative
to the current directory if given as a relative path name
rather than a full path name.

See the introduction to this section regarding constraints on
the position in which this option may be specified.

Read only the given option file. If the file does not exist or
is otherwise inaccessible, an error occurs.
file_name is interpreted relative
to the current directory if given as a relative path name
rather than a full path name.

Read not only the usual option groups, but also groups with
the usual names and a suffix of
str. For example, the
mysql client normally reads the
[client] and [mysql]
groups. If the
--defaults-group-suffix=_other
option is given, mysql also reads the
[client_other] and
[mysql_other] groups.

A client program reads the option group corresponding to the
named login path, in addition to option groups that the
program reads by default. Consider this command:

shell> mysql --login-path=mypath

By default, the mysql client reads the
[client] and [mysql]
option groups. So for the command shown,
mysql reads [client] and
[mysql] from other option files, and
[client], [mysql], and
[mypath] from the login path file.

Client programs read the login path file even when the
--no-defaults option is used.

To specify an alternate login path file name, set the
MYSQL_TEST_LOGIN_FILE environment variable.

See the introduction to this section regarding constraints on
the position in which this option may be specified.

4.2.8 Using Options to Set Program Variables

Most of these program variables also can be set at server startup
by using the same syntax that applies to specifying program
options. 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. For variables that take a
numeric value, the value can be given with a suffix of
K, M, or
G (either uppercase or lowercase) to indicate a
multiplier of 1024, 10242 or
10243. (For example, when used to set
max_allowed_packet, the suffixes indicate units
of kilobytes, megabytes, or gigabytes.)

In an option file, variable settings are given without the leading
dashes:

[mysql]
max_allowed_packet=16777216

Or:

[mysql]
max_allowed_packet=16M

If you like, underscores in a variable name can be specified as
dashes. The following option groups are equivalent. Both set the
size of the server's key buffer to 512MB:

[mysqld]
key_buffer_size=512M
[mysqld]
key-buffer-size=512M

A variable can be specified by writing it in full or as any
unambiguous prefix. For example, the
max_allowed_packet variable can be set for
mysql as --max_a, but not as
--max because the latter is ambiguous:

Be aware that the use of variable prefixes can cause problems in
the event that new variables are implemented for a program. A
prefix that is unambiguous now might become ambiguous in the
future.

Suffixes for specifying a value multiplier can be used when
setting a variable at server startup, but not to set the value
with SET
at runtime. On the other hand, with
SET, you
can assign a variable's value using an expression, which is not
true when you set a variable at server startup. For example, the
first of the following lines is legal at server startup, but the
second is not:

4.2.9 Option Defaults, Options Expecting Values, and the = Sign

By convention, long forms of options that assign a value are
written with an equals (=) sign, like this:

shell> mysql --host=tonfisk --user=jon

For options that require a value (that is, not having a default
value), the equals sign is not required, and so the following is
also valid:

shell> mysql --host tonfisk --user jon

In both cases, the mysql client attempts to
connect to a MySQL server running on the host named
“tonfisk” using an account with the user name
“jon”.

Due to this behavior, problems can occasionally arise when no
value is provided for an option that expects one. Consider the
following example, where a user connects to a MySQL server running
on host tonfisk as user jon:

In this case, mysql was unable to find a value
following the --user option
because nothing came after it on the command line. However, if you
omit the value for an option that is not the
last option to be used, you obtain a different error that you may
not be expecting:

Because mysql assumes that any string following
--host on the command line is a
host name, --host--user is interpreted as
--host=--user, and the client
attempts to connect to a MySQL server running on a host named
“--user”.

Options having default values always require an equals sign when
assigning a value; failing to do so causes an error. For example,
the MySQL server --log-error option
has the default value
host_name.err,
where host_name is the name of the host
on which MySQL is running. Assume that you are running MySQL on a
computer whose host name is “tonfisk”, and consider
the following invocation of mysqld_safe:

The result is the same, since
--log-error is not followed by
anything else on the command line, and it supplies its own default
value. (The & character tells the operating
system to run MySQL in the background; it is ignored by MySQL
itself.) Now suppose that you wish to log errors to a file named
my-errors.err. You might try starting the
server with --log-error my-errors, but this does
not have the intended effect, as shown here:

The --log-error option does not
require an argument; however, the
--relay-log option requires one, as
shown in the error log (which in the absence of a specified value,
defaults to
datadir/hostname.err):

This is a change from previous behavior, where the server would
have interpreted the last two lines in the example
my.cnf file as
--relay-log=relay_log_index and created a relay
log file using “relay_log_index” as the base name.
(Bug #25192)

4.2.10 Setting Environment Variables

Environment variables can be set at the command prompt to affect
the current invocation of your command processor, or set
permanently to affect future invocations. To set a variable
permanently, you can set it in a startup file or by using the
interface provided by your system for this purpose. Consult the
documentation for your command interpreter for specific details.
Section 4.9, “MySQL Program Environment Variables”, lists all environment
variables that affect MySQL program operation.

To specify a value for an environment variable, use the syntax
appropriate for your command processor. For example, on Windows,
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 that you want to
specify the TCP/IP port number using the
MYSQL_TCP_PORT variable. Typical syntax (such
as for sh, ksh,
bash, 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, 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, but the settings
persist only until you log out. To have the settings take effect
each time you log in, use the interface provided by your system or
place the appropriate command or commands in a startup file that
your command interpreter reads each time it starts.

On Windows, you can set environment variables using the System
Control Panel (under Advanced).

On Unix, typical shell startup files are
.bashrc or .bash_profile
for bash, or .tcshrc for
tcsh.

Suppose that your MySQL programs are installed in
/usr/local/mysql/bin and that you want to make
it easy to invoke these programs. To do this, set the value of the
PATH environment variable to include that
directory. For example, if your shell is bash,
add the following line to your .bashrc file:

PATH=${PATH}:/usr/local/mysql/bin

bash uses different startup files for login and
nonlogin shells, so you might want to add the setting to
.bashrc for login shells and to
.bash_profile for nonlogin shells to make
sure that PATH is set regardless.

If your shell is tcsh, add the following line
to your .tcshrc file:

setenv PATH ${PATH}:/usr/local/mysql/bin

If the appropriate startup file does not exist in your home
directory, create it with a text editor.

After modifying your PATH setting, open a new
console window on Windows or log in again on Unix so that the
setting goes into effect.

4.3 MySQL Server and Server-Startup Programs

This section describes mysqld, the MySQL server,
and several programs that are used to start the server.

4.3.1 mysqld — The MySQL Server

mysqld, also known as MySQL Server, is the
main program that does most of the work in a MySQL installation.
MySQL Server manages access to the MySQL data directory that
contains databases and tables. The data directory is also the
default location for other information such as log files and
status files.

When MySQL server starts, it listens for network connections
from client programs and manages access to databases on behalf
of those clients.

The mysqld program has many options that can
be specified at startup. For a complete list of options, run
this command:

shell> mysqld --verbose --help

MySQL Server also has a set of system variables that affect its
operation as it runs. System variables can be set at server
startup, and many of them can be changed at runtime to effect
dynamic server reconfiguration. MySQL Server also has a set of
status variables that provide information about its operation.
You can monitor these status variables to access runtime
performance characteristics.

4.3.2 mysqld_safe — MySQL Server Startup Script

mysqld_safe is the recommended way to start a
mysqld server on Unix.
mysqld_safe adds some safety features such as
restarting the server when an error occurs and logging runtime
information to an error log. A description of error logging is
given later in this section.

mysqld_safe reads all options from the
[mysqld], [server], and
[mysqld_safe] sections in option files. For
example, if you specify a [mysqld] section
like this, mysqld_safe will find and use the
--log-error option:

[mysqld]
log-error=error.log

For backward compatibility, mysqld_safe also
reads [safe_mysqld] sections, but to be
current you should rename such sections to
[mysqld_safe].

Read this option file in addition to the usual option files.
If the file does not exist or is otherwise inaccessible, the
server will exit with an error.
file_name is interpreted relative
to the current directory if given as a relative path name
rather than a full path name. This must be the first option
on the command line if it is used.

Use only the given option file. If the file does not exist
or is otherwise inaccessible, the server will exit with an
error. file_name is interpreted
relative to the current directory if given as a relative
path name rather than a full path name. This must be the
first option on the command line if it is used.

The name of the library to use for memory allocation instead
of the system malloc() library. As of
MySQL 5.6.33, the option value must be one of the
directories /usr/lib,
/usr/lib64,
/usr/lib/i386-linux-gnu, or
/usr/lib/x86_64-linux-gnu. Prior to
MySQL 5.6.33, any library can be used by specifying its path
name, but there is a shortcut form to enable use of the
tcmalloc library that is shipped with
binary MySQL distributions for Linux in MySQL
5.6. It is possible that the shortcut form will
not work under certain configurations, in which case you
should specify a path name instead.

Note

As of MySQL 5.6.31, MySQL distributions no longer include
a tcmalloc library.

The --malloc-lib option
works by modifying the LD_PRELOAD
environment value to affect dynamic linking to enable the
loader to find the memory-allocation library when
mysqld runs:

If the option is not given, or is given without a value
(--malloc-lib=),
LD_PRELOAD is not modified and no
attempt is made to use tcmalloc.

If the option is given as
--malloc-lib=tcmalloc,
mysqld_safe looks for a
tcmalloc library in
/usr/lib and then in the MySQL
pkglibdir location (for example,
/usr/local/mysql/lib or whatever is
appropriate). If tmalloc is found,
its path name is added to the beginning of the
LD_PRELOAD value for
mysqld. If
tcmalloc is not found,
mysqld_safe aborts with an error.

The name of the server program (in the
ledir directory) that you want to start.
This option is needed if you use the MySQL binary
distribution but have the data directory outside of the
binary distribution. If mysqld_safe
cannot find the server, use the
--ledir option to
indicate the path name to the directory where the server is
located.

As of MySQL 5.6.33, this option is accepted only on the
command line, not in option files.

Do not read any option files. If program startup fails due
to reading unknown options from an option file,
--no-defaults can be
used to prevent them from being read. This must be the first
option on the command line if it is used.

For logging to syslog, messages from
mysqld_safe and mysqld
are written with identifiers of
mysqld_safe and
mysqld, respectively. To specify a suffix
for the identifiers, use
--syslog-tag=tag,
which modifies the identifiers to be
mysqld_safe-tag
and
mysqld-tag.

Run the mysqld server as the user having
the name user_name or the numeric
user ID user_id.
(“User” in this context refers to a system
login account, not a MySQL user listed in the grant tables.)

If you execute mysqld_safe with the
--defaults-file or
--defaults-extra-file option
to name an option file, the option must be the first one given
on the command line or the option file will not be used. For
example, this command will not use the named option file:

mysql> mysqld_safe --port=port_num --defaults-file=file_name

Instead, use the following command:

mysql> mysqld_safe --defaults-file=file_name --port=port_num

The mysqld_safe script is written so that it
normally can start a server that was installed from either a
source or a binary distribution of MySQL, even though these
types of distributions typically install the server in slightly
different locations. (See
Section 2.1.4, “Installation Layouts”.)
mysqld_safe expects one of the following
conditions to be true:

The server and databases can be found relative to the
working directory (the directory from which
mysqld_safe is invoked). For binary
distributions, mysqld_safe looks under
its working directory for bin and
data directories. For source
distributions, it looks for libexec and
var directories. This condition should
be met if you execute mysqld_safe from
your MySQL installation directory (for example,
/usr/local/mysql for a binary
distribution).

If the server and databases cannot be found relative to the
working directory, mysqld_safe attempts
to locate them by absolute path names. Typical locations are
/usr/local/libexec and
/usr/local/var. The actual locations
are determined from the values configured into the
distribution at the time it was built. They should be
correct if MySQL is installed in the location specified at
configuration time.

Because mysqld_safe tries to find the server
and databases relative to its own working directory, you can
install a binary distribution of MySQL anywhere, as long as you
run mysqld_safe from the MySQL installation
directory:

shell> cd mysql_installation_directory
shell> bin/mysqld_safe &

If mysqld_safe fails, even when invoked from
the MySQL installation directory, specify the
--ledir and
--datadir options to
indicate the directories in which the server and databases are
located on your system.

In MySQL 5.6.5 and later, mysqld_safe tries
to use the sleep and date
system utilities to determine how many times it has attempted to
start this second, and—if these are present and this is
greater than 5 times—is forced to wait 1 full second
before starting again. This is intended to prevent excessive CPU
usage in the event of repeated failures. (Bug #11761530, Bug
#54035)

When mysqld_safe writes a message, notices go
to the logging destination (syslog or the
error log file) and stdout. Errors go to the
logging destination and stderr.

4.3.3 mysql.server — MySQL Server Startup Script

MySQL distributions on Unix and Unix-like system include a
script named mysql.server, which starts the
MySQL server using mysqld_safe. It can be
used on systems such as Linux and Solaris that use System
V-style run directories to start and stop system services. It is
also used by the macOS Startup Item for MySQL.

mysql.server is the script name as used
within the MySQL source tree. The installed name might be
different; for example, mysqld or
mysql. In the following discussion, adjust
the name mysql.server as appropriate for your
system.

To start or stop the server manually using the
mysql.server script, invoke it from the
command line with start or
stop arguments:

shell> mysql.server start
shell> mysql.server stop

mysql.server changes location to the MySQL
installation directory, then invokes
mysqld_safe. To run the server as some
specific user, add an appropriate user option
to the [mysqld] group of the global
/etc/my.cnf option file, as shown later in
this section. (It is possible that you must edit
mysql.server if you've installed a binary
distribution of MySQL in a nonstandard location. Modify it to
change location into the proper directory before it runs
mysqld_safe. If you do this, your modified
version of mysql.server may be overwritten if
you upgrade MySQL in the future; make a copy of your edited
version that you can reinstall.)

If you install MySQL from a source distribution or using a
binary distribution format that does not install
mysql.server automatically, you can
install the script manually. It can be found in the
support-files directory under the MySQL
installation directory or in a MySQL source tree. Copy the
script to the /etc/init.d directory
with the name mysql and make it
executable:

After installing the script, the commands needed to activate
it to run at system startup depend on your operating system.
On Linux, you can use chkconfig:

shell> chkconfig --add mysql

On some Linux systems, the following command also seems to
be necessary to fully enable the mysql
script:

shell> chkconfig --level 345 mysql on

On FreeBSD, startup scripts generally should go in
/usr/local/etc/rc.d/. Install the
mysql.server script as
/usr/local/etc/rc.d/mysql.server.sh to
enable automatic startup. The rc(8)
manual page states that scripts in this directory are
executed only if their base name matches the
*.sh shell file name pattern. Any other
files or directories present within the directory are
silently ignored.

As an alternative to the preceding setup, some operating
systems also use /etc/rc.local or
/etc/init.d/boot.local to start
additional services on startup. To start up MySQL using this
method, append a command like the one following to the
appropriate startup file:

/bin/sh -c 'cd /usr/local/mysql; ./bin/mysqld_safe --user=mysql &'

For other systems, consult your operating system
documentation to see how to install startup scripts.

mysql.server reads options from the
[mysql.server] and
[mysqld] sections of option files. For
backward compatibility, it also reads
[mysql_server] sections, but to be current
you should rename such sections to
[mysql.server].

You can add options for mysql.server in a
global /etc/my.cnf file. A typical
my.cnf file might look like this:

The mysql.server script supports the options
shown in the following table. If specified, they
must be placed in an option file, not on
the command line. mysql.server supports only
start and stop as
command-line arguments.

The path name of the file in which the server should write
its process ID. The server creates the file in the data
directory unless an absolute path name is given to specify a
different directory.

If this option is not given, mysql.server
uses a default value of
host_name.pid.
The PID file value passed to mysqld_safe
overrides any value specified in the
[mysqld_safe] option file group. Because
mysql.server reads the
[mysqld] option file group but not the
[mysqld_safe] group, you can ensure that
mysqld_safe gets the same value when
invoked from mysql.server as when invoked
manually by putting the same pid-file
setting in both the [mysqld_safe] and
[mysqld] groups.

How long in seconds to wait for confirmation of server
startup. If the server does not start within this time,
mysql.server exits with an error. The
default value is 900. A value of 0 means not to wait at all
for startup. Negative values mean to wait forever (no
timeout).

4.3.4 mysqld_multi — Manage Multiple MySQL Servers

mysqld_multi is designed to manage several
mysqld processes that listen for connections
on different Unix socket files and TCP/IP ports. It can start or
stop servers, or report their current status.

mysqld_multi searches for groups named
[mysqldN] in
my.cnf (or in the file named by the
--defaults-file option).
N can be any positive integer. This
number is referred to in the following discussion as the option
group number, or GNR. Group numbers
distinguish option groups from one another and are used as
arguments to mysqld_multi to specify which
servers you want to start, stop, or obtain a status report for.
Options listed in these groups are the same that you would use
in the [mysqld] group used for starting
mysqld. (See, for example,
Section 2.10.5, “Starting and Stopping MySQL Automatically”.) However, when using multiple
servers, it is necessary that each one use its own value for
options such as the Unix socket file and TCP/IP port number. For
more information on which options must be unique per server in a
multiple-server environment, see
Section 5.7, “Running Multiple MySQL Instances on One Machine”.

start, stop,
reload (stop and restart), and
report indicate which operation to perform.
(reload is available as of MySQL 5.6.3.) You
can perform the designated operation for a single server or
multiple servers, depending on the
GNR list that follows the option
name. If there is no list, mysqld_multi
performs the operation for all servers in the option file.

Each GNR value represents an option
group number or range of group numbers. The value should be the
number at the end of the group name in the option file. For
example, the GNR for a group named
[mysqld17] is 17. To
specify a range of numbers, separate the first and last numbers
by a dash. The GNR value
10-13 represents groups
[mysqld10] through
[mysqld13]. Multiple groups or group ranges
can be specified on the command line, separated by commas. There
must be no whitespace characters (spaces or tabs) in the
GNR list; anything after a whitespace
character is ignored.

This command starts a single server using option group
[mysqld17]:

shell> mysqld_multi start 17

This command stops several servers, using option groups
[mysqld8] and [mysqld10]
through [mysqld13]:

shell> mysqld_multi stop 8,10-13

For an example of how you might set up an option file, use this
command:

Otherwise, option files in the standard list of locations
are read, including any file named by the
--defaults-extra-file=file_name
option, if one is given. (If the option is given multiple
times, the last value is used.)

Option files read are searched for
[mysqld_multi] and
[mysqldN] option
groups. The [mysqld_multi] group can be used
for options to mysqld_multi itself.
[mysqldN] groups
can be used for options passed to specific
mysqld instances.

The [mysqld] or
[mysqld_safe] groups can be used for common
options read by all instances of mysqld or
mysqld_safe. You can specify a
--defaults-file=file_name
option to use a different configuration file for that instance,
in which case the [mysqld] or
[mysqld_safe] groups from that file will be
used for that instance.

Connect to each MySQL server through the TCP/IP port instead
of the Unix socket file. (If a socket file is missing, the
server might still be running, but accessible only through
the TCP/IP port.) By default, connections are made using the
Unix socket file. This option affects
stop and report
operations.

Most important: Before
using mysqld_multi be sure that you
understand the meanings of the options that are passed to
the mysqld servers and
why you would want to have separate
mysqld processes. Beware of the dangers
of using multiple mysqld servers with the
same data directory. Use separate data directories, unless
you know what you are doing. Starting
multiple servers with the same data directory does
not give you extra performance in a
threaded system. See Section 5.7, “Running Multiple MySQL Instances on One Machine”.

Make sure that the MySQL account used for stopping the
mysqld servers (with the
mysqladmin program) has the same user
name and password for each server. Also, make sure that the
account has the SHUTDOWN
privilege. If the servers that you want to manage have
different user names or passwords for the administrative
accounts, you might want to create an account on each server
that has the same user name and password. For example, you
might set up a common multi_admin account
by executing the following commands for each server:

The Unix socket file and the TCP/IP port number must be
different for every mysqld.
(Alternatively, if the host has multiple network addresses,
you can use --bind-address to
cause different servers to listen to different interfaces.)

You might want to use the
--user option for
mysqld, but to do this you need to run
the mysqld_multi script as the Unix
superuser (root). Having the option in
the option file doesn't matter; you just get a warning if
you are not the superuser and the mysqld
processes are started under your own Unix account.

The following example shows how you might set up an option file
for use with mysqld_multi. The order in which
the mysqld programs are started or stopped
depends on the order in which they appear in the option file.
Group numbers need not form an unbroken sequence. The first and
fifth [mysqldN]
groups were intentionally omitted from the example to illustrate
that you can have “gaps” in the option file. This
gives you more flexibility.

The programs in this section are used when installing or upgrading
MySQL.

4.4.1 comp_err — Compile MySQL Error Message File

comp_err creates the
errmsg.sys file that is used by
mysqld to determine the error messages to
display for different error codes. comp_err
normally is run automatically when MySQL is built. It compiles
the errmsg.sys file from the text file
located at sql/share/errmsg-utf8.txt in
MySQL source distributions.

4.4.2 mysqlbug — Generate Bug Report

This program is obsolete. It is deprecated as of MySQL 5.6.19
and is removed in MySQL 5.7.

The normal way to report bugs is to visit
http://bugs.mysql.com/, which is the address for
our bugs database. This database is public and can be browsed
and searched by anyone. If you log in to the system, you can
enter new reports.

4.4.3 mysql_install_db — Initialize MySQL Data Directory

mysql_install_db initializes the MySQL data
directory and creates the system tables that it contains, if
they do not exist. It also initializes the
system tablespace
and related data structures needed to manage
InnoDB tables. As of MySQL 5.6.8,
mysql_install_db is a Perl script and can be
used on any system with Perl installed. Before 5.6.8, it is a
shell script and is available only on Unix platforms.

As of MySQL 5.6.8, on Unix platforms,
mysql_install_db creates a default option
file named my.cnf in the base installation
directory. This file is created from a template included in the
distribution package named my-default.cnf.
You can find the template in or under the base installation
directory. When started using mysqld_safe,
the server uses my.cnf file by default. If
my.cnf already exists,
mysql_install_db assumes it to be in use and
writes a new file named my-new.cnf instead.

With one exception, the settings in the default option file are
commented and have no effect. The exception is that the file
sets the sql_mode system
variable to
NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES.
This setting produces a server configuration that results in
errors rather than warnings for bad data in operations that
modify transactional tables. See Section 5.1.10, “Server SQL Modes”.

Because the MySQL server, mysqld, must access
the data directory when it runs later, you should either run
mysql_install_db from the same system account
that will be used for running mysqld, or run
it as root and specify the
--user option to
indicate the user name that mysqld will run
as. It might be necessary to specify other options such as
--basedir or
--datadir if
mysql_install_db does not use the correct
locations for the installation directory or data directory. For
example:

If those options are in your configuration file but that file
is not in a location that MySQL reads by default, specify the
file location using the
--defaults-extra-file
option when you run mysql_install_db.

Note

If you have set a custom TMPDIR environment
variable when performing the installation, and the specified
directory is not accessible,
mysql_install_db may fail. If so, unset
TMPDIR or set TMPDIR to
point to the system temporary directory (usually
/tmp).

mysql_install_db supports the following
options, which can be specified on the command line or in the
[mysql_install_db] group of an option file.
(Options that are common to mysqld can also
be specified in the [mysqld] group.) Other
options are passed to mysqld. For information
about option files used by MySQL programs, see
Section 4.2.6, “Using Option Files”.

The path to the MySQL data directory. Beginning with MySQL
5.6.8, mysql_install_db is more strict
about the option value. Only the last component of the path
name is created if it does not exist; the parent directory
must already exist or an error occurs.

Read this option file after the global option file but (on
Unix) before the user option file. If the file does not
exist or is otherwise inaccessible, an error occurs.
file_name is interpreted relative
to the current directory if given as a relative path name
rather than a full path name.

Use only the given option file. If the file does not exist
or is otherwise inaccessible, an error occurs.
file_name is interpreted relative
to the current directory if given as a relative path name
rather than a full path name.

On Unix platforms, this option provides for more secure
MySQL installation. Invoking
mysql_install_db with
--random-passwords
causes it to perform the following actions in addition to
its normal operation:

The installation process creates a random password,
assigns it to the initial MySQL root
accounts, and sets the “password expired”
flag for those accounts.

The initial random root password is
written to the .mysql_secret file
in the directory named by the HOME
environment variable. Depending on operating system,
using a command such as sudo may
cause the value of HOME to refer to
the home directory of the root system
user.

If .mysql_secret already exists,
the new password information is appended to it. Each
password entry includes a timestamp so that in the event
of multiple install operations it is possible to
determine the password associated with each one.

.mysql_secret is created with mode
600 to be accessible only to the system user for whom it
is created.

No anonymous-user MySQL accounts are created.

As a result of these actions, it is necessary after
installation to start the server, connect as
root using the password written to the
.mysql_secret file, and specify a new
root password. Until this is done,
root cannot do anything else. This must
be done for each root account you intend
to use. To change the password, you can use the
SET PASSWORD statement (for
example, with the mysql client). You can
also use mysqladmin or
mysql_secure_installation.

New RPM install operations (not upgrades) invoke
mysql_install_db with the
--random-passwords option. (Install
operations using RPMs for Unbreakable Linux Network are
unaffected because they do not use
mysql_install_db.)

For install operations using a binary
.tar.gz distribution or a source
distribution, you can invoke
mysql_install_db with the
--random-passwords option manually to make
your MySQL installation more secure. This is recommended,
particularly for sites with sensitive data.

The system (login) user name to use for running
mysqld. Files and directories created by
mysqld will be owned by this user. You
must be the system root user to use this
option. By default, mysqld runs using
your current login name and files and directories that it
creates will be owned by you.

Depending on whether mysql_plugin is invoked
to enable or disable plugins, it inserts or deletes rows in the
mysql.plugin table that serves as a plugin
registry. (To perform this operation,
mysql_plugin invokes the MySQL server in
bootstrap mode. This means that the server must not already be
running.) For normal server startups, the server loads and
enables plugins listed in mysql.plugin
automatically. For additional control over plugin activation,
use --plugin_name
options named for specific plugins, as described in
Section 5.5.1, “Installing and Uninstalling Plugins”.

Each invocation of mysql_plugin reads a
configuration file to determine how to configure the plugins
contained in a single plugin library file. To invoke
mysql_plugin, use this syntax:

mysql_plugin [options] plugin {ENABLE|DISABLE}

plugin is the name of the plugin to
configure. ENABLE or
DISABLE (not case-sensitive) specify whether
to enable or disable components of the plugin library named in
the configuration file. The order of the
plugin and ENABLE
or DISABLE arguments does not matter.

For example, to configure components of a plugin library file
named myplugins.so on Linux or
myplugins.dll on Windows, specify a
plugin value of
myplugins. Suppose that this plugin library
contains three plugins, plugin1,
plugin2, and plugin3, all
of which should be configured under
mysql_plugin control. By convention,
configuration files have a suffix of .ini
and the same base name as the plugin library, so the default
configuration file name for this plugin library is
myplugins.ini. The configuration file
contents look like this:

myplugins
plugin1
plugin2
plugin3

The first line in the myplugins.ini file is
the name of the library file, without any extension such as
.so or .dll. The
remaining lines are the names of the components to be enabled or
disabled. Each value in the file should be on a separate line.
Lines on which the first character is '#' are
taken as comments and ignored.

To enable the plugins listed in the configuration file, invoke
mysql_plugin this way:

shell> mysql_plugin myplugins ENABLE

To disable the plugins, use DISABLE rather
than ENABLE.

An error occurs if mysql_plugin cannot find
the configuration file or plugin library file, or if
mysql_plugin cannot start the MySQL server.

The mysql_plugin configuration file.
Relative path names are interpreted relative to the current
directory. If this option is not given, the default is
plugin.ini
in the plugin directory, where
plugin is the
plugin argument on the command
line.

Display the default values from the configuration file. This
option causes mysql_plugin to print the
defaults for --basedir,
--datadir, and
--plugin-dir if they
are found in the configuration file. If no value for a
variable is found, nothing is shown.

When executed, the script prompts you to determine which actions
to perform.

4.4.6 mysql_tzinfo_to_sql — Load the Time Zone Tables

The mysql_tzinfo_to_sql program loads the
time zone tables in the mysql database. It is
used on systems that have a
zoneinfo database (the set
of files describing time zones). Examples of such systems are
Linux, FreeBSD, Solaris, and OS X. One likely location for these
files is the /usr/share/zoneinfo directory
(/usr/share/lib/zoneinfo on Solaris). If
your system does not have a zoneinfo database, you can use the
downloadable package described in
Section 5.1.12, “MySQL Server Time Zone Support”.

For the first invocation syntax, pass the zoneinfo directory
path name to mysql_tzinfo_to_sql and send the
output into the mysql program. For example:

shell> mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root mysql

mysql_tzinfo_to_sql reads your system's time
zone files and generates SQL statements from them.
mysql processes those statements to load the
time zone tables.

The second syntax causes mysql_tzinfo_to_sql
to load a single time zone file
tz_file that corresponds to a time
zone name tz_name:

shell> mysql_tzinfo_to_sql tz_filetz_name | mysql -u root mysql

If your time zone needs to account for leap seconds, invoke
mysql_tzinfo_to_sql using the third syntax,
which initializes the leap second information.
tz_file is the name of your time zone
file:

shell> mysql_tzinfo_to_sql --leap tz_file | mysql -u root mysql

After running mysql_tzinfo_to_sql, it is best
to restart the server so that it does not continue to use any
previously cached time zone data.

4.4.7 mysql_upgrade — Check and Upgrade MySQL Tables

mysql_upgrade examines all tables in all
databases for incompatibilities with the current version of
MySQL Server. mysql_upgrade also upgrades the
system tables so that you can take advantage of new privileges
or capabilities that might have been added.

On Windows, you must run mysql_upgrade with
administrator privileges. You can do this by running a Command
Prompt as Administrator and running the command. Failure to do
so may result in the upgrade failing to execute correctly.

Some upgrade incompatibilities may require special handling
before you upgrade your MySQL installation and run
mysql_upgrade. See
Section 2.11.1, “Upgrading MySQL”, for instructions on determining
whether any such incompatibilities apply to your installation
and how to handle them.

To use mysql_upgrade, make sure that the
server is running. Then invoke it like this:

shell> mysql_upgrade [options]

After running mysql_upgrade, stop the server
and restart it so that any changes made to the system tables
take effect.

If you have multiple MySQL server instances running, invoke
mysql_upgrade with connection parameters
appropriate for connecting to the desired server. For example,
with servers running on the local host on parts 3306 through
3308, upgrade each of them by connecting to the appropriate
port:

Because mysql_upgrade invokes
mysqlcheck with the
--all-databases option,
it processes all tables in all databases, which might take a
long time to complete. Each table is locked and therefore
unavailable to other sessions while it is being processed.
Check and repair operations can be time-consuming,
particularly for large tables.

fix_priv_tables represents a
script generated internally by
mysql_upgrade that contains SQL
statements to upgrade the tables in the
mysql database.

All checked and repaired tables are marked with the current
MySQL version number. This ensures that next time you run
mysql_upgrade with the same version of the
server, it can tell whether there is any need to check or repair
the table again.

mysql_upgrade also saves the MySQL version
number in a file named mysql_upgrade_info
in the data directory. This is used to quickly check whether all
tables have been checked for this release so that table-checking
can be skipped. To ignore this file and perform the check
regardless, use the
--force option.

By default, mysql_upgrade runs as the MySQL
root user. If the root
password is expired when you run
mysql_upgrade, you will see a message that
your password is expired and that
mysql_upgrade failed as a result. To correct
this, reset the root password to unexpire it
and run mysql_upgrade again:

Read this option file after the global option file but (on
Unix) before the user option file. If the file does not
exist or is otherwise inaccessible, an error occurs.
file_name is interpreted relative
to the current directory if given as a relative path name
rather than a full path name.

Use only the given option file. If the file does not exist
or is otherwise inaccessible, an error occurs.
file_name is interpreted relative
to the current directory if given as a relative path name
rather than a full path name.

Read not only the usual option groups, but also groups with
the usual names and a suffix of
str. For example,
mysql_upgrade normally reads the
[client] and
[mysql_upgrade] groups. If the
--defaults-group-suffix=_other
option is given, mysql_upgrade also reads
the [client_other] and
[mysql_upgrade_other] groups.

The password to use when connecting to the server. If you
use the short option form (-p), you
cannot have a space between the option
and the password. If you omit the
password value following the
--password or
-p option on the command line,
mysql_upgrade prompts for one.

The connection protocol to use for connecting to the server.
It is useful when the other connection parameters normally
would cause a protocol to be used other than the one you
want. For details on the permissible values, see
Section 4.2.2, “Connecting to the MySQL Server”.

Check the version of the server to which
mysql_upgrade is connecting to verify
that it is the same as the version for which
mysql_upgrade was built. If not,
mysql_upgrade exits. This option is
enabled by default; to disable the check, use
--skip-version-check. This option was added
in MySQL 5.6.12.

Cause binary logging to be enabled while
mysql_upgrade runs. In MySQL 5.6.6 and
earlier, this was the default behavior. (To disable binary
logging during the upgrade, it was necessary to use the
inverse of this option, by starting the program with
--skip-write-binlog.) Beginning with MySQL
5.6.7, binary logging by mysql_upgrade is
disabled by default (Bug #14221043). Invoke the program
explicitly with --write-binlog if you want
its actions to be written to the binary log. (Also beginning
with MySQL 5.6.7, the --skip-write-binlog
option effectively does nothing.)

When the server is running with global transaction
identifiers (GTIDs) enabled
(gtid_mode=ON), do not
enable binary logging by mysql_upgrade.

4.5.1 mysql — The MySQL Command-Line Client

mysql is a simple SQL shell with input line
editing capabilities. It supports interactive and noninteractive
use. When used interactively, query results are presented in an
ASCII-table format. When used noninteractively (for example, as
a filter), the result is presented in tab-separated format. The
output format can be changed using command options.

If you have problems due to insufficient memory for large result
sets, use the --quick option. This
forces mysql to retrieve results from the
server a row at a time rather than retrieving the entire result
set and buffering it in memory before displaying it. This is
done by returning the result set using the
mysql_use_result() C API
function in the client/server library rather than
mysql_store_result().

Using mysql is very easy. Invoke it from the
prompt of your command interpreter as follows:

4.5.1.1 mysql Client Options

mysql supports the following options, which
can be specified on the command line or in the
[mysql] and [client]
groups of an option file. For information about option files
used by MySQL programs, see Section 4.2.6, “Using Option Files”.

Enable automatic rehashing. This option is on by default,
which enables database, table, and column name completion.
Use
--disable-auto-rehash
to disable rehashing. That causes mysql
to start faster, but you must issue the
rehash command or its
\# shortcut if you want to use name
completion.

To complete a name, enter the first part and press Tab. If
the name is unambiguous, mysql completes
it. Otherwise, you can press Tab again to see the possible
names that begin with what you have typed so far. Completion
does not occur if there is no default database.

Note

This feature requires a MySQL client that is compiled with
the readline library.
Typically, the readline
library is not available on Windows.

This option helps when processing
mysqlbinlog output that may contain
BLOB values. By default,
mysql translates \r\n
in statement strings to \n and interprets
\0 as the statement terminator.
--binary-mode disables both
features. It also disables all mysql
commands except charset and
delimiter in non-interactive mode (for
input piped to mysql or loaded using the
source command).

Indicate to the server that the client can handle sandbox
mode if the account used to connect has an expired password.
This can be useful for noninteractive invocations of
mysql because normally the server
disconnects noninteractive clients that attempt to connect
using an account with an expired password. (See
Section 6.3.6, “Password Expiration and Sandbox Mode”.) This option
was added in MySQL 5.6.12.

Use charset_name as the default
character set for the client and connection.

This option can be useful if the operating system uses one
character set and the mysql client by
default uses another. In this case, output may be formatted
incorrectly. You can usually fix such issues by using this
option to force the client to use the system character set
instead.

Read this option file after the global option file but (on
Unix) before the user option file. If the file does not
exist or is otherwise inaccessible, an error occurs.
file_name is interpreted relative
to the current directory if given as a relative path name
rather than a full path name.

Use only the given option file. If the file does not exist
or is otherwise inaccessible, an error occurs.
file_name is interpreted relative
to the current directory if given as a relative path name
rather than a full path name.

Read not only the usual option groups, but also groups with
the usual names and a suffix of
str. For example,
mysql normally reads the
[client] and [mysql]
groups. If the
--defaults-group-suffix=_other
option is given, mysql also reads the
[client_other] and
[mysql_other] groups.

Disable named commands. Use the \* form
only, or use named commands only at the beginning of a line
ending with a semicolon (;).
mysql starts with this option
enabled by default. However, even with
this option, long-format commands still work from the first
line. See Section 4.5.1.2, “mysql Client Commands”.

A colon-separated list of one or more patterns specifying
statements to ignore for logging purposes. These patterns
are added to the default pattern list
("*IDENTIFIED*:*PASSWORD*"). The value
specified for this option affects logging of statements
written to the history file. For more information, see
Section 4.5.1.3, “mysql Client Logging”. This option was added in
MySQL 5.6.8.

Ignore statements except those that occur while the default
database is the one named on the command line. This option
is rudimentary and should be used with care. Statement
filtering is based only on
USE statements.

Initially, mysql executes statements in
the input because specifying a database
db_name on the command line is
equivalent to inserting
USE
db_name at the
beginning of the input. Then, for each
USE statement encountered,
mysql accepts or rejects following
statements depending on whether the database named is the
one on the command line. The content of the statements is
immaterial.

Use the given command for paging query output. If the
command is omitted, the default pager is the value of your
PAGER environment variable. Valid pagers
are less, more,
cat [> filename], and so forth. This
option works only on Unix and only in interactive mode. To
disable paging, use
--skip-pager.
Section 4.5.1.2, “mysql Client Commands”, discusses output paging
further.

The password to use when connecting to the server. If you
use the short option form (-p), you
cannot have a space between the option
and the password. If you omit the
password value following the
--password or
-p option on the command line,
mysql prompts for one.

The connection protocol to use for connecting to the server.
It is useful when the other connection parameters normally
would cause a protocol to be used other than the one you
want. For details on the permissible values, see
Section 4.2.2, “Connecting to the MySQL Server”.

For tabular output, the “boxing” around columns
enables one column value to be distinguished from another.
For nontabular output (such as is produced in batch mode or
when the --batch or
--silent option is given),
special characters are escaped in the output so they can be
identified easily. Newline, tab, NUL, and
backslash are written as \n,
\t, \0, and
\\. The
--raw option disables this
character escaping.

The following example demonstrates tabular versus nontabular
output and the use of raw mode to disable escaping:

Do not send passwords to the server in old (pre-4.1) format.
This prevents connections except for servers that use the
newer password format. As of MySQL 5.6.7, this option is
enabled by default; use
--skip-secure-auth
to disable it.

The path name to a file containing a client-side copy of the
public key required by the server for RSA key pair-based
password exchange. The file must be in PEM format. This
option applies to clients that connect to the server using
an account that authenticates with the
sha256_password authentication plugin.
This option is ignored for accounts that do not authenticate
with one of those plugins. It is also ignored if RSA-based
password exchange is not used, as is the case when the
client connects to the server using a secure connection.

Verbose mode. Produce more output about what the program
does. This option can be given multiple times to produce
more and more output. (For example, -v -v
-v produces table output format even in batch
mode.)

4.5.1.2 mysql Client Commands

mysql sends each SQL statement that you issue
to the server to be executed. There is also a set of commands
that mysql itself interprets. For a list of
these commands, type help or
\h at the mysql>
prompt:

mysql> help
List of all MySQL commands:
Note that all text commands must be first on line and end with ';'
? (\?) Synonym for `help'.
clear (\c) Clear command.
connect (\r) Reconnect to the server. Optional arguments are db and host.
delimiter (\d) Set statement delimiter.
edit (\e) Edit command with $EDITOR.
ego (\G) Send command to mysql server, display result vertically.
exit (\q) Exit mysql. Same as quit.
go (\g) Send command to mysql server.
help (\h) Display this help.
nopager (\n) Disable pager, print to stdout.
notee (\t) Don't write into outfile.
pager (\P) Set PAGER [to_pager]. Print the query results via PAGER.
print (\p) Print current command.
prompt (\R) Change your mysql prompt.
quit (\q) Quit mysql.
rehash (\#) Rebuild completion hash.
source (\.) Execute an SQL script file. Takes a file name as an argument.
status (\s) Get status information from the server.
system (\!) Execute a system shell command.
tee (\T) Set outfile [to_outfile]. Append everything into given
outfile.
use (\u) Use another database. Takes database name as argument.
charset (\C) Switch to another charset. Might be needed for processing
binlog with multi-byte charsets.
warnings (\W) Show warnings after every statement.
nowarning (\w) Don't show warnings after every statement.
For server side help, type 'help contents'

If mysql is invoked with the
--binary-mode option, all
mysql commands are disabled except
charset and delimiter in
non-interactive mode (for input piped to
mysql or loaded using the
source command).

Each command has both a long and short form. The long form is
not case-sensitive; the short form is. The long form can be
followed by an optional semicolon terminator, but the short form
should not.

The use of short-form commands within multiple-line /*
... */ comments is not supported.

Change the default character set and issue a
SET NAMES statement. This
enables the character set to remain synchronized on the
client and server if mysql is run with
auto-reconnect enabled (which is not recommended), because
the specified character set is used for reconnects.

clear, \c

Clear the current input. Use this if you change your mind
about executing the statement that you are entering.

connect [db_namehost_name]],
\r [db_namehost_name]]

Reconnect to the server. The optional database name and host
name arguments may be given to specify the default database
or the host where the server is running. If omitted, the
current values are used.

delimiter str,
\d str

Change the string that mysql interprets
as the separator between SQL statements. The default is the
semicolon character (;).

The delimiter string can be specified as an unquoted or
quoted argument on the delimiter command
line. Quoting can be done with either single quote
('), double quote ("),
or backtick (`) characters. To include a
quote within a quoted string, either quote the string with a
different quote character or escape the quote with a
backslash (\) character. Backslash should
be avoided outside of quoted strings because it is the
escape character for MySQL. For an unquoted argument, the
delimiter is read up to the first space or end of line. For
a quoted argument, the delimiter is read up to the matching
quote on the line.

mysql interprets instances of the
delimiter string as a statement delimiter anywhere it
occurs, except within quoted strings. Be careful about
defining a delimiter that might occur within other words.
For example, if you define the delimiter as
X, you will be unable to use the word
INDEX in statements.
mysql interprets this as
INDE followed by the delimiter
X.

When the delimiter recognized by mysql is
set to something other than the default of
;, instances of that character are sent
to the server without interpretation. However, the server
itself still interprets ; as a statement
delimiter and processes statements accordingly. This
behavior on the server side comes into play for
multiple-statement execution (see
Section 23.8.16, “C API Multiple Statement Execution Support”), and for parsing
the body of stored procedures and functions, triggers, and
events (see Section 20.1, “Defining Stored Programs”).

edit, \e

Edit the current input statement. mysql
checks the values of the EDITOR and
VISUAL environment variables to determine
which editor to use. The default editor is
vi if neither variable is set.

The edit command works only in Unix.

ego, \G

Send the current statement to the server to be executed and
display the result using vertical format.

Enable output paging. By using the
--pager option when you invoke
mysql, it is possible to browse or search
query results in interactive mode with Unix programs such as
less, more, or any
other similar program. If you specify no value for the
option, mysql checks the value of the
PAGER environment variable and sets the
pager to that. Pager functionality works only in interactive
mode.

Output paging can be enabled interactively with the
pager command and disabled with
nopager. The command takes an optional
argument; if given, the paging program is set to that. With
no argument, the pager is set to the pager that was set on
the command line, or stdout if no pager
was specified.

Output paging works only in Unix because it uses the
popen() function, which does not exist on
Windows. For Windows, the tee option can
be used instead to save query output, although it is not as
convenient as pager for browsing output
in some situations.

print, \p

Print the current input statement without executing it.

prompt [str],
\R [str]

Reconfigure the mysql prompt to the given
string. The special character sequences that can be used in
the prompt are described later in this section.

If you specify the prompt command with no
argument, mysql resets the prompt to the
default of mysql>.

Rebuild the completion hash that enables database, table,
and column name completion while you are entering
statements. (See the description for the
--auto-rehash option.)

source
file_name, \.
file_name

Read the named file and executes the statements contained
therein. On Windows, you can specify path name separators as
/ or \\.

Quote characters are taken as part of the file name itself.
For best results, the name should not include space
characters.

status, \s

Provide status information about the connection and the
server you are using. If you are running with
--safe-updates enabled,
status also prints the values for the
mysql variables that affect your queries.

system
command, \!
command

Execute the given command using your default command
interpreter.

The system command works only in Unix.

tee
[file_name],
\T [file_name]

By using the --tee option when
you invoke mysql, you can log statements
and their output. All the data displayed on the screen is
appended into a given file. This can be very useful for
debugging purposes also. mysql flushes
results to the file after each statement, just before it
prints its next prompt. Tee functionality works only in
interactive mode.

You can enable this feature interactively with the
tee command. Without a parameter, the
previous file is used. The tee file can
be disabled with the notee command.
Executing tee again re-enables logging.

use db_name,
\u db_name

Use db_name as the default
database.

warnings, \W

Enable display of warnings after each statement (if there
are any).

Here are a few tips about the pager command:

You can use it to write to a file and the results go only to
the file:

mysql> pager cat > /tmp/log.txt

You can also pass any options for the program that you want
to use as your pager:

mysql> pager less -n -i -S

In the preceding example, note the -S
option. You may find it very useful for browsing wide query
results. Sometimes a very wide result set is difficult to
read on the screen. The -S option to
less can make the result set much more
readable because you can scroll it horizontally using the
left-arrow and right-arrow keys. You can also use
-S interactively within
less to switch the horizontal-browse mode
on and off. For more information, read the
less manual page:

shell> man less

The -F and -X options may
be used with less to cause it to exit if
output fits on one screen, which is convenient when no
scrolling is necessary:

mysql> pager less -n -i -S -F -X

You can specify very complex pager commands for handling
query output:

In this example, the command would send query results to two
files in two different directories on two different file
systems mounted on /dr1 and
/dr2, yet still display the results
onscreen using less.

You can also combine the tee and
pager functions. Have a
tee file enabled and pager
set to less, and you are able to browse the
results using the less program and still have
everything appended into a file the same time. The difference
between the Unix tee used with the
pager command and the
mysql built-in tee command
is that the built-in tee works even if you do
not have the Unix tee available. The built-in
tee also logs everything that is printed on
the screen, whereas the Unix tee used with
pager does not log quite that much.
Additionally, tee file logging can be turned
on and off interactively from within mysql.
This is useful when you want to log some queries to a file, but
not others.

The prompt command reconfigures the default
mysql> prompt. The string for defining the
prompt can contain the following special sequences.

Option

Description

\c

A counter that increments for each statement you issue

\D

The full current date

\d

The default database

\h

The server host

\l

The current delimiter

\m

Minutes of the current time

\n

A newline character

\O

The current month in three-letter format (Jan, Feb, …)

\o

The current month in numeric format

\P

am/pm

\p

The current TCP/IP port or socket file

\R

The current time, in 24-hour military time (0–23)

\r

The current time, standard 12-hour time (1–12)

\S

Semicolon

\s

Seconds of the current time

\t

A tab character

\U

Your full
user_name@host_name
account name

\u

Your user name

\v

The server version

\w

The current day of the week in three-letter format (Mon, Tue, …)

\Y

The current year, four digits

\y

The current year, two digits

\_

A space

\

A space (a space follows the backslash)

\'

Single quote

\"

Double quote

\\

A literal \ backslash character

\x

x, for any
“x” not listed
above

You can set the prompt in several ways:

Use an environment variable. You can
set the MYSQL_PS1 environment variable to
a prompt string. For example:

shell> export MYSQL_PS1="(\u@\h) [\d]> "

Use a command-line option. You can set
the --prompt option on the
command line to mysql. For example:

shell> mysql --prompt="(\u@\h) [\d]> "
(user@host) [database]>

Use an option file. You can set the
prompt option in the
[mysql] group of any MySQL option file,
such as /etc/my.cnf or the
.my.cnf file in your home directory.
For example:

[mysql]
prompt=(\\u@\\h) [\\d]>\\_

In this example, note that the backslashes are doubled. If
you set the prompt using the prompt
option in an option file, it is advisable to double the
backslashes when using the special prompt options. There is
some overlap in the set of permissible prompt options and
the set of special escape sequences that are recognized in
option files. (The rules for escape sequences in option
files are listed in Section 4.2.6, “Using Option Files”.) The
overlap may cause you problems if you use single
backslashes. For example, \s is
interpreted as a space rather than as the current seconds
value. The following example shows how to define a prompt
within an option file to include the current time in
HH:MM:SS> format:

[mysql]
prompt="\\r:\\m:\\s> "

Set the prompt interactively. You can
change your prompt interactively by using the
prompt (or \R)
command. For example:

4.5.1.3 mysql Client Logging

On Unix, the mysql client logs statements
executed interactively to a history file. By default, this file
is named .mysql_history in your home
directory. To specify a different file, set the value of the
MYSQL_HISTFILE environment variable.

How Logging Occurs

Statement logging occurs as follows:

Statements are logged only when executed interactively.
Statements are noninteractive, for example, when read from a
file or a pipe. It is also possible to suppress statement
logging by using the --batch
or --execute option.

Statements are ignored and not logged if they match any
pattern in the “ignore” list. This list is
described later.

If a nonignored statement spans multiple lines (not
including the terminating delimiter),
mysql concatenates the lines to form the
complete statement, maps newlines to spaces, and logs the
result, plus a delimiter.

Consequently, an input statement that spans multiple lines can
be logged twice. Consider this input:

mysql> SELECT
-> 'Today is'
-> ,
-> CURDATE()
-> ;

In this case, mysql logs the
“SELECT”, “'Today is'”,
“,”, “CURDATE()”, and “;”
lines as it reads them. It also logs the complete statement,
after mapping SELECT\n'Today
is'\n,\nCURDATE() to SELECT 'Today is' ,
CURDATE(), plus a delimiter. Thus, these lines appear
in logged output:

SELECT
'Today is'
,
CURDATE()
;
SELECT 'Today is' , CURDATE();

As of MySQL 5.6.8, mysql ignores for logging
purposes statements that match any pattern in the
“ignore” list. By default, the pattern list is
"*IDENTIFIED*:*PASSWORD*", to ignore
statements that refer to passwords. Pattern matching is not case
sensitive. Within patterns, two characters are special:

? matches any single character.

* matches any sequence of zero or more
characters.

To specify additional patterns, use the
--histignore option or set the
MYSQL_HISTIGNORE environment variable. (If
both are specified, the option value takes precedence.) The
value should be a colon-separated list of one or more patterns,
which are appended to the default pattern list.

Patterns specified on the command line might need to be quoted
or escaped to prevent your command interpreter from treating
them specially. For example, to suppress logging for
UPDATE and DELETE
statements in addition to statements that refer to passwords,
invoke mysql like this:

If you do not want to maintain a history file, first remove
.mysql_history if it exists. Then use
either of the following techniques to prevent it from being
created again:

Set the MYSQL_HISTFILE environment
variable to /dev/null. To cause this
setting to take effect each time you log in, put it in one
of your shell's startup files.

Create .mysql_history as a symbolic
link to /dev/null; this need be done
only once:

shell> ln -s /dev/null $HOME/.mysql_history

4.5.1.4 mysql Client Server-Side Help

mysql> help search_string

If you provide an argument to the help
command, mysql uses it as a search string to
access server-side help from the contents of the MySQL Reference
Manual. The proper operation of this command requires that the
help tables in the mysql database be
initialized with help topic information (see
Section 5.1.13, “Server-Side Help”).

If there is no match for the search string, the search fails:

mysql> help me
Nothing found
Please try to run 'help contents' for a list of all accessible topics

mysql> help contents
You asked for help about help category: "Contents"
For more information, type 'help <item>', where <item> is one of the
following categories:
Account Management
Administration
Data Definition
Data Manipulation
Data Types
Functions
Functions and Modifiers for Use with GROUP BY
Geographic Features
Language Structure
Plugins
Storage Engines
Stored Routines
Table Maintenance
Transactions
Triggers

mysql> help logs
Many help items for your request exist.
To make a more specific request, please type 'help <item>',
where <item> is one of the following topics:
SHOW
SHOW BINARY LOGS
SHOW ENGINE
SHOW LOGS

Use a topic as the search string to see the help entry for that
topic:

mysql> help show binary logs
Name: 'SHOW BINARY LOGS'
Description:
Syntax:
SHOW BINARY LOGS
SHOW MASTER LOGS
Lists the binary log files on the server. This statement is used as
part of the procedure described in [purge-binary-logs], that shows how
to determine which logs can be purged.
mysql> SHOW BINARY LOGS;
+---------------+-----------+
| Log_name | File_size |
+---------------+-----------+
| binlog.000015 | 724935 |
| binlog.000016 | 733481 |
+---------------+-----------+

The search string can contain the wildcard characters
% and _. These have the
same meaning as for pattern-matching operations performed with
the LIKE operator. For example,
HELP rep% returns a list of topics that begin
with rep:

mysql> HELP rep%
Many help items for your request exist.
To make a more specific request, please type 'help <item>',
where <item> is one of the following
topics:
REPAIR TABLE
REPEAT FUNCTION
REPEAT LOOP
REPLACE
REPLACE FUNCTION

4.5.1.5 Executing SQL Statements from a Text File

However, it is also possible to put your SQL statements in a
file and then tell mysql to read its input
from that file. To do so, create a text file
text_file that contains the
statements you wish to execute. Then invoke
mysql as shown here:

shell> mysql db_name < text_file

If you place a USE
db_name statement as the
first statement in the file, it is unnecessary to specify the
database name on the command line:

shell> mysql < text_file

If you are already running mysql, you can
execute an SQL script file using the source
command or \. command:

mysql> source file_name
mysql> \. file_name

Sometimes you may want your script to display progress
information to the user. For this you can insert statements like
this:

SELECT '<info_to_display>' AS ' ';

The statement shown outputs
<info_to_display>.

You can also invoke mysql with the
--verbose option, which causes
each statement to be displayed before the result that it
produces.

mysql ignores Unicode byte order mark (BOM)
characters at the beginning of input files. Previously, it read
them and sent them to the server, resulting in a syntax error.
Presence of a BOM does not cause mysql to
change its default character set. To do that, invoke
mysql with an option such as
--default-character-set=utf8.

4.5.1.6 mysql CLient Tips

This section describes some techniques that can help you use
mysql more effectively.

4.5.1.6.1 Input-Line Editing

mysql supports input-line editing, which
enables you to modify the current input line in place or
recall previous input lines. For example, the
left-arrow and right-arrow
keys move horizontally within the current input line, and the
up-arrow and down-arrow keys
move up and down through the set of previously entered lines.
Backspace deletes the character before the
cursor and typing new characters enters them at the cursor
position. To enter the line, press Enter.

On Windows, the editing key sequences are the same as
supported for command editing in console windows. On Unix, the
key sequences depend on the input library used to build
mysql (for example, the
libedit or readline
library).

Documentation for the libedit and
readline libraries is available online. To
change the set of key sequences permitted by a given input
library, define key bindings in the library startup file. This
is a file in your home directory: .editrc
for libedit and
.inputrc for readline.

For example, in libedit,
Control+W deletes everything before the
current cursor position and Control+U deletes
the entire line. In readline,
Control+W deletes the word before the cursor
and Control+U deletes everything before the
current cursor position. If mysql was built
using libedit, a user who prefers the
readline behavior for these two keys can
put the following lines in the .editrc
file (creating the file if necessary):

bind "^W" ed-delete-prev-word
bind "^U" vi-kill-line-prev

To see the current set of key bindings, temporarily put a line
that says only bind at the end of
.editrc. mysql will
show the bindings when it starts.

4.5.1.6.2 Unicode Support on Windows

Windows provides APIs based on UTF-16LE for reading from and
writing to the console. As of MySQL 5.6.2, the
mysql client for Windows is able to use
these APIs. As of 5.6.3, the Windows installer creates an item
in the MySQL menu named MySQL command line client -
Unicode. This item invokes the
mysql client with properties set to
communicate through the console to the MySQL server using
Unicode.

To take advantage of this support manually, run
mysql within a console that uses a
compatible Unicode font and set the default character set to a
Unicode character set that is supported for communication with
the server:

Open a console window.

Go to the console window properties, select the font tab,
and choose Lucida Console or some other compatible Unicode
font. This is necessary because console windows start by
default using a DOS raster font that is inadequate for
Unicode.

With those changes, mysql will use the
Windows APIs to communicate with the console using UTF-16LE,
and communicate with the server using UTF-8. (The menu item
mentioned previously sets the font and character set as just
described.)

To avoid those steps each time you run
mysql, you can create a shortcut that
invokes mysql.exe. The shortcut should set
the console font to Lucida Console or some other compatible
Unicode font, and pass the
--default-character-set=utf8 (or
utf8mb4) option to
mysql.exe.

Alternatively, create a shortcut that only sets the console
font, and set the character set in the
[mysql] group of your
my.ini file:

[mysql]
default-character-set=utf8

4.5.1.6.3 Displaying Query Results Vertically

Some query results are much more readable when displayed
vertically, instead of in the usual horizontal table format.
Queries can be displayed vertically by terminating the query
with \G instead of a semicolon. For example, longer text
values that include newlines often are much easier to read
with vertical output:

4.5.1.6.4 Using Safe-Updates Mode (--safe-updates)

For beginners, a useful startup option is
--safe-updates (or
--i-am-a-dummy,
which has the same effect). Safe-updates mode is helpful for
cases when you might have issued an
UPDATE or
DELETE statement but forgotten
the WHERE clause indicating which rows to
modify. Normally, such statements update or delete all rows in
the table. With --safe-updates,
you can modify rows only by specifying the key values that
identify them, or a LIMIT clause, or both.
This helps prevent accidents. Safe-updates mode also restricts
SELECT statements that produce
(or are estimated to produce) very large result sets.

Setting sql_select_limit
to 1,000 causes the server to limit all
SELECT result sets to 1,000
rows unless the statement includes a
LIMIT clause.

Setting max_join_size to
1,000,000 causes multiple-table
SELECT statements to
produce an error if the server estimates it must examine
more than 1,000,000 row combinations.

To specify result set limits different from 1,000 and
1,000,000, you can override the defaults by using the
--select_limit and
--max_join_size options when you
invoke mysql:

mysql --safe-updates --select_limit=500 --max_join_size=10000

It is possible for UPDATE and
DELETE statements to produce an
error in safe-updates mode even with a key specified in the
WHERE clause, if the optimizer decides not
to use the index on the key column. For example, if key
comparisons require type conversion, the index may not be used
(see Section 8.3.1, “How MySQL Uses Indexes”). Suppose that an indexed
string column c1 is compared to a numeric
value using WHERE c1 = 2222. For such
comparisons, the string value is converted to a number and the
operands are compared numerically (see
Section 12.2, “Type Conversion in Expression Evaluation”), preventing use of the
index. If safe-updates mode is enabled, an error occurs.

4.5.1.6.5 Disabling mysql Auto-Reconnect

If the mysql client loses its connection to
the server while sending a statement, it immediately and
automatically tries to reconnect once to the server and send
the statement again. However, even if mysql
succeeds in reconnecting, your first connection has ended and
all your previous session objects and settings are lost:
temporary tables, the autocommit mode, and user-defined and
session variables. Also, any current transaction rolls back.
This behavior may be dangerous for you, as in the following
example where the server was shut down and restarted between
the first and second statements without you knowing it:

The @a user variable has been lost with the
connection, and after the reconnection it is undefined. If it
is important to have mysql terminate with
an error if the connection has been lost, you can start the
mysql client with the
--skip-reconnect
option.

Set a new password. This changes the password to
new_password for the account that
you use with mysqladmin for connecting to
the server. Thus, the next time you invoke
mysqladmin (or any other client program)
using the same account, you will need to specify the new
password.

Warning

Setting a password using mysqladmin
should be considered insecure. On
some systems, your password becomes visible to system
status programs such as ps that may be
invoked by other users to display command lines. MySQL
clients typically overwrite the command-line password
argument with zeros during their initialization sequence.
However, there is still a brief interval during which the
value is visible. Also, on some systems this overwriting
strategy is ineffective and the password remains visible
to ps. (SystemV Unix systems and
perhaps others are subject to this problem.)

If the new_password value
contains spaces or other characters that are special to your
command interpreter, you need to enclose it within quotation
marks. On Windows, be sure to use double quotation marks
rather than single quotation marks; single quotation marks
are not stripped from the password, but rather are
interpreted as part of the password. For example:

shell> mysqladmin password "my new password"

In MySQL 5.6, the new password can be omitted
following the password command. In this
case, mysqladmin prompts for the password
value, which enables you to avoid specifying the password on
the command line. Omitting the password value should be done
only if password is the final command on
the mysqladmin command line. Otherwise,
the next argument is taken as the password.

Caution

Do not use this command used if the server was started
with the
--skip-grant-tables option.
No password change will be applied. This is true even if
you precede the password command with
flush-privileges on the same command
line to re-enable the grant tables because the flush
operation occurs after you connect. However, you can use
mysqladmin flush-privileges to
re-enable the grant table and then use a separate
mysqladmin password command to change
the password.

ping

Check whether the server is available. The return status
from mysqladmin is 0 if the server is
running, 1 if it is not. This is 0 even in case of an error
such as Access denied, because this means
that the server is running but refused the connection, which
is different from the server not running.

The number of flush-*,
refresh, and reload
commands the server has executed.

Open tables

The number of tables that currently are open.

If you execute mysqladmin shutdown when
connecting to a local server using a Unix socket file,
mysqladmin waits until the server's process
ID file has been removed, to ensure that the server has stopped
properly.

mysqladmin supports the following options,
which can be specified on the command line or in the
[mysqladmin] and [client]
groups of an option file. For information about option files
used by MySQL programs, see Section 4.2.6, “Using Option Files”.

Read this option file after the global option file but (on
Unix) before the user option file. If the file does not
exist or is otherwise inaccessible, an error occurs.
file_name is interpreted relative
to the current directory if given as a relative path name
rather than a full path name.

Use only the given option file. If the file does not exist
or is otherwise inaccessible, an error occurs.
file_name is interpreted relative
to the current directory if given as a relative path name
rather than a full path name.

Read not only the usual option groups, but also groups with
the usual names and a suffix of
str. For example,
mysqladmin normally reads the
[client] and
[mysqladmin] groups. If the
--defaults-group-suffix=_other
option is given, mysqladmin also reads
the [client_other] and
[mysqladmin_other] groups.

The password to use when connecting to the server. If you
use the short option form (-p), you
cannot have a space between the option
and the password. If you omit the
password value following the
--password or
-p option on the command line,
mysqladmin prompts for one.

The connection protocol to use for connecting to the server.
It is useful when the other connection parameters normally
would cause a protocol to be used other than the one you
want. For details on the permissible values, see
Section 4.2.2, “Connecting to the MySQL Server”.

Do not send passwords to the server in old (pre-4.1) format.
This prevents connections except for servers that use the
newer password format. This option is enabled by default;
use
--skip-secure-auth
to disable it. This option was added in MySQL 5.6.17.

Execute commands repeatedly, sleeping for
delay seconds in between. The
--count option determines
the number of iterations. If
--count is not given,
mysqladmin executes commands indefinitely
until interrupted.

4.5.3 mysqlcheck — A Table Maintenance Program

Each table is locked and therefore unavailable to other sessions
while it is being processed, although for check operations, the
table is locked with a READ lock only (see
Section 13.3.5, “LOCK TABLES and UNLOCK TABLES Syntax”, for more information about
READ and WRITE locks).
Table maintenance operations can be time-consuming, particularly
for large tables. If you use the
--databases or
--all-databases option to
process all tables in one or more databases, an invocation of
mysqlcheck might take a long time. (This is
also true for mysql_upgrade because that
program invokes mysqlcheck to check all
tables and repair them if necessary.)

mysqlcheck is similar in function to
myisamchk, but works differently. The main
operational difference is that mysqlcheck
must be used when the mysqld server is
running, whereas myisamchk should be used
when it is not. The benefit of using
mysqlcheck is that you do not have to stop
the server to perform table maintenance.

The MyISAM storage engine supports all four
maintenance operations, so mysqlcheck can be
used to perform any of them on MyISAM tables.
Other storage engines do not necessarily support all operations.
In such cases, an error message is displayed. For example, if
test.t is a MEMORY table,
an attempt to check it produces this result:

It is best to make a backup of a table before performing a
table repair operation; under some circumstances the operation
might cause data loss. Possible causes include but are not
limited to file system errors.

If you do not name any tables following
db_name or if you use the
--databases or
--all-databases option,
entire databases are checked.

mysqlcheck has a special feature compared to
other client programs. The default behavior of checking tables
(--check) can be changed by
renaming the binary. If you want to have a tool that repairs
tables by default, you should just make a copy of
mysqlcheck named
mysqlrepair, or make a symbolic link to
mysqlcheck named
mysqlrepair. If you invoke
mysqlrepair, it repairs tables.

The names shown in the following table can be used to change
mysqlcheck default behavior.

mysqlcheck supports the following options,
which can be specified on the command line or in the
[mysqlcheck] and [client]
groups of an option file. For information about option files
used by MySQL programs, see Section 4.2.6, “Using Option Files”.

Check all tables in all databases. This is the same as using
the --databases option
and naming all the databases on the command line, except
that the INFORMATION_SCHEMA and
performance_schema databases are not
checked. They can be checked by explicitly naming them with
the --databases option.

Process all tables in the named databases. Normally,
mysqlcheck treats the first name argument
on the command line as a database name and any following
names as table names. With this option, it treats all name
arguments as database names.

Read this option file after the global option file but (on
Unix) before the user option file. If the file does not
exist or is otherwise inaccessible, an error occurs.
file_name is interpreted relative
to the current directory if given as a relative path name
rather than a full path name.

Use only the given option file. If the file does not exist
or is otherwise inaccessible, an error occurs.
file_name is interpreted relative
to the current directory if given as a relative path name
rather than a full path name.

Read not only the usual option groups, but also groups with
the usual names and a suffix of
str. For example,
mysqlcheck normally reads the
[client] and
[mysqlcheck] groups. If the
--defaults-group-suffix=_other
option is given, mysqlcheck also reads
the [client_other] and
[mysqlcheck_other] groups.

The password to use when connecting to the server. If you
use the short option form (-p), you
cannot have a space between the option
and the password. If you omit the
password value following the
--password or
-p option on the command line,
mysqlcheck prompts for one.

The connection protocol to use for connecting to the server.
It is useful when the other connection parameters normally
would cause a protocol to be used other than the one you
want. For details on the permissible values, see
Section 4.2.2, “Connecting to the MySQL Server”.

Do not send passwords to the server in old (pre-4.1) format.
This prevents connections except for servers that use the
newer password format. This option is enabled by default;
use
--skip-secure-auth
to disable it. This option was added in MySQL 5.6.17.

4.5.4 mysqldump — A Database Backup Program

The mysqldump client utility performs
logical backups,
producing a set of SQL statements that can be executed to
reproduce the original database object definitions and table
data. It dumps one or more MySQL databases for backup or
transfer to another SQL server. The mysqldump
command can also generate output in CSV, other delimited text,
or XML format.

To reload a dump file, you must have the privileges required to
execute the statements that it contains, such as the appropriate
CREATE privileges for objects created by
those statements.

mysqldump output can include
ALTER DATABASE statements that
change the database collation. These may be used when dumping
stored programs to preserve their character encodings. To reload
a dump file containing such statements, the
ALTER privilege for the affected database is
required.

Note

A dump made using PowerShell on Windows with output
redirection creates a file that has UTF-16 encoding:

shell> mysqldump [options] > dump.sql

However, UTF-16 is not permitted as a connection character set
(see
Impermissible Client Character Sets),
so the dump file will not load correctly. To work around this
issue, use the --result-file option, which
creates the output in ASCII format:

shell> mysqldump [options] --result-file=dump.sql

Performance and Scalability Considerations

mysqldump advantages include the convenience
and flexibility of viewing or even editing the output before
restoring. You can clone databases for development and DBA work,
or produce slight variations of an existing database for
testing. It is not intended as a fast or scalable solution for
backing up substantial amounts of data. With large data sizes,
even if the backup step takes a reasonable time, restoring the
data can be very slow because replaying the SQL statements
involves disk I/O for insertion, index creation, and so on.

For large-scale backup and restore, a
physical backup is more
appropriate, to copy the data files in their original format
that can be restored quickly:

If your tables are primarily InnoDB
tables, or if you have a mix of InnoDB
and MyISAM tables, consider using the
mysqlbackup command of the MySQL
Enterprise Backup product. (Available as part of the
Enterprise subscription.) It provides the best performance
for InnoDB backups with minimal
disruption; it can also back up tables from
MyISAM and other storage engines; and it
provides a number of convenient options to accommodate
different backup scenarios. See
Section 25.2, “MySQL Enterprise Backup Overview”.

mysqldump can retrieve and dump table
contents row by row, or it can retrieve the entire content from
a table and buffer it in memory before dumping it. Buffering in
memory can be a problem if you are dumping large tables. To dump
tables row by row, use the
--quick option (or
--opt, which enables
--quick). The
--opt option (and hence
--quick) is enabled by
default, so to enable memory buffering, use
--skip-quick.

Option Syntax - Alphabetical Summary

mysqldump supports the following options,
which can be specified on the command line or in the
[mysqldump] and [client]
groups of an option file. For information about option files
used by MySQL programs, see Section 4.2.6, “Using Option Files”.

The password to use when connecting to the server. If you
use the short option form (-p), you
cannot have a space between the option
and the password. If you omit the
password value following the
--password or -p option on
the command line, mysqldump prompts for
one.

The connection protocol to use for connecting to the server.
It is useful when the other connection parameters normally
would cause a protocol to be used other than the one you
want. For details on the permissible values, see
Section 4.2.2, “Connecting to the MySQL Server”.

Do not send passwords to the server in old (pre-4.1) format.
This prevents connections except for servers that use the
newer password format. This option is enabled by default;
use
--skip-secure-auth
to disable it. This option was added in MySQL 5.6.17.

Option-File Options

Read this option file after the global option file but (on
Unix) before the user option file. If the file does not
exist or is otherwise inaccessible, an error occurs.
file_name is interpreted relative
to the current directory if given as a relative path name
rather than a full path name.

Use only the given option file. If the file does not exist
or is otherwise inaccessible, an error occurs.
file_name is interpreted relative
to the current directory if given as a relative path name
rather than a full path name.

Read not only the usual option groups, but also groups with
the usual names and a suffix of
str. For example,
mysqldump normally reads the
[client] and
[mysqldump] groups. If the
--defaults-group-suffix=_other
option is given, mysqldump also reads the
[client_other] and
[mysqldump_other] groups.

DDL Options

Usage scenarios for mysqldump include setting
up an entire new MySQL instance (including database tables), and
replacing data inside an existing instance with existing
databases and tables. The following options let you specify
which things to tear down and set up when restoring a dump, by
encoding various DDL statements within the dump file.

Adds to a table dump all SQL statements needed to create any
tablespaces used by an NDB
table. This information is not otherwise included in the
output from mysqldump. This option is
currently relevant only to NDB Cluster tables.

Debug Options

The following options print debugging information, encode
debugging information in the dump file, or let the dump
operation proceed regardless of potential problems.

--allow-keywords

Permit creation of column names that are keywords. This
works by prefixing each column name with the table name.

--comments, -i

Write additional information in the dump file such as
program version, server version, and host. This option is
enabled by default. To suppress this additional information,
use --skip-comments.

--debug[=debug_options],
-#
[debug_options]

Write a debugging log. A typical
debug_options string is
d:t:o,file_name.
The default value is
d:t:o,/tmp/mysqldump.trace.

--debug-check

Print some debugging information when the program exits.

--debug-info

Print debugging information and memory and CPU usage
statistics when the program exits.

--dump-date

If the --comments option
is given, mysqldump produces a comment at
the end of the dump of the following form:

-- Dump completed on DATE

However, the date causes dump files taken at different times
to appear to be different, even if the data are otherwise
identical. --dump-date and
--skip-dump-date
control whether the date is added to the comment. The
default is --dump-date
(include the date in the comment).
--skip-dump-date
suppresses date printing.

--force, -f

Continue even if an SQL error occurs during a table dump.

One use for this option is to cause
mysqldump to continue executing even when
it encounters a view that has become invalid because the
definition refers to a table that has been dropped. Without
--force, mysqldump exits
with an error message. With --force,
mysqldump prints the error message, but
it also writes an SQL comment containing the view definition
to the dump output and continues executing.

--log-error=file_name

Log warnings and errors by appending them to the named file.
The default is to do no logging.

Replication Options

The mysqldump command is frequently used to
create an empty instance, or an instance including data, on a
slave server in a replication configuration. The following
options apply to dumping and restoring data on replication
master and slave servers.

On a master replication server, delete the binary logs by
sending a PURGE BINARY LOGS
statement to the server after performing the dump operation.
This option automatically enables
--master-data.

--dump-slave[=value]

This option is similar to
--master-data except that
it is used to dump a replication slave server to produce a
dump file that can be used to set up another server as a
slave that has the same master as the dumped server. It
causes the dump output to include a
CHANGE MASTER TO statement
that indicates the binary log coordinates (file name and
position) of the dumped slave's master. These are the master
server coordinates from which the slave should start
replicating.

--dump-slave causes the coordinates from
the master to be used rather than those of the dumped
server, as is done by the
--master-data option. In
addition, specfiying this option causes the
--master-data option to be overridden, if
used, and effectively ignored.

The option value is handled the same way as for
--master-data (setting no
value or 1 causes a CHANGE MASTER TO
statement to be written to the dump, setting 2 causes the
statement to be written but encased in SQL comments) and has
the same effect as --master-data in terms
of enabling or disabling other options and in how locking is
handled.

This option causes mysqldump to stop the
slave SQL thread before the dump and restart it again after.

For the CHANGE MASTER TO
statement in a slave dump produced with the
--dump-slave option, add
MASTER_HOST and
MASTER_PORT options for the host name and
TCP/IP port number of the slave's master.

--master-data[=value]

Use this option to dump a master replication server to
produce a dump file that can be used to set up another
server as a slave of the master. It causes the dump output
to include a CHANGE MASTER TO
statement that indicates the binary log coordinates (file
name and position) of the dumped server. These are the
master server coordinates from which the slave should start
replicating after you load the dump file into the slave.

If the option value is 2, the CHANGE
MASTER TO statement is written as an SQL comment,
and thus is informative only; it has no effect when the dump
file is reloaded. If the option value is 1, the statement is
not written as a comment and takes effect when the dump file
is reloaded. If no option value is specified, the default
value is 1.

This option requires the
RELOAD privilege and the
binary log must be enabled.

The --master-data option automatically
turns off --lock-tables.
It also turns on
--lock-all-tables, unless
--single-transaction also
is specified, in which case, a global read lock is acquired
only for a short time at the beginning of the dump (see the
description for
--single-transaction). In
all cases, any action on logs happens at the exact moment of
the dump.

It is also possible to set up a slave by dumping an existing
slave of the master, using the
--dump-slave option, which
overrides --master-data and causes it to be
ignored if both options are used.

This option enables control over global transaction ID
(GTID) information written to the dump file, by indicating
whether to add a
SET
@@GLOBAL.gtid_purged statement to the output. This
option may also cause a statement to be written to the
output that disables binary logging while the dump file is
being reloaded.

The following table shows the permitted option values. The
default value is AUTO.

Value

Meaning

OFF

Add no SET statement to the output.

ON

Add a SET statement to the output. An error occurs if
GTIDs are not enabled on the server.

AUTO

Add a SET statement to the output if GTIDs are
enabled on the server.

The --set-gtid-purged option has the
following effect on binary logging when the dump file is
reloaded:

--set-gtid-purged=OFF: SET
@@SESSION.SQL_LOG_BIN=0; is not added to the
output.

--set-gtid-purged=ON: SET
@@SESSION.SQL_LOG_BIN=0; is added to the
output.

--set-gtid-purged=AUTO: SET
@@SESSION.SQL_LOG_BIN=0; is added to the
output if GTIDs are enabled on the server you are
backing up (that is, if AUTO
evaluates to ON).

Note

It is not recommended to load a dump file when GTIDs are
enabled on the server
(gtid_mode=ON),
if your dump file includes system tables.
mysqldump issues DML instructions for
the system tables which use the non-transactional MyISAM
storage engine, and this combination is not permitted when
GTIDs are enabled. Also be aware that loading a dump file
from a server with GTIDs enabled, into another server with
GTIDs enabled, causes different transaction identifiers to
be generated.

This option was added in MySQL 5.6.9.

Format Options

The following options specify how to represent the entire dump
file or certain kinds of data in the dump file. They also
control whether certain optional information is written to the
dump file.

Produce output that is more compatible with other database
systems or with older MySQL servers. The value of
name can be
ansi, mysql323,
mysql40, postgresql,
oracle, mssql,
db2, maxdb,
no_key_options,
no_table_options, or
no_field_options. To use several values,
separate them by commas. These values have the same meaning
as the corresponding options for setting the server SQL
mode. See Section 5.1.10, “Server SQL Modes”.

This option does not guarantee compatibility with other
servers. It only enables those SQL mode values that are
currently available for making dump output more compatible.
For example, --compatible=oracle does not
map data types to Oracle types or use Oracle comment syntax.

Quote identifiers (such as database, table, and column
names) within ` characters. If the
ANSI_QUOTES SQL mode is
enabled, identifiers are quoted within "
characters. This option is enabled by default. It can be
disabled with --skip-quote-names, but this
option should be given after any option such as
--compatible that may
enable --quote-names.

--result-file=file_name,
-r file_name

Direct output to the named file. The result file is created
and its previous contents overwritten, even if an error
occurs while generating the dump.

This option should be used on Windows to prevent newline
\n characters from being converted to
\r\n carriage return/newline sequences.

--tab=dir_name,
-T dir_name

Produce tab-separated text-format data files. For each
dumped table, mysqldump creates a
tbl_name.sql
file that contains the CREATE
TABLE statement that creates the table, and the
server writes a
tbl_name.txt
file that contains its data. The option value is the
directory in which to write the files.

Note

This option should be used only when
mysqldump is run on the same machine as
the mysqld server. Because the server
creates *.txt files in the directory
that you specify, the directory must be writable by the
server and the MySQL account that you use must have the
FILE privilege. Because
mysqldump creates
*.sql in the same directory, it must
be writable by your system login account.

By default, the .txt data files are
formatted using tab characters between column values and a
newline at the end of each line. The format can be specified
explicitly using the
--fields-xxx and
--lines-terminated-by
options.

This option enables TIMESTAMP
columns to be dumped and reloaded between servers in
different time zones. mysqldump sets its
connection time zone to UTC and adds SET
TIME_ZONE='+00:00' to the dump file. Without this
option, TIMESTAMP columns are
dumped and reloaded in the time zones local to the source
and destination servers, which can cause the values to
change if the servers are in different time zones.
--tz-utc also protects against changes due
to daylight saving time. --tz-utc is
enabled by default. To disable it, use
--skip-tz-utc.

--xml, -X

Write dump output as well-formed XML.

NULL,
'NULL', and Empty Values: For
a column named column_name, the
NULL value, an empty string, and the
string value 'NULL' are distinguished
from one another in the output generated by this option as
follows.

Prior to MySQL 5.6.5, this option prevented the
--routines option from
working correctly—that is, no stored routines,
triggers, or events could be dumped in XML format. (Bug
#11760384, Bug #52792)

Filtering Options

The following options control which kinds of schema objects are
written to the dump file: by category, such as triggers or
events; by name, for example, choosing which databases and
tables to dump; or even filtering rows from the table data using
a WHERE clause.

--all-databases, -A

Dump all tables in all databases. This is the same as using
the --databases option and
naming all the databases on the command line.

Dump several databases. Normally,
mysqldump treats the first name argument
on the command line as a database name and following names
as table names. With this option, it treats all name
arguments as database names. CREATE
DATABASE and USE
statements are included in the output before each new
database.

This option may be used to dump the
INFORMATION_SCHEMA and
performance_schema databases, which
normally are not dumped even with the
--all-databases option.
(Also use the
--skip-lock-tables
option.)

--events, -E

Include Event Scheduler events for the dumped databases in
the output. This option requires the
EVENT privileges for those
databases.

The output generated by using --events
contains CREATE EVENT
statements to create the events. However, these statements
do not include attributes such as the event creation and
modification timestamps, so when the events are reloaded,
they are created with timestamps equal to the reload time.

If you require events to be created with their original
timestamp attributes, do not use --events.
Instead, dump and reload the contents of the
mysql.event table directly, using a MySQL
account that has appropriate privileges for the
mysql database.

--ignore-table=db_name.tbl_name

Do not dump the given table, which must be specified using
both the database and table names. To ignore multiple
tables, use this option multiple times. This option also can
be used to ignore views.

--no-data, -d

Do not write any table row information (that is, do not dump
table contents). This is useful if you want to dump only the
CREATE TABLE statement for
the table (for example, to create an empty copy of the table
by loading the dump file).

--routines, -R

Include stored routines (procedures and functions) for the
dumped databases in the output. This option requires the
SELECT privilege for the
mysql.proc table.

The output generated by using --routines
contains CREATE PROCEDURE and
CREATE FUNCTION statements to
create the routines. However, these statements do not
include attributes such as the routine creation and
modification timestamps, so when the routines are reloaded,
they are created with timestamps equal to the reload time.

If you require routines to be created with their original
timestamp attributes, do not use
--routines. Instead, dump and reload the
contents of the mysql.proc table
directly, using a MySQL account that has appropriate
privileges for the mysql database.

Prior to MySQL 5.6.5, this option had no effect when used
together with the --xml
option. (Bug #11760384, Bug #52792)

--tables

Override the --databases
or -B option. mysqldump
regards all name arguments following the option as table
names.

--triggers

Include triggers for each dumped table in the output. This
option is enabled by default; disable it with
--skip-triggers.

To be able to dump a table's triggers, you must have the
TRIGGER privilege for the
table.

--where='where_condition',
-w
'where_condition'

Dump only rows selected by the given
WHERE condition. Quotes around the
condition are mandatory if it contains spaces or other
characters that are special to your command interpreter.

Examples:

--where="user='jimf'"
-w"userid>1"
-w"userid<1"

Performance Options

The following options are the most relevant for the performance
particularly of the restore operations. For large data sets,
restore operation (processing the INSERT
statements in the dump file) is the most time-consuming part.
When it is urgent to restore data quickly, plan and test the
performance of this stage in advance. For restore times measured
in hours, you might prefer an alternative backup and restore
solution, such as MySQL
Enterprise Backup for InnoDB-only and
mixed-use databases, or mysqlhotcopy for
MyISAM-only databases.

For those nontransactional tables that support the
INSERT DELAYED syntax, use
that statement rather than regular
INSERT statements.

As of MySQL 5.6.6, DELAYED inserts are
deprecated, so this option will be removed in a future
release.

--disable-keys, -K

For each table, surround the
INSERT statements with
/*!40000 ALTER TABLE
tbl_name DISABLE KEYS
*/; and /*!40000 ALTER TABLE
tbl_name ENABLE KEYS
*/; statements. This makes loading the dump file
faster because the indexes are created after all rows are
inserted. This option is effective only for nonunique
indexes of MyISAM tables.

--extended-insert, -e

Write INSERT statements using
multiple-row syntax that includes several
VALUES lists. This results in a smaller
dump file and speeds up inserts when the file is reloaded.

Because the --opt option is enabled by
default, you only specify its converse, the
--skip-opt to turn off
several default settings. See the discussion of
mysqldump
option groups for information about selectively
enabling or disabling a subset of the options affected by
--opt.

--quick, -q

This option is useful for dumping large tables. It forces
mysqldump to retrieve rows for a table
from the server a row at a time rather than retrieving the
entire row set and buffering it in memory before writing it
out.

Add a FLUSH PRIVILEGES
statement to the dump output after dumping the
mysql database. This option should be
used any time the dump contains the mysql
database and any other database that depends on the data in
the mysql database for proper
restoration.

--lock-all-tables, -x

Lock all tables across all databases. This is achieved by
acquiring a global read lock for the duration of the whole
dump. This option automatically turns off
--single-transaction and
--lock-tables.

--lock-tables, -l

For each dumped database, lock all tables to be dumped
before dumping them. The tables are locked with
READ LOCAL to permit concurrent inserts
in the case of MyISAM tables. For
transactional tables such as InnoDB,
--single-transaction is a
much better option than --lock-tables
because it does not need to lock the tables at all.

Because --lock-tables
locks tables for each database separately, this option does
not guarantee that the tables in the dump file are logically
consistent between databases. Tables in different databases
may be dumped in completely different states.

Some options, such as
--opt, automatically
enable --lock-tables. If you want to
override this, use --skip-lock-tables at
the end of the option list.

--no-autocommit

Enclose the INSERT statements
for each dumped table within SET autocommit =
0 and COMMIT
statements.

--order-by-primary

Dump each table's rows sorted by its primary key, or by its
first unique index, if such an index exists. This is useful
when dumping a MyISAM table to be loaded
into an InnoDB table, but makes the dump
operation take considerably longer.

On Windows, the shared-memory name to use, for connections
made using shared memory to a local server. The default
value is MYSQL. The shared-memory name is
case-sensitive.

The server must be started with the
--shared-memory option to
enable shared-memory connections.

--single-transaction

This option sets the transaction isolation mode to
REPEATABLE READ and sends
a START
TRANSACTION SQL statement to the server before
dumping data. It is useful only with transactional tables
such as InnoDB, because then it dumps the
consistent state of the database at the time when
START
TRANSACTION was issued without blocking any
applications.

When using this option, you should keep in mind that only
InnoDB tables are dumped in a consistent
state. For example, any MyISAM or
MEMORY tables dumped while using this
option may still change state.

While a
--single-transaction dump
is in process, to ensure a valid dump file (correct table
contents and binary log coordinates), no other connection
should use the following statements:
ALTER TABLE,
CREATE TABLE,
DROP TABLE,
RENAME TABLE,
TRUNCATE TABLE. A consistent
read is not isolated from those statements, so use of them
on a table to be dumped can cause the
SELECT that is performed by
mysqldump to retrieve the table contents
to obtain incorrect contents or fail.

The --single-transaction option and the
--lock-tables option are
mutually exclusive because LOCK
TABLES causes any pending transactions to be
committed implicitly.

To dump large tables, combine the
--single-transaction option with the
--quick option.

Option Groups

The --opt option turns on
several settings that work together to perform a fast dump
operation. All of these settings are on by default, because
--opt is on by default. Thus you rarely if
ever specify --opt. Instead, you can turn
these settings off as a group by specifying
--skip-opt, the optionally re-enable
certain settings by specifying the associated options later
on the command line.

The --compact option turns
off several settings that control whether optional
statements and comments appear in the output. Again, you can
follow this option with other options that re-enable certain
settings, or turn all the settings on by using the
--skip-compact form.

When you selectively enable or disable the effect of a group
option, order is important because options are processed first
to last. For example,
--disable-keys--lock-tables--skip-opt would not have the
intended effect; it is the same as
--skip-opt by itself.

Examples

To make a backup of an entire database:

shell> mysqldump db_name > backup-file.sql

To load the dump file back into the server:

shell> mysql db_name < backup-file.sql

Another way to reload the dump file:

shell> mysql -e "source /path-to-backup/backup-file.sql" db_name

mysqldump is also very useful for populating
databases by copying data from one MySQL server to another:

This backup acquires a global read lock on all tables (using
FLUSH TABLES WITH READ LOCK) at
the beginning of the dump. As soon as this lock has been
acquired, the binary log coordinates are read and the lock is
released. If long updating statements are running when the
FLUSH statement is issued, the
MySQL server may get stalled until those statements finish.
After that, the dump becomes lock free and does not disturb
reads and writes on the tables. If the update statements that
the MySQL server receives are short (in terms of execution
time), the initial lock period should not be noticeable, even
with many updates.

For point-in-time recovery (also known as
“roll-forward,” when you need to restore an old
backup and replay the changes that happened since that backup),
it is often useful to rotate the binary log (see
Section 5.4.4, “The Binary Log”) or at least know the binary log
coordinates to which the dump corresponds:

The --master-data and
--single-transaction options
can be used simultaneously, which provides a convenient way to
make an online backup suitable for use prior to point-in-time
recovery if tables are stored using the
InnoDB storage engine.

Restrictions

mysqldump does not dump the
INFORMATION_SCHEMA or
performance_schema database by default. To
dump either of these, name it explicitly on the command line.
You can also name it with the
--databases option. Also, use
the
--skip-lock-tables
option.

Before MySQL 5.6.6, mysqldump does not dump
the general_log or
slow_query_log tables for dumps of the
mysql database. As of 5.6.6, the dump
includes statements to recreate those tables so that they are
not missing after reloading the dump file. Log table contents
are not dumped.

For each text file named on the command line,
mysqlimport strips any extension from the
file name and uses the result to determine the name of the table
into which to import the file's contents. For example, files
named patient.txt,
patient.text, and
patient all would be imported into a table
named patient.

mysqlimport supports the following options,
which can be specified on the command line or in the
[mysqlimport] and [client]
groups of an option file. For information about option files
used by MySQL programs, see Section 4.2.6, “Using Option Files”.

Read this option file after the global option file but (on
Unix) before the user option file. If the file does not
exist or is otherwise inaccessible, an error occurs.
file_name is interpreted relative
to the current directory if given as a relative path name
rather than a full path name.

Use only the given option file. If the file does not exist
or is otherwise inaccessible, an error occurs.
file_name is interpreted relative
to the current directory if given as a relative path name
rather than a full path name.

Read not only the usual option groups, but also groups with
the usual names and a suffix of
str. For example,
mysqlimport normally reads the
[client] and
[mysqlimport] groups. If the
--defaults-group-suffix=_other
option is given, mysqlimport also reads
the [client_other] and
[mysqlimport_other] groups.

This option has the same meaning as the corresponding clause
for LOAD DATA. For example,
to import Windows files that have lines terminated with
carriage return/linefeed pairs, use
--lines-terminated-by="\r\n".
(You might have to double the backslashes, depending on the
escaping conventions of your command interpreter.) See
Section 13.2.6, “LOAD DATA Syntax”.

The password to use when connecting to the server. If you
use the short option form (-p), you
cannot have a space between the option
and the password. If you omit the
password value following the
--password or
-p option on the command line,
mysqlimport prompts for one.

The connection protocol to use for connecting to the server.
It is useful when the other connection parameters normally
would cause a protocol to be used other than the one you
want. For details on the permissible values, see
Section 4.2.2, “Connecting to the MySQL Server”.

The --replace and
--ignore options control
handling of input rows that duplicate existing rows on
unique key values. If you specify
--replace, new rows
replace existing rows that have the same unique key value.
If you specify --ignore,
input rows that duplicate an existing row on a unique key
value are skipped. If you do not specify either option, an
error occurs when a duplicate key value is found, and the
rest of the text file is ignored.

Do not send passwords to the server in old (pre-4.1) format.
This prevents connections except for servers that use the
newer password format. This option is enabled by default;
use
--skip-secure-auth
to disable it. This option was added in MySQL 5.6.17.

4.5.6 mysqlshow — Display Database, Table, and Column Information

The mysqlshow client can be used to quickly
see which databases exist, their tables, or a table's columns or
indexes.

mysqlshow provides a command-line interface
to several SQL SHOW statements.
See Section 13.7.5, “SHOW Syntax”. The same information can be obtained
by using those statements directly. For example, you can issue
them from the mysql client program.

If no column is given, all matching columns and column types
in the table are shown.

The output displays only the names of those databases, tables,
or columns for which you have some privileges.

If the last argument contains shell or SQL wildcard characters
(*, ?,
%, or _), only those names
that are matched by the wildcard are shown. If a database name
contains any underscores, those should be escaped with a
backslash (some Unix shells require two) to get a list of the
proper tables or columns. * and
? characters are converted into SQL
% and _ wildcard
characters. This might cause some confusion when you try to
display the columns for a table with a _ in
the name, because in this case, mysqlshow
shows you only the table names that match the pattern. This is
easily fixed by adding an extra % last on the
command line as a separate argument.

mysqlshow supports the following options,
which can be specified on the command line or in the
[mysqlshow] and [client]
groups of an option file. For information about option files
used by MySQL programs, see Section 4.2.6, “Using Option Files”.

Read this option file after the global option file but (on
Unix) before the user option file. If the file does not
exist or is otherwise inaccessible, an error occurs.
file_name is interpreted relative
to the current directory if given as a relative path name
rather than a full path name.

Use only the given option file. If the file does not exist
or is otherwise inaccessible, an error occurs.
file_name is interpreted relative
to the current directory if given as a relative path name
rather than a full path name.

Read not only the usual option groups, but also groups with
the usual names and a suffix of
str. For example,
mysqlshow normally reads the
[client] and
[mysqlshow] groups. If the
--defaults-group-suffix=_other
option is given, mysqlshow also reads the
[client_other] and
[mysqlshow_other] groups.

The password to use when connecting to the server. If you
use the short option form (-p), you
cannot have a space between the option
and the password. If you omit the
password value following the
--password or
-p option on the command line,
mysqlshow prompts for one.

The connection protocol to use for connecting to the server.
It is useful when the other connection parameters normally
would cause a protocol to be used other than the one you
want. For details on the permissible values, see
Section 4.2.2, “Connecting to the MySQL Server”.

Do not send passwords to the server in old (pre-4.1) format.
This prevents connections except for servers that use the
newer password format. This option is enabled by default;
use
--skip-secure-auth
to disable it. This option was added in MySQL 5.6.17.

Some options such as --create
or --query enable you to
specify a string containing an SQL statement or a file
containing statements. If you specify a file, by default it must
contain one statement per line. (That is, the implicit statement
delimiter is the newline character.) Use the
--delimiter option to specify
a different delimiter, which enables you to specify statements
that span multiple lines or place multiple statements on a
single line. You cannot include comments in a file;
mysqlslap does not understand them.

Let mysqlslap build the query SQL statement
with a table of two INT columns
and three VARCHAR columns. Use
five clients querying 20 times each. Do not create the table or
insert the data (that is, use the previous test's schema and
data):

Tell the program to load the create, insert, and query SQL
statements from the specified files, where the
create.sql file has multiple table creation
statements delimited by ';' and multiple
insert statements delimited by ';'. The
--query file will have multiple queries
delimited by ';'. Run all the load
statements, then run all the queries in the query file with five
clients (five times each):

mysqlslap supports the following options,
which can be specified on the command line or in the
[mysqlslap] and [client]
groups of an option file. For information about option files
used by MySQL programs, see Section 4.2.6, “Using Option Files”.

How many different queries to generate for automatic tests.
For example, if you run a key test that
performs 1000 selects, you can use this option with a value
of 1000 to run 1000 unique queries, or with a value of 50 to
perform 50 different selects. The default is 10.

Read this option file after the global option file but (on
Unix) before the user option file. If the file does not
exist or is otherwise inaccessible, an error occurs.
file_name is interpreted relative
to the current directory if given as a relative path name
rather than a full path name.

Use only the given option file. If the file does not exist
or is otherwise inaccessible, an error occurs.
file_name is interpreted relative
to the current directory if given as a relative path name
rather than a full path name.

Read not only the usual option groups, but also groups with
the usual names and a suffix of
str. For example,
mysqlslap normally reads the
[client] and
[mysqlslap] groups. If the
--defaults-group-suffix=_other
option is given, mysqlslap also reads the
[client_other] and
[mysqlslap_other] groups.

Limit each client to approximately this many queries. Query
counting takes into account the statement delimiter. For
example, if you invoke mysqlslap as
follows, the ; delimiter is recognized so
that each instance of the query string counts as two
queries. As a result, 5 rows (not 10) are inserted.

The password to use when connecting to the server. If you
use the short option form (-p), you
cannot have a space between the option
and the password. If you omit the
password value following the
--password or
-p option on the command line,
mysqlslap prompts for one.

The connection protocol to use for connecting to the server.
It is useful when the other connection parameters normally
would cause a protocol to be used other than the one you
want. For details on the permissible values, see
Section 4.2.2, “Connecting to the MySQL Server”.

Do not send passwords to the server in old (pre-4.1) format.
This prevents connections except for servers that use the
newer password format. This option is enabled by default;
use
--skip-secure-auth
to disable it. This option was added in MySQL 5.6.17.

4.6.1 innochecksum — Offline InnoDB File Checksum Utility

innochecksum prints checksums for
InnoDB files. This tool reads an
InnoDB tablespace file, calculates the
checksum for each page, compares the calculated checksum to the
stored checksum, and reports mismatches, which indicate damaged
pages. It was originally developed to speed up verifying the
integrity of tablespace files after power outages but can also
be used after file copies. Because checksum mismatches cause
InnoDB to deliberately shut down a running
server, it may be preferable to use this tool rather than
waiting for an in-production server to encounter the damaged
pages. As of MySQL 5.6.16, innochecksum
supports files greater than 2GB in size. Previously,
innochecksum only supported files up to 2GB
in size.

innochecksum does not support tablespaces
that contain compressed pages.

innochecksum cannot be used on tablespace
files that the server already has open. For such files, you
should use CHECK TABLE to check
tables within the tablespace.

If checksum mismatches are found, you would normally restore the
tablespace from backup or start the server and attempt to use
mysqldump to make a backup of the tables
within the tablespace.

innochecksum supports the following options.
For options that refer to page numbers, the numbers are
zero-based.

-c

Print a count of the number of pages in the file.

-d

Debug mode; prints checksums for each page.

-e num

End at this page number.

-p num

Check only this page number.

-s num

Start at this page number.

-v

Verbose mode; print a progress indicator every five seconds.

4.6.2 myisam_ftdump — Display Full-Text Index information

myisam_ftdump displays information about
FULLTEXT indexes in MyISAM
tables. It reads the MyISAM index file
directly, so it must be run on the server host where the table
is located. Before using myisam_ftdump, be
sure to issue a FLUSH TABLES statement first
if the server is running.

myisam_ftdump scans and dumps the entire
index, which is not particularly fast. On the other hand, the
distribution of words changes infrequently, so it need not be
run often.

The tbl_name argument should be the
name of a MyISAM table. You can also specify
a table by naming its index file (the file with the
.MYI suffix). If you do not invoke
myisam_ftdump in the directory where the
table files are located, the table or index file name must be
preceded by the path name to the table's database directory.
Index numbers begin with 0.

Example: Suppose that the test database
contains a table named mytexttable that has
the following definition:

The index on id is index 0 and the
FULLTEXT index on txt is
index 1. If your working directory is the
test database directory, invoke
myisam_ftdump as follows:

shell> myisam_ftdump mytexttable 1

If the path name to the test database
directory is /usr/local/mysql/data/test,
you can also specify the table name argument using that path
name. This is useful if you do not invoke
myisam_ftdump in the database directory:

shell> myisam_ftdump /usr/local/mysql/data/test/mytexttable 1

You can use myisam_ftdump to generate a list
of index entries in order of frequency of occurrence like this
on Unix-like systems:

The myisamchk utility gets information about
your database tables or checks, repairs, or optimizes them.
myisamchk works with
MyISAM tables (tables that have
.MYD and .MYI files
for storing data and indexes).

It is best to make a backup of a table before performing a
table repair operation; under some circumstances the operation
might cause data loss. Possible causes include but are not
limited to file system errors.

The options specify what you want
myisamchk to do. They are described in the
following sections. You can also get a list of options by
invoking myisamchk --help.

With no options, myisamchk simply checks your
table as the default operation. To get more information or to
tell myisamchk to take corrective action,
specify options as described in the following discussion.

tbl_name is the database table you
want to check or repair. If you run myisamchk
somewhere other than in the database directory, you must specify
the path to the database directory, because
myisamchk has no idea where the database is
located. In fact, myisamchk does not actually
care whether the files you are working on are located in a
database directory. You can copy the files that correspond to a
database table into some other location and perform recovery
operations on them there.

You can name several tables on the myisamchk
command line if you wish. You can also specify a table by naming
its index file (the file with the .MYI
suffix). This enables you to specify all tables in a directory
by using the pattern *.MYI. For example, if
you are in a database directory, you can check all the
MyISAM tables in that directory like this:

shell> myisamchk *.MYI

If you are not in the database directory, you can check all the
tables there by specifying the path to the directory:

shell> myisamchk /path/to/database_dir/*.MYI

You can even check all tables in all databases by specifying a
wildcard with the path to the MySQL data directory:

shell> myisamchk /path/to/datadir/*/*.MYI

The recommended way to quickly check all
MyISAM tables is:

shell> myisamchk --silent --fast /path/to/datadir/*/*.MYI

If you want to check all MyISAM tables and
repair any that are corrupted, you can use the following
command:

You must ensure that no other program is using the
tables while you are running
myisamchk. The most effective
means of doing so is to shut down the MySQL server while
running myisamchk, or to lock all tables
that myisamchk is being used on.

Otherwise, when you run myisamchk, it may
display the following error message:

warning: clients are using or haven't closed the table properly

This means that you are trying to check a table that has been
updated by another program (such as the
mysqld server) that hasn't yet closed the
file or that has died without closing the file properly, which
can sometimes lead to the corruption of one or more
MyISAM tables.

If mysqld is running, you must force it to
flush any table modifications that are still buffered in
memory by using FLUSH TABLES.
You should then ensure that no one is using the tables while
you are running myisamchk

myisamchk supports the following options,
which can be specified on the command line or in the
[myisamchk] group of an option file. For
information about option files used by MySQL programs, see
Section 4.2.6, “Using Option Files”.

4.6.3.1 myisamchk General Options

The options described in this section can be used for any type
of table maintenance operation performed by
myisamchk. The sections following this one
describe options that pertain only to specific operations, such
as table checking or repairing.

Read this option file after the global option file but (on
Unix) before the user option file. If the file does not
exist or is otherwise inaccessible, an error occurs.
file_name is interpreted relative
to the current directory if given as a relative path name
rather than a full path name.

Use only the given option file. If the file does not exist
or is otherwise inaccessible, an error occurs.
file_name is interpreted relative
to the current directory if given as a relative path name
rather than a full path name.

Read not only the usual option groups, but also groups with
the usual names and a suffix of
str. For example,
myisamchk normally reads the
[myisamchk] group. If the
--defaults-group-suffix=_other
option is given, myisamchk also reads the
[myisamchk_other] group.

Instead of terminating with an error if the table is locked,
wait until the table is unlocked before continuing. If you
are running mysqld with external locking
disabled, the table can be locked only by another
myisamchk command.

You can also set the following variables by using
--var_name=value
syntax:

sort_buffer_size is used when the keys are
repaired by sorting keys, which is the normal case when you use
--recover.
myisam_sort_buffer_size is available as an
alternative name to sort_buffer_size.
myisam_sort_buffer_size is preferable to
sort_buffer_size because its name corresponds
to the myisam_sort_buffer_size
server system variable that has a similar meaning.
sort_buffer_size should be considered
deprecated.

key_buffer_size is used when you are checking
the table with --extend-check
or when the keys are repaired by inserting keys row by row into
the table (like when doing normal inserts). Repairing through
the key buffer is used in the following cases:

The temporary files needed to sort the keys would be more
than twice as big as when creating the key file directly.
This is often the case when you have large key values for
CHAR,
VARCHAR, or
TEXT columns, because the
sort operation needs to store the complete key values as it
proceeds. If you have lots of temporary space and you can
force myisamchk to repair by sorting, you
can use the --sort-recover
option.

Repairing through the key buffer takes much less disk space than
using sorting, but is also much slower.

If you want a faster repair, set the
key_buffer_size and
myisam_sort_buffer_size variables to about
25% of your available memory. You can set both variables to
large values, because only one of them is used at a time.

ft_min_word_len and
ft_max_word_len indicate the minimum and
maximum word length for FULLTEXT indexes on
MyISAM tables.
ft_stopword_file names the stopword file.
These need to be set under the following circumstances.

If you use myisamchk to perform an operation
that modifies table indexes (such as repair or analyze), the
FULLTEXT indexes are rebuilt using the
default full-text parameter values for minimum and maximum word
length and the stopword file unless you specify otherwise. This
can result in queries failing.

The problem occurs because these parameters are known only by
the server. They are not stored in MyISAM
index files. To avoid the problem if you have modified the
minimum or maximum word length or the stopword file in the
server, specify the same ft_min_word_len,
ft_max_word_len, and
ft_stopword_file values to
myisamchk that you use for
mysqld. For example, if you have set the
minimum word length to 3, you can repair a table with
myisamchk like this:

shell> myisamchk --recover --ft_min_word_len=3 tbl_name.MYI

To ensure that myisamchk and the server use
the same values for full-text parameters, you can place each one
in both the [mysqld] and
[myisamchk] sections of an option file:

Check the table very thoroughly. This is quite slow if the
table has many indexes. This option should only be used in
extreme cases. Normally, myisamchk or
myisamchk --medium-check should be able
to determine whether there are any errors in the table.

If you are using
--extend-check and have
plenty of memory, setting the
key_buffer_size variable to a large value
helps the repair operation run faster.