Productive work with MySQL on the command line

Original author: Stephane Combaudon
  • Transfer
Even if you use GUI tools for working with MySQL, one day you will encounter a command line. Therefore, it is useful to know some tricks that make work easier.

Note: these commands work only for Unix / Linux.

Pagination


Most GUI utilities display the result page by page, which is very convenient. But at the mysql prompt, you get the full result. This is easily solved with the command pager:

mysql> pager more
PAGER set to 'more'
mysql> select title from sakila.film;
+-----------------------------+
| title                       |
+-----------------------------+
| ACADEMY DINOSAUR            |
| ACE GOLDFINGER              |
| ADAPTATION HOLES            |
| AFFAIR PREJUDICE            |
| AFRICAN EGG                 |
| AGENT TRUMAN                |
| AIRPLANE SIERRA             |
| AIRPORT POLLOCK             |
| ALABAMA DEVIL               |
| ALADDIN CALENDAR            |
| ALAMO VIDEOTAPE             |
| ALASKA PHANTOM              |
| ALI FOREVER                 |
| ALICE FANTASIA              |
| ALIEN CENTER                |
| ALLEY EVOLUTION             |
| ALONE TRIP                  |
| ALTER VICTORY               |
| AMADEUS HOLY                |
--Plus--

Another example of using this command is that you, for example, want to figure out what size to set for InnoDB redo logs. Estimation of this size is based on changing the Log Sequence Number value for a given period of time. Instead of manually searching for the desired line in the output SHOW ENGINE INNODB STATUS(which can be quite large), you can use pager:

mysql> pager grep sequence
PAGER set to 'grep sequence'
mysql> show engine innodb status\Gselect sleep(60);show engine innodb status\G
Log sequence number 380166807992
1 row in set (0.41 sec)
1 row in set (1 min 0.00 sec)
Log sequence number 380170274979
1 row in set (0.00 sec)

To disable pagination, run:

mysql> pager
Default pager wasn't set, using stdout.

Editing


When optimizing queries on the command line, it is very inconvenient to edit them and there is a wildly lacking text editor. This is also solvable. For example, you have this request:

mysql> select count(*) from film left join film_category using(film_id) left join category using(category_id) where name='Music';

and you want to change LEFTjoins to INNERjoins and use uppercase for the words reserved in SQL. Instead of dull manual editing, just call edit:

mysql> edit

and a text editor will open in front of you containing your last request. The default editor is vi . When you finish editing, save the changes and exit the editor. You will find yourself back in the mysql client, where you can enter ;or \Gto start the query.

Using tee


In some situations, for example, when you test a set of commands when writing documentation or when you are in an emergency, you want to record the requests that you execute. The console client provides you with this opportunity with the help of a command teethat logs all requests that you launched and their output:

mysql> tee queries.log
Logging to file 'queries.log'
mysql> use sakila
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> select count(*) from sakila;
ERROR 1146 (42S02): Table 'sakila.sakila' doesn't exist
mysql> select count(*) from film;
+----------+
| count(*) |
+----------+
|     1000 |
+----------+
1 row in set (0.00 sec)
mysql> exit

If you now look at the file queries.log, you will see a copy of your session.

Conclusion


A console client may not be as convenient as graphical tools, but if you can cook it, it can be very powerful.

Also popular now: