Chapter 5 MySQL Programs

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.

5.1 Overview of MySQL Programs

There are many different programs in a MySQL installation. This
раздел provides a brief overview of them. Later разделs provide
a more detailed description of each one. Each program's
description indicates its invocation syntax and the options that it supports.

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,
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
раздел 5.2, 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:

A server startup script. This script is used on systems that
use System V-style run directories containing scripts that
start system services for particular run levels. It invokes
mysqld_safe to start the MySQL server. See
раздел 5.3.3.

This program creates the SSL certificate and key files and RSA
key-pair files required to support secure connections, if
those files are missing. Files created by
mysql_ssl_rsa_setup can be used for secure
connections using SSL or RSA. See
раздел 5.4.3.

This program is used after a MySQL upgrade operation. It
checks tables for incompatibilities and repairs them if
necessary, and updates the grant tables with any changes that
have been made in newer versions of MySQL. See
раздел 5.4.5.

A client that performs administrative operations, such as
creating or dropping databases, reloading the grant tables,
flushing tables to disk, and reopening log files.
mysqladmin can also be used to retrieve
version, process, and status information from the server. See
раздел 5.5.2.

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.

Environment Variable

Meaning

MYSQL_UNIX_PORT

The default Unix socket file; used for connections to
localhost

MYSQL_TCP_PORT

The default port number; used for TCP/IP connections

MYSQL_PWD

The default password

MYSQL_DEBUG

Debug trace options when debugging

TMPDIR

The directory where temporary tables and files are created

For a full list of environment variables used
by MySQL programs,
see раздел 5.9.

5.2 Using MySQL Programs

5.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
раздел 5.2.3.

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 разделs 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 раздел 5.2.2.

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
раздел 5.2.10.) 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.

5.2.2 Connecting to the MySQL Server

This раздел describes how to establish a connection to the MySQL
server. For additional information if you are unable to connect,
see раздел 7.2.8.

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
раздел 7.1.2.1.

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. For connections to localhost, MySQL
programs attempt to connect to the local server by 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:

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

The --protocol option enables you
to establish a particular type of connection even when the other
options would normally default to some other protocol.

If the server is configured to accept IPv6 connections, clients
can connect over IPv6 using
--host=::1. See
раздел 6.1.9.

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

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.

The protocols permitted by the client for encrypted
connections. The value is a comma-separated list containing
one or more protocol names. The protocols that can be named
for this option depend on the SSL library used to compile
MySQL. For details, see
раздел 7.4.3.

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] раздел of an option file. The
relevant раздел of the file might look like this:

You can specify some connection parameters using environment
variables. The host can be specified for
mysql using MYSQL_HOST.
The MySQL user name can be specified using
USER (this is for Windows only). The
password can be specified using MYSQL_PWD,
although this is insecure; see
раздел 7.1.2.1. For a list of
variables, see раздел 5.9.

5.2.3 Specifying Program Options

There are several ways to specify options for MySQL programs:

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
раздел 5.2.10). 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
раздел 6.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.

For the server, one exception applies: The
mysqld-auto.cnf option file in the data
directory is processed last, so it takes precedence even over
command-line options.

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.

5.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 и
--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

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:

5.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-query_cache_size=4M, the
server produces this error:

Maximum value of 'query_cache_size' cannot be set

5.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.

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

The .mylogin.cnf file that contains login
path options. This is an encrypted file created by the
mysql_config_editor utility. See
раздел 5.6.7. 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.

The mysqld-auto.cnf file in the data
directory. This JSON-format file contains persisted system
variable settings. It is created by the server upon execution
of SET
PERSIST statements. See
раздел 14.7.4.1, SET Syntax for Variable Assignment. Management of
mysqld-auto.cnf should be left to the
server and not performed manually.

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.

On Windows, MySQL programs read startup options from the files
shown in the following table, in the specified order (top files
are read first, files read later take precedence).

In the preceding table, %PROGRAMDATA%
represents the file system directory that contains application
data for all users on the host. This path defaults to
C:\ProgramData on Microsoft Windows Vista and
greater, and C:\Documents and Settings\All
Users\Application Data on older versions of Microsoft Windows.

%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 8.0 has been
installed using MySQL Installer, this is typically
C:\PROGRAMDIR\MySQL\MySQL 8.0 Server where
PROGRAMDIR represents the programs
directory (usually Program Files on
English-language versions of Windows), See
раздел 2.3.3.

DATADIR represents the MySQL data
directory. As used to find mysqld-auto.cnf,
its default value is the data directory location built in when
MySQL was compiled, but can be changed by
--datadir specified as an
option-file or command-line option processed before
mysqld-auto.cnf is processed.

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

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 sets it to
BASEDIR, the MySQL base installation directory.

DATADIR represents the MySQL data
directory. As used to find mysqld-auto.cnf,
its default value is the data directory location built in when
MySQL was compiled, but can be changed by
--datadir specified as an
option-file or command-line option processed before
mysqld-auto.cnf is processed.

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, and
mysqld-auto.cnf, which the server creates in JSON format.

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
раздел 5.2.4). 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
раздел 10.1.1.

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
8.0 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 (but not by
mysqld). This 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.

[client]
# The following password will be sent to all standard MySQL clients
password="my password"
[mysql]
no-auto-rehash
connect_timeout=2

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

[mysqld-8.0]
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.

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.

5.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
раздел 5.2.6.) 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, 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.

Read options from the named login path in the
.mylogin.cnf login path file. A
login path is an option group containing
options that specify which MySQL server to connect to and
which account to authenticate as. To create or modify a login
path file, use the mysql_config_editor
utility. See раздел 5.6.7.

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.

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.

The exception is that client programs read the
.mylogin.cnf login path file, if it
exists, even when
--no-defaults is used. This
permits passwords to be specified in a safer way than on the
command line even if
--no-defaults is present.
(.mylogin.cnf is created by the
mysql_config_editor utility. See
раздел 5.6.7.)

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

5.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)

5.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.
раздел 5.9, 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.

5.3 MySQL Server and Server-Startup Programs

This раздел describes mysqld,
the MySQL server,
and several programs that are used to start the server.

5.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.

5.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 file. A description of error logging
is given later in this раздел.

For MySQL installation using an RPM distribution, server
startup and shutdown is managed by systemd on several Linux
platforms. On these platforms, mysqld_safe
is no longer installed because it is unnecessary. For more
information, see раздел 2.5.9.

Options unknown to mysqld_safe are passed to
mysqld if they are specified on the command
line, but ignored if they are specified in the
[mysqld_safe] group of an option file. See
раздел 5.2.6.

mysqld_safe reads all options from the
[mysqld], [server], and
[mysqld_safe] разделs in option files. For
example, if you specify a [mysqld] раздел
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] разделs, but to be
current you should rename such разделs to
[mysqld_safe].

mysqld_safe accepts options on the command
line and in option files, as described in the following table.
For information about option files used by MySQL programs, see
раздел 5.2.6.

The name of an option file to be read in addition to the
usual option files. This must be the first option on the
command line if it is used. If the file does not exist or is
otherwise inaccessible, the server will exit with an error.

This option controls the format for timestamps in log output
produced by mysqld_safe. The following
list describes the permitted values. For any other value,
mysqld_safe logs a warning and uses
UTC format.

The name of the library to use for memory allocation instead
of the system malloc() library. 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.

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

This option can be given only on the command line and not in
an option file.

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
раздел 2.1.4.)
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.

mysqld_safe tries to use the
sleep and date system
utilities to determine how many times per second it has
attempted to start. If these utilities are present and the
attempted starts per second is greater than 5,
mysqld_safe waits 1 full second before
starting again. This is intended to prevent excessive CPU usage
in the event of repeated failures. (Bug #11761530, Bug #54035)

5.3.3 mysql.server Б─■ MySQL Server Startup Script

MySQL distributions on Unix include a script named
mysql.server, which starts the 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 OS X
Startup Item for MySQL.

For MySQL installation using an RPM distribution, server
startup and shutdown is managed by systemd on several Linux
platforms. On these platforms, mysql.server
and mysqld_safe are no longer installed
because they are unnecessary. For more information, see
раздел 2.5.9.

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

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

Before mysql.server starts the server, it
changes location to the MySQL installation directory, and then
invokes mysqld_safe. To run the server as
some specific user, add an appropriate user
option to the [mysqld] group of the
/etc/my.cnf option file, as shown later in
this раздел. (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, so you should make a copy of
your edited version that you can reinstall.)

To start and stop MySQL automatically on your server, you must
add start and stop commands to the appropriate places in your
/etc/rc* files.

If you use the Linux server RPM package
(MySQL-server-VERSION.rpm),
or a native Linux package installation, the
mysql.server script may be installed in the
/etc/init.d directory with the name
mysql. See
раздел 2.5.4, for more information
on the Linux RPM packages.

Some vendors provide RPM packages that install a startup script
under a different name such as mysqld.

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

Older Red Hat systems use the
/etc/rc.d/init.d directory rather than
/etc/init.d. Adjust the preceding
commands accordingly. Alternatively, first create
/etc/init.d as a symbolic link that
points to /etc/rc.d/init.d:

shell> cd /etc
shell> ln -s rc.d/init.d .

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/. 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. In other words, on FreeBSD, you should install the
mysql.server script as
/usr/local/etc/rc.d/mysql.server.sh to
enable automatic startup.

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] разделs of option files. For
backward compatibility, it also reads
[mysql_server] разделs, but to be current
you should rename such разделs to
[mysql.server].

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

The mysql.server script supports the
following options. 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.

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
invoke using 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).

5.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.

For MySQL installation using an RPM distribution, server
startup and shutdown is managed by systemd on several Linux
platforms. On these platforms, mysqld_multi
is no longer installed because it is unnecessary. For
information about using systemd to handle multiple MySQL
instances, see раздел 2.5.9.

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,
раздел 2.9.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
раздел 6.7, Running Multiple MySQL Instances on One Machine.

start, stop,
reload (stop and restart), and
report indicate which operation to perform.
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.

The mysqld binary to be used. Note that
you can specify mysqld_safe as the value
for this option also. If you use
mysqld_safe to start the server, you can
include the mysqld or
ledir options in the corresponding
[mysqldN]
option group. These options indicate the name of the server
that mysqld_safe should start and the
path name of the directory where the server is located. (See
the descriptions for these options in
раздел 5.3.2.) Example:

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
раздел 6.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:

See
раздел 7.2. You have to do this
for each mysqld server. Change the
connection parameters appropriately when connecting to each
one. Note that the host name part of the account name must
permit you to connect as multi_admin from
the host where you want to run
mysqld_multi.

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.)

The --pid-file option is
very important if you are using
mysqld_safe to start
mysqld (for example,
--mysqld=mysqld_safe)
Every mysqld should have its own process
ID file. The advantage of using
mysqld_safe instead of
mysqld is that
mysqld_safe monitors its
mysqld process and restarts it if the
process terminates due to a signal sent using kill
-9 or for other reasons, such as a segmentation
fault. Please note that the mysqld_safe
script might require that you start it from a certain place.
This means that you might have to change location to a
certain directory before running
mysqld_multi. If you have problems
starting, please see the mysqld_safe
script. Check especially the lines:

The test performed by these lines should be successful, or
you might encounter problems. See
раздел 5.3.2.

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.

5.4 MySQL Installation-Related Programs

The programs in this раздел are used when installing or upgrading MySQL.

5.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.

The validate_password plugin can be used for
password strength checking. If the plugin is not installed,
mysql_secure_installation prompts the user
whether to install it. Any passwords entered later are checked
using the plugin if it is enabled.

Most of the usual MySQL client options such as
--host and
--port can be
used on the command line and in option files. For example, to
connect to the local server over IPv6 using port 3307, use this command:

shell> mysql_secure_installation --host=::1 --port=3307

mysql_secure_installation supports the
following options, which can be specified on the command line or
in the [mysql_secure_installation] and
[client] groups of an option file. For
information about option files used by MySQL programs, see
раздел 5.2.6.

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.

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.

The exception is that the .mylogin.cnf
file, if it exists, is read in all cases. This permits
passwords to be specified in a safer way than on the command
line even when
--no-defaults
is used. (.mylogin.cnf is created by
the mysql_config_editor utility. See
раздел 5.6.7.)

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
раздел 5.2.2.

The protocols permitted by the client for encrypted
connections. The value is a comma-separated list containing
one or more protocol names. The protocols that can be named
for this option depend on the SSL library used to compile
MySQL. For details, see
раздел 7.4.3.

5.4.3 mysql_ssl_rsa_setup Б─■ Create SSL/RSA Files

This program creates the SSL certificate and key files and RSA
key-pair files required to support secure connections using SSL
and secure password exchange using RSA over unencrypted
connections, if those files are missing.
mysql_ssl_rsa_setup can also be used to
create new SSL files if the existing ones have expired.

