reference manuals search engine

MySQL Reference Manual - 4.0.20

8.3 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.

If you have problems due to insufficient memory for large result sets, use the --quick option! This forces mysql to retrieve results from the server a row at a time rather than retrieving the entire result set and buffering it in memory before displaying it. This is done by using mysql_use_result() rather than mysql_store_result() to retrieve the result set.

Using mysql is very easy. Invoke it from the prompt of your command interpreter as follows:

shell> mysql db_name

Or:

shell> mysql --user=user_name --password=your_password db_name

Then type an SQL statement, end it with `;', \g, or \G and press Enter.

You can run a script simply like this:

shell> mysql db_name < script.sql > output.tab

mysql supports the following options:

--help, -?

Display a help message and exit.

--batch, -B

Print results using tab as the column separator, with each row on a new line. With this option, mysql doesn't use the history file.

--character-sets-dir=path

The directory where character sets are installed. See section The Character Set Used for Data and Sorting.

--compress, -C

Compress all information sent between the client and the server if both support compression.

--database=db_name, -D db_name

The database to use. This is useful mainly in an option file.

--debug[=debug_options], -# [debug_options]

Write a debugging log. The debug_options string often is 'd:t:o,file_name'. The default is 'd:t:o,/tmp/mysql.trace'.

--debug-info, -T

Print some debugging information when the program exits.

--default-character-set=charset

Use charset as the default character set. See section The Character Set Used for Data and Sorting.

--execute=statement, -e statement

Execute the statement and quit. The default output format is like that produced with --batch.

--force, -f

Continue even if an SQL error occurs.

--host=host_name, -h host_name

Connect to the MySQL server on the given host.

--html, -H

Produce HTML output.

--ignore-space, -i

Ignore spaces after function names. The effect of this is described in the discussion for IGNORE_SPACE in The Server SQL Mode.

--local-infile[={0|1}]

Enable or disable LOCAL capability for LOAD DATA INFILE. With no value, the option enables LOCAL. It may be given as --local-infile=0 or --local-infile=1 to explicitly disable or enable LOCAL. Enabling LOCAL has no effect if the server does not also support it.

--named-commands, -G

Named commands are enabled. Long format commands are allowed as well as shortened \* commands. For example, quit and \q both are recognized.

--no-auto-rehash, -A

No automatic rehashing. This option causes mysql to start faster, but you must issue the rehash command if you want to use table and column name completion.

--no-beep, -b

Do not beep when errors occur.

--no-named-commands, -g

Named commands are disabled. Use the \* form only, or use named commands only at the beginning of a line ending with a semicolon (`;'). As of MySQL 3.23.22, mysql starts with this option enabled by default! However, even with this option, long-format commands still work from the first line.

--no-pager

Do not use a pager for displaying query output. Output paging is discussed further in mysql Commands.

--no-tee

Do not copy output to a file. Tee files are discussed further in mysql Commands.

--one-database, -O

Ignore statements except those for the default database named on the command line. This is useful for skipping updates to other databases in the binary log.

--pager[=command]

Use the given command for paging query output. If the command is omitted, the default pager is the value of your PAGER environment variable. Valid pagers are less, more, cat [> filename], and so forth. This option works only on Unix. It does not work in batch mode. Output paging is discussed further in mysql Commands.

--password[=password], -p[password]

The password to use when connecting to the server. Note that if you use the short option form (-p), you cannot have a space between the option and the password. If no password is given on the command line, you will be prompted for one.

--port=port_num, -P port_num

The TCP/IP port number to use for the connection.

--prompt=format_str

Set the prompt to the specified format. The default is mysql>. The special sequences that the prompt can contain are described in mysql Commands.

--protocol={TCP | SOCKET | PIPE | MEMORY}

The connection protocol to use. New in MySQL 4.1.

--quick, -q

Don't cache each query result, print it row-by-row. This may slow down the server if the output is suspended. With this option, mysql doesn't use the history file.

--raw, -r

Write column values without escape conversion. Often used with the --batch option.

--reconnect

If the connection to the server is lost, automatically try to reconnect. A single reconnect attempt is made each time the connection is lost. To suppress reconnection behavior, use --skip-reconnect. New in MySQL 4.1.0.

--safe-updates, --i-am-a-dummy, -U

Allow only UPDATE and DELETE statements that specify rows to affect using key values. If you have this option in an option file, you can override it by using --safe-updates on the command line. See mysql Tips for more information about this option.

--silent, -s

Silent mode. Produce less output. This option can be given multiple times to produce less and less output.

--skip-column-names, -N

Don't write column names in results.

--skip-line-numbers, -L

Don't write line numbers for errors. Useful when you want to compare result files that include error messages.

--socket=path, -S path

The socket file to use for the connection.

--table, -t

Display output in table format. This is the default for interactive use, but can be used to produce table output in batch mode.

--tee=file_name

Append a copy of output to the given file. This option does not work in batch mode. Tee files are discussed further in mysql Commands.

--unbuffered, -n

Flush the buffer after each query.

--user=user_name, -u user_name

The MySQL username to use when connecting to the server.

--verbose, -v

Verbose mode. Produce more output. This option can be given multiple times to produce more and more output. (For example, -v -v -v produces the table output format even in batch mode.)

--version, -V

Display version information and exit.

--vertical, -E

Print the rows of query output vertically. Without this option, you can specify vertical output for individual statements by terminating them with \G.

--wait, -w

If the connection cannot be established, wait and retry instead of aborting.

--xml, -X

Produce XML output.

You can also set the following variables by using --var_name=value options:

connect_timeout

The number of seconds before connection timeout. (Default value is 0.)

max_allowed_packet

The maximum packet length to send to or receive from the server. (Default value is 16MB.)

max_join_size

The automatic limit for rows in a join when using --safe-updates. (Default value is 1,000,000.)

net_buffer_length

The buffer size for TCP/IP and socket communication. (Default value is 16KB.)

select_limit

The automatic limit for SELECT statements when using --safe-updates. (Default value is 1,000.)

It is also possible to set variables by using --set-variable=var_name=value or -O var_name=value syntax. However, this syntax is deprecated as of MySQL 4.0.

On Unix, the mysql client writes a record of executed statements to a history file. By default, the history file is named `.mysql_history' and is created in your home directory. To specify a different file, set the value of the MYSQL_HISTFILE environment variable.

If you do not want to maintain a history file, first remove `.mysql_history' if it exists, and then use either of the following techniques:

• Set the MYSQL_HISTFILE variable to `/dev/null'. To cause this setting to take effect each time you log in, put the setting in one of your shell's startup files.

• Create `.mysql_histfile' as a symbolic link to `/dev/null':

  shell> ln -s /dev/null $HOME/.mysql_history

  You need do this only once.

This document was generated on July, 26 2004 using texi2html 1.70.