perl下mysql同步监控

写了个perl下监控mysql同步状态的脚本，利用163的邮局进行发送邮件，避免本机用sendmail发送时出现问题，可以本机监控，也可以远程。

    在同步异常时候，将日志写入/var/log/mysql_check_log文件，并将Slave_IO_Running，Slave_SQL_Running，Seconds_Behind_Master 3个状态值发送到手机，顺便写入log文件，代码如下：


#!/usr/bin/perl

# Use mandatory external modules
use strict;
use warnings;

use DBI;
use Net::SMTP_auth;
use Time::HiRes;
use POSIX "strftime";


my $host     = '192.168.200.2';
my $user     = 'test';
my $pass     = 'test';
my $port     = '3306';
my $max_behind_m = 120;
my $check_log = '/var/log/mysql_check_log';


my $mailhost = 'smtp.163.com';
my $mailfrom = 'xxxxxx@163.com';
my @mailto   = '159xxxxxx@139.com';
my $subject  = "mysql_repl_error::$host";
my $text;
my $mailuser   = 'xxxxx';
my $mailpasswd = 'xxxxxx';


# open  log--file
open FH, ">> $check_log" or die $!;


# connect to servers (this)
my $this_dbh = &MysqlConnect( $host, $port, $user, $pass );
print FH "ERROR: Can't connect to MySQL (host = $host:$port, user = $user)!"
  if ( !$this_dbh );


# Get slave info
my $slave_status = &MysqlQuery( $this_dbh, "SHOW SLAVE STATUS" );
print FH "ERROR: SQL Query Error: " . $this_dbh->errstr unless ($slave_status);

my $Slave_IO  = $slave_status->{Slave_IO_Running};
my $Slave_SQL = $slave_status->{Slave_SQL_Running};
my $Seconds_Behind_Master = $slave_status->{Seconds_Behind_Master};

print "IO:\t\t $Slave_IO\n";
print "SQL:\t\t $Slave_SQL\n";
print "Behind_Master:\t $Seconds_Behind_Master\n";


# Send to Mail
if ( ( $Slave_IO eq 'Yes' ) and ( $Slave_SQL eq 'Yes' ) and ( $Seconds_Behind_Master < $max_behind_m ) ) {
    print  "mysql replicate is OK" . "\n";
}
else {
    print FH "-" x 80 . ">", "\n";
    my $start_time = ¤t_time();
    print FH "start at $start_time\n";

    print FH "mysql replicate is Fail!!!!!!!!!!!!" . "\n";
    print FH "IO:\t\t $Slave_IO\n";
    print FH "SQL:\t\t $Slave_SQL\n";
    print FH "Behind_Master:\t $Seconds_Behind_Master\n";

    my $end_time = ¤t_time();
    print FH "end   at $end_time\n";
    close (FH);

    $text = "$Slave_IO, $Slave_SQL, $Seconds_Behind_Master";

    &SendMail();
}



#-----------------------------------------------------------------
sub MysqlConnect() {
    my ( $host, $port, $user, $pass ) = @_;

    my $dsn = "DBI:mysql:host=$host;port=$port";
    return DBI->connect( $dsn, $user, $pass, { PrintError => 0 } );
}

#-----------------------------------------------------------------
sub MysqlQuery($$) {
    my ( $dbh, $query ) = @_;

    my $sth = $dbh->prepare($query);
    my $res = $sth->execute;
    return undef unless ($res);

    my $row = $sth->fetchrow_hashref;
    $sth->finish;

    return $row;
}


#-----------------------------------------------------------------
sub current_time() {
   
    my $time_now = POSIX::strftime("[%Y-%m-%d %H:%M:%S]", localtime);
    return $time_now;
}


#-----------------------------------------------------------------
sub SendMail() {

    my $smtp = Net::SMTP_auth->new( $mailhost, Timeout => 120, Debug => 1 )
      or die "Error.\n";
    $smtp->auth( 'LOGIN', $mailuser, $mailpasswd );

    foreach my $mailto (@mailto) {
        $smtp->mail($mailfrom);
        $smtp->to($mailto);
        $smtp->data();
        $smtp->datasend("To: $mailto\n");
        $smtp->datasend("From:$mailfrom\n");
        $smtp->datasend("Subject: $subject\n");
        $smtp->datasend("\n");
        $smtp->datasend("$text\n");
        $smtp->dataend();
    }

    $smtp->quit;
}