MySQL pager

There are a vast amount of tips and trick ode to the MySQL command line interface. One thing you'll be quick to notice with MySQL's client program is that the stdout formatting isn't always as readable as you may want. Also the fact that when viewing the list of variables set on the server or the statuses you'll surely find yourself scrolling and searching for the values you're specifically seeking. Well take the pain out of listing by making use of the mysql pager functionality. I'm not sure the right word for it, 'macro'?

From your mysql prompt change your pager by issuing the likes of;

Step through the INNODB STATUS output

mysql> pager more
mysql> SHOW ENGINE INNODB STATUS \G
...

grep-ing your output;

Sleeping Connections

mysql> pager grep -i sleep
PAGER set to 'grep -i sleep'
mysql> show processlist;
| 152 | squeezecenter | localhost | squeezecenter | Sleep | 36 | | NULL |
3 rows in set (0.02 sec)

Grouping variables/status counters together for easy eye-balling

mysql> pager grep -i 'key'
PAGER set to 'grep -i key'
mysql> SHOW GLOBAL STATUS;
| Com_assign_to_keycache | 0 |
| Com_preload_keys | 0 |
| Com_show_keys | 0 |
| Handler_read_key | 611 |
| Key_blocks_not_flushed | 0 |
| Key_blocks_unused | 14343 |
| Key_blocks_used | 10 |
| Key_read_requests | 4197 |
| Key_reads | 13 |
| Key_write_requests | 144 |
| Key_writes | 140 |
291 rows in set (0.03 sec)

There is the potential to do some really cool things with this;

Using Maatkit/Percona toolkit or custom scripts

mysql> pager mk-visual-explain
PAGER set to 'mk-visual-explain'
mysql> EXPLAIN select a.title, c.name from albums a join contributors c on a.contributor = c.id where c.name LIKE '%Monkeys%';
JOIN
+- Filter with WHERE
| +- Bookmark lookup
| +- Table
| | table c
| | possible_keys PRIMARY
| +- Unique index lookup
| key c->PRIMARY
| possible_keys PRIMARY
| key_len 4
| ref db.a.contributor
| rows 1
+- Table scan
rows 483
+- Table
table a
2 rows in set (0.00 sec)

Don't forget to revert to normal;

mysql> nopager
PAGER set to stdout