mysql_ssl_rsa_setup uses the
openssl command, so its use is contingent
on having OpenSSL installed on your machine.

Another way to generate SSL and RSA files, for MySQL
distributions compiled using OpenSSL, is to have the server
generated them automatically. See
раздел 7.4.6.1.

mysql_ssl_rsa_setup helps lower the barrier
to using SSL by making it easier to generate the required
files. However, certificates generated by
mysql_ssl_rsa_setup are self-signed, which
is not very secure. After you gain experience using the files
created by mysql_ssl_rsa_setup, consider
obtaining a CA certificate from a registered certificate authority.

mysql_ssl_rsa_setup attempts to create SSL
and RSA files using a default set of file names.
It works as follows:

mysql_ssl_rsa_setup checks for the
openssl binary at the locations specified
by the PATH environment variable. If
openssl is not found,
mysql_ssl_rsa_setup does nothing. If
openssl is present,
mysql_ssl_rsa_setup looks for default SSL
and RSA files in the MySQL data directory specified by the
--datadir
option, or the compiled-in data directory if that option is
not given.

private_key.pemPrivate member of private/public key pair
public_key.pem Public member of private/public key pair

If any of these files are present,
mysql_ssl_rsa_setup creates no RSA files.
Otherwise, it invokes openssl to create
them. These files enable secure password exchange using RSA
over unencrypted connections for accounts authenticated by
the sha256_password plugin; see
раздел 7.5.1.2.

At startup, the MySQL server automatically uses the SSL files
created by mysql_ssl_rsa_setup to enable SSL
if no explicit SSL options are given other than
--ssl. If you prefer to
designate the files explicitly, use the
--ssl-ca,
--ssl-cert, and
--ssl-key options at startup to
name the ca.pem,
server-cert.pem, and
server-key.pem files, respectively.

The server also automatically uses the RSA files created by
mysql_ssl_rsa_setup to enable RSA if no
explicit RSA options are given.

If the server is SSL-enabled, clients use SSL by default for the
connection. To specify certificate and key files explicitly, use
the --ssl-ca,
--ssl-cert, and
--ssl-key options to name the
ca.pem, client-cert.pem, and
client-key.pem files, respectively.
However, some additional client setup may be required first
because mysql_ssl_rsa_setup by default
creates those files in the data directory. The permissions for
the data directory normally enable access only to the system
account that runs the MySQL server, so client programs cannot
use files located there. To make the files available, copy them
to a directory that is readable (but not
writable) by clients:

For local clients, the MySQL installation directory can be
used. For example, if the data directory is a subdirectory
of the installation directory and your current location is
the data directory, you can copy the files like this:

shell> cp ca.pem client-cert.pem client-key.pem ..

For remote clients, distribute the files using a secure
channel to ensure they are not tampered with during transit.

If the SSL files used for a MySQL installation have expired, you
can use
mysql_ssl_rsa_setup to create new ones:

Stop the server.

Rename or remove the existing SSL files. You may wish to
make a backup of them first. (The RSA files do not expire,
so you need not remove them.
mysql_ssl_rsa_setup will see that they
exist and not overwrite them.)

mysql_ssl_rsa_setup supports the following
command-line options, which can be specified on the command line
or in the [mysql_ssl_rsa_setup] and
[mysqld] groups of an option file. For
information about option files used by MySQL programs, see
раздел 5.2.6.

The name of the user who should be the owner of any created
files. The value is a user name, not a numeric user ID. In
the absence of this option, files created by
mysql_ssl_rsa_setup are owned by the user
who executes it. This option is valid only if you execute
the program as root on a system that
supports the chown() system call.

Verbose mode. Produce more output about what the program
does. For example, the program shows the
openssl commands it runs, and produces
output to indicate whether it skips SSL or RSA file creation
because some default file already exists.

5.4.4 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
раздел 11.6.

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.

5.4.5 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.

mysql_upgrade communicates directly with the
MySQL server, sending it the SQL statements required to
perform an upgrade.

On Windows Server 2008, Vista, and newer, 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
раздел 2.10.1, 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 to check and repair
tables and to upgrade the system tables:

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:

For local host connections on Unix, the
--protocol=tcp option
forces a connection using TCP/IP rather than the Unix socket file.

mysql_upgrade 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.

For details about what table-checking operations entail, see the
description of the FOR UPGRADE option of the
CHECK TABLE statement (see
раздел 14.7.2.2).

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.

mysql_upgrade checks user
table rows and, for any row with an empty
plugin column, sets that column to
'mysql_native_password' if the credentials
use a hash format compatible with that plugin. Rows with a
pre-4.1 password hash must be upgraded manually.

Unless invoked with the
--skip-sys-schema option,
mysql_upgrade installs the
sys schema if it is not installed, and
upgrades it to the current version otherwise.
mysql_upgrade returns an error if a
sys schema exists but has no
version view, on the assumption that its
absence indicates a user-created schema:

Error occurred: A sys schema exists with no sys.version view. If
you have a user created sys schema, this must be renamed for the
upgrade to succeed.

To upgrade in this case, remove or rename the existing
sys schema first.

mysql_upgrade checks for partitioned
InnoDB tables that were created using the
generic partitioning handler and attempts to upgrade them to
InnoDB native partitioning. You can upgrade
such tables individually in the mysql client
using the ALTER
TABLE ... UPGRADE PARTITIONING SQL statement.

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. First, connect
to the server as root:

mysql_upgrade supports the following options,
which can be specified on the command line or in the
[mysql_upgrade] and
[client] groups of an option file. For
information about option files used by MySQL programs, see
раздел 5.2.6.

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.

Read options from the named login path in the
.mylogin.cnf login path file. A
login path is an option group containing
options that specify which MySQL server to connect to and
which account to authenticate as. To create or modify a
login path file, use the
mysql_config_editor utility. See
раздел 5.6.7.

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.

The exception is that the .mylogin.cnf
file, if it exists, is read in all cases. This permits
passwords to be specified in a safer way than on the command
line even when
--no-defaults is used.
(.mylogin.cnf is created by the
mysql_config_editor utility. See
раздел 5.6.7.)

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.

Specifying a password on the command line should be
considered insecure. See
раздел 7.1.2.1. You can use an
option file to avoid giving the password on the command
line.

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
раздел 5.2.2.

The protocols permitted by the client for encrypted
connections. The value is a comma-separated list containing
one or more protocol names. The protocols that can be named
for this option depend on the SSL library used to compile
MySQL. For details, see
раздел 7.4.3.

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.

By default, binary logging by
mysql_upgrade is disabled. Invoke the
program with --write-binlog if you want its
actions to be written to the binary log.

Running mysql_upgrade is not recommended
with a MySQL Server that is running with global transaction
identifiers enabled (Bug #13833710). This is because
enabling GTIDs means that any updates which
mysql_upgrade might need to perform on
system tables using a nontransactional storage engine such
as MyISAM to fail. See
раздел 19.1.3.4, for more information.

5.5 MySQL Client Programs

This раздел describes client programs that connect to the MySQL server.

5.5.1 mysql Б─■ The MySQL Command-Line Tool

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:

shell> mysql db_name

Or:

shell> mysql --user=user_name --password=your_passworddb_name

Then type an SQL statement, end it with ;,
\g, or \G and press Enter.

Typing Control+C interrupts the current
statement if there is one, or cancels any partial
input line otherwise.

You can execute SQL statements in a script file (batch file) like this:

shell> mysql db_name < script.sql > em>output.tab

On Unix, the mysql client logs statements
executed interactively to a history file. See
раздел 5.5.1.3.

5.5.1.1 mysql 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 раздел 5.2.6.

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.

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
раздел 7.3.8.)

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 раздел 5.5.1.2.

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, and to
syslog if the
--syslog option is given. For
more information, see раздел 5.5.1.3.

Enable or disable LOCAL capability for
LOAD DATA
INFILE. With no value, the option enables
LOCAL. The option may be given as
--local-infile=0 or
--local-infile=1 to explicitly
disable or enable LOCAL. Enabling
LOCAL has no effect if the server does
not also support it.

Read options from the named login path in the
.mylogin.cnf login path file. A
login path is an option group containing
options that specify which MySQL server to connect to and
which account to authenticate as. To create or modify a
login path file, use the
mysql_config_editor utility. See
раздел 5.6.7.

Enable named mysql commands. Long-format
commands are permitted, not just short-format commands. For
example, quit and \q
both are recognized. Use
--skip-named-commands
to disable named commands. See
раздел 5.5.1.2.

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.

The exception is that the .mylogin.cnf
file, if it exists, is read in all cases. This permits
passwords to be specified in a safer way than on the command
line even when --no-defaults
is used. (.mylogin.cnf is created by
the mysql_config_editor utility. See
раздел 5.6.7.)

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.
раздел 5.5.1.2,
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.

Specifying a password on the command line should be
considered insecure. See
раздел 7.1.2.1. You can use an
option file to avoid giving the password on the command line.

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
раздел 5.2.2.

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:

Permit only those UPDATE and
DELETE statements that
specify which rows to modify by using key values. If you
have set this option in an option file, you can override it
by using --safe-updates on the
command line. See раздел 5.5.1.6, mysql Tips, for more
information about this option.

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 deprecated and will be removed in a future
MySQL release. It is always enabled and attempting to disable it
(--skip-secure-auth,
--secure-auth=0)
produces an error.

Passwords that use the pre-4.1 hashing method are less
secure than passwords that use the native password hashing
method and should be avoided. Pre-4.1 passwords are
deprecated and support for them has been removed.

The path name to a file containing the server RSA public
key. The file must be in PEM format. The public key is used
for RSA encryption of the client password for connections to
the server made using accounts that authenticate with the
sha256_password plugin. This option is
ignored for client accounts that do not authenticate with
that plugin. It is also ignored if password encryption is
not needed, as is the case when the client connects to the
server using an SSL connection.

The server sends the public key to the client as needed, so
it is not necessary to use this option for RSA password
encryption to occur. It is more efficient to do so because
then the server need not send the key.

For additional discussion regarding use of the
sha256_password plugin, including how to
get the RSA public key, see
раздел 7.5.1.2.

This option causes mysql to send
interactive statements to the system logging facility. On
Unix, this is syslog; on Windows, it is
the Windows Event Log. The destination where logged messages
appear is system dependent. On Linux, the destination is
often the /var/log/messages file.

Here is a sample of output generated on Linux by using
--syslog. This output is formatted for
readability; each logged message actually takes a single
line.

The protocols permitted by the client for encrypted
connections. The value is a comma-separated list containing
one or more protocol names. The protocols that can be named
for this option depend on the SSL library used to compile
MySQL. For details, see
раздел 7.4.3.

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.)

5.5.1.2 mysql 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 the current input statement.
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.
resetconnection(\x) Clean session context.
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.

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. For more information, see
раздел 5.5.1.4.

charset charset_name,
\C charset_name

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
раздел 25.8.17), and for parsing
the body of stored procedures and functions, triggers, and
events (see раздел 21.1).

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 раздел.

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

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

status, \s

Provide status information about the connection and the
server you are using. If you are running in
--safe-updates mode,
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

The current connection identifier

\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 раздел 5.2.6.) 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:

5.5.1.3 mysql Logging

The mysql client can do these types of
logging for statements executed interactively:

On Unix, mysql writes the statements 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.

On all platforms, if the --syslog option is
given, mysql writes the statements to the
system logging facility. On Unix, this is
syslog; on Windows, it is the Windows
Event Log. The destination where logged messages appear is
system dependent. On Linux, the destination is often the
/var/log/messages file.

The following discussion describes characteristics that apply to
all logging types and provides information specific to
each logging type.

How Logging Occurs

For each enabled logging destination, 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();

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:

shell> mysql --histignore="*UPDATE*:*DELETE*"

Controlling the History File

The .mysql_history file should be protected
with a restrictive access mode because sensitive information
might be written to it, such as the text of SQL statements that
contain passwords. See раздел 7.1.2.1.

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

syslog Logging Characteristics

If the --syslog option is given,
mysql writes interactive statements to the
system logging facility. Message logging has the following characteristics.

Logging occurs at the information level. This
corresponds to the LOG_INFO priority for
syslog on Unix/Linux
syslog capability and to
EVENTLOG_INFORMATION_TYPE for the Windows
Event Log. Consult your system documentation for configuration
of your logging capability.

Message size is limited to 1024 bytes.

Messages consist of the identifier
MysqlClient followed by these values:

SYSTEM_USER

The system user name (login name) or --
if the user is unknown.

MYSQL_USER

The MySQL user name (specified with the
--user option) or
-- if the user is unknown.

CONNECTION_ID:

