MySQL Recipes: Connections per hostname using Pager

Posted in: MySQL, Technical Track

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  Host
1  localhost
1  localhost

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 |
1  root    | localhost
1  User    | Host
2  xx      |
3  xx      |
3  xx      |
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!




Want to talk with an expert? Schedule a call with our team to get the conversation started.

About the Author

Database Specialist based in Sydney,Australia

No comments

Leave a Reply

Your email address will not be published. Required fields are marked *