Pager

No, not the kind that wakes you up a 2am. The kind that lets you easily slice and present data from the mysql client in a more managable form. This little known feature of the mysql client is one of my favoriate and has helped me slice through large process lists and page through result sets for years.

Basically the pager command allows you to attach commands to the output of the mysql client to manipulate the output just like pipes in bash. Do you hate wading through all those sleeping connections to find the important ones? Yeah me too:

Normal Processlist:

mysql> show processlist;
+----+------+-----------+------+---------+------+-------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+-----------+------+---------+------+-------+------------------+
| 5 | root | localhost | NULL | Query | 0 | NULL | show processlist |
| 6 | root | localhost | NULL | Sleep | 3 | | NULL |
+----+------+-----------+------+---------+------+-------+------------------+
2 rows in set (0.00 sec)

mysql>

The new way:

mysql> pager grep -v Sleep
PAGER set to 'grep -v Sleep'
mysql> show processlist;
+----+------+-----------+------+---------+------+-------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+-----------+------+---------+------+-------+------------------+
| 5 | root | localhost | NULL | Query | 0 | NULL | show processlist |
+----+------+-----------+------+---------+------+-------+------------------+
2 rows in set (0.00 sec)

Of course this isn’t fool proof. If you happen to have a table called Sleep or some field in a query is called Sleep it will also be stripped out. Since anything longer than pager grep or page less is too much to type on the command line each time pager can also be set from ~/.my.cnf. This makes it possible to write a wicked filtering script and have mysql execute it each time by setting pager=perl -e wicked_filter_script.pl If someone feels like writing a script to do syntax highlighting of mysql cli output I would gladly use it ;)

To reset the pager just type nopager*enter*

mysql> nopager
PAGER set to stdout

3 Comments

  1. After all these years using MySQL I have never seen this in action (that I can remember), but your example is something that will save a ton of time in finding those queries causing problems that seems to scroll by.

    In most cases I end up doing something at the Unix shell like:
    shell> mysql -e “show processlist” | less

    Thanks for the tip.

  2. NIce one.
    Just one step further. I usually pull the “pager” trick to see a large output in vim

    mysql> pager vim –
    mysql> SHOW TABLE STATUS;

    But you can also concatenate two pagers. For example, to exclude Fixed tables from SHOW TABLE STATUS and see the result in vim,

    mysql> pager grep -iv fixed | vim –

    Don’t forget to use
    :set nowrap
    in vim, so that your output will be easily readable.

  3. Chris Moore says:

    I whipped up a quickie mysql output formatter for ya.. check it out at: http://irc.lagclan.com:81/~seymour/format.phps

Leave a Reply