The client connection identifier. This is the same as the
CONNECTION_ID() function
value within the session.

DB_SERVER

The server host or -- if the host is unknown.

DB

The default database or -- if no database
has been selected.

QUERY

The text of the logged statement.

Here is a sample of output generated on Linux by using
--syslog. This output is formatted for
readability; each logged message actually takes a single line.

5.5.1.4 mysql 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
раздел 6.1.10).

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

5.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.

5.5.1.6 mysql Tips

This раздел describes some techniques that can help you use
mysql more effectively.

5.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.

5.5.1.6.2 Unicode Support on Windows

Windows provides APIs based on UTF-16LE for reading from and
writing to the console; the mysql client
for Windows is able to use these APIs. 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

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

5.5.1.6.4 Using the --safe-updates Option

For beginners, a useful startup option is
--safe-updates (or
--i-am-a-dummy,
which has the same effect). It is helpful for cases when you
might have issued a DELETE FROM
tbl_name statement but
forgotten the WHERE clause. Normally, such
a statement deletes all rows from the table. With
--safe-updates, you can delete
rows only by specifying the key values that identify them.
This helps prevent accidents.

When you use the --safe-updates
option, mysql issues the following
statement when it connects to the MySQL server:

The server limits all large
SELECT results to 1,000
rows unless the statement includes a
LIMIT clause.

The server aborts multiple-table
SELECT statements that
probably need to examine more than 1,000,000 row combinations.

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

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

5.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.

For more information about auto-reconnect and its effect on
state information when a reconnection occurs, see
раздел 25.8.16.

5.5.2 mysqladmin Б─■ Client for Administering a MySQL Server

mysqladmin is a client for performing
administrative operations. You can use it to check the server's
configuration and current status, to create and drop databases, and more.

mysqladmin supports the following commands.
Some of the commands take an argument following the command name.

create db_name

Create a new database named
db_name.

debug

Tell the server to write debug information to the error log.
Format and content of this information is subject to change.

This includes information about the Event Scheduler. See
раздел 21.4.5.

drop db_name

Delete the database named db_name
and all its tables.

extended-status

Display the server status variables and their values.

flush-hosts

Flush all information in the host cache.

flush-logs [log_type ...]

Flush all logs.

The mysqladmin flush-logs command permits
optional log types to be given, to specify which logs to
flush. Following the flush-logs command,
you can provide a space-separated list of one or more of the
following log types: binary,
engine, error,
general, relay,
slow. These correspond to the log types
that can be specified for the
FLUSH LOGS SQL statement.

flush-privileges

Reload the grant tables (same as reload).

flush-status

Clear status variables.

flush-tables

Flush all tables.

flush-threads

Flush the thread cache.

kill id,id,...

Kill server threads. If multiple thread ID values are given,
there must be no spaces in the list.

password new_password

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.

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

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 раздел 5.2.6.

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.

Read options from the named login path in the
.mylogin.cnf login path file. A
login path is an option group containing
options that specify which MySQL server to connect to and
which account to authenticate as. To create or modify a
login path file, use the
mysql_config_editor utility. See
раздел 5.6.7.

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.

The exception is that the .mylogin.cnf
file, if it exists, is read in all cases. This permits
passwords to be specified in a safer way than on the command
line even when
--no-defaults is used.
(.mylogin.cnf is created by the
mysql_config_editor utility. See
раздел 5.6.7.)

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.

Specifying a password on the command line should be
considered insecure. See
раздел 7.1.2.1. You can use an
option file to avoid giving the password on the command line.

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
раздел 5.2.2.

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 deprecated and will be removed in a future
MySQL release. It is always enabled and attempting to disable it
(--skip-secure-auth,
--secure-auth=0)
produces an error.

Passwords that use the pre-4.1 hashing method are less
secure than passwords that use the native password hashing
method and should be avoided. Pre-4.1 passwords are
deprecated and support for them has been removed.

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.

The protocols permitted by the client for encrypted
connections. The value is a comma-separated list containing
one or more protocol names. The protocols that can be named
for this option depend on the SSL library used to compile
MySQL. For details, see
раздел 7.4.3.

5.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
раздел 14.3.5, 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 раздел 5.2.6.

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
performace_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.

Read options from the named login path in the
.mylogin.cnf login path file. A
login path is an option group containing
options that specify which MySQL server to connect to and
which account to authenticate as. To create or modify a
login path file, use the
mysql_config_editor utility. See
раздел 5.6.7.

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.

The exception is that the .mylogin.cnf
file, if it exists, is read in all cases. This permits
passwords to be specified in a safer way than on the command
line even when
--no-defaults is used.
(.mylogin.cnf is created by the
mysql_config_editor utility. See
раздел 5.6.7.)

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.

Specifying a password on the command line should be
considered insecure. See
раздел 7.1.2.1. You can use an
option file to avoid giving the password on the command line.

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
раздел 5.2.2.

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 deprecated and will be removed in a future
MySQL release. It is always enabled and attempting to disable it
(--skip-secure-auth,
--secure-auth=0)
produces an error.

Passwords that use the pre-4.1 hashing method are less
secure than passwords that use the native password hashing
method and should be avoided. Pre-4.1 passwords are
deprecated and support for them has been removed.

The protocols permitted by the client for encrypted
connections. The value is a comma-separated list containing
one or more protocol names. The protocols that can be named
for this option depend on the SSL library used to compile
MySQL. For details, see
раздел 7.4.3.

5.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.

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 раздел 11.1.4), 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
раздел 27.2.

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 раздел 5.2.6.

Read options from the named login path in the
.mylogin.cnf login path file. A
login path is an option group containing
options that specify which MySQL server to connect to and
which account to authenticate as. To create or modify a
login path file, use the
mysql_config_editor utility. See
раздел 5.6.7.

--password[=password],
-p[password]

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.

Specifying a password on the command line should be
considered insecure. See
раздел 7.1.2.1. You can use an
option file to avoid giving the password on the command
line.

--pipe, -W

On Windows, connect to the server using a named pipe. This
option applies only if the server supports named-pipe
connections.

--plugin-dir=dir_name

The directory in which to look for plugins. Specify this
option if the
--default-auth option is
used to specify an authentication plugin but
mysqldump does not find it. See
раздел 7.3.9.

--port=port_num,
-P port_num

The TCP/IP port number to use for the connection.

--protocol={TCP|SOCKET|PIPE|MEMORY}

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
раздел 5.2.2.

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 deprecated and will be removed in a future
MySQL release. It is always enabled and attempting to disable it
(
--skip-secure-auth,
--secure-auth=0) produces an error.

Passwords that use the pre-4.1 hashing method are less
secure than passwords that use the native password hashing
method and should be avoided. Pre-4.1 passwords are
deprecated and support for them has been removed.

--socket=path,
-S path

For connections to localhost, the Unix
socket file to use, or, on Windows, the name of the named
pipe to use.

--ssl*

Options that begin with
--ssl specify whether to
connect to the server using SSL and indicate where to find
SSL keys and certificates. See
раздел 7.4.5.

The protocols permitted by the client for encrypted
connections. The value is a comma-separated list containing
one or more protocol names. The protocols that can be named
for this option depend on the SSL library used to compile
MySQL. For details, see
раздел 7.4.3.

--user=user_name,
-u user_name

The MySQL user name to use when connecting to the server.

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

max_allowed_packet

The maximum size of the buffer for client/server
communication. The default is 24MB, the maximum is 1GB.

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.

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.

The exception is that the .mylogin.cnf
file, if it exists, is read in all cases. This permits
passwords to be specified in a safer way than on the command
line even when
--no-defaults is used.
(.mylogin.cnf is created by the
mysql_config_editor utility. See
раздел 5.6.7.)

Print the program name and all options that it gets
from option files.

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 MySQL Cluster tables, which are
not supported in MySQL 8.0.

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

Ignore all errors; 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.

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. The
CHANGE MASTER TO statement
reads the values of Relay_Master_Log_File
and Exec_Master_Log_Pos from the
SHOW SLAVE STATUS output and
uses them for MASTER_LOG_FILE and
MASTER_LOG_POS respectively. These are
the master server coordinates from which the slave should start replicating.

Inconsistencies in the sequence of transactions from the
relay log which have been executed can cause the wrong
position to be used. See
раздел 19.4.1.34
for more information.

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

This option should not be used if the server where the
dump is going to be applied uses
gtid_mode=ON and
MASTER_AUTOPOSITION=1.

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.

--set-gtid-purged=value

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).

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 раздел 6.1.8.

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.

This option requires a server version of 4.1.0 or
higher. With older servers, it does nothing.

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.

This option should be used only when
mysqldump is run on the same machine as
the mysqld server. Because the server
creates files *.txt file 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', 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.

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.

Prior to MySQL 8.0, the
--routines and
--events options for
mysqldump and
mysqlpump are not required to include
stored routines and events when using the
--all-databases option:
The dump includes the mysql system
database, and therefore also the
mysql.proc and
mysql.event tables containing stored
routine and event definitions. As of MySQL 8.0,
the mysql.event and
mysql.proc tables are not used.
Definitions for the corresponding objects are stored in data
dictionary tables, but those tables are not dumped. To
include stored routines and events in a dump made using
--all-databases, use the
--routines and
--events options
explicitly.

--databases, -B

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
performace_schema database, which
normally is 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.

--ignore-error=error[,error]...

Ignore the specified errors. The option value is a
comma-separated list of error numbers specifying the errors
to ignore during mysqldump execution. If
the --force option is also
given to ignore all errors,
--force takes precedence.

--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
global SELECT privilege.

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.

Multiple triggers are permitted.
mysqldump dumps triggers in activation
order so that when the dump file is reloaded, triggers are
created in the same activation order. However, if a
mysqldump dump file contains multiple
triggers for a table that have the same trigger event and
action time, an error occurs for attempts to load the dump
file into an older server that does not support multiple
triggers. (For a workaround, see
раздел 2.10.2.1; you can
convert triggers to be compatible with older servers.)

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

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.

For upgrades to MySQL 5.7.2 or higher from older versions,
do not use --flush-privileges. For
upgrade instructions in this case, see
раздел 2.10.1.1.

--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 > em>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 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
раздел 6.4.4) or at least know the binary log
coordinates to which the dump corresponds:

shell> mysqldump --all-databases --master-data=2 > all_databases.sql

Or:

shell> mysqldump --all-databases --flush-logs --master-data=2
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
performance_schema or sys
schema by default. To dump any of these, name them explicitly on
the command line. You can also name them with the
--databases option. For
performance_schema, also use the
--skip-lock-tables
option.

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 раздел 5.2.6.

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
INFILE. 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
раздел 14.2.6.

Read options from the named login path in the
.mylogin.cnf login path file. A
login path is an option group containing
options that specify which MySQL server to connect to and
which account to authenticate as. To create or modify a
login path file, use the
mysql_config_editor utility. See
раздел 5.6.7.

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.

The exception is that the .mylogin.cnf
file, if it exists, is read in all cases. This permits
passwords to be specified in a safer way than on the command
line even when
--no-defaults is used.
(.mylogin.cnf is created by the
mysql_config_editor utility. See
раздел 5.6.7.)

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.

Specifying a password on the command line should be
considered insecure. See
раздел 7.1.2.1. You can use an
option file to avoid giving the password on the command
line.

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
раздел 5.2.2.

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 deprecated and will be removed in a future
MySQL release. It is always enabled and attempting to
disable it
(--skip-secure-auth,
--secure-auth=0)
produces an error.

Note

Passwords that use the pre-4.1 hashing method are less
secure than passwords that use the native password hashing
method and should be avoided. Pre-4.1 passwords are
deprecated and support for them has been removed.

The protocols permitted by the client for encrypted
connections. The value is a comma-separated list containing
one or more protocol names. The protocols that can be named
for this option depend on the SSL library used to compile
MySQL. For details, see
раздел 7.4.3.

5.5.6 mysqlpump Б─■ A Database Backup Program

The mysqlpump 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.

mysqlpump uses MySQL features introduced in
MySQL 5.7, and thus assumes use with MySQL 5.7 or higher.

mysqlpump requires at least the
SELECT privilege for dumped
tables, SHOW VIEW for dumped
views, TRIGGER for dumped
triggers, and LOCK TABLES if the
--single-transaction option is
not used. The SELECT privilege on
the mysql system database is required to dump
user definitions. Certain options might require other privileges
as noted in the option descriptions.

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.

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

shell> mysqlpump [options] > dump.sql

However, UTF-16 is not permitted as a connection character set
(see раздел 11.1.4), 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:

To treat all name arguments as database names, use the
--databases option:

shell> mysqlpump --databases db_name1 db_name2 ...

