I just installed the Everything engine on my own site, and I noticed that it was putting quite a bit of stress on MySQL. I kept checking `mysqladmin -p status` every few minutes to see how bad the damage was.

But then I figured that I might as well make something in Perl to do this for me, and make it accessible through a browser. So I came up with this.
Simply put the following two files in $WWWROOT/mysqlstatus or something. The .pl file can be named whatever, but the second file needs to be named "mysqltable.html" and placed in the same directory.

So, here's the code.


mysqlstatus.pl 
#!/usr/bin/perl
use DBI;
use CGI;

my $q = new CGI;

select(STDERR);
$|++;
select(STDOUT);


##############
my %desc;
my $data;

{
    local $/ = undef;
    open(DATA, "mysqltable.html");
    $data = <DATA>;
}

$data =~ s/<(?:TR|TD|\/TD|\/TR|TABLE|TBODY|\/TABLE|\/TBODY)>//g;

my @lines = split "\n\n", $data;
my $key;
foreach my $val (@lines) {
        $val =~ s/<CODE>(.*)<\/CODE>/$key=$1;"";/e;
        $desc{$key} = $val if $key;
}
###############

my $rootpwd = $q->param('rootpwd');

if($rootpwd) {
    status();
} else {
    login();
}



sub login {
    my $wasproblem = shift;
    my $html = "<html><head><title>MySQLStatus</title></head><body><h1
+>MySQLStatus</h1>";
    $html .= "<font color=red><b>$wasproblem</b></font><br>" if $waspr
+oblem;
    $html .= "In order to view the status of your MySQL server, please
+ enter the MySQL root password.<br>"
          . "<form action='mysqlstatus.pl' method=post><b>MySQL root p
+assword:</b> <input type=text name=rootpwd>"
          . "<br><input type=submit value='Log in'></form></body></htm
+l>\n";
    print $html;

    exit;
}


sub status {
    my $dbh = DBI->connect('dbi:mysql:mysql:localhost:3306', 'root', $
+rootpwd)
        or login($DBI::errstr);
    my $sth  = $dbh->prepare("SHOW STATUS;");
    $sth->execute();

    my($key, $val, $bgcolor);
    my $color = 0;

    print "<html><head><title>MySQLStatus</title><base href=\"http://w
+ww.mysql.com/doc/\"></head><body><h1>MySQL Server on localhost:3306</
+h1><table>";

    while(($key, $val) = $sth->fetchrow_array()) {
        $bgcolor = ($color++ % 2? "#BECDDA" : "#DEE6ED");
        print "<tr bgcolor='$bgcolor'><td>$key</td><td>$val</td><td>$d
+esc{$key}</td></tr>";
    }

    print "</table></body></html>";
}

[download]


mysqltable.html
Please download this file from http://aboutpcs.com/mysqlstatus/mysqltable.html and place it in the same folder as the script above, named "mysqltable.html" Because this HTML file has <code> tags in it, I can't post it here. :(

The code is by me, and the docs for each statistic (mysqltable.html) were ripped from mysql.com.

This has worked perfectly for me, so far. I would be interested in hearing if any of you have any issues with it.

Enjoy!

Quinn Slack
perl -e 's ssfhjok qupyrqs&&tr sfjkohpyuqrspitnrapj"hs&&eval&&s&&&'

Replies are listed 'Best First'.
Re: MySQL Status Tool
by RMGir (Prior) on Apr 15, 2002 at 16:23 UTC
    Looks interesting...

    A few "rote" questions spring to mind, though.

    • No -T?
    • No taint check on $rootpwd, even without -T?
    • The root password will be flying across the wire in cleartext, you know that, right?
    • No -w, or use strict?
    Aside from that, looks interesting. The taint checking may be needless paranoia, but I'm not sure, since I don't know how the 3rd arg to DBI->connect gets validated. So I'd rather be paranoid...


    --
    Mike

[reply]
      As I was developing it, I ran it like this:

      perl -TwMstrict mysqlstatus.pl

      [download]


      So, it *is* strict, warning, and taint compatible.

      There's no other way to send the root password through HTTP that's completely compatible with all browsers and that doesn't require SSL, as far as I know. If there is a way, I would love to hear it!

      You bring up a good point about the root password argument. I do not think that you could set it to "drowssap; drop mysql;" and erase a database. The only thing that could go wrong is a bug in MySQL of the buffer overflow sort, and I have no control over those sorts of things. It's really meant to be used by an admin, and it can even be used with HTTP Auth security so as to disallow potential attackers even the privilege of having a form to validate potential root passwords. :)

      Quinn Slack
      perl -e 's ssfhjok qupyrqs&&tr sfjkohpyuqrspitnrapj"hs&&eval&&s&&&'
[reply]
[d/l]
        Cool... Why not leave the -Tw and use strict; on in the release version, in case of feature creep?

        Short of using javascript to encrypt the password on the client side, with a random "salt" generated from the server each time the page is issued, I don't see a lot of great solutions.

        In any case, make SURE you never use such an app outside your corporate network, and even there, realize that malicious insideers could wind up with your mySQL root passwd...
        --
        Mike

[reply]
        I might be missing something here, but... maybe you should differentiate the two functions that are currently served by the password: (1) validating the person posting the request vs. (2) logging into the database. If your DB status report includes stuff that you don't want to make public (and/or you don't want the public pinging your DB at will), then do something for validation -- whatever's workable and adequate -- that does not involve sending the login password.

        Put the actual DB login password in the code itself -- actually, in a module or "require" file that's readable when the script executes under the web server, but is not directly under $webhome.

        Warning: I haven't researched this idea to figure out how vulnerable it might be (if anyone has facts or data on this, I'd love to hear it), but it seems safer than having ( $user_validation eq $rootlogin ) going through sniffable switches, even granting that it's only "DB root" that we're talking about here (I hope that's all you're talking about).

[reply]

Back to Cool Uses for Perl