perl监控mysql slave 端口

? 一个监控mysql replication 是否同步,状态,端口,服务的小脚本.仅供参考.

 
 

#!/usr/bin/env?perl?
  
  
use?warnings;?
  
  
use?strict;?
  
  
use?DBI;?
  
  
use?Net::SMTP;?
  
  
use?Net::SMTP_auth;?
  
  
#use?MIME::Base64;?
  
  
use?IO::Socket;?
  
  
my?$slave_ip?=?'10.207.10.128';?
  
  
my?$master_ip?=?'10.207.10.92';?
  
  
my?$monitor_user?=?'monitor';?
  
  
my?$monitor_pass?=?'redhat';?
  
  
my?$monitor_db?=?'test';?
  
  
my?$db_port?=?'3306';?
  
  
my?@ip_list?=?("$master_ip","$slave_ip");?
  
  
my?$M_dbh?=?&get_connect($master_ip);?
  
  
my?$S_dbh?=?&get_connect($slave_ip);?
  
  
my?$M_connect?=?$M_dbh->ping();?
  
  
my?$S_connect?=?$S_dbh->ping();?
  
  
my?@data;?
  
  
????????foreach?my?$data?(&check_rep_status()){?
  
  
????????????????push?(?@data,$data);?
  
  
}?
  
  
#my?$data?=?do?{local?$/;<@data>};?
  
  
my?$data=join('r',@data);?
  
  
????????foreach?my?$ip?(@ip_list){?
  
  
????????????????????????if?(!?&port_status($ip)){?
  
  
????????????????????????????????????????&mail_send($ip,"Mysql_Port:?$db_port","$db_port?is?Downing...!");?
  
  
????????????????????????}?
  
  
????????}?
  
  
????????????????&mail_send($master_ip,"Mysql_Master_Ser","Mysql_Master_Ser?is?Downing...!")?if?(!?$M_connect);?
  
  
????????????????&mail_send($slave_ip,"Mysql_Slave_Ser","Mysql_Slave_Ser?is?Downing...!")?if?(!?$S_connect);?
  
  
????????????????&mail_send($slave_ip,"Replication","Replication_Error...!","$data")?if?(defined?(&check_rep_status));?
  
  
sub?port_status?{?
  
  
????????my?$ip?=?shift;?
  
  
????????my?$sock?=?IO::Socket::INET->new(Proto?=>?'tcp',?
  
  
????????????????????????????????????????PeerAddr?=>?$ip,?
  
  
????????????????????????????????????????PeerPort?=>?$db_port,?
  
  
????????????????????????????????????????Timeout?=>?2);?
  
  
????????$sock???return?1?:?return?0;?
  
  
}?
  
  
=this?
  
  
????????foreach?(@ip_list){?
  
  
????????????????if(check_sql_status($_)){?
  
  
????????????????????????print?"$_?DB?OKn";?
  
  
????????}?
  
  
}?
  
  
=cut?
  
  
sub?get_time?{?
  
  
????????my?$time?=?shift?||?time?();?
  
  
????????my?($sec,$min,$hour,$day,$mon,$year,$wday)?=?localtime($time);?
  
  
????????$year?+=?1900;?
  
  
????????$mon?+=1;?
  
  
????????$min?=?'0'.$min?if?length($min)?<?2;?
  
  
????????$sec?=?'0'.$sec?if?length($sec)?<?2;?
  
  
????????$mon?=?'0'.$mon?if?length($mon)?<?2;?
  
  
????????$day?=?'0'.$day?if?length($day)?<?2;?
  
  
????????$hour?='0'.$hour?if?length($hour)?<?2;?
  
  
????????my?$weekday?=?('Sun','Mon','Tue','Wed','Thu','Fri','Sat')[$wday];?
  
  
????????my?$time_now?=?"$year-$mon-$day?$hour:$min:$sec?$weekday";?
  
  
????????return?$time_now;?
  
  
}?
  
  
sub?get_connect{?
  
  
????????my?$host?=?shift;?
  
  
????????my?$dsn?=?"DBI:mysql:$monitor_db:$host:$db_port";?
  
  
????????my?$dbh?=?DBI->connect($dsn,$monitor_user,$monitor_pass,{RaiseError?=>?0,PrintError?=>?0});?
  
  
????????????????return?$dbh;?
  
  
}?
  
  
sub?check_rep_status?{?
  
  
????????my?($error,%result,$data);?
  
  
????????my?$default_seconds_behind_limit?=?400;?
  
  
????????my?$sql?=?"Show?Slave?Status";?
  
  
????????my?$sth=$S_dbh->prepare($sql);?
  
  
???????????$sth->execute();?
  
  
???????????%result?=?%{$data}??while?($data?=?$sth->fetchrow_hashref);?
  
  
????????if(?defined($result{'Slave_IO_Running'})?&&?$result{'Slave_IO_Running'}?ne?'Yes'){?
  
  
????????????????????????$error?=?"1002";?
  
  
????????????????????????return?("Slave_IO_Running=$result{'Slave_IO_Running'}nSlave_IO_State:?$result{'Slave_IO_State'}");?
  
  
????????????????}?
  
  
????????if(?defined($result{'Slave_SQL_Running'})?&&?$result{'Slave_SQL_Running'}?ne?'Yes'?){?
  
  
????????????????????????$error?=?"1003";?
  
  
????????????????????????return?("Slave_SQL_Running=$result{'Slave_SQL_Running'}nSlave_IO_State:?$result{'Slave_IO_State'}");?
  
  
????????????????}?
  
  
?
  
  
????????if(?defined($result{'Seconds_Behind_Master'})?&&?$result{'Seconds_Behind_Master'}?>?$default_seconds_behind_limit?){?
  
  
????????????????????????$error?=?"1004";?
  
  
????????????????????????return?("Seconds?Behind?Master=$result{'Seconds_Behind_Master'}");?
  
  
????????????????}?
  
  
?
  
  
????????if(?defined($result{'Last_Errno'})?&&?$result{'Last_Errno'}?!=?0?){?
  
  
????????????????????????$error?=?"1005";?
  
  
????????????????????????return?("Last_Errno=$result{'Last_Errno'}");?
  
  
????????????????}?
  
  
????????return?undef;?
  
  
}?
  
  
sub?mail_send?{?
  
  
????????my?$subject?=?shift;?
  
  
????????my?$subject_ip?=?shift;?
  
  
????????my?$mail?=?shift;?
  
  
????????my?$data?=?shift;?
  
  
????????my?$time_now?=?&get_time();?
  
  
????????my?$smtp_mail_host?=?'smtp.sinanet.com';?
  
  
????????my?$mail_user_from?=?'donghui@leju.sina.com.cn';?
  
  
????????my?$mail_user_to?=?'donghui@leju.sina.com.cn';?
  
  
????????my?$mail_user_pass?=?"P@ssW0rd";?
  
  
????????my?$mail_hello?=?'mail.sinanet.com';?
  
  
????????my?$smtp?=?Net::SMTP->new(?
  
  
????????????????Host?=>?"$smtp_mail_host",?
  
  
????????????????Hello?=>?"$mail_hello",?
  
  
????????????????timeout?=>?40,?
  
  
????????????????Debug?=>?0,)or?die?"can?not?connect?mail?server!n";?
  
  
????????$smtp->auth("$mail_user_from","$mail_user_pass")or?die?"auth?faild!n";?
  
  
????????$smtp->mail("$mail_user_from");?
  
  
????????$smtp->to("$mail_user_to");?
  
  
????????$smtp->data();?
  
  
????????$smtp->datasend("subject:warning:?$subject_ip?$subject");?
  
  
????????$smtp->datasend("From:donghui@leju.sina.com.cnn");?
  
  
????????$smtp->datasend("Dear?ALL:n");?
  
  
????????$smtp->datasend("t$subject??$subject_ipn");?
  
  
????????$smtp->datasend("t$mailn");?
  
  
????????$smtp->datasend("t$datanr");?
  
  
????????$smtp->datasend("----------------n");?
  
  
????????$smtp->datasend("$time_nown");?
  
  
????????$smtp->dataend();?
  
  
????????$smtp->quit();?
  
  
}?
  
  
???
 
 
端口down掉,主从同步出错,则发送邮件报警...