By default, mysqlpump does not dump user
account definitions, even if you dump the
mysql system database that contains the grant
tables. To dump grant table contents as logical definitions in
the form of CREATE USER and
GRANT statements, use the
--users option and suppress
all database dumping:

shell> mysqlpump --exclude-databases=% --users

In the preceding command, % is a wildcard
that matches all database names for the
--exclude-databases option.

mysqlpump Option Summary

mysqlpump supports the following options,
which can be specified on the command line or in the
[mysqlpump] and [client]
groups of an option file. For information about option files
used by MySQL programs, see раздел 5.2.6.

This option does not work with parallelism because
INSERT statements from
different tables can be interleaved and
UNLOCK
TABLES following the end of the inserts for one
table could release locks on tables for which inserts remain.

Prior to MySQL 8.0, the
--routines and
--events options for
mysqldump and
mysqlpump are not required to include
stored routines and events when using the
--all-databases option:
The dump includes the mysql system
database, and therefore also the
mysql.proc and
mysql.event tables containing stored
routine and event definitions. As of MySQL 8.0,
the mysql.event and
mysql.proc tables are not used.
Definitions for the corresponding objects are stored in data
dictionary tables, but those tables are not dumped. To
include stored routines and events in a dump made using
--all-databases, use the
--routines and
--events options
explicitly.

Normally, mysqlpump 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.
CREATE DATABASE statements
are included in the output before each new database.

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,
mysqlpump normally reads the
[client] and
[mysqlpump] groups. If the
--defaults-group-suffix=_other
option is given, mysqlpump also reads the
[client_other] and
[mysqlpump_other] groups.

Do not dump the databases in
db_list, which is a
comma-separated list of one or more database names. Multiple
instances of this option are additive. For more information,
see mysqlpump Object Selection.

Do not dump the databases in
event_list, which is a
comma-separated list of one or more event names. Multiple
instances of this option are additive. For more information,
see mysqlpump Object Selection.

Do not dump the events in
routine_list, which is a
comma-separated list of one or more routine (stored
procedure or function) names. Multiple instances of this
option are additive. For more information, see
mysqlpump Object Selection.

Do not dump the triggers in
trigger_list, which is a
comma-separated list of one or more trigger names. Multiple
instances of this option are additive. For more information,
see mysqlpump Object Selection.

Do not dump the user accounts in
user_list, which is a
comma-separated list of one or more account names. Multiple
instances of this option are additive. For more information,
see mysqlpump Object Selection.

Dump the databases in db_list,
which is a comma-separated list of one or more database
names. The dump includes all objects in the named databases.
Multiple instances of this option are additive. For more
information, see
mysqlpump Object Selection.

Dump the routines in
routine_list, which is a
comma-separated list of one or more routine (stored
procedure or function) names. Multiple instances of this
option are additive. For more information, see
mysqlpump Object Selection.

Read options from the named login path in the
.mylogin.cnf login path file. A
login path is an option group containing
options that specify which MySQL server to connect to and
which account to authenticate as. To create or modify a
login path file, use the
mysql_config_editor utility. See
раздел 5.6.7.

The initial size of the buffer for client/server
communication. When creating multiple-row
INSERT statements (as with
the --extended-insert
option), mysqlpump creates rows up to
N bytes long. If you use this
option to increase the value, ensure that the MySQL server
net_buffer_length system
variable has a value at least this large.

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.

The exception is that the .mylogin.cnf
file, if it exists, is read in all cases. This permits
passwords to be specified in a safer way than on the command
line even when
--no-defaults is used.
(.mylogin.cnf is created by the
mysql_config_editor utility. See
раздел 5.6.7.)

Create a queue for processing the databases in
db_list, which is a
comma-separated list of one or more database names. If
N is given, the queue uses
N threads. If
N is not given, the
--default-parallelism
option determines the number of queue threads.

Multiple instances of this option create multiple queues.
mysqlpump also creates a default queue to
use for databases not named in any
--parallel-schemas option,
and for dumping user definitions if command options select
them. For more information, see
mysqlpump Parallel Processing.

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, mysqlpump prompts for one.

Specifying a password on the command line should be
considered insecure. See
раздел 7.1.2.1. You can use an
option file to avoid giving the password on the command line.

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
раздел 5.2.2.

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
mysqlpump to retrieve the table contents
to obtain incorrect contents or fail.

Omit DEFINER and SQL
SECURITY clauses from the
CREATE statements for views and stored
programs. The dump file, when reloaded, creates objects that
use the default DEFINER and SQL
SECURITY values. See
раздел 21.6.

The protocols permitted by the client for encrypted
connections. The value is a comma-separated list containing
one or more protocol names. The protocols that can be named
for this option depend on the SSL library used to compile
MySQL. For details, see
раздел 7.4.3.

This option enables TIMESTAMP
columns to be dumped and reloaded between servers in
different time zones. mysqlpump 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.

Dump user accounts as logical definitions in the form of
CREATE USER and
GRANT statements.

User definitions are stored in the grant tables in the
mysql system database. By default,
mysqlpump does not include the grant
tables in mysql database dumps. To dump
the contents of the grant tables as logical definitions, use
the --users option and
suppress all database dumping:

Any inclusion or exclusion option may be given multiple times.
The effect is additive. Order of these options does not matter.

The value of each inclusion and exclusion option is a
comma-separated list of names of the appropriate object type.
For example:

--exclude-databases=test,world
--include-tables=customer,invoice

Wildcard characters are permitted in the object names:

% matches any sequence of zero or more
characters.

_ matches any single character.

For example,
--include-tables=t%,__tmp
matches all table names that begin with t and
all five-character table names that end with
tmp.

For users, a name specified without a host part is interpreted
with an implied host of %. For example,
u1 and u1@% are
equivalent. This is the same equivalence that applies in MySQL
generally (see
раздел 7.2.3, Specifying Account Names).

If inclusion options are given in the absence of exclusion
options, only the objects named as included are dumped.

If exclusion options are given in the absence of inclusion
options, all objects are dumped except those named as
excluded.

If inclusion and exclusion options are given, all objects
named as excluded and not named as included are not dumped.
All other objects are dumped.

If multiple databases are being dumped, it is possible to name
tables, triggers, and routines in a specific database by
qualifying the object names with the database name. The
following command dumps databases db1 and
db2, but excludes tables
db1.t1 and db2.t2:

mysqlpump Parallel Processing

mysqlpump can use parallelism to achieve
concurrent processing. You can select concurrency between
databases (to dump multiple databases simultaneously) and within
databases (to dump multiple objects from a given database
simultaneously).

By default, mysqlpump sets up one queue with
two threads. You can create additional queues and control the
number of threads assigned to each one, including the default queue:

--default-parallelism=N
specifies the default number of threads used for each queue.
In the absence of this option, N is 2.

The default queue always uses the default number of threads.
Additional queues use the default number of threads unless
you specify otherwise.

--parallel-schemas=[N:]db_list
sets up a processing queue for dumping the databases named
in db_list and optionally
specifies how many threads the queue uses.
db_list is a comma-separated list
of database names. If the option argument begins with
N:, the queue
uses N threads. Otherwise, the
--default-parallelism
option determines the number of queue threads.

Names in the database list are permitted to contain the same
% and _ wildcard
characters supported for filtering options (see
mysqlpump Object Selection).

mysqlpump uses the default queue for
processing any databases not named explicitly with a
--parallel-schemas option, and
for dumping user definitions if command options select them.

In general, with multiple queues, mysqlpump
uses parallelism between the sets of databases processed by the
queues, to dump multiple databases simultaneously. For a queue
that uses multiple threads, mysqlpump uses
parallelism within databases, to dump multiple objects from a
given database simultaneously. Exceptions can occur; for
example, mysqlpump may block queues while it
obtains from the server lists of objects in databases.

With parallelism enabled, it is possible for output from
different databases to be interleaved. For example,
INSERT statements from multiple
tables dumped in parallel can be interleaved; the statements are
not written in any particular order. This does not affect
reloading because output statements qualify object names with
database names or are preceded by
USE statements as required.

The granularity for parallelism is a single database object. For
example, a single table cannot be dumped in parallel using
multiple threads.

Examples:

shell> mysqlpump --parallel-schemas=db1,db2 --parallel-schemas=db3

mysqlpump sets up a queue to process
db1 and db2, another queue
to process db3, and a default queue to
process all other databases. All queues use two threads.

shell> mysqlpump --parallel-schemas=db1,db2 --parallel-schemas=db3
This is the same as the previous example except that all queues
use four threads.

mysqlpump Restrictions

mysqlpump does not dump the
performance_schema,
ndbinfo, or sys schema by
default. To dump any of these, name them explicitly on the
command line. You can also name them with the
--databases or
--include-databases option.

mysqlpump dumps user accounts in logical form
using CREATE USER and
GRANT statements (for example,
when you use the
--include-users or
--users option). For this
reason, dumps of the mysql system database do
not by default include the grant tables that contain user
definitions: user, db,
tables_priv, columns_priv,
procs_priv, or
proxies_priv. To dump any of the grant
tables, name the mysql database followed by
the table names:

shell> mysqlpump mysql user db ...

5.5.7 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 раздел 14.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 раздел 5.2.6.

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.

Read options from the named login path in the
.mylogin.cnf login path file. A
login path is an option group containing
options that specify which MySQL server to connect to and
which account to authenticate as. To create or modify a
login path file, use the
mysql_config_editor utility. See
раздел 5.6.7.

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.

The exception is that the .mylogin.cnf
file, if it exists, is read in all cases. This permits
passwords to be specified in a safer way than on the command
line even when
--no-defaults is used.
(.mylogin.cnf is created by the
mysql_config_editor utility. See
раздел 5.6.7.)

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.

Specifying a password on the command line should be
considered insecure. See
раздел 7.1.2.1. You can use an
option file to avoid giving the password on the command line.

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
раздел 5.2.2.

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 deprecated and will be removed in a future
MySQL release. It is always enabled and attempting to
disable it
(--skip-secure-auth,
--secure-auth=0) produces
an error.

Passwords that use the pre-4.1 hashing method are less
secure than passwords that use the native password hashing
method and should be avoided. Pre-4.1 passwords are
deprecated and support for them has been removed.

The protocols permitted by the client for encrypted
connections. The value is a comma-separated list containing
one or more protocol names. The protocols that can be named
for this option depend on the SSL library used to compile
MySQL. For details, see
раздел 7.4.3.

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 раздел 5.2.6.

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.

Read options from the named login path in the
.mylogin.cnf login path file. A
login path is an option group containing
options that specify which MySQL server to connect to and
which account to authenticate as. To create or modify a
login path file, use the
mysql_config_editor utility. See
раздел 5.6.7.

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.

The exception is that the .mylogin.cnf
file, if it exists, is read in all cases. This permits
passwords to be specified in a safer way than on the command
line even when
--no-defaults is used.
(.mylogin.cnf is created by the
mysql_config_editor utility. See
раздел 5.6.7.)

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.

Specifying a password on the command line should be
considered insecure. See
раздел 7.1.2.1. You can use an
option file to avoid giving the password on the command line.

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
раздел 5.2.2.

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 deprecated and will be removed in a future
MySQL release. It is always enabled and attempting to disable it
(--skip-secure-auth,
--secure-auth=0) produces
an error.

Passwords that use the pre-4.1 hashing method are less
secure than passwords that use the native password hashing
method and should be avoided. Pre-4.1 passwords are
deprecated and support for them has been removed.

The protocols permitted by the client for encrypted
connections. The value is a comma-separated list containing
one or more protocol names. The protocols that can be named
for this option depend on the SSL library used to compile
MySQL. For details, see
раздел 7.4.3.

5.6.1 ibd2sdi Б─■ InnoDB Tablespace SDI Extraction Utility

SDI data is not present in persistent InnoDB
tablespaces in the MySQL 8.0.0 release. The
ibd2sdi utility is reserved for future use.

5.6.2 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 will
cause InnoDB to deliberately shut down a
running server, it can be preferable to use this tool rather
than waiting for a server in production usage to encounter the
damaged 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. Attempting to run
innochecksum on a tablespace that the server
already has open will result in an Unable to
lock file error.

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.

Verbose mode; prints a progress indicator to the log file
every five seconds. In order for the progress indicator to
be printed, the log file must be specified using the
--log option. To turn on
verbose mode, run:

shell> innochecksum --verbose

To turn off verbose mode, run:

shell> innochecksum --verbose=FALSE

The --verbose option and
--log option can be specified at the same
time. For example:

shell> innochecksum --verbose --log=/var/lib/mysql/test/logtest.txt

To locate the progress indicator information in the log
file, you can perform the following search:

shell> cat ./logtest.txt | grep -i "okay"

The progress indicator information in the log file appears
similar to the following:

