写了个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;
}
perl下mysql同步监控
最新推荐文章于 2023-12-27 18:59:37 发布