Beefy Boxes and Bandwidth Generously Provided by pair Networks
There's more than one way to do things
 
PerlMonks  

Monitoring mysql tables in perl

by kamal (Sexton)
on Jan 10, 2013 at 14:34 UTC ( #1012670=perlquestion: print w/ replies, xml ) Need Help??
kamal has asked for the wisdom of the Perl Monks concerning the following question:

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.

Comment on Monitoring mysql tables in perl
Download Code
Re: Monitoring mysql tables in perl
by sundialsvc4 (Monsignor) on Jan 10, 2013 at 14:54 UTC
Re: Monitoring mysql tables in perl
by blue_cowdawg (Prior) on Jan 10, 2013 at 14:56 UTC
        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?


    Peter L. Berghold -- Unix Professional
    Peter -at- Berghold -dot- Net; AOL IM redcowdawg Yahoo IM: blue_cowdawg
      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
            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.


        Peter L. Berghold -- Unix Professional
        Peter -at- Berghold -dot- Net; AOL IM redcowdawg Yahoo IM: blue_cowdawg
Re: Monitoring mysql tables in perl
by Neighbour (Friar) on Jan 10, 2013 at 15:33 UTC
    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
Re: Monitoring mysql tables in perl
by karlgoethebier (Curate) on Jan 10, 2013 at 19:40 UTC

    Please see also:

    Best regards, Karl

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

    «The Crux of the Biscuit is the Apostrophe»

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://1012670]
Approved by blue_cowdawg
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others exploiting the Monastery: (7)
As of 2014-08-30 13:41 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    The best computer themed movie is:











    Results (293 votes), past polls