The maximum number of checksum mismatches allowed before
innochecksum terminates. The default
setting is 0. If
--allow-mismatches=N,
where N>=0,
N mismatches are permitted and
innochecksum terminates at
N+1. When
--allow-mismatches is set to 0,
innochecksum terminates on the first
checksum mismatch.

In this example, an existing innodb
checksum is rewritten to set
--allow-mismatches to 1.

With --allow-mismatches set to 1, if
there is a mismatch at page 600 and another at page 700 on a
file with 1000 pages, the checksum is updated for pages
0-599 and 601-699. Because
--allow-mismatches is set to 1, the
checksum tolerates the first mismatch and terminates on the
second mismatch, leaving page 600 and pages 700-999 unchanged.

Rewrite a checksum. When rewriting an invalid checksum, the
--no-check
option must be used together with the
--write option. The
--no-check option tells
innochecksum to ignore verification of
the invalid checksum. You do not have to specify the
--no-check option if
the current checksum is valid.

An algorithm must be specified when using the
--write option.
Possible values for the --write option are:

innodb: A checksum calculated in
software, using the original algorithm from
InnoDB.

crc32: A checksum calculated using
the crc32 algorithm, possibly done
with a hardware assist.

none: A constant number.

The --write option rewrites entire pages to
disk. If the new checksum is identical to the existing
checksum, the new checksum is not written to disk in order
to minimize I/O.

innochecksum obtains an exclusive lock
when the --write option is used.

In this example, a crc32 checksum is
written for tab1.ibd:

shell> innochecksum -w crc32 ../data/test/tab1.ibd

In this example, a crc32 checksum is
rewritten to replace an invalid crc32 checksum:

Log output for the innochecksum tool. A
log file name must be provided. Log output contains checksum
values for each tablespace page. For uncompressed tables,
LSN values are also provided. The
--log replaces the
--debug option, which was available in
earlier releases. Example usage:

shell> innochecksum --log=/tmp/log.txt ../data/test/tab1.ibd

or:

shell> innochecksum -l /tmp/log.txt ../data/test/tab1.ibd

- option.

Specify the - option to read from
standard input. If the - option is
missing when read from standard in is
expected, innochecksum will output
innochecksum usage information indicating
that the - option was omitted. Example usages:

shell> cat t1.ibd | innochecksum -

In this example, innochecksum writes the
crc32 checksum algorithm to
a.ibd without changing the original
t1.ibd file.

shell> cat t1.ibd | innochecksum --write=crc32 - > a.ibd

Running innochecksum on Multiple User-defined Tablespace Files

The following examples demonstrate how to run
innochecksum on multiple user-defined
tablespace files (.ibd files).

Run innochecksum for all tablespace
(.ibd) files in the test database:

shell> innochecksum ./data/test/*.ibd

Run innochecksum for all tablespace files
(.ibd files) that have a file name starting
with t:

shell> innochecksum ./data/test/t*.ibd

Run innochecksum for all tablespace files
(.ibd files) in the
data directory:

shell> innochecksum ./data/*/*.ibd

Running innochecksum on multiple
user-defined tablespace files is not supported on Windows
operating systems, as Windows shells such as
cmd.exe do not support glob pattern
expansion. On Windows systems, innochecksum
must be run separately for each user-defined tablespace file.
For example:

Running innochecksum on Multiple System Tablespace Files

By default, there is only one InnoDB system
tablespace file (ibdata1) but multiple
files for the system tablespace can be defined using the
innodb_data_file_path option.
In the following example, three files for the system tablespace
are defined using the
innodb_data_file_path option:
ibdata1, ibdata2, and
ibdata3.

The three files (ibdata1,
ibdata2, and ibdata3)
form one logical system tablespace. To run
innochecksum on multiple files that form one
logical system tablespace, innochecksum
requires the - option to read tablespace
files in from standard input, which is equivalent to
concatenating multiple files to create one single file. For the
example provided above, the following
innochecksum command would be used:

shell> cat ibdata* | innochecksum -

Refer to the innochecksum options information
for more information about the - option.

Running innochecksum on multiple files in
the same tablespace is not supported on Windows operating
systems, as Windows shells such as cmd.exe
do not support glob pattern expansion. On Windows systems,
innochecksum must be run separately for
each system tablespace file. For example:

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

5.6.4 myisamchk Б─■ MyISAM Table-Maintenance Utility

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 разделs. 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
раздел 5.2.6.

5.6.4.1 myisamchk General Options

The options described in this раздел can be used for any type
of table maintenance operation performed by
myisamchk. The разделs 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.

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.

The exception is that the .mylogin.cnf
file, if it exists, is read in all cases. This permits
passwords to be specified in a safer way than on the command
line even when
--no-defaults is used.
(.mylogin.cnf is created by the
mysql_config_editor utility. See
раздел 5.6.7.)

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:

myisam_sort_buffer_size is used when the keys
are repaired by sorting keys, which is the normal case when you
use --recover.
sort_buffer_size is a deprecated synonym for
myisam_sort_buffer_size.

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] разделs 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.

Do not mark the table as checked. This is useful if you use
myisamchk to check a table that is in use
by some other application that does not use locking, such as
mysqld
when run with external locking disabled.

Store information in the .MYI file to
indicate when the table was checked and whether the table
crashed. This should be used to get full benefit of the
--check-only-changed
option, but you shouldn't use this option if the
mysqld server is using the table and you
are running it with external locking disabled.

For myisamchk, the option value is a bit
value that indicates which indexes to update. Each binary
bit of the option value corresponds to a table index, where
the first index is bit 0. An option value of 0 disables
updates to all indexes, which can be used to get faster
inserts. Deactivated indexes can be reactivated by using
myisamchk -r.

Do not follow symbolic links. Normally
myisamchk repairs the table that a
symlink points to. This option does not exist as of MySQL
4.0 because versions from 4.0 on do not remove symlinks
during repair operations.

Do a repair that can fix almost any problem except unique
keys that are not unique (which is an extremely unlikely
error with MyISAM tables). If you want to
recover a table, this is the option to try first. You should
try --safe-recover only if
myisamchk reports that the table cannot
be recovered using
--recover. (In the
unlikely case that
--recover fails, the data
file remains intact.)

If you have lots of memory, you should increase the value of
myisam_sort_buffer_size.

Do a repair using an old recovery method that reads through
all rows in order and updates all index trees based on the
rows found. This is an order of magnitude slower than
--recover, but can handle
a couple of very unlikely cases that
--recover cannot. This
recovery method also uses much less disk space than
--recover. Normally, you
should repair first using
--recover, and then with
--safe-recover only if
--recover fails.

If you have lots of memory, you should increase the value of
key_buffer_size.

The path of the directory to be used for storing temporary
files. If this is not set, myisamchk uses
the value of the TMPDIR environment
variable. --tmpdir can be
set to a list of directory paths that are used successively
in round-robin fashion for creating temporary files. The
separator character between directory names is the colon
(:) on Unix and the semicolon
(;) on Windows.

5.6.4.4 Other myisamchk Options

Analyze the distribution of key values. This improves join
performance by enabling the join optimizer to better choose
the order in which to join the tables and which indexes it
should use. To obtain information about the key
distribution, use a myisamchk --description
--verbose tbl_name
command or the SHOW INDEX FROM
tbl_name statement.

Force AUTO_INCREMENT numbering for new
records to start at the given value (or higher, if there are
existing records with AUTO_INCREMENT
values this large). If value is
not specified, AUTO_INCREMENT numbers for
new records begin with the largest value currently in the
table, plus one.

Sort records according to a particular index. This makes
your data much more localized and may speed up range-based
SELECT and ORDER
BY operations that use this index. (The first time
you use this option to sort a table, it may be very slow.)
To determine a table's index numbers, use
SHOW INDEX, which displays a
table's indexes in the same order that
myisamchk sees them. Indexes are numbered
beginning with 1.

If keys are not packed (PACK_KEYS=0),
they have the same length, so when
myisamchk sorts and moves records, it
just overwrites record offsets in the index. If keys are
packed (PACK_KEYS=1),
myisamchk must unpack key blocks first,
then re-create indexes and pack the key blocks again. (In
this case, re-creating indexes is faster than updating
offsets for each index.)

5.6.4.5 Obtaining Table Information with myisamchk

To obtain a description of a MyISAM table or
statistics about it, use the commands shown here. The output
from these commands is explained later in this раздел.

Runs myisamchk in describe
mode to produce a description of your table. If you
start the MySQL server with external locking disabled,
myisamchk may report an error for a table
that is updated while it runs. However, because
myisamchk does not change the table in
describe mode, there is no risk of destroying data.

The tbl_name argument can be either
the name of a MyISAM table or the name of its
index file, as described in раздел 5.6.4.
Multiple tbl_name arguments can be given.

Suppose that a table named person has the
following structure. (The MAX_ROWS table
option is included so that in the example output from
myisamchk shown later, some values are
smaller and fit the output format more easily.)

The size of the data file pointer, in bytes. It is usually
2, 3, 4, or 5 bytes. Most tables manage with 2 bytes, but
this cannot be controlled from MySQL yet. For fixed tables,
this is a row address. For dynamic tables, this is a byte address.

Keyfile pointer

The size of the index file pointer, in bytes. It is usually
1, 2, or 3 bytes. Most tables manage with 2 bytes, but this
is calculated automatically by MySQL. It is always a block address.

Max datafile length

How long the table data file can become, in bytes.

Max keyfile length

How long the table index file can become, in bytes.

Record length

How much space each row takes, in bytes.

The table description part of the output
includes a list of all keys in the table. For each key,
myisamchk
displays some low-level information:

Key

This key's number. This value is shown only for the first
column of the key. If this value is missing, the line
corresponds to the second or later column of a
multiple-column key. For the table shown in the example,
there are two table description lines for
the second index. This indicates that it is a multiple-part
index with two parts.

Start

Where in the row this portion of the index starts.

Len

How long this portion of the index is. For packed numbers,
this should always be the full length of the column. For
strings, it may be shorter than the full length of the
indexed column, because you can index a prefix of a string
column. The total length of a multiple-part key is the sum
of the Len values for all key parts.

Index

Whether a key value can exist multiple times in the index.
Possible values are unique or
multip. (multiple).

Type

What data type this portion of the index has. This is a
MyISAM data type with the possible values
packed, stripped, or empty.

Root

Address of the root index block.

Block size

The size of each index block. By default this is 1024, but
the value may be changed at compile time when MySQL is
built from source.

Rec/key

This is a statistical value used by the optimizer. It tells
how many rows there are per value for this index. A unique
index always has a value of 1. This may be updated after a
table is loaded (or greatly changed) with myisamchk -a. If this is not updated at
all, a default value of 30 is given.

The last part of the output provides information about each column:

Field

The column number.

Start

The byte position of the column within table rows.

Length

The length of the column in bytes.

Nullpos, Nullbit

For columns that can be NULL,
MyISAM stores NULL
values as a flag in a byte. Depending on how many nullable
columns there are, there can be one or more bytes used for
this purpose. The Nullpos and
Nullbit values, if nonempty, indicate
which byte and bit contains that flag indicating whether the
column is NULL.

The position and number of bytes used to store
NULL flags is shown in the line for field
1. This is why there are six Field lines
for the person table even though it has
only five columns.

Type

The data type. The value may contain any of the following descriptors:

constant

All rows have the same value.

no endspace

Do not store endspace.

no endspace, not_always

Do not store endspace and do not do endspace compression
for all values.

What percentage of the keyblocks are used. When a table has
just been reorganized with myisamchk, the
values are very high (very near theoretical maximum).

Packed

MySQL tries to pack key values that have a common suffix.
This can only be used for indexes on
CHAR and
VARCHAR columns. For long
indexed strings that have similar leftmost parts, this can
significantly reduce the space used. In the preceding
example, the second key is 40 bytes long and a 97% reduction
in space is achieved.

Max levels

How deep the B-tree for this key is. Large tables with long
key values get high values.

Records

How many rows are in the table.

M.record length

The average row length. This is the exact row length for
tables with fixed-length rows, because all rows have
the same length.

Packed

MySQL strips spaces from the end of strings. The
Packed value indicates the percentage of
savings achieved by doing this.

Record space used

What percentage of the data file is used.

Empty space

What percentage of the data file is unused.

Blocks/Record

Average number of blocks per row (that is, how many links a
fragmented row is composed of). This is always 1.0 for
fixed-format tables. This value should stay as close to 1.0
as possible. If it gets too large, you can reorganize the
table. See
раздел 8.6.4, MyISAM Table Optimization.

Record blocks

How many blocks (links) are used. For fixed-format tables,
this is the same as the number of rows.

Delete blocks

How many blocks (links) are deleted.

Record data

How many bytes in the data file are used.

Deleted data

