Where is Mysql_config_editor file?

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

4.1. Overview of MySQL Programs

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

Most MySQL distributions include all of these programs, except for those programs that are platform-specific. (For example, the server startup scripts are not used on Windows.) The exception is that RPM distributions are more specialized. There is one RPM for the server, another for client programs, and so forth. If you appear to be missing one or more programs, see Chapter 2, 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

shell> mysql --host=localhost --user=myname --password=mypass mydb
shell> mysql -h localhost -u myname -pmypass mydb

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

The MySQL server, , 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:

Several programs perform setup operations during MySQL installation or upgrading:

MySQL client programs that connect to the MySQL server:

MySQL administrative and utility programs:

MySQL program-development utilities:

Miscellaneous utilities:

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 and MySQL for Excel.

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

Environment VariableMeaning

shell> mysql --host=localhost --user=myname --password=mypass mydb
shell> mysql -h localhost -u myname -pmypass mydb

shell> mysql --host=localhost --user=myname --password=mypass mydb
shell> mysql -h localhost -u myname -pmypass mydb

shell> mysql --host=localhost --user=myname --password=mypass mydb
shell> mysql -h localhost -u myname -pmypass mydb

shell> mysql --host=localhost --user=myname --password=mypass mydb
shell> mysql -h localhost -u myname -pmypass mydb

shell> mysql --host=localhost --user=myname --password=mypass mydb
shell> mysql -h localhost -u myname -pmypass mydb

shell> mysql --host=localhost --user=myname --password=mypass mydb
shell> mysql -h localhost -u myname -pmypass mydb

For a full list of environment variables used by MySQL programs, see .

Use of

shell> mysql --host=localhost --user=myname --password=mypass mydb
shell> mysql -h localhost -u myname -pmypass mydb

4.2. Using MySQL Programs

4.2.1. Invoking MySQL Programs

To invoke a MySQL program from the command line (that is, from your shell or command prompt), enter the program name followed by any options or other arguments needed to instruct the program what you want it to do. The following commands show some sample program invocations. “

shell> mysql --host=localhost --user=myname --password=mypass mydb
shell> mysql -h localhost -u myname -pmypass mydb

shell> mysql --host=localhost --user=myname --password=mypass mydb
shell> mysql -h localhost -u myname -pmypass mydb

shell> mysql --host=localhost --user=myname --password=mypass mydb
shell> mysql -h localhost -u myname -pmypass mydb

