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. 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. make_win_bin_dist — Package MySQL Distribution as ZIP Archive
4.4.3. make_win_src_distribution — Create Source Distribution for Windows
4.4.4. mysqlbug — Generate Bug Report
4.4.5. mysql_fix_privilege_tables — Upgrade MySQL System Tables
4.4.6. mysql_install_db — Initialize MySQL Data Directory
4.4.7. mysql_secure_installation — Improve MySQL Installation Security
4.4.8. mysql_tzinfo_to_sql — Load the Time Zone Tables
4.4.9. mysql_upgrade — Check Tables for MySQL Upgrade
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 and Repair 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.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. mysqlaccess — Client for Checking Access Privileges
4.6.7. mysqlbinlog — Utility for Processing Binary Log Files
4.6.8. mysqlhotcopy — A Database Backup Program
4.6.9. mysqlmanager — The MySQL Instance Manager
4.6.10. mysqlmanagerc — Internal Test-Suite Program
4.6.11. mysqlmanager-pwgen — Internal Test-Suite Program
4.6.12. mysql_convert_table_format — Convert Tables to Use a Given Storage Engine
4.6.13. mysql_explain_log — Use EXPLAIN on Statements in Query Log
4.6.14. mysql_find_rows — Extract SQL Statements from Files
4.6.15. mysql_fix_extensions — Normalize Table Filename Extensions
4.6.16. mysql_setpermission — Interactively Set Permissions in Grant Tables
4.6.17. mysql_tableinfo — Generate Database Metadata
4.6.18. mysql_waitpid — Kill Process and Wait for Its Termination
4.6.19. 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 — Get Compile 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 — Explain Error Codes
4.8.2. replace — A String-Replacement Utility
4.8.3. resolveip — Resolve Hostname to IP Address or Vice Versa

This chapter provides a brief overview of the command-line programs provided by MySQL AB. 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 MySQL Cluster programs. Each program's description indicates its invocation syntax and the options that it understands. Chapter 19, MySQL Cluster, describes programs specific to MySQL 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:

There are several programs that perform setup operations during MySQL installation or upgrading:

MySQL client programs:

MySQL administrative and utility programs:

MySQL program-development utilities:

Miscellaneous utilities:

MySQL AB also provides several GUI tools for administering and otherwise working with MySQL Server:

  • MySQL Administrator: This tool is used for administering MySQL servers, databases, tables, and user accounts.

  • MySQL Query Browser: This graphical tool is provided by MySQL AB for creating, executing, and optimizing queries on MySQL databases.

  • MySQL Migration Toolkit: This tool helps you migrate schemas and data from other relational database management systems for use with MySQL.

These GUI programs are available at http://dev.mysql.com/downloads/. Each has its own manual that you can access at http://dev.mysql.com/doc/.

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

MYSQL_UNIX_PORTThe default Unix socket file; used for connections to localhost
MYSQL_TCP_PORTThe default port number; used for TCP/IP connections
MYSQL_PWDThe default password
MYSQL_DEBUGDebug trace options when debugging
TMPDIRThe directory where temporary tables and files are created

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

Use of MYSQL_PWD is insecure. See Section 5.5.6, “Keeping Your Password Secure”.

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

Non-option arguments (arguments with no leading dash) provide additional information to the program. For example, the mysql program interprets the first non-option 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 understands and describe the meaning of any additional non-option 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 username and password of your MySQL account. All MySQL client programs understand these options; they allow 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 pathname 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 pathname 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 pathname. 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.4, “Setting Environment Variables”.) After modifying your PATH setting, open a new console window on Windows or log in again on Unix so that the setting goes into effect.

4.2.2. Connecting to the MySQL Server

For a client program to be able to connect to the MySQL server, it must use the proper connection parameters, such as the name of the host where the server is running and the username 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 hostname is localhost. On Unix, this has a special meaning, as described later.

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

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

To specify the hostname and username explicitly, as well as a password, supply appropriate options on the command line:

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

For password options, the password value is optional:

  • 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. Any user 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 5.5.6, “Keeping Your Password Secure”.

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

On Unix, MySQL programs treat the hostname localhost specially, in a way that is likely different from what you expect compared to other network-based programs. For connections to localhost, MySQL programs attempt to connect to the local server by using a Unix socket file. This occurs even if a --port or -P option is given to specify a port number. To ensure that the client makes a TCP/IP connection to the local server, use --host or -h to specify a hostname value of 127.0.0.1, or the IP address or name of the local server. You can also specify the connection protocol explicitly, even for localhost, by using the --protocol=TCP option. For example:

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

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

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 options may be used to control how client programs connect to the server:

  • --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 via a named pipe. This option applies for connections to a local server only. The server must have been 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 via 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 via 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 allowable --protocol option values and indicates the platforms on which each value may be used. The values are not case sensitive.

    --protocol ValueConnection ProtocolAllowable Operating Systems
    TCPTCP/IP connection to local or remote serverAll
    SOCKETUnix socket file connection to local serverUnix only
    PIPENamed-pipe connection to local serverWindows only
    MEMORYShared-memory connection to local serverWindows only
  • --shared-memory-base-name=name

    On Windows, the shared-memory name to use, for connections made via 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 via a named pipe to a local server. The default Unix socket filename 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 via SSL, if the server is configured with SSL support. For details, see Section 5.5.7.3, “SSL Command Options”.

  • --user=user_name, -u user_name

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

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

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

    [client]
    host=host_name
    user=user_name
    password=your_pass
    

    Section 4.2.3.2, “Using Option Files”, discusses option files further.

  • You can specify some connection parameters using environment variables. The host can be specified for mysql using MYSQL_HOST. The MySQL username can be specified using USER (this is for Windows and NetWare only). The password can be specified using MYSQL_PWD, although this is insecure; see Section 5.5.6, “Keeping Your Password Secure”. For a list of variables, see Section 2.4.20, “Environment Variables”.

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 most 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.4, “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.6.2, “Running Multiple Servers 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.

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

Because options are processed in order, 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

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 unambigious now might become ambiguous in the future.

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

4.2.3.1. Using Options on the Command Line

Program options specified on the command line follow these rules:

  • Options are given after the command name.

  • An option argument begins with one dash or two dashes, depending on whether it is a short form or long form of the option name. Many options have both short and long forms. For example, -? 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.)

Another option that may occasionally be useful with mysql is the --execute or -e option, which can be used to pass SQL statements to the server. When this option is used, mysql executes the statements 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 user" mysql
Enter password: ******
+------+-----------+
| User | Host      |
+------+-----------+
|      | gigan     |
| root | gigan     |
|      | localhost |
| jon  | localhost |
| root | localhost |
+------+-----------+
shell>

Note that 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 quotes, or use a different type of quotes 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 quotes, you can double quotes around the statement, and single quotes for any quoted values within the statement.

In the preceding example, the name of the mysql database was passed as a separate argument. However, the same statement could have been executed using this command, which specifies no default database:

mysql> mysql -u root -p --execute="SELECT User, Host FROM mysql.user"

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

shell> mysql -u root -p -e "SELECT VERSION();SELECT NOW()"
Enter password: ******
+------------+
| VERSION()  |
+------------+
| 5.0.19-log |
+------------+
+---------------------+
| NOW()               |
+---------------------+
| 2006-01-05 21:19:04 |
+---------------------+

The --execute or -e option may also be used to pass commands in an analogous fashion to the ndb_mgm management client for MySQL Cluster. See Section 19.2.6, “Safe Shutdown and Restart”, for an example.

4.2.3.1.1. Program Option Modifiers

Some options 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

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

mysqld enables a limit to be placed on how large client programs can set dynamic system variables. To do this, use a --maximum prefix with the variable name. For example, --maximum-query_cache_size=4M prevents any client from making the query cache size larger than 4MB.

4.2.3.2. Using Option Files

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

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

Option files used with MySQL Cluster programs are covered in Section 19.3, “MySQL Cluster Configuration”.

On Windows, MySQL programs read startup options from the following files:

FilenamePurpose
WINDIR\my.ini, WINDIR\my.cnfGlobal options
C:\my.ini, C:\my.cnfGlobal options
INSTALLDIR\my.ini, INSTALLDIR\my.cnfGlobal options
defaults-extra-fileThe file specified with --defaults-extra-file=path, if any

WINDIR represents the location of your Windows directory. This is commonly C:\WINDOWS. You can determine its exact location from the value of the WINDIR environment variable using the following command:

C:\> echo %WINDIR%

INSTALLDIR represents the MySQL installation directory. This is typically C:\PROGRAMDIR\MySQL\MySQL 5.0 Server where PROGRAMDIR represents the programs directory (usually Program Files on English-language versions of Windows), when MySQL 5.0 has been installed using the installation and configuration wizards. See Section 2.4.8.4.1.1, “The MySQL Server Configuration Wizard on Windows”.

On Unix, MySQL programs read startup options from the following files:

FilenamePurpose
/etc/my.cnfGlobal options
SYSCONFDIR/my.cnfGlobal options
$MYSQL_HOME/my.cnfServer-specific options
defaults-extra-fileThe file specified with --defaults-extra-file=path, if any
~/.my.cnfUser-specific options

SYSCONFDIR represents the directory specified with the --sysconfdir option to configure when MySQL was built. By default, this is the etc directory located under the compiled-in installation directory. This location is used as of MySQL 5.0.21. (From 5.0.21 to 5.0.53, it was read last, after ~/.my.cnf.)

MYSQL_HOME is an environment variable containing the path to the directory in which the server-specific my.cnf file resides. (This was DATADIR prior to MySQL version 5.0.3.)

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 pathnames 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.0, use of DATADIR as the location for my.cnf is deprecated.

Typically, DATADIR is /usr/local/mysql/data for a binary installation or /usr/local/var for a source installation. Note that this is the data directory location that was specified at configuration time, not the one specified with the --datadir option when mysqld starts. Use of --datadir at runtime has no effect on where the server looks for option files, because it looks for them before processing any options.

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

If multiple instances of a given option are found, the last instance takes precedence. There is one exception: For 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.

Note

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

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

The syntax for specifying options in an option file is similar to command-line syntax, except that you omit the leading two dashes 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. Non-empty 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.

  • 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. You can enclose the value within single quotes or double quotes, which is useful if the value contains a “#” comment character or whitespace.

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:

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

Leading and trailing blanks are automatically deleted from option names and values. You may 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.

Because the “\\” escape sequence represents a single backslash, you must write each “\” as “\\”. Alternatively, you can specify the value using “/” rather than “\” as the pathname separator.

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

The [client] option group is read by all client programs (but not by mysqld). This allows you to specify options that apply to all clients. For example, [client] is the perfect group to use to specify the password that you use to connect to the server. (But make sure that the option file is readable and writable only by yourself, so that other people cannot find out 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.

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

The preceding option file uses var_name=value syntax for the lines that set the key_buffer_size and max_allowed_packet variables.

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

If you want to create option groups that should be read by mysqld servers from a specific MySQL release series only, you can do this by using groups with names of [mysqld-4.1], [mysqld-5.0], and so forth. The following group indicates that the --new option should be used only by MySQL servers with 5.0.x version numbers:

[mysqld-5.0]
new

Beginning with MySQL 5.0.4, 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

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

Note

Currently, any files to be found and included using the !includedir directive on Unix operating systems must have filenames 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 [mysqldamin] 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.

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

Most MySQL programs that support option files handle the following options. They affect option-file handling, so they must be given on the command line and not in an option file. To work properly, each of these options must immediately follow the command name, with the exception that --print-defaults may be used immediately after --defaults-file or --defaults-extra-file. Also, when specifying filenames, you should avoid the use of the “~” shell metacharacter because it might not be interpreted as you expect.

  • --no-defaults

    Don't read any option files.

  • --print-defaults

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

  • --defaults-file=file_name

    Use only the given option file. file_name is the full pathname to the file. If the file does not exist or is otherwise inaccessible, the program will exit with an error.

  • --defaults-extra-file=file_name

    Read this option file after the global option file but (on Unix) before the user option file. file_name is the full pathname to the file. As of MySQL 5.0.6, if the file does not exist or is otherwise inaccessible, the program will exit with an error.

  • --defaults-group-suffix=str

    If this option is given, the program reads not only its usual option groups, but also groups with the usual names and a suffix of str. For example, the mysql client normally reads the [client] and [mysql] groups. If the --default-group-suffix=_other option is given, mysql also reads the [client_other] and [mysql_other] groups. This option was added in MySQL 5.0.10.

4.2.3.2.2. Preconfigured Option Files

MySQL provides a number of preconfigured option files that can be used as a basis for tuning the MySQL server. Look for files such as my-small.cnf, my-medium.cnf, my-large.cnf, and my-huge.cnf, which are sample option files for small, medium, large, and very large systems. On Windows, the extension is .ini rather than .cnf extension.

Note

On Windows, the .cnf or .ini option file extension might not be displayed.

For a binary distribution, look for the files in or under your installation directory. If you have a source distribution, look in the support-files directory. You can rename a copy of a sample file and place it in the appropriate location for use as a base configuration file. Regarding names and appropriate location, see the general information provided in Section 4.2.3.2, “Using Option Files”.

4.2.3.3. Using Options to Set Program Variables

Many MySQL programs have internal variables that can be set at runtime using the SET statement. See Section 12.5.4, “SET Syntax”, and Section 5.1.5, “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_buffer_length 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 unambigious 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;

Note

Before MySQL 4.0.2, the only syntax for setting program variables was --set-variable=option=value (or set-variable=option=value in option files). Underscores cannot be given as dashes, and the variable name must be specified in full. This syntax still is recognized, but is now deprecated.

4.2.3.4. 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 username “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.0.68 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 hostname, --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 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 hostname 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-errors &
[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-errors 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)

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

shell> mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.0.68 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.0.68 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)

4.2.4. Setting Environment Variables

Environment variables can be set at the command prompt to affect the current invocation of your command processor, or set permanently to affect future invocations. To set a variable permanently, you can set it in a startup file or by using the interface provided by your system for this purpose. Consult the documentation for your command interpreter for specific details. Section 2.4.20, “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 or NetWare, 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, 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 non-login shells, so you might want to add the setting to .bashrc for login shells and to .bash_profile for non-login shells to make sure that PATH is set regardless.

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

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

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

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

4.3. MySQL Server and Server-Startup Programs

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

4.3.1. mysqld — The MySQL Server

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

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

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

shell> mysqld --verbose --help

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

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 and NetWare. mysqld_safe adds some safety features such as restarting the server when an error occurs and logging runtime information to an error log file. NetWare-specific behaviors are listed later in this section.

Note

To preserve backward compatibility with older versions of MySQL, MySQL binary distributions still include safe_mysqld as a symbolic link to mysqld_safe. However, you should not rely on this because it is removed as of MySQL 5.1.

By default, mysqld_safe before MySQL 5.0.27 tries to start an executable named mysqld-max if it exists, and mysqld otherwise. Be aware of the implications of this behavior:

  • On Linux, the MySQL-Max RPM relies on this mysqld_safe behavior. The RPM installs an executable named mysqld-max, which causes mysqld_safe to automatically use that executable rather than mysqld from that point on.

  • If you install a MySQL-Max distribution that includes a server named mysqld-max, and then upgrade later to a non-Max version of MySQL, mysqld_safe will still attempt to run the old mysqld-max server. If you perform such an upgrade, you should manually remove the old mysqld-max server to ensure that mysqld_safe runs the new mysqld server.

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.2, “Command Options”.

All options specified to mysqld_safe on the command line are passed to mysqld. If you want to use any options that are specific to mysqld_safe and that mysqld doesn't support, do not specify them on the command line. Instead, list them in the [mysqld_safe] group of an option file. See Section 4.2.3.2, “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, although you should rename such sections to [mysqld_safe] in MySQL 5.0 installations.

Table 4.1. mysqld_safe Option Reference

FormatConfig FileDescriptionIntroduction
--autocloseautocloseOn NetWare, mysqld_safe provides a screen presence 
--basedir=pathbasedirThe path to the MySQL installation directory 
--core-file-size=sizecore-file-sizeThe size of the core file that mysqld should be able to create 
--datadir=pathdatadirThe path to the data directory 
--defaults-extra-file=pathdefaults-extra-fileThe name of an option file to be read in addition to the usual option files 
--defaults-file=file_namedefaults-fileThe name of an option file to be read instead of the usual option files 
--help Display a help message and exit5.0.3
--ledir=pathledirUse this option to indicate the pathname to the directory where the server is located 
--log-error=file_namelog-errorWrite the error log to the given file 
--mysqld=prog_namemysqldThe name of the server program (in the ledir directory) that you want to start 
--mysqld-version=suffixmysqld-versionThis option is similar to the --mysqld option, but you specify only the suffix for the server program name 
--nice=priorityniceUse the nice program to set the server's scheduling priority to the given value 
--no-defaultsno-defaultsDo not read any option files 
--open-files-limit=countopen-files-limitThe number of files that mysqld should be able to open 
--pid-filepid-fileThe pathname of the process ID file 
--port=numberportThe port number that the server should use when listening for TCP/IP connections 
--skip-kill-mysqldskip-kill-mysqldDo not try to kill stray mysqld processes 
--socket=pathsocketThe Unix socket file that the server should use when listening for local connections 
--timezone=timezonetimezoneSet the TZ time zone environment variable to the given option value 
--user={user_name|user_id}userRun the mysqld server as the user having the name user_name or the numeric user ID user_id 

mysqld_safe supports the following options:

  • --help

    Display a help message and exit. (Added in MySQL 5.0.3)

  • --autoclose

    (NetWare only) On NetWare, mysqld_safe provides a screen presence. When you unload (shut down) the mysqld_safe NLM, the screen does not by default go away. Instead, it prompts for user input:

    *<NLM has terminated; Press any key to close the screen>*
    

    If you want NetWare to close the screen automatically instead, use the --autoclose option to mysqld_safe.

  • --basedir=path

    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=path

    The path to the data directory.

  • --defaults-extra-file=path

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

  • --defaults-file=file_name

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

  • --ledir=path

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

  • --log-error=file_name

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

  • --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 pathname to the directory where the server is located.

  • --mysqld-version=suffix

    This option is similar to the --mysqld option, but you specify only the suffix for the server program name. The basename 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.

  • --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. This must be the first option on the command line if it is used.

  • --open-files-limit=count

    The number of files that mysqld should be able to open. The option value is passed to ulimit -n. Note that you need to start mysqld_safe as root for this to work properly!

  • --pid-file=file_name

    The pathname of the process ID file.

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

  • --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-option 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.4.6, “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 pathnames. 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, you can specify the --ledir and --datadir options to indicate the directories in which the server and databases are located on your system.

Normally, you should not edit the mysqld_safe script. Instead, configure mysqld_safe by using command-line options or options in the [mysqld_safe] section of a my.cnf option file. In rare cases, it might be necessary to edit mysqld_safe to get it to start the server properly. However, if you do this, your modified version of mysqld_safe might be overwritten if you upgrade MySQL in the future, so you should make a copy of your edited version that you can reinstall.

On NetWare, mysqld_safe is a NetWare Loadable Module (NLM) that is ported from the original Unix shell script. It starts the server as follows:

  1. Runs a number of system and option checks.

  2. Runs a check on MyISAM tables.

  3. Provides a screen presence for the MySQL server.

  4. Starts mysqld, monitors it, and restarts it if it terminates in error.

  5. Sends error messages from mysqld to the host_name.err file in the data directory.

  6. Sends mysqld_safe screen output to the host_name.safe file in the data directory.

4.3.3. mysql.server — MySQL Server Startup Script

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

mysql.server can be found in the support-files directory under your MySQL installation directory or in a MySQL source distribution.

If you use the Linux server RPM package (MySQL-server-VERSION.rpm), the mysql.server script will be installed in the /etc/init.d directory with the name mysql. You need not install it manually. See Section 2.4.9, “Installing MySQL from RPM Packages on Linux”, for more information on the Linux RPM packages.

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

If you install MySQL from a source distribution or using a binary distribution format that does not install mysql.server automatically, you can install it manually. Instructions are provided in Section 2.4.16.2.2, “Starting and Stopping MySQL Automatically”.

mysql.server reads options from the [mysql.server] and [mysqld] sections of option files. For backward compatibility, it also reads [mysql_server] sections, although you should rename such sections to [mysql.server] when using MySQL 5.0.

mysql.server understands the following options:

  • --basedir=path

    The path to the MySQL installation directory.

  • --datadir=path

    The path to the MySQL data directory.

  • --pid-file=file_name

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

  • --service-startup-timeout=file_name

    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). This option was added in MySQL 5.0.40. Before that, a value of 900 is always used.

  • --use-mysqld_safe

    Use mysqld_safe to start the server. This is the default. This option was added in MySQL 5.0.4.

  • --use-manager

    Use Instance Manager to start the server. This option was added in MySQL 5.0.4.

  • --user=user_name

    The login username to use for running mysqld. This option was added in MySQL 5.0.4.

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. The MySQL Instance Manager is an alternative means of managing multiple servers (see Section 4.6.9, “mysqlmanager — The MySQL Instance Manager”).

