Neat Tricks for the MySQL Command-line Pager

How many of you use the MySQL command-line client?  And did you know about the pager command you can give it?  It’s pretty useful.  It tells MySQL to pipe the output of your commands through the specified program before displaying it to you.

MySQL Command-line Pager

Here’s the most basic thing I can think of to do with it: use it as a pager. (It’s scary how predictable I am sometimes, isn’t it?)

For big result sets, it’s a pretty handy way to be able to search and scroll through. No mouse required, of course.

But it doesn’t have to be this simple! You can specify anything you want as a pager. Hmm, you know what that means? It means you can write your own script and push the output through it. You can’t specify arguments to the script, but since you can write your own, that’s not really a limitation. (Edit: I’m wrong! You can. See Giuseppe’s comment below.) For example, here’s a super-simple script that will show the lock waits in the output of SHOW INNODB STATUS. Save this file as /tmp/lock_waits and make it executable.

Now in your mysql session, set /tmp/lock_waits as your pager and let’s see if there are any lock waits:

Pretty useful, isn’t it? But we can do even more. For example, the Maatkit tools are specifically designed to be useful at the command line in the traditional Unix pipe-and-filter manner. What sort of goodies can we think of here?

Now, that’s handy.

What are your favorite ideas?