shell> mysql --host=localhost --user=myname --password=mypass mydb
shell> mysql -h localhost -u myname -pmypass mydb

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 (“

shell> mysql --host=localhost --user=myname --password=mypass mydb
shell> mysql -h localhost -u myname -pmypass mydb

shell> mysql --host=localhost --user=myname --password=mypass mydb
shell> mysql -h localhost -u myname -pmypass mydb

Nonoption arguments (arguments with no leading dash) provide additional information to the program. For example, the program interprets the first nonoption argument as a database name, so the command

shell> mysql --host=localhost --user=myname --password=mypass mydb
shell> mysql -h localhost -u myname -pmypass mydb

shell> mysql --host=localhost --user=myname --password=mypass mydb
shell> mysql -h localhost -u myname -pmypass mydb

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 (or

shell> mysql --host=localhost --user=myname --password=mypass mydb
shell> mysql -h localhost -u myname -pmypass mydb

shell> mysql --host=localhost --user=myname --password=mypass mydb
shell> mysql -h localhost -u myname -pmypass mydb

shell> mysql --host=localhost --user=myname --password=mypass mydb
shell> mysql -h localhost -u myname -pmypass mydb

shell> mysql --host=localhost --user=myname --password=mypass mydb
shell> mysql -h localhost -u myname -pmypass mydb

shell> mysql --host=localhost --user=myname --password=mypass mydb
shell> mysql -h localhost -u myname -pmypass mydb

You may find it necessary to invoke MySQL programs using the path name to the

shell> mysql --host=localhost --user=myname --password=mypass mydb
shell> mysql -h localhost -u myname -pmypass mydb

shell> mysql --host=localhost --user=myname --password=mypass mydb
shell> mysql -h localhost -u myname -pmypass mydb

shell> mysql --host=localhost --user=myname --password=mypass mydb
shell> mysql -h localhost -u myname -pmypass mydb

shell> mysql --host=localhost --user=myname --password=mypass mydb
shell> mysql -h localhost -u myname -pmypass mydb

shell> mysql --host=localhost --user=myname --password=mypass mydb
shell> mysql -h localhost -u myname -pmypass mydb

Consult the documentation for your command interpreter for instructions on setting your

shell> mysql --host=localhost --user=myname --password=mypass mydb
shell> mysql -h localhost -u myname -pmypass mydb

shell> mysql --host=localhost --user=myname --password=mypass mydb
shell> mysql -h localhost -u myname -pmypass mydb

4.2.2. Connecting to the MySQL Server

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

The examples here use the client program, but the principles apply to other clients such as , , or .

This command invokes without specifying any connection parameters explicitly:

shell> mysql

Because there are no parameter options, the default values apply:

• The default host name is

  shell> mysql --host=localhost --user=myname --password=mypass mydb
  shell> mysql -h localhost -u myname -pmypass mydb
  

  52. On Unix, this has a special meaning, as described later.

• The default user name is

  shell> mysql --host=localhost --user=myname --password=mypass mydb
  shell> mysql -h localhost -u myname -pmypass mydb
  

  84 on Windows or your Unix login name on Unix.

• No password is sent if neither

  shell> mysql --host=localhost --user=myname --password=mypass mydb
  shell> mysql -h localhost -u myname -pmypass mydb
  

  71 nor is given.

• For , the first nonoption argument is taken as the name of the default database. If there is no such option, 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=mypass mydb
shell> mysql -h localhost -u myname -pmypass mydb

For password options, the password value is optional:

• If you use a

  shell> mysql --host=localhost --user=myname --password=mypass mydb
  shell> mysql -h localhost -u myname -pmypass mydb
  

  71 or option and specify the password value, there must be no space between

  shell> mysql --host=localhost --user=myname --password=mypass mydb
  shell> mysql -h localhost -u myname -pmypass mydb
  

  71 or and the password following it.

• If you use a

  shell> mysql --host=localhost --user=myname --password=mypass mydb
  shell> mysql -h localhost -u myname -pmypass mydb
  

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

As just mentioned, including the password value on the command line can be a security risk. To avoid this problem, specify the

shell> mysql --host=localhost --user=myname --password=mypass mydb
shell> mysql -h localhost -u myname -pmypass mydb

shell> mysql --host=localhost --user=myname --password=mypass mydb
shell> mysql -h localhost -u myname -pmypass mydb

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,

shell> mysql --host=localhost --user=myname --password=mypass mydb
shell> mysql -h localhost -u myname -pmypass mydb

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

shell> mysql --host=localhost --user=myname --password=mypass mydb
shell> mysql -h localhost -u myname -pmypass mydb

shell> mysql --host=localhost --user=myname --password=mypass mydb
shell> mysql -h localhost -u myname -pmypass mydb

shell> mysql --host=localhost --user=myname --password=mypass mydb
shell> mysql -h localhost -u myname -pmypass mydb

shell> mysql --host=localhost --user=myname --password=mypass mydb
shell> mysql -h localhost -u myname -pmypass mydb

shell> mysql --host=localhost --user=myname --password mydb
shell> mysql -h localhost -u myname -p mydb

shell> mysql --host=localhost --user=myname --password=mypass mydb
shell> mysql -h localhost -u myname -pmypass mydb

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

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

If the server is configured to accept IPv6 connections, client can connect over IPv6 using . See .

On Windows, you can force a MySQL client to use a named-pipe connection by specifying the or option, or by specifying

shell> mysql --host=localhost --user=myname --password mydb
shell> mysql -h localhost -u myname -p mydb

Connections to remote servers always use TCP/IP. This command connects to the server running on

shell> mysql --host=localhost --user=myname --password mydb
shell> mysql -h localhost -u myname -p mydb

shell> mysql --host=remote.example.com

To specify a port number explicitly, use the or

shell> mysql --host=localhost --user=myname --password=mypass mydb
shell> mysql -h localhost -u myname -pmypass mydb

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

shell> mysql --host=localhost --user=myname --password=mypass mydb
shell> mysql -h localhost -u myname -pmypass mydb

For this command, the program uses a socket file on Unix and the 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:

• ,

  shell> mysql --host=localhost --user=myname --password mydb
  shell> mysql -h localhost -u myname -p mydb
  

  17

  The host where the server is running. The default value is

  shell> mysql --host=localhost --user=myname --password=mypass mydb
  shell> mysql -h localhost -u myname -pmypass mydb
  

  52.

• ,

  shell> mysql --host=localhost --user=myname --password mydb
  shell> mysql -h localhost -u myname -p mydb
  

  20]

  The password of the MySQL account. As described earlier, the password value is optional, but if given, there must be no space between

  shell> mysql --host=localhost --user=myname --password=mypass mydb
  shell> mysql -h localhost -u myname -pmypass mydb
  

  71 or and the password following it. The default is to send no password.

• ,

  shell> mysql --host=localhost --user=myname --password mydb
  shell> mysql -h localhost -u myname -p mydb
  

  24

  On Windows, connect to the server using a named pipe. The server must be started with the option to enable named-pipe connections.

• ,

  shell> mysql --host=localhost --user=myname --password mydb
  shell> mysql -h localhost -u myname -p mydb
  

  27

  The port number to use for the connection, for connections made using TCP/IP. The default port number is 3306.

• 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

  shell> mysql --host=localhost --user=myname --password=mypass mydb
  shell> mysql -h localhost -u myname -pmypass mydb
  

  52 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 option:

  shell> mysql
  

  0

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

  ValueConnection ProtocolPermissible Operating Systems

  shell> mysql --host=localhost --user=myname --password mydb
  shell> mysql -h localhost -u myname -p mydb
  

  33TCP/IP connection to local or remote serverAll

  shell> mysql --host=localhost --user=myname --password mydb
  shell> mysql -h localhost -u myname -p mydb
  

  34Unix socket file connection to local serverUnix only

  shell> mysql --host=localhost --user=myname --password mydb
  shell> mysql -h localhost -u myname -p mydb
  

  35Named-pipe connection to local or remote serverWindows only

  shell> mysql --host=localhost --user=myname --password mydb
  shell> mysql -h localhost -u myname -p mydb
  

  36Shared-memory connection to local serverWindows only

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

  shell> mysql --host=localhost --user=myname --password mydb
  shell> mysql -h localhost -u myname -p mydb
  

  38. The shared-memory name is case sensitive.

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

• ,

  shell> mysql --host=localhost --user=myname --password mydb
  shell> mysql -h localhost -u myname -p mydb
  

  41

  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

  shell> mysql --host=localhost --user=myname --password mydb
  shell> mysql -h localhost -u myname -p mydb
  

  42.

  On Windows, the name of the named pipe to use, for connections to a local server. The default Windows pipe name is

  shell> mysql --host=localhost --user=myname --password mydb
  shell> mysql -h localhost -u myname -p mydb
  

  43. The pipe name is not case sensitive.

  The server must be started with the option to enable named-pipe connections.

• shell> mysql --host=localhost --user=myname --password mydb
  shell> mysql -h localhost -u myname -p mydb
  

  45

  Options that begin with are used for establishing a secure connection to the server using SSL, if the server is configured with SSL support. For details, see .

• ,

  shell> mysql --host=localhost --user=myname --password mydb
  shell> mysql -h localhost -u myname -p mydb
  

  48

  The user name of the MySQL account you want to use. The default user name is

  shell> mysql --host=localhost --user=myname --password=mypass mydb
  shell> mysql -h localhost -u myname -pmypass mydb
  

  84 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

  shell> mysql --host=localhost --user=myname --password mydb
  shell> mysql -h localhost -u myname -p mydb
  

  50 section of an option file. The relevant section of the file might look like this:

  shell> mysql
  

  1

  , discusses option files further.

• You can specify some connection parameters using environment variables. The host can be specified for using

  shell> mysql --host=localhost --user=myname --password mydb
  shell> mysql -h localhost -u myname -p mydb
  

  51. The MySQL user name can be specified using

  shell> mysql --host=localhost --user=myname --password mydb
  shell> mysql -h localhost -u myname -p mydb
  

  52 (this is for Windows only). The password can be specified using

  shell> mysql --host=localhost --user=myname --password=mypass mydb
  shell> mysql -h localhost -u myname -pmypass mydb
  

  54, although this is insecure; see . For a list of variables, see .

4.2.3. Specifying Program Options

There are several ways to specify options for MySQL programs:

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

• List the options in an option file that the program reads when it starts. This is common for options that you want the program to use each time it runs.

• List the options in environment variables (see ). 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 , 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 to connect to the server running on

shell> mysql --host=localhost --user=myname --password=mypass mydb
shell> mysql -h localhost -u myname -pmypass mydb

shell> mysql

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

shell> mysql

MySQL programs determine which options are given first by examining environment variables, then by reading option files, and then by checking the command line. This means that environment variables have the lowest precedence and command-line options the highest.

You can take advantage of the way that MySQL programs process options by specifying default option values for a program in an option file. That enables you to avoid typing them each time you run the program while enabling you to override the defaults if necessary by using command-line options.

An option can be specified by writing it in full or as any unambiguous prefix. For example, the option can be given to as

shell> mysql --host=localhost --user=myname --password mydb
shell> mysql -h localhost -u myname -p mydb

shell> mysql --host=localhost --user=myname --password mydb
shell> mysql -h localhost -u myname -p mydb

shell> mysql

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

4.2.3.1. Using Options on the Command Line

Program options specified on the command line follow these rules:

• Options are given after the command name.

• An option argument begins with one dash or two dashes, depending on whether it is a short form or long form of the option name. Many options have both short and long forms. For example,

  shell> mysql --host=localhost --user=myname --password mydb
  shell> mysql -h localhost -u myname -p mydb
  

  58 and

  shell> mysql --host=localhost --user=myname --password=mypass mydb
  shell> mysql -h localhost -u myname -pmypass mydb
  

  50 are the short and long forms of the option that instructs a MySQL program to display its help message.

• Option names are case sensitive.

  shell> mysql --host=localhost --user=myname --password mydb
  shell> mysql -h localhost -u myname -p mydb
  

  60 and

  shell> mysql --host=localhost --user=myname --password mydb
  shell> mysql -h localhost -u myname -p mydb
  

  61 are both legal and have different meanings. (They are the corresponding short forms of the

  shell> mysql --host=localhost --user=myname --password mydb
  shell> mysql -h localhost -u myname -p mydb
  

  62 and

  shell> mysql --host=localhost --user=myname --password mydb
  shell> mysql -h localhost -u myname -p mydb
  

  63 options.)

• Some options take a value following the option name. For example,

  shell> mysql --host=localhost --user=myname --password mydb
  shell> mysql -h localhost -u myname -p mydb
  

  64 or 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 “

  shell> mysql --host=localhost --user=myname --password mydb
  shell> mysql -h localhost -u myname -p mydb
  

  66” 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:

  shell> mysql --host=localhost --user=myname --password mydb
  shell> mysql -h localhost -u myname -p mydb
  

  67 and

  shell> mysql --host=localhost --user=myname --password mydb
  shell> mysql -h localhost -u myname -p mydb
  

  64 are equivalent. An exception to this rule is the option for specifying your MySQL password. This option can be given in long form as or as . 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

  shell> mysql --host=localhost --user=myname --password mydb
  shell> mysql -h localhost -u myname -p mydb
  

  71 or as

  shell> mysql --host=localhost --user=myname --password=mypass mydb
  shell> mysql -h localhost -u myname -pmypass mydb
  

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

  5

  The first command instructs to use a password value of

  shell> mysql --host=localhost --user=myname --password=mypass mydb
  shell> mysql -h localhost -u myname -pmypass mydb
  

  65, but specifies no default database. The second instructs to prompt for the password value and to use

  shell> mysql --host=localhost --user=myname --password=mypass mydb
  shell> mysql -h localhost -u myname -pmypass mydb
  

  65 as the default database.

• Within option names, dash (“

  shell> mysql --host=localhost --user=myname --password=mypass mydb
  shell> mysql -h localhost -u myname -pmypass mydb
  

  62”) and underscore (“

  shell> mysql --host=localhost --user=myname --password mydb
  shell> mysql -h localhost -u myname -p mydb
  

  76”) may be used interchangeably. For example, and 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

  shell> mysql --host=localhost --user=myname --password mydb
  shell> mysql -h localhost -u myname -p mydb
  

  79,

  shell> mysql --host=localhost --user=myname --password mydb
  shell> mysql -h localhost -u myname -p mydb
  

  80, or

  shell> mysql --host=localhost --user=myname --password mydb
  shell> mysql -h localhost -u myname -p mydb
  

  81 (either uppercase or lowercase) to indicate a multiplier of 1024, 10242 or 10243. For example, the following command tells to ping the server 1024 times, sleeping 10 seconds between each ping:

  shell> mysql
  

  6

Option values that contain spaces must be quoted when given on the command line. For example, the (or

shell> mysql --host=localhost --user=myname --password mydb
shell> mysql -h localhost -u myname -p mydb

shell> mysql

Note that the long form () is followed by an equals sign (

shell> mysql --host=localhost --user=myname --password mydb
shell> mysql -h localhost -u myname -p mydb

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

4.2.3.2. Program Option Modifiers

Some options are “boolean” and control behavior that can be turned on or off. For example, the client supports a 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 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:

shell> mysql

The

shell> mysql --host=localhost --user=myname --password mydb
shell> mysql -h localhost -u myname -p mydb

shell> mysql --host=localhost --user=myname --password mydb
shell> mysql -h localhost -u myname -p mydb

shell> mysql --host=localhost --user=myname --password mydb
shell> mysql -h localhost -u myname -p mydb

The “enabled” form of the option may be specified in any of these ways:

shell> mysql --host=localhost --user=myname --password=mypass mydb
shell> mysql -h localhost -u myname -pmypass mydb

As of MySQL 5.6.2, the values

shell> mysql --host=localhost --user=myname --password mydb
shell> mysql -h localhost -u myname -p mydb

shell> mysql --host=localhost --user=myname --password mydb
shell> mysql -h localhost -u myname -p mydb

shell> mysql --host=localhost --user=myname --password mydb
shell> mysql -h localhost -u myname -p mydb

shell> mysql --host=localhost --user=myname --password mydb
shell> mysql -h localhost -u myname -p mydb

If an option is prefixed by

shell> mysql --host=localhost --user=myname --password mydb
shell> mysql -h localhost -u myname -p mydb

shell> mysql --host=localhost --user=myname --password=mypass mydb
shell> mysql -h localhost -u myname -pmypass mydb

The

shell> mysql --host=localhost --user=myname --password mydb
shell> mysql -h localhost -u myname -p mydb

shell> mysql --host=localhost --user=myname --password mydb
shell> mysql -h localhost -u myname -p mydb

shell> mysql --host=localhost --user=myname --password mydb
shell> mysql -h localhost -u myname -p mydb

enables a limit to be placed on how large client programs can set dynamic system variables. To do this, use a

shell> mysql --host=localhost --user=myname --password mydb
shell> mysql -h localhost -u myname -p mydb

shell> mysql --host=localhost --user=myname --password mydb
shell> mysql -h localhost -u myname -p mydb

4.2.3.3. Using Option Files

Most MySQL programs can read startup options from option files (also sometimes called configuration files). Option files provide a convenient way to specify commonly used options so that they need not be entered on the command line each time you run a program. For the MySQL server, MySQL provides a number of .

To determine whether a program reads option files, invoke it with the

shell> mysql --host=localhost --user=myname --password=mypass mydb
shell> mysql -h localhost -u myname -pmypass mydb

The

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

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

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

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

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

On Windows, MySQL programs read startup options from the following files, in the specified order (top items are used first).

File NamePurpose

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

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

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

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

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

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

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

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

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

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

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

shell> mysql --host=localhost --user=myname --password=mypass mydb
shell> mysql -h localhost -u myname -pmypass mydb

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

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

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

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

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

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

shell> mysql --host=localhost --user=myname --password=mypass mydb
shell> mysql -h localhost -u myname -pmypass mydb

On Unix, Linux and Mac OS X, MySQL programs read startup options from the following files, in the specified order (top items are used first).

File NamePurpose

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

• Let

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

  44 and

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

  45 represent the path names of the MySQL base directory and data directory, respectively.

• If there is a

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

  41 file in

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

  45 but not in

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

  44, sets

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

  40 to

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

  45.

• Otherwise, if

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

  40 is not set and there is no

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

  41 file in

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

  45, sets

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

  40 to

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

  44.

In MySQL 5.6, use of

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

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

Typically,

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

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

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

MySQL looks for option files in the order just described and reads any that exist. If an option file that you want to use does not exist, create it with a plain text editor.

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

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

Any long option that may be given on the command line when running a MySQL program can be given in an option file as well. To get the list of available options for a program, run it with the

shell> mysql --host=localhost --user=myname --password=mypass mydb
shell> mysql -h localhost -u myname -pmypass mydb

The syntax for specifying options in an option file is similar to command-line syntax (see ). 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,

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

shell> mysql --host=localhost --user=myname --password mydb
shell> mysql -h localhost -u myname -p mydb

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

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

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

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

Empty lines in option files are ignored. Nonempty lines can take any of the following forms:

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

  71,

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

  72

  Comment lines start with “

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

  73” or “

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

  74”. A “

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

  73” comment can start in the middle of a line as well.

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

  76]

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

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

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

  78

  This is equivalent to

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

  79 on the command line.

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

  78=

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

  81

  This is equivalent to

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

  79=

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

  81 on the command line. In an option file, you can have spaces around the “

  shell> mysql --host=localhost --user=myname --password mydb
  shell> mysql -h localhost -u myname -p mydb
  

  66” character, something that is not true on the command line. You can optionally enclose the value within single quotation marks or double quotation marks, which is useful if the value contains a “

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

  73” comment character.

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

You can use the escape sequences “

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

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

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

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

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

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

• If a backslash is followed by a valid escape sequence character, the sequence is converted to the character represented by the sequence. For example, “

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

  91” is converted to a space.

• If a backslash is not followed by a valid escape sequence character, it remains unchanged. For example, “

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

  93” is retained as is.

The preceding rules mean that a literal backslash can be given as “

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

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

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 “

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

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

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

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

The escaping rules for option file values are especially pertinent for Windows path names, which use “

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

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

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

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

shell> mysql --host=remote.example.com

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

shell> mysql --host=remote.example.com

shell> mysql --host=localhost --user=myname --password=mypass mydb
shell> mysql -h localhost -u myname -pmypass mydb

If an option group name is the same as a program name, options in the group apply specifically to that program. For example, the

shell> mysql --host=remote.example.com

shell> mysql --host=remote.example.com

The

shell> mysql --host=localhost --user=myname --password mydb
shell> mysql -h localhost -u myname -p mydb

shell> mysql --host=localhost --user=myname --password mydb
shell> mysql -h localhost -u myname -p mydb

shell> mysql --host=localhost --user=myname --password mydb
shell> mysql -h localhost -u myname -p mydb

Here is a typical global option file:

shell> mysql --host=localhost --user=myname --password=mypass mydb
shell> mysql -h localhost -u myname -pmypass mydb

The preceding option file uses

shell> mysql --host=remote.example.com

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

Here is a typical user option file:

shell> mysql --host=localhost --user=myname --password=mypass mydb
shell> mysql -h localhost -u myname -pmypass mydb

If you want to create option groups that should be read by servers from a specific MySQL release series only, you can do this by using groups with names of

shell> mysql --host=remote.example.com

shell> mysql --host=remote.example.com

shell> mysql --host=remote.example.com

shell> mysql --host=localhost --user=myname --password=mypass mydb
shell> mysql -h localhost -u myname -pmypass mydb

It is possible to use

shell> mysql --host=remote.example.com

shell> mysql --host=remote.example.com

shell> mysql --host=remote.example.com

shell> mysql --host=localhost --user=myname --password=mypass mydb
shell> mysql -h localhost -u myname -pmypass mydb

To search the

shell> mysql --host=remote.example.com

shell> mysql --host=localhost --user=myname --password=mypass mydb
shell> mysql -h localhost -u myname -pmypass mydb

There is no guarantee about the order in which the option files in the directory will be read.

Currently, any files to be found and included using the

shell> mysql --host=remote.example.com

shell> mysql --host=remote.example.com

shell> mysql --host=remote.example.com

shell> mysql --host=remote.example.com

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

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

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

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

shell> mysql --host=localhost --user=myname --password=mypass mydb
shell> mysql -h localhost -u myname -pmypass mydb

And suppose that

shell> mysql --host=remote.example.com

shell> mysql --host=localhost --user=myname --password mydb
shell> mysql -h localhost -u myname -p mydb

If

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

shell> mysql --host=remote.example.com

shell> mysql --host=remote.example.com

shell> mysql --host=remote.example.com

shell> mysql --host=remote.example.com

The

shell> mysql --host=remote.example.com

4.2.3.4. Command-Line Options that Affect Option-File Handling

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

When specifying file names, you should avoid the use of the “

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

• 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, the program exits with an error.

  shell> mysql --host=remote.example.com
  

  38 is interpreted relative to the current directory if given as a relative path name rather than a full path name.

• Use only the given option file. If the file does not exist or is otherwise inaccessible, the program exits with an error.

  shell> mysql --host=remote.example.com
  

  38 is interpreted relative to the current directory if given as a relative path name rather than a full path name.

• If this option is given, the program reads not only its usual option groups, but also groups with the usual names and a suffix of

  shell> mysql --host=remote.example.com
  

  42. For example, the client normally reads the

  shell> mysql --host=localhost --user=myname --password mydb
  shell> mysql -h localhost -u myname -p mydb
  

  50 and

  shell> mysql --host=remote.example.com
  

  08 groups. If the option is given, also reads the

  shell> mysql --host=remote.example.com
  

  46 and

  shell> mysql --host=remote.example.com
  

  47 groups.

• Read options from the named login path in the

  shell> mysql --host=remote.example.com
  

  49 login file. A “login path” is an option group that permits only a limited set of options:

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

  04,

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

  05, and

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

  06. Think of a login path as a set of values that indicate the server host and the credentials for authenticating with the server. To create the login file, use the utility. See . This option was added in MySQL 5.6.6.

• Do not read any option files. If a program does not start because it is reading unknown options from an option file, can be used to prevent the program from reading them.

  The exception is that the

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

  03 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 is used. (

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

  03 is created by the utility. See .)

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

4.2.3.5. Using Options to Set Program Variables

Many MySQL programs have internal variables that can be set at runtime using the statement. See , and .

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, has a

shell> mysql --host=remote.example.com

shell> mysql --host=remote.example.com

shell> mysql --host=localhost --user=myname --password mydb
shell> mysql -h localhost -u myname -p mydb

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

shell> mysql --host=localhost --user=myname --password mydb
shell> mysql -h localhost -u myname -p mydb

shell> mysql --host=localhost --user=myname --password mydb
shell> mysql -h localhost -u myname -p mydb

shell> mysql --host=localhost --user=myname --password mydb
shell> mysql -h localhost -u myname -p mydb

shell> mysql --host=remote.example.com

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

shell> mysql --host=localhost --user=myname --password mydb
shell> mysql -h localhost -u myname -p mydb

Or:

shell> mysql --host=localhost --user=myname --password mydb
shell> mysql -h localhost -u myname -p mydb

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:

shell> mysql --host=localhost --user=myname --password mydb
shell> mysql -h localhost -u myname -p mydb

A variable can be specified by writing it in full or as any unambiguous prefix. For example, the

shell> mysql --host=remote.example.com

shell> mysql --host=remote.example.com

shell> mysql --host=remote.example.com

shell> mysql --host=localhost --user=myname --password mydb
shell> mysql -h localhost -u myname -p mydb

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 at runtime. On the other hand, with 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 --host=localhost --user=myname --password mydb
shell> mysql -h localhost -u myname -p mydb

Conversely, the second of the following lines is legal at runtime, but the first is not:

shell> mysql --host=localhost --user=myname --password mydb
shell> mysql -h localhost -u myname -p mydb

4.2.3.6. Option Defaults, Options Expecting Values, and the

shell> mysql --host=localhost --user=myname --password mydb
shell> mysql -h localhost -u myname -p mydb

66 Sign

By convention, long forms of options that assign a value are written with an equals (

shell> mysql --host=localhost --user=myname --password mydb
shell> mysql -h localhost -u myname -p mydb

shell> mysql --host=localhost --user=myname --password mydb
shell> mysql -h localhost -u myname -p mydb

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=127.0.0.1
shell> mysql --protocol=TCP

In both cases, the 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

shell> mysql --host=remote.example.com

shell> mysql --host=remote.example.com

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

Omitting the required value for one of these option yields an error, such as the one shown here:

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

In this case, was unable to find a value following the 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=127.0.0.1
shell> mysql --protocol=TCP

Because assumes that any string following on the command line is a host name, is interpreted as , 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 option has the default value

shell> mysql --host=remote.example.com

shell> mysql --host=remote.example.com

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

After shutting down the server, restart it as follows:

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

The result is the same, since is not followed by anything else on the command line, and it supplies its own default value. (The

shell> mysql --host=remote.example.com

shell> mysql --host=remote.example.com

shell> mysql --host=remote.example.com

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

The server attempted to start using

shell> mysql --host=remote.example.com

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

Because the option supplies a default value, you must use an equals sign to assign a different value to it, as shown here:

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

Now the server has been started successfully, and is logging errors to the file

shell> mysql --host=remote.example.com

Similar issues can arise when specifying option values in option files. For example, consider a

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

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

When the client reads this file, these entries are parsed as or , with the result shown here:

shell> mysql --host=remote.example.com

However, in option files, an equals sign is not assumed. Suppose the

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

shell> mysql --host=remote.example.com

Trying to start in this case causes a different error:

shell> mysql --host=remote.example.com

A similar error would occur if you were to write

shell> mysql --host=remote.example.com

shell> mysql --host=remote.example.com

shell> mysql --host=remote.example.com

Now the login attempt succeeds:

shell> mysql --host=remote.example.com

This is not the same behavior as with the command line, where the equals sign is not required:

shell> mysql --host=remote.example.com

In MySQL 5.6, specifying an option requiring a value without a value in an option file causes the server to abort with an error. Suppose that

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

shell> mysql --host=remote.example.com

This causes the server to fail on startup, as shown here:

shell> mysql --host=remote.example.com

The option does not require an argument; however, the option requires one, as shown in the error log (which in the absence of a specified value, defaults to

shell> mysql --host=remote.example.com --port=13306

shell> mysql --host=remote.example.com --port=13306

shell> mysql --host=remote.example.com

This is a change from previous behavior, where the server would have interpreted the last two lines in the example

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

shell> mysql --host=remote.example.com --port=13306

4.2.4. Setting Environment Variables

Environment variables can be set at the command prompt to affect the current invocation of your command processor, or set permanently to affect future invocations. To set a variable permanently, you can set it in a startup file or by using the interface provided by your system for this purpose. Consult the documentation for your command interpreter for specific details. , 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

shell> mysql --host=localhost --user=myname --password mydb
shell> mysql -h localhost -u myname -p mydb

shell> mysql --host=remote.example.com

The syntax on Unix depends on your shell. Suppose that you want to specify the TCP/IP port number using the

shell> mysql --host=localhost --user=myname --password=mypass mydb
shell> mysql -h localhost -u myname -pmypass mydb

shell> mysql --host=remote.example.com --port=13306

The first command sets the variable, and the

shell> mysql --host=remote.example.com --port=13306

For csh and tcsh, use setenv to make the shell variable available to the environment:

shell> mysql --host=remote.example.com --port=13306

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

shell> mysql --host=remote.example.com --port=13306

shell> mysql --host=remote.example.com --port=13306

shell> mysql --host=remote.example.com --port=13306

Suppose that your MySQL programs are installed in

shell> mysql --host=localhost --user=myname --password=mypass mydb
shell> mysql -h localhost -u myname -pmypass mydb

shell> mysql --host=localhost --user=myname --password=mypass mydb
shell> mysql -h localhost -u myname -pmypass mydb

shell> mysql --host=remote.example.com --port=13306

shell> mysql --host=remote.example.com --port=13306

bash uses different startup files for login and nonlogin shells, so you might want to add the setting to

shell> mysql --host=remote.example.com --port=13306

shell> mysql --host=remote.example.com --port=13306

shell> mysql --host=localhost --user=myname --password=mypass mydb
shell> mysql -h localhost -u myname -pmypass mydb

If your shell is tcsh, add the following line to your

shell> mysql --host=remote.example.com --port=13306

shell> mysql --host=remote.example.com --port=13306

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

After modifying your

shell> mysql --host=localhost --user=myname --password=mypass mydb
shell> mysql -h localhost -u myname -pmypass mydb

4.3. MySQL Server and Server-Startup Programs

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

4.3.1. mysqld — The MySQL Server

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

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

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

shell> mysql --host=remote.example.com --port=13306

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 . For information about installing MySQL and setting up the initial configuration, see Chapter 2, Installing and Upgrading MySQL.

4.3.2. mysqld_safe — MySQL Server Startup Script

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

tries to start an executable named . To override the default behavior and specify explicitly the name of the server you want to run, specify a or option to . You can also use to indicate the directory where should look for the server.

Many of the options to are the same as the options to . See .

Options unknown to are passed to if they are specified on the command line, but ignored if they are specified in the

shell> mysql --host=remote.example.com --port=13306

reads all options from the

shell> mysql --host=remote.example.com

shell> mysql --host=remote.example.com --port=13306

shell> mysql --host=remote.example.com --port=13306

shell> mysql --host=remote.example.com

shell> mysql --host=remote.example.com --port=13306

For backward compatibility, also reads

shell> mysql --host=remote.example.com --port=13306

shell> mysql --host=remote.example.com --port=13306

supports the following options. It also reads option files and supports the options for processing them described at .

Table 4.1. 

shell> mysql --host=remote.example.com --port=13306

• Display a help message and exit.

• The path to the MySQL installation directory.

• The size of the core file that should be able to create. The option value is passed to ulimit -c.

• The path to the data directory.

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

• The name of an option file to be read instead of the usual option files. This must be the first option on the command line if it is used.

• If cannot find the server, use this option to indicate the path name to the directory where the server is located.

• Write the error log to the given file. See .

• The name of the library to use for memory allocation instead of the system

  shell> mysql --host=remote.example.com --port=13306
  

  40 library. Any library can be used by specifying its path name, but there is a shortcut form to enable use of the

  shell> mysql --host=remote.example.com --port=13306
  

  41 library that is shipped with binary MySQL distributions for Linux in MySQL 5.6.

  The option works by modifying the

  shell> mysql --host=remote.example.com --port=13306
  

  43 environment value to affect dynamic linking to enable the loader to find the memory-allocation library when runs:

  • If the option is not given, or is given without a value (),

    shell> mysql --host=remote.example.com --port=13306
    

    43 is not modified and no attempt is made to use

    shell> mysql --host=remote.example.com --port=13306
    

    41.

  • If the option is given as , looks for a

    shell> mysql --host=remote.example.com --port=13306
    

    41 library in

    shell> mysql --host=remote.example.com --port=13306
    

    49 and then in the MySQL

    shell> mysql --host=remote.example.com --port=13306
    

    50 location (for example,

    shell> mysql --host=remote.example.com --port=13306
    

    51 or whatever is appropriate). If

    shell> mysql --host=remote.example.com --port=13306
    

    52 is found, its path name is added to the beginning of the

    shell> mysql --host=remote.example.com --port=13306
    

    43 value for . If

    shell> mysql --host=remote.example.com --port=13306
    

    41 is not found, aborts with an error.

  • If the option is given as , that full path is added to the beginning of the

    shell> mysql --host=remote.example.com --port=13306
    

    43 value. If the full path points to a nonexistent or unreadable file, aborts with an error.

  • For cases where adds a path name to

    shell> mysql --host=remote.example.com --port=13306
    

    43, it adds the path to the beginning of any existing value the variable already has.

  Linux users can use the

  shell> mysql --host=remote.example.com --port=13306
  

  58 included in binary packages by adding these lines to the

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

  41 file:

  shell> mysql --host=remote.example.com --port=13306
  

  6

  Those lines also suffice for users on any platform who have installed a

  shell> mysql --host=remote.example.com --port=13306
  

  41 package in

  shell> mysql --host=remote.example.com --port=13306
  

  49. To use a specific

  shell> mysql --host=remote.example.com --port=13306
  

  41 library, specify its full path name. Example:

  shell> mysql --host=remote.example.com --port=13306
  

  7

• The name of the server program (in the

  shell> mysql --host=remote.example.com --port=13306
  

  64 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 cannot find the server, use the option to indicate the path name to the directory where the server is located.

• This option is similar to the option, but you specify only the suffix for the server program name. The basename is assumed to be . For example, if you use , starts the program in the

  shell> mysql --host=remote.example.com --port=13306
  

  64 directory. If the argument to is empty, uses in the

  shell> mysql --host=remote.example.com --port=13306
  

  64 directory.

• Use the

  shell> mysql --host=remote.example.com --port=13306
  

  73 program to set the server's scheduling priority to the given value.

• Do not read any option files. This must be the first option on the command line if it is used.

• The number of files that should be able to open. The option value is passed to ulimit -n. Note that you need to start as

  shell> mysql --host=remote.example.com --port=13306
  

  76 for this to work properly!

• The path name of the process ID file.

• 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

  shell> mysql --host=remote.example.com --port=13306
  

  76 system user.

• Do not try to kill stray processes at startup. This option works only on Linux.

• The Unix socket file that the server should use when listening for local connections.

• ,

  causes error messages to be sent to

  shell> mysql --host=remote.example.com --port=13306
  

  85 on systems that support the logger program.

  shell> mysql --host=remote.example.com --port=13306
  

  83 suppresses the use of

  shell> mysql --host=remote.example.com --port=13306
  

  85; messages are written to an error log file.

  When

  shell> mysql --host=remote.example.com --port=13306
  

  85 is used, the

  shell> mysql --host=remote.example.com --port=13306
  

  89 syslog priority/facility is used for all log messages.

• For logging to

  shell> mysql --host=remote.example.com --port=13306
  

  85, messages from and are written with a tag of

  shell> mysql --host=remote.example.com --port=13306
  

  30 and

  shell> mysql --host=remote.example.com --port=13306
  

  93, respectively. To specify a suffix for the tag, use , which modifies the tags to be

  shell> mysql --host=remote.example.com --port=13306
  

  95 and

  shell> mysql --host=remote.example.com --port=13306
  

  96.

• Set the

  shell> mysql --host=remote.example.com --port=13306
  

  98 time zone environment variable to the given option value. Consult your operating system documentation for legal time zone specification formats.

• Run the server as the user having the name

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

  01 or the numeric user ID

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

  00. (“User” in this context refers to a system login account, not a MySQL user listed in the grant tables.)

If you execute with the or 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:

shell> mysql --host=remote.example.com --port=13306

Instead, use the following command:

shell> mysql --host=remote.example.com --port=13306

The 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 .) 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 is invoked). For binary distributions, looks under its working directory for

  shell> mysql --host=localhost --user=myname --password=mypass mydb
  shell> mysql -h localhost -u myname -pmypass mydb
  

  76 and

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

  06 directories. For source distributions, it looks for

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

  07 and

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

  08 directories. This condition should be met if you execute from your MySQL installation directory (for example,

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

  09 for a binary distribution).

• If the server and databases cannot be found relative to the working directory, attempts to locate them by absolute path names. Typical locations are

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

  10 and

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

  60. 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 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 from the MySQL installation directory:

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

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

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

When you use to start , arranges for error (and notice) messages from itself and from to go to the same destination.

There are several options for controlling the destination of these messages:

• : Write error messages to

  shell> mysql --host=remote.example.com --port=13306
  

  85 on systems that support the logger program.

• : Do not write error messages to

  shell> mysql --host=remote.example.com --port=13306
  

  85. Messages are written to the default error log file (

  shell> mysql --host=remote.example.com
  

  82.err in the data directory), or to a named file if the option is given.

• : Write error messages to the named error file.

If none of these options is given, the default is .

If and are both given, a warning is issued and takes precedence.

When writes a message, notices go to the logging destination (

shell> mysql --host=remote.example.com --port=13306

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

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

Normally, you should not edit the script. Instead, configure by using command-line options or options in the

shell> mysql --host=remote.example.com --port=13306

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

4.3.3. mysql.server — MySQL Server Startup Script

MySQL distributions on Unix include a script named . 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 Mac OS X Startup Item for MySQL.

can be found in the

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

If you use the Linux server RPM package (

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

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

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

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

If you install MySQL from a source distribution or using a binary distribution format that does not install automatically, you can install it manually. Instructions are provided in .

reads options from the

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

shell> mysql --host=remote.example.com

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

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

supports the following options.

4.3.4. mysqld_multi — Manage Multiple MySQL Servers

is designed to manage several 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.

searches for groups named

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

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

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

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

shell> mysql --host=remote.example.com

To invoke , use the following syntax:

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

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

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

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

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

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

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

Each

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

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

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

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

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

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

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

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

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

This command starts a single server using option group

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

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

This command stops several servers, using option groups

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

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

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

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

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

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

searches for option files as follows:

• With , no option files are read.

• With , only the named file is read.

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

Option files read are searched for

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

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

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

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

The

shell> mysql --host=remote.example.com

shell> mysql --host=remote.example.com --port=13306

shell> mysql --host=remote.example.com

shell> mysql --host=remote.example.com --port=13306

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 binary to be used to stop servers.

• The binary to be used. Note that you can specify as the value for this option also. If you use to start the server, you can include the

  shell> mysql --host=remote.example.com --port=13306
  

  93 or

  shell> mysql --host=remote.example.com --port=13306
  

  64 options in the corresponding

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

  38] option group. These options indicate the name of the server that should start and the path name of the directory where the server is located. (See the descriptions for these options in .) Example:

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

  5

• Print log information to

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

  26 rather than to the log file. By default, output goes to the log file.

• The password of the MySQL account to use when invoking . 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

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

  45 and

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

  47 operations.

• The user name of the MySQL account to use when invoking .

• Be more verbose.

• Display version information and exit.

Some notes about :

• Most important: Before using be sure that you understand the meanings of the options that are passed to the servers and why you would want to have separate processes. Beware of the dangers of using multiple 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 .

• Make sure that the data directory for each server is fully accessible to the Unix account that the specific process is started as. Do not use the Unix

  shell> mysql --host=remote.example.com --port=13306
  

  76 account for this, unless you know what you are doing. See .

• Make sure that the MySQL account used for stopping the servers (with the program) has the same user name and password for each server. Also, make sure that the account has the 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

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

  95 account by executing the following commands for each server:

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

  6

  See . You have to do this for each 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

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

  95 from the host where you want to run .

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

• The option is very important if you are using to start (for example, ) Every should have its own process ID file. The advantage of using instead of is that monitors its process and restarts it if the process terminates due to a signal sent using

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

  00 or for other reasons, such as a segmentation fault. Please note that the script might require that you start it from a certain place. This means that you might have to change location to a certain directory before running . If you have problems starting, please see the script. Check especially the lines:

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

  7

  The test performed by these lines should be successful, or you might encounter problems. See .

• You might want to use the option for , but to do this you need to run the script as the Unix superuser (

  shell> mysql --host=remote.example.com --port=13306
  

  76). Having the option in the option file doesn't matter; you just get a warning if you are not the superuser and the processes are started under your own Unix account.

The following example shows how you might set up an option file for use with . The order in which the 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

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

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

See .

4.4. MySQL Installation-Related Programs

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

4.4.1. comp_err — Compile MySQL Error Message File

creates the

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

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

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

also generates

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

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

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

For more information about how error messages are defined, see the MySQL Internals Manual.

Invoke like this:

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

supports the following options.

• ,

  shell> mysql --host=localhost --user=myname --password mydb
  shell> mysql -h localhost -u myname -p mydb
  

  58

  Display a help message and exit.

• ,

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

  13

  The character set directory. The default is

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

  14.

• ,

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

  16

  Write a debugging log. A typical

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

  17 string is 'd:t:O,

  shell> mysql --host=remote.example.com
  

  38'. The default is

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

  19.

• ,

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

  21

  Print some debugging information when the program exits.

• , -H

  shell> mysql --host=remote.example.com
  

  38

  The name of the error header file. The default is

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

  07.

• , -F

  shell> mysql --host=remote.example.com
  

  38

  The name of the input file. The default is

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

  27.

• , -N

  shell> mysql --host=remote.example.com
  

  38

  The name of the error name file. The default is

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

  08.

• ,

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

  32

  The name of the output base directory. The default is

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

  33.

• , -O

  shell> mysql --host=remote.example.com
  

  38

  The name of the output file. The default is

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

  04.

• ,

  shell> mysql --host=localhost --user=myname --password mydb
  shell> mysql -h localhost -u myname -p mydb
  

  41

  The name for the SQLSTATE header file. The default is

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

  09.

• ,

  shell> mysql --host=localhost --user=myname --password mydb
  shell> mysql -h localhost -u myname -p mydb
  

  61

  Display version information and exit.

4.4.2. mysqlbug — Generate Bug Report

This program is obsolete.

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

4.4.3. mysql_install_db — Initialize MySQL Data Directory

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

As of MySQL 5.6.8, on Unix platforms, creates a default option file named

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

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

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

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

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

With one exception, the settings in the default option file are commented and have no effect. The exception is that the file changes the system variable from its default of to also include . This setting produces a server configuration that results in errors rather than warnings for bad data in operations that modify transactional tables. See .

To invoke , use the following syntax:

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

Because the MySQL server, , needs to access the data directory when it runs later, you should either run from the same system account that will be used for running or run it as

shell> mysql --host=remote.example.com --port=13306

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

needs to invoke with the and options.

If you have set a custom

shell> mysql --host=localhost --user=myname --password=mypass mydb
shell> mysql -h localhost -u myname -pmypass mydb

shell> mysql --host=localhost --user=myname --password=mypass mydb
shell> mysql -h localhost -u myname -pmypass mydb

shell> mysql --host=localhost --user=myname --password=mypass mydb
shell> mysql -h localhost -u myname -pmypass mydb

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

After sets up the

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

supports the following options, which can be specified on the command line or in the

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

shell> mysql --host=remote.example.com

• The path to the MySQL installation directory.

• ,

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

• Cause to run even if DNS does not work. Grant table entries that normally use host names will use IP addresses.

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

  • Create a random password, assign it to the initial MySQL

    shell> mysql --host=remote.example.com --port=13306
    

    76 accounts, and set the “password expired” flag for those accounts.

  • Write the initial password file to the

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

    73 file in the directory named by the

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

    74 environment variable. Depending on operating system, using a command such as sudo may cause the value of

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

    74 to refer to the home directory of the

    shell> mysql --host=remote.example.com --port=13306
    

    76 system user.

    If

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

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

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

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

  • Remove the anonymous-user MySQL accounts.

  As a result of these actions, it is necessary after installation to start the server, connect as

  shell> mysql --host=remote.example.com --port=13306
  

  76 using the password written to the

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

  73 file, and to assign a new

  shell> mysql --host=remote.example.com --port=13306
  

  76 password. Until this is done,

  shell> mysql --host=remote.example.com --port=13306
  

  76 cannot do anything else. This must be done for each

  shell> mysql --host=remote.example.com --port=13306
  

  76 account you intend to use. To change the password, you can use the statement (for example, with the client). You can also use or .

  New RPM install operations (not upgrades) invoke with the

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

  70 option. (Install operations using RPMs for Unbreakable Linux Network are unaffected because they do not use .)

  As of MySQL 5.6.9, new Solaris PKG install operations (not upgrades) invoke with the

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

  70 option.

  For install operations using a binary

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

  87 distribution or a source distribution, you can invoke with the

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

  70 option manually to make your MySQL installation more secure. This is recommended, particularly for sites with sensitive data.

  This option was added in MySQL 5.6.8.

• For internal use. This option is used during the MySQL installation process for install operations performed using RPM packages.

• Use IP addresses rather than host names when creating grant table entries. This option can be useful if your DNS does not work.

• For internal use. This option specifies the directory under which looks for support files such as the error message file and the file for populating the help tables.

• The system (login) user name to use for running . Files and directories created by will be owned by this user. You must be

  shell> mysql --host=remote.example.com --port=13306
  

  76 to use this option. By default, runs using your current login name and files and directories that it creates will be owned by you.

• Verbose mode. Print more information about what the program does.

• For internal use. This option is used for creating Windows distributions.

4.4.4. mysql_plugin — Configure MySQL Server Plugins

The utility enables MySQL administrators to manage which plugins a MySQL server loads. It provides an alternative to manually specifying the option at server startup or using the and statements at runtime. is available as of MySQL 5.6.3.

Depending on whether is invoked to enable or disable plugins, it inserts or deletes rows in the

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

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

shell> mysql --host=localhost

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

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

shell> mysql --host=localhost

shell> mysql --host=localhost

shell> mysql --host=localhost

shell> mysql --host=localhost

shell> mysql --host=localhost

shell> mysql --host=localhost

For example, to configure components of a plugin library file named

shell> mysql --host=localhost

shell> mysql --host=localhost

shell> mysql --host=localhost

shell> mysql --host=localhost

shell> mysql --host=localhost

shell> mysql --host=localhost

shell> mysql --host=localhost

shell> mysql --host=remote.example.com

shell> mysql --host=localhost

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

The first line in the

shell> mysql --host=localhost

shell> mysql --host=localhost

shell> mysql --host=localhost

shell> mysql --host=localhost

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

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

To disable the plugins, use

shell> mysql --host=localhost

shell> mysql --host=localhost

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

supports the following options, which can be specified on the command line or in the

shell> mysql --host=remote.example.com

shell> mysql --host=remote.example.com

Table 4.2. 

shell> mysql --host=localhost

• ,

  shell> mysql --host=localhost --user=myname --password mydb
  shell> mysql -h localhost -u myname -p mydb
  

  58

  Display a help message and exit.

• ,

  shell> mysql --host=localhost
  

  32

  The server base directory.

• ,

  shell> mysql --host=localhost
  

  34

  The server data directory.

• ,

  shell> mysql --host=localhost
  

  32

  The path to the program.

• ,

  shell> mysql --host=localhost
  

  32

  The path to the server.

• ,

  shell> mysql --host=localhost --user=myname --password=mypass mydb
  shell> mysql -h localhost -u myname -pmypass mydb
  

  71

  Do not read values from the configuration file. This option enables an administrator to skip reading defaults from the configuration file.

  With , this option need not be given first on the command line, unlike most other MySQL programs that support .

• ,

  shell> mysql --host=localhost
  

  43

  The server plugin directory.

• , -i

  shell> mysql --host=remote.example.com
  

  38

  The configuration file. Relative path names are interpreted relative to the current directory. If this option is not given, the default is

  shell> mysql --host=localhost
  

  02.ini in the plugin directory, where

  shell> mysql --host=localhost
  

  02 is the

  shell> mysql --host=localhost
  

  02 argument on the command line.

• ,

  shell> mysql --host=localhost --user=myname --password=mypass mydb
  shell> mysql -h localhost -u myname -pmypass mydb
  

  73

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

  With , this option need not be given first on the command line, unlike most other MySQL programs that support .

• ,

  shell> mysql --host=localhost --user=myname --password mydb
  shell> mysql -h localhost -u myname -p mydb
  

  60

  Verbose mode. Print more information about what the program does. This option can be used multiple times to increase the amount of information.

• ,

  shell> mysql --host=localhost --user=myname --password mydb
  shell> mysql -h localhost -u myname -p mydb
  

  61

  Display version information and exit.

4.4.5. mysql_secure_installation — Improve MySQL Installation Security

This program enables you to improve the security of your MySQL installation in the following ways:

• You can set a password for

  shell> mysql --host=remote.example.com --port=13306
  

  76 accounts.

• You can remove

  shell> mysql --host=remote.example.com --port=13306
  

  76 accounts that are accessible from outside the local host.

• You can remove anonymous-user accounts.

• You can remove the

  shell> mysql --host=localhost --user=myname --password=mypass mydb
  shell> mysql -h localhost -u myname -pmypass mydb
  

  65 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

  shell> mysql --host=localhost
  

  62.

helps you implement security recommendations similar to those described at .

Invoke without arguments:

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

The script will prompt you to determine which actions to perform.

is not available on Windows.

4.4.6. mysql_tzinfo_to_sql — Load the Time Zone Tables

The program loads the time zone tables in the

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

shell> mysql --host=localhost

shell> mysql --host=localhost

can be invoked several ways:

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

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

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

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

The second syntax causes to load a single time zone file

shell> mysql --host=localhost

shell> mysql --host=localhost

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

If your time zone needs to account for leap seconds, invoke using the third syntax, which initializes the leap second information.

shell> mysql --host=localhost

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

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

4.4.7. mysql_upgrade — Check and Upgrade MySQL Tables

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

should be executed each time you upgrade MySQL.

If 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 for manual table repair strategies.

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

You should always back up your current MySQL installation before performing an upgrade. See .

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

To use , make sure that the server is running, and then invoke it like this:

shell> mysql --host=localhost

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

executes the following commands to check and repair tables and to upgrade the system tables:

shell> mysql --host=localhost

Notes about the preceding commands:

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

• For details about what checks the option entails, see the description of the

  shell> mysql --host=localhost
  

  71 option of the statement (see ).

• shell> mysql --host=localhost
  

  74 represents a script generated internally by that contains SQL statements to upgrade the tables in the

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

  33 database.

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

also saves the MySQL version number in a file named

shell> mysql --host=localhost

If you install MySQL from RPM packages on Linux, you must install the server and client RPMs. is included in the server RPM but requires the client RPM because the latter includes . (See .)

does not upgrade the contents of the help tables. For upgrade instructions, see .

supports the following options, which can be specified on the command line or in the

shell> mysql --host=localhost

shell> mysql --host=localhost --user=myname --password mydb
shell> mysql -h localhost -u myname -p mydb

• Display a short help message and exit.

• The path to the MySQL installation directory. This option is accepted for backward compatibility but ignored.

• The path to the data directory. This option is accepted for backward compatibility but ignored.

• Print some debugging information when the program exits.

• ,

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

  21

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

• The client-side authentication plugin to use. See .

  This option was added in MySQL 5.6.2.

• Ignore the

  shell> mysql --host=localhost
  

  76 file and force execution of even if has already been executed for the current version of MySQL.

• The directory in which to look for plugins. It may be necessary to specify this option if the option is used to specify an authentication plugin but does not find it. See .

  This option was added in MySQL 5.6.2.

• ,

  shell> mysql --host=localhost
  

  93

  The path name of the directory to use for creating temporary files.

• ,

  shell> mysql --host=localhost
  

  95

  Upgrade only the system tables, do not upgrade data.

• ,

  shell> mysql --host=localhost --user=myname --password mydb
  shell> mysql -h localhost -u myname -p mydb
  

  48

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

  shell> mysql --host=remote.example.com --port=13306
  

  76.

• Verbose mode. Print more information about what the program does.

• Cause binary logging to be enabled while runs. In MySQL 5.6.6 and earlier, this was the default behavior. (To disable binary logging during the upgrade, it was necessary to use the inverse of this option, by starting the program with

  shell> mysql
  

  001.) Beginning with MySQL 5.6.7, binary logging by is disabled by default (Bug #14221043), and you must invoke the program explicitly with

  shell> mysql
  

  000 if you want its actions to be written to the binary log. (Also beginning with MySQL 5.6.7, the

  shell> mysql
  

  001 option effectively does nothing.)

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

4.5. MySQL Client Programs

This section describes client programs that connect to the MySQL server.

4.5.1. mysql — The MySQL Command-Line Tool

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 option. This forces 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 C API function in the client/server library rather than .

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

shell> mysql --host=localhost

Or:

shell> mysql --host=localhost

Then type an SQL statement, end it with “

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

shell> mysql

shell> mysql

Typing Control+C causes to attempt to kill the current statement. If this cannot be done, or Control+C is typed again before the statement is killed, exits. Previously, Control+C caused to exit in all cases.

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

shell> mysql --host=localhost

On Unix, the client writes a record of executed statements to a history file. See .

supports the following options, which can be specified on the command line or in the

shell> mysql --host=remote.example.com

shell> mysql --host=localhost --user=myname --password mydb
shell> mysql -h localhost -u myname -p mydb

• ,

  shell> mysql --host=localhost --user=myname --password mydb
  shell> mysql -h localhost -u myname -p mydb
  

  58

  Display a help message and exit.

• Enable automatic rehashing. This option is on by default, which enables database, table, and column name completion. Use to disable rehashing. That causes to start faster, but you must issue the

  shell> mysql
  

  017 command if you want to use name completion.

  To complete a name, enter the first part and press Tab. If the name is unambiguous, 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.

• 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

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

  74 or

  shell> mysql
  

  010.)

• ,

  shell> mysql
  

  022

  Print results using tab as the column separator, with each row on a new line. With this option, 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 option.

• This option helps when processing output that may contain values. By default, translates

  shell> mysql
  

  026 in statement strings to

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

  88 and interprets

  shell> mysql
  

  028 as the statement terminator. disables both features. It also disables all commands except

  shell> mysql
  

  030 and

  shell> mysql
  

  031 in non-interactive mode (for input piped to or loaded using the

  shell> mysql
  

  032 command).

  This option was added in MySQL 5.6.3.

• On a computer having multiple network interfaces, this option can be used to select which interface is employed when connecting to the MySQL server.

  This option is supported beginning with MySQL 5.6.1.

• The directory where character sets are installed. See .

• Write column names in results.

• ,

  shell> mysql
  

  037

  Display result set metadata.

• ,

  shell> mysql
  

  039

  Whether to preserve comments in statements sent to the server. The default is --skip-comments (discard comments), enable with --comments (preserve comments).

• ,

  shell> mysql
  

  041

  Compress all information sent between the client and the server if both support compression.

• ,

  shell> mysql
  

  043

  The database to use. This is useful primarily in an option file.

• , -# [

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

  17]

  Write a debugging log. A typical

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

  17 string is 'd:t:o,

  shell> mysql --host=remote.example.com
  

  38'. The default is

  shell> mysql
  

  048.

• Print some debugging information when the program exits.

• ,

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

  21

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

• The client-side authentication plugin to use. See .

• Use

  shell> mysql
  

  054 as the default character set for the client and connection.

  A common issue that can occur when the operating system uses

  shell> mysql
  

  055 or another multi-byte character set is that output from the client is formatted incorrectly, due to the fact that the MySQL client uses the

  shell> mysql
  

  056 character set by default. You can usually fix such issues by using this option to force the client to use the system character set instead.

  See , for more information.

• Set the statement delimiter. The default is the semicolon character (“

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

  74”).

• Disable named commands. Use the

  shell> mysql
  

  060 form only, or use named commands only at the beginning of a line ending with a semicolon (“

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

  74”). starts with this option enabled by default. However, even with this option, long-format commands still work from the first line. See .

• Enable the

  shell> mysql
  

  063 cleartext authentication plugin. (See .) This option was added in MySQL 5.6.7.

• ,

  shell> mysql
  

  065

  Execute the statement and quit. The default output format is like that produced with . See , for some examples. With this option, does not use the history file.

• ,

  shell> mysql
  

  068

  Continue even if an SQL error occurs.

• A colon-separated list of one or more patterns specifying statements not to log to the history file. For more information, see . This option was added in MySQL 5.6.8.

• ,

  shell> mysql --host=localhost --user=myname --password mydb
  shell> mysql -h localhost -u myname -p mydb
  

  17

  Connect to the MySQL server on the given host.

• ,

  shell> mysql
  

  073

  Produce HTML output.

• ,

  shell> mysql
  

  075

  Ignore spaces after function names. The effect of this is described in the discussion for the SQL mode (see ).

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

• Enable or disable

  shell> mysql
  

  081 capability for . With no value, the option enables

  shell> mysql
  

  081. The option may be given as or to explicitly disable or enable

  shell> mysql
  

  081. Enabling

  shell> mysql
  

  081 has no effect if the server does not also support it.

• ,

  shell> mysql
  

  089

  Enable named commands. Long-format commands are permitted, not just short-format commands. For example,

  shell> mysql
  

  090 and

  shell> mysql
  

  091 both are recognized. Use to disable named commands. See .

• ,

  shell> mysql
  

  094

  This has the same effect as

  shell> mysql
  

  095. See the description for .

• ,

  shell> mysql
  

  098

  Do not beep when errors occur.

• ,

  shell> mysql
  

  100

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

  Initially, executes statements in the input because specifying a database

  shell> mysql
  

  102 on the command line is equivalent to inserting at the beginning of the input. Then, for each statement encountered, 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 is invoked to process this set of statements:

  shell> mysql --host=localhost
  

  5

  If the command line is , handles the input as follows:

  • The statement is executed because the default database is

    shell> mysql
    

    106, even though the statement names a table in a different database.

  • The and statements are not executed because the default database is not

    shell> mysql
    

    106, even though the statements name a table in

    shell> mysql
    

    106.

  • The and statements are executed because the default database is

    shell> mysql
    

    106, even though the 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

  shell> mysql
  

  116 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 . , discusses output paging further.

• , -p[

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

  06]

  The password to use when connecting to the server. If you use the short option form (

  shell> mysql --host=localhost --user=myname --password=mypass mydb
  shell> mysql -h localhost -u myname -pmypass mydb
  

  71), you cannot have a space between the option and the password. If you omit the

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

  06 value following the or

  shell> mysql --host=localhost --user=myname --password=mypass mydb
  shell> mysql -h localhost -u myname -pmypass mydb
  

  71 option on the command line, prompts for one.

  Specifying a password on the command line should be considered insecure. See . You can use an option file to avoid giving the password on the command line.

• ,

  shell> mysql --host=localhost --user=myname --password mydb
  shell> mysql -h localhost -u myname -p mydb
  

  24

  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. It may be necessary to specify this option if the option is used to specify an authentication plugin but does not find it. See .

• ,

  shell> mysql --host=localhost --user=myname --password mydb
  shell> mysql -h localhost -u myname -p mydb
  

  27

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

• Set the prompt to the specified format. The default is

  shell> mysql
  

  131. The special sequences that the prompt can contain are described in .

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

• ,

  shell> mysql
  

  134

  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, does not use the history file.

• ,

  shell> mysql
  

  136

  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 or option is given), special characters are escaped in the output so they can be identified easily. Newline, tab,

  shell> mysql
  

  139, and backslash are written as

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

  88,

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

  87,

  shell> mysql
  

  028, and

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

  90. The option disables this character escaping.

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

  shell> mysql --host=localhost
  

  6

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

• , ,

  shell> mysql
  

  149

  Permit only those and statements that specify which rows to modify by using key values. If you have set this option in an option file, you can override it by using on the command line. See , for more information about this option.

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

• The path name to a file containing the server RSA public key. The file must be in PEM format. The public key is used for RSA encryption of the client password for connections to the server made using accounts that authenticate with the

  shell> mysql
  

  156 plugin. This option is ignored for client accounts that do not authenticate with that plugin. It is also ignored if password encryption is not needed, as is the case when the client connects to the server using an SSL connection.

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

  For additional discussion regarding use of the

  shell> mysql
  

  156 plugin, including how to get the RSA public key, see .

  This option is available only if MySQL was built using OpenSSL. It was added in MySQL 5.6.6 under the name

  shell> mysql
  

  158 and renamed in 5.6.7 to

  shell> mysql
  

  159.

• Cause warnings to be shown after each statement if there are any. This option applies to interactive and batch mode.

• Ignore

  shell> mysql
  

  162 signals (typically the result of typing Control+C).

• ,

  shell> mysql --host=localhost
  

  95

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

• ,

  shell> mysql
  

  167

  Do not write column names in results.

• ,

  shell> mysql
  

  169

  Do not write line numbers for errors. Useful when you want to compare result files that include error messages.

• ,

  shell> mysql
  

  171

  For connections to

  shell> mysql --host=localhost --user=myname --password=mypass mydb
  shell> mysql -h localhost -u myname -pmypass mydb
  

  52, the Unix socket file to use, or, on Windows, the name of the named pipe to use.

• shell> mysql --host=localhost --user=myname --password mydb
  shell> mysql -h localhost -u myname -p mydb
  

  45

  Options that begin with specify whether to connect to the server using SSL and indicate where to find SSL keys and certificates. See .

• ,

  shell> mysql
  

  176

  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. , discusses tee files further.

• ,

  shell> mysql
  

  179

  Flush the buffer after each query.

• ,

  shell> mysql --host=localhost --user=myname --password mydb
  shell> mysql -h localhost -u myname -p mydb
  

  48

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

• ,

  shell> mysql --host=localhost --user=myname --password mydb
  shell> mysql -h localhost -u myname -p mydb
  

  60

  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,

  shell> mysql
  

  184 produces table output format even in batch mode.)

• ,

  shell> mysql --host=localhost --user=myname --password mydb
  shell> mysql -h localhost -u myname -p mydb
  

  61

  Display version information and exit.

• ,

  shell> mysql
  

  188

  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

  shell> mysql
  

  010.

• ,

  shell> mysql
  

  191

  If the connection cannot be established, wait and retry instead of aborting.

• ,

  shell> mysql
  

  193

  Produce XML output.

  shell> mysql --host=localhost
  

  7

  The output when is used with matches that of . See for details.

  The XML output also uses an XML namespace, as shown here:

  shell> mysql --host=localhost
  

  8

  (See Bug #25946.)

You can also set the following variables by using

shell> mysql

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

• shell> mysql
  

  198

  The number of seconds before connection timeout. (Default value is

  shell> mysql
  

  199.)

• shell> mysql --host=remote.example.com
  

  15

  The maximum packet length to send to or receive from the server. (Default value is 16MB.)

• The automatic limit for rows in a join when using . (Default value is 1,000,000.)

• The buffer size for TCP/IP and socket communication. (Default value is 16KB.)

• shell> mysql
  

  204

  The automatic limit for statements when using . (Default value is 1,000.)

sends each SQL statement that you issue to the server to be executed. There is also a set of commands that itself interprets. For a list of these commands, type

shell> mysql

shell> mysql

shell> mysql

shell> mysql --host=localhost

If is invoked with the option, all commands are disabled except

shell> mysql

shell> mysql

shell> mysql

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 multi-line

shell> mysql

• shell> mysql
  

  215],

  shell> mysql
  

  216],

  shell> mysql
  

  217],

  shell> mysql
  

  218]

  Display a help message listing the available commands.

  If you provide an argument to the

  shell> mysql
  

  207 command, uses it as a search string to access server-side help from the contents of the MySQL Reference Manual. For more information, see .

