The MySQL client has some functionalities some of us never use. Why would you use them and what is the added value of this?

Every DBA and developer has had a moment when he or she needs to connect to a MySQL database using the command line tool. Therefore I’ve written down an explanation of some command line commands you can insert in the CLI, most of them give added value and make your experience with the cli more enjoyable.

prompt

Who has never witnessed the scary feeling of not being connected to the write database when having several terminals open. I do, due to the fact I use the prompt functionality.

mysql >R Production > PROMPT set to 'Production > ' 1 2 mysql > R Production > PROMPT set to 'Production > '

Or you can go a bit further and visualise the user, host and active database in:

mysql > R u@h [d]> PROMPT set to 'u@h [d]>' root@testbox [test]> 1 2 3 mysql > R u @ h [ d ] > PROMPT set to 'u@h [d]>' root @ testbox [ test ] >

edit

In some situations editing the query in an editor instead of the cli can have several enhancements. It gives you the ability to fix typos, have a deep look at the queries before you submit them and etc.

If you’d like to edit the query you are making in your default editor instead of using the cli.

mysql> e 1 mysql > e

The editor appears, in which you can create your query/ies.

use sakila; select * from city limit 10; ~ ~ ~ 1 2 3 4 5 6 use sakila ; select * from city limit 10 ; ~ ~ ~

After closing this down and putting a delimiter in the cli, this query will be run against the database while outputting in the prompt.

mysql> e -> ; +---------+--------------------+------------+---------------------+ | city_id | city | country_id | last_update | +---------+--------------------+------------+---------------------+ | 1 | A Corua (La Corua) | 87 | 2006-02-15 04:45:25 | | 2 | Abha | 82 | 2006-02-15 04:45:25 | | 3 | Abu Dhabi | 101 | 2006-02-15 04:45:25 | | 4 | Acua | 60 | 2006-02-15 04:45:25 | | 5 | Adana | 97 | 2006-02-15 04:45:25 | | 6 | Addis Abeba | 31 | 2006-02-15 04:45:25 | | 7 | Aden | 107 | 2006-02-15 04:45:25 | | 8 | Adoni | 44 | 2006-02-15 04:45:25 | | 9 | Ahmadnagar | 44 | 2006-02-15 04:45:25 | | 10 | Akishima | 50 | 2006-02-15 04:45:25 | +---------+--------------------+------------+---------------------+ 10 rows in set (0.03 sec) 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 mysql > e -> ; + -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- + -- -- -- -- -- -- + -- -- -- -- -- -- -- -- -- -- - + | city_id | city | country_id | last_update | + -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- + -- -- -- -- -- -- + -- -- -- -- -- -- -- -- -- -- - + | 1 | A Corua ( La Corua ) | 87 | 2006 - 02 - 15 04 : 45 : 25 | | 2 | Abha | 82 | 2006 - 02 - 15 04 : 45 : 25 | | 3 | Abu Dhabi | 101 | 2006 - 02 - 15 04 : 45 : 25 | | 4 | Acua | 60 | 2006 - 02 - 15 04 : 45 : 25 | | 5 | Adana | 97 | 2006 - 02 - 15 04 : 45 : 25 | | 6 | Addis Abeba | 31 | 2006 - 02 - 15 04 : 45 : 25 | | 7 | Aden | 107 | 2006 - 02 - 15 04 : 45 : 25 | | 8 | Adoni | 44 | 2006 - 02 - 15 04 : 45 : 25 | | 9 | Ahmadnagar | 44 | 2006 - 02 - 15 04 : 45 : 25 | | 10 | Akishima | 50 | 2006 - 02 - 15 04 : 45 : 25 | + -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- + -- -- -- -- -- -- + -- -- -- -- -- -- -- -- -- -- - + 10 rows in set ( 0.03 sec )

tee



Performing critical maintenance on a database could require to have an entire log of performed queries and actions. You can activate the full output of the MySQL client, including your performed queries. This utility is ideal if you prefer having a log of all of your actions. This could be for documentation stakes or a way to reread your actions if issues would occur.

