4.8.2 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 an SQL statement, end it with `;', `\g', or `\G' and press 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.

--prompt=...

Set the mysql prompt to specified format.

-b, --no-beep

Turn off beep-on-error.

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

-X, --xml

Produce XML 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. Please note that --set-variable is deprecated since MySQL 4.0, just use --var=option on its own.

-o, --one-database

Only update the default database. This is useful for skipping updates to other database in the binary 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_num, --port=port_num

TCP/IP port number to use for connection.

--protocol=(TCP | SOCKET | PIPE | MEMORY)

To specify the connect protocol to use. New in MySQL 4.1.

-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

--reconnect

If the connection is lost, automatically try to reconnect to the server (but only once).

-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; please note that --set-variable is deprecated since MySQL 4.0, just use --var=option on its own:

If the mysql client loses connection to the server while sending it a query, it will immediately and automatically try to reconnect once to the server and send the query again. Note that even if it succeeds in reconnecting, as your first connection has ended, all your previous session objects are lost : temporary tables, user and session variables. Therefore, the above behaviour may be dangerous for you, as in this example where the server was shut down and restarted without you knowing it :

mysql> set @a=1;
Query OK, 0 rows affected (0.05 sec)

mysql> insert into t values(@a);
ERROR 2006: MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    1
Current database: test

Query OK, 1 row affected (1.30 sec)

mysql> select * from t;
+------+
| a    |
+------+
| NULL |
+------+
1 row in set (0.05 sec)

The @a user variable has been lost with the connection, and after the reconnection it is undefined. To protect from this risk, you can start the mysql client with the --disable-reconnect option.

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.
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 outfile.
use     (\u)    Use another database.
                Takes database name as argument.

The edit, nopager, pager, and system commands work only 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 once may have 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. See section 5.5.6 SET Syntax.

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.
• SELECTs 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 the PAGER environment variable 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 switch the mode between on and off within the interactive less with -S. See the 'h' for more help about less.
• 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.

From MySQL version 4.0.2 it is possible to change the prompt in the mysql command-line client.

You can use the following prompt options:

`\' followed by any other letter just becomes that letter.

You may set the prompt in the following places:

Environment Variable

You may set the MYSQL_PS1 environment variable to a prompt string. For example:

shell> export MYSQL_PS1="(\u@\h) [\d]> "

`my.cnf'

`.my.cnf'

You may set the prompt option in any MySQL configuration file, in the mysql group. For example:

[mysql]
prompt=(\u@\h) [\d]>\_

Command Line

You may set the --prompt option on the command line to mysql. For example:

shell> mysql --prompt="(\u@\h) [\d]> "

(user@host) [database]> 

Interactively

You may also use the prompt (or \R) command to change your prompt interactively. For example:

mysql> prompt (\u@\h) [\d]>\_
PROMPT set to '(\u@\h) [\d]>\_'
(user@host) [database]> 
(user@host) [database]> prompt
Returning to default PROMPT of mysql> 
mysql> 

User Comments

The documentation is missing the \! command. It is listed in the mysql client's internal help though:

system (\!) Execute a system shell command.

I'm trying to run the mysql comamnd-line tool within emacs and cygwin.

Seeing the "buffering in shells" section here: http://www.gnu.org/software/emacs/windows/faq7.html

it seems that the mysql command-line tool buffers stdout to a pipe handle in blocks (~1k) rather than by line. This makes an interactive session impossible, unfortunately.

Is there a way to tell the mysql command-line tool to buffer by line, rather than by block?

My feature request is a way to cancel a running query without exiting from the mysql program. It would be ideal if ^C would abort the query but not kill the program (like in oracle sqlplus). If that is not possible then another solution would be to define a signal that could be sent to the mysql program that causes it to cancel a query.
(e.g. after putting the mysql command into the background, type $ kill -SIGwhatever $the-mysql-PID)

Add your own comment.