• charset

  shell> mysql
  

  054, \C

  shell> mysql
  

  054

  Change the default character set and issue a

  shell> mysql
  

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

• shell> mysql
  

  223,

  shell> mysql
  

  224

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

• connect [

  shell> mysql
  

  102

  shell> mysql --host=remote.example.com
  

  83]], \r [

  shell> mysql
  

  102

  shell> mysql --host=remote.example.com
  

  83]]

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

• delimiter

  shell> mysql --host=remote.example.com
  

  42, \d

  shell> mysql --host=remote.example.com
  

  42

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

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

  74”).

  The delimiter string can be specified as an unquoted or quoted argument on the

  shell> mysql
  

  031 command line. Quoting can be done with either single quote (

  shell> mysql
  

  233), double quote (

  shell> mysql
  

  234), or backtick (

  shell> mysql
  

  235) 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 (“

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

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

  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

  shell> mysql
  

  237, you will be unable to use the word

  shell> mysql
  

  238 in statements. interprets this as

  shell> mysql
  

  239 followed by the delimiter

  shell> mysql
  

  237.

  When the delimiter recognized by is set to something other than the default of “

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

  74”, instances of that character are sent to the server without interpretation. However, the server itself still interprets “

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

  74” as a statement delimiter and processes statements accordingly. This behavior on the server side comes into play for multiple-statement execution (see ), and for parsing the body of stored procedures and functions, triggers, and events (see ).

• shell> mysql
  

  243,

  shell> mysql
  

  244

  Edit the current input statement. checks the values of the

  shell> mysql
  

  245 and

  shell> mysql
  

  246 environment variables to determine which editor to use. The default editor is vi if neither variable is set.

  The

  shell> mysql
  

  243 command works only in Unix.

• shell> mysql
  

  248,

  shell> mysql
  

  010

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

• shell> mysql
  

  250,

  shell> mysql
  

  091

  Exit .

• shell> mysql
  

  252,

  shell> mysql
  

  009

  Send the current statement to the server to be executed.

• shell> mysql
  

  254,

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

  88

  Disable output paging. See the description for

  shell> mysql
  

  256.

  The

  shell> mysql
  

  254 command works only in Unix.

• shell> mysql
  

  258,

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

  87

  Disable output copying to the tee file. See the description for

  shell> mysql
  

  260.

• shell> mysql
  

  261,

  shell> mysql
  

  262

  Enable display of warnings after each statement.

• shell> mysql
  

  263],

  shell> mysql
  

  264]

  Enable output paging. By using the option when you invoke , 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, checks the value of the

  shell> mysql
  

  116 environment variable and sets the pager to that. Pager functionality works only in interactive mode.

  Output paging can be enabled interactively with the

  shell> mysql
  

  256 command and disabled with

  shell> mysql
  

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

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

  26 if no pager was specified.

  Output paging works only in Unix because it uses the

  shell> mysql
  

  270 function, which does not exist on Windows. For Windows, the

  shell> mysql
  

  260 option can be used instead to save query output, although it is not as convenient as

  shell> mysql
  

  256 for browsing output in some situations.