mysqld_multi searches for groups named [mysqldN] in my.cnf (or in the file named by the --config-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.4.16.2.2, “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.6, “Running Multiple MySQL Servers on the Same Machine”.

To invoke mysqld_multi, use the following syntax:

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

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

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

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

shell> mysqld_multi start 17

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

shell> mysqld_multi stop 8,10-13

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

shell> mysqld_multi --example

As of MySQL 5.0.42, mysqld_multi searches for option files as follows:

  • With --no-defaults, no option files are read.

  • With --defaults-file=file_name, only the named file is read.

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

Option files read are searched for [mysqld_multi] and [mysqldN] option groups.

Before MySQL 5.0.42, the preceding options are not recognized. Files in the standard locations are read, and any file named by the --config-file=file_name option, if one is given. A file named by --config-file is read only for [mysqldN] option groups, not the [mysqld_multi] group.

mysqld_multi supports the following options:

  • --help

    Display a help message and exit.

  • --config-file=file_name

    As of MySQL 5.0.42, this option is deprecated. If given, it is treated the same way as --defaults-extra-file, described earlier.

    Before MySQL 5.0.42, this option specifies the name of an extra option file. It affects where mysqld_multi looks for [mysqldN] option groups. Without this option, all options are read from the usual my.cnf file. The option does not affect where mysqld_multi reads its own options, which are always taken from the [mysqld_multi] group in the usual my.cnf file.

  • --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. Note that you can specify mysqld_safe as the value for this option also. If you use mysqld_safe to start the server, you can include the mysqld or ledir options in the corresponding [mysqldN] option group. These options indicate the name of the server that mysqld_safe should start and the pathname 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. Note that 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 via 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 via 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 username 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.6, “Running Multiple MySQL Servers on the Same 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 5.3.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 username 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 usernames or passwords for the administrative accounts, you might want to create an account on each server that has the same username 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> GRANT SHUTDOWN ON *.*
        -> TO 'multi_admin'@'localhost' IDENTIFIED BY 'multipass';
    

    See Section 5.4.2, “How the Privilege System Works”. You have to do this for each mysqld server. Change the connection parameters appropriately when connecting to each one. Note that the hostname part of the account name must allow 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-adress to cause different servers to listen to different interfaces.)

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

    ----------------------------------------------------------------
    MY_PWD=`pwd`
    # Check if we are starting this relative (for the binary release)
    if test -d $MY_PWD/data/mysql -a -f ./share/mysql/english/errmsg.sys -a \
     -x ./bin/mysqld
    ----------------------------------------------------------------
    

    The test performed by these lines should be successful, or you might encounter problems. See Section 4.3.2, “mysqld_safe — MySQL Server Startup Script”.

  • 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 root user. 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 file should probably be in your home dir (~/.my.cnf)
# or /etc/my.cnf
# Version 2.1 by Jani Tolonen

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

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

[mysqld3]
socket     = /tmp/mysql.sock3
port       = 3308
pid-file   = /usr/local/mysql/var3/hostname.pid3
datadir    = /usr/local/mysql/var3
language   = /usr/local/share/mysql/swedish
user       = monty

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

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

See Section 4.2.3.2, “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 plaintext file located at sql/share/errmsg.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 understands the options described in the following list.

  • --help, -?

    Display a help message and exit.

  • --charset=path, -C path

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

  • --debug=debug_options, -# debug_options

    Write a debugging log. The debug_options string often 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.txt.

  • --name_file=file_name, -N file_name

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

  • --out_dir=path, -D path

    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. make_win_bin_dist — Package MySQL Distribution as ZIP Archive

This script is used on Windows after building a MySQL distribution from source to create executable programs. It packages the binaries and support files into a ZIP archive that can be unpacked at the location where you want to install MySQL.

make_win_bin_dist is a shell script, so you must have Cygwin installed to use it.

This program's use is subject to change. Currently, you invoke it as follows from the root directory of your source distribution:

shell> make_win_bin_dist [options] package_basename [copy_def ...]

The package_basename argument provides the basename for the resulting ZIP archive. This name will be the name of the directory that results from unpacking the archive.

Because you might want to include files of directories from other builds, you can instruct this script do copy them in for you, via copy_def arguments, which of which is of the form relative_dest_name=source_name.

Example:

bin/mysqld-max.exe=../my-max-build/sql/release/mysqld.exe

If you specify a directory, the entire directory will be copied.

make_win_bin_dist understands the following options:

  • --debug

    Pack the debug binaries and produce an error if they were not built.

  • --embedded

    Pack the embedded server and produce an error if it was not built. The default is to pack it if it was built.

  • --exe-suffix=suffix

    Add a suffix to the basename of the mysql binary. For example, a suffix of -abc produces a binary named mysqld-abc.exe.

  • --no-debug

    Don't pack the debug binaries even if they were built.

  • --no-embedded

    Don't pack the embedded server even if it was built.

  • --only-debug

    Use this option when the target for this build was Debug, and you just want to replace the normal binaries with debug versions (that is, do not use separate debug directories).

4.4.3. make_win_src_distribution — Create Source Distribution for Windows

make_win_src_distribution creates a Windows source package to be used on Windows systems. It is used after you configure and build the source distribution on a Unix or Unix-like system so that you have a server binary to work with. (See the instructions at Section 2.4.15.6.5, “Creating a Windows Source Package from the Bazaar Repository”.)

Invoke make_win_src_distribution like this from the top-level directory of a MySQL source distribution:

shell> make_win_src_distribution [options]

make_win_src_distribution understands the following options:

  • --help

    Display a help message and exit.

  • --debug

    Print information about script operations; do not create a package.

  • --tmp

    Specify the temporary location.

  • --suffix

    The suffix name for the package.

  • --dirname

    Directory name to copy files (intermediate).

  • --silent

    Do not print verbose list of files processed.

  • --tar

    Create a tar.gz package instead of a .zip package.

    By default, make_win_src_distribution creates a Zip-format archive with the name mysql-VERSION-win-src.zip, where VERSION represents the version of your MySQL source tree.

4.4.4. mysqlbug — Generate Bug Report

This program enables you to generate a bug report and send it to MySQL AB. It is a shell script and runs on Unix.

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. If you have no Web access, you can generate a bug report by using the mysqlbug script.

mysqlbug helps you generate a report by determining much of the following information automatically, but if something important is missing, please include it with your message. mysqlbug can be found in the scripts directory (source distribution) and in the bin directory under your MySQL installation directory (binary distribution).

Invoke mysqlbug without arguments:

shell> mysqlbug

The script will place you in an editor with a copy of the report to be sent. Edit the lines near the beginning that indicate the nature of the problem. Then write the file to save your changes, quit the editor, and mysqlbug will send the report by email. perform.

4.4.5. mysql_fix_privilege_tables — Upgrade MySQL System Tables

Some releases of MySQL introduce changes to the structure of the system tables in the mysql database to add new privileges or support new features. When you update to a new version of MySQL, you should update your system tables as well to make sure that their structure is up to date. Otherwise, there might be capabilities that you cannot take advantage of. First, make a backup of your mysql database, and then use the following procedure.

Note

As of MySQL 5.0.19, mysql_fix_privilege_tables is superseded by mysql_upgrade, which should be used instead. See Section 4.4.9, “mysql_upgrade — Check Tables for MySQL Upgrade”.

On Unix or Unix-like systems, update the system tables by running the mysql_fix_privilege_tables script:

shell> mysql_fix_privilege_tables

You must run this script while the server is running. It attempts to connect to the server running on the local host as root. If your root account requires a password, indicate the password on the command line like this:

shell> mysql_fix_privilege_tables --password=root_password

The mysql_fix_privilege_tables script performs any actions necessary to convert your system tables to the current format. You might see some Duplicate column name warnings as it runs; you can ignore them.

After running the script, stop the server and restart it so that it uses any changes that were made to the system tables.

On Windows systems, MySQL distributions include a mysql_fix_privilege_tables.sql SQL script that you can run using the mysql client. For example, if your MySQL installation is located at C:\Program Files\MySQL\MySQL Server 5.0, the commands look like this:

C:\> cd "C:\Program Files\MySQL\MySQL Server 5.0"
C:\> bin\mysql -u root -p mysql
mysql> SOURCE share/mysql_fix_privilege_tables.sql

Note

Prior to version 5.0.38, this script is found in the scripts directory.

The mysql command will prompt you for the root password; enter it when prompted.

If your installation is located in some other directory, adjust the pathnames appropriately.

As with the Unix procedure, you might see some Duplicate column name warnings as mysql processes the statements in the mysql_fix_privilege_tables.sql script; you can ignore them.

After running the script, stop the server and restart it.

4.4.6. 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. Because the MySQL server, mysqld, needs to access the data directory when it runs later, you should either run mysql_install_db from the same account that will be used for running mysqld or run it as root and use the --user option to indicate the username that mysqld will run as.

To invoke mysql_install_db, use the following syntax:

shell> mysql_install_db [options]

mysql_install_db needs to invoke mysqld with the --bootstrap and --skip-grant-tables options (see Section 2.4.15.2, “Typical configure Options”). If MySQL was configured with the --disable-grant-options option, --bootstrap and --skip-grant-tables will be disabled. To handle this, set the MYSQLD_BOOTSTRAP environment variable to the full pathname of a server that has all options enabled. mysql_install_db will use that server.

mysql_install_db supports the following options:

  • --basedir=path

    The path to the MySQL installation directory.

  • --force

    Causes mysql_install_db to run even if DNS does not work. In that case, grant table entries that normally use hostnames will use IP addresses.

  • --datadir=path, --ldata=path

    The path to the MySQL data directory.

  • --rpm

    For internal use. This option is used by RPM files during the MySQL installation process.

  • --skip-name-resolve

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

  • --srcdir=path

    For internal use. 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. This option was added in MySQL 5.0.32.

  • --user=user_name

    The login username to use for running mysqld. Files and directories created by mysqld will be owned by this user. You must be root 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.7. 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 anonymous users.

Invoke mysql_secure_installation without arguments:

shell> mysql_secure_installation

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

4.4.8. 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, Sun Solaris, and Mac 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 9.7, “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 pathname 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.9. mysql_upgrade — Check Tables for MySQL Upgrade

mysql_upgrade should be executed each time you upgrade MySQL. It checks all tables in all databases for incompatibilities with the current version of MySQL Server. If a table is found to have a possible incompatibility, it is checked. If any problems are found, the table is repaired. mysql_upgrade also upgrades the system tables so that you can take advantage of new privileges or capabilities that might have been added.

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 if all tables have been checked for this release so that table-checking can be skipped. To ignore this file, use the --force option.

Caution

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

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

mysqlcheck --check-upgrade --all-databases --auto-repair
mysql_fix_privilege_tables

mysql_upgrade supersedes the older mysql_fix_privilege_tables script. In MySQL 5.0.19, mysql_upgrade was added as a shell script and worked only for Unix systems. As of MySQL 5.0.25, mysql_upgrade is an executable binary and is available on all systems. On systems older than those supporting mysql_upgrade, you can execute the mysqlcheck command manually, and then upgrade your system tables as described in Section 4.4.5, “mysql_fix_privilege_tables — Upgrade MySQL System Tables”.

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.4.9, “Installing MySQL from RPM Packages on Linux”.)

For details about what is checked, see the description of the FOR UPGRADE option of the CHECK TABLE statement (see Section 12.5.2.3, “CHECK TABLE Syntax”).

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

shell> mysql_upgrade [options]

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

mysql_upgrade reads options from the command line and from the [mysql_upgrade] group in option files. It supports the options in the following list. Other options are passed to mysqlcheck and to mysql_fix_privilege_tables. For example, it might be necessary to specify the --password[=password] option.

  • --help

    Display a short help message and exit.

  • --basedir=path

    The path to the MySQL installation directory.

  • --datadir=path

    The path to the data directory.

  • --force

    Force execution of mysqlcheck even if mysql_upgrade has already been executed for the current version of MySQL. (In other words, this option causes the mysql_upgrade_info file to be ignored.)

  • --tmpdir=path, -t path

    The pathname of the directory to use for creating temporary files. This option was added in MySQL 5.0.62.

  • --user=user_name, -u user_name

    The MySQL username to use when connecting to the server. The default username is root.

  • --verbose

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

4.5. MySQL Client Programs

4.5.1. mysql — The MySQL Command-Line Tool

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

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

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

shell> mysql db_name

Or:

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

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

As of MySQL 5.0.25, 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. Previously, Control-C caused mysql to exit in all cases.

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

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

4.5.1.1. mysql Options

Table 4.2. mysql Option Reference

FormatConfig FileDescriptionIntroduction
--auto-rehashauto-rehashEnable automatic rehashing 
--batchbatchDon't use history file 
--character-sets-dir=namecharacter-sets-dirSet the default character set 
--column-namescolumn-namesWrite column names in results 
--commentscommentsWhether to retain or strip comments in statements sent to the server5.0.52
--compresscompressCompress all information sent between the client and the server 
--connect_timeout=valueconnect_timeoutThe number of seconds before connection timeout 
--database=dbnamedatabaseThe database to use 
--debug[=debug_options]debugWrite a debugging log 
--debug-infodebug-infoPrint debugging information, memory and CPU statistics when the program exits 
--default-character-set=charset_namedefault-character-setUse charset_name as the default character set 
--delimiter=strdelimiterSet the statement delimiter 
--execute=statementexecuteExecute the statement and quit 
--forceforceContinue even if an SQL error occurs 
--help Display help message and exit 
--host=host_namehostConnect to the MySQL server on the given host 
--htmlhtmlProduce HTML output 
--ignore-spacesignore-spacesIgnore spaces after function names 
--line-numbersline-numbersWrite line numbers for errors 
--local-infile[={0|1}]local-infileEnable or disable for LOCAL capability for LOAD DATA INFILE 
--max_allowed_packet=valuemax_allowed_packetThe maximum packet length to send to or receive from the server 
--max_join_size=valuemax_join_sizeThe automatic limit for rows in a join when using --safe-updates 
--named-commandsnamed-commandsEnable named mysql commands 
--net_buffer_length=valuenet_buffer_lengthThe buffer size for TCP/IP and socket communication 
--no-auto-rehash Disable automatic rehashing 
--no-beepno-beepDo not beep when errors occur 
--no-named-commandsno-named-commandsDisable named mysql commands 
--no-pagerno-pagerDeprecated form of --skip-pager 
--no-teeno-teeDo not copy output to a file 
--one-databaseone-databaseIgnore statements except those for the default database named on the command line 
--pager[=command]pagerUse the given command for paging query output 
--password[=password]passwordThe password to use when connecting to the server 
--port=port_numportThe TCP/IP port number to use for the connection 
--prompt=format_strpromptSet the prompt to the specified format 
--protocol=typeprotocolThe connection protocol to use 
--quickquickDo not cache each query result 
--rawrawWrite column values without escape conversion 
--reconnectreconnectIf the connection to the server is lost, automatically try to reconnect 
--safe-updatessafe-updatesAllow only UPDATE and DELETE statements that specify key values 
--secure-authsecure-authDo not send passwords to the server in old (pre-4.1.1) format 
--select_limit=valueselect_limitThe automatic limit for SELECT statements when using --safe-updates 
--show-warningsshow-warningsShow warnings after each statement if there are any5.0.6
--sigint-ignoresigint-ignoreIgnore SIGINT signals (typically the result of typing Control-C) 
--silentsilentSilent mode 
--skip-auto-rehashskip-auto-rehashDisable automatic rehashing 
--skip-column-namesskip-column-namesDo not write column names in results 
--skip-line-numbersskip-line-numbersSkip line numbers for errors 
--skip-named-commandsskip-named-commandsDisable named mysql commands 
--skip-pagerskip-pagerDisable paging 
--skip-reconnectskip-reconnectDisable reconnecting 
--socket=pathsocketFor connections to localhost 
--ssl-ca=file_namessl-caThe path to a file that contains a list of trusted SSL CAs 
--ssl-capath=directory_namessl-capathThe path to a directory that contains trusted SSL CA certificates in PEM format 
--ssl-cert=file_namessl-certThe name of the SSL certificate file to use for establishing a secure connection 
--ssl-cipher=cipher_listssl-cipherA list of allowable ciphers to use for SSL encryption 
--ssl-key=file_namessl-keyThe name of the SSL key file to use for establishing a secure connection 
--ssl-verify-server-certssl-verify-server-certThe server's Common Name value in its certificate is verified against the hostname used when connecting to the server 
--tabletableDisplay output in tabular format 
--tee=file_nameteeAppend a copy of output to the given file 
--unbufferedunbufferedFlush the buffer after each query 
--user=user_nameuserThe MySQL username to use when connecting to the server 
--verbose Verbose mode 
--version Display version information and exit 
--verticalverticalPrint query output rows vertically (one line per column value) 
--waitwaitIf the connection cannot be established, wait and retry instead of aborting 
--xmlxmlProduce XML output 

mysql supports the following options:

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

  • --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 non-tabular output format and escaping of special characters. Escaping may be disabled by using raw mode; see the description for the --raw option.

  • --character-sets-dir=path

    The directory where character sets are installed. See Section 9.2, “The Character Set Used for Data and Sorting”.

  • --column-names

    Write column names in results.

  • --comments, -c

    Whether to preserve comments in statements sent to the server. The default is --skip-comments (discard comments), enable with --comments (preserve comments). This option was added in MySQL 5.0.52.

  • --compress, -C

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

  • --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. The debug_options string often is 'd:t:o,file_name'. The default is 'd:t:o,/tmp/mysql.trace'.

  • --debug-info, -T

    Print some debugging information when the program exits.

  • --default-character-set=charset_name

    Use charset_name as the default character set. See Section 9.2, “The Character Set Used for Data and Sorting”.

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

  • --execute=statement, -e statement

    Execute the statement and quit. The default output format is like that produced with --batch. See Section 4.2.3.1, “Using Options on the Command Line”, for some examples.

  • --force, -f

    Continue even if an SQL error occurs.

  • --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.7, “SQL Modes”).

  • --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. With no value, the option enables LOCAL. The option may be given as --local-infile=0 or --local-infile=1 to explicitly disable or enable LOCAL. Enabling LOCAL has no effect if the server does not also support it.

    MySQL Enterprise For expert advice on the security implications of enabling LOCAL, subscribe to the MySQL Enterprise Monitor. For more information, see http://www.mysql.com/products/enterprise/advisors.html.

  • --named-commands, -G

    Enable named mysql commands. Long-format commands are allowed, 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

    Deprecated form of -skip-auto-rehash. Use --disable-auto-rehash instead. See the description for --auto-rehash.

  • --no-beep, -b

    Do not beep when errors occur.

  • --no-named-commands, -g

    Deprecated, use --disable-named-commands instead.

  • --no-pager

    Deprecated form of --skip-pager. See the --pager option.

  • --no-tee

    Do not copy output to a file. Section 4.5.1.2, “mysql Commands”, discusses tee files further.

  • --one-database, -o

    Ignore statements except those for the default database named on the command line. This is useful for skipping updates to other databases in the binary log.

  • --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. It does not work in batch 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, you are prompted for one.

    Specifying a password on the command line should be considered insecure. See Section 5.5.6, “Keeping Your Password Secure”.

  • --pipe, -W

    On Windows, connect to the server via a named pipe. This option applies only for connections to a local server, and only if the server supports named-pipe connections.

  • --port=port_num, -P port_num

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

  • --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 allowable 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 distinquished from another. For non-tabular 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 non-tabular 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

    Allow only those UPDATE and DELETE statements that specify which rows to modify by using key values. If you have set this option in an option file, you can override it by using --safe-updates on the command line. See Section 4.5.1.5, “mysql Tips”, for more information about this option.

  • --secure-auth

    Do not send passwords to the server in old (pre-4.1.1) format. This prevents connections except for servers that use the newer password format.

    MySQL Enterprise For expert advice on database security, subscribe to the MySQL Enterprise Monitor. For more information, see http://www.mysql.com/products/enterprise/advisors.html.

  • --show-warnings

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

  • --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 non-tabular 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. The short format, -N is deprecated, use the long format instead.

  • --skip-line-numbers, -L

    Do not write line numbers for errors. Useful when you want to compare result files that include error messages. The short format, -L is deprecated, use the long format instead.

  • --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 via SSL and indicate where to find SSL keys and certificates. See Section 5.5.7.3, “SSL Command Options”.

  • --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 does not work in batch mode. in 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 username 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.

    Note

    Prior to MySQL 5.0.26, there was no differentiation in the output when using this option between columns containing the NULL value and columns containing the string literal 'NULL'; both were represented as

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

    Beginning with MySQL 5.0.26, the output when --xml is used with mysql matches that of mysqldump --xml. See the section of the Manual which discusses the --xml option for mysqldump for details.

    Beginning with MySQL 5.0.40, the XML output also uses an XML namespace, as shown here:

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

    (See Bug#25946.)

You can also set the following variables by using --var_name=value. The --set-variable format is deprecated.

  • connect_timeout

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

  • max_allowed_packet

    The maximum packet length to send to or receive from the server. (Default value is 16MB.)

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

It is also possible to set variables by using --var_name=value. The --set-variable format is deprecated.

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

If you do not want to maintain a history file, first remove .mysql_history if it exists, and then use either of the following techniques:

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

  • Create .mysql_history as a symbolic link to /dev/null:

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

    You need do this only once.

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. NOTE: Takes the rest of the line as
               new 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'

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

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

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

    Displays 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.3, “mysql Server-Side Help”.

  • charset charset_name, \C charset_name

    The charset command changes the default character set and issues 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. This command was added in MySQL 5.0.19.

  • clear, \c

    Clears 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]]

    Reconnects to the server. The optional database name and hostname 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

    The delimiter command changes the string that mysql interprets as the separator between SQL statements. The default is the semicolon character (“;”). You should avoid the use of the backslash (“\”) character within the delimiter because that is the escape character for MySQL.

    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 26.2.9, “C API Handling of Multiple Statement Execution”), and for parsing the body of stored procedures and functions and triggers (see Section 21.2.2, “CREATE PROCEDURE and CREATE FUNCTION Syntax”).

  • edit, \e

    Edits 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

    Sends the current statement to the server to be executed and displays the result using vertical format.

  • exit, \q

    Exits mysql.

  • go, \g

    Sends the current statement to the server to be executed.

  • nopager, \n

    Disables output paging. See the description for pager.

    The nopager command works only in Unix.

  • notee, \t

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

  • nowarning, \w

    Enables display of warnings after each statement. This command was added in MySQL 5.0.6.

  • pager [command], \P [command]

    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.

    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

    Prints the current input statement without executing it.

  • prompt [str], \R [str]

    Reconfigures 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

    Exits mysql.

  • rehash, \#

    Rebuilds 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

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

  • status, \s

    The status command provides some information about the connection and the server you are using. If you are running in --safe-updates mode, status also prints the values for the mysql variables that affect your queries.

  • system command, \! command

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

    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

    Uses db_name as the default database.

  • warnings, \W

    Enables display of warnings after each statement (if there are any). This command was added in MySQL 5.0.6.

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 filesystems mounted on /dr1 and /dr2, yet still display the results onscreen via 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:

