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:
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:
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
===============
nagiosql - 4 connections
ndoutils - 5 connections
root - 1 connections
0 Comments:
Post a Comment