• shell> mysql
  

  273,

  shell> mysql
  

  274

  Print the current input statement without executing it.

• prompt [

  shell> mysql --host=remote.example.com
  

  42], \R [

  shell> mysql --host=remote.example.com
  

  42]

  Reconfigure the 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

  shell> mysql
  

  277 command with no argument, resets the prompt to the default of

  shell> mysql
  

  131.

• shell> mysql
  

  090,

  shell> mysql
  

  091

  Exit .

• shell> mysql
  

  017,

  shell> mysql
  

  282

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

• source

  shell> mysql --host=remote.example.com
  

  38, \.

  shell> mysql --host=remote.example.com
  

  38

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

  shell> mysql --host=remote.example.com
  

  04 or

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

  90.

• shell> mysql
  

  288,

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

  91

  Provide status information about the connection and the server you are using. If you are running in mode,

  shell> mysql
  

  288 also prints the values for the variables that affect your queries.

• shell> mysql
  

  292,

  shell> mysql
  

  293

  Execute the given command using your default command interpreter.

  The

  shell> mysql
  

  294 command works only in Unix.

• tee [

  shell> mysql --host=remote.example.com
  

  38], \T [

  shell> mysql --host=remote.example.com
  

  38]

  By using the option when you invoke , 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. 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

  shell> mysql
  

  260 command. Without a parameter, the previous file is used. The

  shell> mysql
  

  260 file can be disabled with the

  shell> mysql
  

  258 command. Executing

  shell> mysql
  

  260 again re-enables logging.

