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 $/;};
[*]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 OK\n";
[*] }
[*]}
[*]=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.cn\n");
[*] $smtp->datasend("Dear ALL:\n");
[*] $smtp->datasend("\t$subject$subject_ip\n");
[*] $smtp->datasend("\t$mail\n");
[*] $smtp->datasend("\t$data\n\r");
[*] $smtp->datasend("----------------\n");
[*] $smtp->datasend("$time_now\n");
[*] $smtp->dataend();
[*] $smtp->quit();
[*]}
[*]
端口down掉,主从同步出错,则发送邮件报警...
页:
[1]