背景

之前由于公司從庫(kù)用的服務(wù)器型號(hào)并不統(tǒng)一摊欠，主要是磁盤IO這塊孔庭，相比主庫(kù)的服務(wù)器來(lái)說(shuō)差的有點(diǎn)大，于是只能通過(guò)腳本檢查下從庫(kù)落后的時(shí)間瞬沦，以便于后期的更換和遷移,好幾年前的腳本了添坊，想想perl的現(xiàn)狀剿另，真是已經(jīng)快下不來(lái)perl了

腳本

用perl 寫(xiě)的，主要因?yàn)镈B只有本地登錄密碼贬蛙，懶的在每臺(tái)DB上加帳號(hào)加權(quán)限雨女，所以只能用ssh的方式進(jìn)行了登錄后再查了，最后把結(jié)果更新在本地?cái)?shù)據(jù)庫(kù)里阳准，也方便查詢氛堕。由于數(shù)據(jù)庫(kù)都分布在不同機(jī)房的不同網(wǎng)段，機(jī)器很多野蝇，所以這樣其實(shí)也挺慢的讼稚，最好ssh的時(shí)候fork幾個(gè)進(jìn)程一起跑，會(huì)快不少浪耘。當(dāng)時(shí)寫(xiě)的時(shí)候忘了加了- - 乱灵。

#! /usr/bin/perl -w
#auther Barney by 20160508

#! /usr/bin/perl

use strict;
use DBI;
use Net::SSH::Perl;
use Mail::Sender;

sub main {
&db_init;
&db_update;
&info_print;
my $check_result=`cat data/result.txt | grep "Slave Server"`;
if($check_result) {
&mail_send;
}
else {
print "no behind!!\n";
}
}

sub ssh_get {
    my ($ip,$passwd,$socket)=@_;
    my $cmd="`which mysql`  -ubarney -ppasswd  -e 'show slave status\\G;' --socket=$socket | grep -i \"Seconds_Behind_Master\" |awk -F \":\" '{print \$2}'"; #有些mysql 是編譯安裝的。所以路徑不一定對(duì)七冲。。
    my $osuser="barney";
    my %params = (
        port => '22',
        protocal => '2,1',
    );
    my $ssh = Net::SSH::Perl->new($ip,%params);
    $ssh->login($osuser,$passwd,$socket);
    my ($out,$err,$exit) = $ssh->cmd($cmd);
    return ($out);
}


sub db_init {
    my $driver="DBI:mysql";
    my $database1="DB_OPS";
    my $user="barney";
    my $password='barney@123';
    my $db_connect = DBI->connect("$driver:$database1:mysql_socket=/var/run/mysqld/mysqld_new.sock","$user","$password");
}

sub db_update {
    my $db_connect=&db_init;
    my ($i,$j);
    $db_connect->do("SET NAMES utf8"); 
    my $db_status=$db_connect->prepare("select * from DB_OPS.t_master_db_instance;");
    $db_status->execute;
    
    my( $ip, $project, $behind ,$mysql_cmd,$socket,$check_flag );
    $db_status->bind_columns( undef, \$ip, \$project, \$behind, \$mysql_cmd, \$socket,\$check_flag);
    while( $db_status->fetch() ) {
        if ($ip =~ /192.168.1./ || $ip =~ /192.168.2./ || $ip =~ /192.168.3./ || $ip =~ /192.168.4./ || $ip =~ /192.168.5./ || $ip =~ /192.168.6./) {
            my $passwd="barney@1";
            my $i=&ssh_get($ip,$passwd,$socket);
            my $j=$i/3600;
            my $now_behind = sprintf("%.1f",$j);
            my $db_update = $db_connect->do("update DB_OPS.t_master_db_instance set db_behind='$now_behind' where db_ip='$ip';");
            if ($now_behind =~ /0./) {
                my $check_flag = $db_connect->do("update DB_OPS.t_master_db_instance set check_flag='0' where db_ip='$ip';");
            }
            else {
                my $check_flag = $db_connect->do("update DB_OPS.t_master_db_instance set check_flag='1' where db_ip='$ip';");
                }
        }

}
    $db_status->finish();
    $db_connect->disconnect();
}

sub info_print {
    my $data_dir="./data";
    mkdir $data_dir,0775;
    open(INFO,">$data_dir/result.txt");
    print INFO "Dear All:\n\n\n Slave info:\n\n ++++++++++++++++++++++++\n";
    &result_filter;
    print INFO "++++++++++++++++++++++++\n \n\n End Message from 192.168.2.104\n";
    close (INFO);
}

sub result_filter { ##將有延遲的數(shù)據(jù)篩選出來(lái)
    my $db_connect=&db_init;
    $db_connect->do("SET NAMES utf8");
    my $db_status=$db_connect->prepare("select db_ip,db_project,db_behind,socket from DB_OPS.t_master_db_instance where check_flag=1;");
    $db_status->execute;

    my( $ip, $project, $behind ,$socket);
    $db_status->bind_columns( undef, \$ip,\$project, \$behind, \$socket);
    while( $db_status->fetch() ) {
        my $behind_info="\t\tSlave Server: $ip Project Name: $project Behind Time: ${behind} hour Socket Time: $socket \n\n";
        print INFO "$behind_info";
    }
}


sub mail_send {     ##發(fā)送郵件通知
my $sender = new Mail::Sender
{
        smtp    => 'mail.shidc.barney.com',
        from    => 'ops@barney.com',
} or die "error";
    my $message = `cat ./data/result.txt`;
    if($sender-> MailMsg ({
             to      => 'ops@barney.com',
             subject => 'warnning!!! 從庫(kù)落后',
             msg     =>  $message })<0)
{
  die "$Mail::Sender::Error/n";
}
$sender->Close();
}   


&main();