• use

  shell> mysql
  

  102, \u

  shell> mysql
  

  102

  Use

  shell> mysql
  

  102 as the default database.

• shell> mysql
  

  305,

  shell> mysql
  

  306

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

Here are a few tips about the

shell> mysql

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

  shell> mysql
  

  00

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

  shell> mysql
  

  01

• In the preceding example, note the

  shell> mysql --host=localhost --user=myname --password=mypass mydb
  shell> mysql -h localhost -u myname -pmypass mydb
  

  75 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

  shell> mysql --host=localhost --user=myname --password=mypass mydb
  shell> mysql -h localhost -u myname -pmypass mydb
  

  75 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

  shell> mysql --host=localhost --user=myname --password=mypass mydb
  shell> mysql -h localhost -u myname -pmypass mydb
  

  75 interactively within less to switch the horizontal-browse mode on and off. For more information, read the less manual page:

  shell> mysql
  

  02

• The

  shell> mysql
  

  311 and

  shell> mysql
  

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

  shell> mysql
  

  03

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

  shell> mysql
  

  04

  In this example, the command would send query results to two files in two different directories on two different file systems mounted on

  shell> mysql
  

  313 and

  shell> mysql
  

  314, yet still display the results onscreen using less.

You can also combine the

shell> mysql

shell> mysql