OptionDescription
\cA counter that increments for each statement you issue
\DThe full current date
\dThe default database
\hThe server host
\lThe current delimiter (new in 5.0.25)
\mMinutes of the current time
\nA newline character
\OThe current month in three-letter format (Jan, Feb, …)
\oThe current month in numeric format
\Pam/pm
\pThe current TCP/IP port or socket file
\RThe current time, in 24-hour military time (0-23)
\rThe current time, standard 12-hour time (1-12)
\SSemicolon
\sSeconds of the current time
\tA tab character
\UYour full user_name@host_name account name
\uYour username
\vThe server version
\wThe current day of the week in three-letter format (Mon, Tue, …)
\YThe current year, four digits
\yThe current year, two digits
\_A space
A space (a space follows the backslash)
\'Single quote
\"Double quote
\\A literal “\” backslash character
\xx, 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 allowable prompt options and the set of special escape sequences that are recognized in option files. (These sequences are listed in Section 4.2.3.2, “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 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.8, “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
   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-master-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 |
+---------------+-----------+

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

As of MySQL 5.0.54, 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.5. mysql Tips

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

4.5.1.5.1. 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.5.2. Using the --safe-updates Option

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

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

SET SQL_SAFE_UPDATES=1,SQL_SELECT_LIMIT=1000, SQL_MAX_JOIN_SIZE=1000000;

See Section 5.1.4, “Session System Variables”.

The SET statement has the following effects:

  • You are not allowed to execute an UPDATE or DELETE statement unless you 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;
    
  • The server limits all large SELECT results to 1,000 rows unless the statement includes a LIMIT clause.

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

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

shell> mysql --safe-updates --select_limit=500 --max_join_size=10000
4.5.1.5.3. 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 26.2.13, “Controlling Automatic Reconnect Behavior”.

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 commands described in the following list. 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.

  • drop db_name

    Delete the database named db_name and all its tables.

  • extended-status

    Display the server status variables and their values.

    MySQL Enterprise For expert advice on using server status variables, subscribe to the MySQL Enterprise Monitor. For more information see http://www.mysql.com/products/enterprise/advisors.html.

  • flush-hosts

    Flush all information in 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.

  • 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 5.4.8, “Password Hashing as of MySQL 4.1”.)

    MySQL Enterprise For expert advice on the security implications of using the old-password command, subscribe to the MySQL Enterprise Monitor. For more information, see http://www.mysql.com/products/enterprise/advisors.html.

  • password new-password

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

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

    shell> mysqladmin password "my new password"
    
  • ping

    Check whether the server is alive. 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 12.5.5.24, “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.

    MySQL Enterprise For expert advice on using server system variables, subscribe to the MySQL Enterprise Monitor. For more information see http://www.mysql.com/products/enterprise/advisors.html.

  • 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.2.4, “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.

  • Memory in use

    The amount of memory allocated directly by mysqld. This value is displayed only when MySQL has been compiled with --with-debug=full.

  • Maximum memory used

    The maximum amount of memory allocated directly by mysqld. This value is displayed only when MySQL has been compiled with --with-debug=full.

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.

Table 4.3. mysqladmin Option Reference

FormatConfig FileDescriptionIntroduction
--compresscompressCompress all information sent between the client and the server 
--connect_timeout=secondsconnect_timeoutThe number of seconds before connection timeout 
--count=#countThe number of iterations to make for repeated command execution 
--debug[=debug_options]debugWrite a debugging log 
--default-character-set=charset_namedefault-character-setUse charset_name as the default character set 
--forceforceContinue even if an SQL error occurs 
--help Display help message and exit 
--host=host_namehostConnect to the MySQL server on the given host 
--password[=password]passwordThe password to use when connecting to the server 
--pipe On Windows, connect to server via a named pipe 
--port=port_numportThe TCP/IP port number to use for the connection 
--protocol=typeprotocolThe connection protocol to use 
--relativerelativeShow the difference between the current and previous values when used with the --sleep option 
--shutdown_timeout=secondsshutdown_timeoutThe maximum number of seconds to wait for server shutdown 
--silentsilentSilent mode 
--sleep=delaysleepExecute commands repeatedly, sleeping for delay seconds in between 
--socket=pathsocketFor connections to localhost 
--ssl-ca=file_namessl-caThe path to a file that contains a list of trusted SSL CAs 
--ssl-capath=directory_namessl-capathThe path to a directory that contains trusted SSL CA certificates in PEM format 
--ssl-cert=file_namessl-certThe name of the SSL certificate file to use for establishing a secure connection 
--ssl-cipher=cipher_listssl-cipherA list of allowable ciphers to use for SSL encryption 
--ssl-key=file_namessl-keyThe name of the SSL key file to use for establishing a secure connection 
--ssl-verify-server-certssl-verify-server-certThe server's Common Name value in its certificate is verified against the hostname used when connecting to the server 
--user=user_name,userThe MySQL username to use when connecting to the server 
--verbose Verbose mode 
--version Display version information and exit 
--verticalverticalPrint query output rows vertically (one line per column value) 
--waitwaitIf the connection cannot be established, wait and retry instead of aborting 

mysqladmin supports the following options:

  • --help, -?

    Display a help message and exit.

  • --character-sets-dir=path

    The directory where character sets are installed. See Section 9.2, “The Character Set Used for Data and Sorting”.

  • --compress, -C

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

  • --count=N, -c N

    The number of iterations to make for repeated command execution if the --sleep option is given.

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

    Write a debugging log. The debug_options string often is 'd:t:o,file_name'. The default is 'd:t:o,/tmp/mysqladmin.trace'.

  • --default-character-set=charset_name

    Use charset_name as the default character set. See Section 9.2, “The Character Set Used for Data and Sorting”.

  • --force, -f

    Do not ask for confirmation for the drop db_name command. With multiple commands, continue even if an error occurs.

  • --host=host_name, -h host_name

    Connect to the MySQL server on the given host.

  • --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, you are prompted for one.

    Specifying a password on the command line should be considered insecure. See Section 5.5.6, “Keeping Your Password Secure”.

  • --pipe, -W

    On Windows, connect to the server via a named pipe. This option applies only for connections to a local server, and only if the server supports named-pipe connections.

  • --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 allowable values, see Section 4.2.2, “Connecting to the MySQL Server”.

  • --relative, -r

    Show the difference between the current and previous values when used with the --sleep option. Currently, this option works only with the extended-status command.

  • --silent, -s

    Exit silently if a connection to the server cannot be established.

  • --sleep=delay, -i delay

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

  • --socket=path, -S path

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

  • --ssl*

    Options that begin with --ssl specify whether to connect to the server via SSL and indicate where to find SSL keys and certificates. See Section 5.5.7.3, “SSL Command Options”.

  • --user=user_name, -u user_name

    The MySQL username to use when connecting to the server.

  • --verbose, -v

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

  • --version, -V

    Display version information and exit.

  • --vertical, -E

    Print output vertically. This is similar to --relative, but prints output vertically.

  • --wait[=count], -w[count]

    If the connection cannot be established, wait and retry instead of aborting. If a count value is given, it indicates the number of times to retry. The default is one time.

You can also set the following variables by using --var_name=value The --set-variable format is deprecated. syntax:

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

It is also possible to set variables by using --var_name=value. The --set-variable format is deprecated.

4.5.3. mysqlcheck — A Table Maintenance and Repair Program

The mysqlcheck client checks, repairs, optimizes, and analyzes tables.

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 check or repair your tables.

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 Chapter 12, SQL Statement Syntax.

The MyISAM storage engine supports all four statements, so mysqlcheck can be used to perform all four operations 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

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

There are three general ways to invoke mysqlcheck:

shell> mysqlcheck [options] db_name [tables]
shell> mysqlcheck [options] --databases db_name1 [db_name2 db_name3...]
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 following names can be used to change mysqlcheck default behavior:

mysqlrepairThe default option is --repair
mysqlanalyzeThe default option is --analyze
mysqloptimizeThe default option is --optimize

Table 4.4. mysqlcheck Option Reference

FormatConfig FileDescriptionIntroduction
--all-databasesall-databasesCheck all tables in all databases 
--all-in-1all-in-1Execute a single statement for each database that names all the tables from that database 
--analyzeanalyzeAnalyze the tables 
--auto-repairauto-repairIf a checked table is corrupted, automatically fix it 
--character-sets-dir=pathcharacter-sets-dirThe directory where character sets are installed 
--checkcheckCheck the tables for errors 
--check-only-changedcheck-only-changedCheck only tables that have changed since the last check 
--check-upgradecheck-upgradeInvoke CHECK TABLE with the FOR UPGRADE option5.0.19
--compresscompressCompress all information sent between the client and the server 
--databasesdatabasesProcess all tables in the named databases 
--debug[=debug_options]debugWrite a debugging log 
--default-character-set=charset_namedefault-character-setUse charset_name as the default character set 
--extendedextendedCheck and repair tables 
--fastfastCheck only tables that have not been closed properly 
--forceforceContinue even if an SQL error occurs 
--help Display help message and exit 
--host=host_namehostConnect to the MySQL server on the given host 
--medium-checkmedium-checkDo a check that is faster than an --extended operation 
--optimizeoptimizeOptimize the tables 
--password[=password]passwordThe password to use when connecting to the server 
--pipe On Windows, connect to server via a named pipe 
--port=port_numportThe TCP/IP port number to use for the connection 
--protocol=typeprotocolThe connection protocol to use 
--quickquickThe fastest method of checking 
--repairrepairPerform a repair that can fix almost anything except unique keys that are not unique 
--silentsilentSilent mode 
--socket=pathsocketFor connections to localhost 
--ssl-ca=file_namessl-caThe path to a file that contains a list of trusted SSL CAs 
--ssl-capath=directory_namessl-capathThe path to a directory that contains trusted SSL CA certificates in PEM format 
--ssl-cert=file_namessl-certThe name of the SSL certificate file to use for establishing a secure connection 
--ssl-cipher=cipher_listssl-cipherA list of allowable ciphers to use for SSL encryption 
--ssl-key=file_namessl-keyThe name of the SSL key file to use for establishing a secure connection 
--ssl-verify-server-certssl-verify-server-certThe server's Common Name value in its certificate is verified against the hostname used when connecting to the server 
--tablestablesOverrides the --databases or -B option 
--use-frmuse-frmFor repair operations on MyISAM tables 
--user=user_name,userThe MySQL username to use when connecting to the server 
--verbose Verbose mode 
--version Display version information and exit 

mysqlcheck supports the following options:

  • --help, -?

    Display a help message and exit.

  • --all-databases, -A

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

  • --all-in-1, -1

    Instead of issuing a statement for each table, execute a single statement for each database that names all the tables from that database to be processed.

  • --analyze, -a

    Analyze the tables.

    MySQL Enterprise For expert advice on optimizing tables, subscribe to the MySQL Enterprise Monitor. For more information, see http://www.mysql.com/products/enterprise/advisors.html.

  • --auto-repair

    If a checked table is corrupted, automatically fix it. Any necessary repairs are done after all tables have been checked.

  • --character-sets-dir=path

    The directory where character sets are installed. See Section 9.2, “The Character Set Used for Data and Sorting”.

  • --check, -c

    Check the tables for errors. This is the default operation.

  • --check-only-changed, -C

    Check only tables that have changed since the last check or that have not been closed properly.

  • --check-upgrade, -g

    Invoke CHECK TABLE with the FOR UPGRADE option to check tables for incompatibilities with the current version of the server. This option was added in MySQL 5.0.19.

  • --compress

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

  • --databases, -B

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

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

    Write a debugging log. A typical debug_options string is often 'd:t:o,file_name'.

  • --default-character-set=charset_name

    Use charset_name as the default character set. See Section 9.2, “The Character Set Used for Data and Sorting”.

  • --extended, -e

    If you are using this option to check tables, it ensures that they are 100% consistent but takes a long time.

    If you are using this option to repair tables, it runs an extended repair that may not only take a long time to execute, but may produce a lot of garbage rows also!

  • --fast, -F

    Check only tables that have not been closed properly.

  • --force, -f

    Continue even if an SQL error occurs.

  • --host=host_name, -h host_name

    Connect to the MySQL server on the given host.

  • --medium-check, -m

    Do a check that is faster than an --extended operation. This finds only 99.99% of all errors, which should be good enough in most cases.

  • --optimize, -o

    Optimize the tables.

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

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

    Specifying a password on the command line should be considered insecure. See Section 5.5.6, “Keeping Your Password Secure”.

  • --pipe, -W

    On Windows, connect to the server via a named pipe. This option applies only for connections to a local server, and only if the server supports named-pipe connections.

  • --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 allowable values, see Section 4.2.2, “Connecting to the MySQL Server”.

  • --quick, -q

    If you are using this option to check tables, it prevents the check from scanning the rows to check for incorrect links. This is the fastest check method.

    If you are using this option to repair tables, it tries to repair only the index tree. This is the fastest repair method.

  • --repair, -r

    Perform a repair that can fix almost anything except unique keys that are not unique.

  • --silent, -s

    Silent mode. Print only 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 via SSL and indicate where to find SSL keys and certificates. See Section 5.5.7.3, “SSL Command Options”.

  • --tables

    Overrides the --databases or -B option. All name arguments following the option are regarded as table names.

  • --use-frm

    For repair operations on MyISAM tables, get the table structure from the .frm file so that the table can be repaired even if the .MYI header is corrupted.

  • --user=user_name, -u user_name

    The MySQL username to use when connecting to the server.

  • --verbose, -v

    Verbose mode. Print information about the various stages of program operation.

  • --version, -V

    Display version information and exit.

4.5.4. mysqldump — A Database Backup Program

The mysqldump client is a backup program originally written by Igor Romanenko. It can be used to dump a database or a collection of databases for backup or transfer to another SQL server (not necessarily a MySQL server). The dump typically contains SQL statements to create the table, populate it, or both. However, mysqldump can also be used to generate files in CSV, other delimited text, or XML format.

If you are doing a backup on the server and your tables all are MyISAM tables, consider using the mysqlhotcopy instead because it can accomplish faster backups and faster restores. See Section 4.6.8, “mysqlhotcopy — A Database Backup Program”.

There are three general ways to invoke mysqldump:

