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

 
  
  1. #!/usr/bin/env perl 
  2. use warnings; 
  3. use strict; 
  4. use DBI; 
  5. use Net::SMTP; 
  6. use Net::SMTP_auth; 
  7. #use MIME::Base64; 
  8. use IO::Socket; 
  9. my $slave_ip = '10.207.10.128'; 
  10. my $master_ip = '10.207.10.92'; 
  11. my $monitor_user = 'monitor'; 
  12. my $monitor_pass = 'redhat'; 
  13. my $monitor_db = 'test'; 
  14. my $db_port = '3306'; 
  15. my @ip_list = ("$master_ip","$slave_ip"); 
  16. my $M_dbh = &get_connect($master_ip); 
  17. my $S_dbh = &get_connect($slave_ip); 
  18. my $M_connect = $M_dbh->ping(); 
  19. my $S_connect = $S_dbh->ping(); 
  20. my @data; 
  21.         foreach my $data (&check_rep_status()){ 
  22.                 push ( @data,$data); 
  23. } 
  24. #my $data = do {local $/;<@data>}; 
  25. my $data=join('\r',@data); 
  26.         foreach my $ip (@ip_list){ 
  27.                         if (! &port_status($ip)){ 
  28.                                         &mail_send($ip,"Mysql_Port: $db_port","$db_port is Downing...!"); 
  29.                         } 
  30.         } 
  31.                 &mail_send($master_ip,"Mysql_Master_Ser","Mysql_Master_Ser is Downing...!") if (! $M_connect); 
  32.                 &mail_send($slave_ip,"Mysql_Slave_Ser","Mysql_Slave_Ser is Downing...!") if (! $S_connect); 
  33.                 &mail_send($slave_ip,"Replication","Replication_Error...!","$data") if (defined (&check_rep_status)); 
  34. sub port_status { 
  35.         my $ip = shift; 
  36.         my $sock = IO::Socket::INET->new(Proto => 'tcp', 
  37.                                         PeerAddr => $ip, 
  38.                                         PeerPort => $db_port, 
  39.                                         Timeout => 2); 
  40.         $sock ? return 1 : return 0; 
  41. } 
  42. =this 
  43.         foreach (@ip_list){ 
  44.                 if(check_sql_status($_)){ 
  45.                         print "$_ DB OK\n"; 
  46.         } 
  47. } 
  48. =cut 
  49. sub get_time { 
  50.         my $time = shift || time (); 
  51.         my ($sec,$min,$hour,$day,$mon,$year,$wday) = localtime($time); 
  52.         $year += 1900; 
  53.         $mon +=1; 
  54.         $min = '0'.$min if length($min) < 2; 
  55.         $sec = '0'.$sec if length($sec) < 2; 
  56.         $mon = '0'.$mon if length($mon) < 2; 
  57.         $day = '0'.$day if length($day) < 2; 
  58.         $hour ='0'.$hour if length($hour) < 2; 
  59.         my $weekday = ('Sun','Mon','Tue','Wed','Thu','Fri','Sat')[$wday]; 
  60.         my $time_now = "$year-$mon-$day $hour:$min:$sec $weekday"; 
  61.         return $time_now; 
  62. } 
  63. sub get_connect{ 
  64.         my $host = shift; 
  65.         my $dsn = "DBI:mysql:$monitor_db:$host:$db_port"; 
  66.         my $dbh = DBI->connect($dsn,$monitor_user,$monitor_pass,{RaiseError => 0,PrintError => 0}); 
  67.                 return $dbh; 
  68. } 
  69. sub check_rep_status { 
  70.         my ($error,%result,$data); 
  71.         my $default_seconds_behind_limit = 400; 
  72.         my $sql = "Show Slave Status"; 
  73.         my $sth=$S_dbh->prepare($sql); 
  74.            $sth->execute(); 
  75.            %result = %{$data}  while ($data = $sth->fetchrow_hashref); 
  76.         if( defined($result{'Slave_IO_Running'}) && $result{'Slave_IO_Running'} ne 'Yes'){ 
  77.                         $error = "1002"; 
  78.                         return ("Slave_IO_Running=$result{'Slave_IO_Running'}\nSlave_IO_State: $result{'Slave_IO_State'}"); 
  79.                 } 
  80.         if( defined($result{'Slave_SQL_Running'}) && $result{'Slave_SQL_Running'} ne 'Yes' ){ 
  81.                         $error = "1003"; 
  82.                         return ("Slave_SQL_Running=$result{'Slave_SQL_Running'}\nSlave_IO_State: $result{'Slave_IO_State'}"); 
  83.                 } 
  84.  
  85.         if( defined($result{'Seconds_Behind_Master'}) && $result{'Seconds_Behind_Master'} > $default_seconds_behind_limit ){ 
  86.                         $error = "1004"; 
  87.                         return ("Seconds Behind Master=$result{'Seconds_Behind_Master'}"); 
  88.                 } 
  89.  
  90.         if( defined($result{'Last_Errno'}) && $result{'Last_Errno'} != 0 ){ 
  91.                         $error = "1005"; 
  92.                         return ("Last_Errno=$result{'Last_Errno'}"); 
  93.                 } 
  94.         return undef; 
  95. } 
  96. sub mail_send { 
  97.         my $subject = shift; 
  98.         my $subject_ip = shift; 
  99.         my $mail = shift; 
  100.         my $data = shift; 
  101.         my $time_now = &get_time(); 
  102.         my $smtp_mail_host = 'smtp.sinanet.com'; 
  103.         my $mail_user_from = 'donghui@leju.sina.com.cn'; 
  104.         my $mail_user_to = 'donghui@leju.sina.com.cn'; 
  105.         my $mail_user_pass = "P@ssW0rd"; 
  106.         my $mail_hello = 'mail.sinanet.com'; 
  107.         my $smtp = Net::SMTP->new( 
  108.                 Host => "$smtp_mail_host", 
  109.                 Hello => "$mail_hello", 
  110.                 timeout => 40, 
  111.                 Debug => 0,)or die "can not connect mail server!\n"; 
  112.         $smtp->auth("$mail_user_from","$mail_user_pass")or die "auth faild!\n"; 
  113.         $smtp->mail("$mail_user_from"); 
  114.         $smtp->to("$mail_user_to"); 
  115.         $smtp->data(); 
  116.         $smtp->datasend("subject:warning: $subject_ip $subject"); 
  117.         $smtp->datasend("From:donghui\@leju.sina.com.cn\n"); 
  118.         $smtp->datasend("Dear ALL:\n"); 
  119.         $smtp->datasend("\t$subject  $subject_ip\n"); 
  120.         $smtp->datasend("\t$mail\n"); 
  121.         $smtp->datasend("\t$data\n\r"); 
  122.         $smtp->datasend("----------------\n"); 
  123.         $smtp->datasend("$time_now\n"); 
  124.         $smtp->dataend(); 
  125.         $smtp->quit(); 
  126. } 
  127.    
 
  
端口down掉,主从同步出错,则发送邮件报警...