GNU Info
(mysql.info)mysql
The Command-line Tool
---------------------
`mysql' is a simple SQL shell (with GNU `readline' capabilities). It
supports interactive and non-interactive use. When used interactively,
query results are presented in an ASCII-table format. When used
non-interactively (for example, as a filter), the result is presented in
tab-separated format. (The output format can be changed using
command-line options.) You can run scripts simply like this:
shell> mysql database < script.sql > output.tab
If you have problems due to insufficient memory in the client, use the
`--quick' option! This forces `mysql' to use `mysql_use_result()'
rather than `mysql_store_result()' to retrieve the result set.
Using `mysql' is very easy. Just start it as follows: `mysql database'
or `mysql --user=user_name --password=your_password database'. Type a
SQL statement, end it with `;', `\g', or `\G' and press RETURN/ENTER.
`mysql' supports the following options:
`-?, --help'
Display this help and exit.
`-A, --no-auto-rehash'
No automatic rehashing. One has to use 'rehash' to get table and
field completion. This gives a quicker start of mysql.
`-B, --batch'
Print results with a tab as separator, each row on a new line.
Doesn't use history file.
`--character-sets-dir=...'
Directory where character sets are located.
`-C, --compress'
Use compression in server/client protocol.
`-#, --debug[=...]'
Debug log. Default is 'd:t:o,/tmp/mysql.trace'.
`-D, --database=...'
Database to use. This is mainly useful in the `my.cnf' file.
`--default-character-set=...'
Set the default character set.
`-e, --execute=...'
Execute command and quit. (Output like with -batch)
`-E, --vertical'
Print the output of a query (rows) vertically. Without this option
you can also force this output by ending your statements with `\G'.
`-f, --force'
Continue even if we get a SQL error.
`-g, --no-named-commands'
Named commands are disabled. Use \* form only, or use named
commands only in the beginning of a line ending with a semicolon
(;). Since Version 10.9, the client now starts with this option
ENABLED by default! With the -g option, long format commands will
still work from the first line, however.
`-G, --enable-named-commands'
Named commands are *enabled*. Long format commands are allowed as
well as shortened \* commands.
`-i, --ignore-space'
Ignore space after function names.
`-h, --host=...'
Connect to the given host.
`-H, --html'
Produce HTML output.
`-L, --skip-line-numbers'
Don't write line number for errors. Useful when one wants to
compare result files that includes error messages
`--no-pager'
Disable pager and print to stdout. See interactive help (\h) also.
`--no-tee'
Disable outfile. See interactive help (\h) also.
`-n, --unbuffered'
Flush buffer after each query.
`-N, --skip-column-names'
Don't write column names in results.
`-O, --set-variable var=option'
Give a variable a value. `--help' lists variables.
`-o, --one-database'
Only update the default database. This is useful for skipping
updates to other database in the update log.
``--pager[=...]''
Output type. Default is your `ENV' variable `PAGER'. Valid pagers
are less, more, cat [> filename], etc. See interactive help (\h)
also. This option does not work in batch mode. Pager works only in
UNIX.
`-p[password], --password[=...]'
Password to use when connecting to server. If a password is not
given on the command line, you will be prompted for it. Note that
if you use the short form `-p' you can't have a space between the
option and the password.
`-P --port=...'
TCP/IP port number to use for connection.
`-q, --quick'
Don't cache result, print it row-by-row. This may slow down the
server if the output is suspended. Doesn't use history file.
`-r, --raw'
Write column values without escape conversion. Used with `--batch'
`-s, --silent'
Be more silent.
`-S --socket=...'
Socket file to use for connection.
`-t --table'
Output in table format. This is default in non-batch mode.
`-T, --debug-info'
Print some debug information at exit.
`--tee=...'
Append everything into outfile. See interactive help (\h) also.
Does not work in batch mode.
`-u, --user=#'
User for login if not current user.
`-U, --safe-updates[=#], --i-am-a-dummy[=#]'
Only allow `UPDATE' and `DELETE' that uses keys. See below for
more information about this option. You can reset this option if
you have it in your `my.cnf' file by using `--safe-updates=0'.
`-v, --verbose'
More verbose output (-v -v -v gives the table output format).
`-V, --version'
Output version information and exit.
`-w, --wait'
Wait and retry if connection is down instead of aborting.
You can also set the following variables with `-O' or `--set-variable':
Variable Name Default Description
connect_timeout 0 Number of seconds before timeout
connection.
max_allowed_packet 16777216 Max packetlength to send/receive
from to server
net_buffer_length 16384 Buffer for TCP/IP and socket
communication
select_limit 1000 Automatic limit for SELECT when
using -i-am-a-dummy
max_join_size 1000000 Automatic limit for rows in a join
when using -i-am-a-dummy.
If you type 'help' on the command line, `mysql' will print out the
commands that it supports:
mysql> help
MySQL commands:
help (\h) Display this text.
? (\h) Synonym for `help'.
clear (\c) Clear command.
connect (\r) Reconnect to the server. Optional arguments are db and host.
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.
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.
quit (\q) Quit mysql.
rehash (\#) Rebuild completion hash.
source (\.) Execute a SQL script file. Takes a file name as an argument.
status (\s) Get status information from the server.
tee (\T) Set outfile [to_outfile]. Append everything into given outfile.
use (\u) Use another database. Takes database name as argument.
From the above, pager only works in UNIX.
The `status' command gives you some information about the connection
and the server you are using. If you are running in the
`--safe-updates' mode, `status' will also print the values for the
`mysql' variables that affect your queries.
A useful startup option for beginners (introduced in MySQL Version
3.23.11) is `--safe-updates' (or `--i-am-a-dummy' for users that has at
some time done a `DELETE FROM table_name' but forgot the `WHERE'
clause). When using this option, `mysql' sends the following command
to the MySQL server when opening the connection:
SET SQL_SAFE_UPDATES=1,SQL_SELECT_LIMIT=#select_limit#,
SQL_MAX_JOIN_SIZE=#max_join_size#"
where `#select_limit#' and `#max_join_size#' are variables that can be
set from the `mysql' command line. Note: `SET'.
The effect of the above is:
* You are not allowed to do an `UPDATE' or `DELETE' statement if you
don't have a key constraint in the `WHERE' part. One can, however,
force an `UPDATE/DELETE' by using `LIMIT':
UPDATE table_name SET not_key_column=# WHERE not_key_column=# LIMIT 1;
* All big results are automatically limited to `#select_limit#' rows.
* `SELECT''s that will probably need to examine more than
`#max_join_size' row combinations will be aborted.
Some useful hints about the `mysql' client:
Some data is much more readable when displayed vertically, instead of
the usual horizontal box type output. For example longer text, which
includes new lines, is often much easier to be read with vertical
output.
mysql> select * from mails where length(txt) < 300 limit 300,1\G
*************************** 1. row ***************************
msg_nro: 3068
date: 2000-03-01 23:29:50
time_zone: +0200
mail_from: Monty
reply: monty@no.spam.com
mail_to: "Thimble Smith" <tim@no.spam.com>
sbj: UTF-8
txt: >>>>> "Thimble" == Thimble Smith writes:
Thimble> Hi. I think this is a good idea. Is anyone familiar with UTF-8
Thimble> or Unicode? Otherwise I'll put this on my TODO list and see what
Thimble> happens.
Yes, please do that.
Regards,
Monty
file: inbox-jani-1
hash: 190402944
1 row in set (0.09 sec)
* For logging, you can use the `tee' option. The `tee' can be
started with option `--tee=...', or from the command line
interactively with command `tee'. All the data displayed on the
screen will also be appended into a given file. This can be very
useful for debugging purposes also. The `tee' can be disabled from
the command line with command `notee'. Executing `tee' again
starts logging again. Without a parameter the previous file will be
used. Note that `tee' will flush the results into the file after
each command, just before the command line appears again waiting
for the next command.
* Browsing, or searching the results in the interactive mode in UNIX
less, more, or any other similar program, is now possible with
option `--pager[=...]'. Without argument, `mysql' client will look
for environment variable PAGER and set `pager' to that. `pager'
can be started from the interactive command line with command
`pager' and disabled with command `nopager'. The command takes an
argument optionally and the `pager' will be set to that. Command
`pager' can be called without an argument, but this requires that
the option `--pager' was used, or the `pager' will default to
stdout. `pager' works only in UNIX, since it uses the popen()
function, which doesn't exist in Windows. In Windows, the `tee'
option can be used instead, although it may not be as handy as
`pager' can be in some situations.
* A few tips about `pager': You can use it to write to a file:
mysql> pager cat > /tmp/log.txt
and the results will only go to a file. You can also pass any
options for the programs that you want to use with the `pager':
mysql> pager less -n -i -S
From the above do note the option '-S'. You may find it very
useful when browsing the results; try the option with horizontal
output (end commands with '\g', or ';') and with vertical output
(end commands with '\G'). Sometimes a very wide result set is hard
to be read from the screen, with option -S to less you can browse
the results within the interactive less from left to right,
preventing lines longer than your screen from being continued to
the next line. This can make the result set much more readable.
You can swith the mode between on and off within the interactive
less with '-S'. See the 'h' for more help about less.
* Last (unless you already understood this from the above examples
;) you can combine very complex ways to handle the results, for
example the following would send the results to two files in two
different directories, on two different hard-disks mounted on /dr1
and /dr2, yet let the results still be seen on the screen via less:
mysql> pager cat | tee /dr1/tmp/res.txt | tee /dr2/tmp/res2.txt | less -n -i -S
* You can also combine the two functions above; have the `tee'
enabled, `pager' set to 'less' and you will be able to browse the
results in unix 'less' and still have everything appended into a
file the same time. The difference between `UNIX tee' used with the
`pager' and the `mysql' client in-built `tee', is that the
in-built `tee' works even if you don't have the `UNIX tee'
available. The in-built `tee' also logs everything that is printed
on the screen, where the `UNIX tee' used with `pager' doesn't log
quite that much. Last, but not least, the interactive `tee' is
more handy to switch on and off, when you want to log something
into a file, but want to be able to turn the feature off sometimes.
automatically generated by info2www version 1.2.2.9