Few days back, Baron re-introduced MySQL's command line pager command and described some cool tricks with maatkit's mk-visual-explain (one of my favorite tools). Soon after reading it, I wished if it was possible to describe regex based (on query) paging. I have written a small hack, christened mysmartpager, that can actually do regex based paging for you. The idea is simple, write a relay that will redirect the output based on to the desired pager. The problem was complex, there was no direct way of getting to know the original query. There are a couple of indirect ways of doing so, but of course with hurdles:
- Run mysql client with --xml option: This will print the output of each command in xml and the command itself is included in the xml. The downside was not many pagers (including mk-visual-explain) understand the xml format and parsing the xml to create a output like string was going to be costly. This will also include writing a lot of code that I don't do for a hack. :)
- Use the tee command: MySQL allows you to tee output to a file that contains everything from standard output including mysql> command... We could have used and easily take the last command using a simple grep. The problem is output is tee'd after pager exits.
- Copy mysmartpager file to your /path/to/bin directory
- Copy .mysmartpager file to your $HOME directory
- Copy mysmartpager.cnf file to your MySQL's config directory and make sure it is included.
- Make appropriate changes in mysmartpager.cnf file
- More regexs (perl regex's can be written in .mysmartpager)