Example:

mysql> T /tmp/tee.log Logging to file '/tmp/tee.log' 1 2 mysql > T / tmp / tee .log Logging to file '/tmp/tee.log'

This will provide in the output the queries you perform.

dim0@testing101:~$ cat /tmp/tee.log mysql> select * from city limit 5; +---------+--------------------+------------+---------------------+ | city_id | city | country_id | last_update | +---------+--------------------+------------+---------------------+ | 1 | A Corua (La Corua) | 87 | 2006-02-15 04:45:25 | | 2 | Abha | 82 | 2006-02-15 04:45:25 | | 3 | Abu Dhabi | 101 | 2006-02-15 04:45:25 | | 4 | Acua | 60 | 2006-02-15 04:45:25 | | 5 | Adana | 97 | 2006-02-15 04:45:25 | +---------+--------------------+------------+---------------------+ 5 rows in set (0.00 sec) 1 2 3 4 5 6 7 8 9 10 11 12 dim0 @ testing101 : ~ $ cat / tmp / tee .log mysql > select * from city limit 5 ; + -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- + -- -- -- -- -- -- + -- -- -- -- -- -- -- -- -- -- - + | city_id | city | country_id | last_update | + -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- + -- -- -- -- -- -- + -- -- -- -- -- -- -- -- -- -- - + | 1 | A Corua ( La Corua ) | 87 | 2006 - 02 - 15 04 : 45 : 25 | | 2 | Abha | 82 | 2006 - 02 - 15 04 : 45 : 25 | | 3 | Abu Dhabi | 101 | 2006 - 02 - 15 04 : 45 : 25 | | 4 | Acua | 60 | 2006 - 02 - 15 04 : 45 : 25 | | 5 | Adana | 97 | 2006 - 02 - 15 04 : 45 : 25 | + -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- + -- -- -- -- -- -- + -- -- -- -- -- -- -- -- -- -- - + 5 rows in set ( 0.00 sec )

Whenever, you have only access to the MySQL interface and you need to access one of your created files to see what the output is, you can do so using the ! keystroke, which will execute system commands.

Running a shell command from the MySQL command line interface:

mysql> ! cat /tmp/tee.log mysql> select * from city limit 5; +---------+--------------------+------------+---------------------+ | city_id | city | country_id | last_update | +---------+--------------------+------------+---------------------+ | 1 | A Corua (La Corua) | 87 | 2006-02-15 04:45:25 | | 2 | Abha | 82 | 2006-02-15 04:45:25 | | 3 | Abu Dhabi | 101 | 2006-02-15 04:45:25 | | 4 | Acua | 60 | 2006-02-15 04:45:25 | | 5 | Adana | 97 | 2006-02-15 04:45:25 | +---------+--------------------+------------+---------------------+ 5 rows in set (0.00 sec) 1 2 3 4 5 6 7 8 9 10 11 12 mysql > ! cat / tmp / tee .log mysql > select * from city limit 5 ; + -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- + -- -- -- -- -- -- + -- -- -- -- -- -- -- -- -- -- - + | city_id | city | country_id | last_update | + -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- + -- -- -- -- -- -- + -- -- -- -- -- -- -- -- -- -- - + | 1 | A Corua ( La Corua ) | 87 | 2006 - 02 - 15 04 : 45 : 25 | | 2 | Abha | 82 | 2006 - 02 - 15 04 : 45 : 25 | | 3 | Abu Dhabi | 101 | 2006 - 02 - 15 04 : 45 : 25 | | 4 | Acua | 60 | 2006 - 02 - 15 04 : 45 : 25 | | 5 | Adana | 97 | 2006 - 02 - 15 04 : 45 : 25 | + -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- + -- -- -- -- -- -- + -- -- -- -- -- -- -- -- -- -- - + 5 rows in set ( 0.00 sec )

status

