All MySQL clients that communicate with the server using the mysqlclient library use the following environment variables:

Use of MYSQL_PWD is insecure. Section 4.2.8, “Connecting to the MySQL Server ”.

The mysql client uses the file named in the MYSQL_HISTFILE environment variable to save the command-line history. The default value for the history file is $HOME/.mysql_history, where $HOME is the value of the HOME environment variable. Appendix F, Environment Variables .

All MySQL programs take many different options. However, every MySQL program provides a -help option that you can use to get a full description of the program's different options. For example, try mysql -help.

You can override default options for all standard client programs with an option file. Section 4.1.2, “my.cnf Option Files ”.

The following list briefly describes the client-side MySQL programs:

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:

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

The effect of the above is:

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

A utility for performing administrative operations. The syntax is:

shell mysqladmin [OPTIONS] command [command-option] command ...

You can get a list of the options your version of mysqladmin supports by executing mysqladmin -help.

The current mysqladmin supports the following commands:

All commands can be shortened to their unique prefix. For example:

shell mysqladmin proc stat
+----+-------+-----------+----+-------------+------+-------+------+
| Id | User  | Host      | db | Command     | Time | State | Info |
+----+-------+-----------+----+-------------+------+-------+------+
| 6  | monty | localhost |    | Processlist | 0    |       |      |
+----+-------+-----------+----+-------------+------+-------+------+
Uptime: 10077  Threads: 1  Questions: 9  Slow queries: 0
Opens: 6 Flush tables: 1  Open tables: 2
Memory in use: 1092K  Max memory used: 1116K

The mysqladmin status command result has the following columns:

If you do mysqladmin shutdown on a socket (in other words, on a the computer where mysqld is running), mysqladmin will wait until the MySQL pid-file is removed to ensure that the mysqld server has stopped properly.

You can examine the binary log file (Section 4.9.4, “The Binary Log ”) with the mysqlbinlog utility.

shell mysqlbinlog hostname-bin.001

will print all queries contained in binlog hostname-bin.001, together with information (time the query took, id of the thread which issued it, timestamp when it was issued etc).

You can pipe the output of mysqlbinlog into a mysql client; this is used to recover from a crash when you have an old backup (Section 4.4.1, “Database Backups ”):

shell mysqlbinlog hostname-bin.001 | mysql

or

shell mysqlbinlog hostname-bin.[0-9]* | mysql

