Chapter 4 MySQL Programs

Table of Contents

4.1 Overview of MySQL Programs
4.2 Using MySQL Programs
4.2.1 Invoking MySQL Programs
4.2.2 Connecting to the MySQL Server
4.2.3 Specifying Program Options
4.2.4 Using Options on the Command Line
4.2.5 Program Option Modifiers
4.2.6 Using Option Files
4.2.7 Command-Line Options that Affect Option-File Handling
4.2.8 Using Options to Set Program Variables
4.2.9 Option Defaults, Options Expecting Values, and the = Sign
4.2.10 Setting Environment Variables
4.3 MySQL Server and Server-Startup Programs
4.3.1 mysqld — The MySQL Server
4.3.2 mysqld_safe — MySQL Server Startup Script
4.3.3 mysql.server — MySQL Server Startup Script
4.3.4 mysqld_multi — Manage Multiple MySQL Servers
4.4 MySQL Installation-Related Programs
4.4.1 comp_err — Compile MySQL Error Message File
4.4.2 mysqlbug — Generate Bug Report
4.4.3 mysql_install_db — Initialize MySQL Data Directory
4.4.4 mysql_plugin — Configure MySQL Server Plugins
4.4.5 mysql_secure_installation — Improve MySQL Installation Security
4.4.6 mysql_tzinfo_to_sql — Load the Time Zone Tables
4.4.7 mysql_upgrade — Check and Upgrade MySQL Tables
4.5 MySQL Client Programs
4.5.1 mysql — The MySQL Command-Line Tool
4.5.2 mysqladmin — Client for Administering a MySQL Server
4.5.3 mysqlcheck — A Table Maintenance Program
4.5.4 mysqldump — A Database Backup Program
4.5.5 mysqlimport — A Data Import Program
4.5.6 mysqlshow — Display Database, Table, and Column Information
4.5.7 mysqlslap — Load Emulation Client
4.6 MySQL Administrative and Utility Programs
4.6.1 innochecksum — Offline InnoDB File Checksum Utility
4.6.2 myisam_ftdump — Display Full-Text Index information
4.6.3 myisamchk — MyISAM Table-Maintenance Utility
4.6.4 myisamlog — Display MyISAM Log File Contents
4.6.5 myisampack — Generate Compressed, Read-Only MyISAM Tables
4.6.6 mysql_config_editor — MySQL Configuration Utility
4.6.7 mysqlaccess — Client for Checking Access Privileges
4.6.8 mysqlbinlog — Utility for Processing Binary Log Files
4.6.9 mysqldumpslow — Summarize Slow Query Log Files
4.6.10 mysqlhotcopy — A Database Backup Program
4.6.11 mysql_convert_table_format — Convert Tables to Use a Given Storage Engine
4.6.12 mysql_find_rows — Extract SQL Statements from Files
4.6.13 mysql_fix_extensions — Normalize Table File Name Extensions
4.6.14 mysql_setpermission — Interactively Set Permissions in Grant Tables
4.6.15 mysql_waitpid — Kill Process and Wait for Its Termination
4.6.16 mysql_zap — Kill Processes That Match a Pattern
4.7 MySQL Program Development Utilities
4.7.1 msql2mysql — Convert mSQL Programs for Use with MySQL
4.7.2 mysql_config — Display Options for Compiling Clients
4.7.3 my_print_defaults — Display Options from Option Files
4.7.4 resolve_stack_dump — Resolve Numeric Stack Trace Dump to Symbols
4.8 Miscellaneous Programs
4.8.1 perror — Display MySQL Error Message Information
4.8.2 replace — A String-Replacement Utility
4.8.3 resolveip — Resolve Host name to IP Address or Vice Versa
4.9 MySQL Program Environment Variables

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

4.1 Overview of MySQL Programs

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

Most MySQL distributions include all of these programs, except for those programs that are platform-specific. (For example, the server startup scripts are not used on Windows.) The exception is that RPM distributions are more specialized. There is one RPM for the server, another for client programs, and so forth. If you appear to be missing one or more programs, see Chapter 2, Installing and Upgrading MySQL, for information on types of distributions and what they contain. It may be that you have a distribution that does not include all programs and you need to install an additional package.

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

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

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

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

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

Environment Variable Meaning
MYSQL_UNIX_PORT The default Unix socket file; used for connections to localhost
MYSQL_TCP_PORT The default port number; used for TCP/IP connections
MYSQL_PWD The default password
MYSQL_DEBUG Debug trace options when debugging
TMPDIR The directory where temporary tables and files are created

For a full list of environment variables used by MySQL programs, see Section 4.9, “MySQL Program Environment Variables”.

Use of MYSQL_PWD is insecure. See Section 6.1.2.1, “End-User Guidelines for Password Security”.

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> represents the prompt for your command interpreter; it is not part of what you type. The particular prompt you see depends on your command interpreter. Typical prompts are $ for sh, ksh, or bash, % for csh or tcsh, and C:\> for the Windows command.com or cmd.exe command interpreters.

shell> mysql --user=root test
shell> mysqladmin extended-status variables
shell> mysqlshow --help
shell> mysqldump -u root personnel

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

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

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

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

You may find it necessary to invoke MySQL programs using the path name to the bin directory in which they are installed. This is likely to be the case if you get a program not found error whenever you attempt to run a MySQL program from any directory other than the bin directory. To make it more convenient to use MySQL, you can add the path name of the bin directory to your PATH environment variable setting. That enables you to run a program by typing only its name, not its entire path name. For example, if mysql is installed in /usr/local/mysql/bin, you can run the program by invoking it as mysql, and it is not necessary to invoke it as /usr/local/mysql/bin/mysql.

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

4.2.2 Connecting to the MySQL Server

This section describes how to establish a connection to the MySQL server. For additional information if you are unable to connect, see Section 6.2.7, “Troubleshooting Problems Connecting to MySQL”.

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

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

This command invokes mysql without specifying any connection parameters explicitly:

shell> mysql

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

  • The default host name is localhost. On Unix, this has a special meaning, as described later.

  • The default user name is ODBC on Windows or your Unix login name on Unix.

  • No password is sent if neither -p nor --password is given.

  • For mysql, the first nonoption argument is taken as the name of the default database. If there is no such option, mysql does not select a default database.

To specify the host name and user name explicitly, as well as a password, supply appropriate options on the command line:

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

For password options, the password value is optional:

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

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

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

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

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

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

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

Clients determine what type of connection to make as follows:

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

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

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

  • On Windows, if host is ., or TCP/IP is not enabled and --socket is not specified or the host is empty, the client connects using a named pipe, if the server has named-pipe connections enabled. If named-pipe connections are not enabled, an error occurs.

  • Otherwise, TCP/IP is used.

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

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

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

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

If the server is configured to accept IPv6 connections, clients can connect over IPv6 using --host=::1. See Section 5.1.11, “IPv6 Support”.

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

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

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

To specify a port number explicitly, use the --port or -P option:

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

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

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

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

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

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

The following list summarizes the options that can be used to control how client programs connect to the server:

  • --default-auth=plugin

    A hint about the client-side authentication plugin to use. See Section 6.3.7, “Pluggable Authentication”.

  • --host=host_name, -h host_name

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

  • --password[=pass_val], -p[pass_val]

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

  • --pipe, -W

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

  • --port=port_num, -P port_num

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

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

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

    shell> mysql --host=localhost
    

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

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

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

    --protocol Value Connection Protocol Permissible Operating Systems
    TCP TCP/IP connection to local or remote server All
    SOCKET Unix socket file connection to local server Unix only
    PIPE Named-pipe connection to local or remote server Windows only
    MEMORY Shared-memory connection to local server Windows only
  • --shared-memory-base-name=name

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

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

  • --socket=file_name, -S file_name

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

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

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

  • --ssl*

    Options that begin with --ssl are used for establishing a secure connection to the server using SSL, if the server is configured with SSL support. For details, see Section 6.4.2, “Command Options for Encrypted Connections”.

  • --user=user_name, -u user_name

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

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

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 Section 4.2.10, “Setting Environment Variables”). This method is useful for options that you want to apply each time the program runs. In practice, option files are used more commonly for this purpose, but Section 5.7.3, “Running Multiple MySQL Instances on Unix”, discusses one situation in which environment variables can be very helpful. It describes a handy technique that uses such variables to specify the TCP/IP port number and Unix socket file for the server and for client programs.

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

shell> mysql -h example.com -h localhost

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

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

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

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

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

shell> mysqldump --comp
mysqldump: ambiguous option '--comp' (compatible, compress)

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

Note

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

4.2.4 Using Options on the Command Line

Program options specified on the command line follow these rules:

  • Options are given after the command name.

  • An option argument begins with one dash or two dashes, depending on whether it is a short form or long form of the option name. Many options have both short and long forms. For example, -? and --help are the short and long forms of the option that instructs a MySQL program to display its help message.

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

  • Some options take a value following the option name. For example, -h localhost or --host=localhost indicate the MySQL server host to a client program. The option value tells the program the name of the host where the MySQL server is running.

  • For a long option that takes a value, separate the option name and the value by an = sign. For a short option that takes a value, the option value can immediately follow the option letter, or there can be a space between: -hlocalhost and -h localhost are equivalent. An exception to this rule is the option for specifying your MySQL password. This option can be given in long form as --password=pass_val or as --password. In the latter case (with no password value given), the program prompts you for the password. The password option also may be given in short form as -ppass_val or as -p. However, for the short form, if the password value is given, it must follow the option letter with no intervening space. The reason for this is that if a space follows the option letter, the program has no way to tell whether a following argument is supposed to be the password value or some other kind of argument. Consequently, the following two commands have two completely different meanings:

    shell> mysql -ptest
    shell> mysql -p test
    

    The first command instructs mysql to use a password value of test, but specifies no default database. The second instructs mysql to prompt for the password value and to use test as the default database.

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

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

    shell> mysqladmin --count=1K --sleep=10 ping
    
  • When specifying file names as option values, avoid the use of the ~ shell metacharacter because it might not be interpreted as you expect.

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

shell> mysql -u root -p --execute="SELECT User, Host FROM mysql.user"
Enter password: ******
+------+-----------+
| User | Host      |
+------+-----------+
|      | gigan     |
| root | gigan     |
|      | localhost |
| jon  | localhost |
| root | localhost |
+------+-----------+
shell>
Note

The long form (--execute) is followed by an equals sign (=).

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

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

shell> mysql -u root -p -e "SELECT VERSION();SELECT NOW()"
Enter password: ******
+------------------+
| VERSION()        |
+------------------+
| 5.6.28-debug-log |
+------------------+
+---------------------+
| NOW()               |
+---------------------+
| 2015-11-05 20:01:28 |
+---------------------+

4.2.5 Program Option Modifiers

Some options are boolean and control behavior that can be turned on or off. For example, the mysql client supports a --column-names option that determines whether or not to display a row of column names at the beginning of query results. By default, this option is enabled. However, you may want to disable it in some instances, such as when sending the output of mysql into another program that expects to see only data and not an initial header line.

To disable column names, you can specify the option using any of these forms:

--disable-column-names
--skip-column-names
--column-names=0

The --disable and --skip prefixes and the =0 suffix all have the same effect: They turn the option off.

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

--column-names
--enable-column-names
--column-names=1

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

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

shell> mysql --loose-no-such-option
mysql: WARNING: unknown option '--loose-no-such-option'

The --loose prefix can be useful when you run programs from multiple installations of MySQL on the same machine and list options in an option file. An option that may not be recognized by all versions of a program can be given using the --loose prefix (or loose in an option file). Versions of the program that recognize the option process it normally, and versions that do not recognize it issue a warning and ignore it.

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

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

Maximum value of 'back_log' cannot be set

4.2.6 Using Option Files

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

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

Note

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

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

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

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

Note

Option files used with NDB Cluster programs are covered in Section 18.3, “Configuration of NDB Cluster”.

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

Table 4.1 Option Files Read on Windows Systems

File Name Purpose
%WINDIR%\my.ini, %WINDIR%\my.cnf Global options
C:\my.ini, C:\my.cnf Global options
BASEDIR\my.ini, BASEDIR\my.cnf Global options
defaults-extra-file The file specified with --defaults-extra-file, if any
%APPDATA%\MySQL\.mylogin.cnf Login path options (clients only)

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

C:\> echo %WINDIR%

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

C:\> echo %APPDATA%

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

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

Note

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

Table 4.2 Option Files Read on Unix and Unix-Like Systems

File Name Purpose
/etc/my.cnf Global options
/etc/mysql/my.cnf Global options
SYSCONFDIR/my.cnf Global options
$MYSQL_HOME/my.cnf Server-specific options (server only)
defaults-extra-file The file specified with --defaults-extra-file, if any
~/.my.cnf User-specific options
~/.mylogin.cnf User-specific login path options (clients only)

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

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

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

  • Let BASEDIR and DATADIR represent the path names of the MySQL base directory and data directory, respectively.

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

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

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

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

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

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

Any long option that may be given on the command line when running a MySQL program can be given in an option file as well. To get the list of available options for a program, run it with the --help option. (For mysqld, use --verbose and --help.)

The syntax for specifying options in an option file is similar to command-line syntax (see Section 4.2.4, “Using Options on the Command Line”). However, in an option file, you omit the leading two dashes from the option name and you specify only one option per line. For example, --quick and --host=localhost on the command line should be specified as quick and host=localhost on separate lines in an option file. To specify an option of the form --loose-opt_name in an option file, write it as loose-opt_name.

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

  • #comment, ;comment

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

  • [group]

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

  • opt_name

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

  • opt_name=value

    This is equivalent to --opt_name=value on the command line. In an option file, you can have spaces around the = character, something that is not true on the command line. The value optionally can be enclosed within single quotation marks or double quotation marks, which is useful if the value contains a # comment character.

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

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

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

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

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

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

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

basedir="C:\Program Files\MySQL\MySQL Server 5.6"
basedir="C:\\Program Files\\MySQL\\MySQL Server 5.6"
basedir="C:/Program Files/MySQL/MySQL Server 5.6"
basedir=C:\\Program\sFiles\\MySQL\\MySQL\sServer\s5.6

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

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

The [client] group enables you to specify options that apply to all clients. For example, [client] is the appropriate group to use to specify the password for connecting to the server. (But make sure that the option file is accessible only by yourself, so that other people cannot discover your password.) Be sure not to put an option in the [client] group unless it is recognized by all client programs that you use. Programs that do not understand the option quit after displaying an error message if you try to run them.

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

Here is a typical global option file:

[client]
port=3306
socket=/tmp/mysql.sock

[mysqld]
port=3306
socket=/tmp/mysql.sock
key_buffer_size=16M
max_allowed_packet=8M

[mysqldump]
quick

Here is a typical user option file:

