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