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 (e.g., 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.

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

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

-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 want's to compare result files that includes error messages.

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

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

Password to use when connecting to server. If 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, --exit-info

Only used when debugging. --exit-info=0 will print some usage information on exit.

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

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
exit    (\q)    Exit mysql. Same as quit
go      (\g)    Send command to mysql server
ego     (\G)    Send command to mysql server; Display result vertically
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
use     (\u)    Use another database. Takes database name as argument

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 affects your queries.

A useful startup option for beginners (introduced in MySQL 3.23.11) is --safe-mode (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. See section 7.28 SET syntax.

The effect of the above is:

• You are not allowed to do an UPDATE or DELETE statements 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.