[client]
# The following password will be sent to all standard MySQL clients
password="my password"

[mysql]
no-auto-rehash
connect_timeout=2

[mysqlhotcopy]
interactive-timeout

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

[mysqld-5.6]
sql_mode=TRADITIONAL

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

!include /home/mydir/myopt.cnf

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

!includedir /home/mydir

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

Note

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

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

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

!include /home/mydir/myopt.cnf

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

[mysqladmin]
force

[mysqld]
key_buffer_size=16M

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

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

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

4.2.7 Command-Line Options that Affect Option-File Handling

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

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

  • --defaults-extra-file=file_name

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

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

  • --defaults-file=file_name

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

    Exception: Even with --defaults-file, client programs read .mylogin.cnf.

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

  • --defaults-group-suffix=str

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

  • --login-path=name

    Read options from the named login path in the .mylogin.cnf login path file. A login path is an option group containing options that specify which MySQL server to connect to and which account to authenticate as. To create or modify a login path file, use the mysql_config_editor utility. See Section 4.6.6, “mysql_config_editor — MySQL Configuration Utility”.

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

    shell> mysql --login-path=mypath
    

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

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

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

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

  • --no-defaults

    Do not read any option files. If program startup fails due to reading unknown options from an option file, --no-defaults can be used to prevent them from being read.

    The exception is that client programs read the .mylogin.cnf login path file, if it exists, even when --no-defaults is used. This permits passwords to be specified in a safer way than on the command line even if --no-defaults is present. (.mylogin.cnf is created by the mysql_config_editor utility. See Section 4.6.6, “mysql_config_editor — MySQL Configuration Utility”.)

  • --print-defaults

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

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

4.2.8 Using Options to Set Program Variables

Many MySQL programs have internal variables that can be set at runtime using the SET statement. See Section 13.7.4.1, “SET Syntax for Variable Assignment”, and Section 5.1.8, “Using System Variables”.

Most of these program variables also can be set at server startup by using the same syntax that applies to specifying program options. For example, mysql has a max_allowed_packet variable that controls the maximum size of its communication buffer. To set the max_allowed_packet variable for mysql to a value of 16MB, use either of the following commands:

shell> mysql --max_allowed_packet=16777216
shell> mysql --max_allowed_packet=16M

The first command specifies the value in bytes. The second specifies the value in megabytes. For variables that take a numeric value, the value can be given with a suffix of K, M, or G (either uppercase or lowercase) to indicate a multiplier of 1024, 10242 or 10243. (For example, when used to set max_allowed_packet, the suffixes indicate units of kilobytes, megabytes, or gigabytes.)

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

[mysql]
max_allowed_packet=16777216

Or:

[mysql]
max_allowed_packet=16M

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

[mysqld]
key_buffer_size=512M

[mysqld]
key-buffer-size=512M

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

shell> mysql --max=1000000
mysql: ambiguous option '--max=1000000' (max_allowed_packet, max_join_size)

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

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

shell> mysql --max_allowed_packet=16M
shell> mysql --max_allowed_packet=16*1024*1024

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

mysql> SET GLOBAL max_allowed_packet=16M;
mysql> SET GLOBAL max_allowed_packet=16*1024*1024;

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

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

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

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

shell> mysql --host tonfisk --user jon

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

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

shell> mysql --host 85.224.35.45 --user jon
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.6.43 Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> SELECT CURRENT_USER();
+----------------+
| CURRENT_USER() |
+----------------+
| jon@%          |
+----------------+
1 row in set (0.00 sec)

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

shell> mysql --host 85.224.35.45 --user
mysql: option '--user' requires an argument

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

shell> mysql --host --user jon
ERROR 2005 (HY000): Unknown MySQL server host '--user' (1)

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

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

shell> mysqld_safe &
[1] 11699
shell> 080112 12:53:40 mysqld_safe Logging to '/usr/local/mysql/var/tonfisk.err'.
080112 12:53:40 mysqld_safe Starting mysqld daemon with databases from /usr/local/mysql/var
shell>

After shutting down the server, restart it as follows:

shell> mysqld_safe --log-error &
[1] 11699
shell> 080112 12:53:40 mysqld_safe Logging to '/usr/local/mysql/var/tonfisk.err'.
080112 12:53:40 mysqld_safe Starting mysqld daemon with databases from /usr/local/mysql/var
shell>

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

shell> mysqld_safe --log-error my-errors &
[1] 31357
shell> 080111 22:53:31 mysqld_safe Logging to '/usr/local/mysql/var/tonfisk.err'.
080111 22:53:32 mysqld_safe Starting mysqld daemon with databases from /usr/local/mysql/var
080111 22:53:34 mysqld_safe mysqld from pid file /usr/local/mysql/var/tonfisk.pid ended

[1]+  Done                    ./mysqld_safe --log-error my-errors

The server attempted to start using /usr/local/mysql/var/tonfisk.err as the error log, but then shut down. Examining the last few lines of this file shows the reason:

shell> tail /usr/local/mysql/var/tonfisk.err
080111 22:53:32  InnoDB: Started; log sequence number 0 46409
/usr/local/mysql/libexec/mysqld: Too many arguments (first extra is 'my-errors').
Use --verbose --help to get a list of available options
080111 22:53:32 [ERROR] Aborting

080111 22:53:32  InnoDB: Starting shutdown...
080111 22:53:34  InnoDB: Shutdown completed; log sequence number 0 46409
080111 22:53:34 [Note] /usr/local/mysql/libexec/mysqld: Shutdown complete

080111 22:53:34 mysqld_safe mysqld from pid file /usr/local/mysql/var/tonfisk.pid ended

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

shell> mysqld_safe --log-error=my-errors &
[1] 31437
shell> 080111 22:54:15 mysqld_safe Logging to '/usr/local/mysql/var/my-errors.err'.
080111 22:54:15 mysqld_safe Starting mysqld daemon with databases from /usr/local/mysql/var

shell>

Now the server has been started successfully, and is logging errors to the file /usr/local/mysql/var/my-errors.err.

Similar issues can arise when specifying option values in option files. For example, consider a my.cnf file that contains the following:

[mysql]

host
user

When the mysql client reads this file, these entries are parsed as --host --user or --host=--user, with the result shown here:

shell> mysql
ERROR 2005 (HY000): Unknown MySQL server host '--user' (1)

However, in option files, an equals sign is not assumed. Suppose the my.cnf file is as shown here:

[mysql]

user jon

Trying to start mysql in this case causes a different error:

shell> mysql
mysql: unknown option '--user jon'

A similar error would occur if you were to write host tonfisk in the option file rather than host=tonfisk. Instead, you must use the equals sign:

[mysql]

user=jon

Now the login attempt succeeds:

shell> mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.6.43 Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> SELECT USER();
+---------------+
| USER()        |
+---------------+
| jon@localhost |
+---------------+
1 row in set (0.00 sec)

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

shell> mysql --user jon --host tonfisk
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.6.43 Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> SELECT USER();
+---------------+
| USER()        |
+---------------+
| jon@tonfisk   |
+---------------+
1 row in set (0.00 sec)

Specifying an option requiring a value without a value in an option file causes the server to abort with an error. Suppose that my.cnf contains the following:

[mysqld]
log_error
relay_log
relay_log_index

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

shell> mysqld_safe &

090514 09:48:39 mysqld_safe Logging to '/home/jon/bin/mysql/var/tonfisk.err'.
090514 09:48:39 mysqld_safe Starting mysqld daemon with databases from /home/jon/bin/mysql/var
090514 09:48:39 mysqld_safe mysqld from pid file /home/jon/bin/mysql/var/tonfisk.pid ended

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

shell> tail -n 3 ../var/tonfisk.err

090514 09:48:39 mysqld_safe Starting mysqld daemon with databases from /home/jon/bin/mysql/var
090514  9:48:39 [ERROR] /home/jon/bin/mysql/libexec/mysqld: option '--relay-log' requires an argument
090514  9:48:39 [ERROR] Aborting

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

4.2.10 Setting Environment Variables

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

To specify a value for an environment variable, use the syntax appropriate for your command processor. For example, on Windows, you can set the USER variable to specify your MySQL account name. To do so, use this syntax:

SET USER=your_name

The syntax on Unix depends on your shell. Suppose that you want to specify the TCP/IP port number using the MYSQL_TCP_PORT variable. Typical syntax (such as for sh, ksh, bash, zsh, and so on) is as follows:

MYSQL_TCP_PORT=3306
export MYSQL_TCP_PORT

The first command sets the variable, and the export command exports the variable to the shell environment so that its value becomes accessible to MySQL and other processes.

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

setenv MYSQL_TCP_PORT 3306

The commands to set environment variables can be executed at your command prompt to take effect immediately, but the settings persist only until you log out. To have the settings take effect each time you log in, use the interface provided by your system or place the appropriate command or commands in a startup file that your command interpreter reads each time it starts.

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

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

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

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

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

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

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

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

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

4.3 MySQL Server and Server-Startup Programs

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

4.3.1 mysqld — The MySQL Server

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

Note

Some installation packages contain a debugging version of the server named mysqld-debug. Invoke this version instead of mysqld for debugging support, memory allocation checking, and trace file support (see Section 24.5.1.2, “Creating Trace Files”).

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

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

shell> mysqld --verbose --help

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

For a full description of MySQL Server command options, system variables, and status variables, see Section 5.1, “The MySQL Server”. For information about installing MySQL and setting up the initial configuration, see Chapter 2, Installing and Upgrading MySQL.

4.3.2 mysqld_safe — MySQL Server Startup Script

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

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

Many of the options to mysqld_safe are the same as the options to mysqld. See Section 5.1.6, “Server Command Options”.

Options unknown to mysqld_safe are passed to mysqld if they are specified on the command line, but ignored if they are specified in the [mysqld_safe] group of an option file. See Section 4.2.6, “Using Option Files”.

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

[mysqld]
log-error=error.log

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

mysqld_safe supports the following options. It also reads option files and supports the options for processing them described at Section 4.2.7, “Command-Line Options that Affect Option-File Handling”.

Table 4.3 mysqld_safe Options

Format Description
--basedir Path to MySQL installation directory
--core-file-size Size of core file that mysqld should be able to create
--datadir Path to data directory
--defaults-extra-file Read named option file in addition to usual option files
--defaults-file Read only named option file
--help Display help message and exit
--ledir Path to directory where server is located
--log-error Write error log to named file
--malloc-lib Alternative malloc library to use for mysqld
--mysqld Name of server program to start (in ledir directory)
--mysqld-version Suffix for server program name
--nice Use nice program to set server scheduling priority
--no-defaults Read no option files
--open-files-limit Number of files that mysqld should be able to open
--pid-file Path name of server process ID file
--plugin-dir Directory where plugins are installed
--port Port number on which to listen for TCP/IP connections
--skip-kill-mysqld Do not try to kill stray mysqld processes
--skip-syslog Do not write error messages to syslog; use error log file
--socket Socket file on which to listen for Unix socket connections
--syslog Write error messages to syslog
--syslog-tag Tag suffix for messages written to syslog
--timezone Set TZ time zone environment variable to named value
--user Run mysqld as user having name user_name or numeric user ID user_id

  • --help

    Display a help message and exit.

  • --basedir=dir_name

    The path to the MySQL installation directory.

  • --core-file-size=size

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

  • --datadir=dir_name

    The path to the data directory.

  • --defaults-extra-file=file_name

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

    For additional information about this and other option-file options, see Section 4.2.7, “Command-Line Options that Affect Option-File Handling”.

  • --defaults-file=file_name

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

    For additional information about this and other option-file options, see Section 4.2.7, “Command-Line Options that Affect Option-File Handling”.

  • --ledir=dir_name

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

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

  • --log-error=file_name

    Write the error log to the given file. See Section 5.4.2, “The Error Log”.

  • --malloc-lib=[lib_name]

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

    Note

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

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

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

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

    • If the option is given as --malloc-lib=/path/to/some/library, that full path is added to the beginning of the LD_PRELOAD value. If the full path points to a nonexistent or unreadable file, mysqld_safe aborts with an error.

    • For cases where mysqld_safe adds a path name to LD_PRELOAD, it adds the path to the beginning of any existing value the variable already has.

    Linux users can use the libtcmalloc_minimal.so included in binary packages by adding these lines to the my.cnf file:

    [mysqld_safe]
    malloc-lib=tcmalloc
    

    Those lines also suffice for users on any platform who have installed a tcmalloc package in /usr/lib. To use a specific tcmalloc library, specify its full path name. Example:

    [mysqld_safe]
    malloc-lib=/opt/lib/libtcmalloc_minimal.so
    
  • --mysqld=prog_name

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

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

  • --mysqld-version=suffix

    This option is similar to the --mysqld option, but you specify only the suffix for the server program name. The base name is assumed to be mysqld. For example, if you use --mysqld-version=debug, mysqld_safe starts the mysqld-debug program in the ledir directory. If the argument to --mysqld-version is empty, mysqld_safe uses mysqld in the ledir directory.

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

  • --nice=priority

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

  • --no-defaults

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

    For additional information about this and other option-file options, see Section 4.2.7, “Command-Line Options that Affect Option-File Handling”.

  • --open-files-limit=count

    The number of files that mysqld should be able to open. The option value is passed to ulimit -n.

    Note

    You must start mysqld_safe as root for this to function properly.

  • --pid-file=file_name

    The path name that mysqld should use for its process ID file.

  • --plugin-dir=dir_name

    The path name of the plugin directory.

  • --port=port_num

    The port number that the server should use when listening for TCP/IP connections. The port number must be 1024 or higher unless the server is started by the root system user.

  • --skip-kill-mysqld

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

  • --socket=path

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

  • --syslog, --skip-syslog

    --syslog causes error messages to be sent to syslog on systems that support the logger program. --skip-syslog suppresses the use of syslog; messages are written to an error log file.

    When syslog is used, the daemon.err syslog facility/severity is used for all log messages.

    mysqld_safe ignores --syslog if --log-error is also given.

  • --syslog-tag=tag

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

  • --timezone=timezone

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

  • --user={user_name|user_id}

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

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

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

Instead, use the following command:

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

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

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

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

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

shell> cd mysql_installation_directory
shell> bin/mysqld_safe &

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

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

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

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

  • --log-error=file_name: Write error messages to the named error file.

  • --syslog: Write error messages to syslog on systems that support the logger program.

  • --skip-syslog: Do not write error messages to syslog. Messages are written to the default error log file (host_name.err in the data directory), or to a named file if the --log-error option is given.

If none of these options is given, the default is --skip-syslog.

If --log-error and --syslog are both given, a warning is issued and --log-error takes precedence.

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

4.3.3 mysql.server — MySQL Server Startup Script

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

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

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

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

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

