1MYSQL(1)                     MySQL Database System                    MYSQL(1)
2
3
4

NAME

6       mysql - the MySQL command-line client
7

SYNOPSIS

9       mysql [options] db_name
10

DESCRIPTION

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

MYSQL CLIENT OPTIONS

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

MYSQL CLIENT COMMANDS

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       ┌───────────────────────────┬────────────────────────────┐
1123Option                     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

MYSQL CLIENT LOGGING

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

MYSQL CLIENT SERVER-SIDE HELP

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

EXECUTING SQL STATEMENTS FROM A TEXT FILE

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

MYSQL CLIENT TIPS

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

NOTES

1834        1. MySQL Shell 8.0 (part of MySQL 8.0)
1835           https://dev.mysql.com/doc/mysql-shell/8.0/en/
1836

SEE ALSO

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

AUTHOR

1843       Oracle Corporation (http://dev.mysql.com/).
1844
1845
1846
1847MySQL 8.0                         09/06/2019                          MYSQL(1)
Impressum