About the mysql Command-Line Client
mysql [from MariaDB 10.4.6, also called mariadb] 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 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 returning the result set using the
mysql_use_result[]
C API function in the client/server library rather than mysql_store_result[]
.
Using mysql is very easy. Invoke it from the prompt of your command interpreter as follows:
mysql db_name
Or:
mysql --user=user_name --password=your_password db_name
Then type an SQL statement, end it with “;”, \g, or \G and press Enter.
Typing Control-C causes mysql to attempt to kill the current statement. If this cannot be done, or Control-C is typed again before the statement is killed, mysql exits.
You can execute SQL statements in a script file [batch file] like this:
mysql db_name < script.sql > output.tab
From MariaDB 10.4.6, mariadb
is available as a symlink to mysql
.
From MariaDB 10.5.2, mysql
is the symlink, and mariadb
the binary name.
Using mysql
The command to use mysql
and the general syntax is:
mysql
Options
mysql
supports the following options:
Option Files
In addition to reading options from the command-line, mysql
can also read options from option files. If an unknown option is provided to mysql
in an option file, then it is ignored.
The following options relate to how MariaDB command-line tools handles option files. They must be given as the first argument on the command-line:
In MariaDB 10.2 and later, mysql
is linked with MariaDB Connector/C. However, MariaDB Connector/C does not yet handle the parsing of option files for this client. That is still performed by the server option file parsing code. See
MDEV-19035 for more information.
Option Groups
mysql
reads options from the following option groups from option files:
How to Specify Which Protocol to Use When Connecting to the mysqld Server
You can force which protocol to be used to connect to the mysqld
server by giving the protocol
option one of the following values: tcp
, socket
, pipe
or memory
.
If protocol
is not specified, before MariaDB 10.6.1, command line
connection properties that do not force protocol are ignored.
From MariaDB 10.6.1, a connection property specified via the command line [e.g. --port=3306
] will force its type. The protocol that matches the respective connection property is used, e.g. a TCP/IP connection is created when --port
is specified.
If multiple or no connection properties are specified via the command-line, then the following happens:
Linux/Unix
- If
hostname
is not specified orhostname
islocalhost
, then Unix sockets are used. - In other cases [
hostname
is given and it's notlocalhost
] then a TCP/IP connection through theport
option is used.
Note that localhost
is a special value. Using 127.0.0.1 is not the same thing. The latter will connect to the mysqld server through TCP/IP.
Windows
- If
shared-memory-base-name
is specified andhostname
is not specified orhostname
islocalhost
, then the connection will happen through shared memory. - If
shared-memory-base-name
is not specified andhostname
is not specified orhostname
islocalhost
, then the connection will happen through windows named pipes. - Named pipes will also be used if the
libmysql
/libmariadb
client library detects that the client doesn't support TCP/IP. - In other cases then a TCP/IP connection through the
port
option is used.
How to Test Which Protocol is Used
The status
command shows you information about which protocol is used:
shell> mysql test Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 10 Server version: 10.2.2-MariaDB-valgrind-max-debug Source distribution Copyright [c] 2000, 2016, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MariaDB [test]> status; -------------- mysql Ver 15.1 Distrib 10.0.25-MariaDB, for Linux [x86_64] using readline 5.2 Connection id: 10 Current database: test Current user: monty@localhost ... Connection: Localhost via UNIX socket ... UNIX socket: /tmp/mysql-dbug.sock
mysql Commands
There are also a number of commands that can be run inside the client. Note that all text commands must be first on line and end with ';'
The mysql_history File
On Unix, the mysql client writes a record of executed statements to a history file. By
default, this 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.
The .mysql_history file should be protected with a restrictive access mode because sensitive information might be written to it, such as the text of SQL statements that contain passwords.
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_history as a symbolic link to /dev/null:
shell> ln -s /dev/null $HOME/.mysql_history
You need do this only once.
prompt Command
The prompt command reconfigures the default prompt \N [\d]>
. The string for defining the prompt can contain the following special sequences.
mysql Tips
This section describes some techniques that can help you use mysql
more effectively.
Displaying Query Results Vertically
Some query results are much more readable when displayed vertically, instead of in the usual horizontal table format. Queries can be displayed vertically by terminating the query with \G instead of a semicolon. For example, longer text values that include newlines often are much easier to 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: mail_to: "Thimble Smith" sbj: UTF-8 txt: >>>>> "Thimble" == Thimble Smith writes: Thimble> Hi. I think this is a good idea. Is anyone familiar Thimble> with UTF-8 or Unicode? Otherwise, I´ll put this on my Thimble> TODO list and see what happens. Yes, please do that. Regards, Monty file: inbox-jani-1 hash: 190402944 1 row in set [0.09 sec]
Using the --safe-updates Option
For beginners, a useful startup option is --safe-updates
[or --i-am-a-dummy
, which has the same effect]. It is helpful for cases when you might have issued a DELETE FROM tbl_name
statement but forgotten the WHERE
clause. Normally, such a statement deletes all rows from the table. With --safe-updates
, you can delete rows only by specifying the key values that identify them. This helps prevent accidents.
When you use the --safe-updates
option, mysql issues the following statement when it connects to the MariaDB server:
SET sql_safe_updates=1, sql_select_limit=1000, sql_max_join_size=1000000;
The SET statement has the following effects:
- You are not allowed to execute an UPDATE or DELETE statement unless you specify a key constraint in the WHERE clause or provide a LIMIT clause [or both]. For example:
UPDATE tbl_name SET not_key_column=val WHERE key_column=val; UPDATE tbl_name SET not_key_column=val LIMIT 1;
- The server limits all large
SELECT
results to 1,000 rows unless the statement includes aLIMIT
clause. - The server aborts multiple-table
SELECT
statements that probably need to examine more than 1,000,000 row combinations.
To specify limits different from 1,000 and 1,000,000, you can override the defaults by using the --select_limit
and --max_join_size
options:
mysql --safe-updates --select_limit=500 --max_join_size=10000
Disabling mysql Auto-Reconnect
If the mysql client loses its connection to the server while sending a statement, it immediately and automatically tries to reconnect once to the server and send the statement again. However, even if mysql succeeds in reconnecting, your first connection has ended and all your previous session objects and settings are lost: temporary tables, the autocommit mode, and user-defined and session variables. Also, any current transaction rolls back. This behavior may be dangerous for you, as in the following example where the server was shut down and restarted between the first and second statements 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 | +------+
The @a user variable has been lost with the connection, and after the reconnection it is undefined. If it is important to have mysql terminate with an error if the connection has been lost, you can start the mysql client with the --skip-reconnect
option.
See Also
- Troubleshooting Connection Issues
- Readline commands and configuration