mysql.server stop stops the server by sending a signal to it. You can also stop the server manually by executing mysqladmin shutdown.

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

  • If you use the Linux server RPM package (MySQL-server-VERSION.rpm), or a native Linux package installation, the mysql.server script may be installed in the /etc/init.d directory with the name mysqld or mysql. See Section 2.5.5, “Installing MySQL on Linux Using RPM Packages from Oracle”, for more information on the Linux RPM packages.

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

    shell> cp mysql.server /etc/init.d/mysql
    shell> chmod +x /etc/init.d/mysql
    

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

    shell> chkconfig --add mysql
    

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

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

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

    /bin/sh -c 'cd /usr/local/mysql; ./bin/mysqld_safe --user=mysql &'
    
  • For other systems, consult your operating system documentation to see how to install startup scripts.

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

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

[mysqld]
datadir=/usr/local/mysql/var
socket=/var/tmp/mysql.sock
port=3306
user=mysql

[mysql.server]
basedir=/usr/local/mysql

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

Table 4.4 mysql.server Option-File Options

Option Name Description Type
basedir Path to MySQL installation directory Directory name
datadir Path to MySQL data directory Directory name
pid-file File in which server should write its process ID File name
service-startup-timeout How long to wait for server startup Integer

  • basedir=dir_name

    The path to the MySQL installation directory.

  • datadir=dir_name

    The path to the MySQL data directory.

  • pid-file=file_name

    The path name of the file in which the server should write its process ID.

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

  • service-startup-timeout=seconds

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

4.3.4 mysqld_multi — Manage Multiple MySQL Servers

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

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

To invoke mysqld_multi, use the following syntax:

shell> mysqld_multi [options] {start|stop|reload|report} [GNR[,GNR] ...]

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

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

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

shell> mysqld_multi start 17

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

shell> mysqld_multi stop 8,10-13

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

shell> mysqld_multi --example

mysqld_multi searches for option files as follows:

For additional information about these and other option-file options, see Section 4.2.7, “Command-Line Options that Affect Option-File Handling”.

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

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

mysqld_multi supports the following options.

  • --help

    Display a help message and exit.

  • --example

    Display a sample option file.

  • --log=file_name

    Specify the name of the log file. If the file exists, log output is appended to it.

  • --mysqladmin=prog_name

    The mysqladmin binary to be used to stop servers.

  • --mysqld=prog_name

    The mysqld binary to be used. You can specify mysqld_safe as the value for this option. If you use mysqld_safe to start the server, you can include the mysqld or ledir options in the corresponding [mysqldN] option group. These options indicate the name of the server that mysqld_safe should start and the path name of the directory where the server is located. (See the descriptions for these options in Section 4.3.2, “mysqld_safe — MySQL Server Startup Script”.) Example:

    [mysqld38]
    mysqld = mysqld-debug
    ledir  = /opt/local/mysql/libexec
    
  • --no-log

    Print log information to stdout rather than to the log file. By default, output goes to the log file.

  • --password=password

    The password of the MySQL account to use when invoking mysqladmin. The password value is not optional for this option, unlike for other MySQL programs.

  • --silent

    Silent mode; disable warnings.

  • --tcp-ip

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

  • --user=user_name

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

  • --verbose

    Be more verbose.

  • --version

    Display version information and exit.

Some notes about mysqld_multi:

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

    Important

    Make sure that the data directory for each server is fully accessible to the Unix account that the specific mysqld process is started as. Do not use the Unix root account for this, unless you know what you are doing. See Section 6.1.5, “How to Run MySQL as a Normal User”.

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

    shell> mysql -u root -S /tmp/mysql.sock -p
    Enter password:
    mysql> CREATE USER 'multi_admin'@'localhost' IDENTIFIED BY 'multipass';
    mysql> GRANT SHUTDOWN ON *.* TO 'multi_admin'@'localhost';
    

    See Section 6.2, “The MySQL Access Privilege System”. You have to do this for each mysqld server. Change the connection parameters appropriately when connecting to each one. The host name part of the account name must permit you to connect as multi_admin from the host where you want to run mysqld_multi.

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

  • The --pid-file option is very important if you are using mysqld_safe to start mysqld (for example, --mysqld=mysqld_safe) Every mysqld should have its own process ID file. The advantage of using mysqld_safe instead of mysqld is that mysqld_safe monitors its mysqld process and restarts it if the process terminates due to a signal sent using kill -9 or for other reasons, such as a segmentation fault.

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

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

# This is an example of a my.cnf file for mysqld_multi.
# Usually this file is located in home dir ~/.my.cnf or /etc/my.cnf

[mysqld_multi]
mysqld     = /usr/local/mysql/bin/mysqld_safe
mysqladmin = /usr/local/mysql/bin/mysqladmin
user       = multi_admin
password   = my_password

[mysqld2]
socket     = /tmp/mysql.sock2
port       = 3307
pid-file   = /usr/local/mysql/data2/hostname.pid2
datadir    = /usr/local/mysql/data2
language   = /usr/local/mysql/share/mysql/english
user       = unix_user1

[mysqld3]
mysqld     = /path/to/mysqld_safe
ledir      = /path/to/mysqld-binary/
mysqladmin = /path/to/mysqladmin
socket     = /tmp/mysql.sock3
port       = 3308
pid-file   = /usr/local/mysql/data3/hostname.pid3
datadir    = /usr/local/mysql/data3
language   = /usr/local/mysql/share/mysql/swedish
user       = unix_user2

[mysqld4]
socket     = /tmp/mysql.sock4
port       = 3309
pid-file   = /usr/local/mysql/data4/hostname.pid4
datadir    = /usr/local/mysql/data4
language   = /usr/local/mysql/share/mysql/estonia
user       = unix_user3

[mysqld6]
socket     = /tmp/mysql.sock6
port       = 3311
pid-file   = /usr/local/mysql/data6/hostname.pid6
datadir    = /usr/local/mysql/data6
language   = /usr/local/mysql/share/mysql/japanese
user       = unix_user4

See Section 4.2.6, “Using Option Files”.

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

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

comp_err also generates mysqld_error.h, mysqld_ername.h, and sql_state.h header files.

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

Invoke comp_err like this:

shell> comp_err [options]

comp_err supports the following options.

  • --help, -?

    Display a help message and exit.

  • --charset=dir_name, -C dir_name

    The character set directory. The default is ../sql/share/charsets.

  • --debug=debug_options, -# debug_options

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

  • --debug-info, -T

    Print some debugging information when the program exits.

  • --header_file=file_name, -H file_name

    The name of the error header file. The default is mysqld_error.h.

  • --in_file=file_name, -F file_name

    The name of the input file. The default is ../sql/share/errmsg-utf8.txt.

  • --name_file=file_name, -N file_name

    The name of the error name file. The default is mysqld_ername.h.

  • --out_dir=dir_name, -D dir_name

    The name of the output base directory. The default is ../sql/share/.

  • --out_file=file_name, -O file_name

    The name of the output file. The default is errmsg.sys.

  • --statefile=file_name, -S file_name

    The name for the SQLSTATE header file. The default is sql_state.h.

  • --version, -V

    Display version information and exit.

4.4.2 mysqlbug — Generate Bug Report

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

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

4.4.3 mysql_install_db — Initialize MySQL Data Directory

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

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

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

To invoke mysql_install_db, use the following syntax:

shell> mysql_install_db [options]

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

shell> scripts/mysql_install_db --user=mysql \
         --basedir=/opt/mysql/mysql \
         --datadir=/opt/mysql/mysql/data
Note

After mysql_install_db sets up the InnoDB system tablespace, changes to some tablespace characteristics require setting up a whole new instance. This includes the file name of the first file in the system tablespace and the number of undo logs. If you do not want to use the default values, make sure that the settings for the innodb_data_file_path and innodb_log_file_size configuration parameters are in place in the MySQL configuration file before running mysql_install_db. Also make sure to specify as necessary other parameters that affect the creation and location of InnoDB files, such as innodb_data_home_dir and innodb_log_group_home_dir.

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

Note

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

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

Table 4.5 mysql_install_db Options

Format Description Introduced Deprecated
--basedir Path to base directory
--builddir Path to build directory (for out-of-source builds)
--cross-bootstrap For internal use
--datadir Path to data directory
--defaults-extra-file Read named option file in addition to usual option files
--defaults-file Read only named option file
--force Run even if DNS does not work
--help Display help message and exit
--keep-my-cnf Keep existing my.cnf file, do not create new one 5.6.20 5.6.20
--ldata Synonym for --datadir
--no-defaults Read no option files
--random-passwords Generate administrative account random password 5.6.8
--rpm For internal use
--skip-name-resolve Use IP addresses rather than host names in grant tables
--srcdir For internal use
--user System login user under which to execute mysqld
--verbose Verbose mode
--windows For internal use

  • --help

    Display a help message and exit.

  • --basedir=dir_name

    The path to the MySQL installation directory.

  • --builddir=dir_name

    For use with --srcdir and out-of-source builds. Set this to the location of the directory where the built files reside.

  • --cross-bootstrap

    For internal use. This option is used for building system tables on one host intended for another.

  • --datadir=dir_name

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

  • --defaults-extra-file=file_name

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

    For additional information about this and other option-file options, see Section 4.2.7, “Command-Line Options that Affect Option-File Handling”.

  • --defaults-file=file_name

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

    For additional information about this and other option-file options, see Section 4.2.7, “Command-Line Options that Affect Option-File Handling”.

  • --force

    Cause mysql_install_db to run even if DNS does not work. Grant table entries normally created using host names will use IP addresses instead.

  • --keep-my-cnf

    Tell mysql_install_db to preserve any existing my.cnf file and not create a new default my.cnf file. This option was added in MySQL 5.6.20.

  • --ldata=dir_name

    A synonym for --datadir.

  • --no-defaults

    Do not read any option files. If program startup fails due to reading unknown options from an option file, --no-defaults can be used to prevent them from being read.

    For additional information about this and other option-file options, see Section 4.2.7, “Command-Line Options that Affect Option-File Handling”.

  • --random-passwords

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

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

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

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

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

    • No anonymous-user MySQL accounts are created.

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

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

    As of MySQL 5.6.9, new Solaris PKG install operations (not upgrades) invoke mysql_install_db with the --random-passwords option.

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

    This option was added in MySQL 5.6.8.

  • --rpm

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

  • --skip-name-resolve

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

  • --srcdir=dir_name

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

  • --user=user_name

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

  • --verbose

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

  • --windows

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

4.4.4 mysql_plugin — Configure MySQL Server Plugins

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

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

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

mysql_plugin [options] plugin {ENABLE|DISABLE}

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

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

myplugins
plugin1
plugin2
plugin3

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

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

shell> mysql_plugin myplugins ENABLE

To disable the plugins, use DISABLE rather than ENABLE.

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

mysql_plugin supports the following options, which can be specified on the command line or in the [mysqld] group of any option file. For options specified in a [mysqld] group, mysql_plugin recognizes the --basedir, --datadir, and --plugin-dir options and ignores others. For information about option files used by MySQL programs, see Section 4.2.6, “Using Option Files”.

Table 4.6 mysql_plugin Options

Format Description
--basedir The server base directory
--datadir The server data directory
--help Display help message and exit
--my-print-defaults Path to my_print_defaults
--mysqld Path to server
--no-defaults Do not read configuration file
--plugin-dir Directory where plugins are installed
--plugin-ini The plugin configuration file
--print-defaults Show configuration file defaults
--verbose Verbose mode
--version Display version information and exit

  • --help, -?

    Display a help message and exit.

  • --basedir=dir_name, -b dir_name

    The server base directory.

  • --datadir=dir_name, -d dir_name

    The server data directory.

  • --my-print-defaults=file_name, -b file_name

    The path to the my_print_defaults program.

  • --mysqld=file_name, -b file_name

    The path to the mysqld server.

  • --no-defaults, -p

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

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

  • --plugin-dir=dir_name, -p dir_name

    The server plugin directory.

  • --plugin-ini=file_name, -i file_name

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

  • --print-defaults, -P

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

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

  • --verbose, -v

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

  • --version, -V

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

  • You can remove root accounts that are accessible from outside the local host.

  • You can remove anonymous-user accounts.

  • You can remove the test database (which by default can be accessed by all users, even anonymous users), and privileges that permit anyone to access databases with names that start with test_.

mysql_secure_installation helps you implement security recommendations similar to those described at Section 2.10.4, “Securing the Initial MySQL Accounts”.

Invoke mysql_secure_installation without arguments:

shell> mysql_secure_installation

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

4.4.6 mysql_tzinfo_to_sql — Load the Time Zone Tables

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

mysql_tzinfo_to_sql can be invoked several ways:

shell> mysql_tzinfo_to_sql tz_dir
shell> mysql_tzinfo_to_sql tz_file tz_name
shell> mysql_tzinfo_to_sql --leap tz_file

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

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

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

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

shell> mysql_tzinfo_to_sql tz_file tz_name | mysql -u root mysql

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

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

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

4.4.7 mysql_upgrade — Check and Upgrade MySQL Tables

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

If mysql_upgrade finds that a table has a possible incompatibility, it performs a table check and, if problems are found, attempts a table repair. If the table cannot be repaired, see Section 2.11.3, “Rebuilding or Repairing Tables or Indexes” for manual table repair strategies.

You should execute mysql_upgrade each time you upgrade MySQL.

If you install MySQL from RPM packages on Linux, you must install the server and client RPMs. mysql_upgrade is included in the server RPM but requires the client RPM because the latter includes mysqlcheck. (See Section 2.5.5, “Installing MySQL on Linux Using RPM Packages from Oracle”.)

Note

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

Caution

You should always back up your current MySQL installation before performing an upgrade. See Section 7.2, “Database Backup Methods”.

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

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

shell> mysql_upgrade [options]

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

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

shell> mysql_upgrade --protocol=tcp -P 3306 [other_options]
shell> mysql_upgrade --protocol=tcp -P 3307 [other_options]
shell> mysql_upgrade --protocol=tcp -P 3308 [other_options]

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

mysql_upgrade executes the following commands to check and repair tables and to upgrade the system tables:

mysqlcheck --no-defaults --databases
 --fix-db-names --fix-table-names mysql
mysqlcheck --no-defaults --check-upgrade --databases
 --auto-repair mysql
mysql < fix_priv_tables
mysqlcheck --no-defaults --all-databases
 --skip-database=mysql --fix-db-names --fix-table-names
mysqlcheck --no-defaults --check-upgrade --all-databases
 --skip-database=mysql --auto-repair

Notes about the preceding commands:

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

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

mysql_upgrade does not upgrade the contents of the help tables. For upgrade instructions, see Section 5.1.13, “Server-Side Help”.

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

shell> mysql -u root -p
Enter password: ****  <- enter root password here
mysql> SET PASSWORD = PASSWORD('root-password');
mysql> quit

shell> mysql_upgrade [options]

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

