Chinaunix首页 | 论坛 | 博客
  • 博客访问: 2153938
  • 博文数量: 227
  • 博客积分: 10521
  • 博客等级: 上将
  • 技术积分: 3452
  • 用 户 组: 普通用户
  • 注册时间: 2006-10-20 14:59
个人简介

低调做人,高调做事!

文章分类

全部博文(227)

文章存档

2013年(4)

2012年(8)

2011年(16)

2010年(24)

2009年(92)

2008年(83)

分类: LINUX

2009-05-26 07:36:28

    写了个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;
}
阅读(2158) | 评论(1) | 转发(1) |
给主人留下些什么吧!~~

chinaunix网友2009-07-30 10:08:51

改成条件的话会适合。 Slave_IO_Running,Slave_SQL_Running,Seconds_Behind_Master 当这三个值有问题的再发邮件比较好。