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?)
1 2 | mysql> pager less mysql> show innodb status\G |
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.
1 2 3 | #!/bin/sh grep -A 1 'TRX HAS BEEN WAITING' |
Now in your mysql session, set /tmp/lock_waits as your pager and let’s see if there are any lock waits:
1 2 3 4 5 6 | mysql> pager /tmp/lock_waits PAGER set to '/tmp/lock_waits' mysql> show innodb status\G ------- TRX HAS BEEN WAITING 50 SEC FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 0 page no 52 n bits 72 index `GEN_CLUST_INDEX` of table `test/t` trx id 0 14615 lock_mode X waiting 1 row in set, 1 warning (0.00 sec) |
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?
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 | mysql> pager mk-visual-explain PAGER set to 'mk-visual-explain' mysql> explain select * from sakila.film inner join sakila.film_actor using(film_id) inner join sakila.actor using(actor_id); JOIN +- Bookmark lookup | +- Table | | table actor | | possible_keys PRIMARY | +- Unique index lookup | key actor->PRIMARY | possible_keys PRIMARY | key_len 2 | ref sakila.film_actor.actor_id | rows 1 +- JOIN +- Bookmark lookup | +- Table | | table film_actor | | possible_keys PRIMARY,idx_fk_film_id | +- Index lookup | key film_actor->idx_fk_film_id | possible_keys PRIMARY,idx_fk_film_id | key_len 2 | ref sakila.film.film_id | rows 2 +- Table scan rows 1022 +- Table table film possible_keys PRIMARY 3 rows in set (0.00 sec) |
Now, that’s handy.
What are your favorite ideas?
Cool tricks. My most frequent usage of “pager” is “vim -” and then manipulate the output at will.
I have a handy script that gives me the size of indexes in a database from a SHOW TABLE STATUS. (Useful when you need to calculate how much memory to allocate for a buffer).
$ cat index_size.sh
perl -lane ‘$i+= $F[17];END{print $i}’
mysql> pager ./index_size.sh
PAGER set to ‘./index_size.sh’
mysql> show table status;
71680
mysql> nopager
Giuseppe
This is cool. I have known (and ignored) this earlier, my bad. But something with mk-visual-explain (one of my favorite tools) is just great.
Next, I would like to see some regex based piping that I can specify in my.cnf, something like
pager ^explain = mk-visual-explain
pager ^show engine = less
pager .* = stdout
I dont think I’m asking too much 🙂
—
Parvesh
This is one I use a bit, requires a Unix/Linux OS though. You could do similar on MySQL on Windows.
pager grep -v “Sleep” |sort -n -t “|” -k 7 |tail
show processlist will now suppress sleeping sessions and display the top 10 oldest sessions.
Thanks for the tip on passing any script… very nice. I have interesting tests to try out… or just use mytop eh?
Have Fun
Paul
Parvesh, you can have your cake and eat it too.
superpager.bash:
#!/bin/bash
teefile=~/.mysqltee
goback=100
# find the last ^mysql>
lastcmd=$(tail -n $goback $teefile | grep “^mysql> “|tail -1| awk -F”mysql> ” ‘{print $2}’|sed -e ‘s/\*/\\*/g’)
echo lastcmd=$lastcmd
if echo “$lastcmd” | egrep -qi “^\s*explain” ; then
exec mk-visual-explain
fi
if echo “$lastcmd” | egrep -qi “^\s*show\s+engine” ; then
fgrep -C3 -i “LOCK” | less
exit
fi
less
One has to do a \T ~/.mysqltee before the \P superpager.bash , but thats nice because everything you do gets logged..
Works reasonably well for something I hacked together with bash in 10 minutes. Would be nice if the pager could be passed args like “last command” but this is a decent enough hack until they implement that in version 6.1 of mysql. 😉
I use pager for two things:
1) Save the results of the last query
2) Replace EXPLAIN with mk-visual-explain
Unfortunately, MySQL 5.0.51a and earlier writes to the tee file AFTER the pager has completed (and to .mysql_history only after you exit mysql), so getting the last command is impossible via these methods (however, it works if you are willing to issue queries twice …). You *can* still override commands (EXPLAIN, for example) by having scripts that error if the input does not conform to a standard; an example follows:
#!/usr/bin/perl
use strict;
use warnings FATAL => ‘all’;
use English qw ( -no_match_vars );
my $out;
while ( my $line = ) {
$out .= $line;
}
# Log results to a file (always useful)
# But only ever save one 🙂
open (PAGER_LOG, ‘>/tmp/mysql_pager.log’);
print PAGER_LOG “$out\n”;
close (PAGER_LOG);
# See if it an EXPLAIN
eval { local $SIG{‘__WARN__’}; system(‘/usr/local/bin/mk-visual-explain /tmp/mysql_pager.log 2> /dev/null’); };
if ($?) {
print $out;
}
gah. The scrubber changed:
while ( my $line = <STDIN>)
to
while ( my $line = )
pager less -niSFX
Check my post http://optimmysql.blogspot.com/2008/07/mysql-command-line-pager-mysmartpager.html for a small hack that can do regex based paging.
All – these are a nice set of improvements when using the command-line mysql. I took this a few steps further when looking more into mytop by Jeremy Zawodny. Want to see more – browse to th blog 🙂 http://kbcmdba.blogspot.com/2009/01/easy-editing-with-mysql-command-line.html
Just look for mysql pager info on: http://linux-sysadmin.org/2010/08/mysql-pager-filter-your-output/
Good stuff.
Is there a way to set vi-style command editing for the mysql command line client? That would be super useful.
cat >> ~/.bashrc << EOF
alias mysql="mysql –pager='less -niSFX'"
EOF
On *nix systems, before your sql statement enter:
\P more
or
\P less
> pager column -t | less -S
meaning you don’t have to look at wrapped rows, you can just scroll right and left
Actually, I correct myself. 2 nice options for aligned nowrap browsing
mysql> pager less -S
and
mysql> pager vim -R -c “set nowrap” –