shell> mysqldump [options] db_name [tables]
shell> mysqldump [options] --databases db_name1 [db_name2 db_name3...]
shell> mysqldump [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 dumped.

mysqldump does not dump the INFORMATION_SCHEMA database. If you name that database explicitly on the command line, mysqldump silently ignores it.

To get a list of the options your version of mysqldump supports, execute mysqldump --help.

Some mysqldump options are shorthand for groups of other options. --opt and --compact fall into this category. For example, use of --opt is the same as specifying --add-drop-table --add-locks --create-options --disable-keys --extended-insert --lock-tables --quick --set-charset. Note that all of the options that --opt stands for also are on by default because --opt is on by default.

To reverse the effect of a group option, uses its --skip-xxx form (--skip-opt or --skip-compact). It is also possible to select only part of the effect of a group option by following it with options that enable or disable specific features. Here are some examples:

  • To select the effect of --opt except for some features, use the --skip option for each feature. For example, to disable extended inserts and memory buffering, use --opt --skip-extended-insert --skip-quick. (As of MySQL 5.0, --skip-extended-insert --skip-quick is sufficient because --opt is on by default.)

  • To reverse --opt for all features except index disabling and table locking, use --skip-opt --disable-keys --lock-tables.

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.

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 in MySQL 5.0; 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, you should not use the --opt or --extended-insert option. Use --skip-opt instead.

Before MySQL 4.1.2, out-of-range numeric values such as -inf and inf, as well as NaN (not-a-number) values are dumped by mysqldump as NULL. You can see this using the following sample table:

mysql> CREATE TABLE t (f DOUBLE);
mysql> INSERT INTO t VALUES(1e+111111111111111111111);
mysql> INSERT INTO t VALUES(-1e111111111111111111111);
mysql> SELECT f FROM t;
+------+
| f    |
+------+
|  inf |
| -inf |
+------+

For this table, mysqldump produces the following data output:

--
-- Dumping data for table `t`
--

INSERT INTO t VALUES (NULL);
INSERT INTO t VALUES (NULL);

The significance of this behavior is that if you dump and restore the table, the new table has contents that differ from the original contents. This problem is fixed as of MySQL 4.1.2; you cannot insert inf in the table, so this mysqldump behavior is only relevant when you deal with old servers.

Table 4.5. mysqldump Option Reference

FormatConfig FileDescriptionIntroduction
--add-drop-databaseadd-drop-databaseAdd a DROP DATABASE statement before each CREATE DATABASE statement 
--add-drop-tableadd-drop-tableAdd a DROP TABLE statement before each CREATE TABLE statement 
--add-locksadd-locksSurround each table dump with LOCK TABLES and UNLOCK TABLES statements 
--all-databasesall-databasesDump all tables in all databases 
--allow-keywordsallow-keywordsAllow creation of column names that are keywords 
--all-tablespacesall-tablespacesAdds to a table dump all SQL statements needed to create any tablespaces used by an NDB Cluster table 
--commentscommentsAdd comments to the dump file 
--compactcompactProduce less verbose output 
--compatible=name[,name,...]compatibleProduce output that is more compatible with other database systems or with older MySQL servers 
--complete-insertcomplete-insertUse complete INSERT statements that include column names 
--create-optionscreate-optionsInclude all MySQL-specific table options in the CREATE TABLE statements 
--databasesdatabasesDump several databases 
--debug[=debug_options]debugWrite a debugging log 
--debug-infodebug-infoPrint debugging information, memory and CPU statistics when the program exits5.0.32
--delayed-insertdelayed-insertWrite INSERT DELAYED statements rather than INSERT statements 
--delete-master-logsdelete-master-logsOn a master replication server, delete the binary logs after performing the dump operation 
--disable-keysdisable-keysFor each table, surround the INSERT statements with disable and enable keys statements 
--dump-datedump-dateInclude dump date in "Dump completed on" comment if --comments is given5.0.52
-EeventsDump events from the dumped databases 
--extended-insertextended-insertUse multiple-row INSERT syntax that include several VALUES lists 
--fields-enclosed-by=stringfields-enclosed-byThis option is used with the -T option and has the same meaning as the corresponding clause for LOAD DATA INFILE 
--fields-escaped-byfields-escaped-byThis option is used with the -T option and has the same meaning as the corresponding clause for LOAD DATA INFILE 
--fields-optionally-enclosed-by=stringfields-optionally-enclosed-byThis option is used with the -T option and has the same meaning as the corresponding clause for LOAD DATA INFILE 
--fields-terminated-by=stringfields-terminated-byThis option is used with the -T option and has the same meaning as the corresponding clause for LOAD DATA INFILE 
--lock-all-tablesfirst-slaveDeprecated. Now renamed to --lock-all-tables 
--flush-logsflush-logsFlush the MySQL server log files before starting the dump 
--flush-privilegesflush-privilegesEmit a FLUSH PRIVILEGES statement after dumping the mysql database 
--help Display help message and exit 
--hex-blobhex-blobDump binary columns using hexadecimal notation (for example, 'abc' becomes 0x616263) 
--ignore-table=db_name.tbl_nameignore-tableDo not dump the given table 
--insert-ignoreinsert-ignoreWrite INSERT statements with the IGNORE option 
--lines-terminated-by=stringlines-terminated-byThis option is used with the -T option and has the same meaning as the corresponding clause for LOAD DATA INFILE 
--lock-all-tableslock-all-tablesLock all tables across all databases 
--lock-tableslock-tablesLock all tables before dumping them 
--log-error=file_namelog-errorAppend warnings and errors to the named file5.0.42
--master-data[=value]master-dataWrite the binary log filename and position to the output 
--max_allowed_packet=valuemax_allowed_packetThe maximum packet length to send to or receive from the server 
--net_buffer_length=valuenet_buffer_lengthThe buffer size for TCP/IP and socket communication 
--no-autocommitno-autocommitEnclose the INSERT statements for each dumped table within SET AUTOCOMMIT=0 and COMMIT statements 
--no-create-dbno-create-dbThis option suppresses the CREATE DATABASE statements 
--no-create-infono-create-infoDo not write CREATE TABLE statements that re-create each dumped table 
--no-datano-dataDo not write any table row information (that is, do not dump table contents) 
--no-set-namesno-set-namesTurn off complete-insert 
--optoptThis option is shorthand; it is the same as specifying --add-drop-table --add-locks --create-options --disable-keys --extended-insert --lock-tables --quick --set-charset. 
--order-by-primaryorder-by-primarySorts each table's rows by its primary key, or by its first unique index 
--password[=password]passwordThe password to use when connecting to the server 
--pipe On Windows, connect to server via a named pipe 
--port=port_numportThe TCP/IP port number to use for the connection 
--quickquickRetrieve rows for a table from the server a row at a time 
--quote-namesquote-namesQuote database, table, and column names within backtick characters 
--replacereplaceWrite REPLACE statements rather than INSERT statements 
--result-file=fileresult-fileDirect output to a given file 
-RroutinesDump stored routines (functions and procedures) from the dumped databases 
--set-charsetset-charsetAdd SET NAMES default_character_set to the output 
--single-transactionsingle-transactionThis option issues a BEGIN SQL statement before dumping data from the server 
--skip-add-drop-tableskip-add-drop-tableDo not add 
--skip-add-locksskip-add-locksDo not add locks 
--skip-commentsskip-commentsDo not add comments to the dump file 
--skip-compactskip-compactTurn off compact 
--skip-disable-keysskip-disable-keysDo not disable keys 
--skip-extended-insertskip-extended-insertTurn off extended-insert 
--skip-optskip-optTurn off the options set by opt 
--skip-quickskip-quickDo not retrieve rows for a table from the server a row at a time 
--skip-quote-namesskip-quote-namesTurn off quote names 
-skip-set-charsetskip-set-charsetSuppress the SET NAMES statement 
--skip-triggersskip-triggersTurn off triggers5.0.11
--skip-tz-utcskip-tz-utcTurn off tz-utc 
--ssl-ca=file_namessl-caThe path to a file that contains a list of trusted SSL CAs 
--ssl-capath=directory_namessl-capathThe path to a directory that contains trusted SSL CA certificates in PEM format 
--ssl-cert=file_namessl-certThe name of the SSL certificate file to use for establishing a secure connection 
--ssl-cipher=cipher_listssl-cipherA list of allowable ciphers to use for SSL encryption 
--ssl-key=file_namessl-keyThe name of the SSL key file to use for establishing a secure connection 
--ssl-verify-server-certssl-verify-server-certThe server's Common Name value in its certificate is verified against the hostname used when connecting to the server 
--tab=pathtabProduce tab-separated data files 
--tablestablesOverride the --databases or -B option 
--triggerstriggersDump triggers for each dumped table 
--tz-utctz-utcAdd SET TIME_ZONE='+00:00' to the dump file 
--verbose Verbose mode 
--version Display version information and exit 
--where='where_condition'whereDump only rows selected by the given WHERE condition 
--xmlxmlProduce XML output 

mysqldump supports the following options:

  • --help, -?

    Display a help message and exit.

  • --add-drop-database

    Add a DROP DATABASE statement before each CREATE DATABASE statement.

  • --add-drop-table

    Add a DROP TABLE statement before each CREATE TABLE statement.

  • --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 7.2.18, “Speed of INSERT Statements”.

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

  • --allow-keywords

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

  • --character-sets-dir=path

    The directory where character sets are installed. See Section 9.2, “The Character Set Used for Data and Sorting”.

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

  • --compact

    Produce less verbose output. This option enables the --skip-add-drop-table, --skip-add-locks, --skip-comments, --skip-disable-keys, and --skip-set-charset options.

    Note

    Prior to release 5.0.48, this option did not create valid SQL if the database dump contained views. The recreation of views requires the creation and removal of temporary tables and this option suppressed the removal of those temporary tables. As a workaround, use --compact with the --add-drop-table option and then manually adjust the dump file.

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

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

  • --complete-insert, -c

    Use complete INSERT statements that include column names.

  • --compress, -C

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

  • --create-options

    Include all MySQL-specific table options in the CREATE TABLE statements.

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

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

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

  • --debug-info

    Print debugging information and memory and CPU usage statistics when the program exits. This option was added in MySQL 5.0.32.

  • --default-character-set=charset_name

    Use charset_name as the default character set. See Section 9.2, “The Character Set Used for Data and Sorting”. If no character set is specified, mysqldump uses utf8, and earlier versions use latin1.

    This option has no effect for output data files produced by using the --tab option. See the description for that option.

  • --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. This option automatically enables --master-data.

  • --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 non-unique indexes of MyISAM tables.

  • --dump-date

    mysqldump produces a -- Dump completed on DATE comment at the end of the dump if the --comments option is given. However, the date causes dump files for identical data take at different times to appear to be different. --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. This option was added in MySQL 5.0.52.

  • --extended-insert, -e

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

  • --fields-terminated-by=..., --fields-enclosed-by=..., --fields-optionally-enclosed-by=..., --fields-escaped-by=...

    These options are used with the -T option and have the same meaning as the corresponding clauses for LOAD DATA INFILE. See Section 12.2.5, “LOAD DATA INFILE Syntax”.

  • --first-slave, -x

    Deprecated. Now renamed to --lock-all-tables.

  • --flush-logs, -F

    Flush the MySQL server log files before starting the dump. This option requires the RELOAD privilege. Note that if you use this option in combination with the --all-databases (or -A) option, the logs are flushed for each database dumped. The exception is when using --lock-all-tables or --master-data: In this case, the logs are flushed only once, corresponding to the moment that all tables are locked. If you want your dump and the log flush to happen at exactly the same moment, you should use --flush-logs together with either --lock-all-tables or --master-data.

  • --flush-privileges

    Emit a FLUSH PRIVILEGES statement 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. This option was added in MySQL 5.0.26.

  • --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 defintion 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 a SQL comment containing the view definition to the dump output and continues executing.

  • --host=host_name, -h host_name

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

  • --hex-blob

    Dump binary columns using hexadecimal notation (for example, 'abc' becomes 0x616263). The affected data types are BINARY, VARBINARY, and BLOB. As of MySQL 5.0.13, BIT columns are affected as well.

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

  • --insert-ignore

    Write INSERT statements with the IGNORE option.

  • --lines-terminated-by=...

    This option is used with the -T option and has the same meaning as the corresponding clause for LOAD DATA INFILE. See Section 12.2.5, “LOAD DATA INFILE Syntax”.

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

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

    Please note that when dumping multiple databases, --lock-tables locks tables for each database separately. Therefore, 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.

  • --log-error=file_name

    Append warnings and errors to the named file. This option was added in MySQL 5.0.42.

  • --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 (filename and position) of the dumped server. These are the master server coordinates from which the slave should start replicating.

    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 takes effect when the dump file is reloaded. If the option value is not 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. To do this, use the following procedure on the existing slave:

    1. Stop the slave's SQL thread and get its current status:

      mysql> STOP SLAVE SQL_THREAD;
      mysql> SHOW SLAVE STATUS;
      
    2. From the output of the SHOW SLAVE STATUS statement, get the binary log coordinates of the master server from which the new slave should start replicating. These coordinates are the values of the Relay_Master_Log_File and Exec_Master_Log_Pos values. Denote those values as file_name and file_pos.

    3. Dump the slave server:

      shell> mysqldump --master-data=2 --all-databases > dumpfile
      
    4. Restart the slave:

      mysql> START SLAVE;
      
    5. On the new slave, reload the dump file:

      shell> mysql < dumpfile
      
    6. On the new slave, set the replication coordinates to those of the master server obtained earlier:

      mysql> CHANGE MASTER TO
          -> MASTER_LOG_FILE = 'file_name', MASTER_LOG_POS = file_pos;
      

      The CHANGE MASTER TO statement might also need other parameters, such as MASTER_HOST to point the slave to the correct master server host. Add any such parameters as necessary.

  • --no-autocommit

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

  • --no-create-db, -n

    This option suppresses the CREATE DATABASE statements that are otherwise included in the output if the --databases or --all-databases option is given.

  • --no-create-info, -t

    Do not write CREATE TABLE statements that re-create each dumped table.

  • --no-data, -d

    Do not write any table row information (that is, do not dump table contents). This is very useful if you want to dump only the CREATE TABLE statement for the table.

  • --opt

    This option is shorthand; it is the same as specifying --add-drop-table --add-locks --create-options --disable-keys --extended-insert --lock-tables --quick --set-charset. It should give you a fast dump operation and produce a dump file that can be reloaded into a MySQL server quickly.

    The --opt option is enabled by default. Use --skip-opt to disable it. See the discussion at the beginning of this section for information about selectively enabling or disabling certain of the options affected by --opt.

  • --order-by-primary

    Sorts each table's rows 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 will make the dump itself take considerably longer.

  • --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, you are prompted for one.

    Specifying a password on the command line should be considered insecure. See Section 5.5.6, “Keeping Your Password Secure”.

  • --pipe, -W

    On Windows, connect to the server via a named pipe. This option applies only for connections to a local server, and only if the server supports named-pipe connections.

  • --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 allowable values, see Section 4.2.2, “Connecting to the MySQL Server”.

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

  • --quote-names, -Q

    Quote database, table, and column names within “`” characters. If the ANSI_QUOTES SQL mode is enabled, names 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 a given file. This option should be used on Windows to prevent newline “\n” characters from being converted to “\r\n” carriage return/newline sequences. The result file is created and its contents overwritten, even if an error occurs while generating the dump. The previous contents are lost.

  • --routines, -R

    Dump stored routines (functions and procedures) from the dumped databases. Use of 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 re-create the routines. However, these statements do not include attributes such as the routine creation and modification timestamps. This means that when the routines are reloaded, they will be created with the timestamps equal to the reload time.

    If you require routines to be re-created with their original timestamp attributes, do not use --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.

    This option was added in MySQL 5.0.13. Before that, stored routines are not dumped. Routine DEFINER values are not dumped until MySQL 5.0.20. This means that before 5.0.20, when routines are reloaded, they will be created with the definer set to the reloading user. If you require routines to be re-created with their original definer, dump and load the contents of the mysql.proc table directly as described earlier.

  • --set-charset

    Add SET NAMES default_character_set to the output. This option is enabled by default. To suppress the SET NAMES statement, use --skip-set-charset.

  • --single-transaction

    This option issues a BEGIN SQL statement before dumping data from the server. It is useful only with transactional tables such as InnoDB and BDB, because then it dumps the consistent state of the database at the time when BEGIN 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.

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

    While a --single-transaction dump is in process, to ensure a valid dump file (correct table contents and binary log position), no other connection should use the following statements: ALTER 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 performed by mysqldump to retrieve the table contents to obtain incorrect contents or fail.

    This option is not supported for MySQL Cluster tables; the results cannot be guaranteed to be consistent due to the fact that the NDBCLUSTER storage engine supports only the READ_COMMITTED transaction isolation level. You should always use NDB backup and restore instead.

    To dump large tables, you should combine this option with --quick.

  • --skip-comments

    See the description for the --comments option.

  • --skip-opt

    See the description for the --opt option.

  • --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 via SSL and indicate where to find SSL keys and certificates. See Section 5.5.7.3, “SSL Command Options”.

  • --tab=path, -T path

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

    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 dumped using the binary character set and the --default-character-set option is ignored. In effect, there is no character set conversion. If a table contains columns in several character sets, the output data file will as well and you may not be able to reload the file correctly.

    Note

    This option should be used only when mysqldump is run on the same machine as the mysqld server. You must have the FILE privilege, and the server must have permission to write files in the directory that you specify.

  • --tables

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

  • --triggers

    Dump triggers for each dumped table. This option is enabled by default; disable it with --skip-triggers. This option was added in MySQL 5.0.11. Before that, triggers are not dumped.

  • --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. --tz-utc also protects against changes due to daylight saving time. --tz-utc is enabled by default. To disable it, use --skip-tz-utc. This option was added in MySQL 5.0.15.

  • --user=user_name, -u user_name

    The MySQL username to use when connecting to the server.

  • --verbose, -v

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

  • --version, -V

    Display version information and exit.

  • --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"
    
  • --xml, -X

    Write dump output as well-formed XML.

    NULL, 'NULL', and Empty Values: For some 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>

    Beginning with MySQL 5.0.26, the output from the mysql client when run using the --xml option also follows these rules. (See Section 4.5.1.1, “mysql Options”.)

    Beginning with MySQL 5.0.40, 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="27329
    3" Max_data_length="18858823439613951" Index_length="43008" Data_free="0" Auto_increment="4080" Create_time="2007-03-31 01:47:01" Updat
    e_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>
    

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 maximum is 1GB.

  • net_buffer_length

    The initial size of the buffer for client/server communication. When creating multiple-row-insert statements (as with option --extended-insert or --opt), mysqldump creates rows up to net_buffer_length length. If you increase this variable, you should also ensure that the net_buffer_length variable in the MySQL server is at least this large.

It is also possible to set variables by using --var_name=value. The --set-variable format is deprecated.

The most common use of mysqldump is probably for making a backup of an entire database:

shell> mysqldump db_name > backup-file.sql

You can read the dump file back into the server like this:

shell> mysql db_name < backup-file.sql

Or like this:

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

It is possible to 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 --single-transaction > all_databases.sql

This backup just needs to acquire 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 and only if one long updating statement is running when the FLUSH statement is issued, the MySQL server may get stalled until that long statement finishes, and then the dump becomes lock-free. 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.2.3, “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 point-in-time recovery if tables are stored using the InnoDB storage engine.

For more information on making backups, see Section 6.1, “Database Backups”, and Section 6.2, “Example Backup and Recovery Strategy”.

If you encounter problems backing up views, please read the section that covers restrictions on views which describes a workaround for backing up views when this fails due to insufficient privileges. See Section F.4, “Restrictions on Views”.

MySQL Enterprise MySQL Enterprise subscribers will find more information about mysqldump in the Knowledge Base article, How Can I Avoid Inserting Duplicate Rows From a Dump File?. Access to the MySQL Knowledge Base collection of articles is one of the advantages of subscribing to MySQL Enterprise. For more information, see http://www.mysql.com/products/enterprise/advisors.html.

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 12.2.5, “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 filename 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.

Table 4.6. mysqlimport Option Reference

FormatConfig FileDescriptionIntroduction
--columns=column_listcolumnsThis option takes a comma-separated list of column names as its value 
--compresscompressCompress all information sent between the client and the server 
--debug[=debug_options]debugWrite a debugging log 
--default-character-set=charset_namedefault-character-setUse charset_name as the default character set 
--deletedeleteEmpty the table before importing the text file 
--fields-enclosed-by=stringfields-enclosed-byThis option has the same meaning as the corresponding clause for LOAD DATA INFILE 
--fields-escaped-byfields-escaped-byThis option has the same meaning as the corresponding clause for LOAD DATA INFILE 
--fields-optionally-enclosed-by=stringfields-optionally-enclosed-byThis option has the same meaning as the corresponding clause for LOAD DATA INFILE 
--fields-terminated-by=stringfields-terminated-by-- This option has the same meaning as the corresponding clause for LOAD DATA INFILE 
--forceforceContinue even if an SQL error occurs 
--help Display help message and exit 
--host=host_namehostConnect to the MySQL server on the given host 
--ignoreignoreSee the description for the --replace option 
--ignore-lines=#ignore-linesIgnore the first N lines of the data file 
--lines-terminated-by=stringlines-terminated-byThis option has the same meaning as the corresponding clause for LOAD DATA INFILE 
--locallocalRead input files locally from the client host 
--lock-tableslock-tablesLock all tables for writing before processing any text files 
--low-prioritylow-priorityUse LOW_PRIORITY when loading the table. 
--password[=password]passwordThe password to use when connecting to the server 
--pipe On Windows, connect to server via a named pipe 
--port=port_numportThe TCP/IP port number to use for the connection 
--protocol=typeprotocolThe connection protocol to use 
--replacereplaceThe --replace and --ignore options control handling of input rows that duplicate existing rows on unique key values 
--silentsilentProduce output only when errors occur 
--socket=pathsocketFor connections to localhost 
--ssl-ca=file_namessl-caThe path to a file that contains a list of trusted SSL CAs 
--ssl-capath=directory_namessl-capathThe path to a directory that contains trusted SSL CA certificates in PEM format 
--ssl-cert=file_namessl-certThe name of the SSL certificate file to use for establishing a secure connection 
--ssl-cipher=cipher_listssl-cipherA list of allowable ciphers to use for SSL encryption 
--ssl-key=file_namessl-keyThe name of the SSL key file to use for establishing a secure connection 
--ssl-verify-server-certssl-verify-server-certThe server's Common Name value in its certificate is verified against the hostname used when connecting to the server 
--user=user_name,userThe MySQL username to use when connecting to the server 
--verbose Verbose mode 
--version Display version information and exit 

mysqlimport supports the following options:

  • --help, -?

    Display a help message and exit.

  • --character-sets-dir=path

    The directory where character sets are installed. See Section 9.2, “The Character Set Used for Data and Sorting”.

  • --columns=column_list, -c column_list

    This option takes a comma-separated list of column names as its value. The order of the column names indicates how to match data file columns with table columns.

  • --compress, -C

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

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

    Write a debugging log. The debug_options string often is 'd:t:o,file_name'.

  • --default-character-set=charset_name

    Use charset_name as the default character set. See Section 9.2, “The Character Set Used for Data and Sorting”.

  • --delete, -D

    Empty the table before importing the text file.

  • --fields-terminated-by=..., --fields-enclosed-by=..., --fields-optionally-enclosed-by=..., --fields-escaped-by=...

    These options have the same meaning as the corresponding clauses for LOAD DATA INFILE. See Section 12.2.5, “LOAD DATA INFILE Syntax”.

  • --force, -f

    Ignore errors. For example, if a table for a text file does not exist, continue processing any remaining files. Without --force, mysqlimport exits if a table does not exist.

  • --host=host_name, -h host_name

    Import data to the MySQL server on the given host. The default host is localhost.

  • --ignore, -i

    See the description for the --replace option.

  • --ignore-lines=N

    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. For example, to import Windows files that have lines terminated with carriage return/linefeed pairs, use --lines-terminated-by="\r\n". (You might have to double the backslashes, depending on the escaping conventions of your command interpreter.) See Section 12.2.5, “LOAD DATA INFILE Syntax”.

  • --local, -L

    Read input files locally from the client host.

    MySQL Enterprise For expert advice on the security implications of enabling LOCAL, subscribe to the MySQL Enterprise Monitor. For more information, see http://www.mysql.com/products/enterprise/advisors.html.

  • --lock-tables, -l

    Lock all tables for writing before processing any text files. This ensures that all tables are synchronized on the server.

  • --low-priority

    Use LOW_PRIORITY when loading the table. This affects only storage engines that use only table-level locking (MyISAM, MEMORY, MERGE).

  • --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, you are prompted for one.

    Specifying a password on the command line should be considered insecure. See Section 5.5.6, “Keeping Your Password Secure”.

  • --pipe, -W

    On Windows, connect to the server via a named pipe. This option applies only for connections to a local server, and only if the server supports named-pipe connections.

  • --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 allowable values, see Section 4.2.2, “Connecting to the MySQL Server”.

  • --replace, -r

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

  • --silent, -s

    Silent mode. Produce output only when errors occur.

  • --socket=path, -S path

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

  • --ssl*

    Options that begin with --ssl specify whether to connect to the server via SSL and indicate where to find SSL keys and certificates. See Section 5.5.7.3, “SSL Command Options”.

  • --user=user_name, -u user_name

    The MySQL username to use when connecting to the server.

  • --verbose, -v

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

  • --version, -V

    Display version information and exit.

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

Table 4.7. mysqlshow Option Reference

FormatConfig FileDescriptionIntroduction
--compresscompressCompress all information sent between the client and the server 
--countcountShow the number of rows per table5.0.6
--debug[=debug_options]debugWrite a debugging log 
--default-character-set=charset_namedefault-character-setUse charset_name as the default character set 
--help Display help message and exit 
--host=host_namehostConnect to the MySQL server on the given host 
--keyskeysShow table indexes 
--password[=password]passwordThe password to use when connecting to the server 
--pipe On Windows, connect to server via a named pipe 
--port=port_numportThe TCP/IP port number to use for the connection 
--protocol=typeprotocolThe connection protocol to use 
--show-table-type Show a column indicating the table type5.0.4
--socket=pathsocketFor connections to localhost 
--ssl-ca=file_namessl-caThe path to a file that contains a list of trusted SSL CAs 
--ssl-capath=directory_namessl-capathThe path to a directory that contains trusted SSL CA certificates in PEM format 
--ssl-cert=file_namessl-certThe name of the SSL certificate file to use for establishing a secure connection 
--ssl-cipher=cipher_listssl-cipherA list of allowable ciphers to use for SSL encryption 
--ssl-key=file_namessl-keyThe name of the SSL key file to use for establishing a secure connection 
--ssl-verify-server-certssl-verify-server-certThe server's Common Name value in its certificate is verified against the hostname used when connecting to the server 
--statusstatusDisplay extra information about each table 
--user=user_name,userThe MySQL username to use when connecting to the server 
--verbose Verbose mode 
--version Display version information and exit 

mysqlshow supports the following options:

  • --help, -?

    Display a help message and exit.

  • --character-sets-dir=path

    The directory where character sets are installed. See Section 9.2, “The Character Set Used for Data and Sorting”.

  • --compress, -C

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

  • --count

    Show the number of rows per table. This can be slow for non-MyISAM tables. This option was added in MySQL 5.0.6.

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

    Write a debugging log. The debug_options string often is 'd:t:o,file_name'.

  • --default-character-set=charset_name

    Use charset_name as the default character set. See Section 9.2, “The Character Set Used for Data and Sorting”.

  • --host=host_name, -h host_name

    Connect to the MySQL server on the given host.

  • --keys, -k

    Show table indexes.

  • --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, you are prompted for one.

    Specifying a password on the command line should be considered insecure. See Section 5.5.6, “Keeping Your Password Secure”.

    Specifying a password on the command line should be considered insecure. See Section 5.5.6, “Keeping Your Password Secure”.

  • --pipe, -W

    On Windows, connect to the server via a named pipe. This option applies only for connections to a local server, and only if the server supports named-pipe connections.

  • --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 allowable values, see Section 4.2.2, “Connecting to the MySQL Server”.

  • --show-table-type, -t

    Show a column indicating the table type, as in SHOW FULL TABLES. The type is BASE TABLE or VIEW. This option was added in MySQL 5.0.4.

  • --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 via SSL and indicate where to find SSL keys and certificates. See Section 5.5.7.3, “SSL Command Options”.

  • --status, -i

    Display extra information about each table.

  • --user=user_name, -u user_name

    The MySQL username to use when connecting to the server.

  • --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.6. MySQL Administrative and Utility Programs

4.6.1. innochecksum — Offline InnoDB File Checksum Utility

innochecksum prints checksums for InnoDB files.

Invoke innochecksum like this:

shell> innochecksum [options] file_name

innodchecksum understands the options described in the following list. 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

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 pathname to the table's database directory. Index numbers begin with 0.

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

CREATE TABLE mytexttable
(
  id   INT NOT NULL,
  txt  TEXT NOT NULL,
  PRIMARY KEY (id),
  FULLTEXT (txt)
);

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 pathname to the test database directory is /usr/local/mysql/data/test, you can also specify the table name argument using that pathname. This is useful if you do not invoke myisam_ftdump in the database directory:

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

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

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 filesystem 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 doesn't 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 allows 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 --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.5, “myisamchk Memory Usage”.

MySQL Enterprise For expert advice on checking and repairing tables, subscribe to the MySQL Enterprise Monitor. For more information, see http://www.mysql.com/products/enterprise/advisors.html.

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 12.5.2.3, “CHECK TABLE Syntax”.

A complete listing of all the myisamchk options follows.

Table 4.8. myisamchk Option Reference

FormatConfig FileDescriptionIntroduction
--analyzeanalyzeAnalyze the distribution of key values 
--backupbackupMake a backup of the .MYD file as file_name-time.BAK 
--block-search=offsetblock-searchFind the record that a block at the given offset belongs to 
--checkcheckCheck the table for errors 
--check-only-changedcheck-only-changedCheck only tables that have changed since the last check 
--correct-checksumcorrect-checksumCorrect the checksum information for the table 
--data-file-length=lendata-file-lengthMaximum length of the data file (when re-creating data file when it is full) 
--debug[=debug_options]debugWrite a debugging log 
decode_bits=#decode_bitsDecode_bits 
--descriptiondescriptionPrint some descriptive information about the table 
--extend-checkextend-checkDo a repair that tries to recover every possible row from the data file 
--extended-checkextended-checkCheck the table very thoroughly 
--fastfastCheck only tables that haven't been closed properly 
--forceforceDo a repair operation automatically if myisamchk finds any errors in the table 
--forceforce-recoverOverwrite old temporary files. For use with the -r or -o option 
ft_max_word_len=#ft_max_word_lenMaximum word length for FULLTEXT indexes 
ft_min_word_len=#ft_min_word_lenMinimum word length for FULLTEXT indexes 
ft_stopword_file=valueft_stopword_fileUse stopwords from this file instead of built-in list 
--help Display help message and exit 
--informationinformationPrint informational statistics about the table that is checked 
key_buffer_size=#key_buffer_sizeThe size of the buffer used for index blocks for MyISAM tables 
--keys-used=valkeys-usedA bit-value that indicates which indexes to update 
--max-record-length=lenmax-record-lengthSkip rows larger than the given length if myisamchk cannot allocate memory to hold them 
--medium-checkmedium-checkDo a check that is faster than an --extend-check operation 
myisam_block_size=#myisam_block_sizeBlock size to be used for MyISAM index pages 
--parallel-recoverparallel-recoverUses the same technique as -r and -n, but creates all the keys in parallel, using different threads (beta) 
--quickquickAchieve a faster repair by not modifying the data file. 
read_buffer_size=#read_buffer_sizeEach thread that does a sequential scan allocates a buffer of this size for each table it scans 
--read-onlyread-onlyDon't mark the table as checked 
--recoverrecoverDo a repair that can fix almost any problem except unique keys that aren't unique 
--safe-recoversafe-recoverDo 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[=value]set-auto-incrementForce AUTO_INCREMENT numbering for new records to start at the given value 
--set-character-set=nameset-character-setChange the character set used by the table indexes 
--set-collation=nameset-collationSpecify the collation to use for sorting table indexes 
--silentsilentSilent mode 
sort_buffer_size=#sort_buffer_sizeThe buffer that is allocated when sorting the index when doing a REPAIR or when creating indexes with CREATE INDEX or ALTER TABLE 
--sort-indexsort-indexSort the index tree blocks in high-low order 
sort_key_blocks=#sort_key_blockssort_key_blocks 
--sort-records=#sort-recordsSort records according to a particular index 
--sort-recoversort-recoverForce myisamchk to use sorting to resolve the keys even if the temporary files would be very large 
stats_method=valuestats_methodSpecifies how MyISAM index statistics collection code should treat NULLs 
--tmpdir=pathtmpdirPath of the directory to be used for storing temporary files 
--unpackunpackUnpack a table that was packed with myisampack 
--update-stateupdate-stateStore 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_sizeWrite 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.

  • --help, -?

    Display a help message and exit.

  • --debug=debug_options, -# debug_options

    Write a debugging log. The debug_options string often is 'd:t:o,file_name'.

  • --silent, -s

    Silent mode. Write output only when errors occur. You can use -s twice (-ss) to make myisamchk very silent.

  • --verbose, -v

    Verbose mode. Print more information about what the program does. This can be used with -d and -e. Use -v multiple times (-vv, -vvv) for even more output.

  • --version, -V

    Display version information and exit.

  • --wait, -w

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

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

VariableDefault Value
decode_bits9
ft_max_word_lenversion-dependent
ft_min_word_len4
ft_stopword_filebuilt-in list
key_buffer_size523264
myisam_block_size1024
read_buffer_size262136
sort_buffer_size2097144
sort_key_blocks16
stats_methodnulls_unequal
write_buffer_size262136

It is also possible to set variables by using --set-variable=var_name=value or -O var_name=value syntax. However, this syntax is deprecated as of MySQL 4.0.

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.

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 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.3, “System Variables”, and Section 7.4.7, “MyISAM Index Statistics Collection”. For MySQL 5.0, stats_method was added in MySQL 5.0.14. For older versions, the statistics collection method is equivalent to nulls_equal.

The ft_min_word_len and ft_max_word_len variables are available as of MySQL 4.0.0. ft_stopword_file is available as of MySQL 4.0.19.

ft_min_word_len and ft_max_word_len indicate the minimum and maximum word length for FULLTEXT indexes. 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.

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

  • --medium-check, -m

    Do a check that is faster than an --extend-check operation. This finds only 99.99% of all errors, which should be good enough in most cases.

  • --read-only, -T

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

  • --update-state, -U

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

4.6.3.3. myisamchk Repair Options

myisamchk supports the following options for table repair operations:

  • --backup, -B

    Make a backup of the .MYD file as file_name-time.BAK

  • --character-sets-dir=path

    The directory where character sets are installed. See Section 9.2, “The Character Set Used for Data and Sorting”.

  • --correct-checksum

    Correct the checksum information for the table.

  • --data-file-length=len, -D len

    Maximum length of the data file (when re-creating data file when it is “full”).

  • --extend-check, -e

    Do a repair that tries to recover every possible row from the data file. Normally, this also finds a lot of garbage rows. Don't use this option unless you are desperate.

  • --force, -f

    Overwrite old intermediate files (files with names like tbl_name.TMD) instead of aborting.

  • --keys-used=val, -k val

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

  • --no-symlinks, -l

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

  • --max-record-length=len

    Skip rows larger than the given length if myisamchk cannot allocate memory to hold them.

  • --parallel-recover, -p

    Uses the same technique as -r and -n, but creates all the keys in parallel, using different threads. This is beta-quality code. Use at your own risk!

  • --quick, -q

    Achieve a faster repair by not modifying the data file. You can specify this option twice to force myisamchk to modify the original data file in case of duplicate keys.

  • --recover, -r

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

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

  • --safe-recover, -o

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

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

  • --set-character-set=name

    Change the character set used by the table indexes. This option was replaced by --set-collation in MySQL 5.0.3.

  • --set-collation=name

    Specify the collation to use for sorting table indexes. The character set name is implied by the first part of the collation name. This option was added in MySQL 5.0.3.

  • --sort-recover, -n

    Force myisamchk to use sorting to resolve the keys even if the temporary files would be very large.

  • --tmpdir=path, -t path

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

  • --unpack, -u

    Unpack a table that was packed with myisampack.

4.6.3.4. Other myisamchk Options

myisamchk supports the following options for actions other than table checks and repairs:

  • --analyze, -a

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

    MySQL Enterprise For expert advice on optimizing tables, subscribe to the MySQL Enterprise Monitor. For more information, see http://www.mysql.com/products/enterprise/advisors.html.

  • --block-search=offset, -b offset

    Find the record that a block at the given offset belongs to.

  • --description, -d

    Print some descriptive information about the table.

  • --set-auto-increment[=value], -A[value]

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

  • --sort-index, -S

    Sort the index tree blocks in high-low order. This optimizes seeks and makes table scans that use indexes faster.

  • --sort-records=N, -R N

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

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

4.6.3.5. myisamchk Memory Usage

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

shell> myisamchk --sort_buffer_size=16M --key_buffer_size=16M \
           --read_buffer_size=1M --write_buffer_size=1M ...

Using --sort_buffer_size=16M should probably be enough for most cases.

Be aware that myisamchk uses temporary files in TMPDIR. If TMPDIR points to a memory filesystem, you may easily get out of memory errors. If this happens, run myisamchk with the --tmpdir=path option to specify some directory located on a filesystem that has more space.

When repairing, myisamchk also needs a lot of disk space:

  • Double the size of the data file (the original file and a copy). This space is not needed if you do a repair with --quick; in this case, only the index file is re-created. This space is needed on the same filesystem as the original data file! (The copy is created in the same directory as the original.)

  • Space for the new index file that replaces the old one. The old index file is truncated at the start of the repair operation, so you usually ignore this space. This space is needed on the same filesystem as the original index file!

  • When using --recover or --sort-recover (but not when using --safe-recover), you need space for a sort buffer. The following formula yields the amount of space required:

    (largest_key + row_pointer_length) × number_of_rows × 2
    

    You can check the length of the keys and the row_pointer_length with myisamchk -dv tbl_name. This space is allocated in the temporary directory (specified by TMPDIR or --tmpdir=path).

If you have a problem with disk space during repair, you can try --safe-recover instead of --recover.

4.6.4. myisamlog — Display MyISAM Log File Contents

myisamlog processes the contents of a MyISAM log file.

Invoke myisamlog like this:

shell> myisamlog [options] [log_file [tbl_name] ...]
shell> isamlog [options] [log_file [tbl_name] ...]

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

myisamlog understands the following options:

  • -?, -I

    Display a help message and exit.

  • -c N

    Execute only N commands.

  • -f N

    Specify the maximum number of open files.

  • -i

    Display extra information before exiting.

  • -o offset

    Specify the starting offset.

  • -p N

    Remove N components from path.

  • -r

    Perform a recovery operation.

  • -R record_pos_file record_pos

    Specify record position file and record position.

  • -u

    Perform an update operation.

  • -v

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

  • -w write_file

    Specify the write file.

  • -V

    Display version information.

4.6.5. myisampack — Generate Compressed, Read-Only MyISAM Tables

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

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

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

Please note the following:

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

  • After packing a table, it becomes read only. This is generally intended (such as when accessing packed tables on a CD). Allowing writes to a packed table is on our TODO list, but with low priority.

Invoke myisampack like this:

shell> myisampack [options] file_name ...

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

After you compress a table with myisampack, you should use myisamchk -rq to rebuild its indexes. Section 4.6.3, “myisamchk — MyISAM Table-Maintenance Utility”.

myisampack supports the following options:

  • --help, -?

    Display a help message and exit.

  • --backup, -b

    Make a backup of each table's data file using the name tbl_name.OLD.

  • --character-sets-dir=path

    The directory where character sets are installed. See Section 9.2, “The Character Set Used for Data and Sorting”.

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

    Write a debugging log. The debug_options string often is 'd:t:o,file_name'.

  • --force, -f

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

  • --join=big_tbl_name, -j big_tbl_name

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

    big_tbl_name must not exist prior to the join operation. All source tables named on the command line to be merged into big_tbl_name must exist. The source tables are read for the join operation but not modified. The join operation does not create a .frm file for big_tbl_name, so after the join operation finishes, copy the .frm file from one of the source tables and name it big_tbl_name.frm.

  • --silent, -s

    Silent mode. Write output only when errors occur.

  • --test, -t

    Do not actually pack the table, just test packing it.

  • --tmpdir=path, -T path

    Use the named directory as the location where myisampack creates temporary files.

  • --verbose, -v

    Verbose mode. Write information about the progress of the packing operation and its result.

  • --version, -V

    Display version information and exit.

  • --wait, -w

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

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

shell> ls -l station.*
-rw-rw-r--   1 monty    my         994128 Apr 17 19:00 station.MYD
-rw-rw-r--   1 monty    my          53248 Apr 17 19:00 station.MYI
-rw-rw-r--   1 monty    my           5767 Apr 17 19:00 station.frm

shell> myisamchk -dvv station

MyISAM file:     station
Isam-version:  2
Creation time: 1996-03-13 10:08:58
Recover time:  1997-02-02  3:06:43
Data records:              1192  Deleted blocks:              0
Datafile parts:            1192  Deleted data:                0
Datafile pointer (bytes):     2  Keyfile pointer (bytes):     2
Max datafile length:   54657023  Max keyfile length:   33554431
Recordlength:               834
Record format: Fixed length

table description:
Key Start Len Index   Type                 Root  Blocksize    Rec/key
1   2     4   unique  unsigned long        1024       1024          1
2   32    30  multip. text                10240       1024          1

Field Start Length Type
1     1     1
2     2     4
3     6     4
4     10    1
5     11    20
6     31    1
7     32    30
8     62    35
9     97    35
10    132   35
11    167   4
12    171   16
13    187   35
14    222   4
15    226   16
16    242   20
17    262   20
18    282   20
19    302   30
20    332   4
21    336   4
22    340   1
23    341   8
24    349   8
25    357   8
26    365   2
27    367   2
28    369   4
29    373   4
30    377   1
31    378   2
32    380   8
33    388   4
34    392   4
35    396   4
36    400   4
37    404   1
38    405   4
39    409   4
40    413   4
41    417   4
42    421   4
43    425   4
44    429   20
45    449   30
46    479   1
47    480   1
48    481   79
49    560   79
50    639   79
51    718   79
52    797   8
53    805   1
54    806   1
55    807   20
56    827   4
57    831   4

shell> myisampack station.MYI
Compressing station.MYI: (1192 records)
- Calculating statistics

normal:     20  empty-space:   16  empty-zero:     12  empty-fill:  11
pre-space:   0  end-space:     12  table-lookups:   5  zero:         7
Original trees:  57  After join: 17
- Compressing file
87.14%
Remember to run myisamchk -rq on compressed tables

shell> ls -l station.*
-rw-rw-r--   1 monty    my         127874 Apr 17 19:00 station.MYD
-rw-rw-r--   1 monty    my          55296 Apr 17 19:04 station.MYI
-rw-rw-r--   1 monty    my           5767 Apr 17 19:00 station.frm

shell> myisamchk -dvv station

MyISAM file:     station
Isam-version:  2
Creation time: 1996-03-13 10:08:58
Recover time:  1997-04-17 19:04:26
Data records:               1192  Deleted blocks:              0
Datafile parts:             1192  Deleted data:                0
Datafile pointer (bytes):      3  Keyfile pointer (bytes):     1
Max datafile length:    16777215  Max keyfile length:     131071
Recordlength:                834
Record format: Compressed

table description:
Key Start Len Index   Type                 Root  Blocksize    Rec/key
1   2     4   unique  unsigned long       10240       1024          1
2   32    30  multip. text                54272       1024          1

Field Start Length Type                         Huff tree  Bits
1     1     1      constant                             1     0
2     2     4      zerofill(1)                          2     9
3     6     4      no zeros, zerofill(1)                2     9
4     10    1                                           3     9
5     11    20     table-lookup                         4     0
6     31    1                                           3     9
7     32    30     no endspace, not_always              5     9
8     62    35     no endspace, not_always, no empty    6     9
9     97    35     no empty                             7     9
10    132   35     no endspace, not_always, no empty    6     9
11    167   4      zerofill(1)                          2     9
12    171   16     no endspace, not_always, no empty    5     9
13    187   35     no endspace, not_always, no empty    6     9
14    222   4      zerofill(1)                          2     9
15    226   16     no endspace, not_always, no empty    5     9
16    242   20     no endspace, not_always              8     9
17    262   20     no endspace, no empty                8     9
18    282   20     no endspace, no empty                5     9
19    302   30     no endspace, no empty                6     9
20    332   4      always zero                          2     9
21    336   4      always zero                          2     9
22    340   1                                           3     9
23    341   8      table-lookup                         9     0
24    349   8      table-lookup                        10     0
25    357   8      always zero                          2     9
26    365   2                                           2     9
27    367   2      no zeros, zerofill(1)                2     9
28    369   4      no zeros, zerofill(1)                2     9
29    373   4      table-lookup                        11     0
30    377   1                                           3     9
31    378   2      no zeros, zerofill(1)                2     9
32    380   8      no zeros                             2     9
33    388   4      always zero                          2     9
34    392   4      table-lookup                        12     0
35    396   4      no zeros, zerofill(1)               13     9
36    400   4      no zeros, zerofill(1)                2     9
37    404   1                                           2     9
38    405   4      no zeros                             2     9
39    409   4      always zero                          2     9
40    413   4      no zeros                             2     9
41    417   4      always zero                          2     9
42    421   4      no zeros                             2     9
43    425   4      always zero                          2     9
44    429   20     no empty                             3     9
45    449   30     no empty                             3     9
46    479   1                                          14     4
47    480   1                                          14     4
48    481   79     no endspace, no empty               15     9
49    560   79     no empty                             2     9
50    639   79     no empty                             2     9
51    718   79     no endspace                         16     9
52    797   8      no empty                             2     9
53    805   1                                          17     1
54    806   1                                           3     9
55    807   20     no empty                             3     9
56    827   4      no zeros, zerofill(2)                2     9
57    831   4      no zeros, zerofill(1)                2     9

myisampack displays the following kinds of information:

  • normal

    The number of columns for which no extra packing is used.

  • empty-space

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

  • empty-zero

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

  • empty-fill

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

  • pre-space

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

  • end-space

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

  • table-lookup

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

  • zero

    The number of columns for which all values are zero.

  • Original trees

    The initial number of Huffman trees.

  • After join

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

After a table has been compressed, myisamchk -dvv prints additional information about each column:

  • Type

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

    • constant

      All rows have the same value.

    • no endspace

      Do not store endspace.

    • no endspace, not_always

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

    • no endspace, no empty

      Do not store endspace. Do not store empty values.

    • table-lookup

      The column was converted to an ENUM.

    • zerofill(N)

      The most significant N bytes in the value are always 0 and are not stored.

    • no zeros

      Do not store zeros.

    • always zero

      Zero values are stored using one bit.

  • Huff tree

    The number of the Huffman tree associated with the column.

  • Bits

    The number of bits used in the Huffman tree.

After you run myisampack, you must run myisamchk to re-create any indexes. At this time, you can also sort the index blocks and create statistics needed for the MySQL optimizer to work more efficiently:

shell> myisamchk -rq --sort-index --analyze tbl_name.MYI

After you have installed the packed table into the MySQL database directory, you should execute mysqladmin flush-tables to force mysqld to start using the new table.

To unpack a packed table, use the --unpack option to myisamchk.

4.6.6. mysqlaccess — Client for Checking Access Privileges

mysqlaccess is a diagnostic tool that Yves Carlier has provided for the MySQL distribution. It checks the access privileges for a hostname, username, and database combination. Note that mysqlaccess checks access using only the user, db, and host tables. It does not check table, column, or routine privileges specified in the tables_priv, columns_priv, or procs_priv tables.

Invoke mysqlaccess like this:

shell> mysqlaccess [host_name [user_name [db_name]]] [options]

mysqlaccess understands the following options:

Table 4.9. mysqlaccess Option Reference

FormatConfig FileDescriptionIntroduction
--briefbriefGenerate reports in single-line tabular format 
--commitcommitCopy the new access privileges from the temporary tables to the original grant tables 
--copycopyReload the temporary grant tables from original ones 
--db=db_namedbSpecify the database name 
--debug=#debugSpecify the debug level 
--help Display help message and exit 
--host=host_namehostConnect to the MySQL server on the given host 
--howtohowtoDisplay some examples that show how to use mysqlaccess 
--old_serverold_serverAssume that the server is an old MySQL server (prior to MySQL 3.21) 
--password[=password]passwordThe password to use when connecting to the server 
--planplanDisplay suggestions and ideas for future releases 
--previewpreviewShow the privilege differences after making changes to the temporary grant tables 
--relnotesrelnotesDisplay the release notes 
--rhost=host_namerhostConnect to the MySQL server on the given host 
--rollbackrollbackUndo the most recent changes to the temporary grant tables. 
--spassword[=password]spasswordThe password to use when connecting to the server as the superuser 
--superuser=user_namesuperuserSpecify the username for connecting as the superuser 
--tabletableGenerate reports in table format 
--user=user_name,userThe MySQL username to use when connecting 
--version Display version information and exit 
  • --help, -?

    Display a help message and exit.

  • --brief, -b

    Generate reports in single-line tabular format.

  • --commit

    Copy the new access privileges from the temporary tables to the original grant tables. The grant tables must be flushed for the new privileges to take effect. (For example, execute a mysqladmin reload command.)

  • --copy

    Reload the temporary grant tables from original ones.

  • --db=db_name, -d db_name

    Specify the database name.

  • --debug=N

    Specify the debug level. N can be an integer from 0 to 3.

  • --host=host_name, -h host_name

    The hostname to use in the access privileges.

  • --howto

    Display some examples that show how to use mysqlaccess.

  • --old_server

    Assume that the server is an old MySQL server (before MySQL 3.21) that does not yet know how to handle full WHERE clauses.

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

    The password to use when connecting to the server. If you omit the password value following the --password or -p option on the command line, you are prompted for one.

    Specifying a password on the command line should be considered insecure. See Section 5.5.6, “Keeping Your Password Secure”.

  • --plan

    Display suggestions and ideas for future releases.

  • --preview

    Show the privilege differences after making changes to the temporary grant tables.

  • --relnotes

    Display the release notes.

  • --rhost=host_name, -H host_name

    Connect to the MySQL server on the given host.

  • --rollback

    Undo the most recent changes to the temporary grant tables.

  • --spassword[=password], -P[password]

    The password to use when connecting to the server as the superuser. If you omit the password value following the --password or -p option on the command line, you are prompted for one.

    Specifying a password on the command line should be considered insecure. See Section 5.5.6, “Keeping Your Password Secure”.

  • --superuser=user_name, -U user_name

    Specify the username for connecting as the superuser.

  • --table, -t

    Generate reports in table format.

  • --user=user_name, -u user_name

    The username to use in the access privileges.

  • --version, -v

    Display version information and exit.

If your MySQL distribution is installed in some non-standard location, you must change the location where mysqlaccess expects to find the mysql client. Edit the mysqlaccess script at approximately line 18. Search for a line that looks like this:

$MYSQL     = '/usr/local/bin/mysql';    # path to mysql executable

Change the path to reflect the location where mysql actually is stored on your system. If you do not do this, a Broken pipe error will occur when you run mysqlaccess.

4.6.7. mysqlbinlog — Utility for Processing Binary Log Files

The server's binary log consists of files that contain “events” that describe modifications to database contents. The server writes binary log files in binary format. To display these files in text format, use the mysqlbinlog utility. You can also use mysqlbinlog to display relay log files written by a slave server in a replication setup. Relay logs have the same format as binary log files. The binary log and relay log are discussed further in Section 5.2.3, “The Binary Log”, and Section 18.4.2, “Replication Relay and Status Files”.

Invoke mysqlbinlog like this:

shell> mysqlbinlog [options] log_file ...

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

shell> mysqlbinlog binlog.0000003

The output includes all events contained in binlog.000003. Event information includes the statement executed, the time the statement took, the thread ID of the client that issued it, the timestamp when it was executed, and so forth.

The output from mysqlbinlog can be re-executed (for example, by using it as input to mysql) to reapply the statements in the log. This is useful for recovery operations after a server crash. For other usage examples, see the discussion later in this section.

Normally, you use mysqlbinlog to read binary log files directly and apply them to the local MySQL server. It is also possible to read binary logs from a remote server by using the --read-from-remote-server option. When you read remote binary logs, the connection parameter options can be given to indicate how to connect to the server. These options are --host, --password, --port, --protocol, --socket, and --user; they are ignored except when you also use the --read-from-remote-server option.

Table 4.10. mysqlbinlog Option Reference

FormatConfig FileDescriptionIntroduction
--character-sets-dir=pathcharacter-sets-dirThe directory where character sets are installed 
--database=db_namedatabaseList entries for just this database 
--debug[=debug_options]debugWrite a debugging log 
--disable-log-bindisable-log-binDisable binary logging 
--force-readforce-readIf mysqlbinlog reads a binary log event that it does not recognize, it prints a warning 
--help Display help message and exit 
--hexdumphexdumpDisplay a hex dump of the log in comments5.0.16
--host=host_namehostConnect to the MySQL server on the given host 
--local-load=pathlocal-loadPrepare local temporary files for LOAD DATA INFILE in the specified directory 
--offset=#offsetSkip the first N entries in the log 
--password[=password]passwordThe password to use when connecting to the server 
--port=port_numportThe TCP/IP port number to use for the connection 
--protocol=typeprotocolThe connection protocol to use 
--read-from-remote-serverread-from-remote-serverRead the binary log from a MySQL server rather than reading a local log file 
--result-file=nameresult-fileDirect output to the given file 
--set-charset=charset_nameset-charsetAdd a SET NAMES charset_name statement to the output5.0.23
--short-formshort-formDisplay only the statements contained in the log 
--socket=pathsocketFor connections to localhost 
--start-datetime=datetimestart-datetimeStart reading the binary log at the first event having a timestamp equal to or later than the datetime argument 
--start-position=#start-positionStart reading the binary log at the first event having a position equal to the N argument 
--stop-datetime=datetimestop-datetimeStop reading the binary log at the first event having a timestamp equal or posterior to the datetime argument 
--stop-position=#stop-positionStop reading the binary log at the first event having a position equal or greater than the N argument 
--to-last-logto-last-logDo not stop at the end of the requested binary log from a MySQL server, but rather continue printing until the end of the last binary log 
--user=user_name,userThe MySQL username to use when connecting to the server 
--version Display version information and exit 

mysqlbinlog supports the following options:

  • --help, -?

    Display a help message and exit.

  • --character-sets-dir=path

    The directory where character sets are installed. See Section 9.2, “The Character Set Used for Data and Sorting”.

  • --database=db_name, -d db_name

    List entries for just this database (local log only). You can only specify one database with this option - if you specify multiple --database options, only the last one is used. This option forces mysqlbinlog to output entries from the binary log where the default database (that is, the one selected by USE) is db_name. Note that this does not replicate cross-database statements such as UPDATE some_db.some_table SET foo='bar' while having selected a different database or no database.

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

    Write a debugging log. A typical debug_options string is often 'd:t:o,file_name'.

  • --disable-log-bin, -D

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

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

  • --force-read, -f

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

  • --hexdump, -H

    Display a hex dump of the log in comments. This output can be helpful for replication debugging. Hex dump format is discussed later in this section. This option was added in MySQL 5.0.16.

  • --host=host_name, -h host_name

    Get the binary log from the MySQL server on the given host.

  • --local-load=path, -l path

    Prepare local temporary files for LOAD DATA INFILE in the specified directory.

  • --offset=N, -o N

    Skip the first N entries in the log.

  • --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, you are prompted for one.

    Specifying a password on the command line should be considered insecure. See Section 5.5.6, “Keeping Your Password Secure”.

  • --port=port_num, -P port_num

    The TCP/IP port number to use for connecting to a remote server.

  • --position=N, -j N

    Deprecated. Use --start-position instead.

  • --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 allowable values, see Section 4.2.2, “Connecting to the MySQL Server”.

  • --read-from-remote-server, -R

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

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

  • --result-file=name, -r name

    Direct output to the given file.

  • --set-charset=charset_name

    Add a SET NAMES charset_name statement to the output to specify the character set to be used for processing log files. This option was added in MySQL 5.0.23.

  • --short-form, -s

    Display only the statements contained in the log, without any extra information.

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

  • --start-datetime=datetime

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

    shell> mysqlbinlog --start-datetime="2005-12-25 11:25:56" binlog.000003
    

    This option is useful for point-in-time recovery. See Section 6.2, “Example Backup and Recovery Strategy”.

  • --stop-datetime=datetime

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

  • --start-position=N

    Start reading the binary log at the first event having a position equal to the N argument. This option applies to the first log file named on the command line.

  • --stop-position=N

    Stop reading the binary log at the first event having a position equal or greater than the N argument. This option applies to the last log file named on the command line.

  • --to-last-log, -t

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

  • --user=user_name, -u user_name

    The MySQL username to use when connecting to a remote server.

  • --version, -V

    Display version information and exit.

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

  • open_files_limit

    Specify the number of open file descriptors to reserve.

It is also possible to set variables by using --set-variable=var_name=value or -O var_name=value syntax. This syntax is deprecated.

You can pipe the output of mysqlbinlog into the mysql client to execute the statements contained in the binary log. This is used to recover from a crash when you have an old backup (see Section 6.1, “Database Backups”). For example:

shell> mysqlbinlog binlog.000001 | mysql

Or:

shell> mysqlbinlog binlog.[0-9]* | mysql

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

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

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

shell> mysqlbinlog binlog.000001 | mysql # DANGER!!
shell> mysqlbinlog binlog.000002 | mysql # DANGER!!

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

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

shell> mysqlbinlog binlog.000001 binlog.000002 | mysql

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

shell> mysqlbinlog binlog.000001 >  /tmp/statements.sql
shell> mysqlbinlog binlog.000002 >> /tmp/statements.sql
shell> mysql -e "source /tmp/statements.sql"

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

Because mysqlbinlog converts LOAD DATA INFILE statements to LOAD DATA LOCAL INFILE statements (that is, it adds LOCAL), both the client and the server that you use to process the statements must be configured to allow LOCAL capability. See Section 5.3.4, “Security Issues with LOAD DATA LOCAL.

MySQL Enterprise For expert advice on the security implications of enabling LOCAL, subscribe to the MySQL Enterprise Monitor. For more information, see http://www.mysql.com/products/enterprise/advisors.html.

Warning

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

The --hexdump option produces a hex dump of the log contents in #-format comments:

shell> mysqlbinlog --hexdump master-bin.000001

With the preceding command, the output might look like this:

/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
# at 4
#051024 17:24:13 server id 1  end_log_pos 98
# Position  Timestamp   Type   Master ID        Size      Master Pos    Flags
# 00000004 9d fc 5c 43   0f   01 00 00 00   5e 00 00 00   62 00 00 00   00 00
# 00000017 04 00 35 2e 30 2e 31 35  2d 64 65 62 75 67 2d 6c |..5.0.15.debug.l|
# 00000027 6f 67 00 00 00 00 00 00  00 00 00 00 00 00 00 00 |og..............|
# 00000037 00 00 00 00 00 00 00 00  00 00 00 00 00 00 00 00 |................|
# 00000047 00 00 00 00 9d fc 5c 43  13 38 0d 00 08 00 12 00 |.......C.8......|
# 00000057 04 04 04 04 12 00 00 4b  00 04 1a                |.......K...|
#       Start: binlog v 4, server v 5.0.15-debug-log created 051024 17:24:13
#       at startup
ROLLBACK;

Hex dump output currently contains the following elements. This format might change in the future.

  • Position: The byte position within the log file.

  • Timestamp: The event timestamp. In the example shown, '9d fc 5c 43' is the representation of '051024 17:24:13' in hexadecimal.

  • Type: The type of the log event. In the example shown, '0f' means that the example event is a FORMAT_DESCRIPTION_EVENT. The following table lists the possible types.

    TypeNameMeaning
    00UNKNOWN_EVENTThis event should never be present in the log.
    01START_EVENT_V3This indicates the start of a log file written by MySQL 4 or earlier.
    02QUERY_EVENTThe most common type of events. These contain statements executed on the master.
    03STOP_EVENTIndicates that master has stopped.
    04ROTATE_EVENTWritten when the master switches to a new log file.
    05INTVAR_EVENTUsed mainly for AUTO_INCREMENT values and when the LAST_INSERT_ID() function is used in the statement.
    06LOAD_EVENTUsed for LOAD DATA INFILE in MySQL 3.23.
    07SLAVE_EVENTReserved for future use.
    08CREATE_FILE_EVENTUsed for LOAD DATA INFILE statements. This indicates the start of execution of such a statement. A temporary file is created on the slave. Used in MySQL 4 only.
    09APPEND_BLOCK_EVENTContains data for use in a LOAD DATA INFILE statement. The data is stored in the temporary file on the slave.
    0aEXEC_LOAD_EVENTUsed for LOAD DATA INFILE statements. The contents of the temporary file is stored in the table on the slave. Used in MySQL 4 only.
    0bDELETE_FILE_EVENTRollback of a LOAD DATA INFILE statement. The temporary file should be deleted on slave.
    0cNEW_LOAD_EVENTUsed for LOAD DATA INFILE in MySQL 4 and earlier.
    0dRAND_EVENTUsed to send information about random values if the RAND() function is used in the statement.
    0eUSER_VAR_EVENTUsed to replicate user variables.
    0fFORMAT_DESCRIPTION_EVENTThis indicates the start of a log file written by MySQL 5 or later.
    10XID_EVENTEvent indicating commit of an XA transaction.
    11BEGIN_LOAD_QUERY_EVENTUsed for LOAD DATA INFILE statements in MySQL 5 and later.
    12EXECUTE_LOAD_QUERY_EVENTUsed for LOAD DATA INFILE statements in MySQL 5 and later.
    13TABLE_MAP_EVENTReserved for future use.
    14WRITE_ROWS_EVENTReserved for future use.
    15UPDATE_ROWS_EVENTReserved for future use.
    16DELETE_ROWS_EVENTReserved for future use.
  • Master ID: The server id of the master that created the event.

  • Size: The size in bytes of the event.

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

  • Flags: 16 flags. Currently, the following flags are used. The others are reserved for future use.

    FlagNameMeaning
    01LOG_EVENT_BINLOG_IN_USE_FLog file correctly closed. (Used only in FORMAT_DESCRIPTION_EVENT.) If this flag is set (if the flags are, for example, '01 00') in a FORMAT_DESCRIPTION_EVENT, the log file has not been properly closed. Most probably this is because of a master crash (for example, due to power failure).
    02 Reserved for future use.
    04LOG_EVENT_THREAD_SPECIFIC_FSet if the event is dependent on the connection it was executed in (for example, '04 00'), for example, if the event uses temporary tables.
    08LOG_EVENT_SUPPRESS_USE_FSet in some circumstances when the event is not dependent on the default database.

4.6.8. mysqlhotcopy — A Database Backup Program

mysqlhotcopy is a Perl script that was originally written and contributed by Tim Bunce. It uses LOCK TABLES, FLUSH TABLES, and cp or scp to make a database backup quickly. It is the fastest way to make a backup of the database or single tables, but it can be run only on the same machine where the database directories are located. mysqlhotcopy works only for backing up MyISAM and ARCHIVE tables. It runs on Unix and NetWare.

shell> mysqlhotcopy db_name [/path/to/new_directory]
shell> mysqlhotcopy db_name_1 ... db_name_n /path/to/new_directory

Back up tables in the given database that match a regular expression:

shell> mysqlhotcopy db_name./regex/

The regular expression for the table name can be negated by prefixing it with a tilde (“~”):

shell> mysqlhotcopy db_name./~regex/

Table 4.11. mysqlhotcopy Option Reference

FormatConfig FileDescriptionIntroduction
--addtodestaddtodestDo not rename target directory (if it exists); merely add files to it 
--allowoldallowoldDo not abort if a target exists; rename it by adding an _old suffix 
--checkpoint=db_name.tbl_namecheckpointInsert checkpoint entries 
--chroot=pathchrootBase directory of the chroot jail in which mysqld operates 
--debugdebugWrite a debugging log 
--dryrundryrunReport actions without performing them 
--flushlogsflushlogsFlush logs after all tables are locked 
--help Display help message and exit 
--host=host_namehostConnect to the MySQL server on the given host 
--keepoldkeepoldDo not delete previous (renamed) target when done 
--noindicesnoindicesDo not include full index files in the backup 
--password[=password]passwordThe password to use when connecting to the server 
--port=port_numportThe TCP/IP port number to use for the connection 
--quietquietBe silent except for errors 
--regexpregexpCopy all databases with names that match the given regular expression 
--resetmasterresetmasterReset the binary log after locking all the tables 
--resetslaveresetslaveReset the master.info file after locking all the tables 
--socket=pathsocketFor connections to localhost 
--tmpdir=pathtmpdirThe temporary directory 
--user=user_name,userThe MySQL username to use when connecting to the server 
--version Display version information and exit 

mysqlhotcopy supports the following options:

  • --help, -?

    Display a help message and exit.

  • --addtodest

    Do not rename target directory (if it exists); merely add files to it.

  • --allowold

    Do not abort if a target exists; rename it by adding an _old suffix.

  • --checkpoint=db_name.tbl_name

    Insert checkpoint entries into the specified database db_name and table tbl_name.

  • --chroot=path

    Base directory of the chroot jail in which mysqld operates. The path value should match that of the --chroot option given to mysqld.

  • --debug

    Enable debug output.

  • --dryrun, -n

    Report actions without performing them.

  • --flushlog

    Flush logs after all tables are locked.

  • --host=host_name, -h host_name

    The hostname of the local host to use for making a TCP/IP connection to the local server. By default, the connection is made to localhost using a Unix socket file.

  • --keepold

    Do not delete previous (renamed) target when done.

  • --method=command

    The method for copying files (cp or scp).

  • --noindices

    Do not include full index files in the backup. This makes the backup smaller and faster. The indexes for reloaded tables can be reconstructed later with myisamchk -rq.

  • --password=password, -ppassword

    The password to use when connecting to the server. Note that the password value is not optional for this option, unlike for other MySQL programs. You can use an option file to avoid giving the password on the command line.

    Specifying a password on the command line should be considered insecure. See Section 5.5.6, “Keeping Your Password Secure”.

  • --port=port_num, -P port_num

    The TCP/IP port number to use when connecting to the local server.

  • --quiet, -q

    Be silent except for errors.

  • --record_log_pos=db_name.tbl_name

    Record master and slave status in the specified database db_name and table tbl_name.

  • --regexp=expr

    Copy all databases with names that match the given regular expression.

  • --resetmaster

    Reset the binary log after locking all the tables.

  • --resetslave

    Reset the master.info file after locking all the tables.

  • --socket=path, -S path

    The Unix socket file to use for the connection.

  • --suffix=str

    The suffix for names of copied databases.

  • --tmpdir=path

    The temporary directory. The default is /tmp.

  • --user=user_name, -u user_name

    The MySQL username to use when connecting to the server.

mysqlhotcopy reads the [client] and [mysqlhotcopy] option groups from option files.

To execute mysqlhotcopy, you must have access to the files for the tables that you are backing up, the SELECT privilege for those tables, the RELOAD privilege (to be able to execute FLUSH TABLES), and the LOCK TABLES privilege (to be able to lock the tables).

Use perldoc for additional mysqlhotcopy documentation, including information about the structure of the tables needed for the --checkpoint and --record_log_pos options:

shell> perldoc mysqlhotcopy

MySQL Enterprise MySQL Enterprise subscribers will find more information about mysqlhotcopy in the Knowledge Base article, How Does mysqlhotcopy Work?. Access to the MySQL Knowledge Base collection of articles is one of the advantages of subscribing to MySQL Enterprise. For more information, see http://www.mysql.com/products/enterprise/advisors.html.

4.6.9. mysqlmanager — The MySQL Instance Manager

Important

MySQL Instance Manager is been deprecated in MySQL 5.1 and is removed in MySQL 6.0.

mysqlmanager is the MySQL Instance Manager (IM). This program monitors and manages MySQL Database Server instances. MySQL Instance Manager is available for Unix-like operating systems, and also on Windows as of MySQL 5.0.13. It runs as a daemon that listens on a TCP/IP port. On Unix, it also listens on a Unix socket file.

MySQL Instance Manager is included in MySQL distributions from version 5.0.3, and can be used in place of the mysqld_safe script to start and stop one or more instances of MySQL Server. Because Instance Manager can manage multiple server instances, it can also be used in place of the mysqld_multi script. Instance Manager offers these capabilities:

  • Instance Manager can start and stop instances, and report on the status of instances.

  • Server instances can be treated as guarded or unguarded:

    • When Instance Manager starts, it starts each guarded instance. If the instance crashes, Instance Manager detects this and restarts it. When Instance Manager stops, it stops the instance.

    • A nonguarded instance is not started when Instance Manager starts or monitored by it. If the instance crashes after being started, Instance Manager does not restart it. When Instance Manager exits, it does not stop the instance if it is running.

    Instances are guarded by default. An instance can be designated as nonguarded by including the nonguarded option in the configuration file.

  • Instance Manager provides an interactive interface for configuring instances, so that the need to edit the configuration file manually is reduced or eliminated.

  • Instance Manager provides remote instance management. That is, it runs on the host where you want to control MySQL Server instances, but you can connect to it from a remote host to perform instance-management operations.

The following sections describe MySQL Instance Manager operation in more detail.

4.6.9.1. MySQL Instance Manager Command Options

Important

MySQL Instance Manager is been deprecated in MySQL 5.1 and is removed in MySQL 6.0.

The MySQL Instance Manager supports a number of command options. For a brief listing, invoke mysqlmanager with the --help option. Options may be given on the command line or in the Instance Manager configuration file. On Windows, the standard configuration file is my.ini in the directory where Instance Manager is installed. On Unix, the standard file is /etc/my.cnf. To specify a different configuration file, start Instance Manager with the --defaults-file option.

mysqlmanager supports the following options:

  • --help, -?

    Display a help message and exit.

  • --angel-pid-file=file_name

    The file in which the angel process records its process ID when mysqlmanager runs in daemon mode (that is, when the --run-as-service option is given). The default filename is mysqlmanager.angel.pid.

    If the --angel-pid-file option is not given, the default angel PID file has the same name as the PID file except that any PID file extension is replaced with an extension of .angel.pid. (For example, mysqlmanager.pid becomes mysqlmanager.angel.pid.)

    This option was added in MySQL 5.0.23.

  • --bind-address=IP

    The IP address to bind to.

  • --default-mysqld-path=path

    The pathname of the MySQL Server binary. This pathname is used for all server instance sections in the configuration file for which no mysqld-path option is present. The default value of this option is the compiled-in pathname, which depends on how the MySQL distribution was configured. Example: --default-mysqld-path=/usr/sbin/mysqld

  • --defaults-file=file_name

    Read Instance Manager and MySQL Server settings from the given file. All configuration changes made by the Instance Manager will be written to this file. This must be the first option on the command line if it is used, and the file must exist.

    If this option is not given, Instance Manager uses its standard configuration file. On Windows, the standard file is my.ini in the directory where Instance Manager is installed. On Unix, the standard file is /etc/my.cnf.

  • --install

    On Windows, install Instance Manager as a Windows service. The service name is MySQL Manager. This option was added in MySQL 5.0.11.

  • --log=file_name

    The path to the Instance Manager log file. This option has no effect unless the --run-as-service option is also given. If the filename specified for the option is a relative name, the log file is created under the directory from which Instance Manager is started. To ensure that the file is created in a specific directory, specify it as a full pathname.

    If --run-as-service is given without --log, the log file is mysqlmanager.log in the data directory.

    If --run-as-service is not given, log messages go to the standard output. To capture log output, you can redirect Instance Manager output to a file:

    mysqlmanager > im.log
    
  • --monitoring-interval=seconds

    The interval in seconds for monitoring server instances. The default value is 20 seconds. Instance Manager tries to connect to each monitored (guarded) instance using the non-existing MySQL_Instance_Manager user account to check whether it is alive/not hanging. If the result of the connection attempt indicates that the instance is unavailable, Instance Manager performs several attempts to restart the instance.

    Normally, the MySQL_Instance_Manager account does not exist, so the connection attempts by Instance Manager cause the monitored instance to produce messages in its general query log similar to the following:

    Access denied for user 'MySQL_Instance_M'@'localhost' (using password: YES)
    

    The nonguarded option in the appropriate server instance section disables monitoring for a particular instance. If the instance dies after being started, Instance Manager will not restart it. Instance Manager tries to connect to a nonguarded instance only when you request the instance's status (for example, with the SHOW INSTANCES status.

    See Section 4.6.9.5, “MySQL Server Instance Status Monitoring”, for more information.

  • --passwd, -P

    Prepare an entry for the password file, print it to the standard output, and exit. You can redirect the output from Instance Manager to a file to save the entry in the file. See also Section 4.6.9.4, “Instance Manager User and Password Management”. This

  • --password-file=file_name

    The name of the file where the Instance Manager looks for users and passwords. On Windows, the default is mysqlmanager.passwd in the directory where Instance Manager is installed. On Unix, the default file is /etc/mysqlmanager.passwd. See also Section 4.6.9.4, “Instance Manager User and Password Management”.

  • --pid-file=file_name

    The process ID file to use. On Windows, the default file is mysqlmanager.pid in the directory where Instance Manager is installed. On Unix, the default is mysqlmanager.pid in the data directory.

  • --port=port_num

    The port number to use when listening for TCP/IP connections from clients. The default port number (assigned by IANA) is 2273.

  • --print-defaults

    Print the current defaults and exit. This must be the first option on the command line if it is used.

  • --remove

    On Windows, removes Instance Manager as a Windows service. This assumes that Instance Manager has been run with --install previously. This option was added in MySQL 5.0.11.

  • --run-as-service

    On Unix, daemonize and start an angel process. The angel process monitors Instance Manager and restarts it if it crashes. (The angel process itself is simple and unlikely to crash.)

  • --socket=path

    On Unix, the socket file to use for incoming connections. The default file is named /tmp/mysqlmanager.sock. This option has no meaning on Windows.

  • --standalone

    This option is used on Windows to run Instance Manager in standalone mode. You should specify it when you start Instance Manager from the command line. This option was added in MySQL 5.0.13.

  • --user=user_name

    On Unix, the username of the system account to use for starting and running mysqlmanager. This option generates a warning and has no effect unless you start mysqlmanager as root (so that it can change its effective user ID), or as the named user. It is recommended that you configure mysqlmanager to run using the same account used to run the mysqld server. (“User” in this context refers to a system login account, not a MySQL user listed in the grant tables.)

  • --version, -V

    Display version information and exit.

  • --wait-timeout=N

    The number of seconds to wait for activity on an incoming connection before closing it. The default is 28800 seconds (8 hours).

    This option was added in MySQL 5.0.19. Before that, the timeout is 30 seconds and cannot be changed.

4.6.9.2. MySQL Instance Manager Configuration Files

Important

MySQL Instance Manager is been deprecated in MySQL 5.1 and is removed in MySQL 6.0.

Instance Manager uses its standard configuration file unless it is started with a --defaults-file option that specifies a different file. On Windows, the standard file is my.ini in the directory where Instance Manager is installed. On Unix, the standard file is /etc/my.cnf. (Prior to MySQL 5.0.10, the MySQL Instance Manager read the same configuration files as the MySQL Server, including /etc/my.cnf, ~/.my.cnf, and so forth.)

Instance Manager reads options for itself from the [manager] section of the configuration file, and options for server instances from [mysqld] or [mysqldN] sections. The [manager] section contains any of the options listed in Section 4.6.9.1, “MySQL Instance Manager Command Options”, except for those specified as having to be given as the first option on the command line. Here is a sample [manager] section:

# MySQL Instance Manager options section
[manager]
default-mysqld-path = /usr/local/mysql/libexec/mysqld
socket=/tmp/manager.sock
pid-file=/tmp/manager.pid
password-file = /home/cps/.mysqlmanager.passwd
monitoring-interval = 2
port = 1999
bind-address = 192.168.1.5

Each [mysqld] or [mysqldN] instance section specifies options given by Instance Manager to a server instance at startup. These are mainly common MySQL Server options (see Section 5.1.2, “Command Options”). In addition, a [mysqldN] section can contain the options in the following list, which are specific to Instance Manager. These options are interpreted by Instance Manager itself; it does not pass them to the server when it attempts to start that server.

Warning

The Instance Manager-specific options must not be used in a [mysqld] section. If a server is started without using Instance Manager, it will not recognize these options and will fail to start properly.

  • mysqld-path = path

    The pathname of the mysqld server binary to use for the server instance.

  • nonguarded

    This option disables Instance Manager monitoring functionality for the server instance. By default, an instance is guarded: At Instance Manager start time, it starts the instance. It also monitors the instance status and attempts to restart it if it fails. At Instance Manager exit time, it stops the instance. None of these things happen for nonguarded instances.

  • shutdown-delay = seconds

    The number of seconds Instance Manager should wait for the server instance to shut down. The default value is 35 seconds. After the delay expires, Instance Manager assumes that the instance is hanging and attempts to terminate it. If you use InnoDB with large tables, you should increase this value.

Here are some sample instance sections:

[mysqld1]
mysqld-path=/usr/local/mysql/libexec/mysqld
socket=/tmp/mysql.sock
port=3307
server_id=1
skip-stack-trace
core-file
skip-bdb
log-bin
log-error
log=mylog
log-slow-queries

[mysqld2]
nonguarded
port=3308
server_id=2
mysqld-path= /home/cps/mysql/trees/mysql-5.0/sql/mysqld
socket     = /tmp/mysql.sock5
pid-file   = /tmp/hostname.pid5
datadir= /home/cps/mysql_data/data_dir1
language=/home/cps/mysql/trees/mysql-5.0/sql/share/english
log-bin
log=/tmp/fordel.log

4.6.9.3. Starting the MySQL Server with MySQL Instance Manager

Important

MySQL Instance Manager is been deprecated in MySQL 5.1 and is removed in MySQL 6.0.

This section discusses how Instance Manager starts server instances when it starts. However, before you start Instance Manager, you should set up a password file for it. Otherwise, you will not be able to connect to Instance Manager to control it after it starts. For details about creating Instance Manager accounts, see Section 4.6.9.4, “Instance Manager User and Password Management”.

On Unix, the mysqld MySQL database server normally is started with the mysql.server script, which usually resides in the /etc/init.d/ directory. In MySQL 5.0.3, this script invokes mysqlmanager (the MySQL Instance Manager binary) to start MySQL. (In prior versions of MySQL the mysqld_safe script is used for this purpose.) Starting from MySQL 5.0.4, the behavior of the startup script was changed again to incorporate both setup schemes. In version 5.0.4, the startup script uses the old scheme (invoking mysqld_safe) by default, but one can set the use_mysqld_safe variable in the script to 0 (zero) to use the MySQL Instance Manager to start a server.

Starting with MySQL 5.0.19, you can use Instance Manager if you modify the my.cnf configuration file by adding use-manager to the [mysql.server] section:

[mysql.server]
use-manager

When Instance Manager starts, it reads its configuration file if it exists to find server instance sections and prepare a list of instances. Instance sections have names of the form [mysqld] or [mysqldN], where N is an unsigned integer (for example, [mysqld1], [mysqld2], and so forth).

After preparing the list of instances, Instance Manager starts the guarded instances in the list. If there are no instances, Instance Manager creates an instance named mysqld and attempts to start it with default (compiled-in) configuration values. This means that the Instance Manager cannot find the mysqld program if it is not installed in the default location. (Section 2.4.6, “Installation Layouts”, describes default locations for components of MySQL distributions.) If you have installed the MySQL server in a non-standard location, you should create the Instance Manager configuration file.

Instance Manager also stops all guarded server instances when it shuts down.

The allowable options for [mysqldN] server instance sections are described in Section 4.6.9.2, “MySQL Instance Manager Configuration Files”. In these sections, you can use a special mysqld-path=path-to-mysqld-binary option that is recognized only by Instance Manager. Use this option to let Instance Manager know where the mysqld binary resides. If there are multiple instances, it may also be necessary to set other options such as datadir and port, to ensure that each instance has a different data directory and TCP/IP port number. Section 5.6, “Running Multiple MySQL Servers on the Same Machine”, discusses the configuration values that must differ for each instance when you run multiple instance on the same machine.

Warning

The [mysqld] instance section, if it exists, must not contain any Instance Manager-specific options.

The typical Unix startup/shutdown cycle for a MySQL server with the MySQL Instance Manager enabled is as follows:

  1. The /etc/init.d/mysql script starts MySQL Instance Manager.

  2. Instance Manager starts the guarded server instances and monitors them.

  3. If a server instance fails, Instance Manager restarts it.

  4. If Instance Manager is shut down (for example, with the /etc/init.d/mysql stop command), it shuts down all server instances.

4.6.9.4. Instance Manager User and Password Management

Important

MySQL Instance Manager is been deprecated in MySQL 5.1 and is removed in MySQL 6.0.

The Instance Manager stores its user information in a password file. On Windows, the default is mysqlmanager.passwd in the directory where Instance Manager is installed. On Unix, the default file is /etc/mysqlmanager.passwd. To specify a different location for the password file, use the --password-file option.

If the password file does not exist or contains no password entries, you cannot connect to the Instance Manager.

Note

Any Instance Manager process that is running to monitor server instances does not notice changes to the password file. You must stop it and restart it after making password entry changes.

Entries in the password file have the following format, where the two fields are the account username and encrypted password, separated by a colon:

petr:*35110DC9B4D8140F5DE667E28C72DD2597B5C848

Instance Manager password encryption is the same as that used by MySQL Server. It is a one-way operation; no means are provided for decrypting encrypted passwords.

Instance Manager accounts differ somewhat from MySQL Server accounts:

This means that a client can connect to Instance Manager with a given username from any host. To limit connections so that clients can connect only from the local host, start Instance Manager with the --bind-address=127.0.0.1 option so that it listens only to the local network interface. Remote clients will not be able to connect. Local clients can connect like this:

shell> mysql -h 127.0.0.1 -P 2273

To generate a new entry, invoke Instance Manager with the --passwd option and append the output to the /etc/mysqlmanager.passwd file. Here is an example:

shell> mysqlmanager --passwd >> /etc/mysqlmanager.passwd
Creating record for new user.
Enter user name: mike
Enter password: mikepass
Re-type password: mikepass

At the prompts, enter the username and password for the new Instance Manager user. You must enter the password twice. It does not echo to the screen, so double entry guards against entering a different password than you intend (if the two passwords do not match, no entry is generated).

The preceding command causes the following line to be added to /etc/mysqlmanager.passwd:

mike:*BBF1F551DD9DD96A01E66EC7DDC073911BAD17BA

Use of the --password option fails if mysqlmanager is invoked directly from an IBM 5250 terminal. To work around this, use a command like the following from the coommand line to generate the password entry:

shell< mysql -B --skip-column-name \
         -e 'SELECT CONCAT("user_name",":",PASSWORD("pass_val"));'

The output from the command can be used an entry in the /etc/mysqlmanager.passwd file.

4.6.9.5. MySQL Server Instance Status Monitoring

Important

MySQL Instance Manager is been deprecated in MySQL 5.1 and is removed in MySQL 6.0.

To monitor the status of each guarded server instance, the MySQL Instance Manager attempts to connect to the instance at regular intervals using the MySQL_Instance_Manager@localhost user account with a password of check_connection.

You are not required to create this account for MySQL Server; in fact, it is expected that it will not exist. Instance Manager can tell that a server is operational if the server accepts the connection attempt but refuses access for the account by returning a login error. However, these failed connection attempts are logged by the server to its general query log (see Section 5.2.2, “The General Query Log”).

Instance Manager also attempts a connection to nonguarded server instances when you use the SHOW INSTANCES or SHOW INSTANCE STATUS command. This is the only status monitoring done for nonguarded instances.

Instance Manager knows if a server instance fails at startup because it receives a status from the attempt. For an instance that starts but later crashes, Instance Manager receives a signal because it is the parent process of the instance.

4.6.9.6. Connecting to MySQL Instance Manager

Important

MySQL Instance Manager is been deprecated in MySQL 5.1 and is removed in MySQL 6.0.

After you set up a password file for the MySQL Instance Manager and Instance Manager is running, you can connect to it. The MySQL client-server protocol is used to communicate with the Instance Manager. For example, you can connect to it using the standard mysql client program:

shell> mysql --port=2273 --host=im.example.org --user=mysql --password

Instance Manager supports the version of the MySQL client-server protocol used by the client tools and libraries distributed with MySQL 4.1 or later, so other programs that use the MySQL C API also can connect to it.

4.6.9.7. MySQL Instance Manager Commands

Important

MySQL Instance Manager is been deprecated in MySQL 5.1 and is removed in MySQL 6.0.

After you connect to MySQL Instance Manager, you can issue commands. The following general principles apply to Instance Manager command execution:

  • Commands that take an instance name fail if the name is not a valid instance name.

  • Commands that take an instance name fail if the instance does not exist.

  • Instance Manager maintains information about instance configuration in an internal (in-memory) cache. Initially, this information comes from the configuration file if it exists, but some commands change the configuration of an instance. Commands that modify the configuration file fail if the file does not exist or is not accessible to Instance Manager.

  • On Windows, the standard file is my.ini in the directory where Instance Manager is installed. On Unix, the standard configuration file is /etc/my.cnf. To specify a different configuration file, start Instance Manager with the --defaults-file option.

  • If a [mysqld] instance section exists in the configuration file, it must not contain any Instance Manager-specific options (see Section 4.6.9.2, “MySQL Instance Manager Configuration Files”). Therefore, you must not add any of these options if you change the configuration for an instance named mysqld.

The following list describes the commands that Instance Manager accepts, with examples.

  • START INSTANCE instance_name

    This command attempts to start an offline instance. The command is asynchronous; it does not wait for the instance to start.

    mysql> START INSTANCE mysqld4;
    Query OK, 0 rows affected (0,00 sec)
    
  • STOP INSTANCE instance_name

    This command attempts to stop an instance. The command is synchronous; it waits for the instance to stop.

    mysql> STOP INSTANCE mysqld4;
    Query OK, 0 rows affected (0,00 sec)
    
  • SHOW INSTANCES

    Shows the names and status of all loaded instances.

    mysql> SHOW INSTANCES;
    +---------------+---------+
    | instance_name | status  |
    +---------------+---------+
    | mysqld3       | offline |
    | mysqld4       | online  |
    | mysqld2       | offline |
    +---------------+---------+
    
  • SHOW INSTANCE STATUS instance_name

    Shows status and version information for an instance.

    mysql> SHOW INSTANCE STATUS mysqld3;
    +---------------+--------+---------+
    | instance_name | status | version |
    +---------------+--------+---------+
    | mysqld3       | online | unknown |
    +---------------+--------+---------+
    
  • SHOW INSTANCE OPTIONS instance_name

    Shows the options used by an instance.

    mysql> SHOW INSTANCE OPTIONS mysqld3;
    +---------------+---------------------------------------------------+
    | option_name   | value                                             |
    +---------------+---------------------------------------------------+
    | instance_name | mysqld3                                           |
    | mysqld-path   | /home/cps/mysql/trees/mysql-4.1/sql/mysqld        |
    | port          | 3309                                              |
    | socket        | /tmp/mysql.sock3                                  |
    | pid-file      | hostname.pid3                                     |
    | datadir       | /home/cps/mysql_data/data_dir1/                   |
    | language      | /home/cps/mysql/trees/mysql-4.1/sql/share/english |
    +---------------+---------------------------------------------------+
    
  • SHOW instance_name LOG FILES

    The command lists all log files used by the instance. The result set contains the path to the log file and the log file size. If no log file path is specified in the instance section of the configuration file (for example, log=/var/mysql.log), the Instance Manager tries to guess its placement. If Instance Manager is unable to guess the log file placement you should specify the log file location explicitly by using a log option in the appropriate instance section of the configuration file.

    mysql> SHOW mysqld LOG FILES;
    +-------------+------------------------------------+----------+
    | Logfile     | Path                               | Filesize |
    +-------------+------------------------------------+----------+
    | ERROR LOG   | /home/cps/var/mysql/owlet.err      | 9186     |
    | GENERAL LOG | /home/cps/var/mysql/owlet.log      | 471503   |
    | SLOW LOG    | /home/cps/var/mysql/owlet-slow.log | 4463     |
    +-------------+------------------------------------+----------+
    

    Log options are described in Section 5.1.2, “Command Options”.

  • SHOW instance_name LOG {ERROR | SLOW | GENERAL} size[,offset_from_end]

    This command retrieves a portion of the specified log file. Because most users are interested in the latest log messages, the size parameter defines the number of bytes to retrieve from the end of the log. To retrieve data from the middle of the log file, specify the optional offset_from_end parameter. The following example retrieves 21 bytes of data, starting 23 bytes before the end of the log file and ending 2 bytes before the end:

    mysql> SHOW mysqld LOG GENERAL 21, 2;
    +---------------------+
    | Log                 |
    +---------------------+
    | using password: YES |
    +---------------------+
    
  • SET instance_name.option_name[=option_value]

    This command edits the specified instance's configuration section to change or add instance options. The option is added to the section is it is not already present. Otherwise, the new setting replaces the existing one.

    mysql> SET mysqld2.port=3322;
    Query OK, 0 rows affected (0.00 sec)
    

    Changes made to the configuration file do not take effect until the MySQL server is restarted. In addition, these changes are not stored in the instance manager's local cache of instance settings until a FLUSH INSTANCES command is executed.

  • UNSET instance_name.option_name

    This command removes an option from an instance's configuration section.

    mysql> UNSET mysqld2.port;
    Query OK, 0 rows affected (0.00 sec)
    

    Changes made to the configuration file do not take effect until the MySQL server is restarted. In addition, these changes are not stored in the instance manager's local cache of instance settings until a FLUSH INSTANCES command is executed.

  • FLUSH INSTANCES

    This command forces Instance Manager reread the configuration file and to refresh internal structures. This command should be performed after editing the configuration file. The command does not restart instances.

    mysql> FLUSH INSTANCES;
    Query OK, 0 rows affected (0.04 sec)
    

    FLUSH INSTANCES is deprecated and will be removed in MySQL 5.2.

4.6.10. mysqlmanagerc — Internal Test-Suite Program

This program was used internally for test purposes. As of MySQL 5.0, it is no longer used.

4.6.11. mysqlmanager-pwgen — Internal Test-Suite Program

This program was used internally for test purposes. As of MySQL 5.0, it is no longer used.

4.6.12. mysql_convert_table_format — Convert Tables to Use a Given Storage Engine

mysql_convert_table_format converts the tables in a database to use a particular storage engine (MyISAM by default). mysql_convert_table_format is written in Perl and requires that the DBI and DBD::mysql Perl modules be installed (see Section 2.4.21, “Perl Installation Notes”).

Invoke mysql_convert_table_format like this:

shell> mysql_convert_table_format [options]db_name

The db_name argument indicates the database containing the tables to be converted.

mysql_convert_table_format understands the options described in the following list.

  • --help

    Display a help message and exit.

  • --force

    Continue even if errors occur.

  • --host=host_name

    Connect to the MySQL server on the given host.

  • --password=password

    The password to use when connecting to the server. Note that the password value is not optional for this option, unlike for other MySQL programs. You can use an option file to avoid giving the password on the command line.

    Specifying a password on the command line should be considered insecure. See Section 5.5.6, “Keeping Your Password Secure”.

  • --port=port_num

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

  • --socket=path

    For connections to localhost, the Unix socket file to use.

  • --type=engine_name

    Specify the storage engine that the tables should be converted to use. The default is MyISAM if this option is not given.

    MySQL Enterprise For expert advice on choosing the optimum storage engine, subscribe to the MySQL Enterprise Monitor. For more information see http://www.mysql.com/products/enterprise/advisors.html.

  • --user=user_name

    The MySQL username to use when connecting to the server.

  • --verbose

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

  • --version

    Display version information and exit.

4.6.13. mysql_explain_log — Use EXPLAIN on Statements in Query Log

mysql_explain_log reads its standard input for query log contents. It uses EXPLAIN to analyze SELECT statements found in the input. UPDATE statements are rewritten to SELECT statements and also analyzed with EXPLAIN. mysql_explain_log then displays a summary of its results.

The results may assist you in determining which queries result in table scans and where it would be beneficial to add indexes to your tables.

Invoke mysql_explain_log like this, where log_file contains all or part of a MySQL query log:

shell> mysql_explain_log [options] < log_file

mysql_explain_log understands the following options:

  • --help, -?

    Display a help message and exit.

  • --date=YYMMDD, -d YYMMDD

    Select entries from the log only for the given date.

  • --host=host_name, -h host_name

    Connect to the MySQL server on the given host.

  • --password=password, -p password

    The password to use when connecting to the server.

    Specifying a password on the command line should be considered insecure. See Section 5.5.6, “Keeping Your Password Secure”.

  • --printerror=1, -e 1

    Enable error output.

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

  • --user=user_name, -u user_name

    The MySQL username to use when connecting to the server.

4.6.14. mysql_find_rows — Extract SQL Statements from Files

mysql_find_rows reads files containing SQL statements and extracts statements that match a given regular expression or that contain USE db_name or SET statements. The utility was written for use with update log files, but it can be used with other files that contain SQL statements.

Invoke mysql_find_rows like this:

shell> mysql_find_rows [options] [file_name ...]

Each file_name argument should be the name of file containing SQL statements. If no filenames are given, mysql_find_rows reads the standard input.

Examples:

mysql_find_rows --regexp=problem_table --rows=20 < update.log
mysql_find_rows --regexp=problem_table  update-log.1 update-log.2

mysql_find_rows supports the following options:

  • --help, --Information

    Display a help message and exit.

  • --regexp=pattern

    Display queries that match the pattern.

  • --rows=N

    Quit after displaying N queries.

  • --skip-use-db

    Do not include USE db_name statements in the output.

  • --start_row=N

    Start output from this row.

4.6.15. mysql_fix_extensions — Normalize Table Filename Extensions

mysql_fix_extensions converts the extensions for MyISAM (or ISAM) table files to their canonical forms. It looks for files with extensions matching any lettercase variant of .frm, .myd, .myi, .isd, and .ism and renames them to have extensions of .frm, .MYD, .MYI, .ISD, and .ISM, respectively. This can be useful after transferring the files from a system with case-insensitive filenames (such as Windows) to a system with case-sensitive filenames.

Invoke mysql_fix_extensions like this, where data_dir is the pathname to the MySQL data directory.

shell> mysql_fix_extensions data_dir

4.6.16. mysql_setpermission — Interactively Set Permissions in Grant Tables

mysql_setpermission is a Perl script that was originally written and contributed by Luuk de Boer. It interactively sets permissions in the MySQL grant tables. mysql_setpermission is written in Perl and requires that the DBI and DBD::mysql Perl modules be installed (see Section 2.4.21, “Perl Installation Notes”).

Invoke mysql_setpermission like this:

shell> mysql_setpermission [options]

options should be either --help to display the help message, or options that indicate how to connect to the MySQL server. The account used when you connect determines which permissions you have when attempting to modify existing permissions in the grant tables.

mysql_setpermissions also reads options from the [client] and [perl] groups in the .my.cnf file in your home directory, if the file exists.

mysql_setpermission understands the following options:

  • --help

    Display a help message and exit.

  • --host=host_name

    Connect to the MySQL server on the given host.

  • --password=password

    The password to use when connecting to the server. Note that the password value is not optional for this option, unlike for other MySQL programs. You can use an option file to avoid giving the password on the command line.

    Specifying a password on the command line should be considered insecure. See Section 5.5.6, “Keeping Your Password Secure”.

  • --port=port_num

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

  • --socket=path

    For connections to localhost, the Unix socket file to use.

  • --user=user_name

    The MySQL username to use when connecting to the server.

4.6.17. mysql_tableinfo — Generate Database Metadata

mysql_tableinfo creates tables and populates them with database metadata. It uses SHOW DATABASES, SHOW TABLES, SHOW TABLE STATUS, SHOW COLUMNS, and SHOW INDEX to obtain the metadata.

In MySQL 5.0 and up, the INFORMATION_SCHEMA database contains the same kind of information in the SCHEMATA, TABLES, COLUMNS, and STATISTICS tables. See Chapter 24, INFORMATION_SCHEMA Tables.

Invoke mysql_tableinfo like this:

shell> mysql_tableinfo [options] db_name [db_like [tbl_like]]

The db_name argument indicates which database mysql_tableinfo should use as the location for the metadata tables. The database will be created if it does not exist. The tables will be named db, tbl (or tbl_status), col, and idx.

If the db_like or tbl_like arguments are given, they are used as patterns and metadata is generated only for databases or tables that match the patterns. These arguments default to % if not given.

Examples:

mysql_tableinfo info
mysql_tableinfo info world
mysql_tableinfo info mydb tmp%

Each of the commands stores information into tables in the info database. The first stores information for all databases and tables. The second stores information for all tables in the world database. The third stores information for tables in the mydb database that have names matching the pattern tmp%.

mysql_tableinfo supports the following options:

Table 4.12. mysql_tableinfo Option Reference

FormatConfig FileDescriptionIntroduction
--clearclearBefore populating each metadata table, drop it if it exists 
--clear-onlyclear-onlySimilar to --clear, but exits after dropping the metadata tables to be populated. 
--colcolGenerate column metadata into the col table 
--help Display help message and exit 
--host=host_namehostConnect to the MySQL server on the given host 
--idxidxGenerate index metadata into the idx table 
--password=passwordpasswordThe password to use when connecting to the server -- not optional 
--port=port_numportThe TCP/IP port number to use for the connection 
--prefix=prefix_strprefixAdd prefix_str at the beginning of each metadata table name 
--quietquietBe silent except for errors 
--socket=pathsocketDisplay version information and exit 
--tbl-statustbl-statusUse SHOW TABLE STATUS instead of SHOW TABLES 
--user=user_name,userThe mysql_tableinfo username to use when connecting to the server 
  • --help

    Display a help message and exit.

  • --clear

    Before populating each metadata table, drop it if it exists.

  • --clear-only

    Similar to --clear, but exits after dropping the metadata tables to be populated.

  • --col

    Generate column metadata into the col table.

  • --host=host_name, -h host_name

    Connect to the MySQL server on the given host.

  • --idx

    Generate index metadata into the idx table.

  • --password=password, -ppassword

    The password to use when connecting to the server. Note that the password value is not optional for this option, unlike for other MySQL programs. You can use an option file to avoid giving the password on the command line.

    Specifying a password on the command line should be considered insecure. See Section 5.5.6, “Keeping Your Password Secure”.

  • --port=port_num, -P port_num

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

  • --prefix=prefix_str

    Add prefix_str at the beginning of each metadata table name.

  • --quiet, -q

    Be silent except for errors.

  • --socket=path, -S path

    The Unix socket file to use for the connection.

  • --tbl-status

    Use SHOW TABLE STATUS instead of SHOW TABLES. This provides more complete information, but is slower.

  • --user=user_name, -u user_name

    The MySQL username to use when connecting to the server.

4.6.18. mysql_waitpid — Kill Process and Wait for Its Termination

mysql_waitpid signals a process to terminate and waits for the process to exit. It uses the kill() system call and Unix signals, so it runs on Unix and Unix-like systems.

Invoke mysql_waitpid like this:

shell> mysql_waitpid [options] pid wait_time

mysql_waitpid sends signal 0 to the process identified by pid and waits up to wait_time seconds for the process to terminate. pid and wait_time must be positive integers.

If process termination occurs within the wait time or the process does not exist, mysql_waitpid returns 0. Otherwise, it returns 1.

If the kill() system call cannot handle signal 0, mysql_waitpid() uses signal 1 instead.

mysql_waitpid understands the following options:

  • --help, -?, -I

    Display a help message and exit.

  • --verbose, -v

    Verbose mode. Display a warning if signal 0 could not be used and signal 1 is used instead.

  • --version, -V

    Display version information and exit.

4.6.19. mysql_zap — Kill Processes That Match a Pattern

mysql_zap kills processes that match a pattern. It uses the ps command and Unix signals, so it runs on Unix and Unix-like systems.

Invoke mysql_zap like this:

shell> mysql_zap [-signal] [-?Ift] pattern

A process matches if its output line from the ps command contains the pattern. By default, mysql_zap asks for confirmation for each process. Respond y to kill the process, or q to exit mysql_zap. For any other response, mysql_zap does not attempt to kill the process.

If the -signal option is given, it specifies the name or number of the signal to send to each process. Otherwise, mysql_zap tries first with TERM (signal 15) and then with KILL (signal 9).

mysql_zap understands the following additional options:

  • --help, -?, -I

    Display a help message and exit.

  • -f

    Force mode. mysql_zap attempts to kill each process without confirmation.

  • -t

    Test mode. Display information about each process but do not kill it.

4.7. MySQL Program Development Utilities

This section describes some utilities that you may find useful when developing MySQL programs.

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

shell> my_print_defaults client mysql
--port=3306
--socket=/tmp/mysql.sock
--no-auto-rehash

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

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

4.7.1. msql2mysql — Convert mSQL Programs for Use with MySQL

Initially, the MySQL C API was developed to be very similar to that for the mSQL database system. Because of this, mSQL programs often can be converted relatively easily for use with MySQL by changing the names of the C API functions.

The msql2mysql utility performs the conversion of mSQL C API function calls to their MySQL equivalents. msql2mysql converts the input file in place, so make a copy of the original before converting it. For example, use msql2mysql like this:

shell> cp client-prog.c client-prog.c.orig
shell> msql2mysql client-prog.c
client-prog.c converted

Then examine client-prog.c and make any post-conversion revisions that may be necessary.

msql2mysql uses the replace utility to make the function name substitutions. See Section 4.8.2, “replace — A String-Replacement Utility”.

4.7.2. mysql_config — Get Compile Options for Compiling Clients

mysql_config provides you with useful information for compiling your MySQL client and connecting it to MySQL.

mysql_config supports the following options:

  • --cflags

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

  • --include

    Compiler options to find MySQL include files.

  • --libmysqld-libs, --embedded

    Libraries and options required to link with the MySQL embedded server.

  • --libs

    Libraries and options required to link with the MySQL client library.

  • --libs_r

    Libraries and options required to link with the thread-safe MySQL client library.

  • --port

    The default TCP/IP port number, defined when configuring MySQL.

  • --socket

    The default Unix socket file, defined when configuring MySQL.

  • --version

    Version number for the MySQL distribution.

If you invoke mysql_config with no options, it displays a list of all options that it supports, and their values:

shell> mysql_config
Usage: /usr/local/mysql/bin/mysql_config [options]
Options:
  --cflags         [-I/usr/local/mysql/include/mysql -mcpu=pentiumpro]
  --include        [-I/usr/local/mysql/include/mysql]
  --libs           [-L/usr/local/mysql/lib/mysql -lmysqlclient -lz
                    -lcrypt -lnsl -lm -L/usr/lib -lssl -lcrypto]
  --libs_r         [-L/usr/local/mysql/lib/mysql -lmysqlclient_r
                    -lpthread -lz -lcrypt -lnsl -lm -lpthread]
  --socket         [/tmp/mysql.sock]
  --port           [3306]
  --version        [4.0.16]
  --libmysqld-libs [-L/usr/local/mysql/lib/mysql -lmysqld -lpthread -lz
                    -lcrypt -lnsl -lm -lpthread -lrt]

You can use mysql_config within a command line to include the value that it displays for a particular option. For example, to compile a MySQL client program, use mysql_config as follows:

shell> CFG=/usr/local/mysql/bin/mysql_config
shell> sh -c "gcc -o progname `$CFG --include` progname.c `$CFG --libs`"

When you use mysql_config this way, be sure to invoke it within backtick (“`”) characters. That tells the shell to execute it and substitute its output into the surrounding command.

