Table of Contents
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.
There are many different programs in a MySQL installation. This section provides a brief overview of them. Later sections provide a more detailed description of each one, with the exception of NDB Cluster programs. Each program's description indicates its invocation syntax and the options that it supports. Section 22.4, “NDB Cluster Programs”, describes programs specific to NDB Cluster.
Most MySQL distributions include all of these programs, except for those programs that are platform-specific. (For example, the server startup scripts are not used on Windows.) The exception is that RPM distributions are more specialized. There is one RPM for the server, another for client programs, and so forth. If you appear to be missing one or more programs, see Chapter 2, Installing and Upgrading MySQL, for information on types of distributions and what they contain. It may be that you have a distribution that does not include all programs and you need to install an additional package.
Each MySQL program takes many different options. Most programs
provide a --help
option that you can use to get a
description of the program's different options. For example, try
mysql --help.
You can override default option values for MySQL programs by specifying options on the command line or in an option file. See Section 4.2, “Using MySQL Programs”, for general information on invoking programs and specifying program options.
The MySQL server, mysqld, is the main program that does most of the work in a MySQL installation. The server is accompanied by several related scripts that assist you in starting and stopping the server:
The SQL daemon (that is, the MySQL server). To use client programs, mysqld must be running, because clients gain access to databases by connecting to the server. See Section 4.3.1, “mysqld — The MySQL Server”.
A server startup script. mysqld_safe attempts to start mysqld. See Section 4.3.2, “mysqld_safe — MySQL Server Startup Script”.
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 Section 4.3.3, “mysql.server — MySQL Server Startup Script”.
A server startup script that can start or stop multiple servers installed on the system. See Section 4.3.4, “mysqld_multi — Manage Multiple MySQL Servers”.
Several programs perform setup operations during MySQL installation or upgrading:
This program is used during the MySQL build/installation process. It compiles error message files from the error source files. See Section 4.4.1, “comp_err — Compile MySQL Error Message File”.
This program enables you to improve the security of your MySQL installation. See Section 4.4.2, “mysql_secure_installation — Improve MySQL Installation Security”.
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 Section 4.4.3, “mysql_ssl_rsa_setup — Create SSL/RSA Files”.
This program loads the time zone tables in the
mysql
database using the contents of the
host system zoneinfo
database (the set of files describing time zones). See
Section 4.4.4, “mysql_tzinfo_to_sql — Load the Time Zone Tables”.
This program is used after a MySQL upgrade operation. It updates the grant tables with any changes that have been made in newer versions of MySQL, and checks tables for incompatibilities and repairs them if necessary. See Section 4.4.5, “mysql_upgrade — Check and Upgrade MySQL Tables”.
MySQL client programs that connect to the MySQL server:
The command-line tool for interactively entering SQL statements or executing them from a file in batch mode. See Section 4.5.1, “mysql — The MySQL Command-Line Client”.
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 Section 4.5.2, “mysqladmin — Client for Administering a MySQL Server”.
A table-maintenance client that checks, repairs, analyzes, and optimizes tables. See Section 4.5.3, “mysqlcheck — A Table Maintenance Program”.
A client that dumps a MySQL database into a file as SQL, text, or XML. See Section 4.5.4, “mysqldump — A Database Backup Program”.
A client that imports text files into their respective tables
using LOAD DATA
. See
Section 4.5.5, “mysqlimport — A Data Import Program”.
A client that dumps a MySQL database into a file as SQL. See Section 4.5.6, “mysqlpump — A Database Backup Program”.
MySQL Shell is an advanced client and code editor for MySQL Server. See MySQL Shell 8.0 (part of MySQL 8.0). In addition to the provided SQL functionality, similar to mysql, MySQL Shell provides scripting capabilities for JavaScript and Python and includes APIs for working with MySQL. X DevAPI enables you to work with both relational and document data, see Chapter 20, Using MySQL as a Document Store. AdminAPI enables you to work with InnoDB cluster, see Chapter 21, InnoDB Cluster.
A client that displays information about databases, tables, columns, and indexes. See Section 4.5.7, “mysqlshow — Display Database, Table, and Column Information”.
A client that is designed to emulate client load for a MySQL server and report the timing of each stage. It works as if multiple clients are accessing the server. See Section 4.5.8, “mysqlslap — Load Emulation Client”.
MySQL administrative and utility programs:
An offline InnoDB
offline file checksum
utility. See Section 4.6.2, “innochecksum — Offline InnoDB File Checksum Utility”.
A utility that displays information about full-text indexes in
MyISAM
tables. See
Section 4.6.3, “myisam_ftdump — Display Full-Text Index information”.
A utility to describe, check, optimize, and repair
MyISAM
tables. See
Section 4.6.4, “myisamchk — MyISAM Table-Maintenance Utility”.
A utility that processes the contents of a
MyISAM
log file. See
Section 4.6.5, “myisamlog — Display MyISAM Log File Contents”.
A utility that compresses MyISAM
tables to
produce smaller read-only tables. See
Section 4.6.6, “myisampack — Generate Compressed, Read-Only MyISAM Tables”.
A utility that enables you to store authentication credentials
in a secure, encrypted login path file named
.mylogin.cnf
. See
Section 4.6.7, “mysql_config_editor — MySQL Configuration Utility”.
A utility for reading statements from a binary log. The log of executed statements contained in the binary log files can be used to help recover from a crash. See Section 4.6.8, “mysqlbinlog — Utility for Processing Binary Log Files”.
A utility to read and summarize the contents of a slow query log. See Section 4.6.9, “mysqldumpslow — Summarize Slow Query Log Files”.
MySQL program-development utilities:
A shell script that produces the option values needed when compiling MySQL programs. See Section 4.7.1, “mysql_config — Display Options for Compiling Clients”.
A utility that shows which options are present in option groups of option files. See Section 4.7.2, “my_print_defaults — Display Options from Option Files”.
Miscellaneous utilities:
A utility that decompresses mysqlpump output that was created using LZ4 compression. See Section 4.8.1, “lz4_decompress — Decompress mysqlpump LZ4-Compressed Output”.
A utility that displays the meaning of system or MySQL error codes. See Section 4.8.2, “perror — Display MySQL Error Message Information”.
A utility that decompresses mysqlpump output that was created using ZLIB compression. See Section 4.8.3, “zlib_decompress — Decompress mysqlpump ZLIB-Compressed Output”.
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 Section 4.9, “MySQL Program Environment Variables”.
Use of MYSQL_PWD
is insecure. See
Section 6.1.2.1, “End-User Guidelines for Password Security”.
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.
shell>mysql --user=root test
shell>mysqladmin extended-status variables
shell>mysqlshow --help
shell>mysqldump -u root personnel
Arguments that begin with a single or double dash
(-
, --
) specify program
options. Options typically indicate the type of connection a
program should make to the server or affect its operational mode.
Option syntax is described in Section 4.2.4, “Specifying Program Options”.
Nonoption arguments (arguments with no leading dash) provide
additional information to the program. For example, the
mysql program interprets the first nonoption
argument as a database name, so the command mysql
--user=root test
indicates that you want to use the
test
database.
Later sections that describe individual programs indicate which options a program supports and describe the meaning of any additional nonoption arguments.
Some options are common to a number of programs. The most
frequently used of these are the
--host
(or -h
),
--user
(or -u
),
and --password
(or
-p
) options that specify connection parameters.
They indicate the host where the MySQL server is running, and the
user name and password of your MySQL account. All MySQL client
programs understand these options; they enable you to specify
which server to connect to and the account to use on that server.
Other connection options are
--port
(or -P
) to
specify a TCP/IP port number and
--socket
(or -S
)
to specify a Unix socket file on Unix (or named pipe name on
Windows). For more information on options that specify connection
options, see Section 4.2.2, “Connecting to the MySQL Server”.
You may find it necessary to invoke MySQL programs using the path
name to the bin
directory in which they are
installed. This is likely to be the case if you get a
“program not found” error whenever you attempt to run
a MySQL program from any directory other than the
bin
directory. To make it more convenient to
use MySQL, you can add the path name of the
bin
directory to your PATH
environment variable setting. That enables you to run a program by
typing only its name, not its entire path name. For example, if
mysql is installed in
/usr/local/mysql/bin
, you can run the program
by invoking it as mysql, and it is not
necessary to invoke it as
/usr/local/mysql/bin/mysql.
Consult the documentation for your command interpreter for
instructions on setting your PATH
variable. The
syntax for setting environment variables is interpreter-specific.
(Some information is given in
Section 4.2.11, “Setting Environment Variables”.) After modifying
your PATH
setting, open a new console window on
Windows or log in again on Unix so that the setting goes into
effect.
This section describes how to establish a connection to the MySQL server. For additional information if you are unable to connect, see Section 6.2.9, “Troubleshooting Problems Connecting to MySQL”.
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.
The examples here use the mysql client program, but the principles apply to other clients such as mysqldump, mysqladmin, or mysqlshow. For more information on connecting clients such as MySQL Shell by specifying a path, see Section 4.2.3, “Connecting Using a URI or Key-Value Pairs”.
This command invokes mysql without specifying any connection parameters explicitly:
shell> mysql
Because there are no parameter options, the default values apply:
The default host name is localhost
. On
Unix, this has a special meaning, as described later.
The default user name is ODBC
on Windows or
your Unix login name on Unix.
No password is sent if neither -p
nor
--password
is given.
For mysql, the first nonoption argument is taken as the name of the default database. If there is no such option, mysql does not select a default database.
To specify the host name and user name explicitly, as well as a password, supply appropriate options on the command line:
shell>mysql --host=localhost --user=myname --password=
shell>password
mydbmysql -h localhost -u myname -p
password
mydb
For password options, the password value is optional:
If you use a -p
or
--password
option and specify
the password value, there must be no
space between -p
or
--password=
and the password
following it.
If you use a -p
or
--password
option but do not
specify the password value, the client program prompts you to
enter the password. The password is not displayed as you enter
it. This is more secure than giving the password on the
command line. Other users on your system may be able to see a
password specified on the command line by executing a command
such as ps auxw. See
Section 6.1.2.1, “End-User Guidelines for Password Security”.
As just mentioned, including the password value on the command
line can be a security risk. To avoid this problem, specify the
--password
or -p
option without
any following password value:
shell>mysql --host=localhost --user=myname --password mydb
shell>mysql -h localhost -u myname -p mydb
When the password option has no password value, the client program
prints a prompt and waits for you to enter the password. (In these
examples, mydb
is not
interpreted as a password because it is separated from the
preceding password option by a space.)
On some systems, the library routine that MySQL uses to prompt for a password automatically limits the password to eight characters. That is a problem with the system library, not with MySQL. Internally, MySQL does not have any limit for the length of the password. To work around the problem, change your MySQL password to a value that is eight or fewer characters long, or put your password in an option file.
On Unix, MySQL programs treat the host name
localhost
specially, in a way that is likely
different from what you expect compared to other network-based
programs.
Clients determine what type of connection to make as follows:
If the host is not specified or is
localhost
, a connection to the local host
is assumed:
On Windows, the client connects using a shared-memory connection, if the server has shared-memory connections enabled.
On Unix, the client connects using a Unix socket file. The
--socket
option or the
MYSQL_UNIX_PORT
environment variable
may be used to specify the socket name.
On Windows, if host
is
.
, or TCP/IP is not enabled and
--socket
is not specified or
the host is empty, the client connects using a named pipe, if
the server has named-pipe connections enabled. If named-pipe
connections are not enabled or if the user making the
connection is not a member of the Windows group specified by
the
named_pipe_full_access_group
server system variable, an error occurs.
Otherwise, TCP/IP is used.
The --protocol
option enables you
to establish a particular type of connection even when the other
options would normally default to some other protocol. That is,
--protocol
may be given to specify
the connection protocol explicitly and override the preceding
rules, even for localhost
.
Only connection options that are relevant to the selected protocol
are used or checked. Other connection options are ignored. For
example, with --host=localhost
on
Unix, the client attempts to connect to the local server using a
Unix socket file. This occurs even if a
--port
or -P
option is given to specify a port number.
To ensure that the client makes a TCP/IP connection to the local
server, use --host
or
-h
to specify a host name value of
127.0.0.1
, or the IP address or name of the
local server. You can also specify the connection protocol
explicitly, even for localhost
, by using the
--protocol=TCP
option. For
example:
shell>mysql --host=127.0.0.1
shell>mysql --protocol=TCP
If the server is configured to accept IPv6 connections, clients
can connect over IPv6 using
--host=::1
. See
Section 5.1.12, “IPv6 Support”.
On Windows, you can force a MySQL client to use a named-pipe
connection by specifying the
--pipe
or
--protocol=PIPE
option, or by
specifying .
(period) as the host name. If
named-pipe connections are not enabled or if the user making the
connection is not a member of the Windows group specified by the
named_pipe_full_access_group
server system variable, an error occurs. Use the
--socket
option to specify the
name of the pipe if you do not want to use the default pipe name.
Connections to remote servers always use TCP/IP. This command
connects to the server running on
remote.example.com
using the default port
number (3306):
shell> mysql --host=remote.example.com
To specify a port number explicitly, use the
--port
or -P
option:
shell> mysql --host=remote.example.com --port=13306
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:
shell>mysql --port=13306 --host=127.0.0.1
shell>mysql --port=13306 --protocol=TCP
The following list summarizes the options that can be used to control how client programs connect to the server:
A hint about the client-side authentication plugin to use. See Section 6.3.10, “Pluggable Authentication”.
--host=
,
host_name
-h
host_name
The host where the server is running. The default value is
localhost
.
--password[=
,
pass_val
]-p[
pass_val
]
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.
--pipe
, -W
On Windows, connect to the server using a named pipe. The
server must be started with the
--enable-named-pipe
option to
enable named-pipe connections. In addition, the user making
the connection must be a member of the Windows group specified
by the
named_pipe_full_access_group
server system variable.
--port=
,
port_num
-P
port_num
The port number to use for the connection, for connections made using TCP/IP. The default port number is 3306.
--protocol={TCP|SOCKET|PIPE|MEMORY}
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.
--protocol Value |
Connection Protocol | Permissible Operating Systems |
---|---|---|
TCP |
TCP/IP connection to local or remote server | All |
SOCKET |
Unix socket file connection to local server | Unix only |
PIPE |
Named-pipe connection to local or remote server | Windows only |
MEMORY |
Shared-memory connection to local server | Windows only |
--shared-memory-base-name=
name
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.
--socket=
,
file_name
-S
file_name
On Unix, the name of the Unix socket file to use, for
connections made using a named pipe to a local server. The
default Unix socket file name is
/tmp/mysql.sock
.
On Windows, the name of the named pipe to use, for connections
to a local server. The default Windows pipe name is
MySQL
. The pipe name is not case-sensitive.
The server must be started with the
--enable-named-pipe
option to
enable named-pipe connections. In addition, the user making
the connection must be a member of the Windows group specified
by the
named_pipe_full_access_group
server system variable.
Options that begin with --ssl
are used for establishing a secure connection to the server
using SSL, if the server is configured with SSL support. For
details, see Section 6.4.2, “Command Options for Encrypted Connections”.
The protocols the client permits for encrypted connections. The value is a list of one or more comma-separated protocol names. The protocols that can be named for this option depend on the SSL library used to compile MySQL. For details, see Section 6.4.6, “Encrypted Connection Protocols and Ciphers”.
--user=
,
user_name
-u
user_name
The user name of the MySQL account you want to use. The
default user name is ODBC
on Windows or
your Unix login name on Unix.
It is possible to specify different default values to be used when you make a connection so that you need not enter them on the command line each time you invoke a client program. This can be done in a couple of ways:
You can specify connection parameters in the
[client]
section of an option file. The
relevant section of the file might look like this:
[client] host=host_name
user=user_name
password=your_pass
Section 4.2.7, “Using Option Files”, discusses option files further.
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
Section 6.1.2.1, “End-User Guidelines for Password Security”. For a list of
variables, see Section 4.9, “MySQL Program Environment Variables”.
In addition to specifying the connection parameters to an instance of MySQL server documented at Section 4.2.2, “Connecting to the MySQL Server”, you can specify the connection using a URI-type string or key-value pairs. The following MySQL clients support specifying the connection to MySQL server instance using a URI-type string or key-value pairs:
MySQL Shell
MySQL Router
MySQL Connectors which implement X DevAPI
Many of the parameters you use to specify a connection in this way are similar to those used with the command options, and this section documents all of the valid parameters. The connection's parameters can be specified as:
a URI-type string, such as
myuser@example.com:3306/main-schema
. See
Connecting using a URI String for the full syntax.
key-value pairs, such as {user:'myuser',
host:'example.com', port:3306,
schema:'main-schema'}
. See
Connecting using Key-value Pairs for the
full syntax.
The connection parameters are not case sensitive and can only be defined once. If a parameter is defined more than once, an error is generated.
This section consists of:
This section describes the parameters available when specifying a connection to MySQL. The following parameters can be provided as either a URI type string or in a key-value pairs. In a URI type string they conform to the base URI, see Connecting using a URI String. Alternatively they can be specified as key-value pairs, see Connecting using Key-value Pairs.
scheme
: specifies the connection
protocol to use. Use mysqlx
for
X Protocol connections and mysql
for classic MySQL protocol connections. If no protocol is
specified, the server attempts to guess the protocol.
user
: specifies the MySQL user
account to be used for the authentication process.
password
: specifies the password
to be used for the authentication process.
Storing the password in the connection is insecure and not recommended.
host
: specifies the server
instance the connection refers to. Can be either an IPv4
address, an IPv6 address or a hostname. If not specified,
localhost is used by default.
port
: specifies a network port
which the target MySQL server is listening on for
connections. If not specified, 33060 is used by default for
X Protocol connections, and 3306 is the default for
classic MySQL protocol connections.
socket
: path to a Unix socket or
Windows named-pipe. Values are local file paths and must be
encoded in URI type strings, using percent encoding or
surrounding the path with parentheses, which removes the
need to percent encode characters such as the common
directory separator /
. To connect as
root@localhost
using the Unix socket
/tmp/mysqld.sock
either specify the
path using parenthesis, for example
root@localhost?socket=(/tmp/mysqld.sock)
,
or using percent encoding, for example
root@localhost?socket=%2Ftmp%2Fmysqld.sock
.
schema
: specifies the database to
be set as default when the connection is established.
You can specify options for the connection, either as part of a
URI type string by appending
?
,
or as key-value pairs. The following options are available:
attribute=value
ssl-mode
: the SSL mode to be used
for the connection. The following values are valid:
DISABLED
PREFERRED
REQUIRED
VERIFY_CA
VERIFY_IDENTITY
ssl-ca
: the path to the X.509
certificate authority in PEM format.
ssl-capath
: the path to the
directory that contains the X.509 certificates authorities
in PEM format.
ssl-cert
: The path to the X.509
certificate in PEM format.
ssl-key
: The path to the X.509
key in PEM format.
ssl-crl
: The path to file that
contains certificate revocation lists.
ssl-crlpath
: The path to the
directory that contains certificate revocation-list files.
ssl-cipher
: the SSL cipher to
use.
tls-version
: TLS version
permitted for secure connections. The following values are
valid:
TLSv1
TLSv1.1
TLSv1.2
(Supported only by commercial
edition)
auth-method
: Authentication
method used for the connection. Defaults to
AUTO
, meaning that the server attempts to
guess. The following values are valid:
AUTO
MYSQL41
SHA256_MEMORY
FROM_CAPABILITIES
FALLBACK
PLAIN
When using an X Protocol connection, any configured
auth-method
is overridden to this
sequence of authentication methods:
MYSQL41
,
SHA256_MEMORY
, PLAIN
.
get-server-public-key
: Request
public key from the server required for RSA key pair-based
password exchange. Use when connecting to MySQL 8.0 servers
over classic MySQL protocol with SSL mode
DISABLED
. You must specify the protocol
in this case, for example:
mysql://user@localhost:3306?get-server-key=true
server-public-key-path
: The path
name to a file containing a client-side copy of the public
key required by the server for RSA key pair-based password
exchange. Use when connecting to MySQL 8.0 servers over
classic MySQL protocol with SSL mode
DISABLED
.
connect-timeout
: an integer value used to
configure the number of seconds clients, such as
MySQL Shell, wait until the client stops trying to connect
to an unresponsive MySQL server.
compression
: When set to
true
(or 1), this option enables
compression of all information sent between the client and
the server if both support compression. The default is no
compression (false
or 0). This option is
available for MySQL Shell connections using classic MySQL
protocol only.
You can specify a connection to MySQL Server using a URI type
string format. Such strings can be used with the MySQL Shell
with the --uri
command option,
the MySQL Shell \connect
command, MySQL
Connectors which implement X DevAPI, and tools such as
MySQL Router.
A URI type string has the following format:
[scheme
://][user
[:[password
]]@]target
[:port
][/schema
][?attribute1=value1&attribute2=value2...
Percent encoding must be used for reserved characters in the
elements of the URI type string. For example, if you specify a
string that includes the @
character, the
character must be replaced by %40
. If you
include a zone ID in an IPv6 address, the %
character used as the separator must be replaced with
%25
.
The parameters you can use in a URI type string for a connection are described at Base Connection Parameters.
If no password is specified using the URI type string, which is
recommended, then the password is prompted for. The following
examples show how to specify URI type strings with the user name
user
, in each case the password is
prompted for:
A classic MySQL protocol connection to a local server instance listening at port 3333.
mysql://user@localhost:3333
An X Protocol connection to a local server instance listening at port 33065.
mysqlx://user@localhost:33065
An X Protocol connection to a remote server instance, using a host name, an IPv4 address and an IPv6 address.
mysqlx://user@server.example.com/ mysqlx://user@198.51.100.14:123 mysqlx://user@[2001:db8:85a3:8d3:1319:8a2e:370:7348]
An X Protocol connection using a socket, with the path either provided using percent encoding or parenthesis.
mysqlx://user@/path%2Fto%2Fsocket.sock mysqlx://user@(/path/to/socket.sock)
An optional path can be specified, which represents a database schema.
mysqlx://user@198.51.100.1/world%5Fx mysqlx://user@198.51.100.2:33060/world
An optional query can be specified, consisting of values in
the form of a key=value
pair or as a
single key
. The ,
character is used as a separator for values, a combination
of multiple pairs and keys can be specified. Values can be
of type list, list values are ordered by appearance. Strings
must be either percent encoded or surrounded by parenthesis.
The following are equivalent.
ssluser@127.0.0.1?ssl-ca=%2Froot%2Fclientcert%2Fca-cert.pem\ &ssl-cert=%2Froot%2Fclientcert%2Fclient-cert.pem\ &ssl-key=%2Froot%2Fclientcert%2Fclient-key ssluser@127.0.0.1?ssl-ca=(/root/clientcert/ca-cert.pem)\ &ssl-cert=(/root/clientcert/client-cert.pem)\ &ssl-key=(/root/clientcert/client-key)
The previous examples assume that connections require a
password, and with interactive clients the specified user's
password is requested at the login prompt. If the user has a
password-less account, which is insecure and not recommended, or
if socket peer-credential authentication is in use (for example
with Unix socket connections), you must explicitly specify in
the URI type string that no password is being provided and the
password prompt is not required. To do this, place a
:
after the user
in the
URI type string but do not specify a password after it. For
example:
mysqlx://user:@localhost
You can specify a connection to MySQL Server using key-value
pairs. These key-value pairs are supplied in language natural
constructs for the implementation. This means you can supply
connection parameters using key-value pairs as a JSON object in
JavaScript, or using key-value pairs in a dictionary in Python.
Regardless of the way the key-value pairs are supplied, the
concept remains the same - the keys as specified in this section
can be assigned values that are used to specify a connection.
You can specify connections using key-value pairs in
MySQL Shell's shell.connect()
method or
InnoDB cluster's dba.createCluster()
method, and with some of the MySQL Connectors which implement
X DevAPI.
Generally, key-value pairs are surrounded by
{
and }
characters and the
,
character is used as a separator between
key-value pairs. The :
character is used
between keys and values, and strings must be delimited, for
example using the '
character. It is not
necessary to percent encode strings, unlike URI type strings.
A connection specified as key-value pairs has the following format:
{key
:value
,key
:value
, ...}
The parameters you can use as keys for a connection are described at Base Connection Parameters.
If no password is specified, which is recommended, then in
interactive clients the password is prompted for. The following
examples show how to specify connections using key-value pairs
with the user name user
:
An X Protocol connection to a local server instance listening at port 33065.
{user:'user', host:'localhost', port:33065}
A classic MySQL protocol connection to a local server instance listening at port 3333.
{user:'user', host:'localhost', port:3333}
An X Protocol connection to a remote server instance, using a host name, an IPv4 address and an IPv6 address.
{user:'user', host:'server.example.com'} {user:'user', host:198.51.100.14:123} {user:'user', host:[2001:db8:85a3:8d3:1319:8a2e:370:7348]}
An X Protocol connection using a socket.
{user:'user', socket:'/path/to/socket/file/'}
An optional schema can be specified, which represents a database.
{user:'user', host:'localhost', schema:'world'}
The previous examples assume that connections require a
password, and with interactive clients the specified user's
password is requested at the login prompt. If the user has a
password-less account, which is insecure and not recommended, or
if socket peer-credential authentication is in use (for example
with Unix socket connections), you must explicitly specify that
no password is being provided and the password prompt is not
required. To do this, provide an empty string using
''
after the password
key.
For example:
{user:'user', password:'', host:'localhost'}
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 Section 4.2.11, “Setting Environment Variables”). This method is useful for options that you want to apply each time the program runs. In practice, option files are used more commonly for this purpose, but Section 5.8.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.
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=
or as pass_val
--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
-p
or as
pass_val
-p
. However, for the short form, if the
password value is given, it must follow the option letter with
no intervening space. The reason for this
is that if a space follows the option letter, the program has
no way to tell whether a following argument is supposed to be
the password value or some other kind of argument.
Consequently, the following two commands have two completely
different meanings:
shell>mysql -ptest
shell>mysql -p test
The first command instructs mysql to use a
password value of test
, but specifies no
default database. The second instructs
mysql to prompt for the password value and
to use test
as the default database.
Within option names, dash (-
) and
underscore (_
) may be used interchangeably.
For example,
--skip-grant-tables
and
--skip_grant_tables
are equivalent. (However, the leading dashes cannot be given
as underscores.)
For options that take a numeric value, the value can be given
with a suffix of K
, M
,
or G
to indicate a multiplier of 1024,
10242 or
10243. As of MySQL 8.0.14, a suffix
can also be T
, P
, and
E
to indicate a multiplier of
10244,
10245 or
10246. Suffix letters can be
uppercase or lowercase.
For example, the following command tells mysqladmin to ping the server 1024 times, sleeping 10 seconds between each ping:
shell> mysqladmin --count=1K --sleep=10 ping
When specifying file names as option values, avoid the use of
the ~
shell metacharacter because it might
not be interpreted as you expect.
Option values that contain spaces must be quoted when given on the
command line. For example, the
--execute
(or -e
)
option can be used with mysql to pass SQL
statements to the server. When this option is used,
mysql executes the statements in the option
value and exits. The statements must be enclosed by quotation
marks. For example, you can use the following command to obtain a
list of user accounts:
shell>mysql -u root -p --execute="SELECT User, Host FROM mysql.user"
Enter password:******
+------+-----------+ | User | Host | +------+-----------+ | | gigan | | root | gigan | | | localhost | | jon | localhost | | root | localhost | +------+-----------+ shell>
The long form (--execute
) is
followed by an equals sign (=
).
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:
shell>mysql -u root -p -e "SELECT VERSION();SELECT NOW()"
Enter password:******
+------------+ | VERSION() | +------------+ | 8.0.11 | +------------+ +---------------------+ | NOW() | +---------------------+ | 2018-08-05 20:00:20 | +---------------------+
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:
shell> mysql --loose-no-such-option
mysql: WARNING: unknown option '--loose-no-such-option'
The --loose
prefix can be useful when you run
programs from multiple installations of MySQL on the same machine
and list options in an option file. An option that may not be
recognized by all versions of a program can be given using the
--loose
prefix (or loose
in an
option file). Versions of the program that recognize the option
process it normally, and versions that do not recognize it issue a
warning and ignore it.
The --maximum
prefix is available for
mysqld only and permits a limit to be placed on
how large client programs can set session system variables. To do
this, use a --maximum
prefix with the variable
name. For example,
--maximum-max_heap_table_size=32M
prevents any
client from making the heap table size limit larger than 32M.
The --maximum
prefix is intended for use with
system variables that have a session value. If applied to a system
variable that has only a global value, an error occurs. For
example, with --maximum-back_log=200
, the server
produces this error:
Maximum value of 'back_log' cannot be set
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
.
A server started with the
persisted_globals_load
system
variable disabled does not read
mysqld-auto.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
Section 4.6.7, “mysql_config_editor — MySQL Configuration Utility”. A “login
path” is an option group that permits only certain
options: host
, user
,
password
, port
and
socket
. Client programs specify which login
path to read from .mylogin.cnf
using the
--login-path
option.
To specify an alternative login path file name, set the
MYSQL_TEST_LOGIN_FILE
environment variable.
This variable is used by the
mysql-test-run.pl testing utility, but also
is recognized by mysql_config_editor and by
MySQL clients such as mysql,
mysqladmin, and so forth.
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
or
SET
PERSIST_ONLY
statements. See
Section 5.1.9.3, “Persisted System Variables”. 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.
For information about option files used with NDB Cluster programs, see Section 22.3, “Configuration of NDB Cluster”.
On Windows, MySQL programs read startup options from the files shown in the following table, in the specified order (files listed first are read first, files read later take precedence).
Table 4.1 Option Files Read on Windows Systems
File Name | Purpose |
---|---|
,
|
Global options |
C:\my.ini , C:\my.cnf |
Global options |
,
|
Global options |
defaults-extra-file |
The file specified with
--defaults-extra-file , if
any |
|
Login path options (clients only) |
|
System variables persisted with
SET
PERSIST or
SET
PERSIST_ONLY (server only) |
In the preceding table, %WINDIR%
represents the
location of your Windows directory. This is commonly
C:\WINDOWS
. Use the following command to
determine its exact location from the value of the
WINDIR
environment variable:
C:\> echo %WINDIR%
%APPDATA%
represents the value of the Windows
application data directory. Use the following command to determine
its exact location from the value of the
APPDATA
environment variable:
C:\> echo %APPDATA%
BASEDIR
represents the MySQL base
installation directory. When MySQL 8.0 has been
installed using MySQL Installer, this is typically
C:\
where
PROGRAMDIR
\MySQL\MySQL
8.0 ServerPROGRAMDIR
represents the programs
directory (usually Program Files
on
English-language versions of Windows), See
Section 2.3.3, “MySQL Installer for Windows”.
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 (files listed first 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.
Table 4.2 Option Files Read on Unix and Unix-Like Systems
File Name | Purpose |
---|---|
/etc/my.cnf |
Global options |
/etc/mysql/my.cnf |
Global options |
|
Global options |
$MYSQL_HOME/my.cnf |
Server-specific options (server only) |
defaults-extra-file |
The file specified with
--defaults-extra-file , if
any |
~/.my.cnf |
User-specific options |
~/.mylogin.cnf |
User-specific login path options (clients only) |
|
System variables persisted with
SET
PERSIST or
SE
PERSIST_ONLY (server only) |
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 Section 4.2.5, “Using Options on the Command Line”).
However, in an option file, you omit the leading two dashes from
the option name and you specify only one option per line. For
example, --quick
and
--host=localhost
on the command line should be
specified as quick
and
host=localhost
on separate lines in an option
file. To specify an option of the form
--loose-
in an
option file, write it as
opt_name
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
--
on the
command line.
opt_name
opt_name
=value
This is equivalent to
--
on the command line. In an option file, you can have spaces
around the opt_name
=value
=
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,
\
becomes
“x
x
” rather than
\
. See
Section 9.1.1, “String Literals”.
x
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:
basedir="C:\Program Files\MySQL\MySQL Server 8.0" basedir="C:\\Program Files\\MySQL\\MySQL Server 8.0" basedir="C:/Program Files/MySQL/MySQL Server 8.0" basedir=C:\\Program\sFiles\\MySQL\\MySQL\sServer\s8.0
If an option group name is the same as a program name, options in
the group apply specifically to that program. For example, the
[mysqld]
and [mysql]
groups
apply to the mysqld server and the
mysql client program, respectively.
The [client]
option group is read by all client
programs provided in MySQL distributions (but
not by mysqld). To
understand how third-party client programs that use the C API can
use option files, see the C API documentation at
Section 28.7.7.50, “mysql_options()”.
The [client]
group enables you to specify
options that apply to all clients. For example,
[client]
is the appropriate group to use to
specify the password for connecting to the server. (But make sure
that the option file is accessible only by yourself, so that other
people cannot discover your password.) Be sure not to put an
option in the [client]
group unless it is
recognized by all client programs that you
use. Programs that do not understand the option quit after
displaying an error message if you try to run them.
List more general option groups first and more specific groups
later. For example, a [client]
group is more
general because it is read by all client programs, whereas a
[mysqldump]
group is read only by
mysqldump. Options specified later override
options specified earlier, so putting the option groups in the
order [client]
, [mysqldump]
enables mysqldump-specific options to override
[client]
options.
Here is a typical global option file:
[client] port=3306 socket=/tmp/mysql.sock [mysqld] port=3306 socket=/tmp/mysql.sock key_buffer_size=16M max_allowed_packet=128M [mysqldump] quick
Here is a typical user option file:
[client] # The following password will be sent to all standard MySQL clients password="my password" [mysql] no-auto-rehash connect_timeout=2
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
[
line that
indicates the program to which the options apply.
group
]
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.
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:
--print-defaults
may be used
immediately after
--defaults-file
,
--defaults-extra-file
, or
--login-path
.
On Windows, if the server is started with the
--defaults-file
and
--install
options,
--install
must be first. See
Section 2.3.5.8, “Starting MySQL as a Windows Service”.
When specifying file names as option values, avoid the use of the
~
shell metacharacter because it might not be
interpreted as you expect.
--defaults-extra-file=
file_name
Read this option file after the global option file but (on
Unix) before the user option file and (on all platforms)
before the login path file. (For information about the order
in which option files are used, see
Section 4.2.7, “Using Option Files”.) If the file does not exist or
is otherwise inaccessible, an error occurs.
file_name
is interpreted relative
to the current directory if given as a relative path name
rather than a full path name.
See the introduction to this section regarding constraints on the position in which this option may be specified.
Read only the given option file. If the file does not exist or
is otherwise inaccessible, an error occurs.
file_name
is interpreted relative
to the current directory if given as a relative path name
rather than a full path name.
Exceptions: Even with
--defaults-file
,
mysqld reads
mysqld-auto.cnf
and client programs read
.mylogin.cnf
.
See the introduction to this section regarding constraints on the position in which this option may be specified.
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 Section 4.6.7, “mysql_config_editor — MySQL Configuration Utility”.
A client program reads the option group corresponding to the named login path, in addition to option groups that the program reads by default. Consider this command:
shell> mysql --login-path=mypath
By default, the mysql client reads the
[client]
and [mysql]
option groups. So for the command shown,
mysql reads [client]
and
[mysql]
from other option files, and
[client]
, [mysql]
, and
[mypath]
from the login path file.
Client programs read the login path file even when the
--no-defaults
option is used.
To specify an alternate login path file name, set the
MYSQL_TEST_LOGIN_FILE
environment variable.
See the introduction to this section regarding constraints on the position in which this option may be specified.
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
Section 4.6.7, “mysql_config_editor — MySQL Configuration Utility”.)
Print the program name and all options that it gets from option files. Password values are masked.
See the introduction to this section regarding constraints on the position in which this option may be specified.
Many MySQL programs have internal variables that can be set at
runtime using the
SET
statement. See Section 13.7.5.1, “SET Syntax for Variable Assignment”, and
Section 5.1.9, “Using System 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:
shell>mysql --max_allowed_packet=16777216
shell>mysql --max_allowed_packet=16M
The first command specifies the value in bytes. The second
specifies the value in megabytes. For variables that take a
numeric value, the value can be given with a suffix of
K
, M
, or
G
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.) As of MySQL 8.0.14, a
suffix can also be T
, P
, and
E
to indicate a multiplier of
10244, 10245
or 10246. Suffix letters can be
uppercase or lowercase.
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:
shell> mysql --max=1000000
mysql: ambiguous option '--max=1000000' (max_allowed_packet, max_join_size)
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:
shell>mysql --max_allowed_packet=16M
shell>mysql --max_allowed_packet=16*1024*1024
Conversely, the second of the following lines is legal at runtime, but the first is not:
mysql>SET GLOBAL max_allowed_packet=16M;
mysql>SET GLOBAL max_allowed_packet=16*1024*1024;
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
:
shell>mysql --host 85.224.35.45 --user jon
Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 3 Server version: 8.0.17 Source distribution Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql>SELECT CURRENT_USER();
+----------------+ | CURRENT_USER() | +----------------+ | jon@% | +----------------+ 1 row in set (0.00 sec)
Omitting the required value for one of these option yields an error, such as the one shown here:
shell> mysql --host 85.224.35.45 --user
mysql: option '--user' requires an argument
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:
shell> mysql --host --user jon
ERROR 2005 (HY000): Unknown MySQL server host '--user' (1)
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
,
where host_name
.errhost_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:
shell> mysqld_safe &
[1] 11699
shell> 080112 12:53:40 mysqld_safe Logging to '/usr/local/mysql/var/tonfisk.err'.
080112 12:53:40 mysqld_safe Starting mysqld daemon with databases from /usr/local/mysql/var
shell>
After shutting down the server, restart it as follows:
shell> mysqld_safe --log-error &
[1] 11699
shell> 080112 12:53:40 mysqld_safe Logging to '/usr/local/mysql/var/tonfisk.err'.
080112 12:53:40 mysqld_safe Starting mysqld daemon with databases from /usr/local/mysql/var
shell>
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:
shell> mysqld_safe --log-error my-errors &
[1] 31357
shell> 080111 22:53:31 mysqld_safe Logging to '/usr/local/mysql/var/tonfisk.err'.
080111 22:53:32 mysqld_safe Starting mysqld daemon with databases from /usr/local/mysql/var
080111 22:53:34 mysqld_safe mysqld from pid file /usr/local/mysql/var/tonfisk.pid ended
[1]+ Done ./mysqld_safe --log-error my-errors
The server attempted to start using
/usr/local/mysql/var/tonfisk.err
as the error
log, but then shut down. Examining the last few lines of this file
shows the reason:
shell> tail /usr/local/mysql/var/tonfisk.err
2013-09-24T15:36:22.278034Z 0 [ERROR] Too many arguments (first extra is 'my-errors').
2013-09-24T15:36:22.278059Z 0 [Note] Use --verbose --help to get a list of available options!
2013-09-24T15:36:22.278076Z 0 [ERROR] Aborting
2013-09-24T15:36:22.279704Z 0 [Note] InnoDB: Starting shutdown...
2013-09-24T15:36:23.777471Z 0 [Note] InnoDB: Shutdown completed; log sequence number 2319086
2013-09-24T15:36:23.780134Z 0 [Note] mysqld: Shutdown complete
Because the --log-error
option
supplies a default value, you must use an equals sign to assign a
different value to it, as shown here:
shell> mysqld_safe --log-error=my-errors &
[1] 31437
shell> 080111 22:54:15 mysqld_safe Logging to '/usr/local/mysql/var/my-errors.err'.
080111 22:54:15 mysqld_safe Starting mysqld daemon with databases from /usr/local/mysql/var
shell>
Now the server has been started successfully, and is logging
errors to the file
/usr/local/mysql/var/my-errors.err
.
Similar issues can arise when specifying option values in option
files. For example, consider a my.cnf
file
that contains the following:
[mysql] host user
When the mysql client reads this file, these
entries are parsed as --host
--user
or
--host=--user
, with the result shown
here:
shell> mysql
ERROR 2005 (HY000): Unknown MySQL server host '--user' (1)
However, in option files, an equals sign is not assumed. Suppose
the my.cnf
file is as shown here:
[mysql] user jon
Trying to start mysql in this case causes a different error:
shell> mysql
mysql: unknown option '--user jon'
A similar error would occur if you were to write host
tonfisk
in the option file rather than
host=tonfisk
. Instead, you must use the equals
sign:
[mysql] user=jon
Now the login attempt succeeds:
shell>mysql
Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 5 Server version: 8.0.17 Source distribution Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql>SELECT USER();
+---------------+ | USER() | +---------------+ | jon@localhost | +---------------+ 1 row in set (0.00 sec)
This is not the same behavior as with the command line, where the equals sign is not required:
shell>mysql --user jon --host tonfisk
Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 6 Server version: 8.0.17 Source distribution Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql>SELECT USER();
+---------------+ | USER() | +---------------+ | jon@tonfisk | +---------------+ 1 row in set (0.00 sec)
Specifying an option requiring a value without a value in an
option file causes the server to abort with an error. Suppose that
my.cnf
contains the following:
[mysqld] log_error relay_log relay_log_index
This causes the server to fail on startup, as shown here:
shell> mysqld_safe &
130924 10:41:46 mysqld_safe Logging to '/home/jon/bin/mysql/var/tonfisk.err'.
130924 10:41:46 mysqld_safe Starting mysqld daemon with databases from /home/jon/bin/mysql/var
130924 10:41:47 mysqld_safe mysqld from pid file /home/jon/bin/mysql/var/tonfisk.pid ended
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
shell> tail -n 3 ../var/tonfisk.err
130924 10:41:46 mysqld_safe Starting mysqld daemon with databases from /home/jon/bin/mysql/var
2013-09-24T15:41:47.217180Z 0 [ERROR] /home/jon/bin/mysql/libexec/mysqld: option '--relay-log' requires an argument
2013-09-24T15:41:47.217479Z 0 [ERROR] Aborting
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)
Environment variables can be set at the command prompt to affect the current invocation of your command processor, or set permanently to affect future invocations. To set a variable permanently, you can set it in a startup file or by using the interface provided by your system for this purpose. Consult the documentation for your command interpreter for specific details. Section 4.9, “MySQL Program Environment Variables”, lists all environment variables that affect MySQL program operation.
To specify a value for an environment variable, use the syntax
appropriate for your command processor. For example, on Windows,
you can set the USER
variable to specify your
MySQL account name. To do so, use this syntax:
SET USER=your_name
The syntax on Unix depends on your shell. Suppose that you want to
specify the TCP/IP port number using the
MYSQL_TCP_PORT
variable. Typical syntax (such
as for sh, ksh,
bash, zsh, and so on) is as
follows:
MYSQL_TCP_PORT=3306 export MYSQL_TCP_PORT
The first command sets the variable, and the
export
command exports the variable to the
shell environment so that its value becomes accessible to MySQL
and other processes.
For csh and tcsh, use setenv to make the shell variable available to the environment:
setenv MYSQL_TCP_PORT 3306
The commands to set environment variables can be executed at your command prompt to take effect immediately, but the settings persist only until you log out. To have the settings take effect each time you log in, use the interface provided by your system or place the appropriate command or commands in a startup file that your command interpreter reads each time it starts.
On Windows, you can set environment variables using the System Control Panel (under Advanced).
On Unix, typical shell startup files are
.bashrc
or .bash_profile
for bash, or .tcshrc
for
tcsh.
Suppose that your MySQL programs are installed in
/usr/local/mysql/bin
and that you want to make
it easy to invoke these programs. To do this, set the value of the
PATH
environment variable to include that
directory. For example, if your shell is bash,
add the following line to your .bashrc
file:
PATH=${PATH}:/usr/local/mysql/bin
bash uses different startup files for login and
nonlogin shells, so you might want to add the setting to
.bashrc
for login shells and to
.bash_profile
for nonlogin shells to make
sure that PATH
is set regardless.
If your shell is tcsh, add the following line
to your .tcshrc
file:
setenv PATH ${PATH}:/usr/local/mysql/bin
If the appropriate startup file does not exist in your home directory, create it with a text editor.
After modifying your PATH
setting, open a new
console window on Windows or log in again on Unix so that the
setting goes into effect.
This section describes mysqld, the MySQL server, and several programs that are used to start the 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.
Some installation packages contain a debugging version of the server named mysqld-debug. Invoke this version instead of mysqld for debugging support, memory allocation checking, and trace file support (see Section 29.5.1.2, “Creating Trace 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.
For a full description of MySQL Server command options, system variables, and status variables, see Section 5.1, “The MySQL Server”. For information about installing MySQL and setting up the initial configuration, see Chapter 2, Installing and Upgrading MySQL.
mysqld_safe is the recommended way to start a mysqld server on Unix. mysqld_safe adds some safety features such as restarting the server when an error occurs and logging runtime information to an error log. A description of error logging is given later in this section.
For some Linux platforms, MySQL installation from RPM or Debian packages includes systemd support for managing MySQL server startup and shutdown. On these platforms, mysqld_safe is not installed because it is unnecessary. For more information, see Section 2.5.9, “Managing MySQL Server with systemd”.
mysqld_safe tries to start an executable
named mysqld. To override the default
behavior and specify explicitly the name of the server you want
to run, specify a --mysqld
or --mysqld-version
option
to mysqld_safe. You can also use
--ledir
to indicate the
directory where mysqld_safe should look for
the server.
Many of the options to mysqld_safe are the same as the options to mysqld. See Section 5.1.7, “Server Command Options”.
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
Section 4.2.7, “Using Option Files”.
mysqld_safe reads all options from the
[mysqld]
, [server]
, and
[mysqld_safe]
sections in option files. For
example, if you specify a [mysqld]
section
like this, mysqld_safe will find and use the
--log-error
option:
[mysqld] log-error=error.log
For backward compatibility, mysqld_safe also
reads [safe_mysqld]
sections, but to be
current you should rename such sections to
[mysqld_safe]
.
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 Section 4.2.7, “Using Option Files”.
Table 4.3 mysqld_safe Options
Format | Description |
---|---|
--basedir | Path to MySQL installation directory |
--core-file-size | Size of core file that mysqld should be able to create |
--datadir | Path to data directory |
--defaults-extra-file | Read named option file in addition to usual option files |
--defaults-file | Read only named option file |
--help | Display help message and exit |
--ledir | Path to directory where server is located |
--log-error | Write error log to named file |
--malloc-lib | Alternative malloc library to use for mysqld |
--mysqld | Name of server program to start (in ledir directory) |
--mysqld-safe-log-timestamps | Timestamp format for logging |
--mysqld-version | Suffix for server program name |
--nice | Use nice program to set server scheduling priority |
--no-defaults | Read no option files |
--open-files-limit | Number of files that mysqld should be able to open |
--pid-file | Path name of server process ID file |
--plugin-dir | Directory where plugins are installed |
--port | Port number on which to listen for TCP/IP connections |
--skip-kill-mysqld | Do not try to kill stray mysqld processes |
--skip-syslog | Do not write error messages to syslog; use error log file |
--socket | Socket file on which to listen for Unix socket connections |
--syslog | Write error messages to syslog |
--syslog-tag | Tag suffix for messages written to syslog |
--timezone | Set TZ time zone environment variable to named value |
--user | Run mysqld as user having name user_name or numeric user ID user_id |
Display a help message and exit.
The path to the MySQL installation directory.
The size of the core file that mysqld should be able to create. The option value is passed to ulimit -c.
The
innodb_buffer_pool_in_core_file
variable can be used to reduce the size of core files on
operating systems that support it. For more information,
see Section 15.8.3.8, “Excluding Buffer Pool Pages from Core Files”.
The path to the data directory.
--defaults-extra-file=
file_name
Read this option file in addition to the usual option files.
If the file does not exist or is otherwise inaccessible, the
server will exit with an error.
file_name
is interpreted relative
to the current directory if given as a relative path name
rather than a full path name. This must be the first option
on the command line if it is used.
For additional information about this and other option-file options, see Section 4.2.8, “Command-Line Options that Affect Option-File Handling”.
Use only the given option file. If the file does not exist
or is otherwise inaccessible, the server will exit with an
error. file_name
is interpreted
relative to the current directory if given as a relative
path name rather than a full path name. This must be the
first option on the command line if it is used.
For additional information about this and other option-file options, see Section 4.2.8, “Command-Line Options that Affect Option-File Handling”.
If mysqld_safe cannot find the server, use this option to indicate the path name to the directory where the server is located.
This option is accepted only on the command line, not in
option files. On platforms that use systemd, the value can
be specified in the value of MYSQLD_OPTS
.
See Section 2.5.9, “Managing MySQL Server with systemd”.
Write the error log to the given file. See Section 5.4.2, “The Error Log”.
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.
UTC
, utc
ISO 8601 UTC format (same as
--log_timestamps=UTC
for
the server). This is the default.
SYSTEM
, system
ISO 8601 local time format (same as
--log_timestamps=SYSTEM
for the server).
HYPHEN
, hyphen
YY-MM-DD h:mm:ss
format, as in
mysqld_safe for MySQL 5.6.
LEGACY
, legacy
YYMMDD hh:mm:ss
format, as in
mysqld_safe prior to MySQL 5.6.
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.
If the option is given as
--malloc-lib=
,
that full path is added to the beginning of the
/path/to/some/library
LD_PRELOAD
value. If the full path
points to a nonexistent or unreadable file,
mysqld_safe aborts with an error.
For cases where mysqld_safe adds a
path name to LD_PRELOAD
, it adds the
path to the beginning of any existing value the variable
already has.
On systems that manage the server using systemd,
mysqld_safe is not available. Instead,
specify the allocation library by setting
LD_PRELOAD
in
/etc/sysconfig/mysql
.
Linux users can use the
libtcmalloc_minimal.so
library on any
platform for which a tcmalloc
package is
installed in /usr/lib
by adding these
lines to the my.cnf
file:
[mysqld_safe] malloc-lib=tcmalloc
To use a specific tcmalloc
library,
specify its full path name. Example:
[mysqld_safe] malloc-lib=/opt/lib/libtcmalloc_minimal.so
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 is accepted only on the command line, not in
option files. On platforms that use systemd, the value can
be specified in the value of MYSQLD_OPTS
.
See Section 2.5.9, “Managing MySQL Server with systemd”.
This option is similar to the
--mysqld
option, but you
specify only the suffix for the server program name. The
base name is assumed to be mysqld. For
example, if you use
--mysqld-version=debug
,
mysqld_safe starts the
mysqld-debug program in the
ledir
directory. If the argument to
--mysqld-version
is
empty, mysqld_safe uses
mysqld in the ledir
directory.
This option is accepted only on the command line, not in
option files. On platforms that use systemd, the value can
be specified in the value of MYSQLD_OPTS
.
See Section 2.5.9, “Managing MySQL Server with systemd”.
Use the nice
program to set the server's
scheduling priority to the given value.
Do not read any option files. If program startup fails due
to reading unknown options from an option file,
--no-defaults
can be
used to prevent them from being read. This must be the first
option on the command line if it is used.
For additional information about this and other option-file options, see Section 4.2.8, “Command-Line Options that Affect Option-File Handling”.
The number of files that mysqld should be able to open. The option value is passed to ulimit -n.
You must start mysqld_safe as
root
for this to function properly.
The path name that mysqld should use for its process ID file.
The path name of the plugin directory.
The port number that the server should use when listening
for TCP/IP connections. The port number must be 1024 or
higher unless the server is started by the
root
operating system user.
Do not try to kill stray mysqld processes at startup. This option works only on Linux.
The Unix socket file that the server should use when listening for local connections.
--syslog
causes error
messages to be sent to syslog
on systems
that support the logger program.
--skip-syslog
suppresses the use of
syslog
; messages are written to an error
log file.
When syslog
is used for error logging,
the daemon.err
facility/severity is used
for all log messages.
Using these options to control mysqld
logging is deprecated. To write error log output to the
system log, use the instructions at
Section 5.4.2.3, “Error Logging to the System Log”. To control the facility,
use the server
log_syslog_facility
system
variable.
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=
,
which modifies the identifiers to be
tag
mysqld_safe-
and
tag
mysqld-
.
tag
Using this option to control mysqld
logging is deprecated. Use the server
log_syslog_tag
system
variable instead. See Section 5.4.2.3, “Error Logging to the System Log”.
Set the TZ
time zone environment variable
to the given option value. Consult your operating system
documentation for legal time zone specification formats.
Run the mysqld server as the user having
the name user_name
or the numeric
user ID user_id
.
(“User” in this context refers to a system
login account, not a MySQL user listed in the grant tables.)
If you execute mysqld_safe with the
--defaults-file
or
--defaults-extra-file
option
to name an option file, the option must be the first one given
on the command line or the option file will not be used. For
example, this command will not use the named option file:
mysql> mysqld_safe --port=port_num
--defaults-file=file_name
Instead, use the following command:
mysql> mysqld_safe --defaults-file=file_name
--port=port_num
The mysqld_safe script is written so that it normally can start a server that was installed from either a source or a binary distribution of MySQL, even though these types of distributions typically install the server in slightly different locations. (See Section 2.1.4, “Installation Layouts”.) mysqld_safe expects one of the following conditions to be true:
The server and databases can be found relative to the
working directory (the directory from which
mysqld_safe is invoked). For binary
distributions, mysqld_safe looks under
its working directory for bin
and
data
directories. For source
distributions, it looks for libexec
and
var
directories. This condition should
be met if you execute mysqld_safe from
your MySQL installation directory (for example,
/usr/local/mysql
for a binary
distribution).
If the server and databases cannot be found relative to the
working directory, mysqld_safe attempts
to locate them by absolute path names. Typical locations are
/usr/local/libexec
and
/usr/local/var
. The actual locations
are determined from the values configured into the
distribution at the time it was built. They should be
correct if MySQL is installed in the location specified at
configuration time.
Because mysqld_safe tries to find the server and databases relative to its own working directory, you can install a binary distribution of MySQL anywhere, as long as you run mysqld_safe from the MySQL installation directory:
shell>cd
shell>mysql_installation_directory
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)
When you use mysqld_safe to start mysqld, mysqld_safe arranges for error (and notice) messages from itself and from mysqld to go to the same destination.
There are several mysqld_safe options for controlling the destination of these messages:
--log-error=
:
Write error messages to the named error file.
file_name
--syslog
: Write error
messages to syslog
on systems that
support the logger program.
--skip-syslog
:
Do not write error messages to syslog
.
Messages are written to the default error log file
(
in the data directory), or to a named file if the
host_name
.err--log-error
option is
given.
If none of these options is given, the default is
--skip-syslog
.
When mysqld_safe writes a message, notices go
to the logging destination (syslog
or the
error log file) and stdout
. Errors go to the
logging destination and stderr
.
Controlling mysqld logging from
mysqld_safe is deprecated. Use the server's
native syslog
support instead. For more
information, see Section 5.4.2.3, “Error Logging to the System Log”.
MySQL distributions on Unix and Unix-like system include a script named mysql.server, which starts the MySQL server using mysqld_safe. It can be used on systems such as Linux and Solaris that use System V-style run directories to start and stop system services. It is also used by the macOS Startup Item for MySQL.
mysql.server is the script name as used within the MySQL source tree. The installed name might be different; for example, mysqld or mysql. In the following discussion, adjust the name mysql.server as appropriate for your system.
For some Linux platforms, MySQL installation from RPM or Debian packages includes systemd support for managing MySQL server startup and shutdown. On these platforms, mysql.server and mysqld_safe are not installed because they are unnecessary. For more information, see Section 2.5.9, “Managing MySQL Server with systemd”.
To start or stop the server manually using the
mysql.server script, invoke it from the
command line with start
or
stop
arguments:
shell>mysql.server start
shell>mysql.server stop
mysql.server changes location to the MySQL
installation directory, then invokes
mysqld_safe. To run the server as some
specific user, add an appropriate user
option
to the [mysqld]
group of the global
/etc/my.cnf
option file, as shown later in
this section. (It is possible that you must edit
mysql.server if you've installed a binary
distribution of MySQL in a nonstandard location. Modify it to
change location into the proper directory before it runs
mysqld_safe. If you do this, your modified
version of mysql.server may be overwritten if
you upgrade MySQL in the future; make a copy of your edited
version that you can reinstall.)
mysql.server stop stops the server by sending a signal to it. You can also stop the server manually by executing mysqladmin shutdown.
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-
),
or a native Linux package installation, the
mysql.server script may be installed in
the VERSION
.rpm/etc/init.d
directory with the name
mysqld
or mysql
.
See Section 2.5.4, “Installing MySQL on Linux Using RPM Packages from Oracle”, for more
information on the Linux RPM packages.
If you install MySQL from a source distribution or using a
binary distribution format that does not install
mysql.server automatically, you can
install the script manually. It can be found in the
support-files
directory under the MySQL
installation directory or in a MySQL source tree. Copy the
script to the /etc/init.d
directory
with the name mysql and make it
executable:
shell>cp mysql.server /etc/init.d/mysql
shell>chmod +x /etc/init.d/mysql
After installing the script, the commands needed to activate it to run at system startup depend on your operating system. On Linux, you can use chkconfig:
shell> chkconfig --add mysql
On some Linux systems, the following command also seems to be necessary to fully enable the mysql script:
shell> chkconfig --level 345 mysql on
On FreeBSD, startup scripts generally should go in
/usr/local/etc/rc.d/
. Install the
mysql.server
script as
/usr/local/etc/rc.d/mysql.server.sh
to
enable automatic startup. The rc(8)
manual page states that scripts in this directory are
executed only if their base name matches the
*.sh
shell file name pattern. Any other
files or directories present within the directory are
silently ignored.
As an alternative to the preceding setup, some operating
systems also use /etc/rc.local
or
/etc/init.d/boot.local
to start
additional services on startup. To start up MySQL using this
method, append a command like the one following to the
appropriate startup file:
/bin/sh -c 'cd /usr/local/mysql; ./bin/mysqld_safe --user=mysql &'
For other systems, consult your operating system documentation to see how to install startup scripts.
mysql.server reads options from the
[mysql.server]
and
[mysqld]
sections of option files. For
backward compatibility, it also reads
[mysql_server]
sections, but to be current
you should rename such sections to
[mysql.server]
.
You can add options for mysql.server in a
global /etc/my.cnf
file. A typical
my.cnf
file might look like this:
[mysqld] datadir=/usr/local/mysql/var socket=/var/tmp/mysql.sock port=3306 user=mysql [mysql.server] basedir=/usr/local/mysql
The mysql.server script supports the options
shown in the following table. If specified, they
must be placed in an option file, not on
the command line. mysql.server supports only
start
and stop
as
command-line arguments.
Table 4.4 mysql.server Option-File Options
Option Name | Description | Type |
---|---|---|
basedir |
Path to MySQL installation directory | Directory name |
datadir |
Path to MySQL data directory | Directory name |
pid-file |
File in which server should write its process ID | File name |
service-startup-timeout |
How long to wait for server startup | Integer |
The path to the MySQL installation directory.
The path to the MySQL data directory.
The path name of the file in which the server should write its process ID. The server creates the file in the data directory unless an absolute path name is given to specify a different directory.
If this option is not given, mysql.server
uses a default value of
.
The PID file value passed to mysqld_safe
overrides any value specified in the
host_name
.pid[mysqld_safe]
option file group. Because
mysql.server reads the
[mysqld]
option file group but not the
[mysqld_safe]
group, you can ensure that
mysqld_safe gets the same value when
invoked from mysql.server as when invoked
manually by putting the same pid-file
setting in both the [mysqld_safe]
and
[mysqld]
groups.
service-startup-timeout=
seconds
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).
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 some Linux platforms, MySQL installation from RPM or Debian packages includes systemd support for managing MySQL server startup and shutdown. On these platforms, mysqld_multi is not installed because it is unnecessary. For information about using systemd to handle multiple MySQL instances, see Section 2.5.9, “Managing MySQL Server with systemd”.
mysqld_multi searches for groups named
[mysqld
in
N
]my.cnf
(or in the file named by the
--defaults-file
option).
N
can be any positive integer. This
number is referred to in the following discussion as the option
group number, or GNR
. Group numbers
distinguish option groups from one another and are used as
arguments to mysqld_multi to specify which
servers you want to start, stop, or obtain a status report for.
Options listed in these groups are the same that you would use
in the [mysqld]
group used for starting
mysqld. (See, for example,
Section 2.10.5, “Starting and Stopping MySQL Automatically”.) However, when using multiple
servers, it is necessary that each one use its own value for
options such as the Unix socket file and TCP/IP port number. For
more information on which options must be unique per server in a
multiple-server environment, see
Section 5.8, “Running Multiple MySQL Instances on One Machine”.
To invoke mysqld_multi, use the following syntax:
shell> mysqld_multi [options
] {start|stop|reload|report} [GNR
[,GNR
] ...]
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:
shell> mysqld_multi --example
mysqld_multi searches for option files as follows:
With --no-defaults
, no
option files are read.
With
--defaults-file=
,
only the named file is read.
file_name
Otherwise, option files in the standard list of locations
are read, including any file named by the
--defaults-extra-file=
option, if one is given. (If the option is given multiple
times, the last value is used.)
file_name
For additional information about these and other option-file options, see Section 4.2.8, “Command-Line Options that Affect Option-File Handling”.
Option files read are searched for
[mysqld_multi]
and
[mysqld
option
groups. The N
][mysqld_multi]
group can be used
for options to mysqld_multi itself.
[mysqld
groups
can be used for options passed to specific
mysqld instances.
N
]
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=
option to use a different configuration file for that instance,
in which case the file_name
[mysqld]
or
[mysqld_safe]
groups from that file will be
used for that instance.
mysqld_multi supports the following options.
Display a help message and exit.
Display a sample option file.
Specify the name of the log file. If the file exists, log output is appended to it.
The mysqladmin binary to be used to stop servers.
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
[mysqld
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
Section 4.3.2, “mysqld_safe — MySQL Server Startup Script”.) Example:
N
]
[mysqld38] mysqld = mysqld-debug ledir = /opt/local/mysql/libexec
Print log information to stdout
rather
than to the log file. By default, output goes to the log
file.
The password of the MySQL account to use when invoking mysqladmin. Note that the password value is not optional for this option, unlike for other MySQL programs.
Silent mode; disable warnings.
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.
The user name of the MySQL account to use when invoking mysqladmin.
Be more verbose.
Display version information and exit.
Some notes about mysqld_multi:
Most important: Before using mysqld_multi be sure that you understand the meanings of the options that are passed to the mysqld servers and why you would want to have separate mysqld processes. Beware of the dangers of using multiple mysqld servers with the same data directory. Use separate data directories, unless you know what you are doing. Starting multiple servers with the same data directory does not give you extra performance in a threaded system. See Section 5.8, “Running Multiple MySQL Instances on One Machine”.
Make sure that the data directory for each server is fully
accessible to the Unix account that the specific
mysqld process is started as.
Do not use the Unix
root
account for this, unless
you know what you are doing. See
Section 6.1.5, “How to Run MySQL as a Normal User”.
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:
shell>mysql -u root -S /tmp/mysql.sock -p
Enter password: mysql>CREATE USER 'multi_admin'@'localhost' IDENTIFIED BY 'multipass';
mysql>GRANT SHUTDOWN ON *.* TO 'multi_admin'@'localhost';
See Section 6.2, “The MySQL Access Privilege System”. 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.
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 [mysqld
groups were intentionally omitted from the example to illustrate
that you can have “gaps” in the option file. This
gives you more flexibility.
N
]
# This is an example of a my.cnf file for mysqld_multi. # Usually this file is located in home dir ~/.my.cnf or /etc/my.cnf [mysqld_multi] mysqld = /usr/local/mysql/bin/mysqld_safe mysqladmin = /usr/local/mysql/bin/mysqladmin user = multi_admin password = my_password [mysqld2] socket = /tmp/mysql.sock2 port = 3307 pid-file = /usr/local/mysql/data2/hostname.pid2 datadir = /usr/local/mysql/data2 language = /usr/local/mysql/share/mysql/english user = unix_user1 [mysqld3] mysqld = /path/to/mysqld_safe ledir = /path/to/mysqld-binary/ mysqladmin = /path/to/mysqladmin socket = /tmp/mysql.sock3 port = 3308 pid-file = /usr/local/mysql/data3/hostname.pid3 datadir = /usr/local/mysql/data3 language = /usr/local/mysql/share/mysql/swedish user = unix_user2 [mysqld4] socket = /tmp/mysql.sock4 port = 3309 pid-file = /usr/local/mysql/data4/hostname.pid4 datadir = /usr/local/mysql/data4 language = /usr/local/mysql/share/mysql/estonia user = unix_user3 [mysqld6] socket = /tmp/mysql.sock6 port = 3311 pid-file = /usr/local/mysql/data6/hostname.pid6 datadir = /usr/local/mysql/data6 language = /usr/local/mysql/share/mysql/japanese user = unix_user4
The programs in this section are used when installing or upgrading MySQL.
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.
comp_err also generates
mysqld_error.h
,
mysqld_ername.h
, and
sql_state.h
header files.
For more information about how error messages are defined, see the MySQL Internals Manual.
Invoke comp_err like this:
shell> comp_err [options
]
comp_err supports the following options.
--help
, -?
Display a help message and exit.
--charset=
,
dir_name
-C
dir_name
The character set directory. The default is
../sql/share/charsets
.
--debug=
,
debug_options
-#
debug_options
Write a debugging log. A typical
debug_options
string is
d:t:O,
.
The default is file_name
d:t:O,/tmp/comp_err.trace
.
--debug-info
,
-T
Print some debugging information when the program exits.
--header_file=
,
file_name
-H
file_name
The name of the error header file. The default is
mysqld_error.h
.
--in_file=
,
file_name
-F
file_name
The name of the input file. The default is
../sql/share/errmsg-utf8.txt
.
--name_file=
,
file_name
-N
file_name
The name of the error name file. The default is
mysqld_ername.h
.
--out_dir=
,
dir_name
-D
dir_name
The name of the output base directory. The default is
../sql/share/
.
--out_file=
,
file_name
-O
file_name
The name of the output file. The default is
errmsg.sys
.
--statefile=
,
file_name
-S
file_name
The name for the SQLSTATE header file. The default is
sql_state.h
.
--version
,
-V
Display version information and exit.
This program enables you to improve the security of your MySQL installation in the following ways:
You can set a password for root
accounts.
You can remove root
accounts that are
accessible from outside the local host.
You can remove anonymous-user accounts.
You can remove the test
database (which
by default can be accessed by all users, even anonymous
users), and privileges that permit anyone to access
databases with names that start with
test_
.
mysql_secure_installation helps you implement security recommendations similar to those described at Section 2.10.4, “Securing the Initial MySQL Account”.
Normal usage is to connect to the local MySQL server; invoke mysql_secure_installation without arguments:
shell> mysql_secure_installation
When executed, mysql_secure_installation prompts you to determine which actions to perform.
The validate_password
component 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
Section 4.2.7, “Using Option Files”.
Table 4.5 mysql_secure_installation Options
Format | Description | Introduced |
---|---|---|
--defaults-extra-file | Read named option file in addition to usual option files | |
--defaults-file | Read only named option file | |
--defaults-group-suffix | Option group suffix value | |
--help | Display help message and exit | |
--host | Host to connect to (IP address or host name) | |
--no-defaults | Read no option files | |
--password | Accepted but always ignored. Whenever mysql_secure_installation is invoked, the user is prompted for a password, regardless. | |
--port | TCP/IP port number for connection | |
--print-defaults | Print default options | |
--protocol | Connection protocol to use | |
--socket | For connections to localhost, the Unix socket file to use | |
--ssl-ca | File that contains list of trusted SSL Certificate Authorities | |
--ssl-capath | Directory that contains trusted SSL Certificate Authority certificate files | |
--ssl-cert | File that contains X.509 certificate | |
--ssl-cipher | List of permitted ciphers for connection encryption | |
--ssl-crl | File that contains certificate revocation lists | |
--ssl-crlpath | Directory that contains certificate revocation-list files | |
--ssl-fips-mode | Whether to enable FIPS mode on client side | 8.0.11 |
--ssl-key | File that contains X.509 key | |
--tls-ciphersuites | TLSv1.3 ciphersuites permitted for encrypted connections | 8.0.16 |
--tls-version | Protocols permitted for encrypted connections | |
--use-default | Execute with no user interactivity | |
--user | MySQL user name to use when connecting to server |
--help
,
-?
Display a help message and exit.
--defaults-extra-file=
file_name
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.
For additional information about this and other option-file options, see Section 4.2.8, “Command-Line Options that Affect Option-File Handling”.
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.
For additional information about this and other option-file options, see Section 4.2.8, “Command-Line Options that Affect Option-File Handling”.
Read not only the usual option groups, but also groups with
the usual names and a suffix of
str
. For example,
mysql_secure_installation normally reads
the [client]
and
[mysql_secure_installation]
groups. If
the
--defaults-group-suffix=_other
option is given,
mysql_secure_installation also reads the
[client_other]
and
[mysql_secure_installation_other]
groups.
For additional information about this and other option-file options, see Section 4.2.8, “Command-Line Options that Affect Option-File Handling”.
--host=
,
host_name
-h
host_name
Connect to the MySQL server on the given host.
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
Section 4.6.7, “mysql_config_editor — MySQL Configuration Utility”.)
For additional information about this and other option-file options, see Section 4.2.8, “Command-Line Options that Affect Option-File Handling”.
--password=
,
password
-p
password
This option is accepted but ignored. Whether or not this option is used, mysql_secure_installation always prompts the user for a password.
--port=
,
port_num
-P
port_num
The TCP/IP port number to use for the connection.
Print the program name and all options that it gets from option files.
For additional information about this and other option-file options, see Section 4.2.8, “Command-Line Options that Affect Option-File Handling”.
--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 Section 4.2.2, “Connecting to the MySQL Server”.
--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.
Options that begin with
--ssl
specify whether to
connect to the server using SSL and indicate where to find
SSL keys and certificates. See
Section 6.4.2, “Command Options for Encrypted Connections”.
--ssl-fips-mode={OFF|ON|STRICT}
Controls whether to enable FIPS mode on the client side. The
--ssl-fips-mode
option differs from other
--ssl-
options in that it is not used to establish encrypted
connections, but rather to affect which cryptographic
operations are permitted. See Section 6.6, “FIPS Support”.
xxx
These
--ssl-fips-mode
values are permitted:
OFF
: Disable FIPS mode.
ON
: Enable FIPS mode.
STRICT
: Enable “strict”
FIPS mode.
If the OpenSSL FIPS Object Module is not available, the
only permitted value for
--ssl-fips-mode
is OFF
. In this case, setting
--ssl-fips-mode
to ON
or STRICT
causes the client to produce a warning at startup and to
operate in non-FIPS mode.
--tls-ciphersuites=
ciphersuite_list
For client programs, specifies which TLSv1.3 ciphersuites the client permits for encrypted connections. The value is a list of one or more colon-separated ciphersuite names. The ciphersuites that can be named for this option depend on the SSL library used to compile MySQL. For details, see Section 6.4.6, “Encrypted Connection Protocols and Ciphers”.
This option was added in MySQL 8.0.16.
The protocols the client permits for encrypted connections. The value is a list of one or more comma-separated protocol names. The protocols that can be named for this option depend on the SSL library used to compile MySQL. For details, see Section 6.4.6, “Encrypted Connection Protocols and Ciphers”.
Execute noninteractively. This option can be used for unattended installation operations.
--user=
,
user_name
-u
user_name
The MySQL user name to use when connecting to the server.
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 generate them automatically. See Section 6.4.3.1, “Creating SSL and RSA Certificates and Keys using MySQL”.
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.
Invoke mysql_ssl_rsa_setup like this:
shell> mysql_ssl_rsa_setup [options
]
Typical options are
--datadir
to specify
where to create the files, and
--verbose
to see the
openssl commands that
mysql_ssl_rsa_setup executes.
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 the
--datadir
option
is not given.
mysql_ssl_rsa_setup checks the data directory for SSL files with the following names:
ca.pem server-cert.pem server-key.pem
If any of those files are present, mysql_ssl_rsa_setup creates no SSL files. Otherwise, it invokes openssl to create them, plus some additional files:
ca.pem Self-signed CA certificate ca-key.pem CA private key server-cert.pem Server certificate server-key.pem Server private key client-cert.pem Client certificate client-key.pem Client private key
These files enable secure client connections using SSL; see Section 6.4.1, “Configuring MySQL to Use Encrypted Connections”.
mysql_ssl_rsa_setup checks the data directory for RSA files with the following names:
private_key.pem Private 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
or
caching_sha2_password
plugin; see
Section 6.5.1.2, “SHA-256 Pluggable Authentication”, and
Section 6.5.1.3, “Caching SHA-2 Pluggable Authentication”.
For information about the characteristics of files created by mysql_ssl_rsa_setup, see Section 6.4.3.1, “Creating SSL and RSA Certificates and Keys using MySQL”.
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
(possibly along with
--ssl-cipher
). If you prefer to
designate the files explicitly, invoke clients with 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:
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.)
Run mysql_ssl_rsa_setup with the
--datadir
option
to specify where to create the new files.
Restart the server.
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
Section 4.2.7, “Using Option Files”.
--help
,
?
Display a help message and exit.
The path to the directory that mysql_ssl_rsa_setup should check for default SSL and RSA files and in which it should create files if they are missing. The default is the compiled-in data directory.
The suffix for the Common Name attribute in X.509 certificates. The suffix value is limited to 17 characters. The default is based on the MySQL version number.
--uid=name
,
-v
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
,
-v
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.
--version
,
-V
Display version information and exit.
The mysql_tzinfo_to_sql program loads the
time zone tables in the mysql
database. It is
used on systems that have a
zoneinfo database (the set
of files describing time zones). Examples of such systems are
Linux, FreeBSD, Solaris, and OS X. One likely location for these
files is the /usr/share/zoneinfo
directory
(/usr/share/lib/zoneinfo
on Solaris). If
your system does not have a zoneinfo database, you can use the
downloadable package described in
Section 5.1.13, “MySQL Server Time Zone Support”.
mysql_tzinfo_to_sql can be invoked several ways:
shell>mysql_tzinfo_to_sql
shell>tz_dir
mysql_tzinfo_to_sql
shell>tz_file tz_name
mysql_tzinfo_to_sql --leap
tz_file
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_file
tz_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.
As of MySQL 8.0.16, the MySQL server performs the upgrade
tasks previously handled by mysql_upgrade
(for details, see
Section 2.11.3, “What the MySQL Upgrade Process Upgrades”). Consequently,
mysql_upgrade is unneeded and is deprecated
as of that version, and will be removed in a future MySQL
version. Because mysql_upgrade no longer
performs upgrade tasks, it exits with status 2
(EXIT_ALREADY_UPGRADED
), except that it
exits with status 0 (EXIT_SUCCESS
) if
invoked with the --force
option.
Each time you upgrade MySQL, you should execute mysql_upgrade, which looks for incompatibilities with the upgraded MySQL server:
It upgrades the system tables in the
mysql
schema so that you can take
advantage of new privileges or capabilities that might have
been added.
It upgrades the Performance Schema,
INFORMATION_SCHEMA
, and
sys
schema.
It examines user schemas.
If mysql_upgrade finds that a table has a possible incompatibility, it performs a table check and, if problems are found, attempts a table repair. If the table cannot be repaired, see Section 2.11.13, “Rebuilding or Repairing Tables or Indexes” for manual table repair strategies.
mysql_upgrade communicates directly with the MySQL server, sending it the SQL statements required to perform an upgrade.
You should always back up your current MySQL installation before performing an upgrade. See Section 7.2, “Database Backup Methods”.
Some upgrade incompatibilities may require special handling before upgrading your MySQL installation and running mysql_upgrade. See Section 2.11, “Upgrading MySQL”, for instructions on determining whether any such incompatibilities apply to your installation and how to handle them.
Use mysql_upgrade like this:
Ensure that the server is running.
Invoke mysql_upgrade to upgrade the
system tables in the mysql
schema and
check and repair tables in other schemas:
shell> mysql_upgrade [options
]
Stop the server and restart it so that any system table changes take effect.
If you have multiple MySQL server instances to upgrade, invoke mysql_upgrade with connection parameters appropriate for connecting to each of the desired servers. For example, with servers running on the local host on parts 3306 through 3308, upgrade each of them by connecting to the appropriate port:
shell>mysql_upgrade --protocol=tcp -P 3306 [
shell>other_options
]mysql_upgrade --protocol=tcp -P 3307 [
shell>other_options
]mysql_upgrade --protocol=tcp -P 3308 [
other_options
]
For local host connections on Unix, the
--protocol=tcp
option
forces a connection using TCP/IP rather than the Unix socket
file.
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
:
shell>mysql -u root -p
Enter password:****
<- enter root password here
Reset the password using ALTER
USER
:
mysql> ALTER USER USER() IDENTIFIED BY 'root-password
';
Then exit mysql and run mysql_upgrade again:
shell> mysql_upgrade [options
]
If you run the server with the
disabled_storage_engines
system variable set to disable certain storage engines (for
example, MyISAM
),
mysql_upgrade might fail with an error like
this:
mysql_upgrade: [ERROR] 3161: Storage engine MyISAM is disabled (Table creation is disallowed).
To handle this, restart the server with
disabled_storage_engines
disabled. Then you should be able to run
mysql_upgrade successfully. After that,
restart the server with
disabled_storage_engines
set
to its original value.
Unless invoked with the
--upgrade-system-tables
option, mysql_upgrade processes all tables in
all user schemas as necessary. Table checking 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.
Table checking uses the FOR UPGRADE
option of
the CHECK TABLE
statement. For
details about what this option entails, see
Section 13.7.3.2, “CHECK TABLE Syntax”.
mysql_upgrade marks all checked and repaired tables with the current MySQL version number. This ensures that the next time you run mysql_upgrade with the same version of the server, it can be determined whether there is any need to check or repair a given table again.
mysql_upgrade 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
mysql.user
system 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.
mysql_upgrade does not upgrade the contents of the time zone tables or help tables. For upgrade instructions, see Section 5.1.13, “MySQL Server Time Zone Support”, and Section 5.1.14, “Server-Side Help Support”.
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. An error occurs if
a sys
schema exists but has no
version
view, on the assumption that its
absence indicates a user-created schema:
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 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
Section 4.2.7, “Using Option Files”.
Table 4.7 mysql_upgrade Options
Format | Description | Introduced |
---|---|---|
--bind-address | Use specified network interface to connect to MySQL Server | |
--character-sets-dir | Directory where character sets are installed | |
--compress | Compress all information sent between client and server | |
--debug | Write debugging log | |
--debug-check | Print debugging information when program exits | |
--debug-info | Print debugging information, memory, and CPU statistics when program exits | |
--default-auth | Authentication plugin to use | |
--default-character-set | Specify default character set | |
--defaults-extra-file | Read named option file in addition to usual option files | |
--defaults-file | Read only named option file | |
--defaults-group-suffix | Option group suffix value | |
--force | Force execution even if mysql_upgrade has already been executed for current MySQL version | |
--get-server-public-key | Request RSA public key from server | 8.0.3 |
--help | Display help message and exit | |
--host | Connect to MySQL server on given host | |
--login-path | Read login path options from .mylogin.cnf | |
--max-allowed-packet | Maximum packet length to send to or receive from server | |
--net-buffer-length | Buffer size for TCP/IP and socket communication | |
--no-defaults | Read no option files | |
--password | Password to use when connecting to server | |
--pipe | On Windows, connect to server using named pipe | |
--plugin-dir | Directory where plugins are installed | |
--port | TCP/IP port number for connection | |
--print-defaults | Print default options | |
--protocol | Connection protocol to use | |
--server-public-key-path | Path name to file containing RSA public key | 8.0.4 |
--shared-memory-base-name | Name of shared memory to use for shared-memory connections | |
--skip-sys-schema | Do not install or upgrade sys schema | |
--socket | For connections to localhost, the Unix socket file to use | |
--ssl-ca | File that contains list of trusted SSL Certificate Authorities | |
--ssl-capath | Directory that contains trusted SSL Certificate Authority certificate files | |
--ssl-cert | File that contains X.509 certificate | |
--ssl-cipher | List of permitted ciphers for connection encryption | |
--ssl-crl | File that contains certificate revocation lists | |
--ssl-crlpath | Directory that contains certificate revocation-list files | |
--ssl-fips-mode | Whether to enable FIPS mode on client side | 8.0.11 |
--ssl-key | File that contains X.509 key | |
--ssl-mode | Security state of connection to server | |
--tls-ciphersuites | TLSv1.3 ciphersuites permitted for encrypted connections | 8.0.16 |
--tls-version | Protocols permitted for encrypted connections | |
--upgrade-system-tables | Update only system tables, not user schemas | |
--user | MySQL user name to use when connecting to server | |
--verbose | Verbose mode | |
--version-check | Check for proper server version | |
--write-binlog | Write all statements to binary log |
Display a short help message and exit.
On a computer having multiple network interfaces, use this option to select which interface to use for connecting to the MySQL server.
The directory where character sets are installed. See Section 10.14, “Character Set Configuration”.
--compress
,
-C
Compress all information sent between the client and the server if both support compression.
--debug[=
,
debug_options
]-#
[
debug_options
]
Write a debugging log. A typical
debug_options
string is
d:t:o,
.
The default is
file_name
d:t:O,/tmp/mysql_upgrade.trace
.
Print some debugging information when the program exits.
--debug-info
,
-T
Print debugging information and memory and CPU usage statistics when the program exits.
A hint about the client-side authentication plugin to use. See Section 6.3.10, “Pluggable Authentication”.
--default-character-set=
charset_name
Use charset_name
as the default
character set. See Section 10.14, “Character Set Configuration”.
--defaults-extra-file=
file_name
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.
For additional information about this and other option-file options, see Section 4.2.8, “Command-Line Options that Affect Option-File Handling”.
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.
For additional information about this and other option-file options, see Section 4.2.8, “Command-Line Options that Affect Option-File Handling”.
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.
For additional information about this and other option-file options, see Section 4.2.8, “Command-Line Options that Affect Option-File Handling”.
Ignore the mysql_upgrade_info
file and
force execution even if mysql_upgrade has
already been executed for the current version of MySQL.
Request from the server the public key required for RSA key
pair-based password exchange. This option applies to clients
that that authenticate with the
caching_sha2_password
authentication
plugin. For that plugin, the server does not send the public
key unless requested. This option is ignored for accounts
that do not authenticate with that plugin. It is also
ignored if RSA-based password exchange is not used, as is
the case when the client connects to the server using a
secure connection.
If
--server-public-key-path=
is given and specifies a valid public key file, it takes
precedence over
file_name
--get-server-public-key
.
For information about the
caching_sha2_password
plugin, see
Section 6.5.1.3, “Caching SHA-2 Pluggable Authentication”.
--host=
,
host_name
-h
host_name
Connect to the MySQL server on the given host.
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
Section 4.6.7, “mysql_config_editor — MySQL Configuration Utility”.
For additional information about this and other option-file options, see Section 4.2.8, “Command-Line Options that Affect Option-File Handling”.
The maximum size of the buffer for client/server communication. The default value is 24MB. The minimum and maximum values are 4KB and 2GB.
The initial size of the buffer for client/server communication. The default value is 1MB − 1KB. The minimum and maximum values are 4KB and 16MB.
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
Section 4.6.7, “mysql_config_editor — MySQL Configuration Utility”.)
For additional information about this and other option-file options, see Section 4.2.8, “Command-Line Options that Affect Option-File Handling”.
--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,
mysql_upgrade prompts for one.
Specifying a password on the command line should be considered insecure. See Section 6.1.2.1, “End-User Guidelines for Password Security”. 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.
The directory in which to look for plugins. Specify this
option if the
--default-auth
option
is used to specify an authentication plugin but
mysql_upgrade does not find it. See
Section 6.3.10, “Pluggable Authentication”.
--port=
,
port_num
-P
port_num
The TCP/IP port number to use for the connection.
Print the program name and all options that it gets from option files.
--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 Section 4.2.2, “Connecting to the MySQL Server”.
--server-public-key-path=
file_name
The path name to a file containing a client-side copy of the
public key required by the server for RSA key pair-based
password exchange. The file must be in PEM format. This
option applies to clients that authenticate with the
sha256_password
or
caching_sha2_password
authentication
plugin. This option is ignored for accounts that do not
authenticate with one of those plugins. It is also ignored
if RSA-based password exchange is not used, as is the case
when the client connects to the server using a secure
connection.
If
--server-public-key-path=
is given and specifies a valid public key file, it takes
precedence over
file_name
--get-server-public-key
.
For sha256_password
, this option applies
only if MySQL was built using OpenSSL.
For information about the sha256_password
and caching_sha2_password
plugins, see
Section 6.5.1.2, “SHA-256 Pluggable Authentication”, and
Section 6.5.1.3, “Caching SHA-2 Pluggable Authentication”.
--shared-memory-base-name=
name
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.
By default, mysql_upgrade installs the
sys
schema if it is not installed, and
upgrades it to the current version otherwise. The
--skip-sys-schema
option suppresses this behavior.
--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.
Options that begin with
--ssl
specify whether to
connect to the server using SSL and indicate where to find
SSL keys and certificates. See
Section 6.4.2, “Command Options for Encrypted Connections”.
--ssl-fips-mode={OFF|ON|STRICT}
Controls whether to enable FIPS mode on the client side. The
--ssl-fips-mode
option
differs from other
--ssl-
options in that it is not used to establish encrypted
connections, but rather to affect which cryptographic
operations are permitted. See Section 6.6, “FIPS Support”.
xxx
These --ssl-fips-mode
values are permitted:
OFF
: Disable FIPS mode.
ON
: Enable FIPS mode.
STRICT
: Enable “strict”
FIPS mode.
If the OpenSSL FIPS Object Module is not available, the
only permitted value for
--ssl-fips-mode
is
OFF
. In this case, setting
--ssl-fips-mode
to
ON
or STRICT
causes
the client to produce a warning at startup and to operate
in non-FIPS mode.
--tls-ciphersuites=
ciphersuite_list
For client programs, specifies which TLSv1.3 ciphersuites the client permits for encrypted connections. The value is a list of one or more colon-separated ciphersuite names. The ciphersuites that can be named for this option depend on the SSL library used to compile MySQL. For details, see Section 6.4.6, “Encrypted Connection Protocols and Ciphers”.
This option was added in MySQL 8.0.16.
The protocols the client permits for encrypted connections. The value is a list of one or more comma-separated protocol names. The protocols that can be named for this option depend on the SSL library used to compile MySQL. For details, see Section 6.4.6, “Encrypted Connection Protocols and Ciphers”.
Upgrade only the system tables in the
mysql
schema, do not upgrade user
schemas.
--user=
,
user_name
-u
user_name
The MySQL user name to use when connecting to the server.
The default user name is root
.
Verbose mode. Print more information about what the program does.
--version-check
,
-k
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.
When the server is running with global transaction
identifiers (GTIDs) enabled
(gtid_mode=ON
), do not
enable binary logging by mysql_upgrade.
This section describes client programs that connect to the MySQL server.
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()
.
Alternatively, MySQL Shell offers access to the X DevAPI. For details, see MySQL Shell 8.0 (part of MySQL 8.0).
Using mysql is very easy. Invoke it from the prompt of your command interpreter as follows:
shell> mysql db_name
Or:
shell>mysql --user=
Enter password:user_name
--passworddb_name
your_password
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
> output.tab
On Unix, the mysql client logs statements executed interactively to a history file. See Section 4.5.1.3, “mysql Client Logging”.
mysql supports the following options, which
can be specified on the command line or in the
[mysql]
and [client]
groups of an option file. For information about option files
used by MySQL programs, see Section 4.2.7, “Using Option Files”.
Table 4.8 mysql Client Options
Format | Description | Introduced | Removed |
---|---|---|---|
--auto-rehash | Enable automatic rehashing | ||
--auto-vertical-output | Enable automatic vertical result set display | ||
--batch | Do not use history file | ||
--binary-as-hex | Display binary values in hexadecimal notation | 8.0.2 | |
--binary-mode | Disable \r\n - to - \n translation and treatment of \0 as end-of-query | ||
--bind-address | Use specified network interface to connect to MySQL Server | ||
--character-sets-dir | Directory where character sets are installed | ||
--column-names | Write column names in results | ||
--column-type-info | Display result set metadata | ||
--comments | Whether to retain or strip comments in statements sent to the server | ||
--compress | Compress all information sent between client and server | ||
--connect-expired-password | Indicate to server that client can handle expired-password sandbox mode. | ||
--connect_timeout | Number of seconds before connection timeout | ||
--database | The database to use | ||
--debug | Write debugging log; supported only if MySQL was built with debugging support | ||
--debug-check | Print debugging information when program exits | ||
--debug-info | Print debugging information, memory, and CPU statistics when program exits | ||
--default-auth | Authentication plugin to use | ||
--default-character-set | Specify default character set | ||
--defaults-extra-file | Read named option file in addition to usual option files | ||
--defaults-file | Read only named option file | ||
--defaults-group-suffix | Option group suffix value | ||
--delimiter | Set the statement delimiter | ||
--enable-cleartext-plugin | Enable cleartext authentication plugin | ||
--execute | Execute the statement and quit | ||
--force | Continue even if an SQL error occurs | ||
--get-server-public-key | Request RSA public key from server | 8.0.3 | |
--help | Display help message and exit | ||
--histignore | Patterns specifying which statements to ignore for logging | ||
--host | Connect to MySQL server on given host | ||
--html | Produce HTML output | ||
--ignore-spaces | Ignore spaces after function names | ||
--init-command | SQL statement to execute after connecting | ||
--line-numbers | Write line numbers for errors | ||
--local-infile | Enable or disable for LOCAL capability for LOAD DATA | ||
--login-path | Read login path options from .mylogin.cnf | ||
--max_allowed_packet | Maximum packet length to send to or receive from server | ||
--max_join_size | The automatic limit for rows in a join when using --safe-updates | ||
--named-commands | Enable named mysql commands | ||
--net_buffer_length | Buffer size for TCP/IP and socket communication | ||
--no-auto-rehash | Disable automatic rehashing | ||
--no-beep | Do not beep when errors occur | ||
--no-defaults | Read no option files | ||
--one-database | Ignore statements except those for the default database named on the command line | ||
--pager | Use the given command for paging query output | ||
--password | Password to use when connecting to server | ||
--pipe | On Windows, connect to server using named pipe | ||
--plugin-dir | Directory where plugins are installed | ||
--port | TCP/IP port number for connection | ||
--print-defaults | Print default options | ||
--prompt | Set the prompt to the specified format | ||
--protocol | Connection protocol to use | ||
--quick | Do not cache each query result | ||
--raw | Write column values without escape conversion | ||
--reconnect | If the connection to the server is lost, automatically try to reconnect | ||
--i-am-a-dummy, --safe-updates | Allow only UPDATE and DELETE statements that specify key values | ||
--secure-auth | Do not send passwords to server in old (pre-4.1) format | 8.0.3 | |
--select_limit | The automatic limit for SELECT statements when using --safe-updates | ||
--server-public-key-path | Path name to file containing RSA public key | ||
--shared-memory-base-name | Name of shared memory to use for shared-memory connections | ||
--show-warnings | Show warnings after each statement if there are any | ||
--sigint-ignore | Ignore SIGINT signals (typically the result of typing Control+C) | ||
--silent | Silent mode | ||
--skip-auto-rehash | Disable automatic rehashing | ||
--skip-column-names | Do not write column names in results | ||
--skip-line-numbers | Skip line numbers for errors | ||
--skip-named-commands | Disable named mysql commands | ||
--skip-pager | Disable paging | ||
--skip-reconnect | Disable reconnecting | ||
--socket | For connections to localhost, the Unix socket file or Windows named pipe to use | ||
--ssl-ca | File that contains list of trusted SSL Certificate Authorities | ||
--ssl-capath | Directory that contains trusted SSL Certificate Authority certificate files | ||
--ssl-cert | File that contains X.509 certificate | ||
--ssl-cipher | List of permitted ciphers for connection encryption | ||
--ssl-crl | File that contains certificate revocation lists | ||
--ssl-crlpath | Directory that contains certificate revocation-list files | ||
--ssl-fips-mode | Whether to enable FIPS mode on client side | 8.0.11 | |
--ssl-key | File that contains X.509 key | ||
--ssl-mode | Security state of connection to server | ||
--syslog | Log interactive statements to syslog | ||
--table | Display output in tabular format | ||
--tee | Append a copy of output to named file | ||
--tls-ciphersuites | TLSv1.3 ciphersuites permitted for encrypted connections | 8.0.16 | |
--tls-version | Protocols permitted for encrypted connections | ||
--unbuffered | Flush the buffer after each query | ||
--user | MySQL user name to use when connecting to server | ||
--verbose | Verbose mode | ||
--version | Display version information and exit | ||
--vertical | Print query output rows vertically (one line per column value) | ||
--wait | If the connection cannot be established, wait and retry instead of aborting | ||
--xml | Produce XML output |
--help
, -?
Display a help message and exit.
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.
Cause result sets to be displayed vertically if they are too
wide for the current window, and using normal tabular format
otherwise. (This applies to statements terminated by
;
or \G
.)
--batch
, -B
Print results using tab as the column separator, with each row on a new line. With this option, mysql does not use the history file.
Batch mode results in nontabular output format and escaping
of special characters. Escaping may be disabled by using raw
mode; see the description for the
--raw
option.
When this option is given, mysql displays
binary data using hexadecimal notation
(0x
).
This occurs whether the overall output dislay format is
tabular, vertical, HTML, or XML.
value
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).
On a computer having multiple network interfaces, use this option to select which interface to use for connecting to the MySQL server.
The directory where character sets are installed. See Section 10.14, “Character Set Configuration”.
Write column names in results.
Display result set metadata.
--comments
,
-c
Whether to strip or preserve comments in statements sent to
the server. The default is
--skip-comments
(strip comments), enable with
--comments
(preserve
comments).
The mysql client always passes optimizer hints to the server, regardless of whether this option is given.
Comment stripping is deprecated. This feature and the options to control it will be removed in a future MySQL release.
--compress
,
-C
Compress all information sent between the client and the server if both support compression.
Indicate to the server that the client can handle sandbox mode if the account used to connect has an expired password. This can be useful for noninteractive invocations of mysql because normally the server disconnects noninteractive clients that attempt to connect using an account with an expired password. (See Section 6.3.9, “Server Handling of Expired Passwords”.)
--database=
,
db_name
-D
db_name
The database to use. This is useful primarily in an option file.
--debug[=
,
debug_options
]-#
[
debug_options
]
Write a debugging log. A typical
debug_options
string is
d:t:o,
.
The default is file_name
d:t:o,/tmp/mysql.trace
.
This option is available only if MySQL was built using
WITH_DEBUG
. MySQL release
binaries provided by Oracle are not
built using this option.
Print some debugging information when the program exits.
--debug-info
,
-T
Print debugging information and memory and CPU usage statistics when the program exits.
A hint about the client-side authentication plugin to use. See Section 6.3.10, “Pluggable Authentication”.
--default-character-set=
charset_name
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.
For more information, see Section 10.4, “Connection Character Sets and Collations”, and Section 10.14, “Character Set Configuration”.
--defaults-extra-file=
file_name
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.
For additional information about this and other option-file options, see Section 4.2.8, “Command-Line Options that Affect Option-File Handling”.
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.
Exception: Even with
--defaults-file
, client
programs read .mylogin.cnf
.
For additional information about this and other option-file options, see Section 4.2.8, “Command-Line Options that Affect Option-File Handling”.
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.
For additional information about this and other option-file options, see Section 4.2.8, “Command-Line Options that Affect Option-File Handling”.
Set the statement delimiter. The default is the semicolon
character (;
).
Disable named commands. Use the \*
form
only, or use named commands only at the beginning of a line
ending with a semicolon (;
).
mysql starts with this option
enabled by default. However, even with
this option, long-format commands still work from the first
line. See Section 4.5.1.2, “mysql Client Commands”.
Enable the mysql_clear_password
cleartext
authentication plugin. (See
Section 6.5.1.4, “Client-Side Cleartext Pluggable Authentication”.)
--execute=
,
statement
-e
statement
Execute the statement and quit. The default output format is
like that produced with
--batch
. See
Section 4.2.5, “Using Options on the Command Line”, for some examples.
With this option, mysql does not use the
history file.
--force
, -f
Continue even if an SQL error occurs.
Request from the server the public key required for RSA key
pair-based password exchange. This option applies to clients
that that authenticate with the
caching_sha2_password
authentication
plugin. For that plugin, the server does not send the public
key unless requested. This option is ignored for accounts
that do not authenticate with that plugin. It is also
ignored if RSA-based password exchange is not used, as is
the case when the client connects to the server using a
secure connection.
If
--server-public-key-path=
is given and specifies a valid public key file, it takes
precedence over
file_name
--get-server-public-key
.
For information about the
caching_sha2_password
plugin, see
Section 6.5.1.3, “Caching SHA-2 Pluggable Authentication”.
A list of one or more colon-separated 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 Section 4.5.1.3, “mysql Client Logging”.
--host=
,
host_name
-h
host_name
Connect to the MySQL server on the given host.
--html
, -H
Produce HTML output.
--ignore-spaces
,
-i
Ignore spaces after function names. The effect of this is
described in the discussion for the
IGNORE_SPACE
SQL mode (see
Section 5.1.11, “Server SQL Modes”).
SQL statement to execute after connecting to the server. If auto-reconnect is enabled, the statement is executed again after reconnection occurs.
Write line numbers for errors. Disable this with
--skip-line-numbers
.
Enable or disable LOCAL
capability for
LOAD DATA
. For
mysql, this capability is disabled by
default. 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
data loading also requires that the server permits it; see
Section 6.1.6, “Security Issues with LOAD DATA LOCAL”
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
Section 4.6.7, “mysql_config_editor — MySQL Configuration Utility”.
For additional information about this and other option-file options, see Section 4.2.8, “Command-Line Options that Affect Option-File Handling”.
--named-commands
,
-G
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
Section 4.5.1.2, “mysql Client Commands”.
--no-auto-rehash
,
-A
This has the same effect as
--skip-auto-rehash
.
See the description for
--auto-rehash
.
--no-beep
, -b
Do not beep when errors occur.
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
Section 4.6.7, “mysql_config_editor — MySQL Configuration Utility”.)
For additional information about this and other option-file options, see Section 4.2.8, “Command-Line Options that Affect Option-File Handling”.
--one-database
,
-o
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
at the
beginning of the input. Then, for each
db_name
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.
Suppose that mysql is invoked to process this set of statements:
DELETE FROM db2.t2; USE db2; DROP TABLE db1.t1; CREATE TABLE db1.t1 (i INT); USE db1; INSERT INTO t1 (i) VALUES(1); CREATE TABLE db2.t1 (j INT);
If the command line is mysql --force --one-database db1, mysql handles the input as follows:
The DELETE
statement is
executed because the default database is
db1
, even though the statement names
a table in a different database.
The DROP TABLE
and
CREATE TABLE
statements
are not executed because the default database is not
db1
, even though the statements name
a table in db1
.
The INSERT
and
CREATE TABLE
statements
are executed because the default database is
db1
, even though the
CREATE TABLE
statement
names a table in a different database.
Use the given command for paging query output. If the
command is omitted, the default pager is the value of your
PAGER
environment variable. Valid pagers
are less, more,
cat [> filename], and so forth. This
option works only on Unix and only in interactive mode. To
disable paging, use
--skip-pager
.
Section 4.5.1.2, “mysql Client Commands”, discusses output paging
further.
--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,
mysql prompts for one.
Specifying a password on the command line should be considered insecure. See Section 6.1.2.1, “End-User Guidelines for Password Security”. 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.
The directory in which to look for plugins. Specify this
option if the --default-auth
option is used to specify an authentication plugin but
mysql does not find it. See
Section 6.3.10, “Pluggable Authentication”.
--port=
,
port_num
-P
port_num
The TCP/IP port number to use for the connection.
Print the program name and all options that it gets from option files.
For additional information about this and other option-file options, see Section 4.2.8, “Command-Line Options that Affect Option-File Handling”.
Set the prompt to the specified format. The default is
mysql>
. The special sequences that the
prompt can contain are described in
Section 4.5.1.2, “mysql Client Commands”.
--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 Section 4.2.2, “Connecting to the MySQL Server”.
--quick
, -q
Do not cache each query result, print each row as it is received. This may slow down the server if the output is suspended. With this option, mysql does not use the history file.
--raw
, -r
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:
%mysql
mysql> SELECT CHAR(92); +----------+ | CHAR(92) | +----------+ | \ | +----------+ %mysql -s
mysql> SELECT CHAR(92); CHAR(92) \\ %mysql -s -r
mysql> SELECT CHAR(92); CHAR(92) \
If the connection to the server is lost, automatically try
to reconnect. A single reconnect attempt is made each time
the connection is lost. To suppress reconnection behavior,
use
--skip-reconnect
.
--safe-updates
,
--i-am-a-dummy
,
-U
If this option is enabled,
UPDATE
and
DELETE
statements that do not
use a key in the WHERE
clause or a
LIMIT
clause produce an error. In
addition, restrictions are placed on
SELECT
statements that
produce (or are estimated to produce) very large result
sets. If you have set this option in an option file, you can
use
--skip-safe-updates
on the command line to override it. For more information
about this option, see Section 4.5.1.6.4, “Using Safe-Updates Mode (--safe-updates)”.
This option was removed in MySQL 8.0.3.
--server-public-key-path=
file_name
The path name to a file containing a client-side copy of the
public key required by the server for RSA key pair-based
password exchange. The file must be in PEM format. This
option applies to clients that authenticate with the
sha256_password
or
caching_sha2_password
authentication
plugin. This option is ignored for accounts that do not
authenticate with one of those plugins. It is also ignored
if RSA-based password exchange is not used, as is the case
when the client connects to the server using a secure
connection.
If
--server-public-key-path=
is given and specifies a valid public key file, it takes
precedence over
file_name
--get-server-public-key
.
This option is available only if MySQL was built using OpenSSL.
For information about the sha256_password
and caching_sha2_password
plugins, see
Section 6.5.1.2, “SHA-256 Pluggable Authentication”, and
Section 6.5.1.3, “Caching SHA-2 Pluggable Authentication”.
--shared-memory-base-name=
name
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.
Cause warnings to be shown after each statement if there are any. This option applies to interactive and batch mode.
Ignore SIGINT
signals (typically the
result of typing Control+C).
--silent
, -s
Silent mode. Produce less output. This option can be given multiple times to produce less and less output.
This option results in nontabular output format and escaping
of special characters. Escaping may be disabled by using raw
mode; see the description for the
--raw
option.
Do not write column names in results.
Do not write line numbers for errors. Useful when you want to compare result files that include error messages.
--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.
Options that begin with
--ssl
specify whether to
connect to the server using SSL and indicate where to find
SSL keys and certificates. See
Section 6.4.2, “Command Options for Encrypted Connections”.
--ssl-fips-mode={OFF|ON|STRICT}
Controls whether to enable FIPS mode on the client side. The
--ssl-fips-mode
option differs
from other
--ssl-
options in that it is not used to establish encrypted
connections, but rather to affect which cryptographic
operations are permitted. See Section 6.6, “FIPS Support”.
xxx
These --ssl-fips-mode
values
are permitted:
OFF
: Disable FIPS mode.
ON
: Enable FIPS mode.
STRICT
: Enable “strict”
FIPS mode.
If the OpenSSL FIPS Object Module is not available, the
only permitted value for
--ssl-fips-mode
is
OFF
. In this case, setting
--ssl-fips-mode
to
ON
or STRICT
causes
the client to produce a warning at startup and to operate
in non-FIPS mode.
--syslog
, -j
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.
Mar 7 12:39:25 myhost MysqlClient[20824]: SYSTEM_USER:'oscar', MYSQL_USER:'my_oscar', CONNECTION_ID:23, DB_SERVER:'127.0.0.1', DB:'--', QUERY:'USE test;' Mar 7 12:39:28 myhost MysqlClient[20824]: SYSTEM_USER:'oscar', MYSQL_USER:'my_oscar', CONNECTION_ID:23, DB_SERVER:'127.0.0.1', DB:'test', QUERY:'SHOW TABLES;'
For more information, see Section 4.5.1.3, “mysql Client Logging”.
--table
, -t
Display output in table format. This is the default for interactive use, but can be used to produce table output in batch mode.
Append a copy of output to the given file. This option works only in interactive mode. Section 4.5.1.2, “mysql Client Commands”, discusses tee files further.
--tls-ciphersuites=
ciphersuite_list
For client programs, specifies which TLSv1.3 ciphersuites the client permits for encrypted connections. The value is a list of one or more colon-separated ciphersuite names. The ciphersuites that can be named for this option depend on the SSL library used to compile MySQL. For details, see Section 6.4.6, “Encrypted Connection Protocols and Ciphers”.
This option was added in MySQL 8.0.16.
The protocols the client permits for encrypted connections. The value is a list of one or more comma-separated protocol names. The protocols that can be named for this option depend on the SSL library used to compile MySQL. For details, see Section 6.4.6, “Encrypted Connection Protocols and Ciphers”.
--unbuffered
,
-n
Flush the buffer after each query.
--user=
,
user_name
-u
user_name
The MySQL user name to use when connecting to the server.
--verbose
, -v
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.)
--version
, -V
Display version information and exit.
--vertical
,
-E
Print query output rows vertically (one line per column
value). Without this option, you can specify vertical output
for individual statements by terminating them with
\G
.
--wait
, -w
If the connection cannot be established, wait and retry instead of aborting.
--xml
, -X
Produce XML output.
<field name="column_name
">NULL</field>
The output when --xml
is used
with mysql matches that of
mysqldump
--xml
. See
Section 4.5.4, “mysqldump — A Database Backup Program”, for details.
The XML output also uses an XML namespace, as shown here:
shell> mysql --xml -uroot -e "SHOW VARIABLES LIKE 'version%'"
<?xml version="1.0"?>
<resultset statement="SHOW VARIABLES LIKE 'version%'" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<row>
<field name="Variable_name">version</field>
<field name="Value">5.0.40-debug</field>
</row>
<row>
<field name="Variable_name">version_comment</field>
<field name="Value">Source distribution</field>
</row>
<row>
<field name="Variable_name">version_compile_machine</field>
<field name="Value">i686</field>
</row>
<row>
<field name="Variable_name">version_compile_os</field>
<field name="Value">suse-linux-gnu</field>
</row>
</resultset>
(See Bug #25946.)
You can also set the following variables by using
--
.
var_name
=value
The number of seconds before connection timeout. (Default
value is 0
.)
The maximum size of the buffer for client/server communication. The default is 16MB, the maximum is 1GB.
The automatic limit for rows in a join when using
--safe-updates
. (Default value
is 1,000,000.)
The buffer size for TCP/IP and socket communication. (Default value is 16KB.)
The automatic limit for
SELECT
statements when using
--safe-updates
. (Default value
is 1,000.)
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.
help [
,
arg
]\h [
,
arg
]\? [
,
arg
]? [
arg
]
Display a help message listing the available mysql commands.
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
Section 4.5.1.4, “mysql Client Server-Side Help”.
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 the current input. Use this if you change your mind about executing the statement that you are entering.
connect [
,
db_name
host_name
]]\r [
db_name
host_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.
Change the string that mysql interprets
as the separator between SQL statements. The default is the
semicolon character (;
).
The delimiter string can be specified as an unquoted or
quoted argument on the delimiter
command
line. Quoting can be done with either single quote
('
), double quote ("
),
or backtick (`
) characters. To include a
quote within a quoted string, either quote the string with a
different quote character or escape the quote with a
backslash (\
) character. Backslash should
be avoided outside of quoted strings because it is the
escape character for MySQL. For an unquoted argument, the
delimiter is read up to the first space or end of line. For
a quoted argument, the delimiter is read up to the matching
quote on the line.
mysql interprets instances of the
delimiter string as a statement delimiter anywhere it
occurs, except within quoted strings. Be careful about
defining a delimiter that might occur within other words.
For example, if you define the delimiter as
X
, you will be unable to use the word
INDEX
in statements.
mysql interprets this as
INDE
followed by the delimiter
X
.
When the delimiter recognized by mysql is
set to something other than the default of
;
, instances of that character are sent
to the server without interpretation. However, the server
itself still interprets ;
as a statement
delimiter and processes statements accordingly. This
behavior on the server side comes into play for
multiple-statement execution (see
Section 28.7.23, “C API Multiple Statement Execution Support”), and for parsing
the body of stored procedures and functions, triggers, and
events (see Section 24.1, “Defining Stored Programs”).
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.
Send the current statement to the server to be executed and display the result using vertical format.
Exit mysql.
Send the current statement to the server to be executed.
Disable output paging. See the description for
pager
.
The nopager
command works only in Unix.
Disable output copying to the tee file. See the description
for tee
.
Disable display of warnings after each statement.
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 the current input statement without executing it.
Reconfigure the mysql prompt to the given string. The special character sequences that can be used in the prompt are described later in this section.
If you specify the prompt
command with no
argument, mysql resets the prompt to the
default of mysql>
.
Exit mysql.
Rebuild the completion hash that enables database, table,
and column name completion while you are entering
statements. (See the description for the
--auto-rehash
option.)
Reset the connection to clear the session state.
Resetting a connection has effects similar to
mysql_change_user()
or an
auto-reconnect except that the connection is not closed and
reopened, and re-authentication is not done. See
Section 28.7.7.3, “mysql_change_user()”) and see
Section 28.7.28, “C API Automatic Reconnection Control”).
This example shows how resetconnection
clears a value maintained in the session state:
mysql>SELECT LAST_INSERT_ID(3);
+-------------------+ | LAST_INSERT_ID(3) | +-------------------+ | 3 | +-------------------+ mysql>SELECT LAST_INSERT_ID();
+------------------+ | LAST_INSERT_ID() | +------------------+ | 3 | +------------------+ mysql>resetconnection;
mysql>SELECT LAST_INSERT_ID();
+------------------+ | LAST_INSERT_ID() | +------------------+ | 0 | +------------------+
source
, file_name
\.
file_name
Read the named file and executes the statements contained
therein. On Windows, you can specify path name separators as
/
or \\
.
Quote characters are taken as part of the file name itself. For best results, the name should not include space characters.
Provide status information about the connection and the
server you are using. If you are running with
--safe-updates
enabled,
status
also prints the values for the
mysql variables that affect your queries.
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
as the default
database.
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:
mysql>pager cat | tee /dr1/tmp/res.txt \
| tee /dr2/tmp/res2.txt | less -n -i -S
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
|
\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 |
\ |
|
You can set the prompt in several ways:
Use an environment variable. You can
set the MYSQL_PS1
environment variable to
a prompt string. For example:
shell> export MYSQL_PS1="(\u@\h) [\d]> "
Use a command-line option. You can set
the --prompt
option on the
command line to mysql. For example:
shell> mysql --prompt="(\u@\h) [\d]> "
(user@host) [database]>
Use an option file. You can set the
prompt
option in the
[mysql]
group of any MySQL option file,
such as /etc/my.cnf
or the
.my.cnf
file in your home directory.
For example:
[mysql] prompt=(\\u@\\h) [\\d]>\\_
In this example, note that the backslashes are doubled. If
you set the prompt using the prompt
option in an option file, it is advisable to double the
backslashes when using the special prompt options. There is
some overlap in the set of permissible prompt options and
the set of special escape sequences that are recognized in
option files. (The rules for escape sequences in option
files are listed in Section 4.2.7, “Using Option Files”.) The
overlap may cause you problems if you use single
backslashes. For example, \s
is
interpreted as a space rather than as the current seconds
value. The following example shows how to define a prompt
within an option file to include the current time in
HH:MM:SS>
format:
[mysql] prompt="\\r:\\m:\\s> "
Set the prompt interactively. You can
change your prompt interactively by using the
prompt
(or \R
)
command. For example:
mysql>prompt (\u@\h) [\d]>\_
PROMPT set to '(\u@\h) [\d]>\_' (user
@host
) [database
]> (user
@host
) [database
]> prompt Returning to default PROMPT of mysql> mysql>
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.
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.
mysql logs each nonignored, nonempty statement line individually.
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 list of one or more colon-separated 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*"
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 Section 6.1.2.1, “End-User Guidelines for Password Security”.
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
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 operating 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.
Mar 7 12:39:25 myhost MysqlClient[20824]: SYSTEM_USER:'oscar', MYSQL_USER:'my_oscar', CONNECTION_ID:23, DB_SERVER:'127.0.0.1', DB:'--', QUERY:'USE test;' Mar 7 12:39:28 myhost MysqlClient[20824]: SYSTEM_USER:'oscar', MYSQL_USER:'my_oscar', CONNECTION_ID:23, DB_SERVER:'127.0.0.1', DB:'test', QUERY:'SHOW TABLES;'
mysql> help search_string
If you provide an argument to the help
command, mysql uses it as a search string to
access server-side help from the contents of the MySQL Reference
Manual. The proper operation of this command requires that the
help tables in the mysql
database be
initialized with help topic information (see
Section 5.1.14, “Server-Side Help Support”).
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
Use help contents to see a list of the help categories:
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
If the search string matches multiple items, mysql shows a list of matching topics:
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 | Encrypted |
+---------------+-----------+-----------+
| binlog.000015 | 724935 | Yes |
| binlog.000016 | 733481 | Yes |
+---------------+-----------+-----------+
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
The mysql client typically is used interactively, like this:
shell> mysql db_name
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
statement as the
first statement in the file, it is unnecessary to specify the
database name on the command line:
db_name
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
mysql>file_name
\.
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
.
For more information about batch mode, see Section 3.5, “Using mysql in Batch Mode”.
This section describes some techniques that can help you use mysql more effectively.
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.
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.
Execute mysql.exe with the
--default-character-set=utf8
(or utf8mb4
) option. This option is
necessary because utf16le
is one of the
character sets that cannot be used as the client character
set. See
Impermissible Client Character Sets.
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
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:
mysql> SELECT * FROM mails WHERE LENGTH(txt) < 300 LIMIT 300,1\G
*************************** 1. row ***************************
msg_nro: 3068
date: 2000-03-01 23:29:50
time_zone: +0200
mail_from: Monty
reply: monty@no.spam.com
mail_to: "Thimble Smith" <tim@no.spam.com>
sbj: UTF-8
txt: >>>>> "Thimble" == Thimble Smith writes:
Thimble> Hi. I think this is a good idea. Is anyone familiar
Thimble> with UTF-8 or Unicode? Otherwise, I'll put this on my
Thimble> TODO list and see what happens.
Yes, please do that.
Regards,
Monty
file: inbox-jani-1
hash: 190402944
1 row in set (0.09 sec)
For beginners, a useful startup option is
--safe-updates
(or
--i-am-a-dummy
,
which has the same effect). Safe-updates mode is helpful for
cases when you might have issued an
UPDATE
or
DELETE
statement but forgotten
the WHERE
clause indicating which rows to
modify. Normally, such statements update or delete all rows in
the table. With --safe-updates
,
you can modify rows only by specifying the key values that
identify them, or a LIMIT
clause, or both.
This helps prevent accidents. Safe-updates mode also restricts
SELECT
statements that produce
(or are estimated to produce) very large result sets.
The --safe-updates
option causes
mysql to execute the following statement
when it connects to the MySQL server, to set the session
values of the
sql_safe_updates
,
sql_select_limit
, and
max_join_size
system
variables:
SET sql_safe_updates=1, sql_select_limit=1000, max_join_size=1000000;
The
SET
statement affects statement processing as follows:
Enabling sql_safe_updates
causes UPDATE
and
DELETE
statements to
produce an error if they do not specify a key constraint
in the WHERE
clause, or provide a
LIMIT
clause, or both. For example:
UPDATEtbl_name
SETnot_key_column
=val
WHEREkey_column
=val
; UPDATEtbl_name
SETnot_key_column
=val
LIMIT 1;
Setting sql_select_limit
to 1,000 causes the server to limit all
SELECT
result sets to 1,000
rows unless the statement includes a
LIMIT
clause.
Setting max_join_size
to
1,000,000 causes multiple-table
SELECT
statements to
produce an error if the server estimates it must examine
more than 1,000,000 row combinations.
To specify result set limits different from 1,000 and
1,000,000, you can override the defaults by using the
--select_limit
and
--max_join_size
options when you
invoke mysql:
mysql --safe-updates --select_limit=500 --max_join_size=10000
It is possible for UPDATE
and
DELETE
statements to produce an
error in safe-updates mode even with a key specified in the
WHERE
clause, if the optimizer decides not
to use the index on the key column:
Range access on the index cannot be used if memory usage
exceeds that permitted by the
range_optimizer_max_mem_size
system variable. The optimizer then falls back to a table
scan. See Limiting Memory Use for Range Optimization.
If key comparisons require type conversion, the index may
not be used (see Section 8.3.1, “How MySQL Uses Indexes”). Suppose
that an indexed string column c1
is
compared to a numeric value using WHERE c1 =
2222
. For such comparisons, the string value is
converted to a number and the operands are compared
numerically (see Section 12.2, “Type Conversion in Expression Evaluation”),
preventing use of the index. If safe-updates mode is
enabled, an error occurs.
As of MySQL 8.0.13, safe-updates mode also includes these behaviors:
EXPLAIN
with
UPDATE
and
DELETE
statements does not
produce safe-updates errors. This enables use of
EXPLAIN
plus
SHOW WARNINGS
to see why an
index is not used, which can be helpful in cases such as
when a
range_optimizer_max_mem_size
violation or type conversion occurs and the optimizer does
not use an index even though a key column was specified in
the WHERE
clause.
When a safe-updates error occurs, the error message
includes the first diagnostic that was produced, to
provide information about the reason for failure. For
example, the message may indicate that the
range_optimizer_max_mem_size
value was exceeded or type conversion occurred, either of
which can preclude use of an index.
For multiple-table deletes and updates, an error is produced with safe updates enabled only if any target table uses a table scan.
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:
mysql>SET @a=1;
Query OK, 0 rows affected (0.05 sec) mysql>INSERT INTO t VALUES(@a);
ERROR 2006: MySQL server has gone away No connection. Trying to reconnect... Connection id: 1 Current database: test Query OK, 1 row affected (1.30 sec) mysql>SELECT * FROM t;
+------+ | a | +------+ | NULL | +------+ 1 row in set (0.05 sec)
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 Section 28.7.28, “C API Automatic Reconnection Control”.
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.
Invoke mysqladmin like this:
shell> mysqladmin [options
] command
[command-arg
] [command
[command-arg
]] ...
mysqladmin supports the following commands. Some of the commands take an argument following the command name.
Create a new database named
db_name
.
Tell the server to write debug information to the error log.
The connected user must have the
SUPER
privilege. Format and
content of this information is subject to change.
This includes information about the Event Scheduler. See Section 24.4.5, “Event Scheduler Status”.
Delete the database named db_name
and all its tables.
Display the server status variables and their values.
Flush all information in the host cache. See Section 8.12.4.2, “DNS Lookup Optimization and the Host Cache”.
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.
Reload the grant tables (same as reload
).
Clear status variables.
Flush all tables.
Flush the thread cache.
Kill server threads. If multiple thread ID values are given, there must be no spaces in the list.
To kill threads belonging to other users, the connected user
must have the
CONNECTION_ADMIN
or
SUPER
privilege.
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.
Setting a password using mysqladmin should be considered insecure. On some systems, your password becomes visible to system status programs such as ps that may be invoked by other users to display command lines. MySQL clients typically overwrite the command-line password argument with zeros during their initialization sequence. However, there is still a brief interval during which the value is visible. Also, on some systems this overwriting strategy is ineffective and the password remains visible to ps. (SystemV Unix systems and perhaps others are subject to this problem.)
If the new_password
value
contains spaces or other characters that are special to your
command interpreter, you need to enclose it within quotation
marks. On Windows, be sure to use double quotation marks
rather than single quotation marks; single quotation marks
are not stripped from the password, but rather are
interpreted as part of the password. For example:
shell> mysqladmin password "my new password"
In MySQL 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.
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.
Show a list of active server threads. This is like the
output of the SHOW
PROCESSLIST
statement. If the
--verbose
option is
given, the output is like that of
SHOW FULL
PROCESSLIST
. (See
Section 13.7.6.29, “SHOW PROCESSLIST Syntax”.)
Reload the grant tables.
Flush all tables and close and open log files.
Stop the server.
Start replication on a slave server.
Display a short server status message.
Stop replication on a slave server.
Display the server system variables and their values.
Display version information from the server.
All commands can be shortened to any unique prefix. For example:
shell> mysqladmin proc stat
+----+-------+-----------+----+---------+------+-------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+-------+-----------+----+---------+------+-------+------------------+
| 51 | monty | localhost | | Query | 0 | | show processlist |
+----+-------+-----------+----+---------+------+-------+------------------+
Uptime: 1473624 Threads: 1 Questions: 39487
Slow queries: 0 Opens: 541 Flush tables: 1
Open tables: 19 Queries per second avg: 0.0268
The mysqladmin status command result displays the following values:
The number of seconds the MySQL server has been running.
The number of active threads (clients).
The number of questions (queries) from clients since the server was started.
The number of queries that have taken more than
long_query_time
seconds.
See Section 5.4.5, “The Slow Query Log”.
The number of tables the server has opened.
The number of flush-*
,
refresh
, and reload
commands the server has executed.
The number of tables that currently are open.
If you execute mysqladmin shutdown when connecting to a local server using a Unix socket file, mysqladmin waits until the server's process ID file has been removed, to ensure that the server has stopped properly.
mysqladmin supports the following options,
which can be specified on the command line or in the
[mysqladmin]
and [client]
groups of an option file. For information about option files
used by MySQL programs, see Section 4.2.7, “Using Option Files”.
Table 4.9 mysqladmin Options
Format | Description | Introduced | Removed |
---|---|---|---|
--bind-address | Use specified network interface to connect to MySQL Server | ||
--compress | Compress all information sent between client and server | ||
--connect_timeout | Number of seconds before connection timeout | ||
--count | Number of iterations to make for repeated command execution | ||
--debug | Write debugging log | ||
--debug-check | Print debugging information when program exits | ||
--debug-info | Print debugging information, memory, and CPU statistics when program exits | ||
--default-auth | Authentication plugin to use | ||
--default-character-set | Specify default character set | ||
--defaults-extra-file | Read named option file in addition to usual option files | ||
--defaults-file | Read only named option file | ||
--defaults-group-suffix | Option group suffix value | ||
--enable-cleartext-plugin | Enable cleartext authentication plugin | ||
--force | Continue even if an SQL error occurs | ||
--get-server-public-key | Request RSA public key from server | 8.0.3 | |
--help | Display help message and exit | ||
--host | Connect to MySQL server on given host | ||
--login-path | Read login path options from .mylogin.cnf | ||
--no-beep | Do not beep when errors occur | ||
--no-defaults | Read no option files | ||
--password | Password to use when connecting to server | ||
--pipe | On Windows, connect to server using named pipe | ||
--plugin-dir | Directory where plugins are installed | ||
--port | TCP/IP port number for connection | ||
--print-defaults | Print default options | ||
--protocol | Connection protocol to use | ||
--relative | Show the difference between the current and previous values when used with the --sleep option | ||
--secure-auth | Do not send passwords to server in old (pre-4.1) format | 8.0.3 | |
--server-public-key-path | Path name to file containing RSA public key | 8.0.4 | |
--shared-memory-base-name | Name of shared memory to use for shared-memory connections | ||
--show-warnings | Show warnings after statement execution | ||
--shutdown_timeout | The maximum number of seconds to wait for server shutdown | ||
--silent | Silent mode | ||
--sleep | Execute commands repeatedly, sleeping for delay seconds in between | ||
--socket | For connections to localhost, the Unix socket file to use | ||
--ssl-ca | File that contains list of trusted SSL Certificate Authorities | ||
--ssl-capath | Directory that contains trusted SSL Certificate Authority certificate files | ||
--ssl-cert | File that contains X.509 certificate | ||
--ssl-cipher | List of permitted ciphers for connection encryption | ||
--ssl-crl | File that contains certificate revocation lists | ||
--ssl-crlpath | Directory that contains certificate revocation-list files | ||
--ssl-fips-mode | Whether to enable FIPS mode on client side | 8.0.11 | |
--ssl-key | File that contains X.509 key | ||
--ssl-mode | Security state of connection to server | ||
--tls-ciphersuites | TLSv1.3 ciphersuites permitted for encrypted connections | 8.0.16 | |
--tls-version | Protocols permitted for encrypted connections | ||
--user | MySQL user name to use when connecting to server | ||
--verbose | Verbose mode | ||
--version | Display version information and exit | ||
--vertical | Print query output rows vertically (one line per column value) | ||
--wait | If the connection cannot be established, wait and retry instead of aborting |
--help
,
-?
Display a help message and exit.
On a computer having multiple network interfaces, use this option to select which interface to use for connecting to the MySQL server.
The directory where character sets are installed. See Section 10.14, “Character Set Configuration”.
--compress
,
-C
Compress all information sent between the client and the server if both support compression.
--count=
,
N
-c
N
The number of iterations to make for repeated command
execution if the --sleep
option is given.
--debug[=
,
debug_options
]-#
[
debug_options
]
Write a debugging log. A typical
debug_options
string is
d:t:o,
.
The default is
file_name
d:t:o,/tmp/mysqladmin.trace
.
Print some debugging information when the program exits.
Print debugging information and memory and CPU usage statistics when the program exits.
A hint about the client-side authentication plugin to use. See Section 6.3.10, “Pluggable Authentication”.
--default-character-set=
charset_name
Use charset_name
as the default
character set. See Section 10.14, “Character Set Configuration”.
--defaults-extra-file=
file_name
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.
For additional information about this and other option-file options, see Section 4.2.8, “Command-Line Options that Affect Option-File Handling”.
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.
Exception: Even with
--defaults-file
, client
programs read .mylogin.cnf
.
For additional information about this and other option-file options, see Section 4.2.8, “Command-Line Options that Affect Option-File Handling”.
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.
For additional information about this and other option-file options, see Section 4.2.8, “Command-Line Options that Affect Option-File Handling”.
Enable the mysql_clear_password
cleartext
authentication plugin. (See
Section 6.5.1.4, “Client-Side Cleartext Pluggable Authentication”.)
--force
,
-f
Do not ask for confirmation for the drop
command. With
multiple commands, continue even if an error occurs.
db_name
Request from the server the public key required for RSA key
pair-based password exchange. This option applies to clients
that that authenticate with the
caching_sha2_password
authentication
plugin. For that plugin, the server does not send the public
key unless requested. This option is ignored for accounts
that do not authenticate with that plugin. It is also
ignored if RSA-based password exchange is not used, as is
the case when the client connects to the server using a
secure connection.
If
--server-public-key-path=
is given and specifies a valid public key file, it takes
precedence over
file_name
--get-server-public-key
.
For information about the
caching_sha2_password
plugin, see
Section 6.5.1.3, “Caching SHA-2 Pluggable Authentication”.
--host=
,
host_name
-h
host_name
Connect to the MySQL server on the given host.
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
Section 4.6.7, “mysql_config_editor — MySQL Configuration Utility”.
For additional information about this and other option-file options, see Section 4.2.8, “Command-Line Options that Affect Option-File Handling”.
--no-beep
,
-b
Suppress the warning beep that is emitted by default for errors such as a failure to connect to the server.
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
Section 4.6.7, “mysql_config_editor — MySQL Configuration Utility”.)
For additional information about this and other option-file options, see Section 4.2.8, “Command-Line Options that Affect Option-File Handling”.
--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,
mysqladmin prompts for one.
Specifying a password on the command line should be considered insecure. See Section 6.1.2.1, “End-User Guidelines for Password Security”. 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.
The directory in which to look for plugins. Specify this
option if the
--default-auth
option is
used to specify an authentication plugin but
mysqladmin does not find it. See
Section 6.3.10, “Pluggable Authentication”.
--port=
,
port_num
-P
port_num
The TCP/IP port number to use for the connection.
Print the program name and all options that it gets from option files.
For additional information about this and other option-file options, see Section 4.2.8, “Command-Line Options that Affect Option-File Handling”.
--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 Section 4.2.2, “Connecting to the MySQL Server”.
--relative
,
-r
Show the difference between the current and previous values
when used with the
--sleep
option. This
option works only with the
extended-status
command.
This option was removed in MySQL 8.0.3.
--server-public-key-path=
file_name
The path name to a file containing a client-side copy of the
public key required by the server for RSA key pair-based
password exchange. The file must be in PEM format. This
option applies to clients that authenticate with the
sha256_password
or
caching_sha2_password
authentication
plugin. This option is ignored for accounts that do not
authenticate with one of those plugins. It is also ignored
if RSA-based password exchange is not used, as is the case
when the client connects to the server using a secure
connection.
If
--server-public-key-path=
is given and specifies a valid public key file, it takes
precedence over
file_name
--get-server-public-key
.
For sha256_password
, this option applies
only if MySQL was built using OpenSSL.
For information about the sha256_password
and caching_sha2_password
plugins, see
Section 6.5.1.2, “SHA-256 Pluggable Authentication”, and
Section 6.5.1.3, “Caching SHA-2 Pluggable Authentication”.
--shared-memory-base-name=
name
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.
Show warnings resulting from execution of statements sent to the server.
--silent
,
-s
Exit silently if a connection to the server cannot be established.
--sleep=
,
delay
-i
delay
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.
--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.
Options that begin with
--ssl
specify whether to
connect to the server using SSL and indicate where to find
SSL keys and certificates. See
Section 6.4.2, “Command Options for Encrypted Connections”.
--ssl-fips-mode={OFF|ON|STRICT}
Controls whether to enable FIPS mode on the client side. The
--ssl-fips-mode
option
differs from other
--ssl-
options in that it is not used to establish encrypted
connections, but rather to affect which cryptographic
operations are permitted. See Section 6.6, “FIPS Support”.
xxx
These --ssl-fips-mode
values are permitted:
OFF
: Disable FIPS mode.
ON
: Enable FIPS mode.
STRICT
: Enable “strict”
FIPS mode.
If the OpenSSL FIPS Object Module is not available, the
only permitted value for
--ssl-fips-mode
is
OFF
. In this case, setting
--ssl-fips-mode
to
ON
or STRICT
causes
the client to produce a warning at startup and to operate
in non-FIPS mode.
--tls-ciphersuites=
ciphersuite_list
For client programs, specifies which TLSv1.3 ciphersuites the client permits for encrypted connections. The value is a list of one or more colon-separated ciphersuite names. The ciphersuites that can be named for this option depend on the SSL library used to compile MySQL. For details, see Section 6.4.6, “Encrypted Connection Protocols and Ciphers”.
This option was added in MySQL 8.0.16.
The protocols the client permits for encrypted connections. The value is a list of one or more comma-separated protocol names. The protocols that can be named for this option depend on the SSL library used to compile MySQL. For details, see Section 6.4.6, “Encrypted Connection Protocols and Ciphers”.
--user=
,
user_name
-u
user_name
The MySQL user name to use when connecting to the server.
--verbose
,
-v
Verbose mode. Print more information about what the program does.
--version
,
-V
Display version information and exit.
--vertical
,
-E
Print output vertically. This is similar to
--relative
, but prints
output vertically.
--wait[=
,
count
]-w[
count
]
If the connection cannot be established, wait and retry
instead of aborting. If a count
value is given, it indicates the number of times to retry.
The default is one time.
You can also set the following variables by using
--
.
var_name
=value
The mysqlcheck client performs table maintenance: It checks, repairs, optimizes, or analyzes tables.
Each table is locked and therefore unavailable to other sessions
while it is being processed, although for check operations, the
table is locked with a READ
lock only (see
Section 13.3.6, “LOCK TABLES and UNLOCK TABLES Syntax”, for more information about
READ
and WRITE
locks).
Table maintenance operations can be time-consuming, particularly
for large tables. If you use the
--databases
or
--all-databases
option to
process all tables in one or more databases, an invocation of
mysqlcheck might take a long time. (This is
also true for the MySQL upgrade procedure if it determines that
table checking is needed because it processes tables the same
way.)
mysqlcheck must be used when the mysqld server is running, which means that you do not have to stop the server to perform table maintenance.
mysqlcheck uses the SQL statements
CHECK TABLE
,
REPAIR TABLE
,
ANALYZE TABLE
, and
OPTIMIZE TABLE
in a convenient
way for the user. It determines which statements to use for the
operation you want to perform, and then sends the statements to
the server to be executed. For details about which storage
engines each statement works with, see the descriptions for
those statements in Section 13.7.3, “Table Maintenance Statements”.
All storage engines do not necessarily support all four
maintenance operations. In such cases, an error message is
displayed. For example, if test.t
is an
MEMORY
table, an attempt to check it produces
this result:
shell> mysqlcheck test t
test.t
note : The storage engine for the table doesn't support check
If mysqlcheck is unable to repair a table,
see Section 2.11.13, “Rebuilding or Repairing Tables or Indexes” for manual table repair
strategies. This will be the case, for example, for
InnoDB
tables, which can be checked with
CHECK TABLE
, but not repaired
with REPAIR TABLE
.
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.
There are three general ways to invoke mysqlcheck:
shell>mysqlcheck [
shell>options
]db_name
[tbl_name
...]mysqlcheck [
shell>options
] --databasesdb_name
...mysqlcheck [
options
] --all-databases
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.
Command | Meaning |
---|---|
mysqlrepair | The default option is --repair |
mysqlanalyze | The default option is --analyze |
mysqloptimize | The default option is --optimize |
mysqlcheck supports the following options,
which can be specified on the command line or in the
[mysqlcheck]
and [client]
groups of an option file. For information about option files
used by MySQL programs, see Section 4.2.7, “Using Option Files”.
Table 4.10 mysqlcheck Options
Format | Description | Introduced | Removed |
---|---|---|---|
--all-databases | Check all tables in all databases | ||
--all-in-1 | Execute a single statement for each database that names all the tables from that database | ||
--analyze | Analyze the tables | ||
--auto-repair | If a checked table is corrupted, automatically fix it | ||
--bind-address | Use specified network interface to connect to MySQL Server | ||
--character-sets-dir | Directory where character sets are installed | ||
--check | Check the tables for errors | ||
--check-only-changed | Check only tables that have changed since the last check | ||
--check-upgrade | Invoke CHECK TABLE with the FOR UPGRADE option | ||
--compress | Compress all information sent between client and server | ||
--databases | Interpret all arguments as database names | ||
--debug | Write debugging log | ||
--debug-check | Print debugging information when program exits | ||
--debug-info | Print debugging information, memory, and CPU statistics when program exits | ||
--default-auth | Authentication plugin to use | ||
--default-character-set | Specify default character set | ||
--defaults-extra-file | Read named option file in addition to usual option files | ||
--defaults-file | Read only named option file | ||
--defaults-group-suffix | Option group suffix value | ||
--enable-cleartext-plugin | Enable cleartext authentication plugin | ||
--extended | Check and repair tables | ||
--fast | Check only tables that have not been closed properly | ||
--force | Continue even if an SQL error occurs | ||
--get-server-public-key | Request RSA public key from server | 8.0.3 | |
--help | Display help message and exit | ||
--host | Connect to MySQL server on given host | ||
--login-path | Read login path options from .mylogin.cnf | ||
--medium-check | Do a check that is faster than an --extended operation | ||
--no-defaults | Read no option files | ||
--optimize | Optimize the tables | ||
--password | Password to use when connecting to server | ||
--pipe | On Windows, connect to server using named pipe | ||
--plugin-dir | Directory where plugins are installed | ||
--port | TCP/IP port number for connection | ||
--print-defaults | Print default options | ||
--protocol | Connection protocol to use | ||
--quick | The fastest method of checking | ||
--repair | Perform a repair that can fix almost anything except unique keys that are not unique | ||
--secure-auth | Do not send passwords to server in old (pre-4.1) format | 8.0.3 | |
--server-public-key-path | Path name to file containing RSA public key | 8.0.4 | |
--shared-memory-base-name | Name of shared memory to use for shared-memory connections | ||
--silent | Silent mode | ||
--skip-database | Omit this database from performed operations | ||
--socket | For connections to localhost, the Unix socket file to use | ||
--ssl-ca | File that contains list of trusted SSL Certificate Authorities | ||
--ssl-capath | Directory that contains trusted SSL Certificate Authority certificate files | ||
--ssl-cert | File that contains X.509 certificate | ||
--ssl-cipher | List of permitted ciphers for connection encryption | ||
--ssl-crl | File that contains certificate revocation lists | ||
--ssl-crlpath | Directory that contains certificate revocation-list files | ||
--ssl-fips-mode | Whether to enable FIPS mode on client side | 8.0.11 | |
--ssl-key | File that contains X.509 key | ||
--ssl-mode | Security state of connection to server | ||
--tables | Overrides the --databases or -B option | ||
--tls-ciphersuites | TLSv1.3 ciphersuites permitted for encrypted connections | 8.0.16 | |
--tls-version | Protocols permitted for encrypted connections | ||
--use-frm | For repair operations on MyISAM tables | ||
--user | MySQL user name to use when connecting to server | ||
--verbose | Verbose mode | ||
--version | Display version information and exit | ||
--write-binlog | Log ANALYZE, OPTIMIZE, REPAIR statements to binary log. --skip-write-binlog adds NO_WRITE_TO_BINLOG to these statements. |
--help
,
-?
Display a help message and exit.
--all-databases
,
-A
Check all tables in all databases. This is the same as using
the --databases
option
and naming all the databases on the command line, except
that the INFORMATION_SCHEMA
and
performance_schema
databases are not
checked. They can be checked by explicitly naming them with
the --databases
option.
--all-in-1
,
-1
Instead of issuing a statement for each table, execute a single statement for each database that names all the tables from that database to be processed.
--analyze
,
-a
Analyze the tables.
If a checked table is corrupted, automatically fix it. Any necessary repairs are done after all tables have been checked.
On a computer having multiple network interfaces, use this option to select which interface to use for connecting to the MySQL server.
The directory where character sets are installed. See Section 10.14, “Character Set Configuration”.
--check
,
-c
Check the tables for errors. This is the default operation.
Check only tables that have changed since the last check or that have not been closed properly.
--check-upgrade
,
-g
Invoke CHECK TABLE
with the
FOR UPGRADE
option to check tables for
incompatibilities with the current version of the server.
Compress all information sent between the client and the server if both support compression.
--databases
,
-B
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.
--debug[=
,
debug_options
]-#
[
debug_options
]
Write a debugging log. A typical
debug_options
string is
d:t:o,
.
The default is file_name
d:t:o
.
Print some debugging information when the program exits.
Print debugging information and memory and CPU usage statistics when the program exits.
--default-character-set=
charset_name
Use charset_name
as the default
character set. See Section 10.14, “Character Set Configuration”.
--defaults-extra-file=
file_name
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.
For additional information about this and other option-file options, see Section 4.2.8, “Command-Line Options that Affect Option-File Handling”.
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.
Exception: Even with
--defaults-file
, client
programs read .mylogin.cnf
.
For additional information about this and other option-file options, see Section 4.2.8, “Command-Line Options that Affect Option-File Handling”.
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.
For additional information about this and other option-file options, see Section 4.2.8, “Command-Line Options that Affect Option-File Handling”.
--extended
,
-e
If you are using this option to check tables, it ensures that they are 100% consistent but takes a long time.
If you are using this option to repair tables, it runs an extended repair that may not only take a long time to execute, but may produce a lot of garbage rows also!
A hint about the client-side authentication plugin to use. See Section 6.3.10, “Pluggable Authentication”.
Enable the mysql_clear_password
cleartext
authentication plugin. (See
Section 6.5.1.4, “Client-Side Cleartext Pluggable Authentication”.)
--fast
,
-F
Check only tables that have not been closed properly.
--force
,
-f
Continue even if an SQL error occurs.
Request from the server the public key required for RSA key
pair-based password exchange. This option applies to clients
that that authenticate with the
caching_sha2_password
authentication
plugin. For that plugin, the server does not send the public
key unless requested. This option is ignored for accounts
that do not authenticate with that plugin. It is also
ignored if RSA-based password exchange is not used, as is
the case when the client connects to the server using a
secure connection.
If
--server-public-key-path=
is given and specifies a valid public key file, it takes
precedence over
file_name
--get-server-public-key
.
For information about the
caching_sha2_password
plugin, see
Section 6.5.1.3, “Caching SHA-2 Pluggable Authentication”.
--host=
,
host_name
-h
host_name
Connect to the MySQL server on the given host.
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
Section 4.6.7, “mysql_config_editor — MySQL Configuration Utility”.
For additional information about this and other option-file options, see Section 4.2.8, “Command-Line Options that Affect Option-File Handling”.
--medium-check
,
-m
Do a check that is faster than an
--extended
operation.
This finds only 99.99% of all errors, which should be good
enough in most cases.
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
Section 4.6.7, “mysql_config_editor — MySQL Configuration Utility”.)
For additional information about this and other option-file options, see Section 4.2.8, “Command-Line Options that Affect Option-File Handling”.
--optimize
,
-o
Optimize the tables.
--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,
mysqlcheck prompts for one.
Specifying a password on the command line should be considered insecure. See Section 6.1.2.1, “End-User Guidelines for Password Security”. 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.
The directory in which to look for plugins. Specify this
option if the
--default-auth
option is
used to specify an authentication plugin but
mysqlcheck does not find it. See
Section 6.3.10, “Pluggable Authentication”.
--port=
,
port_num
-P
port_num
The TCP/IP port number to use for the connection.
Print the program name and all options that it gets from option files.
For additional information about this and other option-file options, see Section 4.2.8, “Command-Line Options that Affect Option-File Handling”.
--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 Section 4.2.2, “Connecting to the MySQL Server”.
--quick
,
-q
If you are using this option to check tables, it prevents the check from scanning the rows to check for incorrect links. This is the fastest check method.
If you are using this option to repair tables, it tries to repair only the index tree. This is the fastest repair method.
--repair
,
-r
Perform a repair that can fix almost anything except unique keys that are not unique.
This option was removed in MySQL 8.0.3.
--server-public-key-path=
file_name
The path name to a file containing a client-side copy of the
public key required by the server for RSA key pair-based
password exchange. The file must be in PEM format. This
option applies to clients that authenticate with the
sha256_password
or
caching_sha2_password
authentication
plugin. This option is ignored for accounts that do not
authenticate with one of those plugins. It is also ignored
if RSA-based password exchange is not used, as is the case
when the client connects to the server using a secure
connection.
If
--server-public-key-path=
is given and specifies a valid public key file, it takes
precedence over
file_name
--get-server-public-key
.
For sha256_password
, this option applies
only if MySQL was built using OpenSSL.
For information about the sha256_password
and caching_sha2_password
plugins, see
Section 6.5.1.2, “SHA-256 Pluggable Authentication”, and
Section 6.5.1.3, “Caching SHA-2 Pluggable Authentication”.
--shared-memory-base-name=
name
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.
--silent
,
-s
Silent mode. Print only error messages.
Do not include the named database (case-sensitive) in the operations performed by mysqlcheck.
--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.
Options that begin with --ssl
specify
whether to connect to the server using SSL and indicate
where to find SSL keys and certificates. See
Section 6.4.2, “Command Options for Encrypted Connections”.
--ssl-fips-mode={OFF|ON|STRICT}
Controls whether to enable FIPS mode on the client side. The
--ssl-fips-mode
option
differs from other
--ssl-
options in that it is not used to establish encrypted
connections, but rather to affect which cryptographic
operations are permitted. See Section 6.6, “FIPS Support”.
xxx
These --ssl-fips-mode
values are permitted:
OFF
: Disable FIPS mode.
ON
: Enable FIPS mode.
STRICT
: Enable “strict”
FIPS mode.
If the OpenSSL FIPS Object Module is not available, the
only permitted value for
--ssl-fips-mode
is
OFF
. In this case, setting
--ssl-fips-mode
to
ON
or STRICT
causes
the client to produce a warning at startup and to operate
in non-FIPS mode.
Override the --databases
or -B
option. All name arguments following
the option are regarded as table names.
--tls-ciphersuites=
ciphersuite_list
For client programs, specifies which TLSv1.3 ciphersuites the client permits for encrypted connections. The value is a list of one or more colon-separated ciphersuite names. The ciphersuites that can be named for this option depend on the SSL library used to compile MySQL. For details, see Section 6.4.6, “Encrypted Connection Protocols and Ciphers”.
This option was added in MySQL 8.0.16.
The protocols the client permits for encrypted connections. The value is a list of one or more comma-separated protocol names. The protocols that can be named for this option depend on the SSL library used to compile MySQL. For details, see Section 6.4.6, “Encrypted Connection Protocols and Ciphers”.
For repair operations on MyISAM
tables,
get the table structure from the data dictionary so that the
table can be repaired even if the .MYI
header is corrupted.
--user=
,
user_name
-u
user_name
The MySQL user name to use when connecting to the server.
--verbose
,
-v
Verbose mode. Print information about the various stages of program operation.
--version
,
-V
Display version information and exit.
This option is enabled by default, so that
ANALYZE TABLE
,
OPTIMIZE TABLE
, and
REPAIR TABLE
statements
generated by mysqlcheck are written to
the binary log. Use
--skip-write-binlog
to cause NO_WRITE_TO_BINLOG
to be added
to the statements so that they are not logged. Use the
--skip-write-binlog
when these statements should not be sent to replication
slaves or run when using the binary logs for recovery from
backup.
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.
mysqldump 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. 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.
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
Impermissible Client Character Sets),
so the dump file will not load correctly. To work around this
issue, use the --result-file
option, which
creates the output in ASCII format:
shell> mysqldump [options] --result-file=dump.sql
mysqldump
advantages include the convenience
and flexibility of viewing or even editing the output before
restoring. You can clone databases for development and DBA work,
or produce slight variations of an existing database for
testing. It is not intended as a fast or scalable solution for
backing up substantial amounts of data. With large data sizes,
even if the backup step takes a reasonable time, restoring the
data can be very slow because replaying the SQL statements
involves disk I/O for insertion, index creation, and so on.
For large-scale backup and restore, a physical backup is more appropriate, to copy the data files in their original format that can be restored quickly:
If your tables are primarily InnoDB
tables, or if you have a mix of InnoDB
and MyISAM
tables, consider using the
mysqlbackup command of the MySQL
Enterprise Backup product. (Available as part of the
Enterprise subscription.) It provides the best performance
for InnoDB
backups with minimal
disruption; it can also back up tables from
MyISAM
and other storage engines; and it
provides a number of convenient options to accommodate
different backup scenarios. See
Section 30.2, “MySQL Enterprise Backup Overview”.
mysqldump can retrieve and dump table
contents row by row, or it can retrieve the entire content from
a table and buffer it in memory before dumping it. Buffering in
memory can be a problem if you are dumping large tables. To dump
tables row by row, use the
--quick
option (or
--opt
, which enables
--quick
). The
--opt
option (and hence
--quick
) is enabled by
default, so to enable memory buffering, use
--skip-quick
.
If you are using a recent version of
mysqldump to generate a dump to be reloaded
into a very old MySQL server, use the
--skip-opt
option instead of
the --opt
or
--extended-insert
option.
For additional information about mysqldump, see Section 7.4, “Using mysqldump for Backups”.
There are in general three ways to use mysqldump—in order to dump a set of one or more tables, a set of one or more complete databases, or an entire MySQL server—as shown here:
shell>mysqldump [
shell>options
]db_name
[tbl_name
...]mysqldump [
shell>options
] --databasesdb_name
...mysqldump [
options
] --all-databases
To dump entire databases, do not name any tables following
db_name
, or use the
--databases
or
--all-databases
option.
To see a list of the options your version of mysqldump supports, issue the command mysqldump --help.
mysqldump supports the following options,
which can be specified on the command line or in the
[mysqldump]
and [client]
groups of an option file. For information about option files
used by MySQL programs, see Section 4.2.7, “Using Option Files”.
Table 4.11 mysqldump Options
Format | Description | Introduced | Removed |
---|---|---|---|
--add-drop-database | Add DROP DATABASE statement before each CREATE DATABASE statement | ||
--add-drop-table | Add DROP TABLE statement before each CREATE TABLE statement | ||
--add-drop-trigger | Add DROP TRIGGER statement before each CREATE TRIGGER statement | ||
--add-locks | Surround each table dump with LOCK TABLES and UNLOCK TABLES statements | ||
--all-databases | Dump all tables in all databases | ||
--allow-keywords | Allow creation of column names that are keywords | ||
--apply-slave-statements | Include STOP SLAVE prior to CHANGE MASTER statement and START SLAVE at end of output | ||
--bind-address | Use specified network interface to connect to MySQL Server | ||
--character-sets-dir | Directory where character sets are installed | ||
--column-statistics | Write ANALYZE TABLE statements to generate statistics histograms | 8.0.2 | |
--comments | Add comments to dump file | ||
--compact | Produce more compact output | ||
--compatible | Produce output that is more compatible with other database systems or with older MySQL servers | ||
--complete-insert | Use complete INSERT statements that include column names | ||
--compress | Compress all information sent between client and server | ||
--create-options | Include all MySQL-specific table options in CREATE TABLE statements | ||
--databases | Interpret all name arguments as database names | ||
--debug | Write debugging log | ||
--debug-check | Print debugging information when program exits | ||
--debug-info | Print debugging information, memory, and CPU statistics when program exits | ||
--default-auth | Authentication plugin to use | ||
--default-character-set | Specify default character set | ||
--defaults-extra-file | Read named option file in addition to usual option files | ||
--defaults-file | Read only named option file | ||
--defaults-group-suffix | Option group suffix value | ||
--delete-master-logs | On a master replication server, delete the binary logs after performing the dump operation | ||
--disable-keys | For each table, surround INSERT statements with statements to disable and enable keys | ||
--dump-date | Include dump date as "Dump completed on" comment if --comments is given | ||
--dump-slave | Include CHANGE MASTER statement that lists binary log coordinates of slave's master | ||
--enable-cleartext-plugin | Enable cleartext authentication plugin | ||
--events | Dump events from dumped databases | ||
--extended-insert | Use multiple-row INSERT syntax | ||
--fields-enclosed-by | This option is used with the --tab option and has the same meaning as the corresponding clause for LOAD DATA | ||
--fields-escaped-by | This option is used with the --tab option and has the same meaning as the corresponding clause for LOAD DATA | ||
--fields-optionally-enclosed-by | This option is used with the --tab option and has the same meaning as the corresponding clause for LOAD DATA | ||
--fields-terminated-by | This option is used with the --tab option and has the same meaning as the corresponding clause for LOAD DATA | ||
--flush-logs | Flush MySQL server log files before starting dump | ||
--flush-privileges | Emit a FLUSH PRIVILEGES statement after dumping mysql database | ||
--force | Continue even if an SQL error occurs during a table dump | ||
--get-server-public-key | Request RSA public key from server | 8.0.3 | |
--help | Display help message and exit | ||
--hex-blob | Dump binary columns using hexadecimal notation | ||
--host | Host to connect to (IP address or hostname) | ||
--ignore-error | Ignore specified errors | ||
--ignore-table | Do not dump given table | ||
--include-master-host-port | Include MASTER_HOST/MASTER_PORT options in CHANGE MASTER statement produced with --dump-slave | ||
--insert-ignore | Write INSERT IGNORE rather than INSERT statements | ||
--lines-terminated-by | This option is used with the --tab option and has the same meaning as the corresponding clause for LOAD DATA | ||
--lock-all-tables | Lock all tables across all databases | ||
--lock-tables | Lock all tables before dumping them | ||
--log-error | Append warnings and errors to named file | ||
--login-path | Read login path options from .mylogin.cnf | ||
--master-data | Write the binary log file name and position to the output | ||
--max_allowed_packet | Maximum packet length to send to or receive from server | ||
--net_buffer_length | Buffer size for TCP/IP and socket communication | ||
--network-timeout | Increase network timeouts to permit larger table dumps | 8.0.1 | |
--no-autocommit | Enclose the INSERT statements for each dumped table within SET autocommit = 0 and COMMIT statements | ||
--no-create-db | Do not write CREATE DATABASE statements | ||
--no-create-info | Do not write CREATE TABLE statements that re-create each dumped table | ||
--no-data | Do not dump table contents | ||
--no-defaults | Read no option files | ||
--no-set-names | Same as --skip-set-charset | ||
--no-tablespaces | Do not write any CREATE LOGFILE GROUP or CREATE TABLESPACE statements in output | ||
--opt | Shorthand for --add-drop-table --add-locks --create-options --disable-keys --extended-insert --lock-tables --quick --set-charset. | ||
--order-by-primary | Dump each table's rows sorted by its primary key, or by its first unique index | ||
--password | Password to use when connecting to server | ||
--pipe | On Windows, connect to server using named pipe | ||
--plugin-dir | Directory where plugins are installed | ||
--port | TCP/IP port number for connection | ||
--print-defaults | Print default options | ||
--protocol | Connection protocol to use | ||
--quick | Retrieve rows for a table from the server a row at a time | ||
--quote-names | Quote identifiers within backtick characters | ||
--replace | Write REPLACE statements rather than INSERT statements | ||
--result-file | Direct output to a given file | ||
--routines | Dump stored routines (procedures and functions) from dumped databases | ||
--secure-auth | Do not send passwords to server in old (pre-4.1) format | 8.0.3 | |
--server-public-key-path | Path name to file containing RSA public key | 8.0.4 | |
--set-charset | Add SET NAMES default_character_set to output | ||
--set-gtid-purged | Whether to add SET @@GLOBAL.GTID_PURGED to output | ||
--shared-memory-base-name | Name of shared memory to use for shared-memory connections | ||
--single-transaction | Issue a BEGIN SQL statement before dumping data from server | ||
--skip-add-drop-table | Do not add a DROP TABLE statement before each CREATE TABLE statement | ||
--skip-add-locks | Do not add locks | ||
--skip-comments | Do not add comments to dump file | ||
--skip-compact | Do not produce more compact output | ||
--skip-disable-keys | Do not disable keys | ||
--skip-extended-insert | Turn off extended-insert | ||
--skip-opt | Turn off options set by --opt | ||
--skip-quick | Do not retrieve rows for a table from the server a row at a time | ||
--skip-quote-names | Do not quote identifiers | ||
--skip-set-charset | Do not write SET NAMES statement | ||
--skip-triggers | Do not dump triggers | ||
--skip-tz-utc | Turn off tz-utc | ||
--socket | For connections to localhost, the Unix socket file to use | ||
--ssl-ca | File that contains list of trusted SSL Certificate Authorities | ||
--ssl-capath | Directory that contains trusted SSL Certificate Authority certificate files | ||
--ssl-cert | File that contains X.509 certificate | ||
--ssl-cipher | List of permitted ciphers for connection encryption | ||
--ssl-crl | File that contains certificate revocation lists | ||
--ssl-crlpath | Directory that contains certificate revocation-list files | ||
--ssl-fips-mode | Whether to enable FIPS mode on client side | 8.0.11 | |
--ssl-key | File that contains X.509 key | ||
--ssl-mode | Security state of connection to server | ||
--tab | Produce tab-separated data files | ||
--tables | Override --databases or -B option | ||
--tls-ciphersuites | TLSv1.3 ciphersuites permitted for encrypted connections | 8.0.16 | |
--tls-version | Protocols permitted for encrypted connections | ||
--triggers | Dump triggers for each dumped table | ||
--tz-utc | Add SET TIME_ZONE='+00:00' to dump file | ||
--user | MySQL user name to use when connecting to server | ||
--verbose | Verbose mode | ||
--version | Display version information and exit | ||
--where | Dump only rows selected by given WHERE condition | ||
--xml | Produce XML output |
The mysqldump command logs into a MySQL server to extract information. The following options specify how to connect to the MySQL server, either on the same machine or a remote system.
On a computer having multiple network interfaces, use this option to select which interface to use for connecting to the MySQL server.
Compress all information sent between the client and the server if both support compression.
A hint about the client-side authentication plugin to use. See Section 6.3.10, “Pluggable Authentication”.
Enable the mysql_clear_password
cleartext
authentication plugin. (See
Section 6.5.1.4, “Client-Side Cleartext Pluggable Authentication”.)
Request from the server the public key required for RSA key
pair-based password exchange. This option applies to clients
that that authenticate with the
caching_sha2_password
authentication
plugin. For that plugin, the server does not send the public
key unless requested. This option is ignored for accounts
that do not authenticate with that plugin. It is also
ignored if RSA-based password exchange is not used, as is
the case when the client connects to the server using a
secure connection.
If
--server-public-key-path=
is given and specifies a valid public key file, it takes
precedence over
file_name
--get-server-public-key
.
For information about the
caching_sha2_password
plugin, see
Section 6.5.1.3, “Caching SHA-2 Pluggable Authentication”.
--host=
,
host_name
-h
host_name
Dump data from the MySQL server on the given host. The
default host is localhost
.
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
Section 4.6.7, “mysql_config_editor — MySQL Configuration Utility”.
For additional information about this and other option-file options, see Section 4.2.8, “Command-Line Options that Affect Option-File Handling”.
Enable large tables to be dumped by setting
max_allowed_packet
to its
maximum value and network read and write timeouts to a large
value. This option is enabled by default. To disable it, use
--skip-network-timeout
.
--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 Section 6.1.2.1, “End-User Guidelines for Password Security”. You can use an option file to avoid giving the password on the command line.
On Windows, connect to the server using a named pipe. This option applies only if the server supports named-pipe connections.
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
Section 6.3.10, “Pluggable Authentication”.
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 Section 4.2.2, “Connecting to the MySQL Server”.
This option was removed in MySQL 8.0.3.
--server-public-key-path=
file_name
The path name to a file containing a client-side copy of the
public key required by the server for RSA key pair-based
password exchange. The file must be in PEM format. This
option applies to clients that authenticate with the
sha256_password
or
caching_sha2_password
authentication
plugin. This option is ignored for accounts that do not
authenticate with one of those plugins. It is also ignored
if RSA-based password exchange is not used, as is the case
when the client connects to the server using a secure
connection.
If
--server-public-key-path=
is given and specifies a valid public key file, it takes
precedence over
file_name
--get-server-public-key
.
For sha256_password
, this option applies
only if MySQL was built using OpenSSL.
For information about the sha256_password
and caching_sha2_password
plugins, see
Section 6.5.1.2, “SHA-256 Pluggable Authentication”, and
Section 6.5.1.3, “Caching SHA-2 Pluggable Authentication”.
For connections to localhost
, the Unix
socket file to use, or, on Windows, the name of the named
pipe to use.
Options that begin with
--ssl
specify whether to
connect to the server using SSL and indicate where to find
SSL keys and certificates. See
Section 6.4.2, “Command Options for Encrypted Connections”.
--ssl-fips-mode={OFF|ON|STRICT}
Controls whether to enable FIPS mode on the client side. The
--ssl-fips-mode
option
differs from other
--ssl-
options in that it is not used to establish encrypted
connections, but rather to affect which cryptographic
operations are permitted. See Section 6.6, “FIPS Support”.
xxx
These --ssl-fips-mode
values are permitted:
OFF
: Disable FIPS mode.
ON
: Enable FIPS mode.
STRICT
: Enable “strict”
FIPS mode.
If the OpenSSL FIPS Object Module is not available, the
only permitted value for
--ssl-fips-mode
is
OFF
. In this case, setting
--ssl-fips-mode
to
ON
or STRICT
causes
the client to produce a warning at startup and to operate
in non-FIPS mode.
--tls-ciphersuites=
ciphersuite_list
For client programs, specifies which TLSv1.3 ciphersuites the client permits for encrypted connections. The value is a list of one or more colon-separated ciphersuite names. The ciphersuites that can be named for this option depend on the SSL library used to compile MySQL. For details, see Section 6.4.6, “Encrypted Connection Protocols and Ciphers”.
This option was added in MySQL 8.0.16.
The protocols the client permits for encrypted connections. The value is a list of one or more comma-separated protocol names. The protocols that can be named for this option depend on the SSL library used to compile MySQL. For details, see Section 6.4.6, “Encrypted Connection Protocols and Ciphers”.
--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
--
syntax:
var_name
=value
The maximum size of the buffer for client/server communication. The default is 24MB, the maximum is 1GB.
The initial size of the buffer for client/server
communication. When creating multiple-row
INSERT
statements (as with
the --extended-insert
or
--opt
option),
mysqldump creates rows up to
net_buffer_length
bytes
long. If you increase this variable, ensure that the MySQL
server net_buffer_length
system variable has a value at least this large.
These options are used to control which option files to read.
--defaults-extra-file=
file_name
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.
For additional information about this and other option-file options, see Section 4.2.8, “Command-Line Options that Affect Option-File Handling”.
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.
Exception: Even with
--defaults-file
, client
programs read .mylogin.cnf
.
For additional information about this and other option-file options, see Section 4.2.8, “Command-Line Options that Affect Option-File Handling”.
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.
For additional information about this and other option-file options, see Section 4.2.8, “Command-Line Options that Affect Option-File Handling”.
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
Section 4.6.7, “mysql_config_editor — MySQL Configuration Utility”.)
For additional information about this and other option-file options, see Section 4.2.8, “Command-Line Options that Affect Option-File Handling”.
Print the program name and all options that it gets from option files.
For additional information about this and other option-file options, see Section 4.2.8, “Command-Line Options that Affect Option-File Handling”.
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.
Write a DROP DATABASE
statement before each CREATE
DATABASE
statement. This option is typically used
in conjunction with the
--all-databases
or
--databases
option because
no CREATE DATABASE
statements
are written unless one of those options is specified.
Write a DROP TABLE
statement
before each CREATE TABLE
statement.
Write a DROP TRIGGER
statement before each CREATE
TRIGGER
statement.
Adds to a table dump all SQL statements needed to create any
tablespaces used by an NDB
table. This information is not otherwise included in the
output from mysqldump. This option is
currently relevant only to NDB Cluster tables.
Suppress the CREATE DATABASE
statements that are otherwise included in the output if the
--databases
or
--all-databases
option is
given.
Do not write CREATE TABLE
statements that create each dumped table.
This option does not exclude
statements creating log file groups or tablespaces from
mysqldump output; however, you can use
the --no-tablespaces
option for this purpose.
This option suppresses all CREATE
LOGFILE GROUP
and CREATE
TABLESPACE
statements in the output of
mysqldump.
The following options print debugging information, encode debugging information in the dump file, or let the dump operation proceed regardless of potential problems.
Permit creation of column names that are keywords. This works by prefixing each column name with the table name.
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,
.
The default value is
file_name
d:t:o,/tmp/mysqldump.trace
.
Print some debugging information when the program exits.
Print debugging information and memory and CPU usage statistics when the program exits.
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.
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.
If the --ignore-error
option is also given to ignore specific errors,
--force
takes precedence.
Log warnings and errors by appending them to the named file. The default is to do no logging.
See the description for the
--comments
option.
Verbose mode. Print more information about what the program does.
The following options display information about the mysqldump command itself.
The following options change how the mysqldump command represents character data with national language settings.
The directory where character sets are installed. See Section 10.14, “Character Set Configuration”.
--default-character-set=
charset_name
Use charset_name
as the default
character set. See Section 10.14, “Character Set Configuration”.
If no character set is specified,
mysqldump uses utf8
.
Turns off the
--set-charset
setting, the
same as specifying --skip-set-charset
.
Write SET NAMES
to the output. This option is enabled by default. To
suppress the default_character_set
SET NAMES
statement, use
--skip-set-charset
.
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.
For a slave dump produced with the
--dump-slave
option, add a
STOP SLAVE
statement before
the CHANGE MASTER TO
statement and a START SLAVE
statement at the end of the output.
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
.
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 Section 17.4.1.33, “Replication and Transaction Inconsistencies” 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.
In conjunction with --dump-slave
, the
--apply-slave-statements
and
--include-master-host-port
options can also be used.
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.
Use this option to dump a master replication server to
produce a dump file that can be used to set up another
server as a slave of the master. It causes the dump output
to include a CHANGE MASTER TO
statement that indicates the binary log coordinates (file
name and position) of the dumped server. These are the
master server coordinates from which the slave should start
replicating after you load the dump file into the slave.
If the option value is 2, the CHANGE
MASTER TO
statement is written as an SQL comment,
and thus is informative only; it has no effect when the dump
file is reloaded. If the option value is 1, the statement is
not written as a comment and takes effect when the dump file
is reloaded. If no option value is specified, the default
value is 1.
This option requires the
RELOAD
privilege and the
binary log must be enabled.
The --master-data
option automatically
turns off --lock-tables
.
It also turns on
--lock-all-tables
, unless
--single-transaction
also
is specified, in which case, a global read lock is acquired
only for a short time at the beginning of the dump (see the
description for
--single-transaction
). In
all cases, any action on logs happens at the exact moment of
the dump.
It is also possible to set up a slave by dumping an existing
slave of the master, using the
--dump-slave
option, which
overrides --master-data
and causes it to be
ignored if both options are used.
This option enables control over global transaction ID
(GTID) information written to the dump file, by indicating
whether to add a
SET
@@GLOBAL.gtid_purged
statement to the output. This
option may also cause a statement to be written to the
output that disables binary logging while the dump file is
being reloaded.
The following table shows the permitted option values. The
default value is AUTO
.
Value | Meaning |
---|---|
OFF |
Add no SET statement to the output. |
ON |
Add a SET statement to the output. An error occurs if
GTIDs are not enabled on the server. |
AUTO |
Add a SET statement to the output if GTIDs are
enabled on the server. |
A partial dump from a server that is using GTID-based
replication requires the
--set-gtid-purged={ON|OFF}
option to be specified. Use ON
if the
intention is to deploy a new replication slave using only
some of the data from the dumped server. Use
OFF
if the intention is to repair a table
by copying it within a topology. Use OFF
if the intention is to copy a table between replication
topologies that are disjoint and will remain so.
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
).
It is not recommended to load a dump file when GTIDs are
enabled on the server
(gtid_mode=ON
),
if your dump file includes system tables.
mysqldump issues DML instructions for
the system tables which use the non-transactional MyISAM
storage engine, and this combination is not permitted when
GTIDs are enabled. Also be aware that loading a dump file
from a server with GTIDs enabled, into another server with
GTIDs enabled, causes different transaction identifiers to
be generated.
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 more compact output. This option enables the
--skip-add-drop-table
,
--skip-add-locks
,
--skip-comments
,
--skip-disable-keys
,
and
--skip-set-charset
options.
Produce output that is more compatible with other database
systems or with older MySQL servers. The only permitted
value for this option is ansi
, which has
the same meaning as the corresponding option for setting the
server SQL mode. See Section 5.1.11, “Server SQL Modes”.
Use complete INSERT
statements that include column names.
Include all MySQL-specific table options in the
CREATE TABLE
statements.
--fields-terminated-by=...
,
--fields-enclosed-by=...
,
--fields-optionally-enclosed-by=...
,
--fields-escaped-by=...
These options are used with the
--tab
option and have the
same meaning as the corresponding FIELDS
clauses for LOAD DATA
. See
Section 13.2.7, “LOAD DATA Syntax”.
Dump binary columns using hexadecimal notation (for example,
'abc'
becomes
0x616263
). The affected data types are
BINARY
,
VARBINARY
, the
BLOB
types, and
BIT
.
This option is used with the
--tab
option and has the
same meaning as the corresponding LINES
clause for LOAD DATA
. See
Section 13.2.7, “LOAD DATA Syntax”.
Quote identifiers (such as database, table, and column
names) within `
characters. If the
ANSI_QUOTES
SQL mode is
enabled, identifiers are quoted within "
characters. This option is enabled by default. It can be
disabled with --skip-quote-names
, but this
option should be given after any option such as
--compatible
that may
enable --quote-names
.
--result-file=
,
file_name
-r
file_name
Direct output to the named file. The result file is created and its previous contents overwritten, even if an error occurs while generating the dump.
This option should be used on Windows to prevent newline
\n
characters from being converted to
\r\n
carriage return/newline sequences.
Produce tab-separated text-format data files. For each
dumped table, mysqldump creates a
file that contains the tbl_name
.sqlCREATE
TABLE
statement that creates the table, and the
server writes a
file that contains its data. The option value is the
directory in which to write the files.
tbl_name
.txt
This option should be used only when
mysqldump is run on the same machine as
the mysqld server. Because the server
creates *.txt
files in the directory
that you specify, the directory must be writable by the
server and the MySQL account that you use must have the
FILE
privilege. Because
mysqldump creates
*.sql
in the same directory, it must
be writable by your system login account.
By default, the .txt
data files are
formatted using tab characters between column values and a
newline at the end of each line. The format can be specified
explicitly using the
--fields-
and
xxx
--lines-terminated-by
options.
Column values are converted to the character set specified
by the
--default-character-set
option.
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
.
Write dump output as well-formed XML.
NULL
,
'NULL'
, and Empty Values: For
a column named column_name
, the
NULL
value, an empty string, and the
string value 'NULL'
are distinguished
from one another in the output generated by this option as
follows.
Value: | XML Representation: |
---|---|
NULL (unknown value) |
|
'' (empty string) |
|
'NULL' (string value) |
|
The output from the mysql client when run
using the --xml
option also
follows the preceding rules. (See
Section 4.5.1.1, “mysql Client Options”.)
XML output from mysqldump includes the XML namespace, as shown here:
shell>mysqldump --xml -u root world City
<?xml version="1.0"?> <mysqldump xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"> <database name="world"> <table_structure name="City"> <field Field="ID" Type="int(11)" Null="NO" Key="PRI" Extra="auto_increment" /> <field Field="Name" Type="char(35)" Null="NO" Key="" Default="" Extra="" /> <field Field="CountryCode" Type="char(3)" Null="NO" Key="" Default="" Extra="" /> <field Field="District" Type="char(20)" Null="NO" Key="" Default="" Extra="" /> <field Field="Population" Type="int(11)" Null="NO" Key="" Default="0" Extra="" /> <key Table="City" Non_unique="0" Key_name="PRIMARY" Seq_in_index="1" Column_name="ID" Collation="A" Cardinality="4079" Null="" Index_type="BTREE" Comment="" /> <options Name="City" Engine="MyISAM" Version="10" Row_format="Fixed" Rows="4079" Avg_row_length="67" Data_length="273293" Max_data_length="18858823439613951" Index_length="43008" Data_free="0" Auto_increment="4080" Create_time="2007-03-31 01:47:01" Update_time="2007-03-31 01:47:02" Collation="latin1_swedish_ci" Create_options="" Comment="" /> </table_structure> <table_data name="City"> <row> <field name="ID">1</field> <field name="Name">Kabul</field> <field name="CountryCode">AFG</field> <field name="District">Kabol</field> <field name="Population">1780000</field> </row>...
<row> <field name="ID">4079</field> <field name="Name">Rafah</field> <field name="CountryCode">PSE</field> <field name="District">Rafah</field> <field name="Population">92020</field> </row> </table_data> </database> </mysqldump>
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.
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 were not required to include
stored routines and events when using the
--all-databases
option:
The dump included 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.
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
performance_schema
database, which
normally is not dumped even with the
--all-databases
option.
(Also use the
--skip-lock-tables
option.)
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 list of
comma-separated 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.
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).
Include stored routines (procedures and functions) for the
dumped databases in the output. This option requires the
global SELECT
privilege.
The output generated by using --routines
contains CREATE PROCEDURE
and
CREATE FUNCTION
statements to
create the routines.
Override the --databases
or -B
option. mysqldump
regards all name arguments following the option as table
names.
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 Downgrade Notes; 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"
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.
Performance is also affected by the transactional options, primarily for the dump operation.
Add ANALYZE TABLE
statements
to the output to generate histogram statistics for dumped
tables when the dump file is reloaded. This option is
disabled by default because histogram generation for large
tables can take a long time.
For each table, surround the
INSERT
statements with
/*!40000 ALTER TABLE
and tbl_name
DISABLE KEYS
*/;/*!40000 ALTER TABLE
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 tbl_name
ENABLE KEYS
*/;MyISAM
tables.
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.
Write INSERT
IGNORE
statements rather than
INSERT
statements.
This option, enabled by default, is shorthand for the
combination of
--add-drop-table
--add-locks
--create-options
--disable-keys
--extended-insert
--lock-tables
--quick
--set-charset
. It gives a
fast dump operation and produces a dump file that can be
reloaded into a MySQL server quickly.
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
.
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.
See the description for the
--opt
option.
The following options trade off the performance of the dump operation, against the reliability and consistency of the exported data.
Surround each table dump with LOCK
TABLES
and
UNLOCK
TABLES
statements. This results in faster inserts
when the dump file is reloaded. See
Section 8.2.5.1, “Optimizing INSERT Statements”.
Flush the MySQL server log files before starting the dump.
This option requires the
RELOAD
privilege. If you use
this option in combination with the
--all-databases
option,
the logs are flushed for each database
dumped. The exception is when using
--lock-all-tables
,
--master-data
, or
--single-transaction
: In
this case, the logs are flushed only once, corresponding to
the moment that all tables are locked by
FLUSH TABLES WITH READ LOCK
.
If you want your dump and the log flush to happen at exactly
the same moment, you should use
--flush-logs
together with
--lock-all-tables
,
--master-data
, or
--single-transaction
.
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
Section 2.11.4, “Changes in MySQL 8.0”.
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
.
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.
Enclose the INSERT
statements
for each dumped table within SET autocommit =
0
and COMMIT
statements.
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.
--shared-memory-base-name=
name
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.
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.
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.
To make a backup of an entire database:
shell> mysqldump db_name
> backup-file.sql
To load the dump file back into the server:
shell> mysql db_name
< backup-file.sql
Another way to reload the dump file:
shell> mysql -e "source /path-to-backup/backup-file.sql
" db_name
mysqldump is also very useful for populating databases by copying data from one MySQL server to another:
shell> mysqldump --opt db_name
| mysql --host=remote_host
-C db_name
You can dump several databases with one command:
shell> mysqldump --databases db_name1
[db_name2
...] > my_databases.sql
To dump all databases, use the
--all-databases
option:
shell> mysqldump --all-databases > all_databases.sql
For InnoDB
tables,
mysqldump provides a way of making an online
backup:
shell> mysqldump --all-databases --master-data --single-transaction > all_databases.sql
This backup acquires a global read lock on all tables (using
FLUSH TABLES WITH READ LOCK
) at
the beginning of the dump. As soon as this lock has been
acquired, the binary log coordinates are read and the lock is
released. If long updating statements are running when the
FLUSH
statement is issued, the
MySQL server may get stalled until those statements finish.
After that, the dump becomes lock free and does not disturb
reads and writes on the tables. If the update statements that
the MySQL server receives are short (in terms of execution
time), the initial lock period should not be noticeable, even
with many updates.
For point-in-time recovery (also known as “roll-forward,” when you need to restore an old backup and replay the changes that happened since that backup), it is often useful to rotate the binary log (see Section 5.4.4, “The Binary Log”) or at least know the binary log coordinates to which the dump corresponds:
shell> mysqldump --all-databases --master-data=2 > all_databases.sql
Or:
shell>mysqldump --all-databases --flush-logs --master-data=2
> all_databases.sql
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.
For more information on making backups, see Section 7.2, “Database Backup Methods”, and Section 7.3, “Example Backup and Recovery Strategy”.
To select the effect of
--opt
except for some
features, use the --skip
option for each
feature. To disable extended inserts and memory buffering,
use --opt
--skip-extended-insert
--skip-quick
.
(Actually,
--skip-extended-insert
--skip-quick
is sufficient because
--opt
is on by default.)
To reverse --opt
for all
features except index disabling and table locking, use
--skip-opt
--disable-keys
--lock-tables
.
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.
mysqldump does not dump the
INFORMATION_SCHEMA
schema.
mysqldump does not dump
InnoDB
CREATE
TABLESPACE
statements.
mysqldump does not dump the NDB Cluster
ndbinfo
information database.
mysqldump includes statements to recreate the
general_log
and
slow_query_log
tables for dumps of the
mysql
database. Log table contents are not
dumped.
If you encounter problems backing up views due to insufficient privileges, see Section C.5, “Restrictions on Views” for a workaround.
The mysqlimport client provides a
command-line interface to the LOAD
DATA
SQL statement. Most options to
mysqlimport correspond directly to clauses of
LOAD DATA
syntax. See
Section 13.2.7, “LOAD DATA Syntax”.
Invoke mysqlimport like this:
shell> mysqlimport [options
] db_name
textfile1
[textfile2
...]
For each text file named on the command line,
mysqlimport strips any extension from the
file name and uses the result to determine the name of the table
into which to import the file's contents. For example, files
named patient.txt
,
patient.text
, and
patient
all would be imported into a table
named patient
.
mysqlimport supports the following options,
which can be specified on the command line or in the
[mysqlimport]
and [client]
groups of an option file. For information about option files
used by MySQL programs, see Section 4.2.7, “Using Option Files”.
Table 4.12 mysqlimport Options
Format | Description | Introduced | Removed |
---|---|---|---|
--bind-address | Use specified network interface to connect to MySQL Server | ||
--columns | This option takes a comma-separated list of column names as its value | ||
--compress | Compress all information sent between client and server | ||
--debug | Write debugging log | ||
--debug-check | Print debugging information when program exits | ||
--debug-info | Print debugging information, memory, and CPU statistics when program exits | ||
--default-auth | Authentication plugin to use | ||
--default-character-set | Specify default character set | ||
--defaults-extra-file | Read named option file in addition to usual option files | ||
--defaults-file | Read only named option file | ||
--defaults-group-suffix | Option group suffix value | ||
--delete | Empty the table before importing the text file | ||
--enable-cleartext-plugin | Enable cleartext authentication plugin | ||
--fields-enclosed-by | This option has the same meaning as the corresponding clause for LOAD DATA | ||
--fields-escaped-by | This option has the same meaning as the corresponding clause for LOAD DATA | ||
--fields-optionally-enclosed-by | This option has the same meaning as the corresponding clause for LOAD DATA | ||
--fields-terminated-by | This option has the same meaning as the corresponding clause for LOAD DATA | ||
--force | Continue even if an SQL error occurs | ||
--get-server-public-key | Request RSA public key from server | 8.0.3 | |
--help | Display help message and exit | ||
--host | Connect to MySQL server on given host | ||
--ignore | See the description for the --replace option | ||
--ignore-lines | Ignore the first N lines of the data file | ||
--lines-terminated-by | This option has the same meaning as the corresponding clause for LOAD DATA | ||
--local | Read input files locally from the client host | ||
--lock-tables | Lock all tables for writing before processing any text files | ||
--login-path | Read login path options from .mylogin.cnf | ||
--low-priority | Use LOW_PRIORITY when loading the table. | ||
--no-defaults | Read no option files | ||
--password | Password to use when connecting to server | ||
--pipe | On Windows, connect to server using named pipe | ||
--plugin-dir | Directory where plugins are installed | ||
--port | TCP/IP port number for connection | ||
--print-defaults | Print default options | ||
--protocol | Connection protocol to use | ||
--replace | The --replace and --ignore options control handling of input rows that duplicate existing rows on unique key values | ||
--secure-auth | Do not send passwords to server in old (pre-4.1) format | 8.0.3 | |
--server-public-key-path | Path name to file containing RSA public key | 8.0.4 | |
--shared-memory-base-name | Name of shared memory to use for shared-memory connections | ||
--silent | Produce output only when errors occur | ||
--socket | For connections to localhost, the Unix socket file to use | ||
--ssl-ca | File that contains list of trusted SSL Certificate Authorities | ||
--ssl-capath | Directory that contains trusted SSL Certificate Authority certificate files | ||
--ssl-cert | File that contains X.509 certificate | ||
--ssl-cipher | List of permitted ciphers for connection encryption | ||
--ssl-crl | File that contains certificate revocation lists | ||
--ssl-crlpath | Directory that contains certificate revocation-list files | ||
--ssl-fips-mode | Whether to enable FIPS mode on client side | 8.0.11 | |
--ssl-key | File that contains X.509 key | ||
--ssl-mode | Security state of connection to server | ||
--tls-ciphersuites | TLSv1.3 ciphersuites permitted for encrypted connections | 8.0.16 | |
--tls-version | Protocols permitted for encrypted connections | ||
--use-threads | Number of threads for parallel file-loading | ||
--user | MySQL user name to use when connecting to server | ||
--verbose | Verbose mode | ||
--version | Display version information and exit |
--help
,
-?
Display a help message and exit.
On a computer having multiple network interfaces, use this option to select which interface to use for connecting to the MySQL server.
The directory where character sets are installed. See Section 10.14, “Character Set Configuration”.
--columns=
,
column_list
-c
column_list
This option takes a list of comma-separated column names as its value. The order of the column names indicates how to match data file columns with table columns.
--compress
,
-C
Compress all information sent between the client and the server if both support compression.
--debug[=
,
debug_options
]-#
[
debug_options
]
Write a debugging log. A typical
debug_options
string is
d:t:o,
.
The default is file_name
d:t:o
.
Print some debugging information when the program exits.
Print debugging information and memory and CPU usage statistics when the program exits.
--default-character-set=
charset_name
Use charset_name
as the default
character set. See Section 10.14, “Character Set Configuration”.
A hint about the client-side authentication plugin to use. See Section 6.3.10, “Pluggable Authentication”.
--defaults-extra-file=
file_name
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.
For additional information about this and other option-file options, see Section 4.2.8, “Command-Line Options that Affect Option-File Handling”.
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.
Exception: Even with
--defaults-file
, client
programs read .mylogin.cnf
.
For additional information about this and other option-file options, see Section 4.2.8, “Command-Line Options that Affect Option-File Handling”.
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.
For additional information about this and other option-file options, see Section 4.2.8, “Command-Line Options that Affect Option-File Handling”.
--delete
,
-D
Empty the table before importing the text file.
Enable the mysql_clear_password
cleartext
authentication plugin. (See
Section 6.5.1.4, “Client-Side Cleartext Pluggable Authentication”.)
--fields-terminated-by=...
,
--fields-enclosed-by=...
,
--fields-optionally-enclosed-by=...
,
--fields-escaped-by=...
These options have the same meaning as the corresponding
clauses for LOAD DATA
. See
Section 13.2.7, “LOAD DATA Syntax”.
--force
,
-f
Ignore errors. For example, if a table for a text file does
not exist, continue processing any remaining files. Without
--force
,
mysqlimport exits if a table does not
exist.
Request from the server the public key required for RSA key
pair-based password exchange. This option applies to clients
that that authenticate with the
caching_sha2_password
authentication
plugin. For that plugin, the server does not send the public
key unless requested. This option is ignored for accounts
that do not authenticate with that plugin. It is also
ignored if RSA-based password exchange is not used, as is
the case when the client connects to the server using a
secure connection.
If
--server-public-key-path=
is given and specifies a valid public key file, it takes
precedence over
file_name
--get-server-public-key
.
For information about the
caching_sha2_password
plugin, see
Section 6.5.1.3, “Caching SHA-2 Pluggable Authentication”.
--host=
,
host_name
-h
host_name
Import data to the MySQL server on the given host. The
default host is localhost
.
--ignore
,
-i
See the description for the
--replace
option.
Ignore the first N
lines of the
data file.
This option has the same meaning as the corresponding clause
for LOAD DATA
. For example,
to import Windows files that have lines terminated with
carriage return/linefeed pairs, use
--lines-terminated-by="\r\n"
.
(You might have to double the backslashes, depending on the
escaping conventions of your command interpreter.) See
Section 13.2.7, “LOAD DATA Syntax”.
--local
,
-L
By default, files are read by the server on the server host. With this option, mysqlimport reads input files locally on the client host. Enabling local data loading also requires that the server permits it; see Section 6.1.6, “Security Issues with LOAD DATA LOCAL”
--lock-tables
,
-l
Lock all tables for writing before processing any text files. This ensures that all tables are synchronized on the server.
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
Section 4.6.7, “mysql_config_editor — MySQL Configuration Utility”.
For additional information about this and other option-file options, see Section 4.2.8, “Command-Line Options that Affect Option-File Handling”.
Use LOW_PRIORITY
when loading the table.
This affects only storage engines that use only table-level
locking (such as MyISAM
,
MEMORY
, and MERGE
).
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
Section 4.6.7, “mysql_config_editor — MySQL Configuration Utility”.)
For additional information about this and other option-file options, see Section 4.2.8, “Command-Line Options that Affect Option-File Handling”.
--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,
mysqlimport prompts for one.
Specifying a password on the command line should be considered insecure. See Section 6.1.2.1, “End-User Guidelines for Password Security”. 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.
The directory in which to look for plugins. Specify this
option if the
--default-auth
option is
used to specify an authentication plugin but
mysqlimport does not find it. See
Section 6.3.10, “Pluggable Authentication”.
--port=
,
port_num
-P
port_num
The TCP/IP port number to use for the connection.
Print the program name and all options that it gets from option files.
For additional information about this and other option-file options, see Section 4.2.8, “Command-Line Options that Affect Option-File Handling”.
--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 Section 4.2.2, “Connecting to the MySQL Server”.
--replace
,
-r
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.
This option was removed in MySQL 8.0.3.
--server-public-key-path=
file_name
The path name to a file containing a client-side copy of the
public key required by the server for RSA key pair-based
password exchange. The file must be in PEM format. This
option applies to clients that authenticate with the
sha256_password
or
caching_sha2_password
authentication
plugin. This option is ignored for accounts that do not
authenticate with one of those plugins. It is also ignored
if RSA-based password exchange is not used, as is the case
when the client connects to the server using a secure
connection.
If
--server-public-key-path=
is given and specifies a valid public key file, it takes
precedence over
file_name
--get-server-public-key
.
For sha256_password
, this option applies
only if MySQL was built using OpenSSL.
For information about the sha256_password
and caching_sha2_password
plugins, see
Section 6.5.1.2, “SHA-256 Pluggable Authentication”, and
Section 6.5.1.3, “Caching SHA-2 Pluggable Authentication”.
--shared-memory-base-name=
name
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.
--silent
,
-s
Silent mode. Produce output only when errors occur.
--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.
Options that begin with
--ssl
specify whether to
connect to the server using SSL and indicate where to find
SSL keys and certificates. See
Section 6.4.2, “Command Options for Encrypted Connections”.
--ssl-fips-mode={OFF|ON|STRICT}
Controls whether to enable FIPS mode on the client side. The
--ssl-fips-mode
option
differs from other
--ssl-
options in that it is not used to establish encrypted
connections, but rather to affect which cryptographic
operations are permitted. See Section 6.6, “FIPS Support”.
xxx
These --ssl-fips-mode
values are permitted:
OFF
: Disable FIPS mode.
ON
: Enable FIPS mode.
STRICT
: Enable “strict”
FIPS mode.
If the OpenSSL FIPS Object Module is not available, the
only permitted value for
--ssl-fips-mode
is
OFF
. In this case, setting
--ssl-fips-mode
to
ON
or STRICT
causes
the client to produce a warning at startup and to operate
in non-FIPS mode.
--tls-ciphersuites=
ciphersuite_list
For client programs, specifies which TLSv1.3 ciphersuites the client permits for encrypted connections. The value is a list of one or more colon-separated ciphersuite names. The ciphersuites that can be named for this option depend on the SSL library used to compile MySQL. For details, see Section 6.4.6, “Encrypted Connection Protocols and Ciphers”.
This option was added in MySQL 8.0.16.
The protocols the client permits for encrypted connections. The value is a list of one or more comma-separated protocol names. The protocols that can be named for this option depend on the SSL library used to compile MySQL. For details, see Section 6.4.6, “Encrypted Connection Protocols and Ciphers”.
--user=
,
user_name
-u
user_name
The MySQL user name to use when connecting to the server.
Load files in parallel using N
threads.
--verbose
,
-v
Verbose mode. Print more information about what the program does.
--version
,
-V
Display version information and exit.
Here is a sample session that demonstrates use of mysqlimport:
shell>mysql -e 'CREATE TABLE imptest(id INT, n VARCHAR(30))' test
shell>ed
a 100 Max Sydow 101 Count Dracula . w imptest.txt 32 q shell>od -c imptest.txt
0000000 1 0 0 \t M a x S y d o w \n 1 0 0000020 1 \t C o u n t D r a c u l a \n 0000040 shell>mysqlimport --local test imptest.txt
test.imptest: Records: 2 Deleted: 0 Skipped: 0 Warnings: 0 shell>mysql -e 'SELECT * FROM imptest' test
+------+---------------+ | id | n | +------+---------------+ | 100 | Max Sydow | | 101 | Count Dracula | +------+---------------+
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 features include:
Parallel processing of databases, and of objects within databases, to speed up the dump process
Better control over which databases and database objects (tables, stored programs, user accounts) to dump
Dumping of user accounts as account-management statements
(CREATE USER
,
GRANT
) rather than as inserts
into the mysql
system database
Capability of creating compressed output
Progress indicator (the values are estimates)
For dump file reloading, faster secondary index creation for
InnoDB
tables by adding indexes after
rows are inserted
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 Section 10.4, “Connection Character Sets and Collations”), 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> mysqlpump [options] --result-file=dump.sql
By default, mysqlpump dumps all databases
(with certain exceptions noted in
mysqlpump Restrictions). To specify this
behavior explicitly, use the
--all-databases
option:
shell> mysqlpump --all-databases
To dump a single database, or certain tables within that database, name the database on the command line, optionally followed by table names:
shell>mysqlpump
shell>db_name
mysqlpump
db_name tbl_name1 tbl_name2 ...
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 supports several options for including or excluding databases, tables, stored programs, and user definitions. See mysqlpump Object Selection.
To reload a dump file, execute the statements that it contains. For example, use the mysql client:
shell>mysqlpump [options] > dump.sql
shell>mysql < dump.sql
The following discussion provides additional mysqlpump usage examples.
To see a list of the options mysqlpump supports, issue the command mysqlpump --help.
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 Section 4.2.7, “Using Option Files”.
Table 4.13 mysqlpump Options
Format | Description | Introduced | Removed |
---|---|---|---|
--add-drop-database | Add DROP DATABASE statement before each CREATE DATABASE statement | ||
--add-drop-table | Add DROP TABLE statement before each CREATE TABLE statement | ||
--add-drop-user | Add DROP USER statement before each CREATE USER statement | ||
--add-locks | Surround each table dump with LOCK TABLES and UNLOCK TABLES statements | ||
--all-databases | Dump all databases | ||
--bind-address | Use specified network interface to connect to MySQL Server | ||
--character-sets-dir | Directory where character sets are installed | ||
--column-statistics | Write ANALYZE TABLE statements to generate statistics histograms | 8.0.2 | |
--complete-insert | Use complete INSERT statements that include column names | ||
--compress | Compress all information sent between client and server | ||
--compress-output | Output compression algorithm | ||
--databases | Interpret all name arguments as database names | ||
--debug | Write debugging log | ||
--debug-check | Print debugging information when program exits | ||
--debug-info | Print debugging information, memory, and CPU statistics when program exits | ||
--default-auth | Authentication plugin to use | ||
--default-character-set | Specify default character set | ||
--default-parallelism | Default number of threads for parallel processing | ||
--defaults-extra-file | Read named option file in addition to usual option files | ||
--defaults-file | Read only named option file | ||
--defaults-group-suffix | Option group suffix value | ||
--defer-table-indexes | For reloading, defer index creation until after loading table rows | ||
--events | Dump events from dumped databases | ||
--exclude-databases | Databases to exclude from dump | ||
--exclude-events | Events to exclude from dump | ||
--exclude-routines | Routines to exclude from dump | ||
--exclude-tables | Tables to exclude from dump | ||
--exclude-triggers | Triggers to exclude from dump | ||
--exclude-users | Users to exclude from dump | ||
--extended-insert | Use multiple-row INSERT syntax | ||
--get-server-public-key | Request RSA public key from server | 8.0.3 | |
--help | Display help message and exit | ||
--hex-blob | Dump binary columns using hexadecimal notation | ||
--host | Host to connect to (IP address or hostname) | ||
--include-databases | Databases to include in dump | ||
--include-events | Events to include in dump | ||
--include-routines | Routines to include in dump | ||
--include-tables | Tables to include in dump | ||
--include-triggers | Triggers to include in dump | ||
--include-users | Users to include in dump | ||
--insert-ignore | Write INSERT IGNORE rather than INSERT statements | ||
--log-error-file | Append warnings and errors to named file | ||
--login-path | Read login path options from .mylogin.cnf | ||
--max-allowed-packet | Maximum packet length to send to or receive from server | ||
--net-buffer-length | Buffer size for TCP/IP and socket communication | ||
--no-create-db | Do not write CREATE DATABASE statements | ||
--no-create-info | Do not write CREATE TABLE statements that re-create each dumped table | ||
--no-defaults | Read no option files | ||
--parallel-schemas | Specify schema-processing parallelism | ||
--password | Password to use when connecting to server | ||
--plugin-dir | Directory where plugins are installed | ||
--port | TCP/IP port number for connection | ||
--print-defaults | Print default options | ||
--protocol | Connection protocol to use | ||
--replace | Write REPLACE statements rather than INSERT statements | ||
--result-file | Direct output to a given file | ||
--routines | Dump stored routines (procedures and functions) from dumped databases | ||
--secure-auth | Do not send passwords to server in old (pre-4.1) format | 8.0.3 | |
--server-public-key-path | Path name to file containing RSA public key | 8.0.4 | |
--set-charset | Add SET NAMES default_character_set to output | ||
--set-gtid-purged | Whether to add SET @@GLOBAL.GTID_PURGED to output | 8.0.1 | |
--single-transaction | Dump tables within single transaction | ||
--skip-definer | Omit DEFINER and SQL SECURITY clauses from view and stored program CREATE statements | ||
--skip-dump-rows | Do not dump table rows | ||
--socket | For connections to localhost, the Unix socket file to use | ||
--ssl-ca | File that contains list of trusted SSL Certificate Authorities | ||
--ssl-capath | Directory that contains trusted SSL Certificate Authority certificate files | ||
--ssl-cert | File that contains X.509 certificate | ||
--ssl-cipher | List of permitted ciphers for connection encryption | ||
--ssl-crl | File that contains certificate revocation lists | ||
--ssl-crlpath | Directory that contains certificate revocation-list files | ||
--ssl-fips-mode | Whether to enable FIPS mode on client side | 8.0.11 | |
--ssl-key | File that contains X.509 key | ||
--ssl-mode | Security state of connection to server | ||
--tls-ciphersuites | TLSv1.3 ciphersuites permitted for encrypted connections | 8.0.16 | |
--tls-version | Protocols permitted for encrypted connections | ||
--triggers | Dump triggers for each dumped table | ||
--tz-utc | Add SET TIME_ZONE='+00:00' to dump file | ||
--user | MySQL user name to use when connecting to server | ||
--users | Dump user accounts | ||
--version | Display version information and exit | ||
--watch-progress | Display progress indicator |
--help
,
-?
Display a help message and exit.
Write a DROP DATABASE
statement before each CREATE
DATABASE
statement.
Write a DROP TABLE
statement
before each CREATE TABLE
statement.
Write a DROP USER
statement
before each CREATE USER
statement.
Surround each table dump with LOCK
TABLES
and
UNLOCK
TABLES
statements. This results in faster inserts
when the dump file is reloaded. See
Section 8.2.5.1, “Optimizing INSERT Statements”.
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.
--add-locks
and
--single-transaction
are
mutually exclusive.
--all-databases
,
-A
Dump all databases (with certain exceptions noted in mysqlpump Restrictions). This is the default behavior if no other is specified explicitly.
--all-databases
and
--databases
are mutually
exclusive.
Prior to MySQL 8.0, the
--routines
and
--events
options for
mysqldump and
mysqlpump were not required to include
stored routines and events when using the
--all-databases
option:
The dump included 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.
On a computer having multiple network interfaces, use this option to select which interface to use for connecting to the MySQL server.
The directory where character sets are installed. See Section 10.14, “Character Set Configuration”.
Add ANALYZE TABLE
statements
to the output to generate histogram statistics for dumped
tables when the dump file is reloaded. This option is
disabled by default because histogram generation for large
tables can take a long time.
Write complete INSERT
statements that include column names.
--compress
,
-C
Compress all information sent between the client and the server if both support compression.
By default, mysqlpump does not compress
output. This option specifies output compression using the
specified algorithm. Permitted algorithms are
LZ4
and ZLIB
.
To uncompress compressed output, you must have an
appropriate utility. If the system commands
lz4 and openssl zlib
are not available, MySQL distributions include
lz4_decompress and
zlib_decompress utilities that can be
used to decompress mysqlpump output that
was compressed using the
--compress-output=LZ4
and
--compress-output=ZLIB
options. For more information, see
Section 4.8.1, “lz4_decompress — Decompress mysqlpump LZ4-Compressed Output”, and
Section 4.8.3, “zlib_decompress — Decompress mysqlpump ZLIB-Compressed Output”.
--databases
,
-B
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.
--all-databases
and
--databases
are mutually
exclusive.
--debug[=
,
debug_options
]-#
[
debug_options
]
Write a debugging log. A typical
debug_options
string is
d:t:o,
.
The default is
file_name
d:t:O,/tmp/mysqlpump.trace
.
Print some debugging information when the program exits.
--debug-info
,
-T
Print debugging information and memory and CPU usage statistics when the program exits.
A hint about the client-side authentication plugin to use. See Section 6.3.10, “Pluggable Authentication”.
--default-character-set=
charset_name
Use charset_name
as the default
character set. See Section 10.14, “Character Set Configuration”.
If no character set is specified,
mysqlpump uses utf8
.
The default number of threads for each parallel processing queue. The default is 2.
The --parallel-schemas
option also affects parallelism and can be used to override
the default number of threads. For more information, see
mysqlpump Parallel Processing.
With
--default-parallelism=0
and no --parallel-schemas
options, mysqlpump runs as a
single-threaded process and creates no queues.
With parallelism enabled, it is possible for output from different databases to be interleaved.
--defaults-extra-file=
file_name
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.
For additional information about this and other option-file options, see Section 4.2.8, “Command-Line Options that Affect Option-File Handling”.
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.
Exception: Even with
--defaults-file
, client
programs read .mylogin.cnf
.
For additional information about this and other option-file options, see Section 4.2.8, “Command-Line Options that Affect Option-File Handling”.
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.
For additional information about this and other option-file options, see Section 4.2.8, “Command-Line Options that Affect Option-File Handling”.
In the dump output, defer index creation for each table
until after its rows have been loaded. This works for all
storage engines, but for InnoDB
applies
only for secondary indexes.
This option is enabled by default; use
--skip-defer-table-indexes
to disable it.
Include Event Scheduler events for the dumped databases in
the output. Event dumping requires the
EVENT
privileges for those
databases.
The output generated by using
--events
contains
CREATE EVENT
statements to
create the events.
This option is enabled by default; use
--skip-events
to disable it.
Do not dump the databases in
db_list
, which is a list of one
or more comma-separated 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 list of
one or more comma-separated event names. Multiple instances
of this option are additive. For more information, see
mysqlpump Object Selection.
--exclude-routines=
routine_list
Do not dump the events in
routine_list
, which is a list of
one or more comma-separated routine (stored procedure or
function) names. Multiple instances of this option are
additive. For more information, see
mysqlpump Object Selection.
Do not dump the tables in
table_list
, which is a list of
one or more comma-separated table names. Multiple instances
of this option are additive. For more information, see
mysqlpump Object Selection.
--exclude-triggers=
trigger_list
Do not dump the triggers in
trigger_list
, which is a list of
one or more comma-separated 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 list of one
or more comma-separated account names. Multiple instances of
this option are additive. For more information, see
mysqlpump Object Selection.
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.
The option value indicates the number of rows to include in
each INSERT
statement. The
default is 250. A value of 1 produces one
INSERT
statement per table
row.
Request from the server the public key required for RSA key
pair-based password exchange. This option applies to clients
that that authenticate with the
caching_sha2_password
authentication
plugin. For that plugin, the server does not send the public
key unless requested. This option is ignored for accounts
that do not authenticate with that plugin. It is also
ignored if RSA-based password exchange is not used, as is
the case when the client connects to the server using a
secure connection.
If
--server-public-key-path=
is given and specifies a valid public key file, it takes
precedence over
file_name
--get-server-public-key
.
For information about the
caching_sha2_password
plugin, see
Section 6.5.1.3, “Caching SHA-2 Pluggable Authentication”.
Dump binary columns using hexadecimal notation (for example,
'abc'
becomes
0x616263
). The affected data types are
BINARY
,
VARBINARY
, the
BLOB
types, and
BIT
.
--host=
,
host_name
-h
host_name
Dump data from the MySQL server on the given host.
Dump the databases in db_list
,
which is a list of one or more comma-separated 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 events in event_list
,
which is a list of one or more comma-separated event names.
Multiple instances of this option are additive. For more
information, see mysqlpump Object Selection.
--include-routines=
routine_list
Dump the routines in
routine_list
, which is a list of
one or more comma-separated routine (stored procedure or
function) names. Multiple instances of this option are
additive. For more information, see
mysqlpump Object Selection.
Dump the tables in table_list
,
which is a list of one or more comma-separated table names.
Multiple instances of this option are additive. For more
information, see mysqlpump Object Selection.
--include-triggers=
trigger_list
Dump the triggers in
trigger_list
, which is a list of
one or more comma-separated trigger names. Multiple
instances of this option are additive. For more information,
see mysqlpump Object Selection.
Dump the user accounts in
user_list
, which is a list of one
or more comma-separated user names. Multiple instances of
this option are additive. For more information, see
mysqlpump Object Selection.
Write INSERT
IGNORE
statements rather than
INSERT
statements.
Log warnings and errors by appending them to the named file. If this option is not given, mysqlpump writes warnings and errors to the standard error output.
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
Section 4.6.7, “mysql_config_editor — MySQL Configuration Utility”.
For additional information about this and other option-file options, see Section 4.2.8, “Command-Line Options that Affect Option-File Handling”.
The maximum size of the buffer for client/server communication. The default is 24MB, the maximum is 1GB.
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 thi