MySQL who: See who is connected to your MySQL server with this script

February 29, 2016

Need to determine who is connected to your MySQL server and how many connections they have open? I tossed together this little script to display the information.

[bash]
#!/usr/bin/perl

use DBI;
use 5.010;
use Getopt::Long;

# Set defaults
my $host = ‘localhost’;
my $db = ‘information_schema’;
my $user = ”;
my $pass = ”;

# Get commandline options
GetOptions (
"host=s" => \$host,
"db=s" => \$db,
"user=s" => \$user,
"pass=s" => \$pass,
) or die ( "Valid options\n–host\n–user\n–password\n–db database" );

# Create database connection
my $dbh = DBI->connect( "DBI:mysql:$db:$host", $user, $pass, {‘PrintError’=>0} )
or die "** Connection error!\nTry different options:\n–host\n–user\n–password\n–db database\n\n $DBI:: +++errstr\n";

# Get the current processes to check user
my $sql = "show processlist";
my $sth = $dbh->prepare($sql);
$sth->execute() or die "SQL error: $DBI::errstr\n";

# Create an array of all the users
my %user;
while (my @row = $sth->fetchrow_array()) {
$user{$row[1]}++;
}

# Display User details
print "MySQL – Logged in users\n";
print "=" x 15, "\n";

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

print "\n";
[/bash]