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

NAME

6       mysql - the MariaDB command-line tool
7

SYNOPSIS

9       mysql [options] db_name
10

DESCRIPTION

12       mysql is a simple SQL shell (with GNU readline capabilities). It
13       supports interactive and non-interactive use. When used interactively,
14       query results are presented in an ASCII-table format. When used
15       non-interactively (for example, as a filter), the result is presented
16       in 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       Using mysql is very easy. Invoke it from the prompt of your command
27       interpreter as follows:
28
29           shell> mysql db_name
30
31       Or:
32
33           shell> mysql --user=user_name --password=your_password db_name
34
35       Then type an SQL statement, end it with “;”, \g, or \G and press Enter.
36
37       Typing Control-C causes mysql to attempt to kill the current statement.
38       If this cannot be done, or Control-C is typed again before the
39       statement is killed, mysql exits.
40
41       You can execute SQL statements in a script file (batch file) like this:
42
43           shell> mysql db_name < script.sql > output.tab
44

MYSQL OPTIONS

46       mysql supports the following options, which can be specified on the
47       command line or in the [mysql], [client], [client-server] or [client-
48       mariadb] option file groups.  mysql also supports the options for
49       processing option files.
50
51       ·   --help, -?, -I
52
53           Display a help message and exit.
54
55       ·   --abort-source-on-error
56
57           Abort 'source filename' operations in case of errors.
58
59       ·   --auto-rehash
60
61           Enable automatic rehashing. This option is on by default, which
62           enables database, table, and column name completion. Use
63           --disable-auto-rehash, --no-auto-rehash,  or --skip-auto-rehash to
64           disable rehashing. That causes mysql to start faster, but you must
65           issue the rehash command if you want to use name completion.
66
67           To complete a name, enter the first part and press Tab. If the name
68           is unambiguous, mysql completes it. Otherwise, you can press Tab
69           again to see the possible names that begin with what you have typed
70           so far. Completion does not occur if there is no default database.
71
72       ·   --auto-vertical-output
73
74           Automatically switch to vertical output mode if the result is wider
75           than the terminal width.
76
77       ·   --batch, -B
78
79           Print results using tab as the column separator, with each row on a
80           new line. With this option, mysql does not use the history file.
81
82           Batch mode results in nontabular output format and escaping of
83           special characters. Escaping may be disabled by using raw mode; see
84           the description for the --raw option.
85
86       ·   --binary-mode
87
88           By default, ASCII '\0' is disallowed and '\r\n' is translated to
89           '\n'. This switch turns off both features, and also turns off
90           parsing of all client commands except \C and DELIMITER, in non-
91           interactive mode (for input piped to mysql or loaded using the
92           'source' command). This is necessary when processing output from
93           mysqlbinlog that may contain blobs.
94
95       ·   --character-sets-dir=path
96
97           The directory where character sets are installed.
98
99       ·   --column-names
100
101           Write column names in results.
102
103       ·   --column-type-info, -m
104
105           Display result set metadata.
106
107       ·   --comments, -c
108
109           Whether to preserve comments in statements sent to the server. The
110           default is --skip-comments (discard comments), enable with
111           --comments (preserve comments).
112
113       ·   --compress, -C
114
115           Compress all information sent between the client and the server if
116           both support compression.
117
118       ·   --connect-timeout=seconds
119
120           Set the number of seconds before connection timeout. (Default value
121           is 0.)
122
123       ·   --database=db_name, -D db_name
124
125           The database to use.
126
127       ·   --debug[=debug_options], -# [debug_options]
128
129           Write a debugging log. A typical debug_options string is
130           ´d:t:o,file_name´. The default is ´d:t:o,/tmp/mysql.trace´.
131
132       ·   --debug-check
133
134           Print some debugging information when the program exits.
135
136       ·   --debug-info, -T
137
138           Prints debugging information and memory and CPU usage statistics
139           when the program exits.
140
141       ·   --default-auth=name
142
143           Default authentication client-side plugin to use.
144
145       ·   --default-character-set=charset_name
146
147           Use charset_name as the default character set for the client and
148           connection.
149
150           A common issue that can occur when the operating system uses utf8
151           or another multi-byte character set is that output from the mysql
152           client is formatted incorrectly, due to the fact that the MariaDB
153           client uses the latin1 character set by default. You can usually
154           fix such issues by using this option to force the client to use the
155           system character set instead.
156
157       ·   --defaults-extra-file=filename
158
159           Set filename as the file to read default options from after the
160           global defaults files has been read.  Must be given as first
161           option.
162
163       ·   --defaults-file=filename
164
165           Set filename as the file to read default options from, override
166           global defaults files. Must be given as first option.
167
168       ·   --defaults-group-suffix=suffix
169
170           In addition to the groups named on the command line, read groups
171           that have the given suffix.
172
173       ·   --delimiter=str
174
175           Set the statement delimiter. The default is the semicolon character
176           (“;”).
177
178       ·   --disable-named-commands
179
180           Disable named commands. Use the \* form only, or use named commands
181           only at the beginning of a line ending with a semicolon (“;”).
182           mysql starts with this option enabled by default. However, even
183           with this option, long-format commands still work from the first
184           line. See the section called “MYSQL COMMANDS”.
185
186       ·   --execute=statement, -e statement
187
188           Execute the statement and quit. Disables --force and history file.
189           The default output format is like that produced with --batch.
190
191       ·   --force, -f
192
193           Continue even if an SQL error occurs. Sets --abort-source-on-error
194           to 0.
195
196       ·   --host=host_name, -h host_name
197
198           Connect to the MariaDB server on the given host.
199
200       ·   --html, -H
201
202           Produce HTML output.
203
204       ·   --ignore-spaces, -i
205
206           Ignore spaces after function names. Allows one to have spaces
207           (including tab characters and new line characters) between function
208           name and '('. The drawback is that this causes built in functions
209           to become reserved words.
210
211       ·   --init-command=str
212
213           SQL Command to execute when connecting to the MariaDB server. Will
214           automatically be re-executed when reconnecting.
215
216       ·   --line-numbers
217
218           Write line numbers for errors. Disable this with
219           --skip-line-numbers.
220
221       ·   --local-infile[={0|1}]
222
223           Enable or disable LOCAL capability for LOAD DATA INFILE. With no
224           value, the option enables LOCAL. The option may be given as
225           --local-infile=0 or --local-infile=1 to explicitly disable or
226           enable LOCAL. Enabling LOCAL has no effect if the server does not
227           also support it.
228
229       ·   --max-allowed-packet=num
230
231           Set the maximum packet length to send to or receive from the
232           server. (Default value is 16MB, largest 1GB.)
233
234       ·   --max-join-size=num
235
236           Set the automatic limit for rows in a join when using
237           --safe-updates. (Default value is 1,000,000.)
238
239       ·   --named-commands, -G
240
241           Enable named mysql commands. Long-format commands are allowed, not
242           just short-format commands. For example, quit and \q both are
243           recognized. Use --skip-named-commands to disable named commands.
244           See the section called “MYSQL COMMANDS”. Disabled by default.
245
246       ··   --net-buffer-length=size
247
248           Set the buffer size for TCP/IP and socket communication. (Default
249           value is 16KB.)
250
251       ·   --no-auto-rehash, -A
252
253           This has the same effect as --skip-auto-rehash. See the description
254           for --auto-rehash.
255
256       ·   --no-beep, -b
257
258           Do not beep when errors occur.
259
260       ·   --no-defaults
261
262           Do not read default options from any option file. This must be
263           given as the first argument.
264
265       ·   --one-database, -o
266
267           Ignore statements except those those that occur while the default
268           database is the one named on the command line. This filtering is
269           limited, and based only on USE statements. This is useful for
270           skipping updates to other databases in the binary log.
271
272       ·   --pager[=command]
273
274           Use the given command for paging query output. If the command is
275           omitted, the default pager is the value of your PAGER environment
276           variable. Valid pagers are less, more, cat [> filename], and so
277           forth. This option works only on Unix and only in interactive mode.
278           To disable paging, use --skip-pager.  the section called “MYSQL
279           COMMANDS”, discusses output paging further.
280
281       ·   --password[=password], -p[password]
282
283           The password to use when connecting to the server. If you use the
284           short option form (-p), you cannot have a space between the option
285           and the password. If you omit the password value following the
286           --password or -p option on the command line, mysql prompts for one.
287
288           Specifying a password on the command line should be considered
289           insecure. You can use an option file to avoid giving the password
290           on the command line.
291
292       ·   --pipe, -W
293
294           On Windows, connect to the server via a named pipe. This option
295           applies only if the server supports named-pipe connections.
296
297       ·   --plugin-dir=dir_name
298
299           Directory for client-side plugins.
300
301       ·   --port=port_num, -P port_num
302
303           The TCP/IP port number to use for the connection or 0 for default
304           to, in order of preference, my.cnf, $MYSQL_TCP_PORT, /etc/services,
305           built-in default (3306).
306
307       ·   --print-defaults
308
309           Print the program argument list and exit. This must be given as the
310           first argument.
311
312       ·   --progress-reports
313
314           Get progress reports for long running commands (such as ALTER
315           TABLE). (Defaults to on; use --skip-progress-reports to disable.)
316
317       ·   --prompt=format_str
318
319           Set the prompt to the specified format. The special sequences that
320           the prompt can contain are described in the section called “MYSQL
321           COMMANDS”.
322
323       ·   --protocol={TCP|SOCKET|PIPE|MEMORY}
324
325           The connection protocol to use for connecting to the server. It is
326           useful when the other connection parameters normally would cause a
327           protocol to be used other than the one you want.
328
329       ·   --quick, -q
330
331           Do not cache each query result, print each row as it is received.
332           This may slow down the server if the output is suspended. With this
333           option, mysql does not use the history file.
334
335       ·   --raw, -r
336
337           For tabular output, the “boxing” around columns enables one column
338           value to be distinguished from another. For nontabular output (such
339           as is produced in batch mode or when the --batch or --silent option
340           is given), special characters are escaped in the output so they can
341           be identified easily. Newline, tab, NUL, and backslash are written
342           as \n, \t, \0, and \\. The --raw option disables this character
343           escaping.
344
345           The following example demonstrates tabular versus nontabular output
346           and the use of raw mode to disable escaping:
347
348               % mysql
349               mysql> SELECT CHAR(92);
350               +----------+
351               | CHAR(92) |
352               +----------+
353               | \        |
354               +----------+
355               % mysql -s
356               mysql> SELECT CHAR(92);
357               CHAR(92)
358               \\
359               % mysql -s -r
360               mysql> SELECT CHAR(92);
361               CHAR(92)
362               \
363
364       ·   --reconnect
365
366           If the connection to the server is lost, automatically try to
367           reconnect. A single reconnect attempt is made each time the
368           connection is lost. Enabled by default, to disable use
369           --skip-reconnect or --disable-reconnect.
370
371       ·   --safe-updates, --i-am-a-dummy, -U
372
373           Allow only those UPDATE and DELETE statements that specify which
374           rows to modify by using key values. If you have set this option in
375           an option file, you can override it by using --safe-updates on the
376           command line. See the section called “MYSQL TIPS”, for more
377           information about this option.
378
379       ·   --secure-auth
380
381           Do not send passwords to the server in old (pre-4.1.1) format. This
382           prevents connections except for servers that use the newer password
383           format.
384
385       ·   --select-limit=limit
386
387           Set automatic limit for SELECT when using --safe-updates. (Default
388           value is 1,000.)
389
390       ·   --server-arg=name
391
392           Send name as a parameter to the embedded server.
393
394       ·   --show-warnings
395
396           Cause warnings to be shown after each statement if there are any.
397           This option applies to interactive and batch mode.
398
399       ·   --sigint-ignore
400
401           Ignore SIGINT signals (typically the result of typing Control-C).
402
403       ·   --silent, -s
404
405           Silent mode. Produce less output. This option can be given multiple
406           times to produce less and less output.
407
408           This option results in nontabular output format and escaping of
409           special characters. Escaping may be disabled by using raw mode; see
410           the description for the --raw option.
411
412       ·   --skip-auto-rehash
413
414           Disable automatic rehashing. Synonym for --disable-auto-rehash.
415
416       ·   --skip-column-names, -N
417
418           Do not write column names in results.
419
420       ·   --skip-line-numbers, -L
421
422           Do not write line numbers for errors. Useful when you want to
423           compare result files that include error messages.
424
425       ·   --socket=path, -S path
426
427           For connections to localhost, the Unix socket file to use, or, on
428           Windows, the name of the named pipe to use.
429
430       ·   --ssl
431
432           Enable SSL for connection (automatically enabled with other flags).
433           Disable with --skip-ssl.
434
435       ·   --ssl-ca=name
436
437           CA file in PEM format (check OpenSSL docs, implies --ssl).
438
439       ·   --ssl-capath=name
440
441           CA directory (check OpenSSL docs, implies --ssl).
442
443       ·   --ssl-cert=name
444
445           X509 cert in PEM format (check OpenSSL docs, implies --ssl).
446
447       ·   --ssl-cipher=name
448
449           SSL cipher to use (check OpenSSL docs, implies --ssl).
450
451       ·   --ssl-key=name
452
453           X509 key in PEM format (check OpenSSL docs, implies --ssl).
454
455       ·   --ssl-crl=name
456
457           Certificate revocation list (check OpenSSL docs, implies --ssl).
458
459       ·   --ssl-crlpath=name
460
461           Certificate revocation list path (check OpenSSL docs, implies
462           --ssl).
463
464       ·   --ssl-verify-server-cert
465
466           Verify server's "Common Name" in its cert against hostname used
467           when connecting. This option is disabled by default.
468
469       ·   --table, -t
470
471           Display output in table format. This is the default for interactive
472           use, but can be used to produce table output in batch mode.
473
474       ·   --tee=file_name
475
476           Append a copy of output to the given file. This option works only
477           in interactive mode.  the section called “MYSQL COMMANDS”,
478           discusses tee files further.
479
480       ·   --unbuffered, -n
481
482           Flush the buffer after each query.
483
484       ·   --user=user_name, -u user_name
485
486           The MariaDB user name to use when connecting to the server.
487
488       ·   --verbose, -v
489
490           Verbose mode. Produce more output about what the program does. This
491           option can be given multiple times to produce more and more output.
492           (For example, -v -v -v produces table output format even in batch
493           mode.)
494
495       ·   --version, -V
496
497           Display version information and exit.
498
499       ·   --vertical, -E
500
501           Print query output rows vertically (one line per column value).
502           Without this option, you can specify vertical output for individual
503           statements by terminating them with \G.
504
505       ·   --wait, -w
506
507           If the connection cannot be established, wait and retry instead of
508           aborting.
509
510       ·   --xml, -X
511
512           Produce XML output.  The output when --xml is used with mysql
513           matches that of mysqldump --xml. See mysqldump(1) for details.
514
515           The XML output also uses an XML namespace, as shown here:
516
517               shell> mysql --xml -uroot -e "SHOW VARIABLES LIKE ´version%´"
518               <?xml version="1.0"?>
519               <resultset statement="SHOW VARIABLES LIKE ´version%´" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
520               <row>
521               <field name="Variable_name">version</field>
522               <field name="Value">5.0.40-debug</field>
523               </row>
524               <row>
525               <field name="Variable_name">version_comment</field>
526               <field name="Value">Source distribution</field>
527               </row>
528               <row>
529               <field name="Variable_name">version_compile_machine</field>
530               <field name="Value">i686</field>
531               </row>
532               <row>
533               <field name="Variable_name">version_compile_os</field>
534               <field name="Value">suse-linux-gnu</field>
535               </row>
536               </resultset>
537
538           You can also set the following variables by using --var_name=value.
539
540           ·   connect_timeout
541
542               The number of seconds before connection timeout. (Default value
543               is 0.)
544
545           ·   max_allowed_packet
546
547               The maximum packet length to send to or receive from the
548               server. (Default value is 16MB.)
549
550           ·   max_join_size
551
552               The automatic limit for rows in a join when using
553               --safe-updates. (Default value is 1,000,000.)
554
555           ·   net_buffer_length
556
557               The buffer size for TCP/IP and socket communication. (Default
558               value is 16KB.)
559
560           ·   select_limit
561
562               The automatic limit for SELECT statements when using
563               --safe-updates. (Default value is 1,000.)
564
565           On Unix, the mysql client writes a record of executed statements to
566           a history file. By default, this file is named .mysql_history and
567           is created in your home directory. To specify a different file, set
568           the value of the MYSQL_HISTFILE environment variable.
569
570           The .mysql_history should be protected with a restrictive access
571           mode because sensitive information might be written to it, such as
572           the text of SQL statements that contain passwords.
573
574           If you do not want to maintain a history file, first remove
575           .mysql_history if it exists, and then use either of the following
576           techniques:
577
578           ·   Set the MYSQL_HISTFILE variable to /dev/null. To cause this
579               setting to take effect each time you log in, put the setting in
580               one of your shell´s startup files.
581
582           ·   Create .mysql_history as a symbolic link to /dev/null:
583
584                   shell> ln -s /dev/null $HOME/.mysql_history
585
586               You need do this only once.
587