Table 4.7 mysql_upgrade Options

Format Description Introduced
--basedir Not used
--character-sets-dir Directory where character sets are installed
--compress Compress all information sent between client and server
--datadir Not used
--debug Write debugging log
--debug-check Print debugging information when program exits
--debug-info Print debugging information, memory, and CPU statistics when program exits
--default-auth Authentication plugin to use 5.6.2
--default-character-set Specify default character set
--defaults-extra-file Read named option file in addition to usual option files
--defaults-file Read only named option file
--defaults-group-suffix Option group suffix value
--force Force execution even if mysql_upgrade has already been executed for current version of MySQL
--help Display help message and exit
--host Connect to MySQL server on given host
--login-path Read login path options from .mylogin.cnf 5.6.6
--no-defaults Read no option files
--password Password to use when connecting to server
--pipe On Windows, connect to server using named pipe
--plugin-dir Directory where plugins are installed 5.6.2
--port TCP/IP port number for connection
--print-defaults Print default options
--protocol Connection protocol to use
--shared-memory-base-name The name of shared memory to use for shared-memory connections
--socket For connections to localhost, the Unix socket file to use
--ssl Enable encrypted connection
--ssl-ca File that contains list of trusted SSL Certificate Authorities
--ssl-capath Directory that contains trusted SSL Certificate Authority certificate files
--ssl-cert File that contains X.509 certificate
--ssl-cipher List of permitted ciphers for connection encryption
--ssl-crl File that contains certificate revocation lists 5.6.3
--ssl-crlpath Directory that contains certificate revocation list files 5.6.3
--ssl-key File that contains X.509 key
--ssl-mode Security state of connection to server 5.6.30
--ssl-verify-server-cert Verify host name against server certificate Common Name identity
--tmpdir Directory for temporary files
--upgrade-system-tables Update only system tables, not data
--user MySQL user name to use when connecting to server
--verbose Verbose mode
--version-check Check for proper server version 5.6.12
--write-binlog Write all statements to binary log

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

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

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

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

shell> mysql db_name

Or:

shell> mysql --user=user_name --password db_name
Enter password: your_password

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

Typing Control+C causes mysql to attempt to kill the current statement. If this cannot be done, or Control+C is typed again before the statement is killed, mysql exits.

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

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

On Unix, the mysql client logs statements executed interactively to a history file. See Section 4.5.1.3, “mysql Logging”.

4.5.1.1 mysql Options

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

Table 4.8 mysql Options

Format Description Introduced
--auto-rehash Enable automatic rehashing
--auto-vertical-output Enable automatic vertical result set display
--batch Do not use history file
--binary-as-hex Display binary values in hexadecimal notation 5.6.37
--binary-mode Disable \r\n - to - \n translation and treatment of \0 as end-of-query 5.6.3
--bind-address Use specified network interface to connect to MySQL Server 5.6.1
--character-sets-dir Directory where character sets are installed
--column-names Write column names in results
--column-type-info Display result set metadata
--comments Whether to retain or strip comments in statements sent to the server
--compress Compress all information sent between client and server
--connect-expired-password Indicate to server that client can handle expired-password sandbox mode. 5.6.12
--connect_timeout Number of seconds before connection timeout
--database The database to use
--debug Write debugging log; supported only if MySQL was built with debugging support
--debug-check Print debugging information when program exits
--debug-info Print debugging information, memory, and CPU statistics when program exits
--default-auth Authentication plugin to use
--default-character-set Specify default character set
--defaults-extra-file Read named option file in addition to usual option files
--defaults-file Read only named option file
--defaults-group-suffix Option group suffix value
--delimiter Set the statement delimiter
--enable-cleartext-plugin Enable cleartext authentication plugin 5.6.7
--execute Execute the statement and quit
--force Continue even if an SQL error occurs
--help Display help message and exit
--histignore Patterns specifying which statements to ignore for logging 5.6.8
--host Connect to MySQL server on given host
--html Produce HTML output
--ignore-spaces Ignore spaces after function names
--init-command SQL statement to execute after connecting
--line-numbers Write line numbers for errors
--local-infile Enable or disable for LOCAL capability for LOAD DATA INFILE
--login-path Read login path options from .mylogin.cnf 5.6.6
--max_allowed_packet Maximum packet length to send to or receive from server
--max_join_size The automatic limit for rows in a join when using --safe-updates
--named-commands Enable named mysql commands
--net_buffer_length Buffer size for TCP/IP and socket communication
--no-auto-rehash Disable automatic rehashing
--no-beep Do not beep when errors occur
--no-defaults Read no option files
--one-database Ignore statements except those for the default database named on the command line
--pager Use the given command for paging query output
--password Password to use when connecting to server
--pipe On Windows, connect to server using named pipe
--plugin-dir Directory where plugins are installed
--port TCP/IP port number for connection
--print-defaults Print default options
--prompt Set the prompt to the specified format
--protocol Connection protocol to use
--quick Do not cache each query result
--raw Write column values without escape conversion
--reconnect If the connection to the server is lost, automatically try to reconnect
--i-am-a-dummy, --safe-updates Allow only UPDATE and DELETE statements that specify key values
--secure-auth Do not send passwords to server in old (pre-4.1) format
--select_limit The automatic limit for SELECT statements when using --safe-updates
--server-public-key-path Path name to file containing RSA public key 5.6.6
--shared-memory-base-name The name of shared memory to use for shared-memory connections
--show-warnings Show warnings after each statement if there are any
--sigint-ignore Ignore SIGINT signals (typically the result of typing Control+C)
--silent Silent mode
--skip-auto-rehash Disable automatic rehashing
--skip-column-names Do not write column names in results
--skip-line-numbers Skip line numbers for errors
--skip-named-commands Disable named mysql commands
--skip-pager Disable paging
--skip-reconnect Disable reconnecting
--socket For connections to localhost, the Unix socket file or Windows named pipe to use
--ssl Enable encrypted connection
--ssl-ca File that contains list of trusted SSL Certificate Authorities
--ssl-capath Directory that contains trusted SSL Certificate Authority certificate files
--ssl-cert File that contains X.509 certificate
--ssl-cipher List of permitted ciphers for connection encryption
--ssl-crl File that contains certificate revocation lists 5.6.3
--ssl-crlpath Directory that contains certificate revocation list files 5.6.3
--ssl-key File that contains X.509 key
--ssl-mode Security state of connection to server 5.6.30
--ssl-verify-server-cert Verify host name against server certificate Common Name identity
--table Display output in tabular format
--tee Append a copy of output to named file
--unbuffered Flush the buffer after each query
--user MySQL user name to use when connecting to server
--verbose Verbose mode
--version Display version information and exit
--vertical Print query output rows vertically (one line per column value)
--wait If the connection cannot be established, wait and retry instead of aborting
--xml Produce XML output

  • --help, -?

    Display a help message and exit.

  • --auto-rehash

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

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

    Note

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

  • --auto-vertical-output

    Cause result sets to be displayed vertically if they are too wide for the current window, and using normal tabular format otherwise. (This applies to statements terminated by ; or \G.)

  • --batch, -B

    Print results using tab as the column separator, with each row on a new line. With this option, mysql does not use the history file.

    Batch mode results in nontabular output format and escaping of special characters. Escaping may be disabled by using raw mode; see the description for the --raw option.

  • --binary-as-hex

    When this option is given, mysql displays binary data using hexadecimal notation (0xvalue). This occurs whether the overall output dislay format is tabular, vertical, HTML, or XML.

    This option was added in MySQL 5.6.37.

  • --binary-mode

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

    This option was added in MySQL 5.6.3.

  • --bind-address=ip_address

    On a computer having multiple network interfaces, use this option to select which interface to use for connecting to the MySQL server.

    This option is supported beginning with MySQL 5.6.1.

  • --character-sets-dir=dir_name

    The directory where character sets are installed. See Section 10.14, “Character Set Configuration”.

  • --column-names

    Write column names in results.

  • --column-type-info

    Display result set metadata.

  • --comments, -c

    Whether to strip or preserve comments in statements sent to the server. The default is --skip-comments (strip comments), enable with --comments (preserve comments).

  • --compress, -C

    Compress all information sent between the client and the server if both support compression.

  • --connect-expired-password

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

  • --database=db_name, -D db_name

    The database to use. This is useful primarily in an option file.

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

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

    This option is available only if MySQL was built using WITH_DEBUG. MySQL release binaries provided by Oracle are not built using this option.

  • --debug-check

    Print some debugging information when the program exits.

  • --debug-info, -T

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

  • --default-auth=plugin

    A hint about the client-side authentication plugin to use. See Section 6.3.7, “Pluggable Authentication”.

  • --default-character-set=charset_name

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

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

    For more information, see Section 10.4, “Connection Character Sets and Collations”, and Section 10.14, “Character Set Configuration”.

  • --defaults-extra-file=file_name

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

    For additional information about this and other option-file options, see Section 4.2.7, “Command-Line Options that Affect Option-File Handling”.

  • --defaults-file=file_name

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

    Exception: Even with --defaults-file, client programs read .mylogin.cnf.

    For additional information about this and other option-file options, see Section 4.2.7, “Command-Line Options that Affect Option-File Handling”.

  • --defaults-group-suffix=str

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

    For additional information about this and other option-file options, see Section 4.2.7, “Command-Line Options that Affect Option-File Handling”.

  • --delimiter=str

    Set the statement delimiter. The default is the semicolon character (;).

  • --disable-named-commands

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

  • --enable-cleartext-plugin

    Enable the mysql_clear_password cleartext authentication plugin. (See Section 6.5.1.5, “Client-Side Cleartext Pluggable Authentication”.) This option was added in MySQL 5.6.7.

  • --execute=statement, -e statement

    Execute the statement and quit. The default output format is like that produced with --batch. See Section 4.2.4, “Using Options on the Command Line”, for some examples. With this option, mysql does not use the history file.

  • --force, -f

    Continue even if an SQL error occurs.

  • --histignore

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

  • --host=host_name, -h host_name

    Connect to the MySQL server on the given host.

  • --html, -H

    Produce HTML output.

  • --ignore-spaces, -i

    Ignore spaces after function names. The effect of this is described in the discussion for the IGNORE_SPACE SQL mode (see Section 5.1.10, “Server SQL Modes”).

  • --init-command=str

    SQL statement to execute after connecting to the server. If auto-reconnect is enabled, the statement is executed again after reconnection occurs.

  • --line-numbers

    Write line numbers for errors. Disable this with --skip-line-numbers.

  • --local-infile[={0|1}]

    Enable or disable LOCAL capability for LOAD DATA INFILE. For mysql, this capability is disabled by default. With no value, the option enables LOCAL. The option may be given as --local-infile=0 or --local-infile=1 to explicitly disable or enable LOCAL. Enabling local data loading also requires that the server permits it; see Section 6.1.6, “Security Issues with LOAD DATA LOCAL”

  • --login-path=name

    Read options from the named login path in the .mylogin.cnf login path file. A login path is an option group containing options that specify which MySQL server to connect to and which account to authenticate as. To create or modify a login path file, use the mysql_config_editor utility. See Section 4.6.6, “mysql_config_editor — MySQL Configuration Utility”.

    For additional information about this and other option-file options, see Section 4.2.7, “Command-Line Options that Affect Option-File Handling”.

  • --named-commands, -G

    Enable named mysql commands. Long-format commands are permitted, not just short-format commands. For example, quit and \q both are recognized. Use --skip-named-commands to disable named commands. See Section 4.5.1.2, “mysql Commands”.

  • --no-auto-rehash, -A

    This has the same effect as --skip-auto-rehash. See the description for --auto-rehash.

  • --no-beep, -b

    Do not beep when errors occur.

  • --no-defaults

    Do not read any option files. If program startup fails due to reading unknown options from an option file, --no-defaults can be used to prevent them from being read.

    The exception is that the .mylogin.cnf file, if it exists, is read in all cases. This permits passwords to be specified in a safer way than on the command line even when --no-defaults is used. (.mylogin.cnf is created by the mysql_config_editor utility. See Section 4.6.6, “mysql_config_editor — MySQL Configuration Utility”.)

    For additional information about this and other option-file options, see Section 4.2.7, “Command-Line Options that Affect Option-File Handling”.

  • --one-database, -o

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

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

    Suppose that mysql is invoked to process this set of statements:

    DELETE FROM db2.t2;
    USE db2;
    DROP TABLE db1.t1;
    CREATE TABLE db1.t1 (i INT);
    USE db1;
    INSERT INTO t1 (i) VALUES(1);
    CREATE TABLE db2.t1 (j INT);
    

    If the command line is mysql --force --one-database db1, mysql handles the input as follows:

    • The DELETE statement is executed because the default database is db1, even though the statement names a table in a different database.

    • The DROP TABLE and CREATE TABLE statements are not executed because the default database is not db1, even though the statements name a table in db1.

    • The INSERT and CREATE TABLE statements are executed because the default database is db1, even though the CREATE TABLE statement names a table in a different database.

  • --pager[=command]

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

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

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

    Specifying a password on the command line should be considered insecure. See Section 6.1.2.1, “End-User Guidelines for Password Security”. You can use an option file to avoid giving the password on the command line.

  • --pipe, -W

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

  • --plugin-dir=dir_name

    The directory in which to look for plugins. Specify this option if the --default-auth option is used to specify an authentication plugin but mysql does not find it. See Section 6.3.7, “Pluggable Authentication”.

  • --port=port_num, -P port_num

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

  • --print-defaults

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

    For additional information about this and other option-file options, see Section 4.2.7, “Command-Line Options that Affect Option-File Handling”.

  • --prompt=format_str

    Set the prompt to the specified format. The default is mysql>. The special sequences that the prompt can contain are described in Section 4.5.1.2, “mysql Commands”.

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

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

  • --quick, -q

    Do not cache each query result, print each row as it is received. This may slow down the server if the output is suspended. With this option, mysql does not use the history file.

  • --raw, -r

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

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

    % mysql
    mysql> SELECT CHAR(92);
    +----------+
    | CHAR(92) |
    +----------+
    | \        |
    +----------+
    
    % mysql -s
    mysql> SELECT CHAR(92);
    CHAR(92)
    \\
    
    % mysql -s -r
    mysql> SELECT CHAR(92);
    CHAR(92)
    \
    
  • --reconnect

    If the connection to the server is lost, automatically try to reconnect. A single reconnect attempt is made each time the connection is lost. To suppress reconnection behavior, use --skip-reconnect.

  • --safe-updates, --i-am-a-dummy, -U

    If this option is enabled, UPDATE and DELETE statements that do not use a key in the WHERE clause or a LIMIT clause produce an error. In addition, restrictions are placed on SELECT statements that produce (or are estimated to produce) very large result sets. If you have set this option in an option file, you can use --skip-safe-updates on the command line to override it. For more information about this option, see Section 4.5.1.6.4, “Using Safe-Updates Mode (--safe-updates)”.

  • --secure-auth

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

    Note

    Passwords that use the pre-4.1 hashing method are less secure than passwords that use the native password hashing method and should be avoided. Pre-4.1 passwords are deprecated and support for them will be removed in a future MySQL release. For account upgrade instructions, see Section 6.5.1.3, “Migrating Away from Pre-4.1 Password Hashing and the mysql_old_password Plugin”.

    Note

    This option is deprecated and will be removed in a future release. As of MySQL 5.7.5, it is always enabled and attempting to disable it produces an error.

  • --server-public-key-path=file_name

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

    This option is available only if MySQL was built using OpenSSL.

    For information about the sha256_password plugin, see Section 6.5.1.4, “SHA-256 Pluggable Authentication”.

  • --shared-memory-base-name=name

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

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

  • --show-warnings

    Cause warnings to be shown after each statement if there are any. This option applies to interactive and batch mode.

  • --sigint-ignore

    Ignore SIGINT signals (typically the result of typing Control+C).

  • --silent, -s

    Silent mode. Produce less output. This option can be given multiple times to produce less and less output.

    This option results in nontabular output format and escaping of special characters. Escaping may be disabled by using raw mode; see the description for the --raw option.

  • --skip-column-names, -N

    Do not write column names in results.

  • --skip-line-numbers, -L

    Do not write line numbers for errors. Useful when you want to compare result files that include error messages.

  • --socket=path, -S path

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

  • --ssl*

    Options that begin with --ssl specify whether to connect to the server using SSL and indicate where to find SSL keys and certificates. See Section 6.4.2, “Command Options for Encrypted Connections”.

  • --table, -t

    Display output in table format. This is the default for interactive use, but can be used to produce table output in batch mode.

  • --tee=file_name

    Append a copy of output to the given file. This option works only in interactive mode. Section 4.5.1.2, “mysql Commands”, discusses tee files further.

  • --unbuffered, -n

    Flush the buffer after each query.

  • --user=user_name, -u user_name

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

  • --verbose, -v

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

  • --version, -V

    Display version information and exit.

  • --vertical, -E

    Print query output rows vertically (one line per column value). Without this option, you can specify vertical output for individual statements by terminating them with \G.

  • --wait, -w

    If the connection cannot be established, wait and retry instead of aborting.

  • --xml, -X

    Produce XML output.

    <field name="column_name">NULL</field>
    

    The output when --xml is used with mysql matches that of mysqldump --xml. See Section 4.5.4, “mysqldump — A Database Backup Program”, for details.

    The XML output also uses an XML namespace, as shown here:

    shell> mysql --xml -uroot -e "SHOW VARIABLES LIKE 'version%'"
    <?xml version="1.0"?>
    
    <resultset statement="SHOW VARIABLES LIKE 'version%'" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
    <row>
    <field name="Variable_name">version</field>
    <field name="Value">5.0.40-debug</field>
    </row>
    
    <row>
    <field name="Variable_name">version_comment</field>
    <field name="Value">Source distribution</field>
    </row>
    
    <row>
    <field name="Variable_name">version_compile_machine</field>
    <field name="Value">i686</field>
    </row>
    
    <row>
    <field name="Variable_name">version_compile_os</field>
    <field name="Value">suse-linux-gnu</field>
    </row>
    </resultset>
    

    (See Bug #25946.)

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

  • connect_timeout

    The number of seconds before connection timeout. (Default value is 0.)

  • max_allowed_packet

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

  • max_join_size

    The automatic limit for rows in a join when using --safe-updates. (Default value is 1,000,000.)

  • net_buffer_length

    The buffer size for TCP/IP and socket communication. (Default value is 16KB.)

  • select_limit

    The automatic limit for SELECT statements when using --safe-updates. (Default value is 1,000.)

4.5.1.2 mysql Commands

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

mysql> help

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

For server side help, type 'help contents'

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

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

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

  • help [arg], \h [arg], \? [arg], ? [arg]

    Display a help message listing the available mysql commands.

    If you provide an argument to the help command, mysql uses it as a search string to access server-side help from the contents of the MySQL Reference Manual. For more information, see Section 4.5.1.4, “mysql Server-Side Help”.

  • charset charset_name, \C charset_name

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

  • clear, \c

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

  • connect [db_name host_name]], \r [db_name host_name]]

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

  • delimiter str, \d str

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

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

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

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

  • edit, \e

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

    The edit command works only in Unix.

  • ego, \G

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

  • exit, \q

    Exit mysql.

  • go, \g

    Send the current statement to the server to be executed.

  • nopager, \n

    Disable output paging. See the description for pager.

    The nopager command works only in Unix.

  • notee, \t

    Disable output copying to the tee file. See the description for tee.

  • nowarning, \w

    Disable display of warnings after each statement.

  • pager [command], \P [command]

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

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

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

  • print, \p

    Print the current input statement without executing it.

  • prompt [str], \R [str]

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

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

  • quit, \q

    Exit mysql.

  • rehash, \#

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

  • source file_name, \. file_name

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

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

  • status, \s

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

  • system command, \! command

    Execute the given command using your default command interpreter.

    The system command works only in Unix.

  • tee [file_name], \T [file_name]

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

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

  • use db_name, \u db_name

    Use db_name as the default database.

  • warnings, \W

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

