Be productive with the MySQL command line

Be productive with the MySQL command line

PREVIOUS POST
NEXT POST

Even if you are using a GUI tool to connect to your MySQL servers, one day or another, you will have to deal with the command line. So it is nice to know a few tips that can really make your work easier.

Note: The commands below are only available for Unix/Linux.

Using pager

Most of the graphical tools paginate results, which is very handy. But this is not the way the command line client works: it just outputs all results. It can be annoying but it is easily solved by using the pager command:

Another example of the pager command is when you want to estimate a good size for you InnoDB redo logs: the estimation is based on the variation of the Log Sequence Number during a given period of time. Instead of manually looking for the right line in the output of SHOW ENGINE INNODB STATUS (which can be huge), you can call pager to the rescue:

When you are done and you want to disable paging, you can simply run:

Using edit

When you try to optimize a query, it often involves manipulating the text of the query, and sometimes it would be great to have a text editor inside the client. Well, this can be achieved by using the edit command.

Let’s say you have the following query:

and let’s say you want to change the left joins to inner joins and use capital letters for reserved SQL words. Instead of manually editing the statement, which will be boring, you simply call edit:

and it will open your default text editor with the text of the last query. The default text editor is vi, so you now have the power of vi inside the mysql client!
Once you have made your changes, save and exit the editor: you are back in the mysql client where you can type ; or \G to execute the query.

Using tee

In some situations, like when you are testing a set of commands to write documentation or when you are in the middle of an emergency, you want to be able to record the queries that you have executed. The command line client offers the tee command, which will log to a file the statements you typed and their output, pretty much like the Unix tee command:

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

Conclusion

The mysql command line client is not as glossy as most of the graphical tools, but if you know some of its hidden features, it can be very powerful. If you enjoyed these tips, I will write another post with other useful but overlooked features.

PREVIOUS POST
NEXT POST

Share this post

Comments (17)

  • Nikolay Mihaylov Reply

    and dont forget \G

    December 21, 2012 at 9:14 am
  • Justin Swanhart Reply

    Also you can use readline shortcuts in Linux (won’t work in Windows):
    CTRL-r (search backward in history)
    CTRL-a (go to end of line)
    CTRL-e (go to start of line)
    CTRL-w (delete word before cursor)
    META-f (go forward one word)
    etc..

    \? shows help

    \n or \P alone will turn off the pager depending on your client

    source (\.) will execute an SQL file

    prompt – you can change the prompt. this is useful in you .my.cnf. You can make root’s prompt read with ANSI escape sequences for example.

    December 21, 2012 at 9:45 am
  • Justin Swanhart Reply

    That should have read:
    prompt – you can change the prompt. this is useful in your .my.cnf. You can make root’s prompt RED with ANSI escape sequences for example.

    December 21, 2012 at 9:46 am
  • Bill Karwin Reply

    I sometimes set my pager to pt-visual-explain.

    December 22, 2012 at 12:09 pm
  • Kolbe Kegel Reply

    Justin,

    Sadly, the readline shortcuts don’t quite work with the MySQL 5.6 client. Oracle appears to have chosen to start using “EditLine” instead of “readline” for their builds and the semantics for searching/scrolling/skipping through your command-line and command-line history are considerably different than in previous releases. I’ve found it to be very confusing to try to edit commands in 5.6 compared to a decade of earlier releases of MySQL. (Anyone who’s been using commercially-licensed “mysql” will feel right at home; EditLine has been used for many years in commercially-licensed builds of the MySQL Client.)

    Kolbe

    December 23, 2012 at 1:30 am
  • Ernie Souhrada Reply

    One of my personal favorites is “pager md5sum” .. I use it as a nice quick way to compare whether or not two queries (of any kind) are returning the same results.

    December 24, 2012 at 11:19 am
  • Yin Xi Reply

    I like tee, very useful

    December 26, 2012 at 2:22 am
  • Geoffrey Lee Reply

    Great tips for the MySQL CLI. Please do post more!

    December 26, 2012 at 7:53 pm
  • yuanbin Reply

    very good,tks!

    December 28, 2012 at 5:53 pm
  • Daniël van Eeden Reply

    The pager feature is nice, but not available on all platforms (windows).

    It’s possible to used md5sum/sha1sum as a pager to check if the output for two queries is the same. This could be handy after rewriting a query.

    December 29, 2012 at 4:15 am
  • Cédric Reply

    This one is nice too : http://mysql.wisborg.dk/2012/12/25/merry-christmas/
    Happy 2013.
    Bye

    January 11, 2013 at 12:17 pm
  • marc Reply

    Alittle late to this post, but I don’t know how I ever lived without knowing about ‘edit’. Great tip!

    January 22, 2013 at 12:42 pm
  • kan Reply

    useful

    January 23, 2013 at 4:34 am
  • Ramesh Kumar Reply

    Awesome.. i was facing hard time with pagination. I did knot knew about pager command. Thanks for sharing.

    February 15, 2013 at 3:05 am
  • John Reply

    Hi Stephane,

    I never knew about pager before now, thanks.

    If you are looking for a command line tool for graphing (perhaps query performance charts) I recommend sqlchart (http://sqldashboards.com/sqlchart)

    April 18, 2014 at 9:24 am
  • Goutham Reply

    Thanks Stephane. Very helpful

    October 3, 2017 at 7:44 am
  • Matt Reply

    It’s a pity that edit creates a file like: /tmp/sqlzMe6NF
    If it instead created /tmp/sqlzMe6NF.sql it would trigger vim’s sql syntax highlighting

    October 23, 2018 at 1:19 pm

Leave a Reply