How many bytes in the data file are deleted (unused).

Lost space

If a row is updated to a shorter length, some space is lost.
This is the sum of all such losses, in bytes.

Linkdata

When the dynamic table format is used, row fragments are
linked with pointers (4 to 7 bytes each).
Linkdata is the sum of the amount of
storage used by all such pointers.

5.6.4.6 myisamchk Memory Usage

Memory allocation is important when you run
myisamchk. myisamchk uses
no more memory than its memory-related variables are set to. If
you are going to use myisamchk on very large
tables, you should first decide how much memory you want it to
use. The default is to use only about 3MB to perform repairs. By
using larger values, you can get myisamchk to
operate faster. For example, if you have more than 512MB RAM
available, you could use options such as these (in addition to
any other options you might specify):

Using --myisam_sort_buffer_size=16M is probably
enough for most cases.

Be aware that myisamchk uses temporary files
in TMPDIR. If TMPDIR
points to a memory file system, out of memory errors can easily
occur. If this happens, run myisamchk with the
--tmpdir=dir_name
option to specify a directory located on a file system that has more space.

When performing repair operations, myisamchk
also needs a lot of disk space:

Twice the size of the data file (the original file and a
copy). This space is not needed if you do a repair with
--quick; in this case,
only the index file is re-created. This space must
be available on the same file system as the original data
file, as the copy is created in the same
directory as the original.

Space for the new index file that replaces the old one. The
old index file is truncated at the start of the repair
operation, so you usually ignore this space. This space must
be available on the same file system as the original data file.

You can check the length of the keys and the
row_pointer_length with
myisamchk -dv
tbl_name (see
раздел 5.6.4.5). The
row_pointer_length and
number_of_rows values are the
Datafile pointer and Data
records values in the table description. To
determine the largest_key value,
check the Key lines in the table
description. The Len column indicates the
number of bytes for each key part. For a multiple-column
index, the key size is the sum of the Len
values for all key parts.

The default operation is update (-u). If a
recovery is done (-r), all writes and possibly
updates and deletes are done and errors are only counted. The
default log file name is myisam.log if no
log_file argument is given. If tables
are named on the command line, only those tables are updated.

Verbose mode. Print more output about what the program does.
This option can be given multiple times to produce more
and more output.

-w write_file

Specify the write file.

-V

Display version information.

5.6.6 myisampack Б─■ Generate Compressed, Read-Only MyISAM Tables

The myisampack utility compresses
MyISAM tables. myisampack
works by compressing each column in the table separately.
Usually, myisampack packs the data file 40% to 70%.

When the table is used later, the server reads into memory the
information needed to decompress columns. This results in much
better performance when accessing individual rows, because you
only have to uncompress exactly one row.

MySQL uses mmap() when possible to perform
memory mapping on compressed tables. If
mmap() does not work, MySQL falls back to
normal read/write file operations.

Please note the following:

If the mysqld server was invoked with
external locking disabled, it is not a good idea to invoke
myisampack if the table might be updated
by the server during the packing process. It is safest to
compress tables with the server stopped.

After packing a table, it becomes read only. This is
generally intended (such as when accessing packed
tables on a CD).

Each file name argument should be the name of an index
(.MYI) file. If you are not in the database
directory, you should specify the path name to the file. It is
permissible to omit the .MYI extension.

Produce a packed table even if it becomes larger than the
original or if the intermediate file from an earlier
invocation of myisampack exists.
(myisampack creates an intermediate file
named tbl_name.TMD
in the database directory while it compresses the table. If
you kill myisampack, the
.TMD file might not be deleted.)
Normally, myisampack exits with an error
if it finds that
tbl_name.TMD
exists. With --force,
myisampack packs the table anyway.

Join all tables named on the command line into a single
packed table big_tbl_name. All
tables that are to be combined must
have identical structure (same column names and types, same
indexes, and so forth).

big_tbl_name must not exist prior
to the join operation. All source tables named on the
command line to be merged into
big_tbl_name must exist. The
source tables are read for the join operation but not modified.

Wait and retry if the table is in use. If the
mysqld server was invoked with external
locking disabled, it is not a good idea to invoke
myisampack if the table might be updated
by the server during the packing process.

The following sequence of commands illustrates a typical
table compression session:

The number of columns containing values that are only
spaces. These occupy one bit.

empty-zero

The number of columns containing values that are only binary
zeros. These occupy one bit.

empty-fill

The number of integer columns that do not occupy the full
byte range of their type. These are changed to a smaller
type. For example, a BIGINT
column (eight bytes) can be stored as a
TINYINT column (one byte) if
all its values are in the range from -128
to 127.

pre-space

The number of decimal columns that are stored with leading
spaces. In this case, each value contains a count for the
number of leading spaces.

end-space

The number of columns that have a lot of trailing spaces. In
this case, each value contains a count for the number
of trailing spaces.

table-lookup

The column had only a small number of different values,
which were converted to an
ENUM before Huffman compression.

zero

The number of columns for which all values are zero.

Original trees

The initial number of Huffman trees.

After join

The number of distinct Huffman trees left after joining
trees to save some header space.

After a table has been compressed, the Field
lines displayed by myisamchk -dvv include
additional information about each column:

Type

The data type. The value may contain any of the following descriptors:

constant

All rows have the same value.

no endspace

Do not store endspace.

no endspace, not_always

Do not store endspace and do not do endspace compression
for all values.

5.6.7 mysql_config_editor Б─■ MySQL Configuration Utility

The mysql_config_editor utility enables you
to store authentication credentials in an encrypted login path
file named .mylogin.cnf. The file location
is the %APPDATA%\MySQL directory on Windows
and the current user's home directory on non-Windows systems.
The file can be read later by MySQL client programs to obtain
authentication credentials for connecting to MySQL Server.

The unencrypted format of the .mylogin.cnf
login path file consists of option groups, similar to other
option files. Each option group in
.mylogin.cnf is called a login
path, which is a group that permits only certain
options: host, user,
password, port and
socket. Think of a login path option group as a
set of options that specify which MySQL server to connect to and
which account to authenticate as. Here is an unencrypted example:

When you invoke a client program to connect to the server, the
client uses .mylogin.cnf in conjunction
with other option files. Its precedence is higher than other
option files, but less than options specified explicitly on the
client command line. For information about the order in which
option files are used, see раздел 5.2.6.

To specify an alternate login path file name, set the
MYSQL_TEST_LOGIN_FILE environment variable.
This variable is recognized by
mysql_config_editor, by standard MySQL
clients (mysql,
mysqladmin, and so forth), and by the
mysql-test-run.pl testing utility.

Without a --login-path
option, client programs read the same option groups from the
login path file that they read from other option files.
Consider this command:

shell> mysql

By default, the mysql client reads the
[client] and [mysql]
groups from other option files, so it reads them from the
login path file as well.

With a --login-path option,
client programs additionally read the named login path from
the login path file. The option groups read from other
option files remain the same. Consider this command:

shell> mysql --login-path=mypath

The mysql client 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. This permits passwords to be specified in a safer way
than on the command line even if
--no-defaults is present.

mysql_config_editor encrypts the
.mylogin.cnf file so it cannot be read as
cleartext, and its contents when decrypted by client programs
are used only in memory. In this way, passwords can be stored in
a file in non-cleartext format and used later without ever
needing to be exposed on the command line or in an environment
variable. mysql_config_editor provides a
print command for displaying the login path
file contents, but even in this case, password values are masked
so as never to appear in a way that other users can see them.

The encryption used by mysql_config_editor
prevents passwords from appearing in
.mylogin.cnf as cleartext and provides a
measure of security by preventing inadvertent password exposure.
For example, if you display a regular unencrypted
my.cnf option file on the screen, any
passwords it contains are visible for anyone to see. With
.mylogin.cnf, that is not true. But the
encryption used will not deter a determined attacker and you
should not consider it unbreakable. A user who can gain system
administration privileges on your machine to access your files
could decrypt the .mylogin.cnf file with some effort.

The login path file must be readable and writable to the current
user, and inaccessible to other users. Otherwise,
mysql_config_editor ignores it, and client
programs do not use it, either.

The first command line displays a general
mysql_config_editor help message, and ignores
the set command. The second command line
displays a help message specific to the set command.

Suppose that you want to establish a client
login path that defines your default connection parameters, and
an additional login path named remote for
connecting to the MySQL server the host
remote.example.com. You want to log in as follows:

By default, to the local server with a user name and
password of localuser and
localpass

To the remote server with a user name and password of
remoteuser and
remotepass

To set up the login paths in the
.mylogin.cnf file, use the following
set commands. Enter each command on a single
line, and enter the appropriate passwords when prompted:

The print command displays each login path as
a set of lines beginning with a group header indicating the
login path name in square brackets, followed by the option
values for the login path. Password values are masked and do not
appear as cleartext.

If you do not specify --all to display all
login paths or
--login-path=name to
display a named login path, the print command
displays the client login path by default, if
there is one.

As shown by the preceding example, the login path file can
contain multiple login paths. In this way,
mysql_config_editor makes it easy to set up
multiple personalities for connecting to
different MySQL servers, or for connecting to a given server
using different accounts. Any of these can be selected by name
later using the --login-path option when you
invoke a client program. For example, to connect to the remote
server, use this command:

shell> mysql --login-path=remote

Here, mysql reads the
[client] and [mysql]
option groups from other option files, and the
[client], [mysql], and
[remote] groups from the login path file.

To connect to the local server, use this command:

shell> mysql --login-path=client

Because mysql reads the
client and mysql login
paths by default, the
--login-path option does not add
anything in this case. That command is equivalent to this one:

shell> mysql

Options read from the login path file take precedence over
options read from other option files. Options read from login
path groups appearing later in the login path file take
precedence over options read from groups appearing earlier in the file.

mysql_config_editor adds login paths to the
login path file in the order you create them, so you should
create more general login paths first and more specific paths
later. If you need to move a login path within the file, you can
remove it, then recreate it to add it to the end.

When you use the set command with
mysql_config_editor to create a login path,
you need not specify all possible option values (host name, user
name, password, port, socket). Only those values given are
written to the path. Any missing values required later can be
specified when you invoke a client path to connect to the MySQL
server, either in other option files or on the command line. Any
options specified on the command line override those specified
in the login path file or other option files. For example, if
the credentials in the remote login path also
apply for the host remote2.example.com,
connect to the server on that host like this:

Remove a login path from the login path file, or modify a
login path by removing options from it.

This command removes from the login path only such options
as are specified with the --host,
--password, --port,
--socket, and --user
options. If none of those options are given,
remove removes the entire login path. For
example, this command removes only the user
option from the mypath login path rather
than the entire mypath login path:

The login path to remove or modify. The default login
path name is client if this option
is not given.

--password, -p

Remove the password from the login path.

--port, -P

Remove the TCP/IP port number from the login path.

--socket, -S

Remove the Unix socket file name from the login path.

--user, -u

Remove the user name from the login path.

--warn, -w

Warn and prompt the user for confirmation if the command
attempts to remove the default login path
(client) and
--login-path=client was not specified.
This option is enabled by default; use
--skip-warn to disable it.

This command writes to the login path only such options as
are specified with the --host,
--password, --port,
--socket, and --user
options. If none of those options are given,
mysql_config_editor writes the login path
as an empty group.

The login path to create. The default login path name is
client if this option is not given.

--password, -p

Prompt for a password to write to the login path. After
mysql_config_editor displays the
prompt, type the password and press Enter. To prevent
other users from seeing the password,
mysql_config_editor does not echo it.

To specify an empty password, press Enter at the
password prompt. The resulting login path written to the
login path file will include a line like this:

password =

--port=port_num,
-P port_num

The TCP/IP port number to write to the login path.

--socket=file_name,
-S file_name

The Unix socket file name to write to the login path.

--user=user_name,
-u user_name

The user name to write to the login path.

--warn, -w

Warn and prompt the user for confirmation if the command
attempts to overwrite an existing login path. This
option is enabled by default; use
--skip-warn to disable it.

5.6.8 mysqlbinlog Б─■ Utility for Processing Binary Log Files

The server's binary log consists of files containing
events that describe modifications to database
contents. The server writes these files in binary format. To
display their contents in text format, use the
mysqlbinlog utility. You can also use
mysqlbinlog to display the contents of relay
log files written by a slave server in a replication setup
because relay logs have the same format as binary logs. The
binary log and relay log are discussed further in
раздел 6.4.4, and
раздел 19.2.4.

For example, to display the contents of the binary log file
named binlog.000003, use this command:

shell> mysqlbinlog binlog.0000003

The output includes events contained in
binlog.000003. For statement-based logging,
event information includes the SQL statement, the ID of the
server on which it was executed, the timestamp when the
statement was executed, how much time it took, and so forth. For
row-based logging, the event indicates a row change rather than
an SQL statement. See
раздел 19.2.1, Replication Formats, for
information about logging modes.