Here are a few tips about the pager command:

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

    mysql> pager cat > /tmp/log.txt
    

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

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

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

    mysql> pager less -n -i -S -F -X
    
  • You can specify very complex pager commands for handling query output:

    mysql> pager cat | tee /dr1/tmp/res.txt \
              | tee /dr2/tmp/res2.txt | less -n -i -S
    

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

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

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

Option Description
\c A counter that increments for each statement you issue
\D The full current date
\d The default database
\h The server host
\l The current delimiter
\m Minutes of the current time
\n A newline character
\O The current month in three-letter format (Jan, Feb, …)
\o The current month in numeric format
\P am/pm
\p The current TCP/IP port or socket file
\R The current time, in 24-hour military time (0–23)
\r The current time, standard 12-hour time (1–12)
\S Semicolon
\s Seconds of the current time
\t A tab character
\U

Your full user_name@host_name account name

\u Your user name
\v The server version
\w The current day of the week in three-letter format (Mon, Tue, …)
\Y The current year, four digits
\y The current year, two digits
\_ A space
A space (a space follows the backslash)
\' Single quote
\" Double quote
\\ A literal \ backslash character
\x

x, for any x not listed above

You can set the prompt in several ways:

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

    shell> export MYSQL_PS1="(\u@\h) [\d]> "
    
  • Use a command-line option. You can set the --prompt option on the command line to mysql. For example:

    shell> mysql --prompt="(\u@\h) [\d]> "
    (user@host) [database]>
    
  • Use an option file. You can set the prompt option in the [mysql] group of any MySQL option file, such as /etc/my.cnf or the .my.cnf file in your home directory. For example:

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

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

    [mysql]
    prompt="\\r:\\m:\\s> "
    
  • Set the prompt interactively. You can change your prompt interactively by using the prompt (or \R) command. For example:

    mysql> prompt (\u@\h) [\d]>\_
    PROMPT set to '(\u@\h) [\d]>\_'
    (user@host) [database]>
    (user@host) [database]> prompt
    Returning to default PROMPT of mysql>
    mysql>
    

4.5.1.3 mysql Logging

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

How Logging Occurs

Statement logging occurs as follows:

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

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

  • mysql logs each nonignored, nonempty statement line individually.

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

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

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

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

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

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

  • ? matches any single character.

  • * matches any sequence of zero or more characters.

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

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

shell> mysql --histignore="*UPDATE*:*DELETE*"
Controlling the History File

The .mysql_history file should be protected with a restrictive access mode because sensitive information might be written to it, such as the text of SQL statements that contain passwords. See Section 6.1.2.1, “End-User Guidelines for Password Security”.

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

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

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

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

4.5.1.4 mysql Server-Side Help

mysql> help search_string

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

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

mysql> help me

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

Use help contents to see a list of the help categories:

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

If the search string matches multiple items, mysql shows a list of matching topics:

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

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

mysql> help show binary logs
Name: 'SHOW BINARY LOGS'
Description:
Syntax:
SHOW BINARY LOGS
SHOW MASTER LOGS

Lists the binary log files on the server. This statement is used as
part of the procedure described in [purge-binary-logs], that shows how
to determine which logs can be purged.

mysql> SHOW BINARY LOGS;
+---------------+-----------+
| Log_name      | File_size |
+---------------+-----------+
| binlog.000015 |    724935 |
| binlog.000016 |    733481 |
+---------------+-----------+

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

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

4.5.1.5 Executing SQL Statements from a Text File

The mysql client typically is used interactively, like this:

shell> mysql db_name

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

shell> mysql db_name < text_file

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

shell> mysql < text_file

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

mysql> source file_name
mysql> \. file_name

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

SELECT '<info_to_display>' AS ' ';

The statement shown outputs <info_to_display>.

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

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

For more information about batch mode, see Section 3.5, “Using mysql in Batch Mode”.

4.5.1.6 mysql Tips

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

4.5.1.6.1 Input-Line Editing

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

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

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

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

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

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

4.5.1.6.2 Unicode Support on Windows

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

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

  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 mysql.exe with the --default-character-set=utf8 (or utf8mb4) option. This option is necessary because utf16le is one of the character sets that cannot be used as the client character set. See Impermissible Client Character Sets.

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

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

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

[mysql]
default-character-set=utf8
4.5.1.6.3 Displaying Query Results Vertically

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

mysql> SELECT * FROM mails WHERE LENGTH(txt) < 300 LIMIT 300,1\G
*************************** 1. row ***************************
  msg_nro: 3068
     date: 2000-03-01 23:29:50
time_zone: +0200
mail_from: Monty
    reply: monty@no.spam.com
  mail_to: "Thimble Smith" <tim@no.spam.com>
      sbj: UTF-8
      txt: >>>>> "Thimble" == Thimble Smith writes:

Thimble> Hi.  I think this is a good idea.  Is anyone familiar
Thimble> with UTF-8 or Unicode? Otherwise, I'll put this on my
Thimble> TODO list and see what happens.

Yes, please do that.

Regards,
Monty
     file: inbox-jani-1
     hash: 190402944
1 row in set (0.09 sec)
4.5.1.6.4 Using Safe-Updates Mode (--safe-updates)

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

The --safe-updates option causes mysql to execute the following statement when it connects to the MySQL server, to set the session values of the sql_safe_updates, sql_select_limit, and max_join_size system variables:

SET sql_safe_updates=1, sql_select_limit=1000, max_join_size=1000000;

The SET statement affects statement processing as follows:

  • Enabling sql_safe_updates causes UPDATE and DELETE statements to produce an error if they do not specify a key constraint in the WHERE clause, or provide a LIMIT clause, or both. For example:

    UPDATE tbl_name SET not_key_column=val WHERE key_column=val;
    
    UPDATE tbl_name SET not_key_column=val LIMIT 1;
    
  • Setting sql_select_limit to 1,000 causes the server to limit all SELECT result sets to 1,000 rows unless the statement includes a LIMIT clause.

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

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

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

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

4.5.1.6.5 Disabling mysql Auto-Reconnect

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

mysql> SET @a=1;
Query OK, 0 rows affected (0.05 sec)

mysql> INSERT INTO t VALUES(@a);
ERROR 2006: MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    1
Current database: test

Query OK, 1 row affected (1.30 sec)

mysql> SELECT * FROM t;
+------+
| a    |
+------+
| NULL |
+------+
1 row in set (0.05 sec)

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

For more information about auto-reconnect and its effect on state information when a reconnection occurs, see Section 23.8.20, “C API Automatic Reconnection Control”.

4.5.2 mysqladmin — Client for Administering a MySQL Server

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

Invoke mysqladmin like this:

shell> mysqladmin [options] command [command-arg] [command [command-arg]] ...

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

  • create db_name

    Create a new database named db_name.

  • debug

    Tell the server to write debug information to the error log. The connected user must have the SUPER privilege. Format and content of this information is subject to change.

    This includes information about the Event Scheduler. See Section 20.4.5, “Event Scheduler Status”.

  • drop db_name

    Delete the database named db_name and all its tables.

  • extended-status

    Display the server status variables and their values.

  • flush-hosts

    Flush all information in the host cache. See Section 8.12.5.2, “DNS Lookup Optimization and the Host Cache”.

  • flush-logs

    Flush all logs.

  • flush-privileges

    Reload the grant tables (same as reload).

  • flush-status

    Clear status variables.

  • flush-tables

    Flush all tables.

  • flush-threads

    Flush the thread cache.

  • kill id,id,...

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

    To kill threads belonging to other users, the connected user must have the SUPER privilege.

  • old-password new_password

    This is like the password command but stores the password using the old (pre-4.1) password-hashing format. (See Section 6.1.2.4, “Password Hashing in MySQL”.)

  • password new_password

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

    Warning

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

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

    shell> mysqladmin password "my new password"
    

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

    Caution

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

  • ping

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

  • processlist

    Show a list of active server threads. This is like the output of the SHOW PROCESSLIST statement. If the --verbose option is given, the output is like that of SHOW FULL PROCESSLIST. (See Section 13.7.5.30, “SHOW PROCESSLIST Syntax”.)

  • reload

    Reload the grant tables.

  • refresh

    Flush all tables and close and open log files.

  • shutdown

    Stop the server.

  • start-slave

    Start replication on a slave server.

  • status

    Display a short server status message.

  • stop-slave

    Stop replication on a slave server.

  • variables

    Display the server system variables and their values.

  • version

    Display version information from the server.

All commands can be shortened to any unique prefix. For example:

shell> mysqladmin proc stat
+----+-------+-----------+----+---------+------+-------+------------------+
| Id | User  | Host      | db | Command | Time | State | Info             |
+----+-------+-----------+----+---------+------+-------+------------------+
| 51 | monty | localhost |    | Query   | 0    |       | show processlist |
+----+-------+-----------+----+---------+------+-------+------------------+
Uptime: 1473624  Threads: 1  Questions: 39487
Slow queries: 0  Opens: 541  Flush tables: 1
Open tables: 19  Queries per second avg: 0.0268

The mysqladmin status command result displays the following values:

  • Uptime

    The number of seconds the MySQL server has been running.

  • Threads

    The number of active threads (clients).

  • Questions

    The number of questions (queries) from clients since the server was started.

  • Slow queries

    The number of queries that have taken more than long_query_time seconds. See Section 5.4.5, “The Slow Query Log”.

  • Opens

    The number of tables the server has opened.

  • Flush tables

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

  • Open tables

    The number of tables that currently are open.

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

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

Table 4.9 mysqladmin Options

