Monitoring mysql tables in perl

[kamal]

So here is what i want to do: Monitor a bunch of tables in a MySQL database, append time stamps to WHEN the values changed, and then render the result in a graph.

1. Is there a tool/utility that somewhat does this already ( I could not find it ) 2. i used DBI, and was able to connect to the database and get some table column values:

#!/usr/bin/env perl

use strict;
use warnings;
use Net::SSH::Perl;
use DBI;

my $database = 'co_sysdev';
my $server = 'devqa_dbm.corp';
my $user = 'username';
my $passwd = 'password';
my $row = "";
my $homologs = DBI->connect("dbi:mysql:database=co_sysdev;host=devqa_d
+bm.corp;port=3310",'username','password');
# prepare an SQL statement

#my $query_params = "select * from system_params";
# render the result in a graph format
# save it locally in a csv format, to be used later for report
my $query_coding_event = "select * from coding_event";
my $query_params = "select * from co_event";
#my $query = "select * from n_batch where status = 'started'";
#my $query = "select * from n_batch"; 
#my $query = "select id,customer_id,location_id,submitter,n_notes,hand
+le from submission_batch where id is not null"; 
#my $query = "select id,customer_id,location_id,submitter,n_notes,hand
+le from submission_batch where id is not null"; 
#my $query = "describe submission_batch"; 
#my $query = "show tables";
my $sql_params = $homologs->prepare($query_params);

# execute an SQL statement

$sql_params->execute();

# retrieve and print results

while (my $row = $sql_params->fetchrow_arrayref) {
    #print join("\t", @$row), "\n";
    print join(@$row), "\n";
    print @$row . " \n";
}


# Break connection with MySQL database

$homologs->disconnect;

exit;

This seems to be such a common use case, that i am surprised there arnt many scripts/packages available for the same.

[blue_cowdawg]

So here is what i want to do: Monitor a bunch of tables in a MySQL database, append time stamps to WHEN the values changed, and then render the result in a graph.

Dear Monk,
I'm a bit confused as to what you are trying to do. Let me step through your code though and offer some commentary.

my $database = 'co_sysdev';
my $server = 'devqa_dbm.hq.co.corp';
my $user = 'web';
my $passwd = '3br!';
my $row = "";

Hope those aren't really production passwords. Never post such to this site as Google does a fine job of spidering this site from time to time making this sort of thing easy to find.

my $homologs = DBI->connect("dbi:mysql:database=co_sysdev;host=devqa_d
+bm.corp;port=3310",'username','password');

So.. what were those other lines for?

#my $query_params = "select * from system_params";
# render the result in a graph format
# save it locally in a csv format, to be used later for report
my $query_coding_event = "select * from coding_event";
my $query_params = "select * from co_event";
#my $query = "select * from n_batch where status = 'started'";
#my $query = "select * from n_batch"; 
#my $query = "select id,customer_id,location_id,submitter,n_notes,hand
+le from submission_batch where id is not null"; 
#my $query = "select id,customer_id,location_id,submitter,n_notes,hand
+le from submission_batch where id is not null"; 
#my $query = "describe submission_batch"; 
#my $query = "show tables";
my $sql_params = $homologs->prepare($query_params);

when posting code to the PM remember to remove unnecessary lines for readability. This is what it should have looked like IMHO:

my $query_coding_event = "select * from coding_event";
my $query_params = "select * from co_event";

my $sql_params = $homologs->prepare($query_params);

so we can see what you are trying to run.

while (my $row = $sql_params->fetchrow_arrayref) {
    #print join("\t", @$row), "\n";
    print join(@$row), "\n";
    print @$row . " \n";
}

Let's start with the line print join(@row),"\n";. Is this doing what you expect? What is going to happen is join is going to take the first element in your array and use it for a separator for the rest of the array. Here's an example of what I am saying:

$ perl -e 'print join(qw/a b c d e f/),"\n"'
bacadaeaf

Not sure that's what you were after. Then you do print $@row . "\n";, why? Looks to me like you are repeating yourself.
i used DBI, and was able to connect to the database and get some table column values:
So.. what are you trying to do with those values? What have you tried? What was the result?

[kamal]

no those are made up usernames and passwords, and this is not for production. as far as what i am trying to do is to get reuslts like: staus| time s 10:55 k 12:20 e 13:10 so the table contains char's which change as a result of some other process, i just want to be able to store these values in a csv format, so i can render it in a graph. Hope this makes a bit more sense

[blue_cowdawg]

i just want to be able to store these values in a csv format, so i can render it in a graph.

Rendering an array of data into a CSV row is fairly simple. Here's one example:

#!/usr/bin/perl 
use strict;

my $data = [
   [ qw/ a b c d /],
   [ qw/ e f g h /],
   [ qw/ i j k l /]
];
my $outfile = "/path/to/my/file.csv";
open FOUT,"> $outfile" or die "$outfile: $!";

foreach my $row (@$data){
    printf FOUT "%s\n',join(",",@$row);
}
close FOUT;

There are many many other ways of doing this but there is one way.

[sundialsvc4]

http://www.nagios.com/solutions/mysql-monitoring

[Neighbour]

Or you could add a field to the table you're monitoring which does the whole thing for you (column definition follows):

`last_modified` timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAM
+P

[karlgoethebier]

Please see also:

• Nagios
• pnp4nagios
• Nagios Plugin API

Best regards, Karl

P.S: Don't bother yourself with Nagios related modules from CPAN. IMHO they are inferior.