In some cases you’d like seeing the parameters currently active on your MySQL client. Therefore you can actually use the s command. This command will clarify which of the options are active on the client. The info which is shown should not be confused with SHOW VARIABLES. which is focussed on the connection variables.

Trigger the status information of your connection using s:

mysql> s> -------------- mysql Ver 14.14 Distrib 5.6.15, for Linux (x86_64) using EditLine wrapper Connection id: 13149 Current database: sakila Current user: root@localhost SSL: Not in use Current pager: stdout Using outfile: '' Using delimiter: ; Server version: 5.6.15-63.0-log Percona Server (GPL), Release 63.0 Protocol version: 10 Connection: 127.0.0.1 via TCP/IP Server characterset: latin1 Db characterset: latin1 Client characterset: utf8 Conn. characterset: utf8 TCP port: 3306 Uptime: 10 days 23 hours 32 min 57 sec Threads: 1 Questions: 1203169 Slow queries: 43745 Opens: 626 Flush tables: 1 Open tables: 178 Queries per second avg: 1.268 -------------- 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 mysql > s > -- -- -- -- -- -- -- mysql Ver 14.14 Distrib 5.6.15 , for Linux ( x86_64 ) using EditLine wrapper Connection id : 13149 Current database : sakila Current user : root @ localhost SSL : Not in use Current pager : stdout Using outfile : '' Using delimiter : ; Server version : 5.6.15 - 63.0 - log Percona Server ( GPL ) , Release 63.0 Protocol version : 10 Connection : 127.0.0.1 via TCP / IP Server characterset : latin1 Db characterset : latin1 Client characterset : utf8 Conn . characterset : utf8 TCP port : 3306 Uptime : 10 days 23 hours 32 min 57 sec Threads : 1 Questions : 1203169 Slow queries : 43745 Opens : 626 Flush tables : 1 Open tables : 178 Queries per second avg : 1.268 -- -- -- -- -- -- --

clear

Clearing your current input query. Use c to clear the input field:

mysql> SELECT * -> FROM city -> c mysql> 1 2 3 4 mysql > SELECT * -> FROM city -> c mysql >

pager

Honestly one of the more useful tools in the mysqlclient is actually pager. For people prone to typing queries while forgetting adding a limit if they don’t need to view the full output.

‘P less’ will output the query data using the UNIX command less.

You can also choose to output the query results in a parsable format on the filesystem using ‘P cat > voila.log’.

for example:

mysql> P cat > /tmp/voila.log PAGER set to 'cat > /tmp/voila.log' mysql> SELECT * FROM city; 600 rows in set (0.01 sec) 1 2 3 4 mysql > P cat > / tmp / voila .log PAGER set to 'cat > /tmp/voila.log' mysql > SELECT * FROM city ; 600 rows in set ( 0.01 sec )

This will create the file ‘voila.log’ in which only the output is saved of the query. This solution mimics the ‘SELECT INTO OUTFILE’ query.

During optimisation of your workload, it can be interesting to see if a query you’ve modified has the same query output

mysql >P md5sum PAGER set to 'md5sum' root@127.0.0.1 [sakila]>select * from city limit 10; 449d5bcae6e0e5b19e7101478934a7e6 - 10 rows in set (0.00 sec) mysql >select city_id, city, country_id, last_update FROM city LIMIT 10 ; 449d5bcae6e0e5b19e7101478934a7e6 - 10 rows in set (0.00 sec) 1 2 3 4 5 6 7 8 9 mysql > P md5sum PAGER set to 'md5sum' root @ 127.0.0.1 [ sakila ] > select * from city limit 10 ; 449d5bcae6e0e5b19e7101478934a7e6 - 10 rows in set ( 0.00 sec ) mysql > select city_id , city , country_id , last_update FROM city LIMIT 10 ; 449d5bcae6e0e5b19e7101478934a7e6 - 10 rows in set ( 0.00 sec )

Other commands



There are of course several other options you might use. You can get an overview of all the internal cli functions through ?.

Disabling the tee and pager commands described in this blogentry can be done with t or notee, n or nopager.