Format Description Introduced
--bind-address Use specified network interface to connect to MySQL Server 5.6.1
--compress Compress all information sent between client and server
--connect_timeout Number of seconds before connection timeout
--count Number of iterations to make for repeated command execution
--debug Write debugging log
--debug-check Print debugging information when program exits
--debug-info Print debugging information, memory, and CPU statistics when program exits
--default-auth Authentication plugin to use
--default-character-set Specify default character set
--defaults-extra-file Read named option file in addition to usual option files
--defaults-file Read only named option file
--defaults-group-suffix Option group suffix value
--enable-cleartext-plugin Enable cleartext authentication plugin 5.6.7
--force Continue even if an SQL error occurs
--help Display help message and exit
--host Connect to MySQL server on given host
--login-path Read login path options from .mylogin.cnf 5.6.6
--no-beep Do not beep when errors occur
--no-defaults Read no option files
--password Password to use when connecting to server
--pipe On Windows, connect to server using named pipe
--plugin-dir Directory where plugins are installed
--port TCP/IP port number for connection
--print-defaults Print default options
--protocol Connection protocol to use
--relative Show the difference between the current and previous values when used with the --sleep option
--secure-auth Do not send passwords to server in old (pre-4.1) format 5.6.17
--shared-memory-base-name The name of shared memory to use for shared-memory connections
--shutdown_timeout The maximum number of seconds to wait for server shutdown
--silent Silent mode
--sleep Execute commands repeatedly, sleeping for delay seconds in between
--socket For connections to localhost, the Unix socket file to use
--ssl Enable encrypted connection
--ssl-ca File that contains list of trusted SSL Certificate Authorities
--ssl-capath Directory that contains trusted SSL Certificate Authority certificate files
--ssl-cert File that contains X.509 certificate
--ssl-cipher List of permitted ciphers for connection encryption
--ssl-crl File that contains certificate revocation lists 5.6.3
--ssl-crlpath Directory that contains certificate revocation list files 5.6.3
--ssl-key File that contains X.509 key
--ssl-mode Security state of connection to server 5.6.30
--ssl-verify-server-cert Verify host name against server certificate Common Name identity
--user MySQL user name to use when connecting to server
--verbose Verbose mode
--version Display version information and exit
--vertical Print query output rows vertically (one line per column value)
--wait If the connection cannot be established, wait and retry instead of aborting

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

  • connect_timeout

    The maximum number of seconds before connection timeout. The default value is 43200 (12 hours).

  • shutdown_timeout

    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 mysqlcheck client performs table maintenance: It checks, repairs, optimizes, or analyzes tables.

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

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

mysqlcheck uses the SQL statements CHECK TABLE, REPAIR TABLE, ANALYZE TABLE, and OPTIMIZE TABLE in a convenient way for the user. It determines which statements to use for the operation you want to perform, and then sends the statements to the server to be executed. For details about which storage engines each statement works with, see the descriptions for those statements in Section 13.7.2, “Table Maintenance Statements”.

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

shell> mysqlcheck test t
test.t
note     : The storage engine for the table doesn't support check

If mysqlcheck is unable to repair a table, see Section 2.11.3, “Rebuilding or Repairing Tables or Indexes” for manual table repair strategies. This will be the case, for example, for InnoDB tables, which can be checked with CHECK TABLE, but not repaired with REPAIR TABLE.

Caution

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

There are three general ways to invoke mysqlcheck:

shell> mysqlcheck [options] db_name [tbl_name ...]
shell> mysqlcheck [options] --databases db_name ...
shell> mysqlcheck [options] --all-databases

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

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

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

Command Meaning
mysqlrepair The default option is --repair
mysqlanalyze The default option is --analyze
mysqloptimize The default option is --optimize

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

Table 4.10 mysqlcheck Options

Format Description Introduced
--all-databases Check all tables in all databases
--all-in-1 Execute a single statement for each database that names all the tables from that database
--analyze Analyze the tables
--auto-repair If a checked table is corrupted, automatically fix it
--bind-address Use specified network interface to connect to MySQL Server 5.6.1
--character-sets-dir Directory where character sets are installed
--check Check the tables for errors
--check-only-changed Check only tables that have changed since the last check
--check-upgrade Invoke CHECK TABLE with the FOR UPGRADE option
--compress Compress all information sent between client and server
--databases Interpret all arguments as database names
--debug Write debugging log
--debug-check Print debugging information when program exits
--debug-info Print debugging information, memory, and CPU statistics when program exits
--default-auth Authentication plugin to use 5.6.2
--default-character-set Specify default character set
--defaults-extra-file Read named option file in addition to usual option files
--defaults-file Read only named option file
--defaults-group-suffix Option group suffix value
--enable-cleartext-plugin Enable cleartext authentication plugin 5.6.28
--extended Check and repair tables
--fast Check only tables that have not been closed properly
--fix-db-names Convert database names to 5.1 format
--fix-table-names Convert table names to 5.1 format
--force Continue even if an SQL error occurs
--help Display help message and exit
--host Connect to MySQL server on given host
--login-path Read login path options from .mylogin.cnf 5.6.6
--medium-check Do a check that is faster than an --extended operation
--no-defaults Read no option files
--optimize Optimize the tables
--password Password to use when connecting to server
--pipe On Windows, connect to server using named pipe
--plugin-dir Directory where plugins are installed 5.6.2
--port TCP/IP port number for connection
--print-defaults Print default options
--protocol Connection protocol to use
--quick The fastest method of checking
--repair Perform a repair that can fix almost anything except unique keys that are not unique
--secure-auth Do not send passwords to server in old (pre-4.1) format 5.6.17
--shared-memory-base-name The name of shared memory to use for shared-memory connections
--silent Silent mode
--skip-database Omit this database from performed operations 5.6.11
--socket For connections to localhost, the Unix socket file to use
--ssl Enable encrypted connection
--ssl-ca File that contains list of trusted SSL Certificate Authorities
--ssl-capath Directory that contains trusted SSL Certificate Authority certificate files
--ssl-cert File that contains X.509 certificate
--ssl-cipher List of permitted ciphers for connection encryption
--ssl-crl File that contains certificate revocation lists 5.6.3
--ssl-crlpath Directory that contains certificate revocation list files 5.6.3
--ssl-key File that contains X.509 key
--ssl-mode Security state of connection to server 5.6.30
--ssl-verify-server-cert Verify host name against server certificate Common Name identity
--tables Overrides the --databases or -B option
--use-frm For repair operations on MyISAM tables
--user MySQL user name to use when connecting to server
--verbose Verbose mode
--version Display version information and exit
--write-binlog Log ANALYZE, OPTIMIZE, REPAIR statements to binary log. --skip-write-binlog adds NO_WRITE_TO_BINLOG to these statements.

4.5.4 mysqldump — A Database Backup Program

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

mysqldump requires at least the SELECT privilege for dumped tables, SHOW VIEW for dumped views, TRIGGER for dumped triggers, and LOCK TABLES if the --single-transaction option is not used. Certain options might require other privileges as noted in the option descriptions.

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

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

Note

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

shell> mysqldump [options] > dump.sql

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

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

Performance and Scalability Considerations

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

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

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

  • If your tables are primarily MyISAM tables, consider using the mysqlhotcopy instead, for better performance than mysqldump of backup and restore operations. See Section 4.6.10, “mysqlhotcopy — A Database Backup Program”.

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

If you are using a recent version of mysqldump to generate a dump to be reloaded into a very old MySQL server, use the --skip-opt option instead of the --opt or --extended-insert option.

For additional information about mysqldump, see Section 7.4, “Using mysqldump for Backups”.

Invocation Syntax

There are in general three ways to use mysqldump—in order to dump a set of one or more tables, a set of one or more complete databases, or an entire MySQL server—as shown here:

shell> mysqldump [options] db_name [tbl_name ...]
shell> mysqldump [options] --databases db_name ...
shell> mysqldump [options] --all-databases

To dump entire databases, do not name any tables following db_name, or use the --databases or --all-databases option.

To see a list of the options your version of mysqldump supports, issue the command mysqldump --help.

Option Syntax - Alphabetical Summary

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

Table 4.11 mysqldump Options

Format Description Introduced
--add-drop-database Add DROP DATABASE statement before each CREATE DATABASE statement
--add-drop-table Add DROP TABLE statement before each CREATE TABLE statement
--add-drop-trigger Add DROP TRIGGER statement before each CREATE TRIGGER statement
--add-locks Surround each table dump with LOCK TABLES and UNLOCK TABLES statements
--all-databases Dump all tables in all databases
--allow-keywords Allow creation of column names that are keywords
--apply-slave-statements Include STOP SLAVE prior to CHANGE MASTER statement and START SLAVE at end of output
--bind-address Use specified network interface to connect to MySQL Server 5.6.1
--character-sets-dir Directory where character sets are installed
--comments Add comments to dump file
--compact Produce more compact output
--compatible Produce output that is more compatible with other database systems or with older MySQL servers
--complete-insert Use complete INSERT statements that include column names
--compress Compress all information sent between client and server
--create-options Include all MySQL-specific table options in CREATE TABLE statements
--databases Interpret all name arguments as database names
--debug Write debugging log
--debug-check Print debugging information when program exits
--debug-info Print debugging information, memory, and CPU statistics when program exits
--default-auth Authentication plugin to use
--default-character-set Specify default character set
--defaults-extra-file Read named option file in addition to usual option files
--defaults-file Read only named option file
--defaults-group-suffix Option group suffix value
--delayed-insert Write INSERT DELAYED statements rather than INSERT statements
--delete-master-logs On a master replication server, delete the binary logs after performing the dump operation
--disable-keys For each table, surround INSERT statements with statements to disable and enable keys
--dump-date Include dump date as "Dump completed on" comment if --comments is given
--dump-slave Include CHANGE MASTER statement that lists binary log coordinates of slave's master
--enable-cleartext-plugin Enable cleartext authentication plugin 5.6.28
--events Dump events from dumped databases
--extended-insert Use multiple-row INSERT syntax
--fields-enclosed-by This option is used with the --tab option and has the same meaning as the corresponding clause for LOAD DATA INFILE
--fields-escaped-by This option is used with the --tab option and has the same meaning as the corresponding clause for LOAD DATA INFILE
--fields-optionally-enclosed-by This option is used with the --tab option and has the same meaning as the corresponding clause for LOAD DATA INFILE
--fields-terminated-by This option is used with the --tab option and has the same meaning as the corresponding clause for LOAD DATA INFILE
--flush-logs Flush MySQL server log files before starting dump
--flush-privileges Emit a FLUSH PRIVILEGES statement after dumping mysql database
--force Continue even if an SQL error occurs during a table dump
--help Display help message and exit
--hex-blob Dump binary columns using hexadecimal notation
--host Host to connect to (IP address or hostname)
--ignore-table Do not dump given table
--include-master-host-port Include MASTER_HOST/MASTER_PORT options in CHANGE MASTER statement produced with --dump-slave
--insert-ignore Write INSERT IGNORE rather than INSERT statements
--lines-terminated-by This option is used with the --tab option and has the same meaning as the corresponding clause for LOAD DATA INFILE
--lock-all-tables Lock all tables across all databases
--lock-tables Lock all tables before dumping them
--log-error Append warnings and errors to named file
--login-path Read login path options from .mylogin.cnf 5.6.6
--master-data Write the binary log file name and position to the output
--max_allowed_packet Maximum packet length to send to or receive from server
--net_buffer_length Buffer size for TCP/IP and socket communication
--no-autocommit Enclose the INSERT statements for each dumped table within SET autocommit = 0 and COMMIT statements
--no-create-db Do not write CREATE DATABASE statements
--no-create-info Do not write CREATE TABLE statements that re-create each dumped table
--no-data Do not dump table contents
--no-defaults Read no option files
--no-set-names Same as --skip-set-charset
--no-tablespaces Do not write any CREATE LOGFILE GROUP or CREATE TABLESPACE statements in output
--opt Shorthand for --add-drop-table --add-locks --create-options --disable-keys --extended-insert --lock-tables --quick --set-charset.
--order-by-primary Dump each table's rows sorted by its primary key, or by its first unique index
--password Password to use when connecting to server
--pipe On Windows, connect to server using named pipe
--plugin-dir Directory where plugins are installed
--port TCP/IP port number for connection
--print-defaults Print default options
--protocol Connection protocol to use
--quick Retrieve rows for a table from the server a row at a time
--quote-names Quote identifiers within backtick characters
--replace Write REPLACE statements rather than INSERT statements
--result-file Direct output to a given file
--routines Dump stored routines (procedures and functions) from dumped databases
--secure-auth Do not send passwords to server in old (pre-4.1) format 5.6.17
--set-charset Add SET NAMES default_character_set to output
--set-gtid-purged Whether to add SET @@GLOBAL.GTID_PURGED to output 5.6.9
--shared-memory-base-name The name of shared memory to use for shared-memory connections
--single-transaction Issue a BEGIN SQL statement before dumping data from server
--skip-add-drop-table Do not add a DROP TABLE statement before each CREATE TABLE statement
--skip-add-locks Do not add locks
--skip-comments Do not add comments to dump file
--skip-compact Do not produce more compact output
--skip-disable-keys Do not disable keys
--skip-extended-insert Turn off extended-insert
--skip-opt Turn off options set by --opt
--skip-quick Do not retrieve rows for a table from the server a row at a time
--skip-quote-names Do not quote identifiers
--skip-set-charset Do not write SET NAMES statement
--skip-triggers Do not dump triggers
--skip-tz-utc Turn off tz-utc
--socket For connections to localhost, the Unix socket file to use
--ssl Enable encrypted connection
--ssl-ca File that contains list of trusted SSL Certificate Authorities
--ssl-capath Directory that contains trusted SSL Certificate Authority certificate files
--ssl-cert File that contains X.509 certificate
--ssl-cipher List of permitted ciphers for connection encryption
--ssl-crl File that contains certificate revocation lists 5.6.3
--ssl-crlpath Directory that contains certificate revocation list files 5.6.3
--ssl-key File that contains X.509 key
--ssl-mode Security state of connection to server 5.6.30
--ssl-verify-server-cert Verify host name against server certificate Common Name identity
--tab Produce tab-separated data files
--tables Override --databases or -B option
--triggers Dump triggers for each dumped table
--tz-utc Add SET TIME_ZONE='+00:00' to dump file
--user MySQL user name to use when connecting to server
--verbose Verbose mode
--version Display version information and exit
--where Dump only rows selected by given WHERE condition
--xml Produce XML output

Connection Options