shell> mysql

shell> mysql

shell> mysql

shell> mysql

shell> mysql

shell> mysql

shell> mysql

shell> mysql

shell> mysql

The

shell> mysql

shell> mysql

OptionDescription

shell> mysql

shell> mysql

shell> mysql

shell> mysql

shell> mysql

shell> mysql

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

shell> mysql

shell> mysql

shell> mysql

shell> mysql

shell> mysql

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

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

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

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

shell> mysql

Your full

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

shell> mysql --host=remote.example.com

shell> mysql

shell> mysql

shell> mysql

shell> mysql

shell> mysql

shell> mysql

shell> mysql

shell> mysql

shell> mysql

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

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

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

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

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

You can set the prompt in several ways:

• Use an environment variable. You can set the

  shell> mysql
  

  361 environment variable to a prompt string. For example:

  shell> mysql
  

  05

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

  shell> mysql
  

  06

• Use an option file. You can set the

  shell> mysql
  

  277 option in the

  shell> mysql --host=remote.example.com
  

  08 group of any MySQL option file, such as

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

  27 or the

  shell> mysql
  

  366 file in your home directory. For example:

  shell> mysql
  

  07

  In this example, note that the backslashes are doubled. If you set the prompt using the

  shell> mysql
  

  277 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 .) The overlap may cause you problems if you use single backslashes. For example,

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

  91 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

  shell> mysql
  

  369 format:

  shell> mysql
  

  08

• Set the prompt interactively. You can change your prompt interactively by using the

  shell> mysql
  

  277 (or

  shell> mysql
  

  339) command. For example:

  shell> mysql
  

  09

4.5.1.3. mysql History File

On Unix, the client writes a record of executed statements to a history file. By default, this file is named

shell> mysql

shell> mysql

The

shell> mysql

does not write statements to the history file when used noninteractively (for example, when reading input from a file or a pipe). It is also possible to explicitly suppress logging of statements to the history file by using the or option.

If you do not want to maintain a history file, first remove

shell> mysql

• Set the

  shell> mysql
  

  373 variable to

  shell> mysql
  

  379. To cause this setting to take effect each time you log in, put the setting in one of your shell's startup files.

• Create

  shell> mysql
  

  372 as a symbolic link to

  shell> mysql
  

  379:

  shell> mysql
  

  10

  You need do this only once.

As of MySQL 5.6.8, ignores for logging purposes statements that match any pattern in the “ignore” list. By default, the pattern list is

shell> mysql

• shell> mysql
  

  383 matches any single character.

• shell> mysql
  

  384 matches any sequence of zero or more characters

To specify additional patterns, use the command option or set the

shell> mysql

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

shell> mysql

shell> mysql

shell> mysql

4.5.1.4. mysql Server-Side Help

shell> mysql

If you provide an argument to the

shell> mysql

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

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

shell> mysql

Use to see a list of the help categories:

shell> mysql

If the search string matches multiple items, shows a list of matching topics:

shell> mysql

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

shell> mysql

4.5.1.5. Executing SQL Statements from a Text File

The client typically is used interactively, like this:

shell> mysql --host=localhost

However, it is also possible to put your SQL statements in a file and then tell to read its input from that file. To do so, create a text file

shell> mysql

shell> mysql

If you place a USE

shell> mysql

shell> mysql

If you are already running , you can execute an SQL script file using the

shell> mysql

shell> mysql

shell> mysql

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

shell> mysql

The statement shown outputs

shell> mysql

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

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 to change its default character set. To do that, invoke with an option such as .

For more information about batch mode, see .

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

4.5.1.6.1. Unicode Support on Windows

Windows provides APIs based on UTF-16LE for reading from and writing to the console. As of MySQL 5.6.2, the client for Windows is able to use these APIs. As of 5.6.3, the Windows installer creates an item in the MySQL menu named

shell> mysql

To take advantage of this support manually, run 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:

1. Open a console window.

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

3. Execute with the (or

   shell> mysql
   

   400) option. This option is necessary because

   shell> mysql
   

   401 is not supported as a connection character set.

With those changes, 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 , you can create a shortcut that invokes . The shortcut should set the console font to Lucida Console or some other compatible Unicode font, and pass the (or

shell> mysql

Alternatively, create a shortcut that only sets the console font, and set the character set in the

shell> mysql --host=remote.example.com

shell> mysql

shell> mysql

4.5.1.6.2. Displaying Query Results Vertically

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

shell> mysql

For beginners, a useful startup option is (or , which has the same effect). It is helpful for cases when you might have issued a

shell> mysql

shell> mysql

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

shell> mysql

See .

The statement has the following effects:

• You are not permitted to execute an or statement unless you specify a key constraint in the

  shell> mysql
  

  409 clause or provide a

  shell> mysql
  

  416 clause (or both). For example:

  shell> mysql
  

  25

• The server limits all large results to 1,000 rows unless the statement includes a

  shell> mysql
  

  416 clause.

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

To specify limits different from 1,000 and 1,000,000, you can override the defaults by using the

shell> mysql

shell> mysql

shell> mysql

4.5.1.6.4. Disabling mysql Auto-Reconnect

If the 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 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:

shell> mysql

The

shell> mysql

For more information about auto-reconnect and its effect on state information when a reconnection occurs, see .

4.5.2. mysqladmin — Client for Administering a MySQL Server

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 like this:

shell> mysql

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

• create

  shell> mysql
  

  102

  Create a new database named

  shell> mysql
  

  102.

• shell> mysql
  

  426

  Tell the server to write debug information to the error log.

  This includes information about the Event Scheduler. See .

• drop

  shell> mysql
  

  102

  Delete the database named

  shell> mysql
  

  102 and all its tables.

• shell> mysql
  

  429

  Display the server status variables and their values.

• shell> mysql
  

  430

  Flush all information in the host cache.

• shell> mysql
  

  431

  Flush all logs.

• shell> mysql
  

  432

  Reload the grant tables (same as

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

  46).

• shell> mysql
  

  434

  Clear status variables.

• shell> mysql
  

  435

  Flush all tables.

• shell> mysql
  

  436

  Flush the thread cache.

• shell> mysql
  

  437,

  shell> mysql
  

  438,...

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

• shell> mysql
  

  439

  This is like the

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

  06 command but stores the password using the old (pre-4.1) password-hashing format. (See .)

• shell> mysql
  

  441

  Set a new password. This changes the password to

  shell> mysql
  

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

  If the

  shell> mysql
  

  442 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> mysql
  

  29

  In MySQL 5.6, the new password can be omitted following the

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

  06 command. In this case, 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

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

  06 is the final command on the command line. Otherwise, the next argument is taken as the password.

  Do not use this command used if the server was started with the option. No password change will be applied. This is true even if you precede the

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

  06 command with

  shell> mysql
  

  432 on the same command line to re-enable the grant tables because the flush operation occurs after you connect. However, you can use to re-enable the grant table and then use a separate command to change the password.

• shell> mysql
  

  449

  Check whether the server is available. The return status from is 0 if the server is running, 1 if it is not. This is 0 even in case of an error such as

  shell> mysql
  

  450, because this means that the server is running but refused the connection, which is different from the server not running.

• shell> mysql
  

  451

  Show a list of active server threads. This is like the output of the statement. If the option is given, the output is like that of . (See .)

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

  46

  Reload the grant tables.

• shell> mysql
  

  457

  Flush all tables and close and open log files.

• shell> mysql
  

  458

  Stop the server.

• shell> mysql
  

  459

  Start replication on a slave server.

• shell> mysql
  

  288

  Display a short server status message.

• shell> mysql
  

  461

  Stop replication on a slave server.

• shell> mysql
  

  462

  Display the server system variables and their values.

• shell> mysql
  

  463

  Display version information from the server.

All commands can be shortened to any unique prefix. For example:

shell> mysql

The command result displays the following values:

• The number of seconds the MySQL server has been running.

• shell> mysql
  

  465

  The number of active threads (clients).

• The number of questions (queries) from clients since the server was started.

• shell> mysql
  

  467

  The number of queries that have taken more than seconds. See .

• shell> mysql
  

  469

  The number of tables the server has opened.

• shell> mysql
  

  470

  The number of

  shell> mysql
  

  471,

  shell> mysql
  

  457, and

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

  46 commands the server has executed.

• shell> mysql
  

  474

  The number of tables that currently are open.

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

supports the following options, which can be specified on the command line or in the

shell> mysql --host=remote.example.com

shell> mysql --host=localhost --user=myname --password mydb
shell> mysql -h localhost -u myname -p mydb

Table 4.4. 

shell> mysql

• ,

  shell> mysql --host=localhost --user=myname --password mydb
  shell> mysql -h localhost -u myname -p mydb
  

  58

  Display a help message and exit.

• On a computer having multiple network interfaces, this option can be used to select which interface is employed when connecting to the MySQL server.

  This option is supported beginning with MySQL 5.6.1.

• The directory where character sets are installed. See .

• ,

  shell> mysql
  

  041

  Compress all information sent between the client and the server if both support compression.

• , -c

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

  41

  The number of iterations to make for repeated command execution if the option is given.

• , -# [

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

  17]

  Write a debugging log. A typical

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

  17 string is 'd:t:o,

  shell> mysql --host=remote.example.com
  

  38'. The default is

  shell> mysql
  

  491.

• Print some debugging information when the program exits.

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

• The client-side authentication plugin to use. See .

• Use

  shell> mysql
  

  054 as the default character set. See .

• Enable the

  shell> mysql
  

  063 cleartext authentication plugin. (See .) This option was added in MySQL 5.6.7.

• ,

  shell> mysql
  

  068

  Do not ask for confirmation for the drop

  shell> mysql
  

  102 command. With multiple commands, continue even if an error occurs.

• ,

  shell> mysql --host=localhost --user=myname --password mydb
  shell> mysql -h localhost -u myname -p mydb
  

  17

  Connect to the MySQL server on the given host.

• ,

  shell> mysql
  

  098

  Suppress the warning beep that is emitted by default for errors such as a failure to connect to the server.

• , -p[

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

  06]

  The password to use when connecting to the server. If you use the short option form (

  shell> mysql --host=localhost --user=myname --password=mypass mydb
  shell> mysql -h localhost -u myname -pmypass mydb
  

  71), you cannot have a space between the option and the password. If you omit the

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

  06 value following the or

  shell> mysql --host=localhost --user=myname --password=mypass mydb
  shell> mysql -h localhost -u myname -pmypass mydb
  

  71 option on the command line, prompts for one.

  Specifying a password on the command line should be considered insecure. See . You can use an option file to avoid giving the password on the command line.

• ,

  shell> mysql --host=localhost --user=myname --password mydb
  shell> mysql -h localhost -u myname -p mydb
  

  24

  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. It may be necessary to specify this option if the option is used to specify an authentication plugin but does not find it. See .

• ,

  shell> mysql --host=localhost --user=myname --password mydb
  shell> mysql -h localhost -u myname -p mydb
  

  27

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

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

• ,

  shell> mysql
  

  136

  Show the difference between the current and previous values when used with the option. This option works only with the

  shell> mysql
  

  429 command.

• ,

  shell> mysql --host=localhost
  

  95

  Exit silently if a connection to the server cannot be established.

• ,

  shell> mysql
  

  526

  Execute commands repeatedly, sleeping for

  shell> mysql
  

  527 seconds in between. The option determines the number of iterations. If is not given, executes commands indefinitely until interrupted.

• ,

  shell> mysql
  

  171

  For connections to

  shell> mysql --host=localhost --user=myname --password=mypass mydb
  shell> mysql -h localhost -u myname -pmypass mydb
  

  52, the Unix socket file to use, or, on Windows, the name of the named pipe to use.

• shell> mysql --host=localhost --user=myname --password mydb
  shell> mysql -h localhost -u myname -p mydb
  

  45

  Options that begin with specify whether to connect to the server using SSL and indicate where to find SSL keys and certificates. See .

• ,

  shell> mysql --host=localhost --user=myname --password mydb
  shell> mysql -h localhost -u myname -p mydb
  

  48

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

• ,

  shell> mysql --host=localhost --user=myname --password mydb
  shell> mysql -h localhost -u myname -p mydb
  

  60

  Verbose mode. Print more information about what the program does.

• ,

  shell> mysql --host=localhost --user=myname --password mydb
  shell> mysql -h localhost -u myname -p mydb
  

  61

  Display version information and exit.

• ,

  shell> mysql
  

  188

  Print output vertically. This is similar to , but prints output vertically.

• ,

  shell> mysql
  

  545]

  If the connection cannot be established, wait and retry instead of aborting. If a

  shell> mysql
  

  546 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