MYSQL COMMANDS

589       mysql sends each SQL statement that you issue to the server to be
590       executed. There is also a set of commands that mysql itself interprets.
591       For a list of these commands, type help or \h at the mysql> prompt:
592
593           mysql> help
594           List of all MySQL commands:
595           Note that all text commands must be first on line and end with ´;´
596           ?         (\?) Synonym for `help´.
597           clear     (\c) Clear command.
598           connect   (\r) Reconnect to the server. Optional arguments are db and host.
599           delimiter (\d) Set statement delimiter.
600           edit      (\e) Edit command with $EDITOR.
601           ego       (\G) Send command to mysql server, display result vertically.
602           exit      (\q) Exit mysql. Same as quit.
603           go        (\g) Send command to mysql server.
604           help      (\h) Display this help.
605           nopager   (\n) Disable pager, print to stdout.
606           notee     (\t) Don´t write into outfile.
607           pager     (\P) Set PAGER [to_pager]. Print the query results via PAGER.
608           print     (\p) Print current command.
609           prompt    (\R) Change your mysql prompt.
610           quit      (\q) Quit mysql.
611           rehash    (\#) Rebuild completion hash.
612           source    (\.) Execute an SQL script file. Takes a file name as an argument.
613           status    (\s) Get status information from the server.
614           system    (\!) Execute a system shell command.
615           tee       (\T) Set outfile [to_outfile]. Append everything into given
616                          outfile.
617           use       (\u) Use another database. Takes database name as argument.
618           charset   (\C) Switch to another charset. Might be needed for processing
619                          binlog with multi-byte charsets.
620           warnings  (\W) Show warnings after every statement.
621           nowarning (\w) Don´t show warnings after every statement.
622           For server side help, type ´help contents´
623
624       Each command has both a long and short form. The long form is not case
625       sensitive; the short form is. The long form can be followed by an
626       optional semicolon terminator, but the short form should not.
627
628       The use of short-form commands within multi-line /* ... */ comments is
629       not supported.
630
631       ·   help [arg], \h [arg], \? [arg], ? [arg]
632
633           Display a help message listing the available mysql commands.
634
635           If you provide an argument to the help command, mysql uses it as a
636           search string to access server-side help. For more information, see
637           the section called “MYSQL SERVER-SIDE HELP”.
638
639       ·   charset charset_name, \C charset_name
640
641           Change the default character set and issue a SET NAMES statement.
642           This enables the character set to remain synchronized on the client
643           and server if mysql is run with auto-reconnect enabled (which is
644           not recommended), because the specified character set is used for
645           reconnects.
646
647       ·   clear, \c
648
649           Clear the current input. Use this if you change your mind about
650           executing the statement that you are entering.
651
652       ·   connect [db_name host_name]], \r [db_name host_name]]
653
654           Reconnect to the server. The optional database name and host name
655           arguments may be given to specify the default database or the host
656           where the server is running. If omitted, the current values are
657           used.
658
659       ·   delimiter str, \d str
660
661           Change the string that mysql interprets as the separator between
662           SQL statements. The default is the semicolon character (“;”).
663
664           The delimiter can be specified as an unquoted or quoted argument.
665           Quoting can be done with either single quote (´) or douple quote
666           (") characters. To include a quote within a quoted string, either
667           quote the string with the other quote character or escape the quote
668           with a backslash (“\”) character. Backslash should be avoided
669           outside of quoted strings because it is the escape character for
670           MariaDB. For an unquoted argument, the delmiter is read up to the
671           first space or end of line. For a quoted argument, the delimiter is
672           read up to the matching quote on the line.
673
674           When the delimiter recognized by mysql is set to something other
675           than the default of “;”, instances of that character are sent to
676           the server without interpretation. However, the server itself still
677           interprets “;” as a statement delimiter and processes statements
678           accordingly. This behavior on the server side comes into play for
679           multiple-statement execution, and for parsing the body of stored
680           procedures and functions, triggers, and events.
681
682       ·   edit, \e
683
684           Edit the current input statement.  mysql checks the values of the
685           EDITOR and VISUAL environment variables to determine which editor
686           to use. The default editor is vi if neither variable is set.
687
688           The edit command works only in Unix.
689
690       ·   ego, \G
691
692           Send the current statement to the server to be executed and display
693           the result using vertical format.
694
695       ·   exit, \q
696
697           Exit mysql.
698
699       ·   go, \g
700
701           Send the current statement to the server to be executed.
702
703       ·   nopager, \n
704
705           Disable output paging. See the description for pager.
706
707           The nopager command works only in Unix.
708
709       ·   notee, \t
710
711           Disable output copying to the tee file. See the description for
712           tee.
713
714       ·   nowarning, \w
715
716           Enable display of warnings after each statement.
717
718       ·   pager [command], \P [command]
719
720           Enable output paging. By using the --pager option when you invoke
721           mysql, it is possible to browse or search query results in
722           interactive mode with Unix programs such as less, more, or any
723           other similar program. If you specify no value for the option,
724           mysql checks the value of the PAGER environment variable and sets
725           the pager to that. Pager functionality works only in interactive
726           mode.
727
728           Output paging can be enabled interactively with the pager command
729           and disabled with nopager. The command takes an optional argument;
730           if given, the paging program is set to that. With no argument, the
731           pager is set to the pager that was set on the command line, or
732           stdout if no pager was specified.
733
734           Output paging works only in Unix because it uses the popen()
735           function, which does not exist on Windows. For Windows, the tee
736           option can be used instead to save query output, although it is not
737           as convenient as pager for browsing output in some situations.
738
739       ·   print, \p
740
741           Print the current input statement without executing it.
742
743       ·   prompt [str], \R [str]
744
745           Reconfigure the mysql prompt to the given string. The special
746           character sequences that can be used in the prompt are described
747           later in this section.
748
749           If you specify the prompt command with no argument, mysql resets
750           the prompt to the default of mysql>.
751
752       ·   quit, \q
753
754           Exit mysql.
755
756       ·   rehash, \#
757
758           Rebuild the completion hash that enables database, table, and
759           column name completion while you are entering statements. (See the
760           description for the --auto-rehash option.)
761
762       ·   source file_name, \. file_name
763
764           Read the named file and executes the statements contained therein.
765           On Windows, you can specify path name separators as / or \\.
766
767       ·   status, \s
768
769           Provide status information about the connection and the server you
770           are using. If you are running in --safe-updates mode, status also
771           prints the values for the mysql variables that affect your queries.
772
773       ·   system command, \! command
774
775           Execute the given command using your default command interpreter.
776
777           The system command works only in Unix.
778
779       ·   tee [file_name], \T [file_name]
780
781           By using the --tee option when you invoke mysql, you can log
782           statements and their output. All the data displayed on the screen
783           is appended into a given file. This can be very useful for
784           debugging purposes also.  mysql flushes results to the file after
785           each statement, just before it prints its next prompt. Tee
786           functionality works only in interactive mode.
787
788           You can enable this feature interactively with the tee command.
789           Without a parameter, the previous file is used. The tee file can be
790           disabled with the notee command. Executing tee again re-enables
791           logging.
792
793       ·   use db_name, \u db_name
794
795           Use db_name as the default database.
796
797       ·   warnings, \W
798
799           Enable display of warnings after each statement (if there are any).
800
801       Here are a few tips about the pager command:
802
803       ·   You can use it to write to a file and the results go only to the
804           file:
805
806               mysql> pager cat > /tmp/log.txt
807
808           You can also pass any options for the program that you want to use
809           as your pager:
810
811               mysql> pager less -n -i -S
812
813       ·   In the preceding example, note the -S option. You may find it very
814           useful for browsing wide query results. Sometimes a very wide
815           result set is difficult to read on the screen. The -S option to
816           less can make the result set much more readable because you can
817           scroll it horizontally using the left-arrow and right-arrow keys.
818           You can also use -S interactively within less to switch the
819           horizontal-browse mode on and off. For more information, read the
820           less manual page:
821
822               shell> man less
823
824       ·   The -F and -X options may be used with less to cause it to exit if
825           output fits on one screen, which is convenient when no scrolling is
826           necessary:
827
828               mysql> pager less -n -i -S -F -X
829
830       ·   You can specify very complex pager commands for handling query
831           output:
832
833               mysql> pager cat | tee /dr1/tmp/res.txt \
834                         | tee /dr2/tmp/res2.txt | less -n -i -S
835
836           In this example, the command would send query results to two files
837           in two different directories on two different file systems mounted
838           on /dr1 and /dr2, yet still display the results onscreen via less.
839
840       You can also combine the tee and pager functions. Have a tee file
841       enabled and pager set to less, and you are able to browse the results
842       using the less program and still have everything appended into a file
843       the same time. The difference between the Unix tee used with the pager
844       command and the mysql built-in tee command is that the built-in tee
845       works even if you do not have the Unix tee available. The built-in tee
846       also logs everything that is printed on the screen, whereas the Unix
847       tee used with pager does not log quite that much. Additionally, tee
848       file logging can be turned on and off interactively from within mysql.
849       This is useful when you want to log some queries to a file, but not
850       others.
851
852       The prompt command reconfigures the default mysql> prompt. The string
853       for defining the prompt can contain the following special sequences.
854
855       ┌───────┬────────────────────────────┐
856Option Description                
857       ├───────┼────────────────────────────┤
858       │\c     │ A counter that increments  │
859       │       │ for each statement you     │
860       │       │ issue                      │
861       ├───────┼────────────────────────────┤
862       │\D     │ The full current date      │
863       ├───────┼────────────────────────────┤
864       │\d     │ The default database       │
865       ├───────┼────────────────────────────┤
866       │\h     │ The server host            │
867       ├───────┼────────────────────────────┤
868       │\l     │ The current delimiter (new │
869       │       │ in 5.1.12)                 │
870       ├───────┼────────────────────────────┤
871       │\m     │ Minutes of the current     │
872       │       │ time                       │
873       ├───────┼────────────────────────────┤
874       │\n     │ A newline character        │
875       ├───────┼────────────────────────────┤
876       │\O     │ The current month in       │
877       │       │ three-letter format (Jan,  │
878       │       │ Feb, ...)                  │
879       ├───────┼────────────────────────────┤
880       │\o     │ The current month in       │
881       │       │ numeric format             │
882       ├───────┼────────────────────────────┤
883       │\P     │ am/pm                      │
884       ├───────┼────────────────────────────┤
885       │\p     │ The current TCP/IP port or │
886       │       │ socket file                │
887       ├───────┼────────────────────────────┤
888       │\R     │ The current time, in       │
889       │       │ 24-hour military time      │
890       │       │ (0–23)                     │
891       ├───────┼────────────────────────────┤
892       │\r     │ The current time, standard │
893       │       │ 12-hour time (1–12)        │
894       ├───────┼────────────────────────────┤
895       │\S     │ Semicolon                  │
896       ├───────┼────────────────────────────┤
897       │\s     │ Seconds of the current     │
898       │       │ time                       │
899       ├───────┼────────────────────────────┤
900       │\t     │ A tab character            │
901       ├───────┼────────────────────────────┤
902       │\U     │                            │
903       │       │        Your full           │
904       │       │        user_name@host_name
905       │       │        account name        │
906       ├───────┼────────────────────────────┤
907       │\u     │ Your user name             │
908       ├───────┼────────────────────────────┤
909       │\v     │ The server version         │
910       ├───────┼────────────────────────────┤
911       │\w     │ The current day of the     │
912       │       │ week in three-letter       │
913       │       │ format (Mon, Tue, ...)     │
914       ├───────┼────────────────────────────┤
915       │\Y     │ The current year, four     │
916       │       │ digits                     │
917       ├───────┼────────────────────────────┤
918       │\y     │ The current year, two      │
919       │       │ digits                     │
920       ├───────┼────────────────────────────┤
921       │\_     │ A space                    │
922       ├───────┼────────────────────────────┤
923       │\      │ A space (a space follows   │
924       │       │ the backslash)             │
925       ├───────┼────────────────────────────┤
926       │\´     │ Single quote               │
927       ├───────┼────────────────────────────┤
928       │\"     │ Double quote               │
929       ├───────┼────────────────────────────┤
930       │\\     │ A literal “\” backslash    │
931       │       │ character                  │
932       ├───────┼────────────────────────────┤
933       │\x     │                            │
934       │       │        x, for any “x” not  │
935       │       │        listed above        │
936       └───────┴────────────────────────────┘
937
938       You can set the prompt in several ways:
939
940       ·   Use an environment variable.  You can set the MYSQL_PS1 environment
941           variable to a prompt string. For example:
942
943               shell> export MYSQL_PS1="(\u@\h) [\d]> "
944
945       ·   Use a command-line option.  You can set the --prompt option on the
946           command line to mysql. For example:
947
948               shell> mysql --prompt="(\u@\h) [\d]> "
949               (user@host) [database]>
950
951       ·   Use an option file.  You can set the prompt option in the [mysql]
952           group of any MariaDB option file, such as /etc/my.cnf or the
953           .my.cnf file in your home directory. For example:
954
955               [mysql]
956               prompt=(\\u@\\h) [\\d]>\\_
957
958           In this example, note that the backslashes are doubled. If you set
959           the prompt using the prompt option in an option file, it is
960           advisable to double the backslashes when using the special prompt
961           options. There is some overlap in the set of allowable prompt
962           options and the set of special escape sequences that are recognized
963           in option files. The overlap may cause you problems if you use
964           single backslashes. For example, \s is interpreted as a space
965           rather than as the current seconds value. The following example
966           shows how to define a prompt within an option file to include the
967           current time in HH:MM:SS> format:
968
969               [mysql]
970               prompt="\\r:\\m:\\s> "
971
972       ·   Set the prompt interactively.  You can change your prompt
973           interactively by using the prompt (or \R) command. For example:
974
975               mysql> prompt (\u@\h) [\d]>\_
976               PROMPT set to ´(\u@\h) [\d]>\_´
977               (user@host) [database]>
978               (user@host) [database]> prompt
979               Returning to default PROMPT of mysql>
980               mysql>
981

MYSQL SERVER-SIDE HELP

983           mysql> help search_string
984
985       If you provide an argument to the help command, mysql uses it as a
986       search string to access server-side help. The proper operation of this
987       command requires that the help tables in the mysql database be
988       initialized with help topic information.
989
990       If there is no match for the search string, the search fails:
991
992           mysql> help me
993           Nothing found
994           Please try to run ´help contents´ for a list of all accessible topics
995
996       Use help contents to see a list of the help categories:
997
998           mysql> help contents
999           You asked for help about help category: "Contents"
1000           For more information, type ´help <item>´, where <item> is one of the
1001           following categories:
1002              Account Management
1003              Administration
1004              Data Definition
1005              Data Manipulation
1006              Data Types
1007              Functions
1008              Functions and Modifiers for Use with GROUP BY
1009              Geographic Features
1010              Language Structure
1011              Plugins
1012              Storage Engines
1013              Stored Routines
1014              Table Maintenance
1015              Transactions
1016              Triggers
1017
1018       If the search string matches multiple items, mysql shows a list of
1019       matching topics:
1020
1021           mysql> help logs
1022           Many help items for your request exist.
1023           To make a more specific request, please type ´help <item>´,
1024           where <item> is one of the following topics:
1025              SHOW
1026              SHOW BINARY LOGS
1027              SHOW ENGINE
1028              SHOW LOGS
1029
1030       Use a topic as the search string to see the help entry for that topic:
1031
1032           mysql> help show binary logs
1033           Name: ´SHOW BINARY LOGS´
1034           Description:
1035           Syntax:
1036           SHOW BINARY LOGS
1037           SHOW MASTER LOGS
1038           Lists the binary log files on the server. This statement is used as
1039           part of the procedure described in [purge-binary-logs], that shows how
1040           to determine which logs can be purged.
1041           mysql> SHOW BINARY LOGS;
1042           +---------------+-----------+
1043           | Log_name      | File_size |
1044           +---------------+-----------+
1045           | binlog.000015 |    724935 |
1046           | binlog.000016 |    733481 |
1047           +---------------+-----------+
1048

EXECUTING SQL STATEMENTS FROM A TEXT FILE

1050       The mysql client typically is used interactively, like this:
1051
1052           shell> mysql db_name
1053
1054       However, it is also possible to put your SQL statements in a file and
1055       then tell mysql to read its input from that file. To do so, create a
1056       text file text_file that contains the statements you wish to execute.
1057       Then invoke mysql as shown here:
1058
1059           shell> mysql db_name < text_file
1060
1061       If you place a USE db_name statement as the first statement in the
1062       file, it is unnecessary to specify the database name on the command
1063       line:
1064
1065           shell> mysql < text_file
1066
1067       If you are already running mysql, you can execute an SQL script file
1068       using the source command or \.  command:
1069
1070           mysql> source file_name
1071           mysql> \. file_name
1072
1073       Sometimes you may want your script to display progress information to
1074       the user. For this you can insert statements like this:
1075
1076           SELECT ´<info_to_display>´ AS ´ ´;
1077
1078       The statement shown outputs <info_to_display>.
1079
1080       You can also invoke mysql with the --verbose option, which causes each
1081       statement to be displayed before the result that it produces.
1082
1083       mysql ignores Unicode byte order mark (BOM) characters at the beginning
1084       of input files. Presence of a BOM does not cause mysql to change its
1085       default character set. To do that, invoke mysql with an option such as
1086       --default-character-set=utf8.
1087

MYSQL TIPS

1089       This section describes some techniques that can help you use mysql more
1090       effectively.
1091
1092   Displaying Query Results Vertically
1093       Some query results are much more readable when displayed vertically,
1094       instead of in the usual horizontal table format. Queries can be
1095       displayed vertically by terminating the query with \G instead of a
1096       semicolon. For example, longer text values that include newlines often
1097       are much easier to read with vertical output:
1098
1099           mysql> SELECT * FROM mails WHERE LENGTH(txt) < 300 LIMIT 300,1\G
1100           *************************** 1. row ***************************
1101             msg_nro: 3068
1102                date: 2000-03-01 23:29:50
1103           time_zone: +0200
1104           mail_from: Monty
1105               reply: monty@no.spam.com
1106             mail_to: "Thimble Smith" <tim@no.spam.com>
1107                 sbj: UTF-8
1108                 txt: >>>>> "Thimble" == Thimble Smith writes:
1109           Thimble> Hi.  I think this is a good idea.  Is anyone familiar
1110           Thimble> with UTF-8 or Unicode? Otherwise, I´ll put this on my
1111           Thimble> TODO list and see what happens.
1112           Yes, please do that.
1113           Regards,
1114           Monty
1115                file: inbox-jani-1
1116                hash: 190402944
1117           1 row in set (0.09 sec)
1118
1119   Using the --safe-updates Option
1120       For beginners, a useful startup option is --safe-updates (or
1121       --i-am-a-dummy, which has the same effect). It is helpful for cases
1122       when you might have issued a DELETE FROM tbl_name statement but
1123       forgotten the WHERE clause. Normally, such a statement deletes all rows
1124       from the table. With --safe-updates, you can delete rows only by
1125       specifying the key values that identify them. This helps prevent
1126       accidents.
1127
1128       When you use the --safe-updates option, mysql issues the following
1129       statement when it connects to the MariaDB server:
1130
1131           SET sql_safe_updates=1, sql_select_limit=1000, sql_max_join_size=1000000;
1132
1133       The SET statement has the following effects:
1134
1135       ·   You are not allowed to execute an UPDATE or DELETE statement unless
1136           you specify a key constraint in the WHERE clause or provide a LIMIT
1137           clause (or both). For example:
1138
1139               UPDATE tbl_name SET not_key_column=val WHERE key_column=val;
1140               UPDATE tbl_name SET not_key_column=val LIMIT 1;
1141
1142       ·   The server limits all large SELECT results to 1,000 rows unless the
1143           statement includes a LIMIT clause.
1144
1145       ·   The server aborts multiple-table SELECT statements that probably
1146           need to examine more than 1,000,000 row combinations.
1147
1148       To specify limits different from 1,000 and 1,000,000, you can override
1149       the defaults by using the --select-limit and --max-join-size options:
1150
1151           shell> mysql --safe-updates --select-limit=500 --max-join-size=10000
1152
1153   Disabling mysql Auto-Reconnect
1154       If the mysql client loses its connection to the server while sending a
1155       statement, it immediately and automatically tries to reconnect once to
1156       the server and send the statement again. However, even if mysql
1157       succeeds in reconnecting, your first connection has ended and all your
1158       previous session objects and settings are lost: temporary tables, the
1159       autocommit mode, and user-defined and session variables. Also, any
1160       current transaction rolls back. This behavior may be dangerous for you,
1161       as in the following example where the server was shut down and
1162       restarted between the first and second statements without you knowing
1163       it:
1164
1165           mysql> SET @a=1;
1166           Query OK, 0 rows affected (0.05 sec)
1167           mysql> INSERT INTO t VALUES(@a);
1168           ERROR 2006: MySQL server has gone away
1169           No connection. Trying to reconnect...
1170           Connection id:    1
1171           Current database: test
1172           Query OK, 1 row affected (1.30 sec)
1173           mysql> SELECT * FROM t;
1174           +------+
1175           | a    |
1176           +------+
1177           | NULL |
1178           +------+
1179           1 row in set (0.05 sec)
1180
1181       The @a user variable has been lost with the connection, and after the
1182       reconnection it is undefined. If it is important to have mysql
1183       terminate with an error if the connection has been lost, you can start
1184       the mysql client with the --skip-reconnect option.
1185
1187       Copyright 2007-2008 MySQL AB, 2008-2010 Sun Microsystems, Inc.,
1188       2010-2015 MariaDB Foundation
1189
1190       This documentation is free software; you can redistribute it and/or
1191       modify it only under the terms of the GNU General Public License as
1192       published by the Free Software Foundation; version 2 of the License.
1193
1194       This documentation is distributed in the hope that it will be useful,
1195       but WITHOUT ANY WARRANTY; without even the implied warranty of
1196       MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
1197       General Public License for more details.
1198
1199       You should have received a copy of the GNU General Public License along
1200       with the program; if not, write to the Free Software Foundation, Inc.,
1201       51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA or see
1202       http://www.gnu.org/licenses/.
1203
1204

NOTES

1206        1. Bug#25946
1207           http://bugs.mysql.com/bug.php?id=25946
1208

SEE ALSO

1210       For more information, please refer to the MariaDB Knowledge Base,
1211       available online at https://mariadb.com/kb/
1212

AUTHOR

1214       MariaDB Foundation (http://www.mariadb.org/).
1215
1216
1217
1218MariaDB 10.3                      9 May 2017                          MYSQL(1)
Impressum