The mysqldump command logs into a MySQL server to extract information. The following options specify how to connect to the MySQL server, either on the same machine or a remote system.

  • --bind-address=ip_address

    On a computer having multiple network interfaces, use this option to select which interface to use for connecting to the MySQL server.

    This option is supported beginning with MySQL 5.6.1.

  • --compress, -C

    Compress all information sent between the client and the server if both support compression.

  • --default-auth=plugin

    A hint about the client-side authentication plugin to use. See Section 6.3.7, “Pluggable Authentication”.

  • --enable-cleartext-plugin

    Enable the mysql_clear_password cleartext authentication plugin. (See Section 6.5.1.5, “Client-Side Cleartext Pluggable Authentication”.)

    This option was added in MySQL 5.6.28.

  • --host=host_name, -h host_name

    Dump data from the MySQL server on the given host. The default host is localhost.

  • --login-path=name

    Read options from the named login path in the .mylogin.cnf login path file. A login path is an option group containing options that specify which MySQL server to connect to and which account to authenticate as. To create or modify a login path file, use the mysql_config_editor utility. See Section 4.6.6, “mysql_config_editor — MySQL Configuration Utility”.

    For additional information about this and other option-file options, see Section 4.2.7, “Command-Line Options that Affect Option-File Handling”.

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

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

    Specifying a password on the command line should be considered insecure. See Section 6.1.2.1, “End-User Guidelines for Password Security”. You can use an option file to avoid giving the password on the command line.

  • --pipe, -W

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

  • --plugin-dir=dir_name

    The directory in which to look for plugins. Specify this option if the --default-auth option is used to specify an authentication plugin but mysqldump does not find it. See Section 6.3.7, “Pluggable Authentication”.

  • --port=port_num, -P port_num

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

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

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

  • --secure-auth

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

    Note

    Passwords that use the pre-4.1 hashing method are less secure than passwords that use the native password hashing method and should be avoided. Pre-4.1 passwords are deprecated and support for them will be removed in a future MySQL release. For account upgrade instructions, see Section 6.5.1.3, “Migrating Away from Pre-4.1 Password Hashing and the mysql_old_password Plugin”.

    Note

    This option is deprecated and will be removed in a future release. As of MySQL 5.7.5, it is always enabled and attempting to disable it produces an error.

  • --socket=path, -S path

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

  • --ssl*

    Options that begin with --ssl specify whether to connect to the server using SSL and indicate where to find SSL keys and certificates. See Section 6.4.2, “Command Options for Encrypted Connections”.

  • --user=user_name, -u user_name

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

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

  • max_allowed_packet

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

  • net_buffer_length

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

Option-File Options

These options are used to control which option files to read.

DDL Options

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

Debug Options

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

  • --allow-keywords

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

  • --comments, -i

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

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

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

  • --debug-check

    Print some debugging information when the program exits.

  • --debug-info

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

  • --dump-date

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

    -- Dump completed on DATE
    

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

  • --force, -f

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

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

  • --log-error=file_name

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

  • --skip-comments

    See the description for the --comments option.

  • --verbose, -v

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

Help Options

The following options display information about the mysqldump command itself.

  • --help, -?

    Display a help message and exit.

  • --version, -V

    Display version information and exit.

Internationalization Options

The following options change how the mysqldump command represents character data with national language settings.

Replication Options

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

  • --apply-slave-statements

    For a slave dump produced with the --dump-slave option, add a STOP SLAVE statement before the CHANGE MASTER TO statement and a START SLAVE statement at the end of the output.

  • --delete-master-logs

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

  • --dump-slave[=value]

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

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

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

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

    In conjunction with --dump-slave, the --apply-slave-statements and --include-master-host-port options can also be used.

  • --include-master-host-port

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

  • --master-data[=value]

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

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

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

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

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

    Prior to MySQL 5.6.4, this option was required for dumping the replication log tables (see Section 17.2.2, “Replication Relay and Status Logs”).

  • --set-gtid-purged=value

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

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

    Value Meaning
    OFF Add no SET statement to the output.
    ON Add a SET statement to the output. An error occurs if GTIDs are not enabled on the server.
    AUTO Add a SET statement to the output if GTIDs are enabled on the server.

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

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

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

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

    Note

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

    This option was added in MySQL 5.6.9.

Format Options

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

  • --compact

    Produce more compact output. This option enables the --skip-add-drop-table, --skip-add-locks, --skip-comments, --skip-disable-keys, and --skip-set-charset options.

  • --compatible=name

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

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

  • --complete-insert, -c

    Use complete INSERT statements that include column names.

  • --create-options

    Include all MySQL-specific table options in the CREATE TABLE statements.

  • --fields-terminated-by=..., --fields-enclosed-by=..., --fields-optionally-enclosed-by=..., --fields-escaped-by=...

    These options are used with the --tab option and have the same meaning as the corresponding FIELDS clauses for LOAD DATA INFILE. See Section 13.2.6, “LOAD DATA INFILE Syntax”.

  • --hex-blob

    Dump binary columns using hexadecimal notation (for example, 'abc' becomes 0x616263). The affected data types are BINARY, VARBINARY, the BLOB types, and BIT.

  • --lines-terminated-by=...

    This option is used with the --tab option and has the same meaning as the corresponding LINES clause for LOAD DATA INFILE. See Section 13.2.6, “LOAD DATA INFILE Syntax”.

  • --quote-names, -Q

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

  • --result-file=file_name, -r file_name

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

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

  • --tab=dir_name, -T dir_name

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

    Note

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

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

    Column values are converted to the character set specified by the --default-character-set option.

  • --tz-utc

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

  • --xml, -X

    Write dump output as well-formed XML.

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

    Value: XML Representation:
    NULL (unknown value)

    <field name="column_name" xsi:nil="true" />

    '' (empty string)

    <field name="column_name"></field>

    'NULL' (string value)

    <field name="column_name">NULL</field>

    The output from the mysql client when run using the --xml option also follows the preceding rules. (See Section 4.5.1.1, “mysql Options”.)

    XML output from mysqldump includes the XML namespace, as shown here:

    shell> mysqldump --xml -u root world City
    <?xml version="1.0"?>
    <mysqldump xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
    <database name="world">
    <table_structure name="City">
    <field Field="ID" Type="int(11)" Null="NO" Key="PRI" Extra="auto_increment" />
    <field Field="Name" Type="char(35)" Null="NO" Key="" Default="" Extra="" />
    <field Field="CountryCode" Type="char(3)" Null="NO" Key="" Default="" Extra="" />
    <field Field="District" Type="char(20)" Null="NO" Key="" Default="" Extra="" />
    <field Field="Population" Type="int(11)" Null="NO" Key="" Default="0" Extra="" />
    <key Table="City" Non_unique="0" Key_name="PRIMARY" Seq_in_index="1" Column_name="ID"
    Collation="A" Cardinality="4079" Null="" Index_type="BTREE" Comment="" />
    <options Name="City" Engine="MyISAM" Version="10" Row_format="Fixed" Rows="4079"
    Avg_row_length="67" Data_length="273293" Max_data_length="18858823439613951"
    Index_length="43008" Data_free="0" Auto_increment="4080"
    Create_time="2007-03-31 01:47:01" Update_time="2007-03-31 01:47:02"
    Collation="latin1_swedish_ci" Create_options="" Comment="" />
    </table_structure>
    <table_data name="City">
    <row>
    <field name="ID">1</field>
    <field name="Name">Kabul</field>
    <field name="CountryCode">AFG</field>
    <field name="District">Kabol</field>
    <field name="Population">1780000</field>
    </row>
    
    ...
    
    <row>
    <field name="ID">4079</field>
    <field name="Name">Rafah</field>
    <field name="CountryCode">PSE</field>
    <field name="District">Rafah</field>
    <field name="Population">92020</field>
    </row>
    </table_data>
    </database>
    </mysqldump>
    

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

Filtering Options

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

  • --all-databases, -A

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

    Prior to MySQL 5.6.4, the slave_master_info and slave_relay_log_info tables (see Section 17.2.2, “Replication Relay and Status Logs”) were not included by this option.

  • --databases, -B

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

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

  • --events, -E

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

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

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

  • --ignore-table=db_name.tbl_name

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

  • --no-data, -d

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

  • --routines, -R

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

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

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

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

  • --tables

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

  • --triggers

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

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

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

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

    Examples:

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

Performance Options

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

Performance is also affected by the transactional options, primarily for the dump operation.

  • --delayed-insert

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

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

  • --disable-keys, -K

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

  • --extended-insert, -e

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

  • --insert-ignore

    Write INSERT IGNORE statements rather than INSERT statements.

  • --opt

    This option, enabled by default, is shorthand for the combination of --add-drop-table --add-locks --create-options --disable-keys --extended-insert --lock-tables --quick --set-charset. It gives a fast dump operation and produces a dump file that can be reloaded into a MySQL server quickly.

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

  • --quick, -q

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

  • --skip-opt

    See the description for the --opt option.

Transactional Options

The following options trade off the performance of the dump operation, against the reliability and consistency of the exported data.

  • --add-locks

    Surround each table dump with LOCK TABLES and UNLOCK TABLES statements. This results in faster inserts when the dump file is reloaded. See Section 8.2.4.1, “Optimizing INSERT Statements”.

  • --flush-logs, -F

    Flush the MySQL server log files before starting the dump. This option requires the RELOAD privilege. If you use this option in combination with the --all-databases option, the logs are flushed for each database dumped. The exception is when using --lock-all-tables, --master-data, or --single-transaction: In this case, the logs are flushed only once, corresponding to the moment that all tables are locked by FLUSH TABLES WITH READ LOCK. If you want your dump and the log flush to happen at exactly the same moment, you should use --flush-logs together with --lock-all-tables, --master-data, or --single-transaction.

  • --flush-privileges

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

  • --lock-all-tables, -x

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

  • --lock-tables, -l

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

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

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

  • --no-autocommit

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

  • --order-by-primary

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

  • --shared-memory-base-name=name

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

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

  • --single-transaction

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

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

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

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

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

Option Groups

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

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

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

Examples

To make a backup of an entire database:

shell> mysqldump db_name > backup-file.sql

To load the dump file back into the server:

shell> mysql db_name < backup-file.sql

Another way to reload the dump file:

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

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

shell> mysqldump --opt db_name | mysql --host=remote_host -C db_name

You can dump several databases with one command:

shell> mysqldump --databases db_name1 [db_name2 ...] > my_databases.sql

To dump all databases, use the --all-databases option:

shell> mysqldump --all-databases > all_databases.sql

For InnoDB tables, mysqldump provides a way of making an online backup:

shell> mysqldump --all-databases --master-data --single-transaction > all_databases.sql

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

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

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

Or:

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

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

For more information on making backups, see Section 7.2, “Database Backup Methods”, and Section 7.3, “Example Backup and Recovery Strategy”.

Restrictions

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

mysqldump does not dump the NDB Cluster ndbinfo information database.

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

If you encounter problems backing up views due to insufficient privileges, see Section C.5, “Restrictions on Views” for a workaround.

4.5.5 mysqlimport — A Data Import Program

The mysqlimport client provides a command-line interface to the LOAD DATA INFILE SQL statement. Most options to mysqlimport correspond directly to clauses of LOAD DATA INFILE syntax. See Section 13.2.6, “LOAD DATA INFILE Syntax”.

Invoke mysqlimport like this:

shell> mysqlimport [options] db_name textfile1 [textfile2 ...]

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

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

Table 4.12 mysqlimport Options

Format Description Introduced
--bind-address Use specified network interface to connect to MySQL Server 5.6.1
--columns This option takes a comma-separated list of column names as its value
--compress Compress all information sent between client and server
--debug Write debugging log
--debug-check Print debugging information when program exits
--debug-info Print debugging information, memory, and CPU statistics when program exits
--default-auth Authentication plugin to use 5.6.2
--default-character-set Specify default character set
--defaults-extra-file Read named option file in addition to usual option files
--defaults-file Read only named option file
--defaults-group-suffix Option group suffix value
--delete Empty the table before importing the text file
--enable-cleartext-plugin Enable cleartext authentication plugin 5.6.28
--fields-enclosed-by This option has the same meaning as the corresponding clause for LOAD DATA INFILE
--fields-escaped-by This option has the same meaning as the corresponding clause for LOAD DATA INFILE
--fields-optionally-enclosed-by This option has the same meaning as the corresponding clause for LOAD DATA INFILE
--fields-terminated-by This option has the same meaning as the corresponding clause for LOAD DATA INFILE
--force Continue even if an SQL error occurs
--help Display help message and exit
--host Connect to MySQL server on given host
--ignore See the description for the --replace option
--ignore-lines Ignore the first N lines of the data file
--lines-terminated-by This option has the same meaning as the corresponding clause for LOAD DATA INFILE
--local Read input files locally from the client host
--lock-tables Lock all tables for writing before processing any text files
--login-path Read login path options from .mylogin.cnf 5.6.6
--low-priority Use LOW_PRIORITY when loading the table.
--no-defaults Read no option files
--password Password to use when connecting to server
--pipe On Windows, connect to server using named pipe
--plugin-dir Directory where plugins are installed 5.6.2
--port TCP/IP port number for connection
--print-defaults Print default options
--protocol Connection protocol to use
--replace The --replace and --ignore options control handling of input rows that duplicate existing rows on unique key values
--secure-auth Do not send passwords to server in old (pre-4.1) format 5.6.17
--shared-memory-base-name The name of shared memory to use for shared-memory connections
--silent Produce output only when errors occur
--socket For connections to localhost, the Unix socket file to use
--ssl Enable encrypted connection
--ssl-ca File that contains list of trusted SSL Certificate Authorities
--ssl-capath Directory that contains trusted SSL Certificate Authority certificate files
--ssl-cert File that contains X.509 certificate
--ssl-cipher List of permitted ciphers for connection encryption
--ssl-crl File that contains certificate revocation lists 5.6.3
--ssl-crlpath Directory that contains certificate revocation list files 5.6.3
--ssl-key File that contains X.509 key
--ssl-mode Security state of connection to server 5.6.30
--ssl-verify-server-cert Verify host name against server certificate Common Name identity
--use-threads Number of threads for parallel file-loading
--user MySQL user name to use when connecting to server
--verbose Verbose mode
--version Display version information and exit

Here is a sample session that demonstrates use of mysqlimport:

shell> mysql -e 'CREATE TABLE imptest(id INT, n VARCHAR(30))' test
shell> ed
a
100     Max Sydow
101     Count Dracula
.
w imptest.txt
32
q
shell> od -c imptest.txt
0000000   1   0   0  \t   M   a   x       S   y   d   o   w  \n   1   0
0000020   1  \t   C   o   u   n   t       D   r   a   c   u   l   a  \n
0000040
shell> mysqlimport --local test imptest.txt
test.imptest: Records: 2  Deleted: 0  Skipped: 0  Warnings: 0
shell> mysql -e 'SELECT * FROM imptest' test
+------+---------------+
| id   | n             |
+------+---------------+
|  100 | Max Sydow     |
|  101 | Count Dracula |
+------+---------------+

