MySQL Recipes: Connections per hostname using Pager

Over-the-Top Tales from the Trenches.
Motto: Bringing order to the chaos of every day DBA life.

This diary has made you conversant in the world of RMAN standbys, duplicates and corruptions… exposed you to the world the hidden nature of shmmax. You have seen the nastiness of Oracle MONTHS_BETWEEN. What more worlds and mysteries are left for us to conquer? Do we weep like Alexander, or board a boat and discover America?

Actually, my aim in any posting is to make your job as a DBA in Oracle, SQLserver and MySQL easier. This is not a contest on how complex or internal we can get. This is about getting the job done, so you can get back to

1. playing with your kids/dog/partner.
2. writing articles in a blog.
3. generally not worrying about databases.

Ever wondered how to get a quick count of the number of connections per hostname from MySQL? Welcome to the arcane (but extremely powerful) world of the MySQL command line pager.

Here are couple recipes to start with. I invite you to add more to the list in the comments.

You want a report of the number of connections per hostname. (I’ve omitted the table lines from that to make it more readable.)

mysql> pager cut -d '|' -f 4 | cut -d ':' -f 1 | uniq -c | sort -n
PAGER set to 'cut -d '|' -f 4 | cut -d ':' -f 1 | uniq -c | sort -n'
mysql> show processlist;
1  172.168.xxx.xxx
1  Host
1  localhost
1  localhost
2  172.168.xxx.xxx
3  172.168.xxx.xxx
3  172.168.xxx.xxx

Ok, what about the number of connections per user per hostname?

mysql> pager cut -d '|' -f 3,4 | cut -d ':' -f 1 | uniq -c | sort -n
PAGER set to 'cut -d '|' -f 3,4 | cut -d ':' -f 1 | uniq -c | sort -n'
mysql> show processlist;
1  pythian | localhost
1  repldb2 | 172.16.xxx.xxx
1  root    | localhost
1  User    | Host
2  xx      | 172.16.xxx.xxx
3  xx      | 172.16.xxx.xxx
3  xx      | 172.16.xxx.xxx
11 rows in set (0.00 sec)

To turn off any pager settings:

mysql> pager
Default pager wasn't set, using stdout.

Here is the MySQL doc on pager.

Have fun!

Paul