Events are preceded by header comments that provide additional
information. For example:

In the first line, the number following at
indicates the file offset, or starting position, of the event in
the binary log file.

The second line starts with a date and time indicating when the
statement started on the server where the event originated. For
replication, this timestamp is propagated to slave servers.
server id is the
server_id value of the server
where the event originated. end_log_pos
indicates where the next event starts (that is, it is the end
position of the current event + 1). thread_id
indicates which thread executed the event.
exec_time is the time spent executing the
event, on a master server. On a slave, it is the difference of
the end execution time on the slave minus the beginning
execution time on the master. The difference serves as an
indicator of how much replication lags behind the master.
error_code indicates the result from
executing the event. Zero means that no error occurred.

When using event groups, the file offsets of events may be
grouped together and the comments of events may be grouped
together. Do not mistake these grouped events for blank file offsets.

The output from mysqlbinlog can be
re-executed (for example, by using it as input to
mysql) to redo the statements in the log.
This is useful for recovery operations after a server crash. For
other usage examples, see the discussion later in this раздел
and in раздел 8.5.

When running mysqlbinlog against a large
binary log, be careful that the filesystem has enough space for
the resulting files. To configure the directory that
mysqlbinlog uses for temporary files, use the
TMPDIR environment variable.

mysqlbinlog supports the following options,
which can be specified on the command line or in the
[mysqlbinlog] and [client]
groups of an option file. For information about option files
used by MySQL programs, see раздел 5.2.6
.

Extract only those events created by the server having the given server ID

--server-id-bits

Tell mysqlbinlog how to interpret server IDs in binary log when log was written by a mysqld having its server-id-bits set to less than the maximum; supported only by MySQL Cluster version of mysqlbinlog

This option determines when events should be displayed
encoded as base-64 strings using
BINLOG statements. The option
has these permissible values (not case sensitive):

AUTO ("automatic") or
UNSPEC ("unspecified") displays
BINLOG statements
automatically when necessary (that is, for format
description events and row events). If no
--base64-output
option is given, the effect is the same as
--base64-output=AUTO.

Automatic BINLOG
display is the only safe behavior if you intend to use
the output of mysqlbinlog to
re-execute binary log file contents. The other option
values are intended only for debugging or testing
purposes because they may produce output that does not
include all events in executable form.

NEVER causes
BINLOG statements not to
be displayed. mysqlbinlog exits with
an error if a row event is found that must be displayed
using BINLOG.

DECODE-ROWS specifies to
mysqlbinlog that you intend for row
events to be decoded and displayed as commented SQL
statements by also specifying the
--verbose option.
Like NEVER,
DECODE-ROWS suppresses display of
BINLOG statements, but
unlike NEVER, it does not exit with
an error if a row event is found.

