List Logged In MySQL Users using Perl DBI

I saw this question on Stackoverflow.com.  The user wanted to programatically list all users that are connected to MySQL database server.

He was using the SQL statement "show processlist;".  This is a very useful statement which lists all MySQL connections with additional information.



Here is a snippet of how it appears:

mysql> show processlist;
+---------+----------+-----------+----------+---------+------+-------+------------------+
| Id      | User     | Host      | db       | Command | Time | State | Info             |
+---------+----------+-----------+----------+---------+------+-------+------------------+
| 1075602 | ndoutils | localhost | nagios   | Sleep   |    0 |       | NULL             |
| 1399040 | root     | localhost | NULL     | Query   |    0 | NULL  | show processlist |
| 1400766 | ndoutils | localhost | nagios   | Sleep   |   40 |       | NULL             |
| 1400768 | ndoutils | localhost | nagios   | Sleep   |   40 |       | NULL             |
| 1400769 | nagiosql | localhost | nagiosql | Sleep   |   40 |       | NULL             |
+---------+----------+-----------+----------+---------+------+-------+------------------+

Not so friendly for reporting purpose, eh!  This is where the friendly Perl DBI module comes in.

#!/usr/bin/perl

use strict;
use warnings;
use DBI;

# authentication information
my $user = "root";
my $pass = "password";

my $db = "information_schema";
my $sql = "show processlist";

# attempt a connection to MySQL server running locally
my $dbh = DBI->connect("DBI:mysql:$db", $user, $pass)
        or die "Connection error: $DBI::errstr\n";

my $sth = $dbh->prepare($sql);
$sth->execute() or die "SQL error: $DBI::errstr\n";


# declare a hash to hold all logged-in user names
my %user;

while (my @row = $sth->fetchrow_array()) {
        # the second row lists logged in users
        $user{$row[1]}++;
}

print "MySQL - Logged in users\n";
print "=" x 15, "\n";

for my $x (sort keys %user) {
        print "$x - $user{$x} connections\n";
}

The final output is as follows:

MySQL - Logged in users
===============
nagiosql - 4 connections
ndoutils - 5 connections
root - 1 connections