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