You can also redirect the output of mysqlbinlog to a text file instead, modify this text file (to cut queries you don't want to execute for some reason), then execute the queries from the text file into mysql.

mysqlbinlog has the position=# options which will print only queries whose offset in the binlog is greater or equal to #.

If you have more than one binary log to execute on the MySQL server, the safe method is to do it in one unique MySQL connection. Here is what may be UNsafe:

shell mysqlbinlog hostname-bin.001 | mysql # DANGER!!
shell mysqlbinlog hostname-bin.002 | mysql # DANGER!!

It will cause problems if the first binlog contains a CREATE TEMPORARY TABLE and the second one contains a query which uses this temporary table: when the first mysql terminates, it will drop the temporary table, so the second mysql will report "unknown table". This is why you should run all binlogs you want in one unique connection, especially if you use temporary tables. Here are two possible ways:

shell mysqlbinlog hostname-bin.001 hostname-bin.002 | mysql

shell mysqlbinlog hostname-bin.001   /tmp/queries.sql
shell mysqlbinlog hostname-bin.002  /tmp/queries.sql
shell mysql -e "source /tmp/queries.sql"

Starting from MySQL 4.0.14, mysqlbinlog can prepare suitable input for mysql to execute a LOAD DATA INFILE from a binlog. As the binlog contains the data to load (this is true for MySQL 4.0; MySQL 3.23 did not write the loaded data into the binlog, so the original file was needed when one wanted to execute the content of the binlog), mysqlbinlog will copy this data to a temporary file and print a LOAD DATA INFILE command for mysql to load this temporary file. The location where the temporary file is created is by default the temporary directory; it can be changed with the local-load option of mysqlbinlog.

Before MySQL 4.1, mysqlbinlog could not prepare suitable output for mysql when the binary log contained queries from different threads using temporary tables of the same name, if these queries were interlaced. This is solved in MySQL 4.1.

You can also use mysqlbinlog to read the binary log directly from a remote MySQL server.

mysqlbinlog -help will give you more information.

Since MySQL version 3.23.38 you will be able to use a new checking and repairing tool for MyISAM tables. The difference to myisamchk is that mysqlcheck should be used when the mysqld server is running, whereas myisamchk should be used when it is not. The benefit is that you no longer have to take the server down for checking or repairing your tables.

mysqlcheck uses MySQL server commands CHECK, REPAIR, ANALYZE and OPTIMIZE in a convenient way for the user.

There are three alternative ways to invoke mysqlcheck:

shell mysqlcheck [OPTIONS] database [tables]
shell mysqlcheck [OPTIONS] --databases DB1 [DB2 DB3...]
shell mysqlcheck [OPTIONS] --all-databases

So it can be used in a similar way as mysqldump when it comes to what databases and tables you want to choose.

mysqlcheck does have a special feature compared to the other clients; the default behaviour, checking tables (-c), can be changed by renaming the binary. So if you want to have a tool that repairs tables by default, you should just copy mysqlcheck to your harddrive with a new name, mysqlrepair, or alternatively make a symbolic link to mysqlrepair and name the symbolic link as mysqlrepair. If you invoke mysqlrepair now, it will repair tables by default.

The names that you can use to change mysqlcheck default behaviour are here:

mysqlrepair:   The default option will be -r
mysqlanalyze:  The default option will be -a
mysqloptimize: The default option will be -o

The options available for mysqlcheck are listed here, please check what your version supports with mysqlcheck -help.

Utility to dump a database or a collection of database for backup or for transferring the data to another SQL server (not necessarily a MySQL server). The dump will contain SQL statements to create the table and/or populate the table.

If you are doing a backup on the server, you should consider using the mysqlhotcopy instead. Section 4.8.7, “mysqlhotcopy, Copying MySQL Databases and Tables ”.

shell mysqldump [OPTIONS] database [tables]
OR     mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...]
OR     mysqldump [OPTIONS] --all-databases [OPTIONS]

If you don't give any tables or use the -databases or -all-databases, the whole database(s) will be dumped.

You can get a list of the options your version of mysqldump supports by executing mysqldump -help.

Note that if you run mysqldump without -quick or -opt, mysqldump will load the whole result set into memory before dumping the result. This will probably be a problem if you are dumping a big database.

Note that if you are using a new copy of the mysqldump program and you are going to do a dump that will be read into a very old MySQL server, you should not use the -opt or -e options.

mysqldump supports the following options:

The most normal use of mysqldump is probably for making a backup of whole databases. Section 4.4.1, “Database Backups ”.

mysqldump --opt database  backup-file.sql

You can read this back into MySQL with:

mysql database  backup-file.sql

or

mysql -e "source /patch-to-backup/backup-file.sql" database

However, it's also very useful to populate another MySQL server with information from a database:

mysqldump --opt database | mysql ---host=remote-host -C database

It is possible to dump several databases with one command:

mysqldump --databases database1 [database2 ...]  my_databases.sql

If all the databases are wanted, one can use:

mysqldump --all-databases  all_databases.sql

mysqlhotcopy is a Perl script that uses LOCK TABLES, FLUSH TABLES and cp or scp to quickly make a backup of a database. It's the fastest way to make a backup of the database or single tables, but it can only be run on the same machine where the database directories are. mysqlhotcopy works only on Unix, and it works only for MyISAM and ISAM tables.

mysqlhotcopy db_name [/path/to/new_directory]

mysqlhotcopy db_name_1 ... db_name_n /path/to/new_directory

mysqlhotcopy db_name./regex/

mysqlhotcopy supports the following options:

You can use perldoc mysqlhotcopy to get more complete documentation for mysqlhotcopy.

mysqlhotcopy reads the groups [client] and [mysqlhotcopy] from the option files.

To be able to execute mysqlhotcopy you need write access to the backup directory, the SELECT privilege for the tables you are about to copy and the MySQL RELOAD privilege (to be able to execute FLUSH TABLES).

mysqlimport provides a command-line interface to the LOAD DATA INFILE SQL statement. Most options to mysqlimport correspond directly to the same options to LOAD DATA INFILE. Section 6.4.9, “LOAD DATA INFILE Syntax ”.

mysqlimport is invoked like this:

shell mysqlimport [options] database textfile1 [textfile2 ...]

