mysql sends each SQL statement that you
issue to the server to be executed. There is also a set of
commands that mysql itself interprets. For
a list of these commands, type
\h at the
List of all MySQL commands:
Note that all text commands must be first on line and end with ';'
? (\?) Synonym for `help'.
clear (\c) Clear command.
connect (\r) Reconnect to the server. Optional arguments are db and host.
delimiter (\d) Set statement delimiter. NOTE: Takes the rest of the line as
edit (\e) Edit command with $EDITOR.
ego (\G) Send command to mysql server, display result vertically.
exit (\q) Exit mysql. Same as quit.
go (\g) Send command to mysql server.
help (\h) Display this help.
nopager (\n) Disable pager, print to stdout.
notee (\t) Don't write into outfile.
pager (\P) Set PAGER [to_pager]. Print the query results via PAGER.
print (\p) Print current command.
prompt (\R) Change your mysql prompt.
quit (\q) Quit mysql.
rehash (\#) Rebuild completion hash.
source (\.) Execute an SQL script file. Takes a file name as an argument.
status (\s) Get status information from the server.
system (\!) Execute a system shell command.
tee (\T) Set outfile [to_outfile]. Append everything into given
use (\u) Use another database. Takes database name as argument.
warnings (\W) Show warnings after every statement.
nowarning (\w) Don't show warnings after every statement.
Each command has both a long and short form. The long form is
not case sensitive; the short form is. The long form can be
followed by an optional semicolon terminator, but the short
form should not.
delimiter command, you should avoid
the use of the backslash (‘
character because that is the escape character for MySQL.
The edit, nopager,
pager, and system
commands work only in Unix.
status command provides some
information about the connection and the server you are using.
If you are running in
status also prints the values for the
mysql variables that affect your queries.
To log queries and their output, use the
tee command. All the data displayed on the
screen is appended into a given file. This can be very useful
for debugging purposes also. You can enable this feature on
the command line with the
--tee option, or
interactively with the tee command. The
tee file can be disabled interactively with
the notee command. Executing
tee again re-enables logging. Without a
parameter, the previous file is used. Note that
tee flushes query results to the file after
each statement, just before mysql prints
its next prompt.
By using the
--pager option, it is possible
to browse or search query results in interactive mode with
Unix programs such as less,
more, or any other similar program. If you
specify no value for the option, mysql
checks the value of the
variable and sets the pager to that. Output paging can be
enabled interactively with the pager
command and disabled with nopager. The
command takes an optional argument; if given, the paging
program is set to that. With no argument, the pager is set to
the pager that was set on the command line, or
stdout if no pager was specified.
Output paging works only in Unix because it uses the
popen() function, which does not exist on
Windows. For Windows, the tee option can be
used instead to save query output, although this is not as
convenient as pager for browsing output in
Here are a few tips about the pager
You can use it to write to a file and the results go only
to the file:
pager cat > /tmp/log.txt
You can also pass any options for the program that you
want to use as your pager:
pager less -n -i -S
In the preceding example, note the
option. You may find it very useful for browsing wide
query results. Sometimes a very wide result set is
difficult to read on the screen. The
option to less can make the result set
much more readable because you can scroll it horizontally
using the left-arrow and right-arrow keys. You can also
-S interactively within
less to switch the horizontal-browse
mode on and off. For more information, read the
less manual page:
You can specify very complex pager commands for handling
pager cat | tee /dr1/tmp/res.txt \
| tee /dr2/tmp/res2.txt | less -n -i -S
In this example, the command would send query results to
two files in two different directories on two different
filesystems mounted on
/dr2, yet still display the results
onscreen via less.
You can also combine the tee and
pager functions. Have a
tee file enabled and
pager set to less, and
you are able to browse the results using the
less program and still have everything
appended into a file the same time. The difference between the
Unix tee used with the
pager command and the
mysql built-in tee
command is that the built-in tee works even
if you do not have the Unix tee available.
The built-in tee also logs everything that
is printed on the screen, whereas the Unix
tee used with pager does
not log quite that much. Additionally, tee
file logging can be turned on and off interactively from
within mysql. This is useful when you want
to log some queries to a file, but not others.
mysql> prompt can be
reconfigured. The string for defining the prompt can contain
the following special sequences:
|The server version
|The default database
|The server host
|The current TCP/IP port or socket file
|A literal ‘
\’ backslash character
|A newline character
|A tab character
|A space (a space follows the backslash)
|The current time, in 24-hour military time (0-23)
|The current time, standard 12-hour time (1-12)
|Minutes of the current time
|The current year, two digits
|The current year, four digits
|The full current date
|Seconds of the current time
|The current day of the week in three-letter format (Mon, Tue, …)
|The current month in numeric format
|The current month in three-letter format (Jan, Feb, …)
|A counter that increments for each statement you issue
\’ followed by any other
letter just becomes that letter.
If you specify the
prompt command with no
argument, mysql resets the prompt to the
You can set the prompt in several ways:
Use an environment variable. You can
MYSQL_PS1 environment variable
to a prompt string. For example:
export MYSQL_PS1="(\u@\h) [\d]> "
Use a command-line option. You can
--prompt option on the command
line to mysql. For example:
mysql --prompt="(\u@\h) [\d]> "
Use an option file. You can set the
prompt option in the
[mysql] group of any MySQL option file,
/etc/my.cnf or the
.my.cnf file in your home directory.
In this example, note that the backslashes are doubled. If
you set the prompt using the
option in an option file, it is advisable to double the
backslashes when using the special prompt options. There
is some overlap in the set of allowable prompt options and
the set of special escape sequences that are recognized in
option files. (These sequences are listed in
Section 4.3.2, “Using Option Files”.) The overlap may cause you
problems if you use single backslashes. For example,
\s is interpreted as a space rather
than as the current seconds value. The following example
shows how to define a prompt within an option file to
include the current time in
Set the prompt interactively. You can
change your prompt interactively by using the
command. For example:
prompt (\u@\h) [\d]>\_
PROMPT set to '(\u@\h) [\d]>\_'
Returning to default PROMPT of mysql>