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, -? Display a help message and exit.
59 · --auto-rehash Enable automatic rehashing. This option is on by
61 default, which enables database, table, and column name completion.
62 Use --disable-auto-rehash to disable rehashing. That causes mysql
63 to start faster, but you must issue the rehash command or its \#
64 shortcut if you want to use name completion.
65 To complete a name, enter the first part and press Tab. If the name
67 is unambiguous, mysql completes it. Otherwise, you can press Tab
68 again to see the possible names that begin with what you have typed
69 so far. Completion does not occur if there is no default database.
70 Note
72 This feature requires a MySQL client that is compiled with the
73 readline library. Typically, the readline library is not
74 available on Windows.
75 · --auto-vertical-output Cause result sets to be displayed vertically
77 if they are too wide for the current window, and using normal
78 tabular format otherwise. (This applies to statements terminated by
79 ; or \G.)
80 · --batch, -B Print results using tab as the column separator, with
82 each row on a new line. With this option, mysql does not use the
83 history file.
84 Batch mode results in nontabular output format and escaping of
86 special characters. Escaping may be disabled by using raw mode; see
87 the description for the --raw option.
88 · --binary-as-hex When this option is given, mysql displays binary
90 data using hexadecimal notation (0xvalue). This occurs whether the
91 overall output display format is tabular, vertical, HTML, or XML.
92 --binary-as-hex when enabled affects display of all binary strings,
94 including those returned by functions such as CHAR() and UNHEX().
95 The following example demonistrates this using the ASCII code for A
96 (65 decimal, 41 hexadecimal):
97 · --binary-as-hex disabled:
99 mysql> SELECT CHAR(0x41), UNHEX('41');
101 +------------+-------------+
102 | CHAR(0x41) | UNHEX('41') |
103 +------------+-------------+
104 | A | A |
105 +------------+-------------+
106 · --binary-as-hex enabled:
108 mysql> SELECT CHAR(0x41), UNHEX('41');
110 +------------------------+--------------------------+
111 | CHAR(0x41) | UNHEX('41') |
112 +------------------------+--------------------------+
113 | 0x41 | 0x41 |
114 +------------------------+--------------------------+
115 To write a binary string expression so that it displays as a
117 character string regardless of whether --binary-as-hex is enabled,
118 use these techniques:
119 · The CHAR() function has a USING charset clause:
121 mysql> SELECT CHAR(0x41 USING utf8mb4);
123 +--------------------------+
124 | CHAR(0x41 USING utf8mb4) |
125 +--------------------------+
126 | A |
127 +--------------------------+
128 · More generally, use CONVERT() to convert an expression to a
130 given character set:
131 mysql> SELECT CONVERT(UNHEX('41') USING utf8mb4);
133 +------------------------------------+
134 | CONVERT(UNHEX('41') USING utf8mb4) |
135 +------------------------------------+
136 | A |
137 +------------------------------------+
138 As of MySQL 8.0.19, when mysql operates in interactive mode, this
140 option is enabled by default. In addition, output from the status
141 (or \s) command includes this line when the option is enabled
142 implicitly or explicitly:
143 Binary data as: Hexadecimal
145 To disable hexadecimal notation, use --skip-binary-as-hex
147 · --binary-mode This option helps when processing mysqlbinlog output
149 that may contain BLOB values. By default, mysql translates \r\n in
150 statement strings to \n and interprets \0 as the statement
151 terminator. --binary-mode disables both features. It also disables
152 all mysql commands except charset and delimiter in noninteractive
153 mode (for input piped to mysql or loaded using the source command).
154 · --bind-address=ip_address On a computer having multiple network
156 interfaces, use this option to select which interface to use for
157 connecting to the MySQL server.
158 · --character-sets-dir=dir_name The directory where character sets
160 are installed. See Section 10.15, “Character Set Configuration”.
161 · --column-names Write column names in results.
163 · --column-type-info Display result set metadata. This information
165 corresponds to the contents of C API MYSQL_FIELD data structures.
166 See C API Data Structures[2].
167 · --comments, -c Whether to strip or preserve comments in statements
169 sent to the server. The default is --skip-comments (strip
170 comments), enable with --comments (preserve comments).
171 Note
173 The mysql client always passes optimizer hints to the server,
174 regardless of whether this option is given.
175 Comment stripping is deprecated. Expect this feature and the
177 options to control it to be removed in a future MySQL release.
178 · --compress, -C Compress all information sent between the client and
180 the server if possible. See Section 4.2.8, “Connection Compression
181 Control”.
182 As of MySQL 8.0.18, this option is deprecated. Expect it be removed
184 in a future version of MySQL. See the section called “Configuring
185 Legacy Connection Compression”.
186 · --compression-algorithms=value The permitted compression algorithms
188 for connections to the server. The available algorithms are the
189 same as for the protocol_compression_algorithms system variable.
190 The default value is uncompressed.
191 For more information, see Section 4.2.8, “Connection Compression
193 Control”.
194 This option was added in MySQL 8.0.18.
196 · --connect-expired-password Indicate to the server that the client
198 can handle sandbox mode if the account used to connect has an
199 expired password. This can be useful for noninteractive invocations
200 of mysql because normally the server disconnects noninteractive
201 clients that attempt to connect using an account with an expired
202 password. (See Section 6.2.16, “Server Handling of Expired
203 Passwords”.)
204 · --connect-timeout=value The number of seconds before connection
206 timeout. (Default value is 0.)
207 · --database=db_name, -D db_name The database to use. This is useful
209 primarily in an option file.
210 · --debug[=debug_options], -# [debug_options] Write a debugging log.
212 A typical debug_options string is d:t:o,file_name. The default is
213 d:t:o,/tmp/mysql.trace.
214 This option is available only if MySQL was built using WITH_DEBUG.
216 MySQL release binaries provided by Oracle are not built using this
217 option.
218 · --debug-check Print some debugging information when the program
220 exits.
221 This option is available only if MySQL was built using WITH_DEBUG.
223 MySQL release binaries provided by Oracle are not built using this
224 option.
225 · --debug-info, -T Print debugging information and memory and CPU
227 usage statistics when the program exits.
228 This option is available only if MySQL was built using WITH_DEBUG.
230 MySQL release binaries provided by Oracle are not built using this
231 option.
232 · --default-auth=plugin A hint about which client-side authentication
234 plugin to use. See Section 6.2.17, “Pluggable Authentication”.
235 · --default-character-set=charset_name Use charset_name as the
237 default character set for the client and connection.
238 This option can be useful if the operating system uses one
240 character set and the mysql client by default uses another. In this
241 case, output may be formatted incorrectly. You can usually fix such
242 issues by using this option to force the client to use the system
243 character set instead.
244 For more information, see Section 10.4, “Connection Character Sets
246 and Collations”, and Section 10.15, “Character Set Configuration”.
247 · --defaults-extra-file=file_name Read this option file after the
249 global option file but (on Unix) before the user option file. If
250 the file does not exist or is otherwise inaccessible, an error
251 occurs. file_name is interpreted relative to the current directory
252 if given as a relative path name rather than a full path name.
253 For additional information about this and other option-file
255 options, see Section 4.2.2.3, “Command-Line Options that Affect
256 Option-File Handling”.
257 · --defaults-file=file_name Use only the given option file. If the
259 file does not exist or is otherwise inaccessible, an error occurs.
260 file_name is interpreted relative to the current directory if given
261 as a relative path name rather than a full path name.
262 Exception: Even with --defaults-file, client programs read
264 .mylogin.cnf.
265 For additional information about this and other option-file
267 options, see Section 4.2.2.3, “Command-Line Options that Affect
268 Option-File Handling”.
269 · --defaults-group-suffix=str Read not only the usual option groups,
271 but also groups with the usual names and a suffix of str. For
272 example, mysql normally reads the [client] and [mysql] groups. If
273 the --defaults-group-suffix=_other option is given, mysql also
274 reads the [client_other] and [mysql_other] groups.
275 For additional information about this and other option-file
277 options, see Section 4.2.2.3, “Command-Line Options that Affect
278 Option-File Handling”.
279 · --delimiter=str Set the statement delimiter. The default is the
281 semicolon character (;).
282 · --disable-named-commands Disable named commands. Use the \* form
284 only, or use named commands only at the beginning of a line ending
285 with a semicolon (;). mysql starts with this option enabled by
286 default. However, even with this option, long-format commands still
287 work from the first line. See the section called “MYSQL CLIENT
288 COMMANDS”.
289 · --dns-srv-name=name Specifies the name of a DNS SRV record that
291 determines the candidate hosts to use for establishing a connection
292 to a MySQL server. For information about DNS SRV support in MySQL,
293 see Section 4.2.6, “Connecting to the Server Using DNS SRV
294 Records”.
295 Suppose that DNS is configured with this SRV information for the
297 example.com domain:
298 Name TTL Class Priority Weight Port Target
300 _mysql._tcp.example.com. 86400 IN SRV 0 5 3306 host1.example.com
301 _mysql._tcp.example.com. 86400 IN SRV 0 10 3306 host2.example.com
302 _mysql._tcp.example.com. 86400 IN SRV 10 5 3306 host3.example.com
303 _mysql._tcp.example.com. 86400 IN SRV 20 5 3306 host4.example.com
304 To use that DNS SRV record, invoke mysql like this:
306 mysql --dns-srv-name=_mysql._tcp.example.com
308 mysql then attempts a connection to each server in the group until
310 a successful connection is established. A failure to connect occurs
311 only if a connection cannot be established to any of the servers.
312 The priority and weight values in the DNS SRV record determine the
313 order in which servers should be tried.
314 When invoked with --dns-srv-name, mysql attempts to establish TCP
316 connections only.
317 The --dns-srv-name option takes precedence over the --host option
319 if both are given. --dns-srv-name causes connection establishment
320 to use the mysql_real_connect_dns_srv() C API function rather than
321 mysql_real_connect(). However, if the connect command is
322 subsequently used at runtime and specifies a host name argument,
323 that host name takes precedence over any --dns-srv-name option
324 given at mysql startup to specify a DNS SRV record.
325 This option was added in MySQL 8.0.22.
327 · --enable-cleartext-plugin Enable the mysql_clear_password cleartext
329 authentication plugin. (See Section 6.4.1.4, “Client-Side Cleartext
330 Pluggable Authentication”.)
331 · --execute=statement, -e statement Execute the statement and quit.
333 The default output format is like that produced with --batch. See
334 Section 4.2.2.1, “Using Options on the Command Line”, for some
335 examples. With this option, mysql does not use the history file.
336 · --force, -f Continue even if an SQL error occurs.
338 · --get-server-public-key Request from the server the public key
340 required for RSA key pair-based password exchange. This option
341 applies to clients that authenticate with the caching_sha2_password
342 authentication plugin. For that plugin, the server does not send
343 the public key unless requested. This option is ignored for
344 accounts that do not authenticate with that plugin. It is also
345 ignored if RSA-based password exchange is not used, as is the case
346 when the client connects to the server using a secure connection.
347 If --server-public-key-path=file_name is given and specifies a
349 valid public key file, it takes precedence over
350 --get-server-public-key.
351 For information about the caching_sha2_password plugin, see
353 Section 6.4.1.2, “Caching SHA-2 Pluggable Authentication”.
354 · --histignore A list of one or more colon-separated patterns
356 specifying statements to ignore for logging purposes. These
357 patterns are added to the default pattern list
358 ("*IDENTIFIED*:*PASSWORD*"). The value specified for this option
359 affects logging of statements written to the history file, and to
360 syslog if the --syslog option is given. For more information, see
361 the section called “MYSQL CLIENT LOGGING”.
362 · --host=host_name, -h host_name Connect to the MySQL server on the
364 given host.
365 The --dns-srv-name option takes precedence over the --host option
367 if both are given. --dns-srv-name causes connection establishment
368 to use the mysql_real_connect_dns_srv() C API function rather than
369 mysql_real_connect(). However, if the connect command is
370 subsequently used at runtime and specifies a host name argument,
371 that host name takes precedence over any --dns-srv-name option
372 given at mysql startup to specify a DNS SRV record.
373 · --html, -H Produce HTML output.
375 · --ignore-spaces, -i Ignore spaces after function names. The effect
377 of this is described in the discussion for the IGNORE_SPACE SQL
378 mode (see Section 5.1.11, “Server SQL Modes”).
379 · --init-command=str SQL statement to execute after connecting to the
381 server. If auto-reconnect is enabled, the statement is executed
382 again after reconnection occurs.
383 · --line-numbers Write line numbers for errors. Disable this with
385 --skip-line-numbers.
386 · --load-data-local-dir=dir_name This option affects the client-side
388 LOCAL capability for LOAD DATA operations. It specifies the
389 directory in which files named in LOAD DATA LOCAL statements must
390 be located. The effect of --load-data-local-dir depends on whether
391 LOCAL data loading is enabled or disabled:
392 · If LOCAL data loading is enabled, either by default in the
394 MySQL client library or by specifying --local-infile[=1], the
395 --load-data-local-dir option is ignored.
396 · If LOCAL data loading is disabled, either by default in the
398 MySQL client library or by specifying --local-infile=0, the
399 --load-data-local-dir option applies.
400 When --load-data-local-dir applies, the option value designates the
402 directory in which local data files must be located. Comparison of
403 the directory path name and the path name of files to be loaded is
404 case-sensitive regardless of the case-sensitivity of the underlying
405 file system. If the option value is the empty string, it names no
406 directory, with the result that no files are permitted for local
407 data loading.
408 For example, to explicitly disable local data loading except for
410 files located in the /my/local/data directory, invoke mysql like
411 this:
412 mysql --local-infile=0 --load-data-local-dir=/my/local/data
414 When both --local-infile and --load-data-local-dir are given, the
416 order in which they are given does not matter.
417 Successful use of LOCAL load operations within mysql also requires
419 that the server permits local loading; see Section 6.1.6, “Security
420 Considerations for LOAD DATA LOCAL”
421 The --load-data-local-dir option was added in MySQL 8.0.21.
423 · --local-infile[={0|1}] By default, LOCAL capability for LOAD DATA
425 is determined by the default compiled into the MySQL client
426 library. To enable or disable LOCAL data loading explicitly, use
427 the --local-infile option. When given with no value, the option
428 enables LOCAL data loading. When given as --local-infile=0 or
429 --local-infile=1, the option disables or enables LOCAL data
430 loading.
431 If LOCAL capability is disabled, the --load-data-local-dir option
433 can be used to permit restricted local loading of files located in
434 a designated directory.
435 Successful use of LOCAL load operations within mysql also requires
437 that the server permits local loading; see Section 6.1.6, “Security
438 Considerations for LOAD DATA LOCAL”
439 · --login-path=name Read options from the named login path in the
441 .mylogin.cnf login path file. A “login path” is an option group
442 containing options that specify which MySQL server to connect to
443 and which account to authenticate as. To create or modify a login
444 path file, use the mysql_config_editor utility. See
445 mysql_config_editor(1).
446 For additional information about this and other option-file
448 options, see Section 4.2.2.3, “Command-Line Options that Affect
449 Option-File Handling”.
450 · --max-allowed-packet=value The maximum size of the buffer for
452 client/server communication. The default is 16MB, the maximum is
453 1GB.
454 · --max-join-size=value The automatic limit for rows in a join when
456 using --safe-updates. (Default value is 1,000,000.)
457 · --named-commands, -G Enable named mysql commands. Long-format
459 commands are permitted, not just short-format commands. For
460 example, quit and \q both are recognized. Use --skip-named-commands
461 to disable named commands. See the section called “MYSQL CLIENT
462 COMMANDS”.
463 · --net-buffer-length=value The buffer size for TCP/IP and socket
465 communication. (Default value is 16KB.)
466 · --network-namespace=name The network namespace to use for TCP/IP
468 connections. If omitted, the connection uses the default (global)
469 namespace. For information about network namespaces, see
470 Section 5.1.14, “Network Namespace Support”.
471 This option was added in MySQL 8.0.22. It is available only on
473 platforms that implement network namespace support.
474 · --no-auto-rehash, -A This has the same effect as
476 --skip-auto-rehash. See the description for --auto-rehash.
477 · --no-beep, -b Do not beep when errors occur.
479 · --no-defaults Do not read any option files. If program startup
481 fails due to reading unknown options from an option file,
482 --no-defaults can be used to prevent them from being read.
483 The exception is that the .mylogin.cnf file, if it exists, is read
485 in all cases. This permits passwords to be specified in a safer way
486 than on the command line even when --no-defaults is used.
487 (.mylogin.cnf is created by the mysql_config_editor utility. See
488 mysql_config_editor(1).)
489 For additional information about this and other option-file
491 options, see Section 4.2.2.3, “Command-Line Options that Affect
492 Option-File Handling”.
493 · --one-database, -o Ignore statements except those that occur while
495 the default database is the one named on the command line. This
496 option is rudimentary and should be used with care. Statement
497 filtering is based only on USE statements.
498 Initially, mysql executes statements in the input because
500 specifying a database db_name on the command line is equivalent to
501 inserting USE db_name at the beginning of the input. Then, for each
502 USE statement encountered, mysql accepts or rejects following
503 statements depending on whether the database named is the one on
504 the command line. The content of the statements is immaterial.
505 Suppose that mysql is invoked to process this set of statements:
507 DELETE FROM db2.t2;
509 USE db2;
510 DROP TABLE db1.t1;
511 CREATE TABLE db1.t1 (i INT);
512 USE db1;
513 INSERT INTO t1 (i) VALUES(1);
514 CREATE TABLE db2.t1 (j INT);
515 If the command line is mysql --force --one-database db1, mysql
517 handles the input as follows:
518 · The DELETE statement is executed because the default database
520 is db1, even though the statement names a table in a different
521 database.
522 · The DROP TABLE and CREATE TABLE statements are not executed
524 because the default database is not db1, even though the
525 statements name a table in db1.
526 · The INSERT and CREATE TABLE statements are executed because the
528 default database is db1, even though the CREATE TABLE statement
529 names a table in a different database.
530 · --pager[=command] Use the given command for paging query output. If
532 the command is omitted, the default pager is the value of your
533 PAGER environment variable. Valid pagers are less, more, cat [>
534 filename], and so forth. This option works only on Unix and only in
535 interactive mode. To disable paging, use --skip-pager. the section
536 called “MYSQL CLIENT COMMANDS”, discusses output paging further.
537 · --password[=password], -p[password] The password of the MySQL
539 account used for connecting to the server. The password value is
540 optional. If not given, mysql prompts for one. If given, there must
541 be no space between --password= or -p and the password following
542 it. If no password option is specified, the default is to send no
543 password.
544 Specifying a password on the command line should be considered
546 insecure. To avoid giving the password on the command line, use an
547 option file. See Section 6.1.2.1, “End-User Guidelines for Password
548 Security”.
549 To explicitly specify that there is no password and that mysql
551 should not prompt for one, use the --skip-password option.
552 · --pipe, -W On Windows, connect to the server using a named pipe.
554 This option applies only if the server was started with the
555 named_pipe system variable enabled to support named-pipe
556 connections. In addition, the user making the connection must be a
557 member of the Windows group specified by the
558 named_pipe_full_access_group system variable.
559 · --plugin-dir=dir_name The directory in which to look for plugins.
561 Specify this option if the --default-auth option is used to specify
562 an authentication plugin but mysql does not find it. See
563 Section 6.2.17, “Pluggable Authentication”.
564 · --port=port_num, -P port_num For TCP/IP connections, the port
566 number to use.
567 · --print-defaults Print the program name and all options that it
569 gets from option files.
570 For additional information about this and other option-file
572 options, see Section 4.2.2.3, “Command-Line Options that Affect
573 Option-File Handling”.
574 · --prompt=format_str Set the prompt to the specified format. The
576 default is mysql>. The special sequences that the prompt can
577 contain are described in the section called “MYSQL CLIENT
578 COMMANDS”.
579 · --protocol={TCP|SOCKET|PIPE|MEMORY} The transport protocol to use
581 for connecting to the server. It is useful when the other
582 connection parameters normally result in use of a protocol other
583 than the one you want. For details on the permissible values, see
584 Section 4.2.7, “Connection Transport Protocols”.
585 · --quick, -q Do not cache each query result, print each row as it is
587 received. This may slow down the server if the output is suspended.
588 With this option, mysql does not use the history file.
589 · --raw, -r For tabular output, the “boxing” around columns enables
591 one column value to be distinguished from another. For nontabular
592 output (such as is produced in batch mode or when the --batch or
593 --silent option is given), special characters are escaped in the
594 output so they can be identified easily. Newline, tab, NUL, and
595 backslash are written as \n, \t, \0, and \\. The --raw option
596 disables this character escaping.
597 The following example demonstrates tabular versus nontabular output
599 and the use of raw mode to disable escaping:
600 % mysql
602 mysql> SELECT CHAR(92);
603 +----------+
604 | CHAR(92) |
605 +----------+
606 | \ |
607 +----------+
608 % mysql -s
609 mysql> SELECT CHAR(92);
610 CHAR(92)
611 \\
612 % mysql -s -r
613 mysql> SELECT CHAR(92);
614 CHAR(92)
615 \
616 · --reconnect If the connection to the server is lost, automatically
618 try to reconnect. A single reconnect attempt is made each time the
619 connection is lost. To suppress reconnection behavior, use
620 --skip-reconnect.
621 · --safe-updates, --i-am-a-dummy, -U If this option is enabled,
623 UPDATE and DELETE statements that do not use a key in the WHERE
624 clause or a LIMIT clause produce an error. In addition,
625 restrictions are placed on SELECT statements that produce (or are
626 estimated to produce) very large result sets. If you have set this
627 option in an option file, you can use --skip-safe-updates on the
628 command line to override it. For more information about this
629 option, see Using Safe-Updates Mode (--safe-updates).
630 · --select-limit=value The automatic limit for SELECT statements when
632 using --safe-updates. (Default value is 1,000.)
633 · --server-public-key-path=file_name The path name to a file in PEM
635 format containing a client-side copy of the public key required by
636 the server for RSA key pair-based password exchange. This option
637 applies to clients that authenticate with the sha256_password or
638 caching_sha2_password authentication plugin. This option is ignored
639 for accounts that do not authenticate with one of those plugins. It
640 is also ignored if RSA-based password exchange is not used, as is
641 the case when the client connects to the server using a secure
642 connection.
643 If --server-public-key-path=file_name is given and specifies a
645 valid public key file, it takes precedence over
646 --get-server-public-key.
647 This option is available only if MySQL was built using OpenSSL.
649 For information about the sha256_password and caching_sha2_password
651 plugins, see Section 6.4.1.3, “SHA-256 Pluggable Authentication”,
652 and Section 6.4.1.2, “Caching SHA-2 Pluggable Authentication”.
653 · --shared-memory-base-name=name On Windows, the shared-memory name
655 to use for connections made using shared memory to a local server.
656 The default value is MYSQL. The shared-memory name is
657 case-sensitive.
658 This option applies only if the server was started with the
660 shared_memory system variable enabled to support shared-memory
661 connections.
662 · --show-warnings Cause warnings to be shown after each statement if
664 there are any. This option applies to interactive and batch mode.
665 · --sigint-ignore Ignore SIGINT signals (typically the result of
667 typing Control+C).
668 Without this option, typing Control+C interrupts the current
670 statement if there is one, or cancels any partial input line
671 otherwise.
672 · --silent, -s Silent mode. Produce less output. This option can be
674 given multiple times to produce less and less output.
675 This option results in nontabular output format and escaping of
677 special characters. Escaping may be disabled by using raw mode; see
678 the description for the --raw option.
679 · --skip-column-names, -N Do not write column names in results.
681 · --skip-line-numbers, -L Do not write line numbers for errors.
683 Useful when you want to compare result files that include error
684 messages.
685 · --socket=path, -S path For connections to localhost, the Unix
687 socket file to use, or, on Windows, the name of the named pipe to
688 use.
689 On Windows, this option applies only if the server was started with
691 the named_pipe system variable enabled to support named-pipe
692 connections. In addition, the user making the connection must be a
693 member of the Windows group specified by the
694 named_pipe_full_access_group system variable.
695 · --ssl* Options that begin with --ssl specify whether to connect to
697 the server using SSL and indicate where to find SSL keys and
698 certificates. See the section called “Command Options for Encrypted
699 Connections”.
700 · --ssl-fips-mode={OFF|ON|STRICT} Controls whether to enable FIPS
702 mode on the client side. The --ssl-fips-mode option differs from
703 other --ssl-xxx options in that it is not used to establish
704 encrypted connections, but rather to affect which cryptographic
705 operations to permit. See Section 6.8, “FIPS Support”.
706 These --ssl-fips-mode values are permitted:
708 · OFF: Disable FIPS mode.
710 · ON: Enable FIPS mode.
712 · STRICT: Enable “strict” FIPS mode.
714 Note
717 If the OpenSSL FIPS Object Module is not available, the only
718 permitted value for --ssl-fips-mode is OFF. In this case,
719 setting --ssl-fips-mode to ON or STRICT causes the client to
720 produce a warning at startup and to operate in non-FIPS mode.
721 · --syslog, -j This option causes mysql to send interactive
723 statements to the system logging facility. On Unix, this is syslog;
724 on Windows, it is the Windows Event Log. The destination where
725 logged messages appear is system dependent. On Linux, the
726 destination is often the /var/log/messages file.
727 Here is a sample of output generated on Linux by using --syslog.
729 This output is formatted for readability; each logged message
730 actually takes a single line.
731 Mar 7 12:39:25 myhost MysqlClient[20824]:
733 SYSTEM_USER:'oscar', MYSQL_USER:'my_oscar', CONNECTION_ID:23,
734 DB_SERVER:'127.0.0.1', DB:'--', QUERY:'USE test;'
735 Mar 7 12:39:28 myhost MysqlClient[20824]:
736 SYSTEM_USER:'oscar', MYSQL_USER:'my_oscar', CONNECTION_ID:23,
737 DB_SERVER:'127.0.0.1', DB:'test', QUERY:'SHOW TABLES;'
738 For more information, see the section called “MYSQL CLIENT
740 LOGGING”.
741 · --table, -t Display output in table format. This is the default for
743 interactive use, but can be used to produce table output in batch
744 mode.
745 · --tee=file_name Append a copy of output to the given file. This
747 option works only in interactive mode. the section called “MYSQL
748 CLIENT COMMANDS”, discusses tee files further.
749 · --tls-ciphersuites=ciphersuite_list The permissible ciphersuites
751 for encrypted connections that use TLSv1.3. The value is a list of
752 one or more colon-separated ciphersuite names. The ciphersuites
753 that can be named for this option depend on the SSL library used to
754 compile MySQL. For details, see Section 6.3.2, “Encrypted
755 Connection TLS Protocols and Ciphers”.
756 This option was added in MySQL 8.0.16.
758 · --tls-version=protocol_list The permissible TLS protocols for
760 encrypted connections. The value is a list of one or more
761 comma-separated protocol names. The protocols that can be named for
762 this option depend on the SSL library used to compile MySQL. For
763 details, see Section 6.3.2, “Encrypted Connection TLS Protocols and
764 Ciphers”.
765 · --unbuffered, -n Flush the buffer after each query.
767 · --user=user_name, -u user_name The user name of the MySQL account
769 to use for connecting to the server.
770 · --verbose, -v Verbose mode. Produce more output about what the
772 program does. This option can be given multiple times to produce
773 more and more output. (For example, -v -v -v produces table output
774 format even in batch mode.)
775 · --version, -V Display version information and exit.
777 · --vertical, -E Print query output rows vertically (one line per
779 column value). Without this option, you can specify vertical output
780 for individual statements by terminating them with \G.
781 · --wait, -w If the connection cannot be established, wait and retry
783 instead of aborting.
784 · --xml, -X Produce XML output.
786 <field name="column_name">NULL</field>
788 The output when --xml is used with mysql matches that of mysqldump
790 --xml. See mysqldump(1), for details.
791 The XML output also uses an XML namespace, as shown here:
793 shell> mysql --xml -uroot -e "SHOW VARIABLES LIKE 'version%'"
795 <?xml version="1.0"?>
796 <resultset statement="SHOW VARIABLES LIKE 'version%'" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
797 <row>
798 <field name="Variable_name">version</field>
799 <field name="Value">5.0.40-debug</field>
800 </row>
801 <row>
802 <field name="Variable_name">version_comment</field>
803 <field name="Value">Source distribution</field>
804 </row>
805 <row>
806 <field name="Variable_name">version_compile_machine</field>
807 <field name="Value">i686</field>
808 </row>
809 <row>
810 <field name="Variable_name">version_compile_os</field>
811 <field name="Value">suse-linux-gnu</field>
812 </row>
813 </resultset>
814 · --zstd-compression-level=level The compression level to use for
816 connections to the server that use the zstd compression algorithm.
817 The permitted levels are from 1 to 22, with larger values
818 indicating increasing levels of compression. The default zstd
819 compression level is 3. The compression level setting has no effect
820 on connections that do not use zstd compression.
821 For more information, see Section 4.2.8, “Connection Compression
823 Control”.
824 This option was added in MySQL 8.0.18.
826
828 mysql sends each SQL statement that you issue to the server to be
829 executed. There is also a set of commands that mysql itself interprets.
830 For a list of these commands, type help or \h at the mysql> prompt:
831 mysql> help
833 List of all MySQL commands:
834 Note that all text commands must be first on line and end with ';'
835 ? (\?) Synonym for `help'.
836 clear (\c) Clear the current input statement.
837 connect (\r) Reconnect to the server. Optional arguments are db and host.
838 delimiter (\d) Set statement delimiter.
839 edit (\e) Edit command with $EDITOR.
840 ego (\G) Send command to mysql server, display result vertically.
841 exit (\q) Exit mysql. Same as quit.
842 go (\g) Send command to mysql server.
843 help (\h) Display this help.
844 nopager (\n) Disable pager, print to stdout.
845 notee (\t) Don't write into outfile.
846 pager (\P) Set PAGER [to_pager]. Print the query results via PAGER.
847 print (\p) Print current command.
848 prompt (\R) Change your mysql prompt.
849 quit (\q) Quit mysql.
850 rehash (\#) Rebuild completion hash.
851 source (\.) Execute an SQL script file. Takes a file name as an argument.
852 status (\s) Get status information from the server.
853 system (\!) Execute a system shell command.
854 tee (\T) Set outfile [to_outfile]. Append everything into given
855 outfile.
856 use (\u) Use another database. Takes database name as argument.
857 charset (\C) Switch to another charset. Might be needed for processing
858 binlog with multi-byte charsets.
859 warnings (\W) Show warnings after every statement.
860 nowarning (\w) Don't show warnings after every statement.
861 resetconnection(\x) Clean session context.
862 query_attributes(\) Sets string parameters (name1 value1 name2 value2 ...)
863 for the next query to pick up.
864 For server side help, type 'help contents'
865 If mysql is invoked with the --binary-mode option, all mysql commands
867 are disabled except charset and delimiter in noninteractive mode (for
868 input piped to mysql or loaded using the source command).
869 Each command has both a long and short form. The long form is not
871 case-sensitive; the short form is. The long form can be followed by an
872 optional semicolon terminator, but the short form should not.
873 The use of short-form commands within multiple-line /* ... */ comments
875 is not supported. Short-form commands do work within single-line /*!
876 ... */ version comments, as do /*+ ... */ optimizer-hint comments,
877 which are stored in object definitions. If there is a concern that
878 optimizer-hint comments may be stored in object definitions so that
879 dump files when reloaded with mysql would result in execution of such
880 commands, either invoke mysql with the --binary-mode option or use a
881 reload client other than mysql.
882 · help [arg], \h [arg], \? [arg], ? [arg]
884 Display a help message listing the available mysql commands.
886 If you provide an argument to the help command, mysql uses it as a
888 search string to access server-side help from the contents of the
889 MySQL Reference Manual. For more information, see the section
890 called “MYSQL CLIENT SERVER-SIDE HELP”.
891 · charset charset_name, \C charset_name
893 Change the default character set and issue a SET NAMES statement.
895 This enables the character set to remain synchronized on the client
896 and server if mysql is run with auto-reconnect enabled (which is
897 not recommended), because the specified character set is used for
898 reconnects.
899 · clear, \c
901 Clear the current input. Use this if you change your mind about
903 executing the statement that you are entering.
904 · connect [db_name [host_name]], \r [db_name [host_name]]
906 Reconnect to the server. The optional database name and host name
908 arguments may be given to specify the default database or the host
909 where the server is running. If omitted, the current values are
910 used.
911 If the connect command specifies a host name argument, that host
913 takes precedence over any --dns-srv-name option given at mysql
914 startup to specify a DNS SRV record.
915 · delimiter str, \d str
917 Change the string that mysql interprets as the separator between
919 SQL statements. The default is the semicolon character (;).
920 The delimiter string can be specified as an unquoted or quoted
922 argument on the delimiter command line. Quoting can be done with
923 either single quote ('), double quote ("), or backtick (`)
924 characters. To include a quote within a quoted string, either quote
925 the string with a different quote character or escape the quote
926 with a backslash (\) character. Backslash should be avoided outside
927 of quoted strings because it is the escape character for MySQL. For
928 an unquoted argument, the delimiter is read up to the first space
929 or end of line. For a quoted argument, the delimiter is read up to
930 the matching quote on the line.
931 mysql interprets instances of the delimiter string as a statement
933 delimiter anywhere it occurs, except within quoted strings. Be
934 careful about defining a delimiter that might occur within other
935 words. For example, if you define the delimiter as X, it is not
936 possible to use the word INDEX in statements. mysql interprets
937 this as INDE followed by the delimiter X.
938 When the delimiter recognized by mysql is set to something other
940 than the default of ;, instances of that character are sent to the
941 server without interpretation. However, the server itself still
942 interprets ; as a statement delimiter and processes statements
943 accordingly. This behavior on the server side comes into play for
944 multiple-statement execution (see C API Multiple Statement
945 Execution Support[3]), and for parsing the body of stored
946 procedures and functions, triggers, and events (see Section 25.1,
947 “Defining Stored Programs”).
948 · edit, \e
950 Edit the current input statement. mysql checks the values of the
952 EDITOR and VISUAL environment variables to determine which editor
953 to use. The default editor is vi if neither variable is set.
954 The edit command works only in Unix.
956 · ego, \G
958 Send the current statement to the server to be executed and display
960 the result using vertical format.
961 · exit, \q
963 Exit mysql.
965 · go, \g
967 Send the current statement to the server to be executed.
969 · nopager, \n
971 Disable output paging. See the description for pager.
973 The nopager command works only in Unix.
975 · notee, \t
977 Disable output copying to the tee file. See the description for
979 tee.
980 · nowarning, \w
982 Disable display of warnings after each statement.
984 · pager [command], \P [command]
986 Enable output paging. By using the --pager option when you invoke
988 mysql, it is possible to browse or search query results in
989 interactive mode with Unix programs such as less, more, or any
990 other similar program. If you specify no value for the option,
991 mysql checks the value of the PAGER environment variable and sets
992 the pager to that. Pager functionality works only in interactive
993 mode.
994 Output paging can be enabled interactively with the pager command
996 and disabled with nopager. The command takes an optional argument;
997 if given, the paging program is set to that. With no argument, the
998 pager is set to the pager that was set on the command line, or
999 stdout if no pager was specified.
1000 Output paging works only in Unix because it uses the popen()
1002 function, which does not exist on Windows. For Windows, the tee
1003 option can be used instead to save query output, although it is not
1004 as convenient as pager for browsing output in some situations.
1005 · print, \p
1007 Print the current input statement without executing it.
1009 · prompt [str], \R [str]
1011 Reconfigure the mysql prompt to the given string. The special
1013 character sequences that can be used in the prompt are described
1014 later in this section.
1015 If you specify the prompt command with no argument, mysql resets
1017 the prompt to the default of mysql>.
1018 · query_attributes name value [name value ...]
1020 Define query attributes that apply to the next query sent to the
1022 server. For discussion of the purpose and use of query attributes,
1023 see Section 9.6, “Query Attributes”.
1024 The query_attributes command follows these rules:
1026 · The format and quoting rules for attribute names and values are
1028 the same as for the delimiter command.
1029 · The command permits up to 32 attribute name/value pairs. Names
1031 and values may be up to 1024 characters long. If a name is
1032 given without a value, an error occurs.
1033 · If multiple query_attributes commands are issued prior to query
1035 execution, only the last command applies. After sending the
1036 query, mysql clears the attribute set.
1037 · If multiple attributes are defined with the same name, attempts
1039 to retrieve the attribute value have an undefined result.
1040 · An attribute defined with an empty name cannot be retrieved by
1042 name.
1043 · If a reconnect occurs while mysql executes the query, mysql
1045 restores the attributes after reconnecting so the query can be
1046 executed again with the same attributes.
1047 · quit, \q
1050 Exit mysql.
1052 · rehash, \#
1054 Rebuild the completion hash that enables database, table, and
1056 column name completion while you are entering statements. (See the
1057 description for the --auto-rehash option.)
1058 · resetconnection, \x
1060 Reset the connection to clear the session state. This includes
1062 clearing any current query attributes defined using the
1063 query_attributes command.
1064 Resetting a connection has effects similar to mysql_change_user()
1066 or an auto-reconnect except that the connection is not closed and
1067 reopened, and re-authentication is not done. See
1068 mysql_change_user()[4], and C API Automatic Reconnection
1069 Control[5].
1070 This example shows how resetconnection clears a value maintained in
1072 the session state:
1073 mysql> SELECT LAST_INSERT_ID(3);
1075 +-------------------+
1076 | LAST_INSERT_ID(3) |
1077 +-------------------+
1078 | 3 |
1079 +-------------------+
1080 mysql> SELECT LAST_INSERT_ID();
1081 +------------------+
1082 | LAST_INSERT_ID() |
1083 +------------------+
1084 | 3 |
1085 +------------------+
1086 mysql> resetconnection;
1087 mysql> SELECT LAST_INSERT_ID();
1088 +------------------+
1089 | LAST_INSERT_ID() |
1090 +------------------+
1091 | 0 |
1092 +------------------+
1093 · source file_name, \. file_name
1095 Read the named file and executes the statements contained therein.
1097 On Windows, specify path name separators as / or \\.
1098 Quote characters are taken as part of the file name itself. For
1100 best results, the name should not include space characters.
1101 · status, \s
1103 Provide status information about the connection and the server you
1105 are using. If you are running with --safe-updates enabled, status
1106 also prints the values for the mysql variables that affect your
1107 queries.
1108 · system command, \! command
1110 Execute the given command using your default command interpreter.
1112 Prior to MySQL 8.0.19, the system command works only in Unix. As of
1114 8.0.19, it also works on Windows.
1115 · tee [file_name], \T [file_name]
1117 By using the --tee option when you invoke mysql, you can log
1119 statements and their output. All the data displayed on the screen
1120 is appended into a given file. This can be very useful for
1121 debugging purposes also. mysql flushes results to the file after
1122 each statement, just before it prints its next prompt. Tee
1123 functionality works only in interactive mode.
1124 You can enable this feature interactively with the tee command.
1126 Without a parameter, the previous file is used. The tee file can be
1127 disabled with the notee command. Executing tee again re-enables
1128 logging.
1129 · use db_name, \u db_name
1131 Use db_name as the default database.
1133 · warnings, \W
1135 Enable display of warnings after each statement (if there are any).
1137 Here are a few tips about the pager command:
1139 · You can use it to write to a file and the results go only to the
1141 file:
1142 mysql> pager cat > /tmp/log.txt
1144 You can also pass any options for the program that you want to use
1146 as your pager:
1147 mysql> pager less -n -i -S
1149 · In the preceding example, note the -S option. You may find it very
1151 useful for browsing wide query results. Sometimes a very wide
1152 result set is difficult to read on the screen. The -S option to
1153 less can make the result set much more readable because you can
1154 scroll it horizontally using the left-arrow and right-arrow keys.
1155 You can also use -S interactively within less to switch the
1156 horizontal-browse mode on and off. For more information, read the
1157 less manual page:
1158 shell> man less
1160 · The -F and -X options may be used with less to cause it to exit if
1162 output fits on one screen, which is convenient when no scrolling is
1163 necessary:
1164 mysql> pager less -n -i -S -F -X
1166 · You can specify very complex pager commands for handling query
1168 output:
1169 mysql> pager cat | tee /dr1/tmp/res.txt \
1171 | tee /dr2/tmp/res2.txt | less -n -i -S
1172 In this example, the command would send query results to two files
1174 in two different directories on two different file systems mounted
1175 on /dr1 and /dr2, yet still display the results onscreen using
1176 less.
1177 You can also combine the tee and pager functions. Have a tee file
1179 enabled and pager set to less, and you are able to browse the results
1180 using the less program and still have everything appended into a file
1181 the same time. The difference between the Unix tee used with the pager
1182 command and the mysql built-in tee command is that the built-in tee
1183 works even if you do not have the Unix tee available. The built-in tee
1184 also logs everything that is printed on the screen, whereas the Unix
1185 tee used with pager does not log quite that much. Additionally, tee
1186 file logging can be turned on and off interactively from within mysql.
1187 This is useful when you want to log some queries to a file, but not
1188 others.
1189 The prompt command reconfigures the default mysql> prompt. The string
1191 for defining the prompt can contain the following special sequences.
1192.br
1194.br
1195.br
119672
1197 ┌───────────────────────────┬────────────────────────────┐
1198 │Option │ Description │
1199 ├───────────────────────────┼────────────────────────────┤
1200 │ │ The current connection │
1201 │ │ identifier │
1202 ├───────────────────────────┼────────────────────────────┤
1203 │ │ A counter that increments │
1204 │ │ for each statement you │
1205 │ │ issue │
1206 ├───────────────────────────┼────────────────────────────┤
1207 │ │ The full current date │
1208 ├───────────────────────────┼────────────────────────────┤
1209 │ │ The default database │
1210 ├───────────────────────────┼────────────────────────────┤
1211 │ │ The server host │
1212 ├───────────────────────────┼────────────────────────────┤
1213 │ │ The current delimiter │
1214 ├───────────────────────────┼────────────────────────────┤
1215 │ │ Minutes of the current │
1216 │ │ time │
1217 ├───────────────────────────┼────────────────────────────┤
1218 │ │ A newline character │
1219 ├───────────────────────────┼────────────────────────────┤
1220 │ │ The current month in │
1221 │ │ three-letter format (Jan, │
1222 │ │ Feb, ...) │
1223 ├───────────────────────────┼────────────────────────────┤
1224 │ │ The current month in │
1225 │ │ numeric format │
1226 ├───────────────────────────┼────────────────────────────┤
1227 │P │ am/pm │
1228 ├───────────────────────────┼────────────────────────────┤
1229 │ │ The current TCP/IP port or │
1230 │ │ socket file │
1231 ├───────────────────────────┼────────────────────────────┤
1232 │ │ The current time, in │
1233 │ │ 24-hour military time │
1234 │ │ (0–23) │
1235 ├───────────────────────────┼────────────────────────────┤
1236 │ │ The current time, standard │
1237 │ │ 12-hour time (1–12) │
1238 ├───────────────────────────┼────────────────────────────┤
1239 │ │ Semicolon │
1240 ├───────────────────────────┼────────────────────────────┤
1241 │ │ Seconds of the current │
1242 │ │ time │
1243 ├───────────────────────────┼────────────────────────────┤
1244 │ │ A tab character │
1245 ├───────────────────────────┼────────────────────────────┤
1246 │U │ │
1247 │ │ Your full │
1248 │ │ user_name@host_name │
1249 │ │ account name │
1250 ├───────────────────────────┼────────────────────────────┤
1251 │ │ Your user name │
1252 ├───────────────────────────┼────────────────────────────┤
1253 │ │ The server version │
1254 ├───────────────────────────┼────────────────────────────┤
1255 │ │ The current day of the │
1256 │ │ week in three-letter │
1257 │ │ format (Mon, Tue, ...) │
1258 ├───────────────────────────┼────────────────────────────┤
1259 │ │ The current year, four │
1260 │ │ digits │
1261 ├───────────────────────────┼────────────────────────────┤
1262 │y │ The current year, two │
1263 │ │ digits │
1264 ├───────────────────────────┼────────────────────────────┤
1265 │_ │ A space │
1266 ├───────────────────────────┼────────────────────────────┤
1267 │\ │ A space (a space follows │
1268 │ │ the backslash) │
1269 ├───────────────────────────┼────────────────────────────┤
1270 │´ │ Single quote │
1271 ├───────────────────────────┼────────────────────────────┤
1272 │ │ Double quote │
1273 ├───────────────────────────┼────────────────────────────┤
1274 │T}:T{ A literal backslash │ │
1275 │character │ │
1276 ├───────────────────────────┼────────────────────────────┤
1277 │\fIx │ │
1278 │ │ x, for any “x” not │
1279 │ │ listed above │
1280 └───────────────────────────┴────────────────────────────┘
1281 You can set the prompt in several ways:
1283 · Use an environment variable. You can set the MYSQL_PS1 environment
1285 variable to a prompt string. For example:
1286 shell> export MYSQL_PS1="(\u@\h) [\d]> "
1288 · Use a command-line option. You can set the --prompt option on the
1290 command line to mysql. For example:
1291 shell> mysql --prompt="(\u@\h) [\d]> "
1293 (user@host) [database]>
1294 · Use an option file. You can set the prompt option in the [mysql]
1296 group of any MySQL option file, such as /etc/my.cnf or the .my.cnf
1297 file in your home directory. For example:
1298 [mysql]
1300 prompt=(\\u@\\h) [\\d]>\\_
1301 In this example, note that the backslashes are doubled. If you set
1303 the prompt using the prompt option in an option file, it is
1304 advisable to double the backslashes when using the special prompt
1305 options. There is some overlap in the set of permissible prompt
1306 options and the set of special escape sequences that are recognized
1307 in option files. (The rules for escape sequences in option files
1308 are listed in Section 4.2.2.2, “Using Option Files”.) The overlap
1309 may cause you problems if you use single backslashes. For example,
1310 \s is interpreted as a space rather than as the current seconds
1311 value. The following example shows how to define a prompt within an
1312 option file to include the current time in hh:mm:ss> format:
1313 [mysql]
1315 prompt="\\r:\\m:\\s> "
1316 · Set the prompt interactively. You can change your prompt
1318 interactively by using the prompt (or \R) command. For example:
1319 mysql> prompt (\u@\h) [\d]>\_
1321 PROMPT set to '(\u@\h) [\d]>\_'
1322 (user@host) [database]>
1323 (user@host) [database]> prompt
1324 Returning to default PROMPT of mysql>
1325 mysql>
1326
1328 The mysql client can do these types of logging for statements executed
1329 interactively:
1330 · On Unix, mysql writes the statements to a history file. By default,
1332 this file is named .mysql_history in your home directory. To
1333 specify a different file, set the value of the MYSQL_HISTFILE
1334 environment variable.
1335 · On all platforms, if the --syslog option is given, mysql writes the
1337 statements to the system logging facility. On Unix, this is syslog;
1338 on Windows, it is the Windows Event Log. The destination where
1339 logged messages appear is system dependent. On Linux, the
1340 destination is often the /var/log/messages file.
1341 The following discussion describes characteristics that apply to all
1343 logging types and provides information specific to each logging type.
1344 · How Logging Occurs
1346 · Controlling the History File
1348 · syslog Logging Characteristics
1350 How Logging Occurs
1351 For each enabled logging destination, statement logging occurs as
1353 follows:
1354 · Statements are logged only when executed interactively. Statements
1356 are noninteractive, for example, when read from a file or a pipe.
1357 It is also possible to suppress statement logging by using the
1358 --batch or --execute option.
1359 · Statements are ignored and not logged if they match any pattern in
1361 the “ignore” list. This list is described later.
1362 · mysql logs each nonignored, nonempty statement line individually.
1364 · If a nonignored statement spans multiple lines (not including the
1366 terminating delimiter), mysql concatenates the lines to form the
1367 complete statement, maps newlines to spaces, and logs the result,
1368 plus a delimiter.
1369 Consequently, an input statement that spans multiple lines can be
1371 logged twice. Consider this input:
1372 mysql> SELECT
1374 -> 'Today is'
1375 -> ,
1376 -> CURDATE()
1377 -> ;
1378 In this case, mysql logs the “SELECT”, “'Today is'”, “,”, “CURDATE()”,
1380 and “;” lines as it reads them. It also logs the complete statement,
1381 after mapping SELECT\n'Today is'\n,\nCURDATE() to SELECT 'Today is' ,
1382 CURDATE(), plus a delimiter. Thus, these lines appear in logged output:
1383 SELECT
1385 'Today is'
1386 ,
1387 CURDATE()
1388 ;
1389 SELECT 'Today is' , CURDATE();
1390 mysql ignores for logging purposes statements that match any pattern in
1392 the “ignore” list. By default, the pattern list is
1393 "*IDENTIFIED*:*PASSWORD*", to ignore statements that refer to
1394 passwords. Pattern matching is not case-sensitive. Within patterns, two
1395 characters are special:
1396 · ? matches any single character.
1398 · * matches any sequence of zero or more characters.
1400 To specify additional patterns, use the --histignore option or set the
1402 MYSQL_HISTIGNORE environment variable. (If both are specified, the
1403 option value takes precedence.) The value should be a list of one or
1404 more colon-separated patterns, which are appended to the default
1405 pattern list.
1406 Patterns specified on the command line might need to be quoted or
1408 escaped to prevent your command interpreter from treating them
1409 specially. For example, to suppress logging for UPDATE and DELETE
1410 statements in addition to statements that refer to passwords, invoke
1411 mysql like this:
1412 shell> mysql --histignore="*UPDATE*:*DELETE*"
1414 Controlling the History File
1416 The .mysql_history file should be protected with a restrictive access
1418 mode because sensitive information might be written to it, such as the
1419 text of SQL statements that contain passwords. See Section 6.1.2.1,
1420 “End-User Guidelines for Password Security”. Statements in the file are
1421 accessible from the mysql client when the up-arrow key is used to
1422 recall the history. See Disabling Interactive History.
1423 If you do not want to maintain a history file, first remove
1425 .mysql_history if it exists. Then use either of the following
1426 techniques to prevent it from being created again:
1427 · Set the MYSQL_HISTFILE environment variable to /dev/null. To cause
1429 this setting to take effect each time you log in, put it in one of
1430 your shell's startup files.
1431 · Create .mysql_history as a symbolic link to /dev/null; this need be
1433 done only once:
1434 shell> ln -s /dev/null $HOME/.mysql_history
1436 syslog Logging Characteristics
1437 If the --syslog option is given, mysql writes interactive statements to
1439 the system logging facility. Message logging has the following
1440 characteristics.
1441 Logging occurs at the “information” level. This corresponds to the
1443 LOG_INFO priority for syslog on Unix/Linux syslog capability and to
1444 EVENTLOG_INFORMATION_TYPE for the Windows Event Log. Consult your
1445 system documentation for configuration of your logging capability.
1446 Message size is limited to 1024 bytes.
1448 Messages consist of the identifier MysqlClient followed by these
1450 values:
1451 · SYSTEM_USER
1453 The operating system user name (login name) or -- if the user is
1455 unknown.
1456 · MYSQL_USER
1458 The MySQL user name (specified with the --user option) or -- if the
1460 user is unknown.
1461 · CONNECTION_ID:
1463 The client connection identifier. This is the same as the
1465 CONNECTION_ID() function value within the session.
1466 · DB_SERVER
1468 The server host or -- if the host is unknown.
1470 · DB
1472 The default database or -- if no database has been selected.
1474 · QUERY
1476 The text of the logged statement.
1478 Here is a sample of output generated on Linux by using --syslog. This
1480 output is formatted for readability; each logged message actually takes
1481 a single line.
1482 Mar 7 12:39:25 myhost MysqlClient[20824]:
1484 SYSTEM_USER:'oscar', MYSQL_USER:'my_oscar', CONNECTION_ID:23,
1485 DB_SERVER:'127.0.0.1', DB:'--', QUERY:'USE test;'
1486 Mar 7 12:39:28 myhost MysqlClient[20824]:
1487 SYSTEM_USER:'oscar', MYSQL_USER:'my_oscar', CONNECTION_ID:23,
1488 DB_SERVER:'127.0.0.1', DB:'test', QUERY:'SHOW TABLES;'
1489
1491 mysql> help search_string
1492 If you provide an argument to the help command, mysql uses it as a
1494 search string to access server-side help from the contents of the MySQL
1495 Reference Manual. The proper operation of this command requires that
1496 the help tables in the mysql database be initialized with help topic
1497 information (see Section 5.1.17, “Server-Side Help Support”).
1498 If there is no match for the search string, the search fails:
1500 mysql> help me
1502 Nothing found
1503 Please try to run 'help contents' for a list of all accessible topics
1504 Use help contents to see a list of the help categories:
1506 mysql> help contents
1508 You asked for help about help category: "Contents"
1509 For more information, type 'help <item>', where <item> is one of the
1510 following categories:
1511 Account Management
1512 Administration
1513 Data Definition
1514 Data Manipulation
1515 Data Types
1516 Functions
1517 Functions and Modifiers for Use with GROUP BY
1518 Geographic Features
1519 Language Structure
1520 Plugins
1521 Storage Engines
1522 Stored Routines
1523 Table Maintenance
1524 Transactions
1525 Triggers
1526 If the search string matches multiple items, mysql shows a list of
1528 matching topics:
1529 mysql> help logs
1531 Many help items for your request exist.
1532 To make a more specific request, please type 'help <item>',
1533 where <item> is one of the following topics:
1534 SHOW
1535 SHOW BINARY LOGS
1536 SHOW ENGINE
1537 SHOW LOGS
1538 Use a topic as the search string to see the help entry for that topic:
1540 mysql> help show binary logs
1542 Name: 'SHOW BINARY LOGS'
1543 Description:
1544 Syntax:
1545 SHOW BINARY LOGS
1546 SHOW MASTER LOGS
1547 Lists the binary log files on the server. This statement is used as
1548 part of the procedure described in [purge-binary-logs], that shows how
1549 to determine which logs can be purged.
1550 mysql> SHOW BINARY LOGS;
1551 +---------------+-----------+-----------+
1552 | Log_name | File_size | Encrypted |
1553 +---------------+-----------+-----------+
1554 | binlog.000015 | 724935 | Yes |
1555 | binlog.000016 | 733481 | Yes |
1556 +---------------+-----------+-----------+
1557 The search string can contain the wildcard characters % and _. These
1559 have the same meaning as for pattern-matching operations performed with
1560 the LIKE operator. For example, HELP rep% returns a list of topics that
1561 begin with rep:
1562 mysql> HELP rep%
1564 Many help items for your request exist.
1565 To make a more specific request, please type 'help <item>',
1566 where <item> is one of the following
1567 topics:
1568 REPAIR TABLE
1569 REPEAT FUNCTION
1570 REPEAT LOOP
1571 REPLACE
1572 REPLACE FUNCTION
1573
1575 The mysql client typically is used interactively, like this:
1576 shell> mysql db_name
1578 However, it is also possible to put your SQL statements in a file and
1580 then tell mysql to read its input from that file. To do so, create a
1581 text file text_file that contains the statements you wish to execute.
1582 Then invoke mysql as shown here:
1583 shell> mysql db_name < text_file
1585 If you place a USE db_name statement as the first statement in the
1587 file, it is unnecessary to specify the database name on the command
1588 line:
1589 shell> mysql < text_file
1591 If you are already running mysql, you can execute an SQL script file
1593 using the source command or \. command:
1594 mysql> source file_name
1596 mysql> \. file_name
1597 Sometimes you may want your script to display progress information to
1599 the user. For this you can insert statements like this:
1600 SELECT '<info_to_display>' AS ' ';
1602 The statement shown outputs <info_to_display>.
1604 You can also invoke mysql with the --verbose option, which causes each
1606 statement to be displayed before the result that it produces.
1607 mysql ignores Unicode byte order mark (BOM) characters at the beginning
1609 of input files. Previously, it read them and sent them to the server,
1610 resulting in a syntax error. Presence of a BOM does not cause mysql to
1611 change its default character set. To do that, invoke mysql with an
1612 option such as --default-character-set=utf8.
1613 For more information about batch mode, see Section 3.5, “Using mysql in
1615 Batch Mode”.
1616
1618 This section provides information about techniques for more effective
1619 use of mysql and about mysql operational behavior.
1620 · Input-Line Editing
1622 · Disabling Interactive History
1624 · Unicode Support on Windows
1626 · Displaying Query Results Vertically
1628 · Using Safe-Updates Mode (--safe-updates)
1630 · Disabling mysql Auto-Reconnect
1632 · mysql Client Parser Versus Server Parser
1634 Input-Line Editing
1635 mysql supports input-line editing, which enables you to modify the
1637 current input line in place or recall previous input lines. For
1638 example, the left-arrow and right-arrow keys move horizontally within
1639 the current input line, and the up-arrow and down-arrow keys move up
1640 and down through the set of previously entered lines. Backspace
1641 deletes the character before the cursor and typing new characters
1642 enters them at the cursor position. To enter the line, press Enter.
1643 On Windows, the editing key sequences are the same as supported for
1645 command editing in console windows. On Unix, the key sequences depend
1646 on the input library used to build mysql (for example, the libedit or
1647 readline library).
1648 Documentation for the libedit and readline libraries is available
1650 online. To change the set of key sequences permitted by a given input
1651 library, define key bindings in the library startup file. This is a
1652 file in your home directory: .editrc for libedit and .inputrc for
1653 readline.
1654 For example, in libedit, Control+W deletes everything before the
1656 current cursor position and Control+U deletes the entire line. In
1657 readline, Control+W deletes the word before the cursor and Control+U
1658 deletes everything before the current cursor position. If mysql was
1659 built using libedit, a user who prefers the readline behavior for these
1660 two keys can put the following lines in the .editrc file (creating the
1661 file if necessary):
1662 bind "^W" ed-delete-prev-word
1664 bind "^U" vi-kill-line-prev
1665 To see the current set of key bindings, temporarily put a line that
1667 says only bind at the end of .editrc. mysql shows the bindings when it
1668 starts. Disabling Interactive History
1669 The up-arrow key enables you to recall input lines from current and
1671 previous sessions. In cases where a console is shared, this behavior
1672 may be unsuitable. mysql supports disabling the interactive history
1673 partially or fully, depending on the host platform.
1674 On Windows, the history is stored in memory. Alt+F7 deletes all input
1676 lines stored in memory for the current history buffer. It also deletes
1677 the list of sequential numbers in front of the input lines displayed
1678 with F7 and recalled (by number) with F9. New input lines entered after
1679 you press Alt+F7 repopulate the current history buffer. Clearing the
1680 buffer does not prevent logging to the Windows Event Viewer, if the
1681 --syslog option was used to start mysql. Closing the console window
1682 also clears the current history buffer.
1683 To disable interactive history on Unix, first delete the .mysql_history
1685 file, if it exists (previous entries are recalled otherwise). Then
1686 start mysql with the --histignore="*" option to ignore all new input
1687 lines. To re-enable the recall (and logging) behavior, restart mysql
1688 without the option.
1689 If you prevent the .mysql_history file from being created (see
1691 Controlling the History File) and use --histignore="*" to start the
1692 mysql client, the interactive history recall facility is disabled
1693 fully. Alternatively, if you omit the --histignore option, you can
1694 recall the input lines entered during the current session. Unicode
1695 Support on Windows
1696 Windows provides APIs based on UTF-16LE for reading from and writing to
1698 the console; the mysql client for Windows is able to use these APIs.
1699 The Windows installer creates an item in the MySQL menu named MySQL
1700 command line client - Unicode. This item invokes the mysql client with
1701 properties set to communicate through the console to the MySQL server
1702 using Unicode.
1703 To take advantage of this support manually, run mysql within a console
1705 that uses a compatible Unicode font and set the default character set
1706 to a Unicode character set that is supported for communication with the
1707 server:
1708 1. Open a console window.
1710 2. Go to the console window properties, select the font tab, and
1712 choose Lucida Console or some other compatible Unicode font. This
1713 is necessary because console windows start by default using a DOS
1714 raster font that is inadequate for Unicode.
1715 3. Execute mysql.exe with the --default-character-set=utf8 (or
1717 utf8mb4) option. This option is necessary because utf16le is one of
1718 the character sets that cannot be used as the client character set.
1719 See the section called “Impermissible Client Character Sets”.
1720 With those changes, mysql uses the Windows APIs to communicate with the
1722 console using UTF-16LE, and communicate with the server using UTF-8.
1723 (The menu item mentioned previously sets the font and character set as
1724 just described.)
1725 To avoid those steps each time you run mysql, you can create a shortcut
1727 that invokes mysql.exe. The shortcut should set the console font to
1728 Lucida Console or some other compatible Unicode font, and pass the
1729 --default-character-set=utf8 (or utf8mb4) option to mysql.exe.
1730 Alternatively, create a shortcut that only sets the console font, and
1732 set the character set in the [mysql] group of your my.ini file:
1733 [mysql]
1735 default-character-set=utf8
1736 Displaying Query Results Vertically
1738 Some query results are much more readable when displayed vertically,
1740 instead of in the usual horizontal table format. Queries can be
1741 displayed vertically by terminating the query with \G instead of a
1742 semicolon. For example, longer text values that include newlines often
1743 are much easier to read with vertical output:
1744 mysql> SELECT * FROM mails WHERE LENGTH(txt) < 300 LIMIT 300,1\G
1746 *************************** 1. row ***************************
1747 msg_nro: 3068
1748 date: 2000-03-01 23:29:50
1749 time_zone: +0200
1750 mail_from: Jones
1751 reply: jones@example.com
1752 mail_to: "John Smith" <smith@example.com>
1753 sbj: UTF-8
1754 txt: >>>>> "John" == John Smith writes:
1755 John> Hi. I think this is a good idea. Is anyone familiar
1756 John> with UTF-8 or Unicode? Otherwise, I'll put this on my
1757 John> TODO list and see what happens.
1758 Yes, please do that.
1759 Regards,
1760 Jones
1761 file: inbox-jani-1
1762 hash: 190402944
1763 1 row in set (0.09 sec)
1764 Using Safe-Updates Mode (--safe-updates)
1766 For beginners, a useful startup option is --safe-updates (or
1768 --i-am-a-dummy, which has the same effect). Safe-updates mode is
1769 helpful for cases when you might have issued an UPDATE or DELETE
1770 statement but forgotten the WHERE clause indicating which rows to
1771 modify. Normally, such statements update or delete all rows in the
1772 table. With --safe-updates, you can modify rows only by specifying the
1773 key values that identify them, or a LIMIT clause, or both. This helps
1774 prevent accidents. Safe-updates mode also restricts SELECT statements
1775 that produce (or are estimated to produce) very large result sets.
1776 The --safe-updates option causes mysql to execute the following
1778 statement when it connects to the MySQL server, to set the session
1779 values of the sql_safe_updates, sql_select_limit, and max_join_size
1780 system variables:
1781 SET sql_safe_updates=1, sql_select_limit=1000, max_join_size=1000000;
1783 The SET statement affects statement processing as follows:
1785 · Enabling sql_safe_updates causes UPDATE and DELETE statements to
1787 produce an error if they do not specify a key constraint in the
1788 WHERE clause, or provide a LIMIT clause, or both. For example:
1789 UPDATE tbl_name SET not_key_column=val WHERE key_column=val;
1791 UPDATE tbl_name SET not_key_column=val LIMIT 1;
1792 · Setting sql_select_limit to 1,000 causes the server to limit all
1794 SELECT result sets to 1,000 rows unless the statement includes a
1795 LIMIT clause.
1796 · Setting max_join_size to 1,000,000 causes multiple-table SELECT
1798 statements to produce an error if the server estimates it must
1799 examine more than 1,000,000 row combinations.
1800 To specify result set limits different from 1,000 and 1,000,000, you
1802 can override the defaults by using the --select-limit and
1803 --max-join-size options when you invoke mysql:
1804 mysql --safe-updates --select-limit=500 --max-join-size=10000
1806 It is possible for UPDATE and DELETE statements to produce an error in
1808 safe-updates mode even with a key specified in the WHERE clause, if the
1809 optimizer decides not to use the index on the key column:
1810 · Range access on the index cannot be used if memory usage exceeds
1812 that permitted by the range_optimizer_max_mem_size system variable.
1813 The optimizer then falls back to a table scan. See the section
1814 called “Limiting Memory Use for Range Optimization”.
1815 · If key comparisons require type conversion, the index may not be
1817 used (see Section 8.3.1, “How MySQL Uses Indexes”). Suppose that an
1818 indexed string column c1 is compared to a numeric value using WHERE
1819 c1 = 2222. For such comparisons, the string value is converted to a
1820 number and the operands are compared numerically (see Section 12.3,
1821 “Type Conversion in Expression Evaluation”), preventing use of the
1822 index. If safe-updates mode is enabled, an error occurs.
1823 As of MySQL 8.0.13, safe-updates mode also includes these behaviors:
1825 · EXPLAIN with UPDATE and DELETE statements does not produce
1827 safe-updates errors. This enables use of EXPLAIN plus SHOW WARNINGS
1828 to see why an index is not used, which can be helpful in cases such
1829 as when a range_optimizer_max_mem_size violation or type conversion
1830 occurs and the optimizer does not use an index even though a key
1831 column was specified in the WHERE clause.
1832 · When a safe-updates error occurs, the error message includes the
1834 first diagnostic that was produced, to provide information about
1835 the reason for failure. For example, the message may indicate that
1836 the range_optimizer_max_mem_size value was exceeded or type
1837 conversion occurred, either of which can preclude use of an index.
1838 · For multiple-table deletes and updates, an error is produced with
1840 safe updates enabled only if any target table uses a table scan.
1841 Disabling mysql Auto-Reconnect
1842 If the mysql client loses its connection to the server while sending a
1844 statement, it immediately and automatically tries to reconnect once to
1845 the server and send the statement again. However, even if mysql
1846 succeeds in reconnecting, your first connection has ended and all your
1847 previous session objects and settings are lost: temporary tables, the
1848 autocommit mode, and user-defined and session variables. Also, any
1849 current transaction rolls back. This behavior may be dangerous for you,
1850 as in the following example where the server was shut down and
1851 restarted between the first and second statements without you knowing
1852 it:
1853 mysql> SET @a=1;
1855 Query OK, 0 rows affected (0.05 sec)
1856 mysql> INSERT INTO t VALUES(@a);
1857 ERROR 2006: MySQL server has gone away
1858 No connection. Trying to reconnect...
1859 Connection id: 1
1860 Current database: test
1861 Query OK, 1 row affected (1.30 sec)
1862 mysql> SELECT * FROM t;
1863 +------+
1864 | a |
1865 +------+
1866 | NULL |
1867 +------+
1868 1 row in set (0.05 sec)
1869 The @a user variable has been lost with the connection, and after the
1871 reconnection it is undefined. If it is important to have mysql
1872 terminate with an error if the connection has been lost, you can start
1873 the mysql client with the --skip-reconnect option.
1874 For more information about auto-reconnect and its effect on state
1876 information when a reconnection occurs, see C API Automatic
1877 Reconnection Control[5]. mysql Client Parser Versus Server Parser
1878 The mysql client uses a parser on the client side that is not a
1880 duplicate of the complete parser used by the mysqld server on the
1881 server side. This can lead to differences in treatment of certain
1882 constructs. Examples:
1883 · The server parser treats strings delimited by " characters as
1885 identifiers rather than as plain strings if the ANSI_QUOTES SQL
1886 mode is enabled.
1887 The mysql client parser does not take the ANSI_QUOTES SQL mode into
1889 account. It treats strings delimited by ", ', and ` characters the
1890 same, regardless of whether ANSI_QUOTES is enabled.
1891 · Within /*! ... */ and /*+ ... */ comments, the mysql client parser
1893 interprets short-form mysql commands. The server parser does not
1894 interpret them because these commands have no meaning on the server
1895 side.
1896 If it is desirable for mysql not to interpret short-form commands
1898 within comments, a partial workaround is to use the --binary-mode
1899 option, which causes all mysql commands to be disabled except \C
1900 and \d in noninteractive mode (for input piped to mysql or loaded
1901 using the source command).
1902
1904 Copyright © 1997, 2020, Oracle and/or its affiliates.
1905 This documentation is free software; you can redistribute it and/or
1907 modify it only under the terms of the GNU General Public License as
1908 published by the Free Software Foundation; version 2 of the License.
1909 This documentation is distributed in the hope that it will be useful,
1911 but WITHOUT ANY WARRANTY; without even the implied warranty of
1912 MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
1913 General Public License for more details.
1914 You should have received a copy of the GNU General Public License along
1916 with the program; if not, write to the Free Software Foundation, Inc.,
1917 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA or see
1918 http://www.gnu.org/licenses/.
1919
1922 1. MySQL Shell 8.0 (part of MySQL 8.0)
1923 https://dev.mysql.com/doc/mysql-shell/8.0/en/
1924 2. C API Data Structures
1926 https://dev.mysql.com/doc/c-api/8.0/en/c-api-data-structures.html
1927 3. C API Multiple Statement Execution Support
1929 https://dev.mysql.com/doc/c-api/8.0/en/c-api-multiple-queries.html
1930 4. mysql_change_user()
1932 https://dev.mysql.com/doc/c-api/8.0/en/mysql-change-user.html
1933 5. C API Automatic Reconnection Control
1935 https://dev.mysql.com/doc/c-api/8.0/en/c-api-auto-reconnect.html
1936
1938 For more information, please refer to the MySQL Reference Manual, which
1939 may already be installed locally and which is also available online at
1940 http://dev.mysql.com/doc/.
1941
1943 Oracle Corporation (http://dev.mysql.com/).
1944MySQL 8.0 11/26/2020 MYSQL(1)