For each text file named on the command-line, mysqlimport strips any extension from the filename and uses the result to determine which table to import the file's contents into. For example, files named patient.txt, patient.text, and patient would all be imported into a table named patient.

mysqlimport supports the following options:

Here is a sample run using mysqlimport:

$ mysql --version
mysql  Ver 9.33 Distrib 3.22.25, for pc-linux-gnu (i686)
$ uname -a
Linux xxx.com 2.2.5-15 #1 Mon Apr 19 22:21:09 EDT 1999 i586 unknown
$ mysql -e 'CREATE TABLE imptest(id INT, n VARCHAR(30))' test
$ ed
a
100     Max Sydow
101     Count Dracula
.
w imptest.txt
32
q
$ od -c imptest.txt
0000000   1   0   0  \t   M   a   x       S   y   d   o   w  \n   1   0
0000020   1  \t   C   o   u   n   t       D   r   a   c   u   l   a  \n
0000040
$ mysqlimport --local test imptest.txt
test.imptest: Records: 2  Deleted: 0  Skipped: 0  Warnings: 0
$ mysql -e 'SELECT * FROM imptest' test
+------+---------------+
| id   | n             |
+------+---------------+
|  100 | Max Sydow     |
|  101 | Count Dracula |
+------+---------------+

mysqlshow can be used to quickly look at which databases exist, their tables, and the table's columns.

With the mysql program you can get the same information with the SHOW commands. Section 4.5.7, “SHOW Syntax ”.

mysqlshow is invoked like this:

shell mysqlshow [OPTIONS] [database [table [column]]]

Note that in newer MySQL versions, you only see those database/tables/columns for which you have some privileges.

If the last argument contains a shell or SQL wildcard (*, ?, % or _) then only what's matched by the wildcard is shown. If a database contains underscore(s), those should be escaped with backslash (some Unix shells will require two), in order to get tables / columns properly. '*' are converted into SQL '%' wildcard and '?' into SQL '_' wildcard. This may cause some confusion when you try to display the columns for a table with a _ as in this case mysqlshow only shows you the table names that match the pattern. This is easily fixed by adding an extra % last on the command-line (as a separate argument).

mysql_config provides you with useful information how to compile your MySQL client and connect it to MySQL.

mysql_config supports the following options:

If you execute mysql_config without any options it will print all options it supports plus the value of all options:

shell mysql_config
sage: /usr/local/mysql/bin/mysql_config [OPTIONS]
Options:
        --cflags         [-I'/usr/local/mysql/include/mysql']
        --libs           [-L'/usr/local/mysql/lib/mysql' -lmysqlclient -lz -lcrypt -lnsl -lm -L/usr/lib -lssl -lcrypto]
        --socket         [/tmp/mysql.sock]
        --port           [3306]
        --version        [4.0.8-gamma]
        --libmysqld-libs [ -L'/usr/local/mysql/lib/mysql' -lmysqld -lpthread -lz -lcrypt -lnsl -lm  -lpthread  -lrt]

You can use this to compile a MySQL client by as follows:

CFG=/usr/local/mysql/bin/mysql_config
sh -c "gcc -o progname `$CFG --cflags` progname.c `$CFG --libs`"

For most system errors MySQL will, in addition to a internal text message, also print the system error code in one of the following styles: message ... (errno: #) or message ... (Errcode: #).

You can find out what the error code means by either examining the documentation for your system or use the perror utility.

perror prints a description for a system error code, or an MyISAM/ISAM storage engine (table handler) error code.

perror is invoked like this:

shell perror [OPTIONS] [ERRORCODE [ERRORCODE...]]

Example:

shell perror 13 64
Error code  13:  Permission denied
Error code  64:  Machine is not on the network

Note that the error messages are mostly system dependent!

The mysql client typically is used interactively, like this:

shell mysql database

However, it's also possible to put your SQL commands in a file and tell mysql to read its input from that file. To do so, create a text file text_file that contains the commands you wish to execute. Then invoke mysql as shown here:

shell mysql database  text_file

You can also start your text file with a USE db_name statement. In this case, it is unnecessary to specify the database name on the command line:

shell mysql  text_file

If you are already running mysql, you can execute an SQL script file using the source command:

mysql source filename;

For more information about batch mode, Section 3.5, “Using mysql in Batch Mode ”.