shell> mysql

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

• shell> mysql
  

  198

  The maximum number of seconds before connection timeout. The default value is 43200 (12 hours).

• shell> mysql
  

  550

  The maximum number of seconds to wait for server shutdown. The default value is 3600 (1 hour).

4.5.3. mysqlcheck — A Table Maintenance Program

The 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

shell> mysql

shell> mysql

shell> mysql

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

uses the SQL statements , , , and 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 .

The

shell> mysql

shell> mysql

shell> mysql

shell> mysql --host=localhost --user=myname --password mydb
shell> mysql -h localhost -u myname -p mydb

shell> mysql

If is unable to repair a table, see for manual table repair strategies. This will be the case, for example, for

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

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 :

shell> mysql

If you do not name any tables following

shell> mysql

has a special feature compared to other client programs. The default behavior of checking tables () 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 named mysqlrepair, or make a symbolic link to named mysqlrepair. If you invoke mysqlrepair, it repairs tables.

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

CommandMeaningmysqlrepairThe default option is mysqlanalyzeThe default option is mysqloptimizeThe default option is

supports the following options, which can be specified on the command line or in the

shell> mysql

shell> mysql --host=localhost --user=myname --password mydb
shell> mysql -h localhost -u myname -p mydb

Table 4.5. 

shell> mysql

• ,

  shell> mysql --host=localhost --user=myname --password mydb
  shell> mysql -h localhost -u myname -p mydb
  

  58

  Display a help message and exit.

• ,

  shell> mysql
  

  094

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

• ,

  shell> mysql
  

  585

  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.

• ,

  shell> mysql
  

  587

  Analyze the tables.

• If a checked table is corrupted, automatically fix it. Any necessary repairs are done after all tables have been checked.

• shell> mysql
  

  033

  On a computer having multiple network interfaces, this option can be used to select which interface is employed when connecting to the MySQL server.

  This option is supported beginning with MySQL 5.6.1.

• The directory where character sets are installed. See .

• ,

  shell> mysql
  

  039

  Check the tables for errors. This is the default operation.

• ,

  shell> mysql
  

  041

  Check only tables that have changed since the last check or that have not been closed properly.

• ,

  shell> mysql
  

  596

  Invoke with the

  shell> mysql --host=localhost
  

  71 option to check tables for incompatibilities with the current version of the server. This option automatically enables the and options.

• Compress all information sent between the client and the server if both support compression.

• ,

  shell> mysql
  

  022

  Process all tables in the named databases. Normally, 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.

• , -# [

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

  17]

  Write a debugging log. A typical

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

  17 string is 'd:t:o,

  shell> mysql --host=remote.example.com
  

  38'. The default is

  shell> mysql
  

  608.

• Print some debugging information when the program exits.

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

• Use

  shell> mysql
  

  054 as the default character set. See .

• ,

  shell> mysql --host=localhost --user=myname --password mydb
  shell> mysql -h localhost -u myname -p mydb
  

  83

  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!

• The client-side authentication plugin to use. See .

  This option was added in MySQL 5.6.2.

• ,

  shell> mysql
  

  311

  Check only tables that have not been closed properly.

• Convert database names to 5.1 format. Only database names that contain special characters are affected.

• Convert table names to 5.1 format. Only table names that contain special characters are affected. This option also applies to views.

• ,

  shell> mysql
  

  068

  Continue even if an SQL error occurs.

• ,

  shell> mysql --host=localhost --user=myname --password mydb
  shell> mysql -h localhost -u myname -p mydb
  

  17

  Connect to the MySQL server on the given host.

• ,

  shell> mysql
  

  037

  Do a check that is faster than an operation. This finds only 99.99% of all errors, which should be good enough in most cases.

• ,

  shell> mysql
  

  100

  Optimize the tables.

• , -p[

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

  06]

  The password to use when connecting to the server. If you use the short option form (

  shell> mysql --host=localhost --user=myname --password=mypass mydb
  shell> mysql -h localhost -u myname -pmypass mydb
  

  71), you cannot have a space between the option and the password. If you omit the

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

  06 value following the or

  shell> mysql --host=localhost --user=myname --password=mypass mydb
  shell> mysql -h localhost -u myname -pmypass mydb
  

  71 option on the command line, prompts for one.

  Specifying a password on the command line should be considered insecure. See . You can use an option file to avoid giving the password on the command line.

• ,

  shell> mysql --host=localhost --user=myname --password mydb
  shell> mysql -h localhost -u myname -p mydb
  

  24

  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. It may be necessary to specify this option if the option is used to specify an authentication plugin but does not find it. See .

  This option was added in MySQL 5.6.2.

• ,

  shell> mysql --host=localhost --user=myname --password mydb
  shell> mysql -h localhost -u myname -p mydb
  

  27

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

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

• ,

  shell> mysql
  

  134

  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.

• ,

  shell> mysql
  

  136

  Perform a repair that can fix almost anything except unique keys that are not unique.

• ,

  shell> mysql --host=localhost
  

  95

  Silent mode. Print only error messages.

• ,

  shell> mysql
  

  171

  For connections to

  shell> mysql --host=localhost --user=myname --password=mypass mydb
  shell> mysql -h localhost -u myname -pmypass mydb
  

  52, the Unix socket file to use, or, on Windows, the name of the named pipe to use.

• shell> mysql --host=localhost --user=myname --password mydb
  shell> mysql -h localhost -u myname -p mydb
  

  45

  Options that begin with

  shell> mysql --host=localhost --user=myname --password mydb
  shell> mysql -h localhost -u myname -p mydb
  

  46 specify whether to connect to the server using SSL and indicate where to find SSL keys and certificates. See .

• Override the or

  shell> mysql
  

  022 option. All name arguments following the option are regarded as table names.

• For repair operations on

  shell> mysql
  

  004 tables, get the table structure from the

  shell> mysql
  

  658 file so that the table can be repaired even if the

  shell> mysql
  

  659 header is corrupted.

• ,

  shell> mysql --host=localhost --user=myname --password mydb
  shell> mysql -h localhost -u myname -p mydb
  

  48

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

• ,

  shell> mysql --host=localhost --user=myname --password mydb
  shell> mysql -h localhost -u myname -p mydb
  

  60

  Verbose mode. Print information about the various stages of program operation.

• ,

  shell> mysql --host=localhost --user=myname --password mydb
  shell> mysql -h localhost -u myname -p mydb
  

  61

  Display version information and exit.

• This option is enabled by default, so that , , and statements generated by are written to the binary log. Use to cause

  shell> mysql
  

  671 to be added to the statements so that they are not logged. Use the when these statements should not be sent to replication slaves or run when using the binary logs for recovery from backup.

4.5.4. mysqldump — A Database Backup Program

The client is a utility that performs , producing a set of SQL statements that can be run to reproduce the original schema objects, table data, or both. It dumps one or more MySQL database for backup or transfer to another SQL server. The command can also generate output in CSV, other delimited text, or XML format.

requires at least the privilege for dumped tables, for dumped views, and if the option is not used. Certain options might require other privileges as noted in the option descriptions.

Performance and Scalability Considerations

shell> mysql

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

• If your tables are primarily

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

  42 tables, or if you have a mix of

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

  42 and

  shell> mysql
  

  004 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

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

  42 backups with minimal disruption; it can also back up tables from

  shell> mysql
  

  004 and other storage engines; and it provides a number of convenient options to accommodate different backup scenarios. See MySQL Enterprise Backup User's Guide (Version 3.8.1).

• If your tables are primarily

  shell> mysql
  

  004 tables, consider using the instead, for better performance than of backup and restore operations. See .

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 option (or , which enables ). The option (and hence ) is enabled by default, so to enable memory buffering, use .

If you are using a recent version of to generate a dump to be reloaded into a very old MySQL server, use the option instead of the or option.

For additional information about , see .

Syntax

There are three general ways to invoke , to dump progressively more data:

shell> mysql

To dump entire databases, do not name any tables following

shell> mysql

To see a list of the options your version of supports, issue the command .

Option Syntax - Alphabetical Summary

supports the following options, which can be specified on the command line or in the

shell> mysql

shell> mysql --host=localhost --user=myname --password mydb
shell> mysql -h localhost -u myname -p mydb

Table 4.6. 

shell> mysql

Connection Options

The 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, this option can be used to select which interface is employed when connecting to the MySQL server.

  This option is supported beginning with MySQL 5.6.1.

• shell> mysql --host=localhost --user=myname --password mydb
  shell> mysql -h localhost -u myname -p mydb
  

  55,

  shell> mysql
  

  041

  Compress all information sent between the client and the server if both support compression.

• --default-auth=

  shell> mysql --host=localhost
  

  02

  The client-side authentication plugin to use. See .

• shell> mysql --host=localhost --user=myname --password mydb
  shell> mysql -h localhost -u myname -p mydb
  

  16,

  shell> mysql --host=localhost --user=myname --password mydb
  shell> mysql -h localhost -u myname -p mydb
  

  17

  Dump data from the MySQL server on the given host. The default host is

  shell> mysql --host=localhost --user=myname --password=mypass mydb
  shell> mysql -h localhost -u myname -pmypass mydb
  

  52.

• --password[=

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

  06], -p[

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

  06]

  The password to use when connecting to the server. If you use the short option form (

  shell> mysql --host=localhost --user=myname --password=mypass mydb
  shell> mysql -h localhost -u myname -pmypass mydb
  

  71), you cannot have a space between the option and the password. If you omit the

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

  06 value following the

  shell> mysql --host=localhost --user=myname --password=mypass mydb
  shell> mysql -h localhost -u myname -pmypass mydb
  

  70 or

  shell> mysql --host=localhost --user=myname --password=mypass mydb
  shell> mysql -h localhost -u myname -pmypass mydb
  

  71 option on the command line, prompts for one.

  Specifying a password on the command line should be considered insecure. See . You can use an option file to avoid giving the password on the command line.

• shell> mysql --host=localhost --user=myname --password mydb
  shell> mysql -h localhost -u myname -p mydb
  

  07,

  shell> mysql --host=localhost --user=myname --password mydb
  shell> mysql -h localhost -u myname -p mydb
  

  24

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

• shell> mysql --host=localhost
  

  42

  The directory in which to look for plugins. It may be necessary to specify this option if the option is used to specify an authentication plugin but does not find it. See .

• shell> mysql --host=localhost --user=myname --password mydb
  shell> mysql -h localhost -u myname -p mydb
  

  26,

  shell> mysql --host=localhost --user=myname --password mydb
  shell> mysql -h localhost -u myname -p mydb
  

  27

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

• shell> mysql --host=localhost --user=myname --password mydb
  shell> mysql -h localhost -u myname -p mydb
  

  28

  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 .

• shell> mysql --host=remote.example.com --port=13306
  

  81,

  shell> mysql
  

  171

  For connections to

  shell> mysql --host=localhost --user=myname --password=mypass mydb
  shell> mysql -h localhost -u myname -pmypass mydb
  

  52, the Unix socket file to use, or, on Windows, the name of the named pipe to use.

• shell> mysql --host=localhost --user=myname --password mydb
  shell> mysql -h localhost -u myname -p mydb
  

  45

  Options that begin with specify whether to connect to the server using SSL and indicate where to find SSL keys and certificates. See .

• shell> mysql --host=localhost --user=myname --password mydb
  shell> mysql -h localhost -u myname -p mydb
  

  47,

  shell> mysql --host=localhost --user=myname --password mydb
  shell> mysql -h localhost -u myname -p mydb
  

  48

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

You can also set the following variables by using

shell> mysql

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

• shell> mysql --host=remote.example.com
  

  15

  The maximum size of the buffer for client/server communication. The maximum is 1GB.

• shell> mysql
  

  203

  The initial size of the buffer for client/server communication. When creating multiple-row statements (as with the or option), creates rows up to length. If you increase this variable, ensure that the variable in the MySQL server is at least this large.

DDL Options

Usage scenarios for 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.

Debug Options

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

• shell> mysql
  

  735

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

• shell> mysql
  

  038,

  shell> mysql
  

  075

  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 .

• --debug[=

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

  17], -# [

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

  17]

  Write a debugging log. A typical

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

  17 string is 'd:t:o,

  shell> mysql --host=remote.example.com
  

  38'. The default value is

  shell> mysql
  

  743.

• shell> mysql --host=localhost
  

  84

  Print some debugging information when the program exits.

• shell> mysql --port=13306 --host=127.0.0.1
  shell> mysql --port=13306 --protocol=TCP
  

  20

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

• shell> mysql
  

  746

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

  shell> mysql
  

  34

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

• shell> mysql --port=13306 --host=127.0.0.1
  shell> mysql --port=13306 --protocol=TCP
  

  69,

  shell> mysql
  

  068

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

  One use for this option is to cause 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

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

  69, exits with an error message. With

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

  69, prints the error message, but it also writes an SQL comment containing the view definition to the dump output and continues executing.

• --log-error=

  shell> mysql --host=remote.example.com
  

  38

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

• shell> mysql
  

  738

  See the description for the option.

• shell> mysql --host=localhost --user=myname --password mydb
  shell> mysql -h localhost -u myname -p mydb
  

  62,

  shell> mysql --host=localhost --user=myname --password mydb
  shell> mysql -h localhost -u myname -p mydb
  

  60

  Verbose mode. Print more information about what the program does.

Help Options

The following options display information about the command itself.

• shell> mysql --host=localhost --user=myname --password=mypass mydb
  shell> mysql -h localhost -u myname -pmypass mydb
  

  50,

  shell> mysql --host=localhost --user=myname --password mydb
  shell> mysql -h localhost -u myname -p mydb
  

  58

  Display a help message and exit.

• shell> mysql --host=localhost --user=myname --password mydb
  shell> mysql -h localhost -u myname -p mydb
  

  63,

  shell> mysql --host=localhost --user=myname --password mydb
  shell> mysql -h localhost -u myname -p mydb
  

  61

  Display version information and exit.

Internationalization Options

The following options change how the command represents character data with national language settings.

• shell> mysql
  

  034

  The directory where character sets are installed. See .

• shell> mysql
  

  053

  Use

  shell> mysql
  

  054 as the default character set. See . If no character set is specified, uses

  shell> mysql
  

  055, and earlier versions use

  shell> mysql
  

  056.

