1MYSQL(1) MySQL Database System MYSQL(1)
2
6 mysql - the MySQL command-line client
7
9 mysql [options] db_name
10
12 mysql is a simple SQL shell with input line editing capabilities. It
13 supports interactive and noninteractive use. When used interactively,
14 query results are presented in an ASCII-table format. When used
15 noninteractively (for example, as a filter), the result is presented in
16 tab-separated format. The output format can be changed using command
17 options.
18 If you have problems due to insufficient memory for large result sets,
20 use the --quick option. This forces mysql to retrieve results from the
21 server a row at a time rather than retrieving the entire result set and
22 buffering it in memory before displaying it. This is done by returning
23 the result set using the mysql_use_result() C API function in the
24 client/server library rather than mysql_store_result().
25 Note
27 Alternatively, MySQL Shell offers access to the X DevAPI. For
28 details, see MySQL Shell 8.0 (part of MySQL 8.0)[1].
29 Using mysql is very easy. Invoke it from the prompt of your command
31 interpreter as follows:
32 shell> mysql db_name
34 Or:
36 shell> mysql --user=user_name --password db_name
38 Enter password: your_password
39 Then type an SQL statement, end it with ;, \g, or \G and press Enter.
41 Typing Control+C interrupts the current statement if there is one, or
43 cancels any partial input line otherwise.
44 You can execute SQL statements in a script file (batch file) like this:
46 shell> mysql db_name < script.sql > output.tab
48 On Unix, the mysql client logs statements executed interactively to a
50 history file. See the section called “MYSQL CLIENT LOGGING”.
51
53 mysql supports the following options, which can be specified on the
54 command line or in the [mysql] and [client] groups of an option file.
55 For information about option files used by MySQL programs, see
56 Section 4.2.2.2, “Using Option Files”.
57 · --help, -?
59 Display a help message and exit.
61 · --auto-rehash
63 Enable automatic rehashing. This option is on by default, which
65 enables database, table, and column name completion. Use
66 --disable-auto-rehash to disable rehashing. That causes mysql to
67 start faster, but you must issue the rehash command or its \#
68 shortcut if you want to use name completion.
69 To complete a name, enter the first part and press Tab. If the name
71 is unambiguous, mysql completes it. Otherwise, you can press Tab
72 again to see the possible names that begin with what you have typed
73 so far. Completion does not occur if there is no default database.
74 Note
76 This feature requires a MySQL client that is compiled with the
77 readline library. Typically, the readline library is not
78 available on Windows.
79 · --auto-vertical-output
81 Cause result sets to be displayed vertically if they are too wide
83 for the current window, and using normal tabular format otherwise.
84 (This applies to statements terminated by ; or \G.)
85 · --batch, -B
87 Print results using tab as the column separator, with each row on a
89 new line. With this option, mysql does not use the history file.
90 Batch mode results in nontabular output format and escaping of
92 special characters. Escaping may be disabled by using raw mode; see
93 the description for the --raw option.
94 · --binary-as-hex
96 When this option is given, mysql displays binary data using
98 hexadecimal notation (0xvalue). This occurs whether the overall
99 output dislay format is tabular, vertical, HTML, or XML.
100 As of MySQL 8.0.19, when mysql operates in interactive mode, this
102 option is enabled by default. In addition, output from the status
103 (or \s) command includes this line when the option is enabled
104 implicitly or explicitly:
105 Binary data as: Hexadecimal
107 To disable hexadecimal notation, use --skip-binary-as-hex
109 · --binary-mode
111 This option helps when processing mysqlbinlog output that may
113 contain BLOB values. By default, mysql translates \r\n in statement
114 strings to \n and interprets \0 as the statement terminator.
115 --binary-mode disables both features. It also disables all mysql
116 commands except charset and delimiter in noninteractive mode (for
117 input piped to mysql or loaded using the source command).
118 · --bind-address=ip_address
120 On a computer having multiple network interfaces, use this option
122 to select which interface to use for connecting to the MySQL
123 server.
124 · --character-sets-dir=dir_name
126 The directory where character sets are installed. See
128 Section 10.15, “Character Set Configuration”.
129 · --column-names
131 Write column names in results.
133 · --column-type-info
135 Display result set metadata.
137 · --comments, -c
139 Whether to strip or preserve comments in statements sent to the
141 server. The default is --skip-comments (strip comments), enable
142 with --comments (preserve comments).
143 Note
145 The mysql client always passes optimizer hints to the server,
146 regardless of whether this option is given.
147 Comment stripping is deprecated. This feature and the options
149 to control it will be removed in a future MySQL release.
150 · --compress, -C
152 Compress all information sent between the client and the server if
154 possible. See Section 4.2.6, “Connection Compression Control”.
155 As of MySQL 8.0.18, this option is deprecated. It will be removed
157 in a future MySQL version. See the section called “Legacy
158 Connection Compression Configuration”.
159 · --compression-algorithms=value The permitted compression algorithms
161 for connections to the server. The available algorithms are the
162 same as for the protocol_compression_algorithms system variable.
163 The default value is uncompressed.
164 For more information, see Section 4.2.6, “Connection Compression
166 Control”.
167 This option was added in MySQL 8.0.18.
169 · --connect-expired-password
171 Indicate to the server that the client can handle sandbox mode if
173 the account used to connect has an expired password. This can be
174 useful for noninteractive invocations of mysql because normally the
175 server disconnects noninteractive clients that attempt to connect
176 using an account with an expired password. (See Section 6.2.16,
177 “Server Handling of Expired Passwords”.)
178 · --connect-timeout=value The number of seconds before connection
180 timeout. (Default value is 0.)
181 · --database=db_name, -D db_name
183 The database to use. This is useful primarily in an option file.
185 · --debug[=debug_options], -# [debug_options]
187 Write a debugging log. A typical debug_options string is
189 d:t:o,file_name. The default is d:t:o,/tmp/mysql.trace.
190 This option is available only if MySQL was built using WITH_DEBUG.
192 MySQL release binaries provided by Oracle are not built using this
193 option.
194 · --debug-check
196 Print some debugging information when the program exits.
198 This option is available only if MySQL was built using WITH_DEBUG.
200 MySQL release binaries provided by Oracle are not built using this
201 option.
202 · --debug-info, -T
204 Print debugging information and memory and CPU usage statistics
206 when the program exits.
207 This option is available only if MySQL was built using WITH_DEBUG.
209 MySQL release binaries provided by Oracle are not built using this
210 option.
211 · --default-auth=plugin
213 A hint about which client-side authentication plugin to use. See
215 Section 6.2.17, “Pluggable Authentication”.
216 · --default-character-set=charset_name
218 Use charset_name as the default character set for the client and
220 connection.
221 This option can be useful if the operating system uses one
223 character set and the mysql client by default uses another. In this
224 case, output may be formatted incorrectly. You can usually fix such
225 issues by using this option to force the client to use the system
226 character set instead.
227 For more information, see Section 10.4, “Connection Character Sets
229 and Collations”, and Section 10.15, “Character Set Configuration”.
230 · --defaults-extra-file=file_name
232 Read this option file after the global option file but (on Unix)
234 before the user option file. If the file does not exist or is
235 otherwise inaccessible, an error occurs. file_name is interpreted
236 relative to the current directory if given as a relative path name
237 rather than a full path name.
238 For additional information about this and other option-file
240 options, see Section 4.2.2.3, “Command-Line Options that Affect
241 Option-File Handling”.
242 · --defaults-file=file_name
244 Use only the given option file. If the file does not exist or is
246 otherwise inaccessible, an error occurs. file_name is interpreted
247 relative to the current directory if given as a relative path name
248 rather than a full path name.
249 Exception: Even with --defaults-file, client programs read
251 .mylogin.cnf.
252 For additional information about this and other option-file
254 options, see Section 4.2.2.3, “Command-Line Options that Affect
255 Option-File Handling”.
256 · --defaults-group-suffix=str
258 Read not only the usual option groups, but also groups with the
260 usual names and a suffix of str. For example, mysql normally reads
261 the [client] and [mysql] groups. If the
262 --defaults-group-suffix=_other option is given, mysql also reads
263 the [client_other] and [mysql_other] groups.
264 For additional information about this and other option-file
266 options, see Section 4.2.2.3, “Command-Line Options that Affect
267 Option-File Handling”.
268 · --delimiter=str
270 Set the statement delimiter. The default is the semicolon character
272 (;).
273 · --disable-named-commands
275 Disable named commands. Use the \* form only, or use named commands
277 only at the beginning of a line ending with a semicolon (;). mysql
278 starts with this option enabled by default. However, even with this
279 option, long-format commands still work from the first line. See
280 the section called “MYSQL CLIENT COMMANDS”.
281 · --enable-cleartext-plugin
283 Enable the mysql_clear_password cleartext authentication plugin.
285 (See Section 6.4.1.4, “Client-Side Cleartext Pluggable
286 Authentication”.)
287 · --execute=statement, -e statement
289 Execute the statement and quit. The default output format is like
291 that produced with --batch. See Section 4.2.2.1, “Using Options on
292 the Command Line”, for some examples. With this option, mysql does
293 not use the history file.
294 · --force, -f
296 Continue even if an SQL error occurs.
298 · --get-server-public-key
300 Request from the server the public key required for RSA key
302 pair-based password exchange. This option applies to clients that
303 authenticate with the caching_sha2_password authentication plugin.
304 For that plugin, the server does not send the public key unless
305 requested. This option is ignored for accounts that do not
306 authenticate with that plugin. It is also ignored if RSA-based
307 password exchange is not used, as is the case when the client
308 connects to the server using a secure connection.
309 If --server-public-key-path=file_name is given and specifies a
311 valid public key file, it takes precedence over
312 --get-server-public-key.
313 For information about the caching_sha2_password plugin, see
315 Section 6.4.1.2, “Caching SHA-2 Pluggable Authentication”.
316 · --histignore
318 A list of one or more colon-separated patterns specifying
320 statements to ignore for logging purposes. These patterns are added
321 to the default pattern list ("*IDENTIFIED*:*PASSWORD*"). The value
322 specified for this option affects logging of statements written to
323 the history file, and to syslog if the --syslog option is given.
324 For more information, see the section called “MYSQL CLIENT
325 LOGGING”.
326 · --host=host_name, -h host_name
328 Connect to the MySQL server on the given host.
330 · --html, -H
332 Produce HTML output.
334 · --ignore-spaces, -i
336 Ignore spaces after function names. The effect of this is described
338 in the discussion for the IGNORE_SPACE SQL mode (see
339 Section 5.1.11, “Server SQL Modes”).
340 · --init-command=str
342 SQL statement to execute after connecting to the server. If
344 auto-reconnect is enabled, the statement is executed again after
345 reconnection occurs.
346 · --line-numbers
348 Write line numbers for errors. Disable this with
350 --skip-line-numbers.
351 · --local-infile[={0|1}]
353 By default, LOCAL capability for LOAD DATA is determined by the
355 default compiled into the MySQL client library. To enable or
356 disable LOCAL explicitly, use the --local-infile option. When given
357 with no value, the option enables LOCAL. When given as
358 --local-infile=0 or --local-infile=1, the option explicitly
359 disables or enables LOCAL.
360 Successful use of LOCAL load operations within mysql also requires
362 that the server permits local loading; see Section 6.1.6, “Security
363 Issues with LOAD DATA LOCAL”
364 · --login-path=name
366 Read options from the named login path in the .mylogin.cnf login
368 path file. A “login path” is an option group containing options
369 that specify which MySQL server to connect to and which account to
370 authenticate as. To create or modify a login path file, use the
371 mysql_config_editor utility. See mysql_config_editor(1).
372 For additional information about this and other option-file
374 options, see Section 4.2.2.3, “Command-Line Options that Affect
375 Option-File Handling”.
376 · --max-allowed-packet=value The maximum size of the buffer for
378 client/server communication. The default is 16MB, the maximum is
379 1GB.
380 · --max-join-size=value The automatic limit for rows in a join when
382 using --safe-updates. (Default value is 1,000,000.)
383 · --named-commands, -G
385 Enable named mysql commands. Long-format commands are permitted,
387 not just short-format commands. For example, quit and \q both are
388 recognized. Use --skip-named-commands to disable named commands.
389 See the section called “MYSQL CLIENT COMMANDS”.
390 · --net-buffer-length=value The buffer size for TCP/IP and socket
392 communication. (Default value is 16KB.)
393 · --no-auto-rehash, -A
395 This has the same effect as --skip-auto-rehash. See the description
397 for --auto-rehash.
398 · --no-beep, -b
400 Do not beep when errors occur.
402 · --no-defaults
404 Do not read any option files. If program startup fails due to
406 reading unknown options from an option file, --no-defaults can be
407 used to prevent them from being read.
408 The exception is that the .mylogin.cnf file, if it exists, is read
410 in all cases. This permits passwords to be specified in a safer way
411 than on the command line even when --no-defaults is used.
412 (.mylogin.cnf is created by the mysql_config_editor utility. See
413 mysql_config_editor(1).)
414 For additional information about this and other option-file
416 options, see Section 4.2.2.3, “Command-Line Options that Affect
417 Option-File Handling”.
418 · --one-database, -o
420 Ignore statements except those that occur while the default
422 database is the one named on the command line. This option is
423 rudimentary and should be used with care. Statement filtering is
424 based only on USE statements.
425 Initially, mysql executes statements in the input because
427 specifying a database db_name on the command line is equivalent to
428 inserting USE db_name at the beginning of the input. Then, for each
429 USE statement encountered, mysql accepts or rejects following
430 statements depending on whether the database named is the one on
431 the command line. The content of the statements is immaterial.
432 Suppose that mysql is invoked to process this set of statements:
434 DELETE FROM db2.t2;
436 USE db2;
437 DROP TABLE db1.t1;
438 CREATE TABLE db1.t1 (i INT);
439 USE db1;
440 INSERT INTO t1 (i) VALUES(1);
441 CREATE TABLE db2.t1 (j INT);
442 If the command line is mysql --force --one-database db1, mysql
444 handles the input as follows:
445 · The DELETE statement is executed because the default database
447 is db1, even though the statement names a table in a different
448 database.
449 · The DROP TABLE and CREATE TABLE statements are not executed
451 because the default database is not db1, even though the
452 statements name a table in db1.
453 · The INSERT and CREATE TABLE statements are executed because the
455 default database is db1, even though the CREATE TABLE statement
456 names a table in a different database.
457 · --pager[=command]
459 Use the given command for paging query output. If the command is
461 omitted, the default pager is the value of your PAGER environment
462 variable. Valid pagers are less, more, cat [> filename], and so
463 forth. This option works only on Unix and only in interactive mode.
464 To disable paging, use --skip-pager. the section called “MYSQL
465 CLIENT COMMANDS”, discusses output paging further.
466 · --password[=password], -p[password]
468 The password of the MySQL account used for connecting to the
470 server. The password value is optional. If not given, mysql prompts
471 for one. If given, there must be no space between --password= or -p
472 and the password following it. If no password option is specified,
473 the default is to send no password.
474 Specifying a password on the command line should be considered
476 insecure. To avoid giving the password on the command line, use an
477 option file. See Section 6.1.2.1, “End-User Guidelines for Password
478 Security”.
479 To explicitly specify that there is no password and that mysql
481 should not prompt for one, use the --skip-password option.
482 · --pipe, -W
484 On Windows, connect to the server using a named pipe. This option
486 applies only if the server was started with the named_pipe system
487 variable enabled to support named-pipe connections. In addition,
488 the user making the connection must be a member of the Windows
489 group specified by the named_pipe_full_access_group system
490 variable.
491 · --plugin-dir=dir_name
493 The directory in which to look for plugins. Specify this option if
495 the --default-auth option is used to specify an authentication
496 plugin but mysql does not find it. See Section 6.2.17, “Pluggable
497 Authentication”.
498 · --port=port_num, -P port_num
500 For TCP/IP connections, the port number to use.
502 · --print-defaults
504 Print the program name and all options that it gets from option
506 files.
507 For additional information about this and other option-file
509 options, see Section 4.2.2.3, “Command-Line Options that Affect
510 Option-File Handling”.
511 · --prompt=format_str
513 Set the prompt to the specified format. The default is mysql>. The
515 special sequences that the prompt can contain are described in the
516 section called “MYSQL CLIENT COMMANDS”.
517 · --protocol={TCP|SOCKET|PIPE|MEMORY}
519 The connection protocol to use for connecting to the server. It is
521 useful when the other connection parameters normally result in use
522 of a protocol other than the one you want. For details on the
523 permissible values, see Section 4.2.4, “Connecting to the MySQL
524 Server Using Command Options”.
525 · --quick, -q
527 Do not cache each query result, print each row as it is received.
529 This may slow down the server if the output is suspended. With this
530 option, mysql does not use the history file.
531 · --raw, -r
533 For tabular output, the “boxing” around columns enables one column
535 value to be distinguished from another. For nontabular output (such
536 as is produced in batch mode or when the --batch or --silent option
537 is given), special characters are escaped in the output so they can
538 be identified easily. Newline, tab, NUL, and backslash are written
539 as \n, \t, \0, and \\. The --raw option disables this character
540 escaping.
541 The following example demonstrates tabular versus nontabular output
543 and the use of raw mode to disable escaping:
544 % mysql
546 mysql> SELECT CHAR(92);
547 +----------+
548 | CHAR(92) |
549 +----------+
550 | \ |
551 +----------+
552 % mysql -s
553 mysql> SELECT CHAR(92);
554 CHAR(92)
555 \\
556 % mysql -s -r
557 mysql> SELECT CHAR(92);
558 CHAR(92)
559 \
560 · --reconnect
562 If the connection to the server is lost, automatically try to
564 reconnect. A single reconnect attempt is made each time the
565 connection is lost. To suppress reconnection behavior, use
566 --skip-reconnect.
567 · --safe-updates, --i-am-a-dummy, -U
569 If this option is enabled, UPDATE and DELETE statements that do not
571 use a key in the WHERE clause or a LIMIT clause produce an error.
572 In addition, restrictions are placed on SELECT statements that
573 produce (or are estimated to produce) very large result sets. If
574 you have set this option in an option file, you can use
575 --skip-safe-updates on the command line to override it. For more
576 information about this option, see Using Safe-Updates Mode (--safe-
577 updates).
578 · --select-limit=value The automatic limit for SELECT statements when
580 using --safe-updates. (Default value is 1,000.)
581 · --server-public-key-path=file_name
583 The path name to a file containing a client-side copy of the public
585 key required by the server for RSA key pair-based password
586 exchange. The file must be in PEM format. This option applies to
587 clients that authenticate with the sha256_password or
588 caching_sha2_password authentication plugin. This option is ignored
589 for accounts that do not authenticate with one of those plugins. It
590 is also ignored if RSA-based password exchange is not used, as is
591 the case when the client connects to the server using a secure
592 connection.
593 If --server-public-key-path=file_name is given and specifies a
595 valid public key file, it takes precedence over
596 --get-server-public-key.
597 This option is available only if MySQL was built using OpenSSL.
599 For information about the sha256_password and caching_sha2_password
601 plugins, see Section 6.4.1.3, “SHA-256 Pluggable Authentication”,
602 and Section 6.4.1.2, “Caching SHA-2 Pluggable Authentication”.
603 · --shared-memory-base-name=name
605 On Windows, the shared-memory name to use for connections made
607 using shared memory to a local server. The default value is MYSQL.
608 The shared-memory name is case-sensitive.
609 This option applies only if the server was started with the
611 shared_memory system variable enabled to support shared-memory
612 connections.
613 · --show-warnings
615 Cause warnings to be shown after each statement if there are any.
617 This option applies to interactive and batch mode.
618 · --sigint-ignore
620 Ignore SIGINT signals (typically the result of typing Control+C).
622 Without this option, typing Control+C interrupts the current
624 statement if there is one, or cancels any partial input line
625 otherwise.
626 · --silent, -s
628 Silent mode. Produce less output. This option can be given multiple
630 times to produce less and less output.
631 This option results in nontabular output format and escaping of
633 special characters. Escaping may be disabled by using raw mode; see
634 the description for the --raw option.
635 · --skip-column-names, -N
637 Do not write column names in results.
639 · --skip-line-numbers, -L
641 Do not write line numbers for errors. Useful when you want to
643 compare result files that include error messages.
644 · --socket=path, -S path
646 For connections to localhost, the Unix socket file to use, or, on
648 Windows, the name of the named pipe to use.
649 On Windows, this option applies only if the server was started with
651 the named_pipe system variable enabled to support named-pipe
652 connections. In addition, the user making the connection must be a
653 member of the Windows group specified by the
654 named_pipe_full_access_group system variable.
655 · --ssl*
657 Options that begin with --ssl specify whether to connect to the
659 server using SSL and indicate where to find SSL keys and
660 certificates. See the section called “Command Options for Encrypted
661 Connections”.
662 · --ssl-fips-mode={OFF|ON|STRICT} Controls whether to enable FIPS
664 mode on the client side. The --ssl-fips-mode option differs from
665 other --ssl-xxx options in that it is not used to establish
666 encrypted connections, but rather to affect which cryptographic
667 operations are permitted. See Section 6.5, “FIPS Support”.
668 These --ssl-fips-mode values are permitted:
670 · OFF: Disable FIPS mode.
672 · ON: Enable FIPS mode.
674 · STRICT: Enable “strict” FIPS mode.
676 Note
679 If the OpenSSL FIPS Object Module is not available, the only
680 permitted value for --ssl-fips-mode is OFF. In this case,
681 setting --ssl-fips-mode to ON or STRICT causes the client to
682 produce a warning at startup and to operate in non-FIPS mode.
683 · --syslog, -j
685 This option causes mysql to send interactive statements to the
687 system logging facility. On Unix, this is syslog; on Windows, it is
688 the Windows Event Log. The destination where logged messages appear
689 is system dependent. On Linux, the destination is often the
690 /var/log/messages file.
691 Here is a sample of output generated on Linux by using --syslog.
693 This output is formatted for readability; each logged message
694 actually takes a single line.
695 Mar 7 12:39:25 myhost MysqlClient[20824]:
697 SYSTEM_USER:'oscar', MYSQL_USER:'my_oscar', CONNECTION_ID:23,
698 DB_SERVER:'127.0.0.1', DB:'--', QUERY:'USE test;'
699 Mar 7 12:39:28 myhost MysqlClient[20824]:
700 SYSTEM_USER:'oscar', MYSQL_USER:'my_oscar', CONNECTION_ID:23,
701 DB_SERVER:'127.0.0.1', DB:'test', QUERY:'SHOW TABLES;'
702 For more information, see the section called “MYSQL CLIENT
704 LOGGING”.
705 · --table, -t
707 Display output in table format. This is the default for interactive
709 use, but can be used to produce table output in batch mode.
710 · --tee=file_name
712 Append a copy of output to the given file. This option works only
714 in interactive mode. the section called “MYSQL CLIENT COMMANDS”,
715 discusses tee files further.
716 · --tls-ciphersuites=ciphersuite_list
718 The permissible ciphersuites for encrypted connections that use
720 TLSv1.3. The value is a list of one or more colon-separated
721 ciphersuite names. The ciphersuites that can be named for this
722 option depend on the SSL library used to compile MySQL. For
723 details, see Section 6.3.2, “Encrypted Connection TLS Protocols and
724 Ciphers”.
725 This option was added in MySQL 8.0.16.
727 · --tls-version=protocol_list
729 The permissible TLS protocols for encrypted connections. The value
731 is a list of one or more comma-separated protocol names. The
732 protocols that can be named for this option depend on the SSL
733 library used to compile MySQL. For details, see Section 6.3.2,
734 “Encrypted Connection TLS Protocols and Ciphers”.
735 · --unbuffered, -n
737 Flush the buffer after each query.
739 · --user=user_name, -u user_name
741 The user name of the MySQL account to use for connecting to the
743 server.
744 · --verbose, -v
746 Verbose mode. Produce more output about what the program does. This
748 option can be given multiple times to produce more and more output.
749 (For example, -v -v -v produces table output format even in batch
750 mode.)
751 · --version, -V
753 Display version information and exit.
755 · --vertical, -E
757 Print query output rows vertically (one line per column value).
759 Without this option, you can specify vertical output for individual
760 statements by terminating them with \G.
761 · --wait, -w
763 If the connection cannot be established, wait and retry instead of
765 aborting.
766 · --xml, -X
768 Produce XML output.
770 <field name="column_name">NULL</field>
772 The output when --xml is used with mysql matches that of mysqldump
774 --xml. See mysqldump(1), for details.
775 The XML output also uses an XML namespace, as shown here:
777 shell> mysql --xml -uroot -e "SHOW VARIABLES LIKE 'version%'"
779 <?xml version="1.0"?>
780 <resultset statement="SHOW VARIABLES LIKE 'version%'" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
781 <row>
782 <field name="Variable_name">version</field>
783 <field name="Value">5.0.40-debug</field>
784 </row>
785 <row>
786 <field name="Variable_name">version_comment</field>
787 <field name="Value">Source distribution</field>
788 </row>
789 <row>
790 <field name="Variable_name">version_compile_machine</field>
791 <field name="Value">i686</field>
792 </row>
793 <row>
794 <field name="Variable_name">version_compile_os</field>
795 <field name="Value">suse-linux-gnu</field>
796 </row>
797 </resultset>
798 · --zstd-compression-level=level The compression level to use for
800 connections to the server that use the zstd compression algorithm.
801 The permitted levels are from 1 to 22, with larger values
802 indicating increasing levels of compression. The default zstd
803 compression level is 3. The compression level setting has no effect
804 on connections that do not use zstd compression.
805 For more information, see Section 4.2.6, “Connection Compression
807 Control”.
808 This option was added in MySQL 8.0.18.
810
812 mysql sends each SQL statement that you issue to the server to be
813 executed. There is also a set of commands that mysql itself interprets.
814 For a list of these commands, type help or \h at the mysql> prompt:
815 mysql> help
817 List of all MySQL commands:
818 Note that all text commands must be first on line and end with ';'
819 ? (\?) Synonym for `help'.
820 clear (\c) Clear the current input statement.
821 connect (\r) Reconnect to the server. Optional arguments are db and host.
822 delimiter (\d) Set statement delimiter.
823 edit (\e) Edit command with $EDITOR.
824 ego (\G) Send command to mysql server, display result vertically.
825 exit (\q) Exit mysql. Same as quit.
826 go (\g) Send command to mysql server.
827 help (\h) Display this help.
828 nopager (\n) Disable pager, print to stdout.
829 notee (\t) Don't write into outfile.
830 pager (\P) Set PAGER [to_pager]. Print the query results via PAGER.
831 print (\p) Print current command.
832 prompt (\R) Change your mysql prompt.
833 quit (\q) Quit mysql.
834 rehash (\#) Rebuild completion hash.
835 source (\.) Execute an SQL script file. Takes a file name as an argument.
836 status (\s) Get status information from the server.
837 system (\!) Execute a system shell command.
838 tee (\T) Set outfile [to_outfile]. Append everything into given
839 outfile.
840 use (\u) Use another database. Takes database name as argument.
841 charset (\C) Switch to another charset. Might be needed for processing
842 binlog with multi-byte charsets.
843 warnings (\W) Show warnings after every statement.
844 nowarning (\w) Don't show warnings after every statement.
845 resetconnection(\x) Clean session context.
846 For server side help, type 'help contents'
847 If mysql is invoked with the --binary-mode option, all mysql commands
849 are disabled except charset and delimiter in noninteractive mode (for
850 input piped to mysql or loaded using the source command).
851 Each command has both a long and short form. The long form is not
853 case-sensitive; the short form is. The long form can be followed by an
854 optional semicolon terminator, but the short form should not.
855 The use of short-form commands within multiple-line /* ... */ comments
857 is not supported. Short-form commands do work within single-line /*!
858 ... */ version comments, as do /*+ ... */ optimizer-hint comments,
859 which are stored in object definitions. If there is a concern that
860 optimizer-hint comments may be stored in object definitions so that
861 dump files when reloaded with mysql would result in execution of such
862 commands, either invoke mysql with the --binary-mode option or use a
863 reload client other than mysql.
864 · help [arg], \h [arg], \? [arg], ? [arg]
866 Display a help message listing the available mysql commands.
868 If you provide an argument to the help command, mysql uses it as a
870 search string to access server-side help from the contents of the
871 MySQL Reference Manual. For more information, see the section
872 called “MYSQL CLIENT SERVER-SIDE HELP”.
873 · charset charset_name, \C charset_name
875 Change the default character set and issue a SET NAMES statement.
877 This enables the character set to remain synchronized on the client
878 and server if mysql is run with auto-reconnect enabled (which is
879 not recommended), because the specified character set is used for
880 reconnects.
881 · clear, \c
883 Clear the current input. Use this if you change your mind about
885 executing the statement that you are entering.
886 · connect [db_name host_name]], \r [db_name host_name]]
888 Reconnect to the server. The optional database name and host name
890 arguments may be given to specify the default database or the host
891 where the server is running. If omitted, the current values are
892 used.
893 · delimiter str, \d str
895 Change the string that mysql interprets as the separator between
897 SQL statements. The default is the semicolon character (;).
898 The delimiter string can be specified as an unquoted or quoted
900 argument on the delimiter command line. Quoting can be done with
901 either single quote ('), double quote ("), or backtick (`)
902 characters. To include a quote within a quoted string, either quote
903 the string with a different quote character or escape the quote
904 with a backslash (\) character. Backslash should be avoided outside
905 of quoted strings because it is the escape character for MySQL. For
906 an unquoted argument, the delimiter is read up to the first space
907 or end of line. For a quoted argument, the delimiter is read up to
908 the matching quote on the line.
909 mysql interprets instances of the delimiter string as a statement
911 delimiter anywhere it occurs, except within quoted strings. Be
912 careful about defining a delimiter that might occur within other
913 words. For example, if you define the delimiter as X, you will be
914 unable to use the word INDEX in statements. mysql interprets this
915 as INDE followed by the delimiter X.
916 When the delimiter recognized by mysql is set to something other
918 than the default of ;, instances of that character are sent to the
919 server without interpretation. However, the server itself still
920 interprets ; as a statement delimiter and processes statements
921 accordingly. This behavior on the server side comes into play for
922 multiple-statement execution (see Section 28.7.22, “C API Multiple
923 Statement Execution Support”), and for parsing the body of stored
924 procedures and functions, triggers, and events (see Section 24.1,
925 “Defining Stored Programs”).
926 · edit, \e
928 Edit the current input statement. mysql checks the values of the
930 EDITOR and VISUAL environment variables to determine which editor
931 to use. The default editor is vi if neither variable is set.
932 The edit command works only in Unix.
934 · ego, \G
936 Send the current statement to the server to be executed and display
938 the result using vertical format.
939 · exit, \q
941 Exit mysql.
943 · go, \g
945 Send the current statement to the server to be executed.
947 · nopager, \n
949 Disable output paging. See the description for pager.
951 The nopager command works only in Unix.
953 · notee, \t
955 Disable output copying to the tee file. See the description for
957 tee.
958 · nowarning, \w
960 Disable display of warnings after each statement.
962 · pager [command], \P [command]
964 Enable output paging. By using the --pager option when you invoke
966 mysql, it is possible to browse or search query results in
967 interactive mode with Unix programs such as less, more, or any
968 other similar program. If you specify no value for the option,
969 mysql checks the value of the PAGER environment variable and sets
970 the pager to that. Pager functionality works only in interactive
971 mode.
972 Output paging can be enabled interactively with the pager command
974 and disabled with nopager. The command takes an optional argument;
975 if given, the paging program is set to that. With no argument, the
976 pager is set to the pager that was set on the command line, or
977 stdout if no pager was specified.
978 Output paging works only in Unix because it uses the popen()
980 function, which does not exist on Windows. For Windows, the tee
981 option can be used instead to save query output, although it is not
982 as convenient as pager for browsing output in some situations.
983 · print, \p
985 Print the current input statement without executing it.
987 · prompt [str], \R [str]
989 Reconfigure the mysql prompt to the given string. The special
991 character sequences that can be used in the prompt are described
992 later in this section.
993 If you specify the prompt command with no argument, mysql resets
995 the prompt to the default of mysql>.
996 · quit, \q
998 Exit mysql.
1000 · rehash, \#
1002 Rebuild the completion hash that enables database, table, and
1004 column name completion while you are entering statements. (See the
1005 description for the --auto-rehash option.)
1006 · resetconnection, \x
1008 Reset the connection to clear the session state.
1010 Resetting a connection has effects similar to mysql_change_user()
1012 or an auto-reconnect except that the connection is not closed and
1013 reopened, and re-authentication is not done. See Section 28.7.6.3,
1014 “mysql_change_user()”, and Section 28.7.27, “C API Automatic
1015 Reconnection Control”.
1016 This example shows how resetconnection clears a value maintained in
1018 the session state:
1019 mysql> SELECT LAST_INSERT_ID(3);
1021 +-------------------+
1022 | LAST_INSERT_ID(3) |
1023 +-------------------+
1024 | 3 |
1025 +-------------------+
1026 mysql> SELECT LAST_INSERT_ID();
1027 +------------------+
1028 | LAST_INSERT_ID() |
1029 +------------------+
1030 | 3 |
1031 +------------------+
1032 mysql> resetconnection;
1033 mysql> SELECT LAST_INSERT_ID();
1034 +------------------+
1035 | LAST_INSERT_ID() |
1036 +------------------+
1037 | 0 |
1038 +------------------+
1039 · source file_name, \. file_name
1041 Read the named file and executes the statements contained therein.
1043 On Windows, specify path name separators as / or \\.
1044 Quote characters are taken as part of the file name itself. For
1046 best results, the name should not include space characters.
1047 · status, \s
1049 Provide status information about the connection and the server you
1051 are using. If you are running with --safe-updates enabled, status
1052 also prints the values for the mysql variables that affect your
1053 queries.
1054 · system command, \! command
1056 Execute the given command using your default command interpreter.
1058 Prior to MySQL 8.0.19, the system command works only in Unix. As of
1060 8.0.19, it also works on Windows.
1061 · tee [file_name], \T [file_name]
1063 By using the --tee option when you invoke mysql, you can log
1065 statements and their output. All the data displayed on the screen
1066 is appended into a given file. This can be very useful for
1067 debugging purposes also. mysql flushes results to the file after
1068 each statement, just before it prints its next prompt. Tee
1069 functionality works only in interactive mode.
1070 You can enable this feature interactively with the tee command.
1072 Without a parameter, the previous file is used. The tee file can be
1073 disabled with the notee command. Executing tee again re-enables
1074 logging.
1075 · use db_name, \u db_name
1077 Use db_name as the default database.
1079 · warnings, \W
1081 Enable display of warnings after each statement (if there are any).
1083 Here are a few tips about the pager command:
1085 · You can use it to write to a file and the results go only to the
1087 file:
1088 mysql> pager cat > /tmp/log.txt
1090 You can also pass any options for the program that you want to use
1092 as your pager:
1093 mysql> pager less -n -i -S
1095 · In the preceding example, note the -S option. You may find it very
1097 useful for browsing wide query results. Sometimes a very wide
1098 result set is difficult to read on the screen. The -S option to
1099 less can make the result set much more readable because you can
1100 scroll it horizontally using the left-arrow and right-arrow keys.
1101 You can also use -S interactively within less to switch the
1102 horizontal-browse mode on and off. For more information, read the
1103 less manual page:
1104 shell> man less
1106 · The -F and -X options may be used with less to cause it to exit if
1108 output fits on one screen, which is convenient when no scrolling is
1109 necessary:
1110 mysql> pager less -n -i -S -F -X
1112 · You can specify very complex pager commands for handling query
1114 output:
1115 mysql> pager cat | tee /dr1/tmp/res.txt \
1117 | tee /dr2/tmp/res2.txt | less -n -i -S
1118 In this example, the command would send query results to two files
1120 in two different directories on two different file systems mounted
1121 on /dr1 and /dr2, yet still display the results onscreen using
1122 less.
1123 You can also combine the tee and pager functions. Have a tee file
1125 enabled and pager set to less, and you are able to browse the results
1126 using the less program and still have everything appended into a file
1127 the same time. The difference between the Unix tee used with the pager
1128 command and the mysql built-in tee command is that the built-in tee
1129 works even if you do not have the Unix tee available. The built-in tee
1130 also logs everything that is printed on the screen, whereas the Unix
1131 tee used with pager does not log quite that much. Additionally, tee
1132 file logging can be turned on and off interactively from within mysql.
1133 This is useful when you want to log some queries to a file, but not
1134 others.
1135 The prompt command reconfigures the default mysql> prompt. The string
1137 for defining the prompt can contain the following special sequences.
1138.br
1140.br
1141.br
114272
1143 ┌───────────────────────────┬────────────────────────────┐
1144 │Option │ Description │
1145 ├───────────────────────────┼────────────────────────────┤
1146 │ │ The current connection │
1147 │ │ identifier │
1148 ├───────────────────────────┼────────────────────────────┤
1149 │ │ A counter that increments │
1150 │ │ for each statement you │
1151 │ │ issue │
1152 ├───────────────────────────┼────────────────────────────┤
1153 │ │ The full current date │
1154 ├───────────────────────────┼────────────────────────────┤
1155 │ │ The default database │
1156 ├───────────────────────────┼────────────────────────────┤
1157 │ │ The server host │
1158 ├───────────────────────────┼────────────────────────────┤
1159 │ │ The current delimiter │
1160 ├───────────────────────────┼────────────────────────────┤
1161 │ │ Minutes of the current │
1162 │ │ time │
1163 ├───────────────────────────┼────────────────────────────┤
1164 │ │ A newline character │
1165 ├───────────────────────────┼────────────────────────────┤
1166 │ │ The current month in │
1167 │ │ three-letter format (Jan, │
1168 │ │ Feb, ...) │
1169 ├───────────────────────────┼────────────────────────────┤
1170 │ │ The current month in │
1171 │ │ numeric format │
1172 ├───────────────────────────┼────────────────────────────┤
1173 │P │ am/pm │
1174 ├───────────────────────────┼────────────────────────────┤
1175 │ │ The current TCP/IP port or │
1176 │ │ socket file │
1177 ├───────────────────────────┼────────────────────────────┤
1178 │ │ The current time, in │
1179 │ │ 24-hour military time │
1180 │ │ (0–23) │
1181 ├───────────────────────────┼────────────────────────────┤
1182 │ │ The current time, standard │
1183 │ │ 12-hour time (1–12) │
1184 ├───────────────────────────┼────────────────────────────┤
1185 │ │ Semicolon │
1186 ├───────────────────────────┼────────────────────────────┤
1187 │ │ Seconds of the current │
1188 │ │ time │
1189 ├───────────────────────────┼────────────────────────────┤
1190 │ │ A tab character │
1191 ├───────────────────────────┼────────────────────────────┤
1192 │U │ │
1193 │ │ Your full │
1194 │ │ user_name@host_name │
1195 │ │ account name │
1196 ├───────────────────────────┼────────────────────────────┤
1197 │ │ Your user name │
1198 ├───────────────────────────┼────────────────────────────┤
1199 │ │ The server version │
1200 ├───────────────────────────┼────────────────────────────┤
1201 │ │ The current day of the │
1202 │ │ week in three-letter │
1203 │ │ format (Mon, Tue, ...) │
1204 ├───────────────────────────┼────────────────────────────┤
1205 │ │ The current year, four │
1206 │ │ digits │
1207 ├───────────────────────────┼────────────────────────────┤
1208 │y │ The current year, two │
1209 │ │ digits │
1210 ├───────────────────────────┼────────────────────────────┤
1211 │_ │ A space │
1212 ├───────────────────────────┼────────────────────────────┤
1213 │\ │ A space (a space follows │
1214 │ │ the backslash) │
1215 ├───────────────────────────┼────────────────────────────┤
1216 │´ │ Single quote │
1217 ├───────────────────────────┼────────────────────────────┤
1218 │ │ Double quote │
1219 ├───────────────────────────┼────────────────────────────┤
1220 │T}:T{ A literal backslash │ │
1221 │character │ │
1222 ├───────────────────────────┼────────────────────────────┤
1223 │\fIx │ │
1224 │ │ x, for any “x” not │
1225 │ │ listed above │
1226 └───────────────────────────┴────────────────────────────┘
1227 You can set the prompt in several ways:
1229 · Use an environment variable. You can set the MYSQL_PS1 environment
1231 variable to a prompt string. For example:
1232 shell> export MYSQL_PS1="(\u@\h) [\d]> "
1234 · Use a command-line option. You can set the --prompt option on the
1236 command line to mysql. For example:
1237 shell> mysql --prompt="(\u@\h) [\d]> "
1239 (user@host) [database]>
1240 · Use an option file. You can set the prompt option in the [mysql]
1242 group of any MySQL option file, such as /etc/my.cnf or the .my.cnf
1243 file in your home directory. For example:
1244 [mysql]
1246 prompt=(\\u@\\h) [\\d]>\\_
1247 In this example, note that the backslashes are doubled. If you set
1249 the prompt using the prompt option in an option file, it is
1250 advisable to double the backslashes when using the special prompt
1251 options. There is some overlap in the set of permissible prompt
1252 options and the set of special escape sequences that are recognized
1253 in option files. (The rules for escape sequences in option files
1254 are listed in Section 4.2.2.2, “Using Option Files”.) The overlap
1255 may cause you problems if you use single backslashes. For example,
1256 \s is interpreted as a space rather than as the current seconds
1257 value. The following example shows how to define a prompt within an
1258 option file to include the current time in hh:mm:ss> format:
1259 [mysql]
1261 prompt="\\r:\\m:\\s> "
1262 · Set the prompt interactively. You can change your prompt
1264 interactively by using the prompt (or \R) command. For example:
1265 mysql> prompt (\u@\h) [\d]>\_
1267 PROMPT set to '(\u@\h) [\d]>\_'
1268 (user@host) [database]>
1269 (user@host) [database]> prompt
1270 Returning to default PROMPT of mysql>
1271 mysql>
1272
1274 The mysql client can do these types of logging for statements executed
1275 interactively:
1276 · On Unix, mysql writes the statements to a history file. By default,
1278 this file is named .mysql_history in your home directory. To
1279 specify a different file, set the value of the MYSQL_HISTFILE
1280 environment variable.
1281 · On all platforms, if the --syslog option is given, mysql writes the
1283 statements to the system logging facility. On Unix, this is syslog;
1284 on Windows, it is the Windows Event Log. The destination where
1285 logged messages appear is system dependent. On Linux, the
1286 destination is often the /var/log/messages file.
1287 The following discussion describes characteristics that apply to all
1289 logging types and provides information specific to each logging type.
1290 · How Logging Occurs
1292 · Controlling the History File
1294 · syslog Logging Characteristics
1296 How Logging Occurs
1297 For each enabled logging destination, statement logging occurs as
1299 follows:
1300 · Statements are logged only when executed interactively. Statements
1302 are noninteractive, for example, when read from a file or a pipe.
1303 It is also possible to suppress statement logging by using the
1304 --batch or --execute option.
1305 · Statements are ignored and not logged if they match any pattern in
1307 the “ignore” list. This list is described later.
1308 · mysql logs each nonignored, nonempty statement line individually.
1310 · If a nonignored statement spans multiple lines (not including the
1312 terminating delimiter), mysql concatenates the lines to form the
1313 complete statement, maps newlines to spaces, and logs the result,
1314 plus a delimiter.
1315 Consequently, an input statement that spans multiple lines can be
1317 logged twice. Consider this input:
1318 mysql> SELECT
1320 -> 'Today is'
1321 -> ,
1322 -> CURDATE()
1323 -> ;
1324 In this case, mysql logs the “SELECT”, “'Today is'”, “,”, “CURDATE()”,
1326 and “;” lines as it reads them. It also logs the complete statement,
1327 after mapping SELECT\n'Today is'\n,\nCURDATE() to SELECT 'Today is' ,
1328 CURDATE(), plus a delimiter. Thus, these lines appear in logged output:
1329 SELECT
1331 'Today is'
1332 ,
1333 CURDATE()
1334 ;
1335 SELECT 'Today is' , CURDATE();
1336 mysql ignores for logging purposes statements that match any pattern in
1338 the “ignore” list. By default, the pattern list is
1339 "*IDENTIFIED*:*PASSWORD*", to ignore statements that refer to
1340 passwords. Pattern matching is not case-sensitive. Within patterns, two
1341 characters are special:
1342 · ? matches any single character.
1344 · * matches any sequence of zero or more characters.
1346 To specify additional patterns, use the --histignore option or set the
1348 MYSQL_HISTIGNORE environment variable. (If both are specified, the
1349 option value takes precedence.) The value should be a list of one or
1350 more colon-separated patterns, which are appended to the default
1351 pattern list.
1352 Patterns specified on the command line might need to be quoted or
1354 escaped to prevent your command interpreter from treating them
1355 specially. For example, to suppress logging for UPDATE and DELETE
1356 statements in addition to statements that refer to passwords, invoke
1357 mysql like this:
1358 shell> mysql --histignore="*UPDATE*:*DELETE*"
1360 Controlling the History File
1362 The .mysql_history file should be protected with a restrictive access
1364 mode because sensitive information might be written to it, such as the
1365 text of SQL statements that contain passwords. See Section 6.1.2.1,
1366 “End-User Guidelines for Password Security”. Statements in the file are
1367 accessible from the mysql client when the up-arrow key is used to
1368 recall the history. See Disabling Interactive History.
1369 If you do not want to maintain a history file, first remove
1371 .mysql_history if it exists. Then use either of the following
1372 techniques to prevent it from being created again:
1373 · Set the MYSQL_HISTFILE environment variable to /dev/null. To cause
1375 this setting to take effect each time you log in, put it in one of
1376 your shell's startup files.
1377 · Create .mysql_history as a symbolic link to /dev/null; this need be
1379 done only once:
1380 shell> ln -s /dev/null $HOME/.mysql_history
1382 syslog Logging Characteristics
1383 If the --syslog option is given, mysql writes interactive statements to
1385 the system logging facility. Message logging has the following
1386 characteristics.
1387 Logging occurs at the “information” level. This corresponds to the
1389 LOG_INFO priority for syslog on Unix/Linux syslog capability and to
1390 EVENTLOG_INFORMATION_TYPE for the Windows Event Log. Consult your
1391 system documentation for configuration of your logging capability.
1392 Message size is limited to 1024 bytes.
1394 Messages consist of the identifier MysqlClient followed by these
1396 values:
1397 · SYSTEM_USER
1399 The operating system user name (login name) or -- if the user is
1401 unknown.
1402 · MYSQL_USER
1404 The MySQL user name (specified with the --user option) or -- if the
1406 user is unknown.
1407 · CONNECTION_ID:
1409 The client connection identifier. This is the same as the
1411 CONNECTION_ID() function value within the session.
1412 · DB_SERVER
1414 The server host or -- if the host is unknown.
1416 · DB
1418 The default database or -- if no database has been selected.
1420 · QUERY
1422 The text of the logged statement.
1424 Here is a sample of output generated on Linux by using --syslog. This
1426 output is formatted for readability; each logged message actually takes
1427 a single line.
1428 Mar 7 12:39:25 myhost MysqlClient[20824]:
1430 SYSTEM_USER:'oscar', MYSQL_USER:'my_oscar', CONNECTION_ID:23,
1431 DB_SERVER:'127.0.0.1', DB:'--', QUERY:'USE test;'
1432 Mar 7 12:39:28 myhost MysqlClient[20824]:
1433 SYSTEM_USER:'oscar', MYSQL_USER:'my_oscar', CONNECTION_ID:23,
1434 DB_SERVER:'127.0.0.1', DB:'test', QUERY:'SHOW TABLES;'
1435
1437 mysql> help search_string
1438 If you provide an argument to the help command, mysql uses it as a
1440 search string to access server-side help from the contents of the MySQL
1441 Reference Manual. The proper operation of this command requires that
1442 the help tables in the mysql database be initialized with help topic
1443 information (see Section 5.1.14, “Server-Side Help Support”).
1444 If there is no match for the search string, the search fails:
1446 mysql> help me
1448 Nothing found
1449 Please try to run 'help contents' for a list of all accessible topics
1450 Use help contents to see a list of the help categories:
1452 mysql> help contents
1454 You asked for help about help category: "Contents"
1455 For more information, type 'help <item>', where <item> is one of the
1456 following categories:
1457 Account Management
1458 Administration
1459 Data Definition
1460 Data Manipulation
1461 Data Types
1462 Functions
1463 Functions and Modifiers for Use with GROUP BY
1464 Geographic Features
1465 Language Structure
1466 Plugins
1467 Storage Engines
1468 Stored Routines
1469 Table Maintenance
1470 Transactions
1471 Triggers
1472 If the search string matches multiple items, mysql shows a list of
1474 matching topics:
1475 mysql> help logs
1477 Many help items for your request exist.
1478 To make a more specific request, please type 'help <item>',
1479 where <item> is one of the following topics:
1480 SHOW
1481 SHOW BINARY LOGS
1482 SHOW ENGINE
1483 SHOW LOGS
1484 Use a topic as the search string to see the help entry for that topic:
1486 mysql> help show binary logs
1488 Name: 'SHOW BINARY LOGS'
1489 Description:
1490 Syntax:
1491 SHOW BINARY LOGS
1492 SHOW MASTER LOGS
1493 Lists the binary log files on the server. This statement is used as
1494 part of the procedure described in [purge-binary-logs], that shows how
1495 to determine which logs can be purged.
1496 mysql> SHOW BINARY LOGS;
1497 +---------------+-----------+-----------+
1498 | Log_name | File_size | Encrypted |
1499 +---------------+-----------+-----------+
1500 | binlog.000015 | 724935 | Yes |
1501 | binlog.000016 | 733481 | Yes |
1502 +---------------+-----------+-----------+
1503 The search string can contain the wildcard characters % and _. These
1505 have the same meaning as for pattern-matching operations performed with
1506 the LIKE operator. For example, HELP rep% returns a list of topics that
1507 begin with rep:
1508 mysql> HELP rep%
1510 Many help items for your request exist.
1511 To make a more specific request, please type 'help <item>',
1512 where <item> is one of the following
1513 topics:
1514 REPAIR TABLE
1515 REPEAT FUNCTION
1516 REPEAT LOOP
1517 REPLACE
1518 REPLACE FUNCTION
1519
1521 The mysql client typically is used interactively, like this:
1522 shell> mysql db_name
1524 However, it is also possible to put your SQL statements in a file and
1526 then tell mysql to read its input from that file. To do so, create a
1527 text file text_file that contains the statements you wish to execute.
1528 Then invoke mysql as shown here:
1529 shell> mysql db_name < text_file
1531 If you place a USE db_name statement as the first statement in the
1533 file, it is unnecessary to specify the database name on the command
1534 line:
1535 shell> mysql < text_file
1537 If you are already running mysql, you can execute an SQL script file
1539 using the source command or \. command:
1540 mysql> source file_name
1542 mysql> \. file_name
1543 Sometimes you may want your script to display progress information to
1545 the user. For this you can insert statements like this:
1546 SELECT '<info_to_display>' AS ' ';
1548 The statement shown outputs <info_to_display>.
1550 You can also invoke mysql with the --verbose option, which causes each
1552 statement to be displayed before the result that it produces.
1553 mysql ignores Unicode byte order mark (BOM) characters at the beginning
1555 of input files. Previously, it read them and sent them to the server,
1556 resulting in a syntax error. Presence of a BOM does not cause mysql to
1557 change its default character set. To do that, invoke mysql with an
1558 option such as --default-character-set=utf8.
1559 For more information about batch mode, see Section 3.5, “Using mysql in
1561 Batch Mode”.
1562
1564 This section provides information about techniques for more effective
1565 use of mysql and about mysql operational behavior.
1566 · Input-Line Editing
1568 · Disabling Interactive History
1570 · Unicode Support on Windows
1572 · Displaying Query Results Vertically
1574 · Using Safe-Updates Mode (--safe-updates)
1576 · Disabling mysql Auto-Reconnect
1578 · mysql Client Parser Versus Server Parser
1580 Input-Line Editing
1581 mysql supports input-line editing, which enables you to modify the
1583 current input line in place or recall previous input lines. For
1584 example, the left-arrow and right-arrow keys move horizontally within
1585 the current input line, and the up-arrow and down-arrow keys move up
1586 and down through the set of previously entered lines. Backspace
1587 deletes the character before the cursor and typing new characters
1588 enters them at the cursor position. To enter the line, press Enter.
1589 On Windows, the editing key sequences are the same as supported for
1591 command editing in console windows. On Unix, the key sequences depend
1592 on the input library used to build mysql (for example, the libedit or
1593 readline library).
1594 Documentation for the libedit and readline libraries is available
1596 online. To change the set of key sequences permitted by a given input
1597 library, define key bindings in the library startup file. This is a
1598 file in your home directory: .editrc for libedit and .inputrc for
1599 readline.
1600 For example, in libedit, Control+W deletes everything before the
1602 current cursor position and Control+U deletes the entire line. In
1603 readline, Control+W deletes the word before the cursor and Control+U
1604 deletes everything before the current cursor position. If mysql was
1605 built using libedit, a user who prefers the readline behavior for these
1606 two keys can put the following lines in the .editrc file (creating the
1607 file if necessary):
1608 bind "^W" ed-delete-prev-word
1610 bind "^U" vi-kill-line-prev
1611 To see the current set of key bindings, temporarily put a line that
1613 says only bind at the end of .editrc. mysql will show the bindings
1614 when it starts. Disabling Interactive History
1615 The up-arrow key enables you to recall input lines from current and
1617 previous sessions. In cases where a console is shared, this behavior
1618 may be unsuitable. mysql supports disabling the interactive history
1619 partially or fully, depending on the host platform.
1620 On Windows, the history is stored in memory. Alt+F7 deletes all input
1622 lines stored in memory for the current history buffer. It also deletes
1623 the list of sequential numbers in front of the input lines displayed
1624 with F7 and recalled (by number) with F9. New input lines entered after
1625 you press Alt+F7 repopulate the current history buffer. Clearing the
1626 buffer does not prevent logging to the Windows Event Viewer, if the
1627 --syslog option was used to start mysql. Closing the console window
1628 also clears the current history buffer.
1629 To disable interactive history on Unix, first delete the .mysql_history
1631 file, if it exists (previous entries are recalled otherwise). Then
1632 start mysql with the --histignore="*" option to ignore all new input
1633 lines. To re-enable the recall (and logging) behavior, restart mysql
1634 without the option.
1635 If you prevent the .mysql_history file from being created (see
1637 Controlling the History File) and use --histignore="*" to start the
1638 mysql client, the interactive history recall facility is disabled
1639 fully. Alternatively, if you omit the --histignore option, you can
1640 recall the input lines entered during the current session. Unicode
1641 Support on Windows
1642 Windows provides APIs based on UTF-16LE for reading from and writing to
1644 the console; the mysql client for Windows is able to use these APIs.
1645 The Windows installer creates an item in the MySQL menu named MySQL
1646 command line client - Unicode. This item invokes the mysql client with
1647 properties set to communicate through the console to the MySQL server
1648 using Unicode.
1649 To take advantage of this support manually, run mysql within a console
1651 that uses a compatible Unicode font and set the default character set
1652 to a Unicode character set that is supported for communication with the
1653 server:
1654 1. Open a console window.
1656 2. Go to the console window properties, select the font tab, and
1658 choose Lucida Console or some other compatible Unicode font. This
1659 is necessary because console windows start by default using a DOS
1660 raster font that is inadequate for Unicode.
1661 3. Execute mysql.exe with the --default-character-set=utf8 (or
1663 utf8mb4) option. This option is necessary because utf16le is one of
1664 the character sets that cannot be used as the client character set.
1665 See the section called “Impermissible Client Character Sets”.
1666 With those changes, mysql will use the Windows APIs to communicate with
1668 the console using UTF-16LE, and communicate with the server using
1669 UTF-8. (The menu item mentioned previously sets the font and character
1670 set as just described.)
1671 To avoid those steps each time you run mysql, you can create a shortcut
1673 that invokes mysql.exe. The shortcut should set the console font to
1674 Lucida Console or some other compatible Unicode font, and pass the
1675 --default-character-set=utf8 (or utf8mb4) option to mysql.exe.
1676 Alternatively, create a shortcut that only sets the console font, and
1678 set the character set in the [mysql] group of your my.ini file:
1679 [mysql]
1681 default-character-set=utf8
1682 Displaying Query Results Vertically
1684 Some query results are much more readable when displayed vertically,
1686 instead of in the usual horizontal table format. Queries can be
1687 displayed vertically by terminating the query with \G instead of a
1688 semicolon. For example, longer text values that include newlines often
1689 are much easier to read with vertical output:
1690 mysql> SELECT * FROM mails WHERE LENGTH(txt) < 300 LIMIT 300,1\G
1692 *************************** 1. row ***************************
1693 msg_nro: 3068
1694 date: 2000-03-01 23:29:50
1695 time_zone: +0200
1696 mail_from: Jones
1697 reply: jones@example.com
1698 mail_to: "John Smith" <smith@example.com>
1699 sbj: UTF-8
1700 txt: >>>>> "John" == John Smith writes:
1701 John> Hi. I think this is a good idea. Is anyone familiar
1702 John> with UTF-8 or Unicode? Otherwise, I'll put this on my
1703 John> TODO list and see what happens.
1704 Yes, please do that.
1705 Regards,
1706 Jones
1707 file: inbox-jani-1
1708 hash: 190402944
1709 1 row in set (0.09 sec)
1710 Using Safe-Updates Mode (--safe-updates)
1712 For beginners, a useful startup option is --safe-updates (or
1714 --i-am-a-dummy, which has the same effect). Safe-updates mode is
1715 helpful for cases when you might have issued an UPDATE or DELETE
1716 statement but forgotten the WHERE clause indicating which rows to
1717 modify. Normally, such statements update or delete all rows in the
1718 table. With --safe-updates, you can modify rows only by specifying the
1719 key values that identify them, or a LIMIT clause, or both. This helps
1720 prevent accidents. Safe-updates mode also restricts SELECT statements
1721 that produce (or are estimated to produce) very large result sets.
1722 The --safe-updates option causes mysql to execute the following
1724 statement when it connects to the MySQL server, to set the session
1725 values of the sql_safe_updates, sql_select_limit, and max_join_size
1726 system variables:
1727 SET sql_safe_updates=1, sql_select_limit=1000, max_join_size=1000000;
1729 The SET statement affects statement processing as follows:
1731 · Enabling sql_safe_updates causes UPDATE and DELETE statements to
1733 produce an error if they do not specify a key constraint in the
1734 WHERE clause, or provide a LIMIT clause, or both. For example:
1735 UPDATE tbl_name SET not_key_column=val WHERE key_column=val;
1737 UPDATE tbl_name SET not_key_column=val LIMIT 1;
1738 · Setting sql_select_limit to 1,000 causes the server to limit all
1740 SELECT result sets to 1,000 rows unless the statement includes a
1741 LIMIT clause.
1742 · Setting max_join_size to 1,000,000 causes multiple-table SELECT
1744 statements to produce an error if the server estimates it must
1745 examine more than 1,000,000 row combinations.
1746 To specify result set limits different from 1,000 and 1,000,000, you
1748 can override the defaults by using the --select-limit and
1749 --max-join-size options when you invoke mysql:
1750 mysql --safe-updates --select-limit=500 --max-join-size=10000
1752 It is possible for UPDATE and DELETE statements to produce an error in
1754 safe-updates mode even with a key specified in the WHERE clause, if the
1755 optimizer decides not to use the index on the key column:
1756 · Range access on the index cannot be used if memory usage exceeds
1758 that permitted by the range_optimizer_max_mem_size system variable.
1759 The optimizer then falls back to a table scan. See the section
1760 called “Limiting Memory Use for Range Optimization”.
1761 · If key comparisons require type conversion, the index may not be
1763 used (see Section 8.3.1, “How MySQL Uses Indexes”). Suppose that an
1764 indexed string column c1 is compared to a numeric value using WHERE
1765 c1 = 2222. For such comparisons, the string value is converted to a
1766 number and the operands are compared numerically (see Section 12.2,
1767 “Type Conversion in Expression Evaluation”), preventing use of the
1768 index. If safe-updates mode is enabled, an error occurs.
1769 As of MySQL 8.0.13, safe-updates mode also includes these behaviors:
1771 · EXPLAIN with UPDATE and DELETE statements does not produce
1773 safe-updates errors. This enables use of EXPLAIN plus SHOW WARNINGS
1774 to see why an index is not used, which can be helpful in cases such
1775 as when a range_optimizer_max_mem_size violation or type conversion
1776 occurs and the optimizer does not use an index even though a key
1777 column was specified in the WHERE clause.
1778 · When a safe-updates error occurs, the error message includes the
1780 first diagnostic that was produced, to provide information about
1781 the reason for failure. For example, the message may indicate that
1782 the range_optimizer_max_mem_size value was exceeded or type
1783 conversion occurred, either of which can preclude use of an index.
1784 · For multiple-table deletes and updates, an error is produced with
1786 safe updates enabled only if any target table uses a table scan.
1787 Disabling mysql Auto-Reconnect
1788 If the mysql client loses its connection to the server while sending a
1790 statement, it immediately and automatically tries to reconnect once to
1791 the server and send the statement again. However, even if mysql
1792 succeeds in reconnecting, your first connection has ended and all your
1793 previous session objects and settings are lost: temporary tables, the
1794 autocommit mode, and user-defined and session variables. Also, any
1795 current transaction rolls back. This behavior may be dangerous for you,
1796 as in the following example where the server was shut down and
1797 restarted between the first and second statements without you knowing
1798 it:
1799 mysql> SET @a=1;
1801 Query OK, 0 rows affected (0.05 sec)
1802 mysql> INSERT INTO t VALUES(@a);
1803 ERROR 2006: MySQL server has gone away
1804 No connection. Trying to reconnect...
1805 Connection id: 1
1806 Current database: test
1807 Query OK, 1 row affected (1.30 sec)
1808 mysql> SELECT * FROM t;
1809 +------+
1810 | a |
1811 +------+
1812 | NULL |
1813 +------+
1814 1 row in set (0.05 sec)
1815 The @a user variable has been lost with the connection, and after the
1817 reconnection it is undefined. If it is important to have mysql
1818 terminate with an error if the connection has been lost, you can start
1819 the mysql client with the --skip-reconnect option.
1820 For more information about auto-reconnect and its effect on state
1822 information when a reconnection occurs, see Section 28.7.27, “C API
1823 Automatic Reconnection Control”. mysql Client Parser Versus Server
1824 Parser
1825 The mysql client uses a parser on the client side that is not a
1827 duplicate of the complete parser used by the mysqld server on the
1828 server side. This can lead to differences in treatment of certain
1829 constructs. Examples:
1830 · The server parser treats strings delimited by " characters as
1832 identifiers rather than as plain strings if the ANSI_QUOTES SQL
1833 mode is enabled.
1834 The mysql client parser does not take the ANSI_QUOTES SQL mode into
1836 account. It treats strings delimited by ", ', and ` characters the
1837 same, regardless of whether ANSI_QUOTES is enabled.
1838 · Within /*! ... */ and /*+ ... */ comments, the mysql client parser
1840 interprets short-form mysql commands. The server parser does not
1841 interpret them because these commands have no meaning on the server
1842 side.
1843 If it is desirable for mysql not to interpret short-form commands
1845 within comments, a partial workaround is to use the --binary-mode
1846 option, which causes all mysql commands to be disabled except \C
1847 and \d in noninteractive mode (for input piped to mysql or loaded
1848 using the source command).
1849
1851 Copyright © 1997, 2020, Oracle and/or its affiliates. All rights
1852 reserved.
1853 This documentation is free software; you can redistribute it and/or
1855 modify it only under the terms of the GNU General Public License as
1856 published by the Free Software Foundation; version 2 of the License.
1857 This documentation is distributed in the hope that it will be useful,
1859 but WITHOUT ANY WARRANTY; without even the implied warranty of
1860 MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
1861 General Public License for more details.
1862 You should have received a copy of the GNU General Public License along
1864 with the program; if not, write to the Free Software Foundation, Inc.,
1865 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA or see
1866 http://www.gnu.org/licenses/.
1867
1870 1. MySQL Shell 8.0 (part of MySQL 8.0)
1871 https://dev.mysql.com/doc/mysql-shell/8.0/en/
1872
1874 For more information, please refer to the MySQL Reference Manual, which
1875 may already be installed locally and which is also available online at
1876 http://dev.mysql.com/doc/.
1877
1879 Oracle Corporation (http://dev.mysql.com/).
1880MySQL 8.0 03/06/2020 MYSQL(1)