一个监控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 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掉,主从同步出错,则发送邮件报警...
转载于:https://blog.51cto.com/donghui/663486