• shell> mysql
  

  770,

  shell> mysql
  

  167

  Turns off the setting, the same as specifying

  shell> mysql
  

  773.

• shell> mysql
  

  772

  Add

  shell> mysql
  

  775 to the output. This option is enabled by default. To suppress the

  shell> mysql
  

  222 statement, use .

Replication Options

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

• shell> mysql
  

  778

  For a slave dump produced with the option, add a statement before the statement and a statement at the end of the output.

• shell> mysql
  

  783

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

• --dump-slave[=

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

  81]

  This option is similar to 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 statement that indicates the binary log coordinates (file name and position) of the dumped slave's master (rather than the coordinates of the dumped server, as is done by the option). These are the master server coordinates from which the slave should start replicating.

  The option value is handled the same way as for and has the same effect as

  shell> mysql
  

  785 in terms of enabling or disabling other options and in how locking is handled.

  In conjunction with

  shell> mysql
  

  779, the and options can also be used.

• shell> mysql
  

  794

  For the statement in a slave dump produced with the option, add

  shell> mysql
  

  798 and

  shell> mysql
  

  798 options for the host name and TCP/IP port number of the slave's master.

• --master-data[=

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

  81]

  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 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 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 privilege and the binary log must be enabled.

  The

  shell> mysql
  

  785 option automatically turns off . It also turns on , unless 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 ). 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. To do this, use the following procedure on the existing slave:

  1. Stop the slave's SQL thread and get its current status:

     shell> mysql
     

     35

  2. From the output of the statement, the binary log coordinates of the master server from which the new slave should start replicating are the values of the

     shell> mysql
     

     810 and

     shell> mysql
     

     811 fields. Denote those values as

     shell> mysql --host=remote.example.com
     

     38 and

     shell> mysql
     

     813.

  3. Dump the slave server:

     shell> mysql
     

     36

     Using

     shell> mysql
     

     814 works only if binary logging has been enabled on the slave. Otherwise, fails with the error Binlogging on server not active. In this case you must handle any locking issues in another manner, using one or more of , , , or , as required by your application and environment.

  4. Restart the slave:

     shell> mysql
     

     37

  5. On the new slave, load the dump file:

     shell> mysql
     

     38

  6. On the new slave, set the replication coordinates to those of the master server obtained earlier:

     shell> mysql
     

     39

     The statement might also need other parameters, such as

     shell> mysql
     

     820 to point the slave to the correct master server host. Add any such parameters as necessary.

  Prior to MySQL 5.6.4, this option was required for dumping the replication log tables (see ).

• --set-gtid-purged=

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

  81

  This option enables control over global transaction ID (GTID) information written to the dump file, by indicating whether to add a statement to the output.

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

  shell> mysql
  

  823.

  ValueMeaning

  shell> mysql --host=localhost --user=myname --password mydb
  shell> mysql -h localhost -u myname -p mydb
  

  92Add no

  shell> mysql --host=remote.example.com
  

  59 statement to the output.

  shell> mysql --host=localhost --user=myname --password mydb
  shell> mysql -h localhost -u myname -p mydb
  

  90Add a

  shell> mysql --host=remote.example.com
  

  59 statement to the output. An error occurs if GTIDs are not enabled on the server.

  shell> mysql
  

  823Add a

  shell> mysql --host=remote.example.com
  

  59 statement to the output if GTIDs are enabled on the server.

  This option was added in MySQL 5.6.9.

Format Options

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

• shell> mysql
  

  830

  Produce more compact output. This option enables the , , , , and options.

• shell> mysql
  

  836

  Produce output that is more compatible with other database systems or with older MySQL servers. The value of

  shell> mysql
  

  837 can be

  shell> mysql
  

  838,

  shell> mysql
  

  839,

  shell> mysql
  

  840,

  shell> mysql
  

  841,

  shell> mysql
  

  842,

  shell> mysql
  

  843,

  shell> mysql
  

  844,

  shell> mysql
  

  845,

  shell> mysql
  

  846,

  shell> mysql
  

  847, or

  shell> mysql
  

  848. To use several values, separate them by commas. These values have the same meaning as the corresponding options for setting the server SQL mode. See .

  This option does not guarantee compatibility with other servers. It only enables those SQL mode values that are currently available for making dump output more compatible. For example,

  shell> mysql
  

  849 does not map data types to Oracle types or use Oracle comment syntax.

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

• shell> mysql
  

  850,

  shell> mysql
  

  039

  Use complete statements that include column names.

• shell> mysql
  

  853

  Include all MySQL-specific table options in the statements.

• shell> mysql
  

  855,

  shell> mysql
  

  856,

  shell> mysql
  

  857,

  shell> mysql
  

  858

  These options are used with the option and have the same meaning as the corresponding

  shell> mysql
  

  860 clauses for . See .

• shell> mysql
  

  863

  Dump binary columns using hexadecimal notation (for example,

  shell> mysql
  

  864 becomes

  shell> mysql
  

  865). The affected data types are , , the types, and .

• shell> mysql
  

  870

  This option is used with the option and has the same meaning as the corresponding

  shell> mysql
  

  872 clause for . See .

• shell> mysql
  

  875,

  shell> mysql
  

  876

  Quote identifiers (such as database, table, and column names) within “

  shell> mysql
  

  235” characters. If the SQL mode is enabled, identifiers are quoted within “

  shell> mysql
  

  234” characters. This option is enabled by default. It can be disabled with

  shell> mysql
  

  880, but this option should be given after any option such as that may enable .

• --result-file=

  shell> mysql --host=remote.example.com
  

  38, -r

  shell> mysql --host=remote.example.com
  

  38

  Direct output to a given file. This option should be used on Windows to prevent newline “

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

  88” characters from being converted to “

  shell> mysql
  

  026” carriage return/newline sequences. The result file is created and its previous contents overwritten, even if an error occurs while generating the dump.

• shell> mysql
  

  887,

  shell> mysql
  

  888

  Produce tab-separated text-format data files. For each dumped table, creates a

  shell> mysql
  

  889.sql file that contains the statement that creates the table, and the server writes a

  shell> mysql
  

  889.txt file that contains its data. The option value is the directory in which to write the files.

  This option should be used only when is run on the same machine as the server. You must have the privilege, and the server must have permission to write files in the directory that you specify.

  By default, the

  shell> mysql
  

  893 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

  shell> mysql
  

  894 and options.

  Column values are converted to the character set specified by the option.

• shell> mysql
  

  897

  This option enables columns to be dumped and reloaded between servers in different time zones. sets its connection time zone to UTC and adds

  shell> mysql
  

  899 to the dump file. Without this option, 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.

  shell> mysql
  

  897 also protects against changes due to daylight saving time.

  shell> mysql
  

  897 is enabled by default. To disable it, use

  shell> mysql
  

  903.

• shell> mysql
  

  192,

  shell> mysql
  

  193

  Write dump output as well-formed XML.

  shell> mysql
  

  906,

  shell> mysql
  

  907, and Empty Values: For a column named

  shell> mysql
  

  908, the

  shell> mysql
  

  906 value, an empty string, and the string value

  shell> mysql
  

  907 are distinguished from one another in the output generated by this option as follows.

  Value:XML Representation:

  shell> mysql
  

  906 (unknown value)

  shell> mysql
  

  395

  shell> mysql
  

  913 (empty string)

  shell> mysql
  

  395

  shell> mysql
  

  907 (string value)

  shell> mysql
  

  906

  The output from the client when run using the option also follows the preceding rules. (See .)

  XML output from includes the XML namespace, as shown here:

  shell> mysql
  

  40

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

Filtering Options

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

shell> mysql

• shell> mysql --host=localhost
  

  69,

  shell> mysql
  

  094

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

  Prior to MySQL 5.6.4, the

  shell> mysql
  

  923 and

  shell> mysql
  

  924 tables (see ) were not included by this option.

• shell> mysql
  

  556,

  shell> mysql
  

  022

  Dump several databases. Normally, 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. and statements are included in the output before each new database.

• shell> mysql
  

  929,

  shell> mysql
  

  188

  Include Event Scheduler events for the dumped databases in the output.

• shell> mysql
  

  931

  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.

• shell> mysql
  

  932,

  shell> mysql
  

  933

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

• shell> mysql
  

  918,

  shell> mysql
  

  936

  Include stored routines (procedures and functions) for the dumped databases in the output. Use of this option requires the privilege for the

  shell> mysql
  

  938 table. The output generated by using

  shell> mysql
  

  918 contains and statements to re-create the routines. However, these statements do not include attributes such as the routine creation and modification timestamps. This means that when the routines are reloaded, they will be created with the timestamps equal to the reload time.

  If you require routines to be re-created with their original timestamp attributes, do not use

  shell> mysql
  

  918. Instead, dump and reload the contents of the

  shell> mysql
  

  938 table directly, using a MySQL account that has appropriate privileges for the

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

  33 database.

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

• shell> mysql
  

  653

  Override the or

  shell> mysql
  

  022 option. regards all name arguments following the option as table names.

• shell> mysql
  

  949

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

  shell> mysql
  

  950.

• shell> mysql
  

  951',

  shell> mysql
  

  952'

  Dump only rows selected by the given

  shell> mysql
  

  409 condition. Quotes around the condition are mandatory if it contains spaces or other characters that are special to your command interpreter.

  Examples:

  shell> mysql
  

  41

Performance Options

The following options are the most relevant for the performance particularly of the restore operations. For large data sets, restore operation (processing the

shell> mysql

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

shell> mysql

Performance is also affected by the , primarily for the dump operation.

• shell> mysql
  

  957

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

  As of MySQL 5.6.6,

  shell> mysql
  

  960 inserts are deprecated, so this option will be removed in a future release.

• shell> mysql
  

  961,

  shell> mysql
  

  962

  For each table, surround the statements with

  shell> mysql
  

  964 DISABLE KEYS */; and

  shell> mysql
  

  964 ENABLE KEYS */; statements. This makes loading the dump file faster because the indexes are created after all rows are inserted. This option is effective only for nonunique indexes of

  shell> mysql
  

  004 tables.

• shell> mysql
  

  692,

  shell> mysql --host=localhost --user=myname --password mydb
  shell> mysql -h localhost -u myname -p mydb
  

  83

  Use multiple-row syntax that include several

  shell> mysql
  

  970 lists. This results in a smaller dump file and speeds up inserts when the file is reloaded.

• shell> mysql
  

  971

  Write statements rather than statements.

• shell> mysql
  

  685

  This option, enabled by default, is shorthand for the combination of . It gives a fast dump operation and produces a dump file that can be reloaded into a MySQL server quickly.

  Because the

  shell> mysql
  

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

  shell> mysql
  

  685.

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

  65,

  shell> mysql
  

  134

  This option is useful for dumping large tables. It forces 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.

• shell> mysql
  

  690

  See the description for the option.

Transactional Options

The following options trade off the performance of the dump operation, against the reliability and consistency of the exported data.

• shell> mysql
  

  815

  Surround each table dump with and statements. This results in faster inserts when the dump file is reloaded. See .

• shell> mysql
  

  995,

  shell> mysql
  

  311

  Flush the MySQL server log files before starting the dump. This option requires the privilege. If you use this option in combination with the option, the logs are flushed for each database dumped. The exception is when using , , or : In this case, the logs are flushed only once, corresponding to the moment that all tables are locked. If you want your dump and the log flush to happen at exactly the same moment, you should use

  shell> mysql
  

  995 together with , , or .

• shell> mysql --host=localhost --user=myname --password=mypass mydb
  shell> mysql -h localhost -u myname -pmypass mydb
  

  006

  Send a statement to the server after dumping the

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

  33 database. This option should be used any time the dump contains the

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

  33 database and any other database that depends on the data in the

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

  33 database for proper restoration.

• shell> mysql
  

  806,

  shell> mysql --host=localhost --user=myname --password=mypass mydb
  shell> mysql -h localhost -u myname -pmypass mydb
  

  012

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

• shell> mysql
  

  805,

  shell> mysql --host=localhost --user=myname --password=mypass mydb
  shell> mysql -h localhost -u myname -pmypass mydb
  

  016

  For each dumped database, lock all tables to be dumped before dumping them. The tables are locked with

  shell> mysql --host=localhost --user=myname --password=mypass mydb
  shell> mysql -h localhost -u myname -pmypass mydb
  

  017 to permit concurrent inserts in the case of

  shell> mysql
  

  004 tables. For transactional tables such as

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

  42, is a much better option than

  shell> mysql
  

  805 because it does not need to lock the tables at all.

  Because

  shell> mysql
  

  805 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 , automatically enable

  shell> mysql
  

  805. If you want to override this, use

  shell> mysql --host=localhost --user=myname --password=mypass mydb
  shell> mysql -h localhost -u myname -pmypass mydb
  

  025 at the end of the option list.

• shell> mysql --host=localhost --user=myname --password=mypass mydb
  shell> mysql -h localhost -u myname -pmypass mydb
  

  026

  Enclose the statements for each dumped table within

  shell> mysql --host=localhost --user=myname --password=mypass mydb
  shell> mysql -h localhost -u myname -pmypass mydb
  

  028 and statements.

• shell> mysql --host=localhost --user=myname --password=mypass mydb
  shell> mysql -h localhost -u myname -pmypass mydb
  

  030

  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

  shell> mysql
  

  004 table to be loaded into an

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

  42 table, but makes the dump operation take considerably longer.

• shell> mysql
  

  676

  This option sends a SQL statement to the server before dumping data. It is useful only with transactional tables such as

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

  42, because then it dumps the consistent state of the database at the time when was issued without blocking any applications.

  When using this option, you should keep in mind that only

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

  42 tables are dumped in a consistent state. For example, any

  shell> mysql
  

  004 or

  shell> mysql --host=localhost --user=myname --password mydb
  shell> mysql -h localhost -u myname -p mydb
  

  36 tables dumped while using this option may still change state.

  While a

  shell> mysql
  

  676 dump is in process, to ensure a valid dump file (correct table contents and binary log coordinates), no other connection can use the following statements: , , , , . Because a consistent read is not isolated from those statements, using them on a table being dumped can cause the underlying statement of to return incorrect contents or fail.

  The

  shell> mysql
  

  676 option and the option are mutually exclusive because causes any pending transactions to be committed implicitly.

  To dump large tables, combine the

  shell> mysql
  

  676 option with the .