This option is used to test a MySQL server for support of
the BINLOG_DUMP_NON_BLOCK connection
flag, which was inadvertently removed in MySQL 5.6.5, and
restored in MySQL 5.7.5 (Bug #18000079, Bug #71178). It is
not required for normal operations.

The effective default and minimum values for this option
depend on whether mysqlbinlog is run in
blocking mode or non-blocking mode. When
mysqlbinlog is run in blocking mode, the
default (and minimum) value is 1; when run in non-blocking
mode, the default (and minimum) value is 0.

The effects of this option depend on whether the
statement-based or row-based logging format is in use, in
the same way that the effects of
--binlog-do-db depend on
whether statement-based or row-based logging is in use.

While db_name is the default
database, statements are output whether they modify
tables in db_name or a different database.

Unless db_name is selected as
the default database, statements are not output, even if
they modify tables in db_name.

There is an exception for CREATE
DATABASE, ALTER
DATABASE, and DROP
DATABASE. The database being
created, altered, or dropped is
considered to be the default database when determining
whether to output the statement.

Suppose that the binary log was created by executing these
statements using statement-based-logging:

Row-based logging. mysqlbinlog outputs only entries that
change tables belonging to
db_name. The default database
has no effect on this. Suppose that the binary log just
described was created using row-based logging rather than
statement-based logging. mysqlbinlog
--database=test outputs only those entries that
modify t1 in the test database,
regardless of whether USE
was issued or what the default database is.

If a server is running with
binlog_format set to
MIXED and you want it to be possible to
use mysqlbinlog with the
--database option, you
must ensure that tables that are modified are in the
database selected by USE. (In
particular, no cross-database updates should be used.)

When used together with the
--rewrite-db option, the
--rewrite-db option is applied first; then
the --database option is applied, using the
rewritten database name. The order in which the options are
provided makes no difference in this regard.

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,
mysqlbinlog normally reads the
[client] and [mysqlbinlog] groups. If the
--defaults-group-suffix=_other
option is given, mysqlbinlog also reads
the [client_other] and
[mysqlbinlog_other] groups.

Disable binary logging. This is useful for avoiding an
endless loop if you use the
--to-last-log option and
are sending the output to the same MySQL server. This option
also is useful when restoring after a crash to avoid
duplication of the statements you have logged.

This option requires that you have the
SUPER privilege. It causes
mysqlbinlog to include a SET
sql_log_bin = 0 statement in its output to disable
binary logging of the remaining output. The
SET
statement is ineffective unless you have the
SUPER privilege.

With this option, if mysqlbinlog reads a
binary log event that it does not recognize, it prints a
warning, ignores the event, and continues. Without this
option, mysqlbinlog stops if it reads
such an event.

Tell the MySQL Server to use idempotent mode while
processing updates; this causes suppression of any
duplicate-key or key-not-found errors that the server
encounters in the current session while processing updates.
This option may prove useful whenever it is desirable or
necessary to replay one or more binary logs to a MySQL
Server which may not contain all of the data to which the logs refer.

The scope of effect for this option includes the current
mysqlbinlog client and session only.

Read options from the named login path in the
.mylogin.cnf login path file. A
login path is an option group containing
options that specify which MySQL server to connect to and
which account to authenticate as. To create or modify a
login path file, use the
mysql_config_editor utility. See
раздел 5.6.7.

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.

The exception is that the .mylogin.cnf
file, if it exists, is read in all cases. This permits
passwords to be specified in a safer way than on the command
line even when
--no-defaults is used.
(.mylogin.cnf is created by the
mysql_config_editor utility. See
раздел 5.6.7.)

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,
mysqlbinlog prompts for one.

Specifying a password on the command line should be
considered insecure. See
раздел 7.1.2.1. You can use an
option file to avoid giving the password on the command line.

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
раздел 5.2.2.

By default, mysqlbinlog reads binary log
files and writes events in text format. The
--raw option tells
mysqlbinlog to write them in their
original binary format. Its use requires that
--read-from-remote-server
also be used because the files are requested from a server.
mysqlbinlog writes one output file for
each file read from the server. The
--raw option can be used
to make a backup of a server's binary log. With the
--stop-never option, the
backup is live because
mysqlbinlog stays connected to the
server. By default, output files are written in the current
directory with the same names as the original log files.
Output file names can be modified using the
--result-file option.
For more information, see
раздел 5.6.8.3.

Read binary logs from a MySQL server with the
COM_BINLOG_DUMP or
COM_BINLOG_DUMP_GTID commands by setting
the option value to either
BINLOG-DUMP-NON-GTIDS or
BINLOG-DUMP-GTIDS, respectively. If
--read-from-remote-master=BINLOG-DUMP-GTIDS
is combined with
--exclude-gtids,
transactions can be filtered out on the master, avoiding
unnecessary network traffic.

Read the binary log from a MySQL server rather than reading
a local log file. Any connection parameter options are
ignored unless this option is given as well. These options
are --host,
--password,
--port,
--protocol,
--socket, and
--user.

This option requires that the remote server be running. It
works only for binary log files on the remote server, not
relay log files.

Without the --raw
option, this option indicates the file to which
mysqlbinlog writes text output. With
--raw,
mysqlbinlog writes one binary output file
for each log file transferred from the server, writing them
by default in the current directory using the same names as
the original log file. In this case, the
--result-file option
value is treated as a prefix that modifies output file names.

When reading from a row-based or statement-based log,
rewrite all occurrences of
from_name to
to_name. Rewriting is done on the
rows, for row-based logs, as well as on the
USE clauses, for
statement-based logs.

Statements in which table names are qualified with
database names are not rewritten to use the new name when
using this option.

The rewrite rule employed as a value for this option is a
string having the form
'from_name->to_name',
as shown previously, and for this reason must be enclosed by
quotation marks.

When used together with the
--database option, the
--rewrite-db option is applied first; then
--database option is applied, using the
rewritten database name. The order in which the options are
provided makes no difference in this regard.

This means that, for example, if
mysqlbinlog is started with
--rewrite-db='mydb->yourdb'
--database=yourdb, then all updates to any tables
in databases mydb and
yourdb are included in the output. On the
other hand, if it is started with
--rewrite-db='mydb->yourdb' --database=mydb, then
mysqlbinlog outputs no statements at all:
since all updates to mydb are first
rewritten as updates to yourdb before
applying the --database option, there
remain no updates that match
--database=mydb.

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 deprecated and will be removed in a future
MySQL release. It is always enabled and attempting to disable it
(--skip-secure-auth,
--secure-auth=0)
produces an error.

Passwords that use the pre-4.1 hashing method are less
secure than passwords that use the native password hashing
method and should be avoided. Pre-4.1 passwords are
deprecated and support for them has been removed.

Start reading the binary log at the first event having a
timestamp equal to or later than the
datetime argument. The
datetime value is relative to the
local time zone on the machine where you run
mysqlbinlog. The value should be in a
format accepted for the
DATETIME or
TIMESTAMP
data types. For example:

Stop reading the binary log at the first event having a
timestamp equal to or later than the
datetime argument. This option is
useful for point-in-time recovery. See the description of
the
--start-datetime
option for information about the
datetime value.

The protocols permitted by the client for encrypted
connections. The value is a comma-separated list containing
one or more protocol names. The protocols that can be named
for this option depend on the SSL library used to compile
MySQL. For details, see
раздел 7.4.3.

Do not stop at the end of the requested binary log from a
MySQL server, but rather continue printing until the end of
the last binary log. If you send the output to the same
MySQL server, this may lead to an endless loop. This option requires
--read-from-remote-server.

Reconstruct row events and display them as commented SQL
statements. If this option is given twice (by passing in
either "-vv" or "--verbose --verbose"), the output includes
comments to indicate column data types and some metadata,
and row query log events if so configured.

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

open_files_limit

Specify the number of open file descriptors to reserve.

You can pipe the output of mysqlbinlog into
the mysql client to execute the events
contained in the binary log. This technique is used to recover
from a crash when you have an old backup (see
раздел 8.5). For example:

You can also redirect the output of
mysqlbinlog to a text file instead, if you
need to modify the statement log first (for example, to remove
statements that you do not want to execute for some reason).
After editing the file, execute the statements that it contains
by using it as input to the mysql program:

When mysqlbinlog is invoked with the
--start-position option, it
displays only those events with an offset in the binary log
greater than or equal to a given position (the given position
must match the start of one event). It also has options to stop
and start when it sees an event with a given date and time. This
enables you to perform point-in-time recovery using the
--stop-datetime option (to
be able to say, for example, roll forward my databases to
how they were today at 10:30 a.m.).

If you have more than one binary log to execute on the MySQL
server, the safe method is to process them all using a single
connection to the server. Here is an example that demonstrates
what may be unsafe:

Processing binary logs this way using multiple connections to
the server causes problems if the first log file contains a
CREATE TEMPORARY
TABLE statement and the second log contains a
statement that uses the temporary table. When the first
mysql process terminates, the server drops
the temporary table. When the second mysql
process attempts to use the table, the server reports
unknown table.

To avoid problems like this, use a singlemysql process to execute the contents of all
binary logs that you want to process. Here is one way to do so:

shell> mysqlbinlog binlog.000001 binlog.000002 | mysql -u root -p

Another approach is to write all the logs to a single file and
then process the file:

mysqlbinlog can produce output that
reproduces a LOAD
DATA INFILE operation without the original data file.
mysqlbinlog copies the data to a temporary
file and writes a LOAD DATA LOCAL
INFILE statement that refers to the file. The default
location of the directory where these files are written is
system-specific. To specify a directory explicitly, use the
--local-load option.

The temporary files created for
LOAD DATA
LOCAL statements are not
automatically deleted because they are needed until you
actually execute those statements. You should delete the
temporary files yourself after you no longer need the
statement log. The files can be found in the temporary file
directory and have names like
original_file_name-#-#.

Row data for a single table that should be created. Used in MySQL 5.1.5
to 5.1.17.

15

PRE_GA_UPDATE_ROWS_EVENT

Row data for a single table that needs to be updated. Used in MySQL
5.1.5 to 5.1.17.

16

PRE_GA_DELETE_ROWS_EVENT

Row data for a single table that should be deleted. Used in MySQL 5.1.5
to 5.1.17.

17

WRITE_ROWS_EVENT

Row data for a single table that should be created. Used in MySQL 5.1.18
and later.

18

UPDATE_ROWS_EVENT

Row data for a single table that needs to be updated. Used in MySQL
5.1.18 and later.

19

DELETE_ROWS_EVENT

Row data for a single table that should be deleted. Used in MySQL 5.1.18
and later.

1a

INCIDENT_EVENT

Something out of the ordinary happened. Added in MySQL 5.1.18.

Master ID: The server ID of the master
that created the event.

Size: The size in bytes of the event.

Master Pos: The position of the next
event in the original master log file.

Flags: 16 flags. The following flags are
used. The others are reserved for future use.

Flag

Name

Meaning

01

LOG_EVENT_BINLOG_IN_USE_F

Log file correctly closed. (Used only in
FORMAT_DESCRIPTION_EVENT.) If
this flag is set (if the flags are, for example,
'01 00') in a
FORMAT_DESCRIPTION_EVENT, the log
file has not been properly closed. Most probably
this is because of a master crash (for example, due
to power failure).

02

Reserved for future use.

04

LOG_EVENT_THREAD_SPECIFIC_F

Set if the event is dependent on the connection it was executed in (for
example, '04 00'), for example,
if the event uses temporary tables.

08

LOG_EVENT_SUPPRESS_USE_F

Set in some circumstances when the event is not dependent on the default
database.

5.6.8.2 mysqlbinlog Row Event Display

The following examples illustrate how
mysqlbinlog displays row events that specify
data modifications. These correspond to events with the
WRITE_ROWS_EVENT, UPDATE_ROWS_EVENT, and
DELETE_ROWS_EVENT type codes. The
--base64-output=DECODE-ROWS
and --verbose options may be
used to affect row event output.

Suppose that the server is using row-based binary logging and
that you execute the following sequence of statements:

By default, mysqlbinlog displays row events
encoded as base-64 strings using
BINLOG statements. Omitting
extraneous lines, the output for the row events produced by the
preceding statement sequence looks like this:

You should not suppress BINLOG
statements if you intend to re-execute
mysqlbinlog output.

The SQL statements produced by
--verbose for row events are
much more readable than the corresponding
BINLOG statements. However, they
do not correspond exactly to the original SQL statements that
generated the events. The following limitations apply:

The original column names are lost and replaced by
@N, where
N is a column number.

Character set information is not available in the binary
log, which affects string column display:

There is no distinction made between corresponding
binary and nonbinary string types
(BINARY and
CHAR,
VARBINARY and
VARCHAR,
BLOB and
TEXT). The output uses a
data type of STRING for fixed-length
strings and VARSTRING for
variable-length strings.

For multibyte character sets, the maximum number of
bytes per character is not present in the binary log, so
the length for string types is displayed in bytes rather
than in characters. For example,
STRING(4) will be used as the data
type for values from either of these column types:

CHAR(4) CHARACTER SET latin1
CHAR(2) CHARACTER SET ucs2

Due to the storage format for events of type
UPDATE_ROWS_EVENT,
UPDATE statements are
displayed with the WHERE clause
preceding the SET clause.

Proper interpretation of row events requires the information
from the format description event at the beginning of the binary
log. Because mysqlbinlog does not know in
advance whether the rest of the log contains row events, by
default it displays the format description event using a
BINLOG statement in the initial
part of the output.

If the binary log is known not to contain any events requiring a
BINLOG statement (that is, no row
events), the
--base64-output=NEVER option
can be used to prevent this header from being written.

5.6.8.3 Using mysqlbinlog to Back Up Binary Log Files

By default, mysqlbinlog reads binary log
files and displays their contents in text format. This enables
you to examine events within the files more easily and to
re-execute them (for example, by using the output as input to
mysql). mysqlbinlog can
read log files directly from the local file system, or, with the
--read-from-remote-server
option, it can connect to a server and request binary log
contents from that server. mysqlbinlog writes
text output to its standard output, or to the file named as the
value of the
--result-file=file_name
option if that option is given.

mysqlbinlog can read binary log files and
write new files containing the same contentБ─■that is, in
binary format rather than text format. This capability enables
you to easily back up a binary log in its original format.
mysqlbinlog can make a static backup, backing
up a set of log files and stopping when the end of the last file
is reached. It can also make a continuous (live)
backup, staying connected to the server when it reaches the end
of the last log file and continuing to copy new events as they
are generated. In continuous-backup operation,
mysqlbinlog runs until the connection ends
(for example, when the server exits) or
mysqlbinlog is forcibly terminated. When the
connection ends, mysqlbinlog does not wait
and retry the connection, unlike a slave replication server. To
continue a live backup after the server has been restarted, you
must also restart mysqlbinlog.

Binary log backup requires that you invoke
mysqlbinlog with two options at minimum:

The
--read-from-remote-server
(or -R) option tells
mysqlbinlog to connect to a server and
request its binary log. (This is similar to a slave
replication server connecting to its master server.)

To back up a server's binary log files with
mysqlbinlog, you must specify file names that
actually exist on the server. If you do not know the names,
connect to the server and use the SHOW
BINARY LOGS statement to see the current names.
Suppose that the statement produces this output:

The first command specifies every file name explicitly. The
second names only the first file and uses
--to-last-log to read
through the last. A difference between these commands is
that if the server happens to open
binlog.000133 before
mysqlbinlog reaches the end of
binlog.000132, the first command will
not read it, but the second command will.

To make a live backup in which
mysqlbinlog starts with
binlog.000130 to copy existing log
files, then stays connected to copy new events as the
server generates them:

Output File Naming

Without --raw,
mysqlbinlog produces text output and the
--result-file option, if
given, specifies the name of the single file to which all output
is written. With --raw,
mysqlbinlog writes one binary output file for
each log file transferred from the server. By default,
mysqlbinlog writes the files in the current
directory with the same names as the original log files. To
modify the output file names, use the
--result-file option. In
conjunction with --raw, the
--result-file option value
is treated as a prefix that modifies the output file names.

Suppose that a server currently has binary log files named
binlog.000999 and up. If you use
mysqlbinlog --raw to back up the files, the
--result-file option
produces output file names as shown in the following table. You
can write the files to a specific directory by beginning the
--result-file value with the
directory path. If the
--result-file value consists
only of a directory name, the value must end with the pathname
separator character. Output files are overwritten if they exist.

Example: mysqldump + mysqlbinlog for Backup and Restore

The following example describes a simple scenario that shows how
to use mysqldump and
mysqlbinlog together to back up a server's
data and binary log, and how to use the backup to restore the
server if data loss occurs. The example assumes that the server
is running on host host_name and its
first binary log file is named
binlog.000999. Enter each command on a single line.

You might find it easier to copy the backup files (dump file and
binary log files) to the server host to make it easier to
perform the restore operation, or if MySQL does not allow remote
root access.

5.6.8.4 Specifying the mysqlbinlog Server ID

When invoked with the --read-from-remote-server
option, mysqlbinlog connects to a MySQL
server, specifies a server ID to identify itself, and requests
binary log files from the server. You can use
mysqlbinlog to request log files from a
server in several ways:

Specify an explicitly named set of files: For each file,
mysqlbinlog connects and issues a
Binlog dump command. The server sends the
file and disconnects. There is one connection per file.

Specify the beginning file and
--to-last-log:
mysqlbinlog connects and issues a
Binlog dump command for all files. The
server sends all files and disconnects.

Specify the beginning file and
--stop-never (which
implies --to-last-log):
mysqlbinlog connects and issues a
Binlog dump command for all files. The
server sends all files, but does not disconnect after
sending the last one.

Thus, for the first two ways of requesting files, the server
disconnects because mysqlbinlog specifies a
server ID of 0. It does not disconnect if
--stop-never is given
because mysqlbinlog
specifies a nonzero server ID.

Normally, mysqldumpslow groups queries that
are similar except for the particular values of number and
string data values. It abstracts these values to
N and 'S' when displaying
summary output. The -a and -n
options can be used to modify value abstracting behavior.

5.7 MySQL Program Development Utilities

This раздел describes some utilities that you may find useful when
developing MySQL programs.

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

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

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

5.7.1 mysql_config Б─■ Display Options for Compiling Clients

mysql_config provides you with useful
information for compiling your MySQL client and connecting it to
MySQL. It is a shell script, so it is available only on Unix and
Unix-like systems.

pkg-config can be used as an alternative to
mysql_config for obtaining information such
as compiler flags or link libraries required to compile MySQL
applications. For more information, see
раздел 25.8.4.2.

For binary distributions for Solaris,
mysql_config does not provide arguments for
linking with the embedded library. To get linking arguments
for the embedded library, use the
mysql_server_config script instead.

C Compiler flags to find include files and critical compiler
flags and defines used when compiling the
libmysqlclient library. The options
returned are tied to the specific compiler that was used
when the library was created and might clash with the
settings for your own compiler. Use
--include for more
portable options that contain only include paths.

Libraries and options required to link with the thread-safe
MySQL client library. In MySQL 8.0, all client
libraries are thread-safe, so this option need not be used.
The --libs
option can be used in all cases.

You can use mysql_config within a command
line using backticks to include the output that it produces for
particular options. For example, to compile and link a MySQL
client program, use mysql_config as follows:

5.7.2 my_print_defaults Б─■ Display Options from Option Files

my_print_defaults displays the options that
are present in option groups of option files. The output
indicates what options will be used by programs that read the
specified option groups. For example, the
mysqlcheck program reads the
[mysqlcheck] and [client]
option groups. To see what options are present in those groups
in the standard option files, invoke
my_print_defaults like this:

Read options from the named login path in the
.mylogin.cnf login path file. A
login path is an option group containing
options that specify which MySQL server to connect to and
which account to authenticate as. To create or modify a
login path file, use the
mysql_config_editor utility. See
раздел 5.6.7.

The symbols file should include the output from the nm
--numeric-sort mysqld command. The numeric dump file
should contain a numeric stack track from
mysqld. If no numeric dump file is named on
the command line, the stack trace is read from the standard input.

from represents a string to look for
and to represents its replacement.
There can be one or more pairs of strings.

Use the -- option to indicate where the
string-replacement list ends and the file names begin. In this
case, any file named on the command line is modified in place,
so you may want to make a copy of the original before converting
it. replace prints a message
indicating which of the input files it actually modifies.

If the -- option is not given,
replace reads the standard input and writes
to the standard output.

replace uses a finite state machine to match
longer strings first. It can be used to swap strings. For
example, the following command swaps a and
b in the given files,
file1 and file2:

5.9 MySQL Program Environment Variables

This раздел lists environment variables that are used directly or
indirectly by MySQL. Most of these can also be found in other places
in this manual.

Options on the command line take precedence over values specified in
option files and environment variables, and values in option files
take precedence over values in environment variables. In many cases,
it is preferable to use an option file instead of environment
variables to modify the behavior of MySQL. See
раздел 5.2.6.

MYSQL_TEST_LOGIN_FILE is the path name of the
login path file (the file created by
mysql_config_editor). If not set, the default
value is %APPDATA%\MySQL\.mylogin.cnf directory
on Windows and $HOME/.mylogin.cnf on
non-Windows systems. See
раздел 5.6.7.

The MYSQL_TEST_TRACE_DEBUG and
MYSQL_TEST_TRACE_CRASH variables control the test
protocol trace client plugin, if MySQL is built with that plugin
enabled. For more information, see
раздел 26.2.4.11.1.

The default UMASK and
UMASK_DIR values are 0640 and
0750, respectively. MySQL assumes that the value
for UMASK or UMASK_DIR is in
octal if it starts with a zero. For example, setting
UMASK=0600 is equivalent to
UMASK=384 because 0600 octal is 384 decimal.

The UMASK and UMASK_DIR
variables, despite their names, are used as modes, not masks:

If UMASK is set, mysqld
uses ($UMASK | 0600) as the mode for file
creation, so that newly created files have a mode in the range
from 0600 to 0666 (all values octal).

If UMASK_DIR is set,
mysqld uses ($UMASK_DIR |
0700) as the base mode for directory creation, which
then is AND-ed with ~(~$UMASK & 0666), so
that newly created directories have a mode in the range from
0700 to 0777 (all values octal). The AND operation may remove
read and write permissions from the directory mode, but
not execute permissions.

It may be necessary to set PKG_CONFIG_PATH if you
use pkg-config for building MySQL programs. See
раздел 25.8.4.2.