4.5 Client Programs

This section describes client programs that connect to the MySQL server.

 File: manual.info.tmp, Node: mysql, Next: mysqladmin, Prev: programs-client, Up: programs-client

4.5.1 mysql -- The MySQL Command-Line Client

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

If you have problems due to insufficient memory for large result sets, use the '--quick' option. This forces *note 'mysql': 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()' (https://dev.mysql.com/doc/c-api/5.7/en/mysql-use-result.html) C API function in the client/server library rather than 'mysql_store_result()' (https://dev.mysql.com/doc/c-api/5.7/en/mysql-store-result.html).

Note:

Alternatively, MySQL Shell offers access to the X DevAPI. For details, see MySQL Shell 8.0 (https://dev.mysql.com/doc/mysql-shell/8.0/en/).

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

 mysql DB_NAME

Or:

 mysql --user=USER_NAME --password DB_NAME

In this case, you'll need to enter your password in response to the prompt that *note 'mysql': mysql. displays:

 Enter password: YOUR_PASSWORD

Then type an SQL statement, end it with ';', '', or '' and press Enter.

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

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

 mysql DB_NAME < SCRIPT.SQL > OUTPUT.TAB

On Unix, the note 'mysql': mysql. client logs statements executed interactively to a history file. See note mysql-logging::.

 File: manual.info.tmp, Node: mysql-command-options, Next: mysql-commands, Prev: mysql, Up: mysql

4.5.1.1 mysql Client Options ............................

note 'mysql': mysql. supports the following options, which can be specified on the command line or in the '[mysql]' and '[client]' groups of an option file. For information about option files used by MySQL programs, see note option-files::.

mysql Client Options

Option Name Description IntroducedDeprecated

-auto-rehash

Enable automatic rehashing

-auto-vertical-output

Enable automatic vertical result set display

-batch

Do not use history file

-binary-as-hex

Display binary 5.7.19 values in
hexadecimal notation

-binary-mode

Disable - to - translation and treatment of as end-of-query

-bind-address

Use specified network interface to connect to MySQL Server

-character-sets-dir

Directory where character sets are installed

-column-names

Write column names in results

-column-type-info

Display result set metadata

-comments

Whether to retain or strip comments in statements sent to the server

-compress

Compress all information sent between client and server

-connect-expired-password

Indicate to server that client can handle expired-password sandbox mode

-connect-timeout

Number of seconds before connection timeout

-database

The database to use

-debug

Write debugging log; supported only if MySQL was built with debugging support

-debug-check

Print debugging information when program exits

-debug-info

Print debugging information, memory, and CPU statistics when program exits

-default-auth

Authentication plugin to use

-default-character-set

Specify default character set

-defaults-extra-file

Read named option file in addition to usual option files

-defaults-file

Read only named option file

-defaults-group-suffix

Option group suffix value

-delimiter

Set the statement delimiter

-enable-cleartext-plugin

Enable cleartext authentication plugin

-execute

Execute the statement and quit

-force

Continue even if an SQL error occurs

-get-server-public-key

Request RSA 5.7.23 public key from
server

-help

Display help message and exit

-histignore

Patterns specifying which statements to ignore for logging

-host

Host on which MySQL server is located

-html

Produce HTML output

-ignore-spaces

Ignore spaces after function names

-init-command

SQL statement to execute after connecting

-line-numbers

Write line numbers for errors

-local-infile

Enable or disable for LOCAL capability for LOAD DATA

-login-path

Read login path options from .mylogin.cnf

-max-allowed-packet

Maximum packet length to send to or receive from server

-max-join-size

The automatic limit for rows in a join when using -safe-updates

-named-commands

Enable named mysql commands

-net-buffer-length

Buffer size for TCP/IP and socket communication

-no-auto-rehash

Disable automatic rehashing

-no-beep

Do not beep when errors occur

-no-defaults

Read no option files

-one-database

Ignore statements except those for the default database named on the command line

-pager

Use the given command for paging query output

-password

Password to use when connecting to server

-pipe

Connect to server using named pipe (Windows only)

-plugin-dir

Directory where plugins are installed

-port

TCP/IP port number for connection

-print-defaults

Print default options

-prompt

Set the prompt to the specified format

-protocol

Transport protocol to use

-quick

Do not cache each query result

-raw

Write column values without escape conversion

-reconnect

If the connection to the server is lost, automatically try to reconnect

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

Allow only UPDATE and DELETE statements that specify key values

-secure-auth

Do not send Yes passwords to
server in old (pre-4.1) format

-select-limit

The automatic limit for SELECT statements when using -safe-updates

-server-public-key-path

Path name to file containing RSA public key

-shared-memory-base-name

Shared-memory name for shared-memory connections (Windows only)

-show-warnings

Show warnings after each statement if there are any

-sigint-ignore

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

-silent

Silent mode

-skip-auto-rehash

Disable automatic rehashing

-skip-column-names

Do not write column names in results

-skip-line-numbers

Skip line numbers for errors

-skip-named-commands

Disable named mysql commands

-skip-pager

Disable paging

-skip-reconnect

Disable reconnecting

-socket

Unix socket file or Windows named pipe to use

-ssl

Enable connection encryption

-ssl-ca

File that contains list of trusted SSL Certificate Authorities

-ssl-capath

Directory that contains trusted SSL Certificate Authority certificate files

-ssl-cert

File that contains X.509 certificate

-ssl-cipher

Permissible ciphers for connection encryption

-ssl-crl

File that contains certificate revocation lists

-ssl-crlpath

Directory that contains certificate revocation-list files

-ssl-key

File that contains X.509 key

-ssl-mode

Desired security 5.7.11 state of
connection to server

-ssl-verify-server-cert

Verify host name against server certificate Common Name identity

-syslog

Log interactive statements to syslog

-table

Display output in tabular format

-tee

Append a copy of output to named file

-tls-version

Permissible TLS 5.7.10 protocols for
encrypted connections

-unbuffered

Flush the buffer after each query

-user

MySQL user name to use when connecting to server

-verbose

Verbose mode

-version

Display version information and exit

-vertical

Print query output rows vertically (one line per column value)

-wait

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

-xml

Produce XML output

 File: manual.info.tmp, Node: mysql-commands, Next: mysql-logging, Prev: mysql-command-options, Up: mysql

4.5.1.2 mysql Client Commands .............................

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

 mysql> help

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

 For server side help, type 'help contents'

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

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

The use of short-form commands within multiple-line '/* ... /' comments is not supported. Short-form commands do work within single-line '/! ... /' version comments, as do '/+ ... /' optimizer-hint comments, which are stored in object definitions. If there is a concern that optimizer-hint comments may be stored in object definitions so that dump files when reloaded with 'mysql' would result in execution of such commands, either invoke note 'mysql': mysql. with the '--binary-mode' option or use a reload client other than *note 'mysql': mysql.

Here are a few tips about the 'pager' command:

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 note 'mysql': 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 note 'mysql': mysql. This is useful when you want to log some queries to a file, but not others.

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

Option Description

'' The current connection identifier

'' A counter that increments for each statement you issue

'' The full current date

'' The default database

'' The server host

'' The current delimiter

'' Minutes of the current time

'' A newline character

'' The current month in three-letter format (Jan, Feb, ...)

'' The current month in numeric format

'' am/pm

'' The current TCP/IP port or socket file

'' The current time, in 24-hour military time (0-23)

'' The current time, standard 12-hour time (1-12)

'' Semicolon

'' Seconds of the current time

'' A tab character

''
Your full 'USER_NAME@HOST_NAME' account name

'' Your user name

'' The server version

'' The current day of the week in three-letter format (Mon, Tue, ...)

'' The current year, four digits

'' The current year, two digits

'_' A space

' ' A space (a space follows the backslash)

''' Single quote

'"' Double quote

'\' A literal '' backslash character

''
X, for any 'X' not listed above

You can set the prompt in several ways:

 File: manual.info.tmp, Node: mysql-logging, Next: mysql-server-side-help, Prev: mysql-commands, Up: mysql

4.5.1.3 mysql Client Logging ............................

The *note 'mysql': mysql. client can do these types of logging for statements executed interactively:

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

How Logging Occurs

For each enabled logging destination, statement logging occurs as follows:

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

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

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

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

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

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

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

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

Controlling the History File

The '.mysql_history' file should be protected with a restrictive access mode because sensitive information might be written to it, such as the text of SQL statements that contain passwords. See note password-security-user::. Statements in the file are accessible from the note 'mysql': mysql. client when the 'up-arrow' key is used to recall the history. See *note mysql-history::.

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

syslog Logging Characteristics

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

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

Message size is limited to 1024 bytes.

Messages consist of the identifier 'MysqlClient' followed by these values:

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

 Mar  7 12:39:25 myhost MysqlClient[20824]:
   SYSTEM_USER:'oscar', MYSQL_USER:'my_oscar', CONNECTION_ID:23,
   DB_SERVER:'127.0.0.1', DB:'--', QUERY:'USE test;'
 Mar  7 12:39:28 myhost MysqlClient[20824]:
   SYSTEM_USER:'oscar', MYSQL_USER:'my_oscar', CONNECTION_ID:23,
   DB_SERVER:'127.0.0.1', DB:'test', QUERY:'SHOW TABLES;'

 File: manual.info.tmp, Node: mysql-server-side-help, Next: mysql-batch-commands, Prev: mysql-logging, Up: mysql

4.5.1.4 mysql Client Server-Side Help .....................................

 mysql> help SEARCH_STRING

If you provide an argument to the 'help' command, note 'mysql': 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 note server-side-help-support::).

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

 mysql> help me

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

Use *note 'help contents': help. to see a list of the help categories:

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

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

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

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

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

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

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

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

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

 File: manual.info.tmp, Node: mysql-batch-commands, Next: mysql-tips, Prev: mysql-server-side-help, Up: mysql

4.5.1.5 Executing SQL Statements from a Text File .................................................

The *note 'mysql': mysql. client typically is used interactively, like this:

 mysql DB_NAME

However, it is also possible to put your SQL statements in a file and then tell note 'mysql': 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 note 'mysql': mysql. as shown here:

 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:

 mysql < text_file

If you are already running *note 'mysql': 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 ''.

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

note 'mysql': 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 note 'mysql': mysql. to change its default character set. To do that, invoke *note 'mysql': mysql. with an option such as '--default-character-set=utf8'.

For more information about batch mode, see *note batch-mode::.

 File: manual.info.tmp, Node: mysql-tips, Prev: mysql-batch-commands, Up: mysql

4.5.1.6 mysql Client Tips .........................

This section provides information about techniques for more effective use of note 'mysql': mysql. and about note 'mysql': mysql. operational behavior.

Input-Line Editing

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

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

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

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

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

To see the current set of key bindings, temporarily put a line that says only 'bind' at the end of '.editrc'. Then *note 'mysql': mysql. shows the bindings when it starts.

Disabling Interactive History

The 'up-arrow' key enables you to recall input lines from current and previous sessions. In cases where a console is shared, this behavior may be unsuitable. *note 'mysql': mysql. supports disabling the interactive history partially or fully, depending on the host platform.

On Windows, the history is stored in memory. 'Alt+F7' deletes all input lines stored in memory for the current history buffer. It also deletes the list of sequential numbers in front of the input lines displayed with 'F7' and recalled (by number) with 'F9'. New input lines entered after you press 'Alt+F7' repopulate the current history buffer. Clearing the buffer does not prevent logging to the Windows Event Viewer, if the '--syslog' option was used to start *note 'mysql': mysql. Closing the console window also clears the current history buffer.

To disable interactive history on Unix, first delete the '.mysql_history' file, if it exists (previous entries are recalled otherwise). Then start note 'mysql': mysql. with the '--histignore=""' option to ignore all new input lines. To re-enable the recall (and logging) behavior, restart *note 'mysql': mysql. without the option.

If you prevent the '.mysql_history' file from being created (see note mysql-logging-history-file::) and use '--histignore=""' to start the *note 'mysql': mysql. client, the interactive history recall facility is disabled fully. Alternatively, if you omit the '--histignore' option, you can recall the input lines entered during the current session.

Unicode Support on Windows

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

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

  1. Open a console window.

  2. Go to the console window properties, select the font tab, and choose Lucida Console or some other compatible Unicode font. This is necessary because console windows start by default using a DOS raster font that is inadequate for Unicode.

  3. Execute note 'mysql.exe': mysql. with the '--default-character-set=utf8' (or 'utf8mb4') option. This option is necessary because 'utf16le' is one of the character sets that cannot be used as the client character set. See note charset-connection-impermissible-client-charset::.

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

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

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

 [mysql]
 default-character-set=utf8

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 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: Jones
     reply: jones@example.com
   mail_to: "John Smith" <smith@example.com>
       sbj: UTF-8
       txt: >>>>> "John" == John Smith writes:

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

 Yes, please do that.

 Regards,
 Jones
      file: inbox-jani-1
      hash: 190402944
 1 row in set (0.09 sec)

Using Safe-Updates Mode (-safe-updates)

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

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

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

The *note 'SET': set-variable. statement affects statement processing as follows:

To specify result set limits different from 1,000 and 1,000,000, you can override the defaults by using the '--select-limit' and '--max-join-size' options when you invoke *note 'mysql': mysql.:

 mysql --safe-updates --select-limit=500 --max-join-size=10000

It is possible for note 'UPDATE': update. and note 'DELETE': delete. statements to produce an error in safe-updates mode even with a key specified in the 'WHERE' clause, if the optimizer decides not to use the index on the key column:

As of MySQL 5.7.25, safe-updates mode also includes these behaviors:

Disabling mysql Auto-Reconnect

If the note 'mysql': 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 note 'mysql': 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 note 'mysql': mysql. terminate with an error if the connection has been lost, you can start the note 'mysql': mysql. client with the '--skip-reconnect' option.

For more information about auto-reconnect and its effect on state information when a reconnection occurs, see Automatic Reconnection Control (https://dev.mysql.com/doc/c-api/5.7/en/c-api-auto-reconnect.html).

mysql Client Parser Versus Server Parser

The note 'mysql': mysql. client uses a parser on the client side that is not a duplicate of the complete parser used by the note 'mysqld': mysqld. server on the server side. This can lead to differences in treatment of certain constructs. Examples:

 File: manual.info.tmp, Node: mysqladmin, Next: mysqlcheck, Prev: mysql, Up: programs-client

4.5.2 mysqladmin -- A MySQL Server Administration Program

*note 'mysqladmin': 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 *note 'mysqladmin': mysqladmin. like this:

 mysqladmin [OPTIONS] COMMAND [COMMAND-ARG] [COMMAND [COMMAND-ARG]] ...

*note 'mysqladmin': mysqladmin. supports the following commands. Some of the commands take an argument following the command name.

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

 $> mysqladmin proc stat
 +----+-------+-----------+----+---------+------+-------+------------------+
 | Id | User  | Host      | db | Command | Time | State | Info             |
 +----+-------+-----------+----+---------+------+-------+------------------+
 | 51 | jones | 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 *note 'mysqladmin status': mysqladmin. command result displays the following values:

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

note 'mysqladmin': mysqladmin. supports the following options, which can be specified on the command line or in the '[mysqladmin]' and '[client]' groups of an option file. For information about option files used by MySQL programs, see note option-files::.

mysqladmin Options

Option Name Description IntroducedDeprecated

-bind-address

Use specified network interface to connect to MySQL Server

-character-sets-dir

Directory where character sets can be found

-compress

Compress all information sent between client and server

-connect-timeout

Number of seconds before connection timeout

-count

Number of iterations to make for repeated command execution

-debug

Write debugging log

-debug-check

Print debugging information when program exits

-debug-info

Print debugging information, memory, and CPU statistics when program exits

-default-auth

Authentication plugin to use

-default-character-set

Specify default character set

-defaults-extra-file

Read named option file in addition to usual option files

-defaults-file

Read only named option file

-defaults-group-suffix

Option group suffix value

-enable-cleartext-plugin

Enable cleartext authentication plugin

-force

Continue even if an SQL error occurs

-get-server-public-key

Request RSA 5.7.23 public key from
server

-help

Display help message and exit

-host

Host on which MySQL server is located

-login-path

Read login path options from .mylogin.cnf

-no-beep

Do not beep when errors occur

-no-defaults

Read no option files

-password

Password to use when connecting to server

-pipe

Connect to server using named pipe (Windows only)

-plugin-dir

Directory where plugins are installed

-port

TCP/IP port number for connection

-print-defaults

Print default options

-protocol

Transport protocol to use

-relative

Show the difference between the current and previous values when used with the -sleep option

-secure-auth

Do not send Yes passwords to
server in old (pre-4.1) format

-server-public-key-path

Path name to file 5.7.23 containing RSA
public key

-shared-memory-base-name

Shared-memory name for shared-memory connections (Windows only)

-show-warnings

Show warnings after statement execution

-shutdown-timeout

The maximum number of seconds to wait for server shutdown

-silent

Silent mode

-sleep

Execute commands repeatedly, sleeping for delay seconds in between

-socket

Unix socket file or Windows named pipe to use

-ssl

Enable connection encryption

-ssl-ca

File that contains list of trusted SSL Certificate Authorities

-ssl-capath

Directory that contains trusted SSL Certificate Authority certificate files

-ssl-cert

File that contains X.509 certificate

-ssl-cipher

Permissible ciphers for connection encryption

-ssl-crl

File that contains certificate revocation lists

-ssl-crlpath

Directory that contains certificate revocation-list files

-ssl-key

File that contains X.509 key

-ssl-mode

Desired security 5.7.11 state of
connection to server

-ssl-verify-server-cert

Verify host name against server certificate Common Name identity

-tls-version

Permissible TLS 5.7.10 protocols for
encrypted connections

-user

MySQL user name to use when connecting to server

-verbose

Verbose mode

-version

Display version information and exit

-vertical

Print query output rows vertically (one line per column value)

-wait

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

 File: manual.info.tmp, Node: mysqlcheck, Next: mysqldump, Prev: mysqladmin, Up: programs-client

4.5.3 mysqlcheck -- A Table Maintenance Program

The *note 'mysqlcheck': mysqlcheck. client performs table maintenance: It checks, repairs, optimizes, or analyzes tables.

Each table is locked and therefore unavailable to other sessions while it is being processed, although for check operations, the table is locked with a 'READ' lock only (see note lock-tables::, for more information about 'READ' and 'WRITE' locks). Table maintenance operations can be time-consuming, particularly for large tables. If you use the '--databases' or '--all-databases' option to process all tables in one or more databases, an invocation of note 'mysqlcheck': mysqlcheck. might take a long time. (This is also true for the MySQL upgrade procedure if it determines that table checking is needed because it processes tables the same way.)

note 'mysqlcheck': mysqlcheck. must be used when the note 'mysqld': mysqld. server is running, which means that you do not have to stop the server to perform table maintenance.

note 'mysqlcheck': mysqlcheck. uses the SQL statements note 'CHECK TABLE': check-table, note 'REPAIR TABLE': repair-table, note 'ANALYZE TABLE': analyze-table, and note 'OPTIMIZE TABLE': 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 note table-maintenance-statements::.

All storage engines do not necessarily support all four maintenance operations. In such cases, an error message is displayed. For example, if 'test.t' is an 'MEMORY' table, an attempt to check it produces this result:

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

If note 'mysqlcheck': mysqlcheck. is unable to repair a table, see note rebuilding-tables:: for manual table repair strategies. This is the case, for example, for 'InnoDB' tables, which can be checked with note 'CHECK TABLE': check-table, but not repaired with note 'REPAIR TABLE': repair-table.

Caution:

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

There are three general ways to invoke *note 'mysqlcheck': mysqlcheck.:

 mysqlcheck [OPTIONS] DB_NAME [TBL_NAME ...]
 mysqlcheck [OPTIONS] --databases DB_NAME ...
 mysqlcheck [OPTIONS] --all-databases

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

note 'mysqlcheck': 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 note 'mysqlcheck': mysqlcheck. named 'mysqlrepair', or make a symbolic link to *note 'mysqlcheck': mysqlcheck. named 'mysqlrepair'. If you invoke 'mysqlrepair', it repairs tables.

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

Command Meaning

'mysqlrepair' The default option is '--repair'

'mysqlanalyze' The default option is '--analyze'

'mysqloptimize' The default option is '--optimize'

note 'mysqlcheck': mysqlcheck. supports the following options, which can be specified on the command line or in the '[mysqlcheck]' and '[client]' groups of an option file. For information about option files used by MySQL programs, see note option-files::.

mysqlcheck Options

Option Name Description IntroducedDeprecated

-all-databases

Check all tables in all databases

-all-in-1

Execute a single statement for each database that names all the tables from that database

-analyze

Analyze the tables

-auto-repair

If a checked table is corrupted, automatically fix it

-bind-address

Use specified network interface to connect to MySQL Server

-character-sets-dir

Directory where character sets are installed

-check

Check the tables for errors

-check-only-changed

Check only tables that have changed since the last check

-check-upgrade

Invoke CHECK TABLE with the FOR UPGRADE option

-compress

Compress all information sent between client and server

-databases

Interpret all arguments as database names

-debug

Write debugging log

-debug-check

Print debugging information when program exits

-debug-info

Print debugging information, memory, and CPU statistics when program exits

-default-auth

Authentication plugin to use

-default-character-set

Specify default character set

-defaults-extra-file

Read named option file in addition to usual option files

-defaults-file

Read only named option file

-defaults-group-suffix

Option group suffix value

-enable-cleartext-plugin

Enable cleartext 5.7.10 authentication
plugin

-extended

Check and repair tables

-fast

Check only tables that have not been closed properly

-fix-db-names

Convert database Yes names to 5.1
format

-fix-table-names

Convert table Yes names to 5.1
format

-force

Continue even if an SQL error occurs

-get-server-public-key

Request RSA 5.7.23 public key from
server

-help

Display help message and exit

-host

Host on which MySQL server is located

-login-path

Read login path options from .mylogin.cnf

-medium-check

Do a check that is faster than an -extended operation

-no-defaults

Read no option files

-optimize

Optimize the tables

-password

Password to use when connecting to server

-pipe

Connect to server using named pipe (Windows only)

-plugin-dir

Directory where plugins are installed

-port

TCP/IP port number for connection

-print-defaults

Print default options

-protocol

Transport protocol to use

-quick

The fastest method of checking

-repair

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

-secure-auth

Do not send Yes passwords to
server in old (pre-4.1) format

-server-public-key-path

Path name to file 5.7.23 containing RSA
public key

-shared-memory-base-name

Shared-memory name for shared-memory connections (Windows only)

-silent

Silent mode

-skip-database

Omit this database from performed operations

-socket

Unix socket file or Windows named pipe to use

-ssl

Enable connection encryption

-ssl-ca

File that contains list of trusted SSL Certificate Authorities

-ssl-capath

Directory that contains trusted SSL Certificate Authority certificate files

-ssl-cert

File that contains X.509 certificate

-ssl-cipher

Permissible ciphers for connection encryption

-ssl-crl

File that contains certificate revocation lists

-ssl-crlpath

Directory that contains certificate revocation-list files

-ssl-key

File that contains X.509 key

-ssl-mode

Desired security 5.7.11 state of
connection to server

-ssl-verify-server-cert

Verify host name against server certificate Common Name identity

-tables

Overrides the -databases or -B option

-tls-version

Permissible TLS 5.7.10 protocols for
encrypted connections

-use-frm

For repair operations on MyISAM tables

-user

MySQL user name to use when connecting to server

-verbose

Verbose mode

-version

Display version information and exit

-write-binlog

Log ANALYZE, OPTIMIZE, REPAIR statements to binary log. -skip-write-binlog adds NO_WRITE_TO_BINLOG to these statements

 File: manual.info.tmp, Node: mysqldump, Next: mysqlimport, Prev: mysqlcheck, Up: programs-client

4.5.4 mysqldump -- A Database Backup Program

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

*note 'mysqldump': mysqldump. requires at least the 'SELECT' privilege for dumped tables, 'SHOW VIEW' for dumped views, 'TRIGGER' for dumped triggers, 'LOCK TABLES' if the '--single-transaction' option is not used, and (as of MySQL 5.7.31) 'PROCESS' if the '--no-tablespaces' option is not used. Certain options might require other privileges as noted in the option descriptions.

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

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

Note:

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

 mysqldump [options] > dump.sql

However, UTF-16 is not permitted as a connection character set (see *note charset-connection-impermissible-client-charset::), so the dump file cannot be loaded correctly. To work around this issue, use the '--result-file' option, which creates the output in ASCII format:

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

It is not recommended to load a dump file when GTIDs are enabled on the server ('gtid_mode=ON'), if your dump file includes system tables. *note 'mysqldump': mysqldump. issues DML instructions for the system tables which use the non-transactional MyISAM storage engine, and this combination is not permitted when GTIDs are enabled.

Performance and Scalability Considerations

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

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

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

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

For additional information about note 'mysqldump': mysqldump, see note using-mysqldump::.

Invocation Syntax

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

 mysqldump [OPTIONS] DB_NAME [TBL_NAME ...]
 mysqldump [OPTIONS] --databases DB_NAME ...
 mysqldump [OPTIONS] --all-databases

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

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

Option Syntax - Alphabetical Summary

note 'mysqldump': mysqldump. supports the following options, which can be specified on the command line or in the '[mysqldump]' and '[client]' groups of an option file. For information about option files used by MySQL programs, see note option-files::.

mysqldump Options

Option Name Description IntroducedDeprecated

-add-drop-database

Add DROP DATABASE statement before each CREATE DATABASE statement

-add-drop-table

Add DROP TABLE statement before each CREATE TABLE statement

-add-drop-trigger

Add DROP TRIGGER statement before each CREATE TRIGGER statement

-add-locks

Surround each table dump with LOCK TABLES and UNLOCK TABLES statements

-all-databases

Dump all tables in all databases

-allow-keywords

Allow creation of column names that are keywords

-apply-slave-statements

Include STOP SLAVE prior to CHANGE MASTER statement and START SLAVE at end of output

-bind-address

Use specified network interface to connect to MySQL Server

-character-sets-dir

Directory where character sets are installed

-comments

Add comments to dump file

-compact

Produce more compact output

-compatible

Produce output that is more compatible with other database systems or with older MySQL servers

-complete-insert

Use complete INSERT statements that include column names

-compress

Compress all information sent between client and server

-create-options

Include all MySQL-specific table options in CREATE TABLE statements

-databases

Interpret all name arguments as database names

-debug

Write debugging log

-debug-check

Print debugging information when program exits

-debug-info

Print debugging information, memory, and CPU statistics when program exits

-default-auth

Authentication plugin to use

-default-character-set

Specify default character set

-defaults-extra-file

Read named option file in addition to usual option files

-defaults-file

Read only named option file

-defaults-group-suffix

Option group suffix value

-delete-master-logs

On a replication source server, delete the binary logs after performing the dump operation

-disable-keys

For each table, surround INSERT statements with statements to disable and enable keys

-dump-date

Include dump date as "Dump completed on" comment if -comments is given

-dump-slave

Include CHANGE MASTER statement that lists binary log coordinates of replica's source

-enable-cleartext-plugin

Enable cleartext 5.7.10 authentication
plugin

-events

Dump events from dumped databases

-extended-insert

Use multiple-row INSERT syntax

-fields-enclosed-by

This option is used with the -tab option and has the same meaning as the corresponding clause for LOAD DATA

-fields-escaped-by

This option is used with the -tab option and has the same meaning as the corresponding clause for LOAD DATA

-fields-optionally-enclosed-by

This option is used with the -tab option and has the same meaning as the corresponding clause for LOAD DATA

-fields-terminated-by

This option is used with the -tab option and has the same meaning as the corresponding clause for LOAD DATA

-flush-logs

Flush MySQL server log files before starting dump

-flush-privileges

Emit a FLUSH PRIVILEGES statement after dumping mysql database

-force

Continue even if an SQL error occurs during a table dump

-get-server-public-key

Request RSA 5.7.23 public key from
server

-help

Display help message and exit

-hex-blob

Dump binary columns using hexadecimal notation

-host

Host on which MySQL server is located

-ignore-error

Ignore specified errors

-ignore-table

Do not dump given table

-include-master-host-port

Include MASTER_HOST/MASTER_PORT options in CHANGE MASTER statement produced with -dump-slave

-insert-ignore

Write INSERT IGNORE rather than INSERT statements

-lines-terminated-by

This option is used with the -tab option and has the same meaning as the corresponding clause for LOAD DATA

-lock-all-tables

Lock all tables across all databases

-lock-tables

Lock all tables before dumping them

-log-error

Append warnings and errors to named file

-login-path

Read login path options from .mylogin.cnf

-master-data

Write the binary log file name and position to the output

-max-allowed-packet

Maximum packet length to send to or receive from server

-net-buffer-length

Buffer size for TCP/IP and socket communication

-no-autocommit

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

-no-create-db

Do not write CREATE DATABASE statements

-no-create-info

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

-no-data

Do not dump table contents

-no-defaults

Read no option files

-no-set-names

Same as -skip-set-charset

-no-tablespaces

Do not write any CREATE LOGFILE GROUP or CREATE TABLESPACE statements in output

-opt

Shorthand for -add-drop-table -add-locks -create-options -disable-keys -extended-insert -lock-tables -quick -set-charset

-order-by-primary

Dump each table's rows sorted by its primary key, or by its first unique index

-password

Password to use when connecting to server

-pipe

Connect to server using named pipe (Windows only)

-plugin-dir

Directory where plugins are installed

-port

TCP/IP port number for connection

-print-defaults

Print default options

-protocol

Transport protocol to use

-quick

Retrieve rows for a table from the server a row at a time

-quote-names

Quote identifiers within backtick characters

-replace

Write REPLACE statements rather than INSERT statements

-result-file

Direct output to a given file

-routines

Dump stored routines (procedures and functions) from dumped databases

-secure-auth

Do not send Yes passwords to
server in old (pre-4.1) format

-server-public-key-path

Path name to file 5.7.23 containing RSA
public key

-set-charset

Add SET NAMES default_character_set to output

-set-gtid-purged

Whether to add SET @@GLOBAL.GTID_PURGED to output

-shared-memory-base-name

Shared-memory name for shared-memory connections (Windows only)

-single-transaction

Issue a BEGIN SQL statement before dumping data from server

-skip-add-drop-table

Do not add a DROP TABLE statement before each CREATE TABLE statement

-skip-add-locks

Do not add locks

-skip-comments

Do not add comments to dump file

-skip-compact

Do not produce more compact output

-skip-disable-keys

Do not disable keys

-skip-extended-insert

Turn off extended-insert

-skip-mysql-schema

Do not drop the 5.7.36 mysql schema

-skip-opt

Turn off options set by -opt

-skip-quick

Do not retrieve rows for a table from the server a row at a time

-skip-quote-names

Do not quote identifiers

-skip-set-charset

Do not write SET NAMES statement

-skip-triggers

Do not dump triggers

-skip-tz-utc

Turn off tz-utc

-socket

Unix socket file or Windows named pipe to use

-ssl

Enable connection encryption

-ssl-ca

File that contains list of trusted SSL Certificate Authorities

-ssl-capath

Directory that contains trusted SSL Certificate Authority certificate files

-ssl-cert

File that contains X.509 certificate

-ssl-cipher

Permissible ciphers for connection encryption

-ssl-crl

File that contains certificate revocation lists

-ssl-crlpath

Directory that contains certificate revocation-list files

-ssl-key

File that contains X.509 key

-ssl-mode

Desired security 5.7.11 state of
connection to server

-ssl-verify-server-cert

Verify host name against server certificate Common Name identity

-tab

Produce tab-separated data files

-tables

Override -databases or -B option

-tls-version

Permissible TLS 5.7.10 protocols for
encrypted connections

-triggers

Dump triggers for each dumped table

-tz-utc

Add SET TIME_ZONE='+00:00' to dump file

-user

MySQL user name to use when connecting to server

-verbose

Verbose mode

-version

Display version information and exit

-where

Dump only rows selected by given WHERE condition

-xml

Produce XML output

Connection Options

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

Option-File Options

These options are used to control which option files to read.

DDL Options

Usage scenarios for *note 'mysqldump': mysqldump. include setting up an entire new MySQL instance (including database tables), and replacing data inside an existing instance with existing databases and tables. The following options let you specify which things to tear down and set up when restoring a dump, by encoding various DDL statements within the dump file.

Debug Options

The following options print debugging information, encode debugging information in the dump file, or let the dump operation proceed regardless of potential problems.

Help Options

The following options display information about the *note 'mysqldump': mysqldump. command itself.

Internationalization Options

The following options change how the *note 'mysqldump': mysqldump. command represents character data with national language settings.

Replication Options

The *note 'mysqldump': mysqldump. command is frequently used to create an empty instance, or an instance including data, on a replica server in a replication configuration. The following options apply to dumping and restoring data on replication source and replica servers.

Format Options

The following options specify how to represent the entire dump file or certain kinds of data in the dump file. They also control whether certain optional information is written to the dump file.

Filtering Options

The following options control which kinds of schema objects are written to the dump file: by category, such as triggers or events; by name, for example, choosing which databases and tables to dump; or even filtering rows from the table data using a 'WHERE' clause.

Performance Options

The following options are the most relevant for the performance particularly of the restore operations. For large data sets, restore operation (processing the 'INSERT' statements in the dump file) is the most time-consuming part. When it is urgent to restore data quickly, plan and test the performance of this stage in advance. For restore times measured in hours, you might prefer an alternative backup and restore solution, such as *note MySQL Enterprise Backup: mysql-enterprise-backup. for 'InnoDB'-only and mixed-use databases.

Performance is also affected by the transactional options, primarily for the dump operation.

Transactional Options

The following options trade off the performance of the dump operation, against the reliability and consistency of the exported data.

Option Groups

When you selectively enable or disable the effect of a group option, order is important because options are processed first to last. For example, '--disable-keys' '--lock-tables' '--skip-opt' would not have the intended effect; it is the same as '--skip-opt' by itself.

Examples

To make a backup of an entire database:

 mysqldump DB_NAME > BACKUP-FILE.SQL

To load the dump file back into the server:

 mysql DB_NAME < BACKUP-FILE.SQL

Another way to reload the dump file:

 mysql -e "source /PATH-TO-BACKUP/BACKUP-FILE.SQL" DB_NAME

*note 'mysqldump': mysqldump. is also very useful for populating databases by copying data from one MySQL server to another:

 mysqldump --opt DB_NAME | mysql --host=REMOTE_HOST -C DB_NAME

You can dump several databases with one command:

 mysqldump --databases DB_NAME1 [DB_NAME2 ...] > my_databases.sql

To dump all databases, use the '--all-databases' option:

 mysqldump --all-databases > all_databases.sql

For 'InnoDB' tables, *note 'mysqldump': mysqldump. provides a way of making an online backup:

 mysqldump --all-databases --master-data --single-transaction > all_databases.sql

This backup acquires a global read lock on all tables (using 'FLUSH TABLES WITH READ LOCK') at the beginning of the dump. As soon as this lock has been acquired, the binary log coordinates are read and the lock is released. If long updating statements are running when the *note 'FLUSH': flush. statement is issued, the MySQL server may get stalled until those statements finish. After that, the dump becomes lock free and does not disturb reads and writes on the tables. If the update statements that the MySQL server receives are short (in terms of execution time), the initial lock period should not be noticeable, even with many updates.

For point-in-time recovery (also known as 'roll-forward,' when you need to restore an old backup and replay the changes that happened since that backup), it is often useful to rotate the binary log (see *note binary-log::) or at least know the binary log coordinates to which the dump corresponds:

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

Or:

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

The '--master-data' and '--single-transaction' options can be used simultaneously, which provides a convenient way to make an online backup suitable for use prior to point-in-time recovery if tables are stored using the 'InnoDB' storage engine.

For more information on making backups, see note backup-methods::, and note backup-strategy-example::.

Restrictions

*note 'mysqldump': mysqldump. does not dump the 'INFORMATION_SCHEMA', 'performance_schema', or 'sys' schema by default. To dump any of these, name them explicitly on the command line. You can also name them with the '--databases' option. For 'INFORMATION_SCHEMA' and 'performance_schema', also use the '--skip-lock-tables' option.

note 'mysqldump': mysqldump. does not dump the NDB Cluster note 'ndbinfo': mysql-cluster-ndbinfo. information database.

note 'mysqldump': mysqldump. does not dump 'InnoDB' note 'CREATE TABLESPACE': create-tablespace. statements.

note 'mysqldump': mysqldump. always strips the 'NO_AUTO_CREATE_USER' SQL mode as 'NO_AUTO_CREATE_USER' is not compatible with MySQL 8.0. It remains stripped even when importing back into MySQL 5.7, which means that stored routines could behave differently after restoring a dump if they rely upon this particular sql_mode. It is stripped as of note 'mysqldump': mysqldump. 5.7.24.

It is not recommended to restore from a dump made using note 'mysqldump': mysqldump. to a MySQL 5.6.9 or earlier server that has GTIDs enabled. See note replication-gtids-restrictions::.

*note 'mysqldump': mysqldump. includes statements to recreate the 'general_log' and 'slow_query_log' tables for dumps of the 'mysql' database. Log table contents are not dumped.

If you encounter problems backing up views due to insufficient privileges, see *note view-restrictions:: for a workaround.

 File: manual.info.tmp, Node: mysqlimport, Next: mysqlpump, Prev: mysqldump, Up: programs-client

4.5.5 mysqlimport -- A Data Import Program

The note 'mysqlimport': mysqlimport. client provides a command-line interface to the note 'LOAD DATA': load-data. SQL statement. Most options to note 'mysqlimport': mysqlimport. correspond directly to clauses of note 'LOAD DATA': load-data. syntax. See *note load-data::.

Invoke *note 'mysqlimport': mysqlimport. like this:

 mysqlimport [OPTIONS] DB_NAME TEXTFILE1 [TEXTFILE2 ...]

For each text file named on the command line, *note 'mysqlimport': mysqlimport. strips any extension from the file name and uses the result to determine the name of the table into which to import the file's contents. For example, files named 'patient.txt', 'patient.text', and 'patient' all would be imported into a table named 'patient'.

note 'mysqlimport': mysqlimport. supports the following options, which can be specified on the command line or in the '[mysqlimport]' and '[client]' groups of an option file. For information about option files used by MySQL programs, see note option-files::.

mysqlimport Options

Option Name Description IntroducedDeprecated

-bind-address

Use specified network interface to connect to MySQL Server

-character-sets-dir

Directory where character sets can be found

-columns

This option takes a comma-separated list of column names as its value

-compress

Compress all information sent between client and server

-debug

Write debugging log

-debug-check

Print debugging information when program exits

-debug-info

Print debugging information, memory, and CPU statistics when program exits

-default-auth

Authentication plugin to use

-default-character-set

Specify default character set

-defaults-extra-file

Read named option file in addition to usual option files

-defaults-file

Read only named option file

-defaults-group-suffix

Option group suffix value

-delete

Empty the table before importing the text file

-enable-cleartext-plugin

Enable cleartext 5.7.10 authentication
plugin

-fields-enclosed-by

This option has the same meaning as the corresponding clause for LOAD DATA

-fields-escaped-by

This option has the same meaning as the corresponding clause for LOAD DATA

-fields-optionally-enclosed-by

This option has the same meaning as the corresponding clause for LOAD DATA

-fields-terminated-by

This option has the same meaning as the corresponding clause for LOAD DATA

-force

Continue even if an SQL error occurs

-get-server-public-key

Request RSA 5.7.23 public key from
server

-help

Display help message and exit

-host

Host on which MySQL server is located

-ignore

See the description for the -replace option

-ignore-lines

Ignore the first N lines of the data file

-lines-terminated-by

This option has the same meaning as the corresponding clause for LOAD DATA

-local

Read input files locally from the client host

-lock-tables

Lock all tables for writing before processing any text files

-login-path

Read login path options from .mylogin.cnf

-low-priority

Use LOW_PRIORITY when loading the table

-no-defaults

Read no option files

-password

Password to use when connecting to server

-pipe

Connect to server using named pipe (Windows only)

-plugin-dir

Directory where plugins are installed

-port

TCP/IP port number for connection

-print-defaults

Print default options

-protocol

Transport protocol to use

-replace

The -replace and -ignore options control handling of input rows that duplicate existing rows on unique key values

-secure-auth

Do not send Yes passwords to
server in old (pre-4.1) format

-server-public-key-path

Path name to file 5.7.23 containing RSA
public key

-shared-memory-base-name

Shared-memory name for shared-memory connections (Windows only)

-silent

Produce output only when errors occur

-socket

Unix socket file or Windows named pipe to use

-ssl

Enable connection encryption

-ssl-ca

File that contains list of trusted SSL Certificate Authorities

-ssl-capath

Directory that contains trusted SSL Certificate Authority certificate files

-ssl-cert

File that contains X.509 certificate

-ssl-cipher

Permissible ciphers for connection encryption

-ssl-crl

File that contains certificate revocation lists

-ssl-crlpath

Directory that contains certificate revocation-list files

-ssl-key

File that contains X.509 key

-ssl-mode

Desired security 5.7.11 state of
connection to server

-ssl-verify-server-cert

Verify host name against server certificate Common Name identity

-tls-version

Permissible TLS 5.7.10 protocols for
encrypted connections

-use-threads

Number of threads for parallel file-loading

-user

MySQL user name to use when connecting to server

-verbose

Verbose mode

-version

Display version information and exit

Here is a sample session that demonstrates use of *note 'mysqlimport': mysqlimport.:

 $> mysql -e 'CREATE TABLE imptest(id INT, n VARCHAR(30))' test
 $> ed
 a
 100     Max Sydow
 101     Count Dracula
 .
 w imptest.txt
 32
 q
 $> 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
 $> mysqlimport --local test imptest.txt
 test.imptest: Records: 2  Deleted: 0  Skipped: 0  Warnings: 0
 $> mysql -e 'SELECT * FROM imptest' test
 +------+---------------+
 | id   | n             |
 +------+---------------+
 |  100 | Max Sydow     |
 |  101 | Count Dracula |
 +------+---------------+

 File: manual.info.tmp, Node: mysqlpump, Next: mysqlshow, Prev: mysqlimport, Up: programs-client

4.5.6 mysqlpump -- A Database Backup Program

The *note 'mysqlpump': mysqlpump. client utility performs logical backups, producing a set of SQL statements that can be executed to reproduce the original database object definitions and table data. It dumps one or more MySQL databases for backup or transfer to another SQL server.

*note 'mysqlpump': mysqlpump. features include:

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

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

Note:

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

 mysqlpump [options] > dump.sql

However, UTF-16 is not permitted as a connection character set (see *note charset-connection::), so the dump file does not load correctly. To work around this issue, use the '--result-file' option, which creates the output in ASCII format:

 mysqlpump [options] --result-file=dump.sql

mysqlpump Invocation Syntax

By default, note 'mysqlpump': mysqlpump. dumps all databases (with certain exceptions noted in note mysqlpump-restrictions::). To specify this behavior explicitly, use the '--all-databases' option:

 mysqlpump --all-databases

To dump a single database, or certain tables within that database, name the database on the command line, optionally followed by table names:

 mysqlpump DB_NAME
 mysqlpump DB_NAME TBL_NAME1 TBL_NAME2 ...

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

 mysqlpump --databases DB_NAME1 DB_NAME2 ...

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

 mysqlpump --exclude-databases=% --users

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

note 'mysqlpump': mysqlpump. supports several options for including or excluding databases, tables, stored programs, and user definitions. See note mysqlpump-filtering::.

To reload a dump file, execute the statements that it contains. For example, use the *note 'mysql': mysql. client:

 mysqlpump [options] > dump.sql
 mysql < dump.sql

The following discussion provides additional *note 'mysqlpump': mysqlpump. usage examples.

To see a list of the options note 'mysqlpump': mysqlpump. supports, issue the command note 'mysqlpump --help': mysqlpump.

mysqlpump Option Summary

note 'mysqlpump': mysqlpump. supports the following options, which can be specified on the command line or in the '[mysqlpump]' and '[client]' groups of an option file. (Prior to MySQL 5.7.30, note 'mysqlpump': mysqlpump. read the '[mysql_dump]' group rather than '[mysqlpump]'. As of 5.7.30, '[mysql_dump]' is still accepted but is deprecated.) For information about option files used by MySQL programs, see *note option-files::.

mysqlpump Options

Option Name Description Introduced

-add-drop-database

Add DROP DATABASE statement before each CREATE DATABASE statement

-add-drop-table

Add DROP TABLE statement before each CREATE TABLE statement

-add-drop-user

Add DROP USER statement before each CREATE USER statement

-add-locks

Surround each table dump with LOCK TABLES and UNLOCK TABLES statements

-all-databases

Dump all databases

-bind-address

Use specified network interface to connect to MySQL Server

-character-sets-dir

Directory where character sets are installed

-complete-insert

Use complete INSERT statements that include column names

-compress

Compress all information sent between client and server

-compress-output

Output compression algorithm

-databases

Interpret all name arguments as database names

-debug

Write debugging log

-debug-check

Print debugging information when program exits

-debug-info

Print debugging information, memory, and CPU statistics when program exits

-default-auth

Authentication plugin to use

-default-character-set

Specify default character set

-default-parallelism

Default number of threads for parallel processing

-defaults-extra-file

Read named option file in addition to usual option files

-defaults-file

Read only named option file

-defaults-group-suffix

Option group suffix value

-defer-table-indexes

For reloading, defer index creation until after loading table rows

-events

Dump events from dumped databases

-exclude-databases

Databases to exclude from dump

-exclude-events

Events to exclude from dump

-exclude-routines

Routines to exclude from dump

-exclude-tables

Tables to exclude from dump

-exclude-triggers

Triggers to exclude from dump

-exclude-users

Users to exclude from dump

-extended-insert

Use multiple-row INSERT syntax

-get-server-public-key

Request RSA public 5.7.23 key from server

-help

Display help message and exit

-hex-blob

Dump binary columns using hexadecimal notation

-host

Host on which MySQL server is located

-include-databases

Databases to include in dump

-include-events

Events to include in dump

-include-routines

Routines to include in dump

-include-tables

Tables to include in dump

-include-triggers

Triggers to include in dump

-include-users

Users to include in dump

-insert-ignore

Write INSERT IGNORE rather than INSERT statements

-log-error-file

Append warnings and errors to named file

-login-path

Read login path options from .mylogin.cnf

-max-allowed-packet

Maximum packet length to send to or receive from server

-net-buffer-length

Buffer size for TCP/IP and socket communication

-no-create-db

Do not write CREATE DATABASE statements

-no-create-info

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

-no-defaults

Read no option files

-parallel-schemas

Specify schema-processing parallelism

-password

Password to use when connecting to server

-plugin-dir

Directory where plugins are installed

-port

TCP/IP port number for connection

-print-defaults

Print default options

-protocol

Transport protocol to use

-replace

Write REPLACE statements rather than INSERT statements

-result-file

Direct output to a given file

-routines

Dump stored routines (procedures and functions) from dumped databases

-secure-auth

Do not send passwords to server in old (pre-4.1) format

-server-public-key-path

Path name to file 5.7.23 containing RSA
public key

-set-charset

Add SET NAMES default_character_set to output

-set-gtid-purged

Whether to add SET 5.7.18 @@GLOBAL.GTID_PURGED
to output

-single-transaction

Dump tables within single transaction

-skip-definer

Omit DEFINER and SQL SECURITY clauses from view and stored program CREATE statements

-skip-dump-rows

Do not dump table rows

-socket

Unix socket file or Windows named pipe to use

-ssl

Enable connection encryption

-ssl-ca

File that contains list of trusted SSL Certificate Authorities

-ssl-capath

Directory that contains trusted SSL Certificate Authority certificate files

-ssl-cert

File that contains X.509 certificate

-ssl-cipher

Permissible ciphers for connection encryption

-ssl-crl

File that contains certificate revocation lists

-ssl-crlpath

Directory that contains certificate revocation-list files

-ssl-key

File that contains X.509 key

-ssl-mode

Desired security 5.7.11 state of connection
to server

-ssl-verify-server-cert

Verify host name against server certificate Common Name identity

-tls-version

Permissible TLS 5.7.10 protocols for
encrypted connections

-triggers

Dump triggers for each dumped table

-tz-utc

Add SET TIME_ZONE='+00:00' to dump file

-user

MySQL user name to use when connecting to server

-users

Dump user accounts

-version

Display version information and exit

-watch-progress

Display progress indicator

mysqlpump Option Descriptions

mysqlpump Object Selection

*note 'mysqlpump': mysqlpump. has a set of inclusion and exclusion options that enable filtering of several object types and provide flexible control over which objects to dump:

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

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

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

Wildcard characters are permitted in the object names:

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

For users, a name specified without a host part is interpreted with an implied host of '%'. For example, 'u1' and 'u1@%' are equivalent. This is the same equivalence that applies in MySQL generally (see *note account-names::).

Inclusion and exclusion options interact as follows:

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

 mysqlpump --include-databases=db1,db2 --exclude-tables=db1.t1,db2.t2

The following options provide alternative ways to specify which databases to dump:

mysqlpump Parallel Processing

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

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

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

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

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

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

Examples:

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

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

 mysqlpump --parallel-schemas=db1,db2 --parallel-schemas=db3
           --default-parallelism=4

This is the same as the previous example except that all queues use four threads.

 mysqlpump --parallel-schemas=5:db1,db2 --parallel-schemas=3:db3

The queue for 'db1' and 'db2' uses five threads, the queue for 'db3' uses three threads, and the default queue uses the default of two threads.

As a special case, with '--default-parallelism=0' and no '--parallel-schemas' options, *note 'mysqlpump': mysqlpump. runs as a single-threaded process and creates no queues.

Note:

Before MySQL 5.7.11, use of the '--single-transaction' option is mutually exclusive with parallelism. To use '--single-transaction', disable parallelism by setting '--default-parallelism' to 0 and not using any instances of '--parallel-schemas':

 mysqlpump --single-transaction --default-parallelism=0

mysqlpump Restrictions

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

note 'mysqlpump': mysqlpump. does not dump 'InnoDB' note 'CREATE TABLESPACE': create-tablespace. statements.

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

 mysqlpump mysql user db ...

 File: manual.info.tmp, Node: mysqlshow, Next: mysqlslap, Prev: mysqlpump, Up: programs-client

4.5.7 mysqlshow -- Display Database, Table, and Column Information

The *note 'mysqlshow': mysqlshow. client can be used to quickly see which databases exist, their tables, or a table's columns or indexes.

note 'mysqlshow': mysqlshow. provides a command-line interface to several SQL note 'SHOW': show. statements. See note show::. The same information can be obtained by using those statements directly. For example, you can issue them from the note 'mysql': mysql. client program.

Invoke *note 'mysqlshow': mysqlshow. like this:

 mysqlshow [OPTIONS] [DB_NAME [TBL_NAME [COL_NAME]]]

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, note 'mysqlshow': 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.

note 'mysqlshow': mysqlshow. supports the following options, which can be specified on the command line or in the '[mysqlshow]' and '[client]' groups of an option file. For information about option files used by MySQL programs, see note option-files::.

mysqlshow Options

Option Name Description IntroducedDeprecated

-bind-address

Use specified network interface to connect to MySQL Server

-character-sets-dir

Directory where character sets can be found

-compress

Compress all information sent between client and server

-count

Show the number of rows per table

-debug

Write debugging log

-debug-check

Print debugging information when program exits

-debug-info

Print debugging information, memory, and CPU statistics when program exits

-default-auth

Authentication plugin to use

-default-character-set

Specify default character set

-defaults-extra-file

Read named option file in addition to usual option files

-defaults-file

Read only named option file

-defaults-group-suffix

Option group suffix value

-enable-cleartext-plugin

Enable cleartext 5.7.10 authentication
plugin

-get-server-public-key

Request RSA 5.7.23 public key from
server

-help

Display help message and exit

-host

Host on which MySQL server is located

-keys

Show table indexes

-login-path

Read login path options from .mylogin.cnf

-no-defaults

Read no option files

-password

Password to use when connecting to server

-pipe

Connect to server using named pipe (Windows only)

-plugin-dir

Directory where plugins are installed

-port

TCP/IP port number for connection

-print-defaults

Print default options

-protocol

Transport protocol to use

-secure-auth

Do not send Yes passwords to
server in old (pre-4.1) format

-server-public-key-path

Path name to file 5.7.23 containing RSA
public key

-shared-memory-base-name

Shared-memory name for shared-memory connections (Windows only)

-show-table-type

Show a column indicating the table type

-socket

Unix socket file or Windows named pipe to use

-ssl

Enable connection encryption

-ssl-ca

File that contains list of trusted SSL Certificate Authorities

-ssl-capath

Directory that contains trusted SSL Certificate Authority certificate files

-ssl-cert

File that contains X.509 certificate

-ssl-cipher

Permissible ciphers for connection encryption

-ssl-crl

File that contains certificate revocation lists

-ssl-crlpath

Directory that contains certificate revocation-list files

-ssl-key

File that contains X.509 key

-ssl-mode

Desired security 5.7.11 state of
connection to server

-ssl-verify-server-cert

Verify host name against server certificate Common Name identity

-status

Display extra information about each table

-tls-version

Permissible TLS 5.7.10 protocols for
encrypted connections

-user

MySQL user name to use when connecting to server

-verbose

Verbose mode

-version

Display version information and exit

 File: manual.info.tmp, Node: mysqlslap, Prev: mysqlshow, Up: programs-client

4.5.8 mysqlslap -- A Load Emulation Client

*note 'mysqlslap': mysqlslap. is a diagnostic program designed to emulate client load for a MySQL server and to report the timing of each stage. It works as if multiple clients are accessing the server.

Invoke *note 'mysqlslap': mysqlslap. like this:

 mysqlslap [OPTIONS]

Some options such as '--create' or '--query' enable you to specify a string containing an SQL statement or a file containing statements. If you specify a file, by default it must contain one statement per line. (That is, the implicit statement delimiter is the newline character.) Use the '--delimiter' option to specify a different delimiter, which enables you to specify statements that span multiple lines or place multiple statements on a single line. You cannot include comments in a file; *note 'mysqlslap': mysqlslap. does not understand them.

*note 'mysqlslap': mysqlslap. runs in three stages:

  1. Create schema, table, and optionally any stored programs or data to use for the test. This stage uses a single client connection.

  2. Run the load test. This stage can use many client connections.

  3. Clean up (disconnect, drop table if specified). This stage uses a single client connection.

Examples:

Supply your own create and query SQL statements, with 50 clients querying and 200 selects for each (enter the command on a single line):

 mysqlslap --delimiter=";"
   --create="CREATE TABLE a (b int);INSERT INTO a VALUES (23)"
   --query="SELECT * FROM a" --concurrency=50 --iterations=200

Let note 'mysqlslap': mysqlslap. build the query SQL statement with a table of two note 'INT': integer-types. columns and three *note 'VARCHAR': char. columns. Use five clients querying 20 times each. Do not create the table or insert the data (that is, use the previous test's schema and data):

 mysqlslap --concurrency=5 --iterations=20
   --number-int-cols=2 --number-char-cols=3
   --auto-generate-sql

Tell the program to load the create, insert, and query SQL statements from the specified files, where the 'create.sql' file has multiple table creation statements delimited by '';'' and multiple insert statements delimited by '';''. The '--query' file has multiple queries delimited by '';''. Run all the load statements, then run all the queries in the query file with five clients (five times each):

 mysqlslap --concurrency=5
   --iterations=5 --query=query.sql --create=create.sql
   --delimiter=";"

note 'mysqlslap': mysqlslap. supports the following options, which can be specified on the command line or in the '[mysqlslap]' and '[client]' groups of an option file. For information about option files used by MySQL programs, see note option-files::.

mysqlslap Options

Option Name Description IntroducedDeprecated

-auto-generate-sql

Generate SQL statements automatically when they are not supplied in files or using command options

-auto-generate-sql-add-autoincrement

Add AUTO_INCREMENT column to automatically generated tables

-auto-generate-sql-execute-number

Specify how many queries to generate automatically

-auto-generate-sql-guid-primary

Add a GUID-based primary key to automatically generated tables

-auto-generate-sql-load-type

Specify the test load type

-auto-generate-sql-secondary-indexes

Specify how many secondary indexes to add to automatically generated tables

-auto-generate-sql-unique-query-number

How many different queries to generate for automatic tests

-auto-generate-sql-unique-write-number

How many different queries to generate for -auto-generate-sql-write-number

-auto-generate-sql-write-number

How many row inserts to perform on each thread

-commit

How many statements to execute before committing

-compress

Compress all information sent between client and server

-concurrency

Number of clients to simulate when issuing the SELECT statement

-create

File or string containing the statement to use for creating the table

-create-schema

Schema in which to run the tests

-csv

Generate output in comma-separated values format

-debug

Write debugging log

-debug-check

Print debugging information when program exits

-debug-info

Print debugging information, memory, and CPU statistics when program exits

-default-auth

Authentication plugin to use

-defaults-extra-file

Read named option file in addition to usual option files

-defaults-file

Read only named option file

-defaults-group-suffix

Option group suffix value

-delimiter

Delimiter to use in SQL statements

-detach

Detach (close and reopen) each connection after each N statements

-enable-cleartext-plugin

Enable cleartext authentication plugin

-engine

Storage engine to use for creating the table

-get-server-public-key

Request RSA 5.7.23 public key from
server

-help

Display help message and exit

-host

Host on which MySQL server is located

-iterations

Number of times to run the tests

-login-path

Read login path options from .mylogin.cnf

-no-defaults

Read no option files

-no-drop

Do not drop any schema created during the test run

-number-char-cols

Number of VARCHAR columns to use if -auto-generate-sql is specified

-number-int-cols

Number of INT columns to use if -auto-generate-sql is specified

-number-of-queries

Limit each client to approximately this number of queries

-only-print

Do not connect to databases. mysqlslap only prints what it would have done

-password

Password to use when connecting to server

-pipe

Connect to server using named pipe (Windows only)

-plugin-dir

Directory where plugins are installed

-port

TCP/IP port number for connection

-post-query

File or string containing the statement to execute after the tests have completed

-post-system

String to execute using system() after the tests have completed

-pre-query

File or string containing the statement to execute before running the tests

-pre-system

String to execute using system() before running the tests

-print-defaults

Print default options

-protocol

Transport protocol to use

-query

File or string containing the SELECT statement to use for retrieving data

-secure-auth

Do not send Yes passwords to
server in old (pre-4.1) format

-server-public-key-path

Path name to file 5.7.23 containing RSA
public key

-shared-memory-base-name

Shared-memory name for shared-memory connections (Windows only)

-silent

Silent mode

-socket

Unix socket file or Windows named pipe to use

-sql-mode

Set SQL mode for client session

-ssl

Enable connection encryption

-ssl-ca

File that contains list of trusted SSL Certificate Authorities

-ssl-capath

Directory that contains trusted SSL Certificate Authority certificate files

-ssl-cert

File that contains X.509 certificate

-ssl-cipher

Permissible ciphers for connection encryption

-ssl-crl

File that contains certificate revocation lists

-ssl-crlpath

Directory that contains certificate revocation-list files

-ssl-key

File that contains X.509 key

-ssl-mode

Desired security 5.7.11 state of
connection to server

-ssl-verify-server-cert

Verify host name against server certificate Common Name identity

-tls-version

Permissible TLS 5.7.10 protocols for
encrypted connections

-user

MySQL user name to use when connecting to server

-verbose

Verbose mode

-version

Display version information and exit

 File: manual.info.tmp, Node: programs-admin-utils, Next: programs-development, Prev: programs-client, Up: programs