4.5.6 mysqlshow — Display Database, Table, and Column Information

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

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

Invoke mysqlshow like this:

shell> mysqlshow [options] [db_name [tbl_name [col_name]]]
  • If no database is given, a list of database names is shown.

  • If no table is given, all matching tables in the database are shown.

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

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

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

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

Table 4.13 mysqlshow Options

Format Description Introduced
--bind-address Use specified network interface to connect to MySQL Server 5.6.1
--compress Compress all information sent between client and server
--count Show the number of rows per table
--debug Write debugging log
--debug-check Print debugging information when program exits
--debug-info Print debugging information, memory, and CPU statistics when program exits
--default-auth Authentication plugin to use 5.6.2
--default-character-set Specify default character set
--defaults-extra-file Read named option file in addition to usual option files
--defaults-file Read only named option file
--defaults-group-suffix Option group suffix value
--enable-cleartext-plugin Enable cleartext authentication plugin 5.6.28
--help Display help message and exit
--host Connect to MySQL server on given host
--keys Show table indexes
--login-path Read login path options from .mylogin.cnf 5.6.6
--no-defaults Read no option files
--password Password to use when connecting to server
--pipe On Windows, connect to server using named pipe
--plugin-dir Directory where plugins are installed 5.6.2
--port TCP/IP port number for connection
--print-defaults Print default options
--protocol Connection protocol to use
--secure-auth Do not send passwords to server in old (pre-4.1) format 5.6.17
--shared-memory-base-name The name of shared memory to use for shared-memory connections
--show-table-type Show a column indicating the table type
--socket For connections to localhost, the Unix socket file to use
--ssl Enable encrypted connection
--ssl-ca File that contains list of trusted SSL Certificate Authorities
--ssl-capath Directory that contains trusted SSL Certificate Authority certificate files
--ssl-cert File that contains X.509 certificate
--ssl-cipher List of permitted ciphers for connection encryption
--ssl-crl File that contains certificate revocation lists 5.6.3
--ssl-crlpath Directory that contains certificate revocation list files 5.6.3
--ssl-key File that contains X.509 key
--ssl-mode Security state of connection to server 5.6.30
--ssl-verify-server-cert Verify host name against server certificate Common Name identity
--status Display extra information about each table
--user MySQL user name to use when connecting to server
--verbose Verbose mode
--version Display version information and exit

4.5.7 mysqlslap — Load Emulation Client

mysqlslap is a diagnostic program designed to emulate client load for a MySQL server and to report the timing of each stage. It works as if multiple clients are accessing the server.

Invoke mysqlslap like this:

shell> mysqlslap [options]

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

mysqlslap runs in three stages:

  1. Create schema, table, and optionally any stored programs or data to use for the test. This stage uses a single client connection.

  2. Run the load test. This stage can use many client connections.

  3. Clean up (disconnect, drop table if specified). This stage uses a single client connection.

Examples:

Supply your own create and query SQL statements, with 50 clients querying and 200 selects for each (enter the command on a single line):

mysqlslap --delimiter=";"
  --create="CREATE TABLE a (b int);INSERT INTO a VALUES (23)"
  --query="SELECT * FROM a" --concurrency=50 --iterations=200

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

mysqlslap --concurrency=5 --iterations=20
  --number-int-cols=2 --number-char-cols=3
  --auto-generate-sql

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

mysqlslap --concurrency=5
  --iterations=5 --query=query.sql --create=create.sql
  --delimiter=";"

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

Table 4.14 mysqlslap Options

Format Description Introduced
--auto-generate-sql Generate SQL statements automatically when they are not supplied in files or using command options
--auto-generate-sql-add-autoincrement Add AUTO_INCREMENT column to automatically generated tables
--auto-generate-sql-execute-number Specify how many queries to generate automatically
--auto-generate-sql-guid-primary Add a GUID-based primary key to automatically generated tables
--auto-generate-sql-load-type Specify the test load type
--auto-generate-sql-secondary-indexes Specify how many secondary indexes to add to automatically generated tables
--auto-generate-sql-unique-query-number How many different queries to generate for automatic tests.
--auto-generate-sql-unique-write-number How many different queries to generate for --auto-generate-sql-write-number
--auto-generate-sql-write-number How many row inserts to perform on each thread
--commit How many statements to execute before committing.
--compress Compress all information sent between client and server
--concurrency Number of clients to simulate when issuing the SELECT statement
--create File or string containing the statement to use for creating the table
--create-schema Schema in which to run the tests
--csv Generate output in comma-separated values format
--debug Write debugging log
--debug-check Print debugging information when program exits
--debug-info Print debugging information, memory, and CPU statistics when program exits
--default-auth Authentication plugin to use 5.6.2
--defaults-extra-file Read named option file in addition to usual option files
--defaults-file Read only named option file
--defaults-group-suffix Option group suffix value
--delimiter Delimiter to use in SQL statements
--detach Detach (close and reopen) each connection after each N statements
--enable-cleartext-plugin Enable cleartext authentication plugin 5.6.7
--engine Storage engine to use for creating the table
--help Display help message and exit
--host Connect to MySQL server on given host
--iterations Number of times to run the tests
--login-path Read login path options from .mylogin.cnf 5.6.6
--no-defaults Read no option files
--no-drop Do not drop any schema created during the test run 5.6.3
--number-char-cols Number of VARCHAR columns to use if --auto-generate-sql is specified
--number-int-cols Number of INT columns to use if --auto-generate-sql is specified
--number-of-queries Limit each client to approximately this number of queries
--only-print Do not connect to databases. mysqlslap only prints what it would have done
--password Password to use when connecting to server
--pipe On Windows, connect to server using named pipe
--plugin-dir Directory where plugins are installed 5.6.2
--port TCP/IP port number for connection
--post-query File or string containing the statement to execute after the tests have completed
--post-system String to execute using system() after the tests have completed
--pre-query File or string containing the statement to execute before running the tests
--pre-system String to execute using system() before running the tests
--print-defaults Print default options
--protocol Connection protocol to use
--query File or string containing the SELECT statement to use for retrieving data
--secure-auth Do not send passwords to server in old (pre-4.1) format 5.6.17
--shared-memory-base-name The name of shared memory to use for shared-memory connections
--silent Silent mode
--socket For connections to localhost, the Unix socket file to use
--ssl Enable encrypted connection
--ssl-ca File that contains list of trusted SSL Certificate Authorities
--ssl-capath Directory that contains trusted SSL Certificate Authority certificate files
--ssl-cert File that contains X.509 certificate
--ssl-cipher List of permitted ciphers for connection encryption
--ssl-crl File that contains certificate revocation lists 5.6.3
--ssl-crlpath Directory that contains certificate revocation list files 5.6.3
--ssl-key File that contains X.509 key
--ssl-mode Security state of connection to server 5.6.30
--ssl-verify-server-cert Verify host name against server certificate Common Name identity
--user MySQL user name to use when connecting to server
--verbose Verbose mode
--version Display version information and exit

4.6 MySQL Administrative and Utility Programs

This section describes administrative programs and programs that perform miscellaneous utility operations.

4.6.1 innochecksum — Offline InnoDB File Checksum Utility

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

innochecksum does not support tablespaces that contain compressed pages.

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

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

Invoke innochecksum like this:

shell> innochecksum [options] file_name

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

  • -c

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

  • -d

    Debug mode; prints checksums for each page.

  • -e num

    End at this page number.

  • -p num

    Check only this page number.

  • -s num

    Start at this page number.

  • -v

    Verbose mode; print a progress indicator every five seconds.

4.6.2 myisam_ftdump — Display Full-Text Index information

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

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

Invoke myisam_ftdump like this:

shell> myisam_ftdump [options] tbl_name index_num

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

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

CREATE TABLE mytexttable
(
  id   INT NOT NULL,
  txt  TEXT NOT NULL,
  PRIMARY KEY (id),
  FULLTEXT (txt)
) ENGINE=MyISAM;

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

shell> myisam_ftdump mytexttable 1

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

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

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

shell> myisam_ftdump -c mytexttable 1 | sort -r

On Windows, use:

shell> myisam_ftdump -c mytexttable 1 | sort /R

myisam_ftdump supports the following options:

  • --help, -h -?

    Display a help message and exit.

  • --count, -c

    Calculate per-word statistics (counts and global weights).

  • --dump, -d

    Dump the index, including data offsets and word weights.

  • --length, -l

    Report the length distribution.

  • --stats, -s

    Report global index statistics. This is the default operation if no other operation is specified.

  • --verbose, -v

    Verbose mode. Print more output about what the program does.

4.6.3 myisamchk — MyISAM Table-Maintenance Utility

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

You can also use the CHECK TABLE and REPAIR TABLE statements to check and repair MyISAM tables. See Section 13.7.2.2, “CHECK TABLE Syntax”, and Section 13.7.2.5, “REPAIR TABLE Syntax”.

The use of myisamchk with partitioned tables is not supported.

Caution

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.

Invoke myisamchk like this:

shell> myisamchk [options] tbl_name ...

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

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

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

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

shell> myisamchk *.MYI

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

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

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

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

The recommended way to quickly check all MyISAM tables is:

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

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

shell> myisamchk --silent --force --fast --update-state \
          --key_buffer_size=64M --myisam_sort_buffer_size=64M \
          --read_buffer_size=1M --write_buffer_size=1M \
          /path/to/datadir/*/*.MYI

This command assumes that you have more than 64MB free. For more information about memory allocation with myisamchk, see Section 4.6.3.6, “myisamchk Memory Usage”.

For additional information about using myisamchk, see Section 7.6, “MyISAM Table Maintenance and Crash Recovery”.

Important

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

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

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

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

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

However, the easiest way to avoid this problem is to use CHECK TABLE instead of myisamchk to check tables. See Section 13.7.2.2, “CHECK TABLE Syntax”.

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

Table 4.15 myisamchk Options

Format Description Introduced Deprecated
--analyze Analyze the distribution of key values
--backup Make a backup of the .MYD file as file_name-time.BAK
--block-search Find the record that a block at the given offset belongs to
--check Check the table for errors
--check-only-changed Check only tables that have changed since the last check
--correct-checksum Correct the checksum information for the table
--data-file-length Maximum length of the data file (when re-creating data file when it is full)
--debug Write debugging log
--decode_bits Decode_bits
--defaults-extra-file Read named option file in addition to usual option files
--defaults-file Read only named option file
--defaults-group-suffix Option group suffix value
--description Print some descriptive information about the table
--extend-check Do very thorough table check or repair that tries to recover every possible row from the data file
--fast Check only tables that haven't been closed properly
--force Do a repair operation automatically if myisamchk finds any errors in the table
--force Overwrite old temporary files. For use with the -r or -o option
--ft_max_word_len Maximum word length for FULLTEXT indexes
--ft_min_word_len Minimum word length for FULLTEXT indexes
--ft_stopword_file Use stopwords from this file instead of built-in list
--HELP Display help message and exit
--help Display help message and exit
--information Print informational statistics about the table that is checked
--key_buffer_size Size of buffer used for index blocks for MyISAM tables
--keys-used A bit-value that indicates which indexes to update
--max-record-length Skip rows larger than the given length if myisamchk cannot allocate memory to hold them
--medium-check Do a check that is faster than an --extend-check operation
--myisam_block_size Block size to be used for MyISAM index pages
--myisam_sort_buffer_size The buffer that is allocated when sorting the index when doing a REPAIR or when creating indexes with CREATE INDEX or ALTER TABLE 5.6.9
--no-defaults Read no option files
--parallel-recover Uses the same technique as -r and -n, but creates all the keys in parallel, using different threads (beta)
--print-defaults Print default options
--quick Achieve a faster repair by not modifying the data file.
--read_buffer_size Each thread that does a sequential scan allocates a buffer of this size for each table it scans
--read-only Do not mark the table as checked
--recover Do a repair that can fix almost any problem except unique keys that aren't unique
--safe-recover Do a repair using an old recovery method that reads through all rows in order and updates all index trees based on the rows found
--set-auto-increment Force AUTO_INCREMENT numbering for new records to start at the given value
--set-collation Specify the collation to use for sorting table indexes
--silent Silent mode
--sort_buffer_size The buffer that is allocated when sorting the index when doing a REPAIR or when creating indexes with CREATE INDEX or ALTER TABLE 5.6.9
--sort-index Sort the index tree blocks in high-low order
--sort_key_blocks sort_key_blocks
--sort-records Sort records according to a particular index
--sort-recover Force myisamchk to use sorting to resolve the keys even if the temporary files would be very large
--stats_method Specifies how MyISAM index statistics collection code should treat NULLs
--tmpdir Directory to be used for storing temporary files
--unpack Unpack a table that was packed with myisampack
--update-state Store information in the .MYI file to indicate when the table was checked and whether the table crashed
--verbose Verbose mode
--version Display version information and exit
--write_buffer_size Write buffer size

4.6.3.1 myisamchk General Options

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

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

Variable Default Value
decode_bits 9
ft_max_word_len version-dependent
ft_min_word_len 4
ft_stopword_file built-in list
key_buffer_size 523264
myisam_block_size 1024
myisam_sort_key_blocks 16
read_buffer_size 262136
sort_buffer_size 2097144
sort_key_blocks 16
stats_method nulls_unequal
write_buffer_size 262136

The possible myisamchk variables and their default values can be examined with myisamchk --help:

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

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

  • You use --safe-recover.

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

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

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

myisam_block_size is the size used for index blocks.

stats_method influences how NULL values are treated for index statistics collection when the --analyze option is given. It acts like the myisam_stats_method system variable. For more information, see the description of myisam_stats_method in Section 5.1.7, “Server System Variables”, and Section 8.3.7, “InnoDB and MyISAM Index Statistics Collection”.

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

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

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

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

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

[mysqld]
ft_min_word_len=3

[myisamchk]
ft_min_word_len=3

An alternative to using myisamchk is to use the REPAIR TABLE, ANALYZE TABLE, OPTIMIZE TABLE, or ALTER TABLE. These statements are performed by the server, which knows the proper full-text parameter values to use.

4.6.3.2 myisamchk Check Options

myisamchk supports the following options for table checking operations:

  • --check, -c

    Check the table for errors. This is the default operation if you specify no option that selects an operation type explicitly.

  • --check-only-changed, -C

    Check only tables that have changed since the last check.

  • --extend-check, -e

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

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

    See also the description of this option under table repair options.

    For a description of the output format, see Section 4.6.3.5, “Obtaining Table Information with myisamchk”.

  • --fast, -F

    Check only tables that haven't been closed properly.

  • --force, -f

    Do a repair operation automatically if myisamchk finds any errors in the table. The repair type is the same as that specified with the --recover or -r option.

  • --information, -i

    Print informational statistics about the table that is checked.