4.7.3. my_print_defaults — Display Options from Option Files

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

shell> my_print_defaults mysqlcheck client
--user=myusername
--password=secret
--host=localhost

The output consists of options, one per line, in the form that they would be specified on the command line.

my_print_defaults understands the following options:

  • --help, -?

    Display a help message and exit.

  • --config-file=file_name, --defaults-file=file_name, -c file_name

    Read only the given option file.

  • --debug=debug_options, -# debug_options

    Write a debugging log. The debug_options string often is 'd:t:o,file_name'. The default is 'd:t:o,/tmp/my_print_defaults.trace'.

  • --defaults-extra-file=file_name, --extra-file=file_name, -e file_name

    Read this option file after the global option file but (on Unix) before the user option file.

  • --defaults-group-suffix=suffix, -g suffix

    In addition to the groups named on the command line, read groups that have the given suffix.

  • --no-defaults, -n

    Return an empty string.

  • --verbose, -v

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

  • --version, -V

    Display version information and exit.

4.7.4. resolve_stack_dump — Resolve Numeric Stack Trace Dump to Symbols

resolve_stack_dump resolves a numeric stack dump to symbols.

Invoke resolve_stack_dump like this:

shell> resolve_stack_dump [options] symbols_file [numeric_dump_file]

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

resolve_stack_dump understands the options described in the following list.

  • --help, -h

    Display a help message and exit.

  • --numeric-dump-file=file_name, -n file_name

    Read the stack trace from the given file.

  • --symbols-file=file_name, -s file_name

    Use the given symbols file.

  • --version, -V

    Display version information and exit.

