Productive work with MySQL on the command line

Even if you use GUI-tools for working with MySQL, one day you will be faced with the command line. Therefore, it is useful to know some tricks that facilitate the work.

Note: these commands only work for Unix / Linux.

Paginated output

Most GUI utilities output the result page by page, which is very convenient. But in the mysql command line, you’ll get all the results. This is easily solved with the pager command:

 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 estimate what size to set for InnoDB redo logs. Estimation of this size is based on the change in the Log Sequence Number value over a given period of time. Instead of manually searching for the desired string in the output of SHOW ENGINE INNODB STATUS (which can be quite large), you can use the 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 paged output 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 wildly lacks a text editor. This is also solved. For example, you have such a query:

 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 the LEFT joins to INNER joins and use upper case for SQL reserved words. Instead of dull manual editing, just call edit :

 mysql> edit 

and you will see a text editor containing your last request. The default editor is vi . When you are done editing, save the changes and exit the editor. You will find yourself back in the mysql client where you can enter ; or \G to run the query.

Using tee

In some situations, for example, when you are testing a set of commands when writing documentation or when you are in an emergency, you want to record the queries that you perform. The console client provides you with this option using the tee command, which 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 “know how to prepare it,” it can be very powerful.