4.8. Miscellaneous Programs

4.8.1. perror — Explain Error Codes

For most system errors, MySQL displays, in addition to an internal text message, the system error code in one of the following styles:

message ... (errno: #)
message ... (Errcode: #)

You can find out what the error code means by examining the documentation for your system or by using the perror utility.

perror prints a description for a system error code or for a storage engine (table handler) error code.

Invoke perror like this:

shell> perror [options] errorcode ...

Example:

shell> perror 13 64
OS error code  13:  Permission denied
OS error code  64:  Machine is not on the network

To obtain the error message for a MySQL Cluster error code, invoke perror with the --ndb option:

shell> perror --ndb errorcode

Note that the meaning of system error messages may be dependent on your operating system. A given error code may mean different things on different operating systems.

perror supports the following options:

  • --help, --info, -I, -?

    Display a help message and exit.

  • --ndb

    Print the error message for a MySQL Cluster error code.

  • --silent, -s

    Silent mode. Print only the error message.

  • --verbose, -v

    Verbose mode. Print error code and message. This is the default behavior.

  • --version, -V

    Display version information and exit.

4.8.2. replace — A String-Replacement Utility

The replace utility program changes strings in place in files or on the standard input.

Invoke replace in one of the following ways:

shell> replace from to [from to] ... -- file_name [file_name] ...
shell> replace from to [from to] ... < file_name

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

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

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

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

shell> replace a b b a -- file1 file2 ...

The replace program is used by msql2mysql. See Section 4.7.1, “msql2mysql — Convert mSQL Programs for Use with MySQL”.

replace supports the following options:

  • -?, -I

    Display a help message and exit.

  • -#debug_options

    Enable debugging.

  • -s

    Silent mode. Print less information what the program does.

  • -v

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

  • -V

    Display version information and exit.

4.8.3. resolveip — Resolve Hostname to IP Address or Vice Versa

The resolveip utility resolves hostnames to IP addresses and vice versa.

Invoke resolveip like this:

shell> resolveip [options] {host_name|ip-addr} ...

resolveip understands the options described in the following list.

  • --help, -info, -?, -I

    Display a help message and exit.

  • --silent, -s

    Silent mode. Produce less output.

  • --version, -V

    Display version information and exit.